Inserting multiple rows of data in table

[jasone]

Hi all,

The system im working on currently allows the user to select a number of flowers.. click submit and whatever they clicked is passed onto the next page, i now want them to click order and each flower is inserted into the table.

username - flowerid - qty - total
user1 ------------ 2 ------- 1----- 1.99
user1 ------------ 3 ------- 5----- 2.55
user1 ------------ 6 ------- 1----- 0.50

i havent set up the session yet to display the username etc... just trying to insrt the flowers selected into multiple rows... heres the code ive got so far.!

line 7 shows where i would but the sql statement that allows me to insert the records.

[PHP]<?php
include "constants/flowertimedbcnx .php";

echo 'You have chosen the following flowerids:<br>' ;
foreach($_GET['flowerid'] as $flower) {
$res = mysql_query ("SELECT * FROM flowers WHERE flowerid = $flower");
//$order = mysql_query ("INSERT INTO bqt_order VALUES ....... ?
while ($row = mysql_fetch_arr ay($res)) {
echo "$flower {$row['flowername']}";
echo " {$row['flowerdesc']} <br>";
}
}
?> [/PHP]



many thanks in advance to anyone who can help me with this!

[ronverdonk]

When you are sure that the flowerid is unique, you can leave the WHILE loop out. When you want each type of flower in the order to be stored in a separate row, you'll have to do the INSERT within the foreach loop, as follows:
[php]
foreach($_GET['flowerid'] as $flower) {
$res = mysql_query ("SELECT * FROM flowers WHERE flowerid = $flower");
// check here if any rows were selected
$row = mysql_fetch_arr ay($res))
echo "$flower {$row['flowername']} : {$row['flowerdesc']} <br>";
if (!mysql_query ("INSERT INTO bqt_order (flowerid, quantity) VALUES (".$row['flowerid'].",".$row['quantity'])
die("INSERT error: ".mysql_error() );
}
[/php]
Ronald

[jasone]

Hi Ronald,

looking at the script it looks very logical.. well written!!

for some reason though im getting the 'http 500 internal server error' usualy get this when things such as ';' or '}' are missing ive checked through but cant see any obvious causes of this... all the names match up in the tables etc but it just wont work! would it be possible to put the insert on a link press? not sure if this would be easier or not..

kind regards
Jason

[jasone]

hey, ok so ive tested it in firefox... and it gives a better message, its saying the following: '
Parse error: syntax error, unexpected T_EXIT in C:\web\Apache2\ htdocs\universi ty\FinalYearPro ject\testing folder\choice.p hp on line 18'

:-s

[ronverdonk]

So all you have to do is look at line 18 and see where in that line, or before that line, you missed something like a closing bracket, a semicolumn, a quote, etc.

Ronald

[jasone]

Hi ron,

tried a number of things, but really cant see what missing, here the script with the error:

Parse error: syntax error, unexpected T_EXIT in C:\web\Apache2\ htdocs\universi ty\FinalYearPro ject\testing folder\choice.p hp on line 19

[PHP]<?php

include "constants/flowertimedbcnx .php";

foreach($_GET['flowerid'] as $flower) {
$res = mysql_query ("SELECT * FROM flowers WHERE flowerid = $flower");
// check here if any rows were selected
$row = mysql_fetch_arr ay($res);
echo "$flower {$row['flowername']} : {$row['flowerdesc']} <br>";
if (!mysql_query ("INSERT INTO orders ('flowerid', 'flowername') VALUES (".$row['flowerid'].",".$row['flowername'])
die("INSERT error: ".mysql_error() );
}
?> [/PHP]

line 10 aboive is line 19!

Jason

[ronverdonk]

No line 19 in sight!

Ronald

[jasone]

ok problem solved.. was missing a closing bracket...

just getting this error now, but i will try and solve this... :-)

INSERT error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''flowerid', 'flowername') VALUES (3,wewrwe' at line 1

[ronverdonk]

This is the error: [php]VALUES (".$row['flowerid'].",".$row['flowername'])[/php]
Typo from my part. VALUES substring starts with a double quote and a dot.

Ronald

[jasone]

hi, tried taking that out.. still getting this error:

INSERT error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3,wewrwe' at line 1

for this line of code:

[PHP] if (!mysql_query ("INSERT INTO orders (flowerid, flowername) VALUES ".$row['flowerid'].",".$row['flowername']))[/PHP]

[ronverdonk]

I don't know what it is with me. I gave you the wrong answer before. What you must do is enclose the flowername within quotes because it is a character-type field. I assigned the 2 columns to variables so it is easier to see:
[php] $fi=$row['flowerid'];
$fn=$row['flowername'];
if (!mysql_query ("INSERT INTO orders (flowerid, flowername) VALUES($fi,'$fn '"))[/php]

Ronald

[jasone]

lol, nothing wrong with you, you are a legend in my eyes!

though for some reason now it doesnt like the curly brackt on line 21...

if i show you the entire code and the error, any chance you can see why?

error :

Parse error: syntax error, unexpected '}' in C:\web\Apache2\ htdocs\universi ty\FinalYearPro ject\testing folder\choice.p hp on line 21

code:

[PHP]<?php

include "constants/flowertimedbcnx .php";

foreach($_GET['flowerid'] as $flower) {
$res = mysql_query ("SELECT * FROM flowers WHERE flowerid = $flower");
// check here if any rows were selected
$row = mysql_fetch_arr ay($res);
echo "$flower {$row['flowername']} : {$row['flowerdesc']} <br>";
$fi=$row['flowerid'];
$fn=$row['flowername'];
if (!mysql_query ("INSERT INTO orders (flowerid, flowername) VALUES ('$fi','$fn')")
}
?> [/PHP]

[ronverdonk]

Show all code of choice.php because I can't see what lines you are talking about.

Ronald

[jasone]

[PHP]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dt d">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitl ed Document</title>
</head>

<body>
<?php

include "constants/flowertimedbcnx .php";

foreach($_GET['flowerid'] as $flower) {
$res = mysql_query ("SELECT * FROM flowers WHERE flowerid = $flower");
// check here if any rows were selected
$row = mysql_fetch_arr ay($res);
echo "$flower {$row['flowername']} : {$row['flowerdesc']} <br>";
$fi=$row['flowerid'];
$fn=$row['flowername'];
if (!mysql_query ("INSERT INTO orders (flowerid, flowername) VALUES ('$fi','$fn')") ;
}
?>



</body>
</html>
[/PHP]

here we go!

Jason

[ronverdonk]

You cut off the original 'die(..)' statement after the insert. The code should be
[php]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dt d">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitl ed Document</title>
</head>
<body>
<?php
include "constants/flowertimedbcnx .php";

foreach($_GET['flowerid'] as $flower) {
$res = mysql_query ("SELECT * FROM flowers WHERE flowerid = $flower");
// check here if any rows were selected
$row = mysql_fetch_arr ay($res);
echo "$flower {$row['flowername']} : {$row['flowerdesc']} <br>";
$fi=$row['flowerid'];
$fn=$row['flowername'];
if (!mysql_query ("INSERT INTO orders (flowerid, flowername) VALUES ('$fi','$fn')") )
die("INSERT error: ".mysql_error() );
}
?>
</body>
</html>[/php]
Ronald