MySQL Insert w/FOR loop.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

MySQL Insert w/FOR loop.

Post by conscience »

I'm trying to generate a dynamic SQL insert to track information entered from a form:

Code: Select all

while($row = mysql_fetch_array($ItemSet)) {
$BigBrother = "INSERT INTO orderhistory (datetime, legalname, title, degree, body, email, phone, address, city, state, zip, totalcost, paymethod,";
for($n = 0; $n = count($row[5]); $n++) {
	$BigBrother .= " item" . $n . ","; }	
for($q = 0; $q = count($row[3]); $q++) {
	$BigBrother .= " qty" . $q . ","; }
$BigBrother .= ") VALUES ('$date', '$name', '$title', '$degree', '$body', '$email', '$phone', '$addr" . " $addr2', '$city', '$state', '$zip', '$total', '$method',";
foreach($row[5] as $name) {
	$BigBrother .= "\'" . $name . "\',"; }	
foreach($row[3] as $qty) {
	$BigBrother .= "\'" . $qty . "\',"; }
$BigBrother .= ")";
global $BigBrother;
}
As far as I can tell, this should work. I get a valid resource ID for the initial query ($ItemSet), so I know the information is there. But the page loads forever due to the FOR loops, and the FOREACH lines fail as well, so it almost seems like the query information isn't getting passed to the WHILE loop. I've also tried using the row names ($row['itemName'] and $row['qty']) but nothing works. WTF? Syntax issues?


feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your table structure sounds a bit off. You should have 2 tables. One for the order details, one for the order items. They could handle your "active" orders as well...

They are attached together by a single id reference the item table would have pointing to the order id.

Have a read: http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Re: MySQL Insert w/FOR loop.

Post by scorphus »

Since the = (assignment) operator always evaluates to true, try changing the for statements _conditions_ to either

Code: Select all

for($n = 0; $n <= count($rowї5]); $n++) {
	$BigBrother .= &quote; item&quote; . $n . &quote;,&quote;; }	
for($q = 0; $q <= count($rowї3]); $q++) {
	$BigBrother .= &quote; qty&quote; . $q . &quote;,&quote;; }
or

Code: Select all

for($n = 0; $n < count($rowї5]); $n++) {
	$BigBrother .= &quote; item&quote; . $n . &quote;,&quote;; }	
for($q = 0; $q < count($rowї3]); $q++) {
	$BigBrother .= &quote; qty&quote; . $q . &quote;,&quote;; }
Then you may have a go!

-- Scorphus.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

heh, didn't even see that, good catch scorphus.. Although the assignment operator doesn't always evaluate true. It always returns the value assigned, whatever that may be, which could evaluate to true or false.
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Re: MySQL Insert w/FOR loop.

Post by conscience »

scorphus wrote: Then you may have a go!
Aye, that did the trick in the FOR loop (much thanks to you), but the FOREACH loops are still buggered.. which still makes no sense.
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Post by conscience »

Noted and appreciated.
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Post by conscience »

For the record, I get the following error:

Warning: Invalid argument supplied for foreach()
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

mysql_fetch_array() returns a record. The record will not contain further array in php's form. You'll need to explode() or otherwise break the string MySQL returns into an array of some sort if you want to do that.
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

feyd, I thought "Where is feyd and his bull's eyes?". You are the fast one who sees what most don't... BTW, thanks for explaining and clarifying the assignment operator evaluation issue, I couldn't do it with just an italic-font always.
conscience wrote:(...) but the FOREACH loops are still buggered.. which still makes no sense.
I think you're passing a string instead of an array as a parameter to those foreach loops. Check what is the content of $row[5] and $row[3], they seem to be strings and not arrays.

Another point, even if those foreach loops would work, their blocks genarate a query string with an ending comma (,) values listing and the final query will be a buggy one. It can be done this way ([edit]just for a sake of explaining why the query bug occurs and how to fix it, the foreach statements still don't work[/edit]):

Code: Select all

$BigBrother .= &quote;) VALUES ('$date', '$name', '$title', '$degree', '$body', '$email', '$phone', '$addr&quote; . &quote; $addr2', '$city', '$state', '$zip', '$total', '$method'&quote;;
foreach($rowї5] as $name) {
    $BigBrother .= &quote;, '$name'&quote;; }
foreach($rowї3] as $qty) {
    $BigBrother .= &quote;, '$qty'&quote;; }
$BigBrother .= &quote;)&quote;;
Also, please note:
-> Those \' are not necessary and were removed
-> The string concatanation operators (.) were also removed and the string will be correctly parsed

-- Scorphus
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Post by conscience »

feyd wrote:mysql_fetch_array() returns a record. The record will not contain further array in php's form. You'll need to explode() or otherwise break the string MySQL returns into an array of some sort if you want to do that.
If this is true, how is it that I can pull a count() on the result set and get the values I want?
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Post by conscience »

scorphus wrote:I think you're passing a string instead of an array as a parameter to those foreach loops. Check what is the content of $row[5] and $row[3], they seem to be strings and not arrays.
It is a text string, yes, but without any unique identifying characters. So if the output of mysql_fetch_array is comma-separated I can do something like this?

Code: Select all

foreach($row[5] as $name) {
    explode(",", $name);
    $BigBrother .= ", '$name'"; }
foreach($row[3] as $qty) {
    explode(",", $qty);
    $BigBrother .= ", '$qty'"; }

feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

conscience wrote:If this is true, how is it that I can pull a count() on the result set and get the values I want?
That's why:
PHP: count - Manual wrote:int count ( mixed var [, int mode] )

Returns the number of elements in var, which is typically an array, since anything else will have one element.
conscience wrote:(..) So if the output of mysql_fetch_array is comma-separated I can do something like this? (...)
You've got close, but the logic is wrong, or inverse! In fact it is a bit different, take a look:

Code: Select all

foreach(explode(',', $row[5]) as $name) { // [url=http://www.php.net/explode]explode[/url]
    $BigBrother .= ", '$name'"; } // [url=http://pear.php.net/manual/en/standards.php]Chapter 4. Coding Standards[/url]
foreach(explode(',', $row[3]) as $qty) { // [url=http://www.php.net/explode]explode[/url]
    $BigBrother .= ", '$qty'"; } // [url=http://pear.php.net/manual/en/standards.php]Chapter 4. Coding Standards[/url]
-- Scorphus
Post Reply