FOR Loops -> SQL Query. Pain. Agony.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

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

FOR Loops -> SQL Query. Pain. Agony.

Post by conscience »

Still trying to generate a SQL query using the code below. I realise this outputs an extra comma at the end of the field list, and I can't seem to grok its destruction. Also, this returns "1" for the $icols and $qcols arrays, even though I think I've exploded the string values correctly and there's more than one value in the $row[5] and $row[3] variables. For the record, it returns the LAST value in each of those variables instead of the first, which I find wierd for some reason.

This is probably extremely poorly written, and easy to solve. Please help end my suffering. Physical death or code solution welcomed. Next-gen multi-table database structure is in the works, but I need a basic working solution first. Thanks for any assistance you can provide.

Code: Select all

$icols = array();
$qcols = array();
$BigBrother = "INSERT INTO orderhistory (datetime, legalname, title, degree, body, email, phone, address, city, state, zip, totalcost, paymethod,";
foreach(explode(',', $row[5]) as $citem) {
	$icols[] = $citem; }
foreach(explode(',', $row[3]) as $cqty) {
	$qcols[] = $cqty; }	
for($n = 1; $n <= count($icols); $n++) {
	$BigBrother .= " item" . $n . ","; }
for($q = 1; $q <= count($qcols); $q++) {
	$BigBrother .= " qty" . $q . ","; }
$BigBrother .= ") VALUES ('$date', '$name', '$title', '$degree', '$body', '$email', '$phone', '$addr";
if($_POST["addr2"] !== "") {
	$BigBrother .= " $addr2', '$city', '$state', '$zip', '$total', '$method'"; }
else {
$BigBrother .= "', '$city', '$state', '$zip', '$total', '$method'"; }
foreach(explode(',', $row[5]) as $iname) {
	$BigBrother .= ", '$iname'"; }	
foreach(explode(',', $row[3]) as $qty) {
	$BigBrother .= ", '$qty'"; }
$BigBrother .= ")";
User avatar
SystemWisdom
Forum Commoner
Posts: 69
Joined: Sat Mar 26, 2005 5:54 pm
Location: A Canadian South of the 49th Parallel

Re: FOR Loops -> SQL Query. Pain. Agony.

Post by SystemWisdom »

conscience wrote:Still trying to generate a SQL query using the code below. I realise this outputs an extra comma at the end of the field list, and I can't seem to grok its destruction.
Logically what you are doing is concatenating the field then a comma, but since you query is already started you should be concatenating the comma then the field, follow me? That way your query string always ends with a field name, not a comma.
conscience wrote: Also, this returns "1" for the $icols and $qcols arrays, even though I think I've exploded the string values correctly and there's more than one value in the $row[5] and $row[3] variables. For the record, it returns the LAST value in each of those variables instead of the first, which I find wierd for some reason.
This:

Code: Select all

$icols = array();
foreach(explode(',', $row[5]) as $citem) {
    $icols[] = $citem; }
Should be:

Code: Select all

$icols = array();
$icols = explode(',', $row[5])
Anyway, I don't know what is in your $row variables from the code you posted, but here is an example of what I meant above:

Code: Select all

$icols = array();
$qcols = array();
$icols = explode(',', $row[5]);
$qcols = explode(',', $row[3]);

$BigBrother = "INSERT INTO orderhistory (datetime, legalname, title, degree, body, email, phone, address, city, state, zip, totalcost, paymethod";

for($n = 1; $n <= count($icols); $n++) {
	$BigBrother .= ", item" . $n; }

for($q = 1; $q <= count($qcols); $q++) {
	$BigBrother .= ", qty" . $q; }

$BigBrother .= ") VALUES ('$date', '$name', '$title', '$degree', '$body', '$email', '$phone', '$addr";

if($_POST["addr2"] !== "") {
	$BigBrother .= " $addr2', '$city', '$state', '$zip', '$total', '$method'"; }
else {
    $BigBrother .= "', '$city', '$state', '$zip', '$total', '$method'"; }

