mysql query not finding any results

[mrooks1984]

hello all, i am hoping someone can help me, i have the following code and check the table a few tims and it should be finding results, so it must be the code.

 

		//run 5 times
		for ($run = 1; $run <= 5; $run++) {

			//Get Option Names
			$sql = "SELECT * FROM store_variations WHERE product = '$product' AND variation = '$run'";
			$res = mysql_query($sql) or die(mysql_error());
			if (0 === mysql_num_rows($res)) {
			echo "another issue";
			//Make the option names and prices blank
			${'option'.$run.'_title'} = "";
			${'option'.$run.'_price'} = "0.00";

			} else {				
				while ($row = mysql_fetch_assoc($res)) {
					${'price'.$run} = $row['price'];
					${'option'.$run.'_title'} = $row['title'];
				}//end of while
			}//end of else
			//If options are set get result
			if (isset($_POST['option'.$run]) && $_POST['option'.$run] != "") {
				${'option'.$run.'_value'} = ${'option'.$run.'_title'};	
			}//end of options if
		}//end of run

 

many thanks all.

[Christian F.]

A couple of questions:

1. [*]Where is

$product defined?

[*]Have you tried dumping the contents of $row?

[*]Do you get any errors, and if so what errors do you get?

[*]Do you have error reporting turned on, and showing all errors?

[mrooks1984]

hi, thanks for your responce.  I have done echos for the varibles out and seem to be fine, i think its just the query keeps defaulting to the num rows 0. i will have a look for code to tell it to show all errors, but i am show xampp does this out of the box with out needing this, but will look.

i will try dumping out the $row but i think it will be empty due to it defaulting to the num rows.

i am not getting any errors at all.

 

product varible is below

	//Import product varible
	global $product;
	$product = mysql_real_escape_string($product);

[mrooks1984]

i have now attached the full class and screenshots of the table, in case i have missed something.

i need to tidy up some of the code and querys but just trying to get it working first.

 

full class

