Page 1 of 1

MySQL Insert w/FOR loop.

Posted: Thu Mar 31, 2005 11:03 am
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]

Posted: Thu Mar 31, 2005 12:16 pm
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

Re: MySQL Insert w/FOR loop.

Posted: Thu Mar 31, 2005 12:28 pm
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.

Posted: Thu Mar 31, 2005 12:39 pm
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.

Re: MySQL Insert w/FOR loop.

Posted: Thu Mar 31, 2005 12:49 pm
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.

Posted: Thu Mar 31, 2005 12:51 pm
by conscience
Noted and appreciated.

Posted: Thu Mar 31, 2005 1:17 pm
by conscience
For the record, I get the following error:

Warning: Invalid argument supplied for foreach()

Posted: Thu Mar 31, 2005 1:21 pm
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.

Posted: Thu Mar 31, 2005 1:26 pm
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

Posted: Fri Apr 01, 2005 7:48 am
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?

Posted: Fri Apr 01, 2005 8:04 am
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]

Posted: Fri Apr 01, 2005 1:37 pm
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