foreach($icols  as $iname) {
	$BigBrother .= ", '$iname'"; }	

foreach($qcols as $qty) {
	$BigBrother .= ", '$qty'"; }

$BigBrother .= ");";
I hope that helps!
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Re: FOR Loops -> SQL Query. Pain. Agony.

Post by conscience »

SystemWisdom wrote:

Code: Select all

$icols = explode(',', $row[5]);
$qcols = explode(',', $row[3]);
I hope that helps!
It does indeed. I will have your children and fan you with palm fronds. However, there's an additional issue.

Code: Select all

print_r($icols);
print_r($qcols);
I only get the last values in each array. The FOR loops count the values in the arrays correctly, but when we're doing the FOREACH loops I only get the final values. Odd. Suggestions?

/just enough knowledge to be annoying.
User avatar
SystemWisdom
Forum Commoner
Posts: 69
Joined: Sat Mar 26, 2005 5:54 pm
Location: A Canadian South of the 49th Parallel

Post by SystemWisdom »

Can you show us the string inside your $row variables?

Also, the foreach construct creates copies of all the values in the array, whereas a for loop works on the actual array itself, maybe try using a for loop instead?
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Post by conscience »

SystemWisdom wrote:Can you show us the string inside your $row variables?

Code: Select all

Array ( [0] => White Gluten ) Array ( [0] => 5 )
Should be:

Code: Select all

Array ( [0] => III Test [1] => Spunk [2] => White Gluten ) Array ( [0] => 4 [1] => 1 [2] => 5 )
The full array as current, missing the two previous values:

Code: Select all

Array ( [0] => 77 [cartId] => 77 [1] => 2a690aea4b2eab93366e1c6e09749642 [cookieId] => 2a690aea4b2eab93366e1c6e09749642 [2] => 21 [itemId] => 21 [3] => 5 [qty] => 5 [4] => 21 [5] => White Gluten [itemName] => White Gluten [6] => IX [itemDegree] => IX [7] => The special ingredient. [itemDesc] => The special ingredient. [8] => 22.00 [itemPrice] => 22.00 [9] => closer_sm.jpg [itemThumb] => closer_sm.jpg [10] => closer.jpg [itemPic] => closer.jpg [11] => [itemLevel] => )
Can't seem to get it to actually print the result of the SQL query without returning a resource ID#.
SystemWisdom wrote:Also, the foreach construct creates copies of all the values in the array, whereas a for loop works on the actual array itself, maybe try using a for loop instead?
Can you show me the logic on this? I thought FOR loops only returned numeric values for use in incrementation.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

queries return a resource identifier for selects. You must get the data yourself using a fetch function. An insert or update returns a true/false for success/fail.
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Post by conscience »

feyd wrote:queries return a resource identifier for selects. You must get the data yourself using a fetch function. An insert or update returns a true/false for success/fail.
At the moment I seem to be incapable of creating a fetch that returns the entire query, but I've managed to display the first and last values in the set. I know the data is there.

/dumber every post.
User avatar
SystemWisdom
Forum Commoner
Posts: 69
Joined: Sat Mar 26, 2005 5:54 pm
Location: A Canadian South of the 49th Parallel

Post by SystemWisdom »

conscience wrote:
SystemWisdom wrote:Can you show us the string inside your $row variables?
[...]

Code: Select all

Array ( [0] => III Test [1] => Spunk [2] => White Gluten ) Array ( [0] => 4 [1] => 1 [2] => 5 )
[...]
Are you saying that the $row variables are arrays? Not strings? They should be strings if you want to explode it into an array, if they are already arrays then you dont need explode()..

I don't think I can grasp what it is you're doing from what you've posted, so it's hard to help.. I am wondering where the $row variables came from, and what they are.. hard to manipulate data if you don't know what it is!!
conscience wrote: Can you show me the logic on this? I thought FOR loops only returned numeric values for use in incrementation.
You are already using FOR loops here:

Code: Select all

for($q = 1; $q <= count($qcols); $q++) {
    $BigBrother .= ", qty" . $q; }