function add_product() {

	//Get Date
	$date = date("Y/m/d");

	//Gets Required Form Information
	$product = $_POST['name'];
	$customer = session_id();

	//Setup Varibles
	$price = '0.00';
	$option_price = '0.00';

	//start options blank
	$option1_value = "";		
	$option2_value = "";	
	$option3_value = "";
	$option4_value = "";	
	$option5_value = "";						

	//Connect to database and get the required product price.
	$result = mysql_query("SELECT price FROM store_products WHERE name = '$product' ORDER BY id");
	while ($row = mysql_fetch_array($result)) {

		//Give results a varible		
		$price = $row['price'];
	}
		//run 5 times
		for ($run = 1; $run <= 5; $run++) {

			//Get Option Names
			$sql = "SELECT * FROM store_variations WHERE product = '$product' AND variation = '$run'";
			$res = mysql_query($sql) or die(mysql_error());
			if (0 === mysql_num_rows($res)) {
			echo "another issue";
			//Make the option names and prices blank
			${'option'.$run.'_title'} = "";
			${'option'.$run.'_price'} = "0.00";

			} else {				
				while ($row = mysql_fetch_assoc($res)) {
					${'price'.$run} = $row['price'];
					${'option'.$run.'_title'} = $row['title'];
				}//end of while
			}//end of else
			//If options are set get result
			if (isset($_POST['option'.$run]) && $_POST['option'.$run] != "") {
				${'option'.$run.'_value'} = ${'option'.$run.'_title'};	
			}//end of options if
		}//end of run

		if ($option1_value != "") {
			$option_price = $option_price + $price1;
		}

		if ($option2_value != "") {
			$option_price = $option_price + $price2;
		}

		if ($option3_value != "") {
			$option_price = $option_price + $price3;
		}

		if ($option4_value != "") {
			$option_price = $option_price + $price4;
		}

		if ($option5_value != "") {
			$option_price = $option_price + $price5;
		}						

		//run 5 times
		for ($run = 1; $run <= 5; $run++) {	

		if (${'option'.$run.'_value'} != "") {

			//Run option price query										
			$sql = "SELECT * FROM store_variations_values WHERE product = '$product' AND title = '${'option'.$run.'_value'}'";
			$res = mysql_query($sql) or die(mysql_error());
			if (0 === mysql_num_rows($res)) {
				echo "Heres the issue";	
			}else{
				while ($row = mysql_fetch_assoc($res)) {

					${'price'.$run} = $row['price'];
					$option_price = $option_price + ${'price'.$run};
				}//end of else
			}//end of while
		}//end of if

		if (isset($_POST['option'.$run]) && $_POST['option'.$run] != "") {
		${'option'.$run.'_value'} = ${'option'.$run.'_title'}.": ".$_POST['option'.$run];
		}//end of options if
	}//end of run

	//Add product price to total option price and give it the price varible
	$price = $price + $option_price;

	//Insert into the database
	mysql_query("INSERT INTO store_cart (product, option1, option2, option3, option4, option5, customer, price, date)
		VALUES ('$product','$option1_value','$option2_value','$option3_value','$option4_value', '$option5_value','$customer','$price', '$date')");
	//Tell customer the product has been added.
	echo $option1_value." ".$product." oh yea";
	echo '<p>' . "Product Added To Basket" . '</p>';
}//Ends Add product function

if i change the query to the following it works:

$sql = "SELECT * FROM store_variations WHERE product = '$product' AND variation = '1'";

 

its very strange

thanks again, i just dont know were i am going wrong

[cpd]

If you're getting an empty result set its because the "WHERE" clause matches nothing in the database. First confirm if $_POST['name'] actually contains the expected value, my suspicion is it doesn't.

 

 

[mrooks1984]

hi thanks for your answer, it seems that one is fine its caused by the $run one as soon as i replace it with a one its fine

[cpd]

You've got to understand the query and its syntax are completely valid. Your issue does not lie with your code it lies with what your actually asking the DBMS for.

 

For example. When you say $product = 'Coffee' your asking the database for all results where the product field has "Coffee" and the variation field has a 1 in it - this will return a single row from the images provided.

If you have $product = 'Tea' your asking the database for all results where the product field has "Tea" and variation field has a 1 in it - this returns no data because there are no rows with "Tea" in it, the fact there are rows with variation = 1 is now irrelevant because the first criteria did not match.

 

If however you said "WHERE product = 'Tea' OR variation = 1" you would have received 1 result again because there is a single row with variation = 1. The two criteria are independent of each other because you've replaced the AND with an OR.

 

This explains why you receive no errors because the query is actually valid but there are no matches for what your asking so you get a result set with 0 records.

 

Lesson is when using AND the criteria are dependent on each other unlike OR which makes them independent. An easy way to visually represent this is with parenthesis.

 

$sql = "SELECT * FROM `test_tbl` WHERE (`field1` = 'Val1' AND `field2` = 'Val2) OR (`field3` = 'Val3' AND `field4` = 'Val4') OR `anythingField` = 'anything'"

[PFMaBiSmAd]

Here's a different slant on the problem - You should never need to run SELECT queries inside of loops. You should also never have a series of name/numbered variables. Using variable variables takes more (and 3x slower) code to accomplish anything. You should be using arrays for sets for data.

 

In general, you should have ONE query that gets all the rows you want in the order that you want them. You then simply loop over the rows and output the data the way you want.

 

 

[mrooks1984]

hi, ok thanks for letting me know, now i know it cant be done like that i rethink the code. short term arrays are out of the question i dont fully understand them so i have a think of another way, thanks again.

[PFMaBiSmAd]

I've been looking at your code, trying to figure out what it is trying to do. There's so much extra code that its got a - "cannot see the forest for the trees" problem. Some questions and comments -

 

1) Are the $_POST['option1'], $_POST['option2'], .... values simply set/not-set or are they a quantity of any particular option?

 

2) Using a HTML array for the above option fields will allow you to have any number of options and the code will be simpler because you will be able to use php array functions, such as a foreach(){} loop, to operate on the data.

 

3) What does the store_variations_values table do? Your code is already getting the price for each option from the store_variations table, so I don't see the point of the last block of code with the SELECT query for the store_variations_values table?

 

4) You have $option1_price,2,3,4,5 and $price1, 2,3,4,5 variables floating around. It appears that those were intended to be all just $option1_price,2,3,4,5

 

Arrays are for sets of data, where you will be processing each value in the same way. I'll try to post a version of code using arrays that shows how simplified it can be (probably about 1/5 the amount of code.)

[mrooks1984]

1. the post option1 etc etc depend if the option in the form is set

2. if i can do it that way that would be great and by the sound of it very useful, i have seen a few arrays in the past but never got my head around them fully, maybe just never seen a good example. i am hoping to change it so not limited to 5 options in the future

3. the store variation values are the seperate values for each drop down menu like pink green etc and price in there is for setting prices in for each value if wanted, the store variation table is for the titles of the dropdown menus and price of all values in that dropdown menu again if wanted. 

4. both price varibles are for the drowndown menu price or the dropdown menu value prices if any are set

 

thanks again.

[PFMaBiSmAd]

