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????