PHP - Select From Table Where Status !='rejected'

Hi,

I would like to do the following in PHP using a MySQL database, but not sure how.

select a, b,c etc.. from mailbox where id = X
then
insert id,a,b,c etc.. into problem

When I select, I will be calling the id

So I would like it to get data from mailbox and then insert it into problem.
How would I go about doing this?

Thanks for any help you can give me

guys, please help

i need balance amount of fees all students has to pay. Following is the DB structure. `feepayid` is primary key for paymentstable and paymentregister & `studentid` is primary in studentstable. I have written a query for this, please evaluate the query.

paymentstable
feepayid classid paydate paymode feeamount remarks feediscount 32 8 2012-03-06 19:32:35 1 500 hgfhf 0 31 8 2012-03-04 19:32:35 1 800 hgfhf 0 30 8 2012-02-06 19:32:35 1 1200 hgfhf 0 29 8 2012-02-06 19:32:35 1 1100 hgfhf 0
paymentregister
feepayid payid totalfee studentid 32 3 1500 2 31 2 3500 2 30 1 4500 4 29 3 5500 4
studentstable
studentid studentname 2 john 4 mathew 5 peter 6 mary
Code: [Select]
SELECT d.`studentid`, SUM(a.`totalfee` - (SELECT SUM(`feeamount`) FROM `paymentstable`  WHERE a.`feepayid` = `feepayid`)) AS balancefee
FROM `studentstable` d
LEFT JOIN `paymentregister` a ON a.`studentid` = d.`studentid`
GROUP BY d.`studentid`;

I have 2 question, which are related to each other.
They are about my current school project. Which I have been really stupid for, I took it too slow and thought I had enough time.
But no, I had to do almost my whole project in the last 3 weeks and sunday is the deadline.
I have a couple pages to make left, which one of them is the hardest one and I have the 2 questions about it.

Question 1.


I have to make a invoice with 4 different prices, divided over 3 tables in my mysql.

Let me sum up the tables.

Products
Productsid - productprice

Productrule
Productruleid - amount - productid - maintainanceid

Maintainance
Maintainanceid - price

I need to show the price
The price of each product
The amount * productprice
The amount * productprice + price

All in a table, it mustn't be edited, so it not in a form.

I know I have to use a SQL select sum for it, but I have never used it, and my querys always fail in the first place

I used a for and while loop to choose the amount of and the name of products for the mechanic page.
There was a max of 5 products to select, just to keep it easier.
If I would only pick 2, it wouldn send something empty to the database and it wasn't required.
My teacher helped me with that and it works.

So then we come to question 2, can I use that in a table, instead of a drop down menu, which I used at the mechanic page.