Here's an example showing how the resulting form would look using an array name for the select fields and how you can loop over the submitted data in the php code -

 

<form method='post'>
Coffee: 
Colours:
<select name='option[1]'>
<option value=''>Make your selection:</option>
<option value='Black'>Black</option>
<option value='White'>White</option>
</select>
Test:
<select name='option[2]'>
<option value=''>Make your selection:</option>
<option value='Bacon'>Bacon</option>
</select>
<input type='submit'>
</form>
<?php
echo '<pre>',print_r($_POST,true);

foreach($_POST['option'] as $key => $value){
if($value !=''){
	echo "Option $key is $value<br />";
} else {
	echo "Option $key is empty<br />";
}
}

[mrooks1984]

thanks very much for your post, just got 3 questions

 

1. how would i add extra values into the array and get it to show in the for each statement

2. how would i get a mysql query to add into the query

3. echo '<pre>',print_r($_POST,true); could you tell me what this line is doing, i know its printing the array but not sure what the post and true for or meaning.

 

thanks again

[PFMaBiSmAd]

#1 - I'm not sure what that refers to. If you are asking about producing the form, I'm going to assume that you have some php code now that is producing your existing form. The only real change to switch to using an array name for the <select> field would be to change the php code that is producing the name='...' attribute from making it name='optionx' to name='option[x]'

 

#2 - I have no idea what you are asking.

 

#3 - the echo statement displays the $_POST data so that you can see exactly what is being submitted.

 

One last comment - you should be using the id value from your database table as the value='...' attribute in the <option> tags. Your current scheme of using the 'title' as the value, then putting that value into the SELECT query will prevent you from ever using the same title more than once under a single product. For example, if you added a second option after 'Bacon', of 'White', a query looking for 'White' would find one row from variation 1 and one from variation 2. By using the id, any query looking for a value would be unique.

[mrooks1984]

1. in other words if i desided i want to add for example price into the array as well as options, these  caused me to get got stuck in the past with arrays adding more then one thing in to it and results into them.

 

