php and mysql help

[mrooks1984]

hello all, i am currently trying to figure out how to do multiple mysql querys, the code i have sort of works.

if displays the correct information, but doesent loop again (only shows the first result.

 

heres the code:

class Cart extends CMS {

function view() {

	$session_id = session_id();

	$query1 = mysql_query("SELECT * FROM store_cart WHERE customer='$session_id'");
	$query2 = mysql_query("SELECT * FROM store_options");

	if (0 === mysql_num_rows($query1)) {

	}else{	

		while ($row1 = mysql_fetch_assoc($query1)) {	
		while ($row2 = mysql_fetch_assoc($query2)) {

			echo '<h1>' . $row1['product'] . '</h1>';

			if ($row1['product'] == $row2['product']) {

				if ($row1['option1'] != 'NULL') {

					echo '<p>' . $row2['title1'] . ": " . $row1['option1'] . '</p>';						
				}

				if ($row1['option2'] != 'NULL') {

					echo '<p>' . $row2['title2'] . ": " . $row1['option2'] . '</p>';						
				}

				if ($row1['option3'] != 'NULL') {

					echo '<p>' . $row2['title3'] . ": " . $row1['option3'] . '</p>';						
				}

				if ($row1['option4'] != 'NULL') {

					echo '<p>' . $row2['title4'] . ": " . $row1['option4'] . '</p>';						
				}

				if ($row1['option5'] != 'NULL') {


					echo '<p>' . $row2['title5'] . ": " . $row1['option5'] . '</p>';						
					}
				}
			}	
		}
	}
}
}

 

 

hoping someone can help me, i am still learning php so any answers please answer as simple as possible. thanks all.

 

 

[Christian F.]

I strongly recommend reading up on LEFT JOIN, and how to use it.

From what I gather of your code, you're trying to list up all of the options for all of the cart items of the current user. This is exactly what JOINs are made for, and which will allow you to pull all the relevant data from the database in a single query¹.

 

If you look at a previous post I made, you'll find a nice example on how you can do what you're looking to do here.

 

PS: Remember that the less code you use to achieve what you want, generally the better it is. You want to keep things simple, and avoid repeating patterns of code. Your if-tests and echos are prime examples of repeated patterns which could have been merged, using variables for the tiny bits that differ between them.

 

¹Database queries are quite expensive time-wise, so most of the time you really want to avoid using them in a loop.

[Barand]

Your code reads the first result from query1 then reads all the rows from query2. When it then reads the second result from query1 there are no rows remaining in query 2 so that inner loop exits. immediately.

 

I notice that you have $row1['option1'], ..., $row1['option5'] which indicated bad (unnormalized) table design. Rectify the design and use JOINS instead of resource-eating nested loops.

 

As you use "SELECT * ", another bad habit, I can't give much advice on your table design since there is no way of knowing their current contents. But you should move those options into another table, one option per row.

[mrooks1984]

thanks for getting back to me, the reason i have the two tables is one stores all the information for different titles of options or variants per product so i have upto 5 different ones per product e.g. size, colour etc, the variants them self's are then stored in a different table for the title and values for the drop down menu then linked to a certain product. the other one stores all values selected from the drop down menu into a database with a customer id along with total price calculated depending on options. i tried doing this using session array for a solid month, but just couldn't get it to work the way i can with the database way.

i have tidied the code down a lot more then it was lol, but this was the best i could figure out.

the code i put in is getting the information from the cart table once finished will get price, each product name  and option one to five if it was selected.

the problem i noticed is i then need to get the title of the variants do display next to each option, put thats stored in the other table, so the only way i could figure out how to get this doing it the way i did.

i attach screenshots of the database to give you a idea.

i will try get my head around the left join, if anyone has other ideas or anything please let me know

thanks again

 

 

i am grateful for you inputs i am hoping to get it working and then keep coming back it and making the code better as i learn more.

[mrooks1984]

hi christian, could you give me a example of what you mean here please: if-tests and echos are prime examples of repeated patterns which could have been merged, using variables for the tiny bits that differ between them

had a look at the link and not sure what it means, so i have to look all over google and get my head around it, do you know any good links that i could read. thanks again.

[Christian F.]

I'm referring to these blocks, and their siblings:

					if ($row1['option1'] != 'NULL') {

					echo '<p>' . $row2['title1'] . ": " . $row1['option1'] . '</p>';						
				}

				if ($row1['option2'] != 'NULL') {

					echo '<p>' . $row2['title2'] . ": " . $row1['option2'] . '</p>';						
				}

As you see very little code distinguishes them, only the number which is used in three locations. That means you can roll them all together, and exchange the number with a variable. Like this:

// Run through all 5 rows, and add them to output if they contain data.
for ($run = 1; $run <= 5; $run++) {
if ($row1['option'.$run] != 'NULL') {
	$output .= '<p>' . $row2['title'.$run] . ": " . $row1['option'.$run] . "</p>\n";
}
}

 

As for tutorials on how to use JOIN in SQL sentences, this seems to be a good one:

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

[Barand]

Keith J Brown seems to have a fixation on left joins. As they are much slower than inner joins, use only when necessary.

[mrooks1984]

thanks very much ChristianF you are the man!, i now know the basics and changed your code abit to work with the new sql code.

the only thing i couldn't get working is the $location .= coming up with unidentified variable if i remember rightly and wasent sure how to actually tell it to display the results so just changed that bit to a echo and it all works a treat, i looked at the article wasent sure about some of it so looked for another one and found this: http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php/ which is helping me get my head around it.

again thanks very much for all your help.

 

this is what i have ended with:

 

function view() {

	$session_id = session_id();

	$query = mysql_query("SELECT * FROM store_cart LEFT JOIN store_options ON store_cart.product = store_options.product WHERE store_cart.customer='$session_id'");

	if (0 === mysql_num_rows($query)) {

	}else{	

		while ($row = mysql_fetch_assoc($query)) {	

			echo '<h1>' . $row['product'] . '</h1>';

			//Run through all 5 rows and add them to the output if they contain data.			
			for ($run = 1; $run <= 5; $run++) {

				if ($row['option'.$run] != 'NULL') {

					echo '<p>' . $row['title'.$run] . ": " . $row['option'.$run] . "</p>\n";

				}
			}
		}
	}
}