PHP, MySQL, Loops and getting them to work!

u0754284 · #1 (**permalink**) 04-17-11, 07:54

Hi Guys,

I'm new to these forums and hoping you can help me with a problem I have using PHP, MySQL and Loops.

I am trying to create an application where the user can order suppliers in what quantity they want and I have the following:

Firstly the db file:
(

PHP Code:


			
function db_connect(){
$DBHOST="localhost";
$DBUSER="is here";
$DBPASSWORD="is here";
$DBDATABASE="is here";

mysql_connect($DBHOST , $DBUSER , $DBPASSWORD)
    or die ("Can't talk to to mysql");
mysql_select_db($DBDATABASE)
    or die ("Can't connect to the database");
}

)

Then I have a page which pulls all of the products you can order from the database and displays them in a table using a while loop, which all works fine:

(

PHP Code:


			
<form method="post" action="submitOrder.php" >
<table>
include ('dbConnection.php');
db_connect();

$ypo_products=mysql_query("SELECT * FROM table_name");

while ($row = mysql_fetch_assoc($ypo_products)) {
print "<tr><td><input type=\"text\" value=\"$row[Qty]\"</td>";
print "<td><input type=\"text\" value=\"$row[Code]\" name=\"code\" readonly=\"readonly\"</td>";
print "<td><input type=\"text\" value=\"$row[Pack]\" readonly=\"readonly\"</td>";
print "<td><input type=\"text\" value=\"$row[Desc]\" readonly=\"readonly\"</td></tr>";
}

)

That displays all the fields from the database in a table on screen. They are all in text boxes, but only one of them is editable which is the quantity one. Example of last entry: (Qty, Code, Size, Desc)

Code:

0, 633445, 2.5kg, Peach Slices

NOW, the problem - when the user has enetered the quantity of everything they want - that needs to go into a different table called orders. The problem is that because all of the products have been loaded from SQL using loops it only ever remembers the last entry, as above!

The submit file is below: I have just used the same variable for testing purposes at the moment (code):

(

PHP Code:


			
include ('dbConnection.php');
db_connect();

$code_list = $_POST['code'];

$query=mysql_query("INSERT INTO fyp_orders_ypo (Order, Qty, Code, Pack, Desc) 
VALUES ('NULL', '5','".$code_list."','".$code_list."','".$code_list."')");

echo "Database updated with: " .$code_list ;

)

So - what do I require?? I need this submit file to enter EVERY product into the orders table, with the correct number in the qty column depending what the user has entered. Should I be using loops again?

I hope this all makes sense and somebody can help!
Thanks,
Chris!

futurity · #2 (**permalink**) 04-18-11, 11:53

I don't know why you're copying all your product information into your orders table. All you need in your orders table is a foreign key to the products table.

You need to submit your quantities as an array. Assuming that 'code' is the primary key to the product table, something like this:

PHP Code:


			
while ($row = mysql_fetch_assoc($ypo_products)) {
    echo '<tr><td><input type="text" name="quantities[' 
        . $row['Code'] . ']" value="' . $row['Qty'] . '"></td>';
    \\ etc ....
}

To insert the data:

PHP Code:


			
$values = array();
foreach ($_POST['quantities'] as $code => $quantity) {
    $values[] = "($code, $quantity)";
}

$sql = 'insert into order (code, quantity) values ' . implode(',', $values);
mysql_query($sql);

Internet Services

Web Development

Digital Marketing

Consumer Tech