Query problem

[MarkoSiroki]

Dear Sirs and Madams!

I am newbe at php programming and there I politly ask for little patience. I have following two lines of code:

Code: Select all

$query_products_id_numbers=mysql_query("SELECT id_product FROM ps_category_product WHERE id_category IN ($subcategories_ids)");
$number_of_products_id_numbers=mysql_num_rows($query_products_id_numbers);

In the second line, I get following error:

"Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in <b>getProducts.php on line 46"

What could be wrong? I use similiar statements in other scripts and they work fine!!

[Emy]

Code: Select all

    $query_products_id_numbers1 = "SELECT id_product FROM ps_category_product WHERE id_category IN ($subcategories_ids)";

echo $query_products_id_numbers1;

    $query_products_id_numbers = mysql_query($query_products_id_numbers1) OR DIE (mysql_error());

    $number_of_products_id_numbers=mysql_num_rows($query_products_id_numbers);

[Nullsig]

I assume that in your initial post your query was not resolving correctly.

In your second post do you see the mysql_error?

[MarkoSiroki]

This is whole script:

Code: Select all

<?php
	session_start();

	if($_SERVER["REQUEST_METHOD"]=="POST")
	{
        	$hostname_localhost ="test";
	        $database_localhost ="test";
	        $username_localhost ="test";
	        $password_localhost ="test";
	        $path_to_categories_images="/httpdocs/img/c/";
		$path_to_products_images="/httpdocs/img/p";

	        $localhost=mysql_connect($hostname_localhost,
                                         $username_localhost,
                                         $password_localhost) or die(mysql_error());

	        mysql_select_db($database_localhost) or die(mysql_error());

	        $id_category=$_POST['id_category'];

		// **** SELECTED CATEGORY SUBCATEGORIES FETCH
		$query_subcategories_id_numbers=mysql_query("SELECT id_category FROM ps_category WHERE id_parent=$id_category");
		$number_of_subcategories=mysql_num_rows($query_subcategories_id_numbers);
		$subcategories_id_numbers="";
		if($number_of_subcategories>0)
		{
			while($subcategories_id_numbers=mysql_fetch_assoc($query_subcategories_id_numbers))
			{
				$subcategories_id_numbers.=$subcategories_id_numbers['id_category'].",";
			}	// while

			$subcategories_id_numbers=substr($subcategories_id_numbers,
						  	 0,
						  	 strlen($subcategories_id_numbers)-1);
		}	// if
		// **** END OF SELECTED CATEGORY SUBCATEGORIES FETCH


		// **** SUBCATEGORIES PRODUCTS FETCH
	        $query_products_id_numbers=mysql_query("SELECT id_product FROM ps_category_product WHERE id_category IN ($subcategories_id_numbers)");
		print($query_products_id_numbers.": ".mysql_error());	// debug code
	 	$number_of_products_id_numbers=mysql_num_rows($query_products_id_numbers);
		$products_ids="";
		if($number_of_products_id_numbers>0)
		{
			while($products_ids=mysql_fetch_assoc($query_products_id_numbers))
			{
				$products_ids.=$products_ids['id_product'].",";
			}	// while
			$products_ids=substr($products_ids,
					     0,
					     strlen($products_ids)-1);

        	        $query_products_names=mysql_query("SELECT name FROM ps_product_lang WHERE id_product IN ($produtcs_ids) AND id_lang=1");
			$number_of_products=mysql_num_rows($query_products_names);
			if($number_of_products>0)
			{
				while($products=mysql_fetch_assoc($query_products_names))
				{
					$userdata[]=array("product_name" => $products['name']);
				}	// while
			}	// if

			print(json_encode($userdata));
			mysql_close();
		}	// if
        	// **** END OF SUBCATEGORIES PRODUCTS FETCH
	}	// if
?>

And this is error I get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in getProducts.php on line 42

[Nullsig]

There is something wrong with your query and as a result you don't have a populated result set. You should echo out the query and try executing it in your console to see if you can rectify the issue.

[ejsexton82]

Is there a problem in this statement?

Code: Select all

 while($subcategories_id_numbers=mysql_fetch_assoc($query_subcategories_id_numbers))
 {
	$subcategories_id_numbers.=$subcategories_id_numbers['id_category'].",";
 }   // while

In the while() statement, you set $subcategories_id_numbers equal to the result of mysql_fetch_assoc(), which should be an array. On the next line, you concatenate a value to $subcategories_id_numbers, which is supposedly an array. The process repeats for each loop iteration.

This may cause problems when $subcategories_id_numbers is used in an SQL query below:

Code: Select all

$query_products_id_numbers=mysql_query("SELECT id_product FROM ps_category_product WHERE id_category IN ($subcategories_id_numbers)");

[MarkoSiroki]