This is the PHP code:
Code: [Select]
for($i=1;$i<=5;$i++)
{
if(($_POST['aantal'.$i] != "") AND ($_POST['product'.$i] != ""))
{
$query3 = mysql_query ("INSERT INTO onderdeelregel SET
aantal='".$_POST['aantal'.$i]."',
onderdelenid='".$_POST['product'.$i]."',
werkzaamhedenid='".$werkzaamhedenid."'");
}
}


And the HTML:
Code: [Select]
<table border="0">
<tr>
<th>Aantal</th>
<th>Product</th>
<tr>

<!-- START BLOCK : PRODUCT_REGEL -->
<tr>
<td>
<input class="wijzigInput" name="aantal{NUMMER}" id="aantal" type="text" maxlength="2" size="2" /></td>
<td>
<select name="product{NUMMER}">
<option selected="selected" class="wijzigOption" value=""></option>
<!-- START BLOCK : PRODUCT_REGEL_OPTION -->
<option class="wijzigOption" value="{PRODUCT_REGEL_WAARDE}">{PRODUCT_REGEL_NAAM}</option>
<!-- END BLOCK : PRODUCT_REGEL_OPTION -->
</select>
</td>
</tr>
<!-- END BLOCK : PRODUCT_REGEL -->
</table>
Oh I didn't used a while loop there, but I know I have to use it in the customer page.

I know this isn't going to work on my own, and I haven't got much time left.
I would really appreciate it if anyone could give me some code, or atleast a start of the sql select sum and the for and while loop in a table.
I know I have add other stuff to the invoice, but that isn't hard, so with this I should be able to do it and finish my project in time.
I usually don't like that, I want to learn it myself, but time is very little right now
Oh and sorry for the Dutch in my code.

Please help me, I don't want to stress out and fail it.
School is very important to me and I hate myself for taking it so slow.


Thanks a lot

Chris

Hi,

I'm a little bit new to php and I'm having some issues selecting some data from a mySQL database and fetching it into a fluid html table.....


Basicly what I want is a table with 4 columns and a X number of rows depending on how much entry is stored in the DB.

Here's the SELECT code :

Code: [Select]
<?php 

mysql_connect("host", "user", "pass") or die(mysql_error()); 
mysql_select_db("DB") or die(mysql_error()); 

$id = $_GET['id'];

$data = mysql_query("SELECT * FROM artist_gallery WHERE artist_picid='$id'") 
or die(mysql_error());

while($info = mysql_fetch_array( $data ))

     {

Here the part I just can't figure.... what I want is to fetch the x number of picture in the DB into a html table :

Code: [Select]
echo "
<table border=\"1\" cellpadding=\"1\" cellspacing=\"0\">
<tr>
 <td><img src=\"".$info['picture']."\" border=\"0\" /></td>
 <td><img src=\"".$info['picture']."\" border=\"0\" /></td>
 <td><img src=\"".$info['picture']."\" border=\"0\" /></td>
</tr>
</table>
    ";
    }

?>

The pictures are just repeating 3 times at each row...

Any help will be greatly appreciated!!!


Thanks!

hi im trying to use  query to select from a table where the price is between to values that the user has to input such as min of 3 and max of 8 and return all the fields from the database where it is true im unsure how to do this but this is what i have so far   $res = pg_query ($conn, "SELECT ref,artist,composer,genre,title,album,label,price,description FROM music WHERE price = < && > ");

Hi, I'm a little bit new to php and i'm having a small problem. Let me explain with the script :

Code: [Select]
<?php

//connect to mysql

$data = mysql_query("SELECT * FROM forum_topic WHERE topic_id='$topic'") 
or die(mysql_error());

while($info = mysql_fetch_array( $data ))

$dete = mysql_query("SELECT * FROM forum_topic WHERE login='$info['username']'") 
or die(mysql_error());

while($info2 = mysql_fetch_array( $dete ))

     {

echo "......";
     }
?>
 
So I have the first SELECT command that will get the data from table forum_topic and array the data and after that I have a second SELECT that will get the data from table_members but this time time the WHERE clause equals to some data "arrayed" in the first SELECT command...And the end I need to output the data from both tables....

Here's the error I get :


Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/m/a/z/mazemontana/html/login/forum_viewtopic.php on line 73


...I know that my syntax is probably not good but can't figure out how to do it.....

Any help will be greatly appreciated!

Thanx

I have a table in a database that I need to filter upon using dependent select boxes.  The database was poorly designed and would be difficult to break into separate tables without affecting code written in other places.  I have found some good code and understanding of how to implement the dependent select boxes if you have two tables with one column functioning as a lookup value between the two.

I have provided a short bit of sql of what the table looks like with some column names changed.  I would need to first sort by lastname and have firstname dependent on the selection of lastname.  Any help would be greatly appreciated.

Code: [Select]
create table `user` (
`id` int(3) Not Null Auto_increment,
`firstname` varchar (60),
`lastname` varchar (24),
`age` double ,
`hometown` varchar (75),
`job` varchar (75),
`birthdate` date
);
insert into `user` (`id`, `firstname`, `lastname`, `age`, `hometown`, `job`, `birthdate`) values
('1','Peter','Griffin','41','Quahog','Brewery','1960-01-01'),
('2','Lois','Griffin','40','Newport','Piano Teacher','1961-08-11'),
('3','Joseph','Swanson','39','Quahog','Police Officer','1962-07-23'),
('4','Glenn','Quagmire','41','Quahog','Pilot','1960-02-28'),
('5','Megan','Griffin','16','Quahog','Student','1984-04-24'),
('6','Stewie','Griffin','2','Quahog','Dictator','2008-03-03');

 

Hi there, wanted to see how I would have the columns I specified in 

$fields = array('id','type');

 

then be generated dynamically in the while loop and any other field I state in

$fields = array('id','type');

then its added to the while loop in the below
 

include('db.php');

class SelectData{


public function wpquery_select($conn,$sql,$fields){

$results = $conn->query($sql);

if($results->num_rows > 0){

	while($row = $results->fetch_assoc()){
		
		// How would you process the array $fields which holds all the rows specified and dynamically create echo $row['id'], $row['id'] and so on?
		
	}
	
  }

$conn->close();

 }

	
}



$select = new SelectData();

$sql = "SELECT * FROM orders";


$fields = array('id','type');


$select->wpquery_select($conn,$sql,$fields);

Thank you

I want to make a table for each paddler_id (Field) which exists into pushup Database

My code for paddler_id = 1 is


require "../sql.php";

$result = mysql_query("EXPLAIN pushup");
$r = mysql_query("SELECT * FROM paddlerinfo t1 LEFT JOIN pushup t2 on t2.paddler_id=t1.id LEFT JOIN practicedate t3 on t3.practice=t2.practice_id ORDER BY t1.firstname ASC ");
$r1 = mysql_query("SELECT * FROM pushup  where paddler_id =1 ORDER BY practice_id ASC ");

echo "<table width='30%' border='1' cellpadding='0' cellspacing='0' style='font-family: monospace'>";
echo "<tr>";
echo "<td>DATE</td>";
while ($row = mysql_fetch_array($result)) {

if (in_array($row["Field"], array("paddler_id", "practice_id", "p_id"))) continue;

echo "<td>",($row["Field"]), "</td>";
}
echo "</tr>";

while (($data = mysql_fetch_array($r1, MYSQL_ASSOC)) !== FALSE) {
   unset($data["p_id"],$data["paddler_id"]);
echo "<tr>";
   foreach ($data as $k => $v) { 
echo "<td>";
      echo"$v";
echo "</td>";
}

echo "</tr>";
}


echo "</table>";

mysql_free_result($result);
mysql_free_result($r);
mysql_free_result($r1);
mysql_close();



The problem is on line $r1 = mysql_query("SELECT * FROM pushup  where paddler_id =1 ORDER BY practice_id ASC ");
I want to put something which makes it paddler_id = X where x = 2,3,4,5... and x exists in database and do not print twice the x (because that database may have rows where x = same id


How do i do that??

Please I want get data from mysql; 

1. The last 24 hours. 
2 the last 7 days 
3. the last Months  4. Last 12 hours

please try and help me

I have the following html code and php code. I would like clients to select which fields in the mysql database they would like to have appear in the resultant table by checking the text box next to the field in the html form. For instance they should be able to select first name and city and have only those columns appear in the resultant table.

# <html>
# <head>
# <title>Search Clients Database</title>
# </head>
# 
# <body>
# <h1>Clients Database Search Page</h1>
# <form action="searchclients.php" method="post">
#   <table width="100%" border="1" cellspacing="1" cellpadding="1">
#     <tr>
#       <th width="37" scope="col">&nbsp;</th>
#       <th width="114" scope="col">Fields</th>
#       <th width="169" scope="col">Filter Value</th>
#       <th width="1157" scope="col">&nbsp;</th>
#     </tr>
#     <tr>
#       <td><input type="checkbox" name="idc" id="idc"></td>
#       <td>ID</td>
#       <td><input type="text" name="ID" id="ID"></td>
#       <td>&nbsp;</td>
#     </tr>
#     <tr>
#       <td><label>
#         <input type="checkbox" name="fnc" id="fnc">
#       </label></td>
#       <td>First Name</td>
#       <td><input type="text" name="FirstName" id="FirstName"></td>
#       <td>&nbsp;</td>
#     </tr>
#     <tr>
#       <td><label>
#         <input type="checkbox" name="lnc" id="lnc">
#       </label></td>
#       <td>Last Name</td>
#       <td><input type="text" name="LastName" id="LastName"></td>
#       <td>&nbsp;</td>
#     </tr>
#     <tr>
#       <td><label>
#         <input type="checkbox" name="cc" id="cc">
#       </label></td>
#       <td>City</td>
#       <td><input type="text" name="City" id="City"></td>
#       <td>&nbsp;</td>
#     </tr>
#     <tr>
#       <td><label>
#         <input type="checkbox" name="pc" id="pc">
#       </label></td>
#       <td>Province</td>
#       <td><select name="Province" id="Province">
#         <option selected> </option>
#         <option>KZN</option>
#         <option>North West Province</option>
#         <option>Gauteng</option>
#         <option>Free State</option>
#         <option>Mpumalanga</option>
#         <option>Eastern Cape</option>
#         <option>Limpopo Province</option>
#         <option>Northern Cape</option>
#         <option>Western Cape</option>
#       </select></td>
#       <td>&nbsp;</td>
#     </tr>
#   </table>
#   <p>
#     <input type="submit" name="submit" value="Search" />
#   </p>
# </form>
# 
# </body>
# </html>


# <?php
# mysql_connect ("localhost", "username","password")  or die (mysql_error());
# mysql_select_db ("clients");
# 
# $fn = $_POST['FirstName'];
# $ln = $_POST['LastName'];
# $city = $_POST['City'];
# $prov = $_POST['Province'];
# 
# $idc = $_POST['idc'];
# $fnc = $_POST['fnc'];
# $lnc = $_POST['lnc'];
# $cc = $_POST['cc'];
# $pc = $_POST['pc'];
# 
# ?>
# 
# <html>
# <body>
# 
# <table border=1>
#   <tr>
#    <th>ID</th>
#    <th>First Name</th>
#    <th>Last Name</th>
#    <th>City</th>
#    <th>Province</th>
# </tr>
# 
# <?php
# $sql = mysql_query("select ID, FirstName, LastName, City, Province from clients where FirstName like '%$fn%' and LastName like '%$ln%' and City like '%$city%' and Province like '%$prov%'");
# 
# while ($row = mysql_fetch_array($sql)){
# 
#     $id = $row['ID'];
#     $fname = $row['FirstName'];
#     $lname = $row['LastName'];
#     $city = $row['City'];
#     $prov = $row['Province'];
# 
# ?>
# 
# <tr>
# <th><?php echo $id;?></th>
# <th><?php echo $fname;?></th>
# <th><?php echo $lname;?></th>
# <th><?php echo $city;?></th>
# <th><?php echo $prov;?></th>
# </tr>
# 
# <?php } //this ends the if?>
# 
# </table>
# </html>

I have set up a form page with a select box of colleges to select. I want the "options" in the select box to be values taken from a field called "name" in a table called "colleges" and they should be ordered alphabetically. I also want the default selected option to be "none."

I have attached a picture to describe what i want. Please be detailed with the code. I am fairly new to php and mysql. Thank you.

Hi all, probably a fairly straightforward question, but I can't find the answer I'm looking for:

I have an array 'idents' that contains ID numbers. I now want to write a MySQL query that selects all values of the table-field 'email' where the field 'ID' = any of the IDs stored in 'idents'. Then I want to put those emails in an array 'emails'.

So e.g. my table is as follows:
ID         |   email
----------------------
111111 |   email1
222222 |   email2
333333 |   email3
444444 |   email4
555555 |   email5
666666 |   email6

and the array 'idents' is as follows: '111111','444444','555555'

then I want the query to return 'emails' = array('email1','email4','email5');

Thanks for your help!

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=321906.0

Hi,

I have a DB with two tables, customers and orders. One customer may have many orders, What I want to do is do a select on the two tables and get the customer info once, and then all the orders associated with the account.

I saw how to do this once before using a join and then having to use a PHP foreach to break it up. I am just not sure of how to put the two together to make it work, or maybe there is an easier way completely.

Any help is appreciated.

Thanks

B

Example:

Bob Smith
Shoes
Coat
Boots
Gloves

Sally Jones
Hat
Purse
Boots
Umbrella