But then you started using foreach() loops here:

Code: Select all

foreach($qcols as $qty) {
    $BigBrother .= ", '$qty'"; }
So you could do the exact same thing manually like:

Code: Select all

for( $i = 0; $i < count($qcols); $i++ )
    $BigBrother .= ", '$qcols[$i]'";
Now the main difference between the way those two methods work is that the foreach() construct creates copies of the values in the original array, whereas the for() construct is capable of manipulating the array values themselves.. Though, this may not make any difference until you start dealing with arrays of objects..

Also, the for() construct is most useful for integer indexed arrays.. foreach() is most useful for associative arrays..
conscience wrote: At the moment I seem to be incapable of creating a fetch that returns the entire query, but I've managed to display the first and last values in the set. I know the data is there.
Show us your query code, where you are passing that query string to the database (not the DB connection tho) and how you are trying to retrieve results.. maybe even the DB table structure..

I hope that helps a bit!
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Post by conscience »

**Long-winded post warning**
SystemWisdom wrote: Are you saying that the $row variables are arrays? Not strings? They should be strings if you want to explode it into an array, if they are already arrays then you dont need explode()..
I do apologise for the confusion and the lack of data. I got the full fetch display to work at some point a few weeks ago when I was debugging the array content, and unwisely deleted it instead of commenting it out.. which I'll never do again. Each $row is an array of values. Maybe I just don't grok whether I'm supposed to see the values of every array or not if I output the data fetch.
SystemWisdom wrote:hard to manipulate data if you don't know what it is!!
I know. I feel totally lame. My learning curve for this project has been more of a learning right-angle, so I'm really good in spots and absolutely horrible in others.
SystemWisdom wrote: So you could do the exact same thing manually like:

Code: Select all

for( $i = 0; $i < count($qcols); $i++ )
    $BigBrother .= ", '$qcols[$i]'";
Right. Brilliant.
SystemWisdom wrote: Though, this may not make any difference until you start dealing with arrays of objects..
I only need to manipulate the values in one location, here, for the express purpose of a database insertion. This occurs at the end of the entire process. Thankfully.
conscience wrote: Show us your query code, where you are passing that query string to the database (not the DB connection tho) and how you are trying to retrieve results.. maybe even the DB table structure..
OrderHistory structure (INSERT destination):

Code: Select all

Field  	        Type     	Null  	Default
itemId  	int(11) 	No  	 
datetime  	varchar(20) 	Yes  	NULL 
legalname  	varchar(50) 	Yes  	NULL 
title    	varchar(50) 	Yes  	NULL 
degree  	varchar(10) 	Yes  	NULL 
body  	        varchar(60) 	Yes  	NULL 
email    	varchar(30) 	Yes  	NULL 
phone    	varchar(15) 	Yes  	NULL 
address  	varchar(75) 	Yes  	NULL 
address2  	varchar(75) 	Yes  	NULL 
city  	        varchar(50) 	Yes  	NULL 
state     	char(2) 	Yes  	NULL 
zip  	        varchar(10) 	Yes  	NULL 
totalcost  	decimal(8,2) 	Yes  	NULL 
paymethod  	varchar(15) 	Yes  	NULL 
qty0  	        varchar(5) 	Yes  	NULL 
item0     	varchar(30) 	Yes  	NULL 

//qty and item fields go up to qty15/item15 until I upgrade the structure.

Indexes:
Keyname 	Type 	Cardinality 	Field
PRIMARY 	PRIMARY 	0  	itemId
id 	        UNIQUE  	0  	itemId
Data Fetch:

Code: Select all

$getItems = "SELECT * FROM cart INNER JOIN items ON cart.itemId = items.itemId WHERE cart.cookieId = '" . GetCartId() . "' ORDER BY items.itemName ASC";
$ItemSet = mysql_query($getItems) or die(mysql_error());
while($row = mysql_fetch_array($ItemSet)) {

//SQL query generation code goes here.

}
The SQL query contains the names of each of the $_POST variables I'm trying to import ($name, $body, etc.) with the items concatenated, named as "$_POST['qty']" and "$_POST['itemName']". I refer to them by number (3 and 5) since its less typing.

