469,926 Members | 1,524 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,926 developers. It's quick & easy.

Inserting multiple rows of data in table

66
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_array($res)) {
echo "$flower {$row['flowername']}";
echo " {$row['flowerdesc']} <br>";
}
}
?> [/PHP]



many thanks in advance to anyone who can help me with this!
Feb 13 '08 #1
16 2297
ronverdonk
4,258 Expert 4TB
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_array($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
Feb 13 '08 #2
jasone
66
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
Feb 14 '08 #3
jasone
66
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\university\FinalYearProject\ testing folder\choice.php on line 18'

:-s
Feb 14 '08 #4
ronverdonk
4,258 Expert 4TB
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
Feb 14 '08 #5
jasone
66
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\university\FinalYearProject\ testing folder\choice.php 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_array($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
Feb 14 '08 #6
ronverdonk
4,258 Expert 4TB
No line 19 in sight!

Ronald
Feb 14 '08 #7
jasone
66
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
Feb 14 '08 #8
ronverdonk
4,258 Expert 4TB
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
Feb 14 '08 #9
jasone
66
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]
Feb 14 '08 #10
ronverdonk
4,258 Expert 4TB
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
Feb 14 '08 #11
jasone
66
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\university\FinalYearProject\ testing folder\choice.php 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_array($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]
Feb 14 '08 #12
ronverdonk
4,258 Expert 4TB
Show all code of choice.php because I can't see what lines you are talking about.

Ronald
Feb 14 '08 #13
jasone
66
[PHP]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled 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_array($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
Feb 14 '08 #14
ronverdonk
4,258 Expert 4TB
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.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled 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_array($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
Feb 14 '08 #15
jasone
66
I take my hat off to you once again ronald!

if you need any help in the future... you know where i am ;-)

hehe! maybe one day!
Feb 14 '08 #16
ronverdonk
4,258 Expert 4TB
I take my hat off to you once again ronald!

if you need any help in the future... you know where i am ;-)

hehe! maybe one day!
Thanks for your kind words. See you again.(see my PM)

Ronald
Feb 14 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Raj Kotaru | last post: by
3 posts views Thread by Joachim Klassen | last post: by
5 posts views Thread by Arsen V. | last post: by
3 posts views Thread by rcoco | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.