2. add results from a array e.g.

 

		$sql = "SELECT * FROM store_variations WHERE product = '$product'";
		$res = mysql_query($sql) or die(mysql_error());
		//If the options are not named
				while ($row = mysql_fetch_assoc($res)) {

$row['value_title'];

 

how would i also add the $row['value_title'] into the array as well.

 

thanks for the heads up on the options naming i will have to do that using ids like you suggested, when i had the to while statements before i used titles as i dident think that each value for each product and dropdown menu would be the same, but now i am doing it this way i be getting all the results in one go from the query and adding them into the array, well thats what i am hoping to save on multiple querys.

thanks again

[cpd]

A quick explanation on arrays.

 

You can think of an array as many variables all wrapped up into one. For example:

 

<?php

$firstname1 = 'Joe';
$firstname2 = 'Tom';

echo $firstname1;
echo $firstname2;

// Can be easily simplified using an array

$names = array();
$names[] = 'Joe';
$names[] = 'Tome';

foreach($names as $name)
    echo $name;

?>

 

The [] on the end of the $names variable just increments the array index by 1. You could also do:

 

<?php

$names = array();
$names[0] = 'Joe';
$names[1] = 'Tome';

?>

 

This is almost the same but your setting the index manually.

 

You can also use strings as indexes, as you want to, but it often infers the need of a multidimensional array - or more easily put: arrays within arrays. So if we we're to use firstname and lastname

 

<?php

$names[] = array("firstname" => "Joe", "lastname" => "Blogs");
$names[] = array("firstname" => "Tom", "lastname" => "Finn");

// The names array can be printed out using var_dump or print_r producing something similar to...

array([0] = 
    array(
        "firstname" = "Joe",
        "lastname" = "blogs"
    ),
[1] = 
    array(
        "firstname" = "Tom",
        "lastname" = "Finn"
    )
)

?>

 

You've effectively built a structure you can then cycle through using the foreach($names as $name) syntax. There are a tone of other examples on the net you can search for - probably slightly easier to understand as well.

 

With regards to your specific question #2 I think you're actually asking 2 separate questions. 1) How do you add data to an array; 2) How can you take data input thats in the form of an array and add it to a query string. The first question is answered above. You can use $array[] = 'Value'; or $array['my_string_index'] = 'Value'. Similarly you can do $array[] = $row['field_name'] as the $row['field_name'] points to a value.

 

2. Building further on what PFMaBiSmAd said, the $_POST['option'] looks like:

 

$_POST['option'][1]
$_POST['option'][2]

 

So its multidimensional and he has cycled through the second dimension using a foreach loop with foreach($_POST['option']...). The $key => $value allows you to access both the index and value so in the first cycle $key = 1 and $value equals the selected option by the end user. In the second cycle $key = 2 and so on. You can then in the foreach loop append the $value to a query string.

[Christian F.]

Also, if you want to know what the "print_r ()" call does, then I recommend reading the manual (which I just linked to).

[mrooks1984]

thanks very much for your help, i am more aless there now and have a 3 arrays setup and getting information and working great.

i have one issue/question

heres the code i am working with:

	//Import product varible
	global $product;
	$product = mysql_real_escape_string($product);
	//convert product value into a title
	$value = str_replace("-"," ",$product);

	//Connect to database and get the required product.
	$result = mysql_query("SELECT * FROM store_products WHERE name = '$value' ORDER BY id");

	//If the product is not found in the database.
	if (mysql_num_rows($result) == 0) {

		//Display a message
		echo '<h1>' . "This product doesent exist" . '</h1>'  . "\n";

		//If products are found in database.
	} else {
		while ($row = mysql_fetch_array($result)) {

			$store_products[] = array("name" => $row['name'], "image" => $row['image'], "category" => $row['category'], "description"=> $row['description'], "price" => $row['price']);

		}

		foreach ($store_products as $store_product)

		//Get variations Names
		$sql = "SELECT * FROM store_variations WHERE product = '$product'";
		$res = mysql_query($sql) or die(mysql_error());
		//If the options are not named
		if (0 === mysql_num_rows($res)) {

		} else {

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

				$store_variations[] = array("title" => $row['title'], "variation" => $row['variation'], "price" => $row['price'], "product"=> $row['product']);

			}
			foreach ($store_variations as $store_variation)
			print_r($store_variations);
		}

		//Get variations values
		$sql = "SELECT * FROM store_variations_values WHERE product = '$product'";
		$res = mysql_query($sql) or die(mysql_error());
		//If the options are not named
		if (0 === mysql_num_rows($res)) {

		} else {

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

				$store_variations_values[] = array("title" => $row['title'], "variation" => $row['variation'], "price" => $row['price'], "product"=> $row['product']);

			}
			foreach ($store_variations_values as $store_variation_values)
			print_r($store_variations_values);
		}

		//Show Product form.

		echo '<div id ="StoreCol1">' . "\n";
		echo '<form method="post" action="">' . "\n";
		echo '<h1>' . $store_product['name'] . '</h1>' . "\n";
		echo '<input type="hidden" name="name" value="' . $store_product['name'] . '">' . "\n";
		echo '<a href="public/images/' . $store_product['image'] . '" target="_blank"><img src="public/images/' . $store_product['image'] . '" height="200" width="300" style="border:none;" /></a>' . "\n";
		echo '<p>' . $store_product['description'] . '</p>' . "\n";
		echo '<a href="store.php?category=' . $store_product['category'] . '">' . "Go Back" . '</a>' . "\n";
		echo '</div>' . "\n";
		echo '<div id="StoreCol2">' . "\n";
		echo '<p>' . "Any extra charges will be displayed and added in your cart." . '</p>' . "\n";
		echo '<p>' . '<b>' . "Price: £" . $store_product['price'] . '</b>' . "</p>" . "\n";
		echo '<p>' . "Excludes postage and packaging." . '</p>' . "\n";
		//Add options here

	}

 

as you can see i have the option section empty (at the bottom) some of the arrays have multiple options and i am trying to figure out how to make it

loop through the result in the code below until it runs out of results.

			echo '<select name = "option1" >' . "\n";
			echo '<option value= "'.$store_variation_values['title'].'">'.$store_variation_values['title'].'</option>' . "\n";

 

thanks again

[mrooks1984]

hi, i have done abit more and got this far:

 

		foreach ($store_variations as $store_variation) {
			foreach ($store_variations_values as $store_variation_values) {
				if ($store_variation_values['variation'] == $store_variation['variation']) {
				echo '<p>' . $store_variation['title'] . ": ";
				echo '<select name = "option'.$store_variation['variation'].'" >' . "\n";
				echo '<option value= "'.$store_variation_values['title'].'">'.$store_variation_values['title'].'</option>' . "\n";
				echo '</select></p>';
				echo '</form></div>';
				}
			}
		}

 

this code sort of works, it displays the title of the dropdown menus and the dropdown menus, the issue is i have 2 values for one of the variations and is showing 2 of the same dropdown menus just with the single different values in. i do know its because its going around multiple times on the foreach loop so its only creating the single value, but dont know how to fix this