GetCardId() returns the SesssionID after some validation.

Items table:

Code: Select all

Field   	Type    	Null  	Default
itemId  	int(11) 	No  	 
itemName  	varchar(50) 	Yes  	NULL 
itemDegree  	varchar(10) 	Yes  	NULL 
itemDesc  	varchar(250) 	Yes  	NULL 
itemPrice  	decimal(4,2) 	Yes  	NULL 
itemThumb  	varchar(100) 	Yes  	NULL 
itemPic  	varchar(100) 	Yes  	NULL 
itemLevel  	varchar(5) 	Yes  	NULL 

Indexes:
Keyname 	Type 	Cardinality 	Field
PRIMARY 	PRIMARY 	21  	itemId
id 	        UNIQUE  	21  	itemId
Cart table:

Code: Select all

Field    	Type    	Null  	Default
cartId  	int(11) 	No  	 
cookieId  	varchar(50) 	Yes  	NULL 
itemId  	int(11) 	Yes  	NULL 
qty     	int(11) 	Yes  	NULL 

Indexes:
Keyname 	Type 	Cardinality 	Field
PRIMARY 	PRIMARY 	61  	cartId
id 	        UNIQUE  	61  	cartId
SystemWisdom wrote:I hope that helps a bit!
I really feel that I owe you something in exchange for your assistance here. This is tedious and annoying, I know, and I'm extremely grateful for your altruism. Let me know if I can PayPal you $10 for a six-pack of microbrews or something for your time.
User avatar
SystemWisdom
Forum Commoner
Posts: 69
Joined: Sat Mar 26, 2005 5:54 pm
Location: A Canadian South of the 49th Parallel

Post by SystemWisdom »

Hmm.. I still don't know what is inside your $row variables.. is it a string with comma seperated values?
I am guessing you got the value from the DB, but what is the value? Maybe echo the value of $row[3] and $row[5] so we can see what is in them before trying to manipulate them?

I understand $row is an associative array, but what is the value of $row[3] or $row[5]??

Also, looping through all the rows in a record set should be something like:

Code: Select all

$getItems = "SELECT * FROM cart INNER JOIN items ON cart.itemId = items.itemId WHERE cart.cookieId = '" . GetCartId() . "' ORDER BY items.itemName ASC";
$ItemSet = mysql_query($getItems) or die(mysql_error());
$iRowCount = @mysql_num_rows( $ItemSet );
for( $i = 0; $i < $iRowCount; $i++ )
{
    mysql_data_seek( $ItemSet, $i );
    $row = @mysql_fetch_array( $ItemSet );
 
    //SQL query generation code goes here.
}
@mysql_free_result( $ItemSet );
I've never seen it done the way you had it, so I don't know if it actually works or not.. but I would guess not..
conscience wrote: I really feel that I owe you something in exchange for your assistance here. This is tedious and annoying, I know, and I'm extremely grateful for your altruism. Let me know if I can PayPal you $10 for a six-pack of microbrews or something for your time.
Nah, all good.. This is a help forum, so I am just glad if I can help.. Your offer is appreciated though! :D
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Post by conscience »

SystemWisdom wrote:I am guessing you got the value from the DB, but what is the value?
A single string.
SystemWisdom wrote:I understand $row is an associative array, but what is the value of $row[3] or $row[5]??
$row[3] is a numeric value ('3', '15', etc.). $row[5] is a text string ('Box', 'Disc', etc.).
SystemWisdom wrote:Nah, all good.. This is a help forum, so I am just glad if I can help.. Your offer is appreciated though! :D
Let me know if you change your mind. "One good turn" and all.
User avatar
SystemWisdom
Forum Commoner
Posts: 69
Joined: Sat Mar 26, 2005 5:54 pm
Location: A Canadian South of the 49th Parallel