Well, in other 5 scripts I have same "approach" using while loop and it works fine. The problem is in following code of my script:

Code: Select all

                $query_products_id_numbers=mysql_query("SELECT id_product FROM ps_category_product WHERE id_category IN ($subcategories_id_numbers)");
                print($query_products_id_numbers.": ".mysql_error());   // debug code

The lower print function displays empty string for $query_products_id_numbers and mysql_error() returns:

": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

The line 1 from message is meant Line 1 of query, not line 1 of php script and I do not know where do I have syntax error in SQL statement. I've copied this query from php code directly onto server and I get results, so the query works fine. Since I am coding php script directly on server using non gui vi editor, should I check for some hidden chars? Or is it possible that php script does not have permissions to access table used in query?

Sincerely,
Marko

[MarkoSiroki]

Or maybe MySQL MyISAM db engine does not support SELECT WHERE IN combination?

Sincerely,
Marko

[Emy]

have u tried this?

[MarkoSiroki]

have u tried this?

Emy, thanks for your tip, now I get following error:

"SELECT id_product FROM ps_category_product WHERE id_category IN ()You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
"

IN part of SELECT statement does not is empty and therefore query creation fails! Will checkout previous script query that fills array of product id's now!

[MarkoSiroki]

I do not know why $subcategories_id_numbers gets empty. If I run mysql statement from upper php script directly on server, I get results. I tried to print_r($subcategories_id_numbers) but it does now work.

[Emy]

$subcategories_id_numbers is an array or a string??

IN Statment

[MarkoSiroki]

Yes, it is an array of string with merchandise subcategories id numbers. In other script, the same approach works without problems.

[Emy]

we should convert the array to string:

Code: Select all

$in = implode (',', $subcategories_id_numbers);
$sql = 'SELECT ............         WHERE ........... IN (' . $in . ')';

[MarkoSiroki]

Then why the same approach is working in following script:

Code: Select all

<?php
	session_start();

	if($_SERVER["REQUEST_METHOD"]=="POST")
	{
		$hostname_localhost ="test";
		$database_localhost ="test";
		$username_localhost ="test";
		$password_localhost ="test";
		$path_to_categories_images="/httpdocs/img/c/";
 
		$localhost=mysql_connect($hostname_localhost,
					 $username_localhost,
					 $password_localhost) or die(mysql_error());
		
		mysql_select_db($database_localhost) or die(mysql_error());

		$query_categories_id_numbers=mysql_query("SELECT id_category
					 		  FROM ps_category
		  					  WHERE id_parent=1 AND active=1");

		$number_of_categories=mysql_num_rows($query_categories_id_numbers);
		if($number_of_categories>0)
		{
			// CATEGORIES FETCH
			$categories_id_numbers="";
			
			while($category_id_number=mysql_fetch_assoc($query_categories_id_numbers))
			{
				$categories_id_numbers.=$category_id_number['id_category'].",";
			}	// while
			$categories_id_numbers = substr($categories_id_numbers,
							0,
							strlen($categories_id_numbers)-1);
		
			$query_categories_names=mysql_query("SELECT id_category,name
						       	     FROM ps_category_lang
						             WHERE id_category IN ($categories_id_numbers) AND id_lang=1");

			$number_of_categories_names=mysql_num_rows($query_categories_names);
			if($number_of_categories_names>0)
			{
				while($categories=mysql_fetch_assoc($query_categories_names))
				{
					$path_to_image=$path_to_categories_images.$categories['id_category']/*."-large.jpg"*/.".jpg";
					$userdata[]=array("id_category" => $categories['id_category'],
							  "name" => $categories['name'],
							  "path" => $path_to_image);
				}	// while

	                        print(json_encode($userdata));
				mysql_close();
			}	// if
			// END OF CATEGORIES FETCH
		}
		else
		{
		}	// if
	}	// if
?>

??

[MarkoSiroki]

I've found a very very strange problem in my script, in following code chunk:

Code: Select all

            if($number_of_subcategories>0)
            {
                while($subcategories_id_numbers=mysql_fetch_assoc($query_subcategories_id_numbers))
                {
                    //print($subcategories_id_numbers['id_category'].",");    // debug code - PARAMETER READ OK
                    $subcategories_id_numbers.=$subcategories_id_numbers['id_category'].",";
                    //print_r($subcategories_id_numbers);   // debug code - PARAMETER READ OK
                }   // while

                print_r($subcategories_id_numbers); // debug code
                $subcategories_id_numbers=substr($subcategories_id_numbers,
                                                 0,
                                                 strlen($subcategories_id_numbers)-1);}

Variable $subcategories_id_numbers gets filled correctly in while loop, but, on second print_r, followed by while loop, prints out empty array/string. What the heck????