Post by SystemWisdom »

conscience wrote:
SystemWisdom wrote:I am guessing you got the value from the DB, but what is the value?
A single string.

[...]

$row[5] is a text string ('Box', 'Disc', etc.).
So, is it more like:

Code: Select all

$row[5] = 'Box';
Or Like:

Code: Select all

$row[5] = array('Box', 'Disc', 'etc.');
If it is like the first one, then I think the logic is all worng.. but if it is like the second one then it is jsut minor errors I think.. Sorry, I'm just having trouble understanding what the $row variable is exactly..
conscience wrote:
SystemWisdom wrote:Nah, all good.. This is a help forum, so I am just glad if I can help.. Your offer is appreciated though! :D
Let me know if you change your mind. "One good turn" and all.
Actually, I don't have a PayPal account.. :(
Free help is all I can offer :lol:
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Post by conscience »

SystemWisdom wrote:I am guessing you got the value from the DB, but what is the value?
A single string.
SystemWisdom wrote: If it is like the first one, then I think the logic is all worng.. but if it is like the second one then it is jsut minor errors I think.. Sorry, I'm just having trouble understanding what the $row variable is exactly..
No worries. I think it's actually more like the first. I just found a copy of the full fetch output from a couple months ago, and it appears that a separate array is generated for each row returned. Does that help us out?
SystemWisdom wrote:Free help is all I can offer :lol:
Fine. Be that way. ;)
conscience
Forum Commoner
Posts: 33
Joined: Mon Dec 27, 2004 12:34 pm

Post by conscience »

Okay, I got the values to output in a very kludgy fashion, but one that proves their existence.

Code: Select all

$flan = "";
$gloogh = "";

while($row = mysql_fetch_array($ItemSet)) {

$flan .= $row[3];
$gloogh .= $row[5];
global $flan;
global $gloogh

}

print_r($flan);
print_r($gloogh);
This spits everything out:

"415III TestSpunkWhite Gluten"

Further supporting the one value per array set theory.
User avatar
SystemWisdom
Forum Commoner
Posts: 69
Joined: Sat Mar 26, 2005 5:54 pm
Location: A Canadian South of the 49th Parallel

Post by SystemWisdom »

Okay, so in the table you have a possible 15 item# and qty# fields right?
And you want to loop through them to make an SQL string according to how many there are?

maybe something like:

Code: Select all

$getItems = "SELECT * FROM cart INNER JOIN items ON cart.itemId = items.itemId WHERE cart.cookieId = '" . GetCartId() . "' ORDER BY items.itemName ASC";

$ItemSet = mysql_query($getItems) or die(mysql_error());

$BigBrother = "INSERT INTO orderhistory (datetime, legalname, title, degree, body, email, phone, address, city, state, zip, totalcost, paymethod";

$iRowCount = @mysql_num_rows( $ItemSet );

for($n = 1; $n <= iRowCount; $n++) {
    $BigBrother .= ", item" . $n; }
 
for($q = 1; $q <= iRowCount; $q++) {
    $BigBrother .= ", qty" . $q; }

$BigBrother .= ") VALUES ('$date', '$name', '$title', '$degree', '$body', '$email', '$phone', '$addr";
 
if($_POST["addr2"] !== "") {
    $BigBrother .= " $addr2', '$city', '$state', '$zip', '$total', '$method'"; }
else {
    $BigBrother .= "', '$city', '$state', '$zip', '$total', '$method'"; }


$icols = array();
$qcols = array();
for( $i = 0; $i < $iRowCount; $i++ )
{
    mysql_data_seek( $ItemSet, $i );
    $row = @mysql_fetch_array( $ItemSet );

    $icols[] = $row[5];
    $qcols[] = $row[3];
}
@mysql_free_result( $ItemSet );

foreach($icols as $iname) {
    $BigBrother .= ", '$iname'"; }    
 
foreach($qcols as $qty) {
    $BigBrother .= ", '$qty'"; }
 
$BigBrother .= ");";
Post Reply