Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Query problem

Codes here !

Moderators: macek, egami, gesf

Query problem

Postby MarkoSiroki » Mon Jun 04, 2012 2:06 pm

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!!
MarkoSiroki
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu May 24, 2012 2:44 pm

Re: Query problem

Postby Emy » Mon Jun 04, 2012 10:01 pm

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);
Emy
New php-forum User
New php-forum User
 
Posts: 16
Joined: Sat Jun 02, 2012 8:16 pm

Re: Query problem

Postby Nullsig » Tue Jun 05, 2012 5:31 am

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

In your second post do you see the mysql_error?
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Query problem

Postby MarkoSiroki » Tue Jun 05, 2012 7:51 am

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
MarkoSiroki
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu May 24, 2012 2:44 pm

Re: Query problem

Postby Nullsig » Tue Jun 05, 2012 8:32 am

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.
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Query problem

Postby ejsexton82 » Tue Jun 05, 2012 11:19 am

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)");
User avatar
ejsexton82
New php-forum User
New php-forum User
 
Posts: 86
Joined: Mon Jun 04, 2012 10:05 pm
Location: Ankara, Turkey

Re: Query problem

Postby MarkoSiroki » Tue Jun 05, 2012 10:42 pm

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
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu May 24, 2012 2:44 pm

Re: Query problem

Postby MarkoSiroki » Wed Jun 06, 2012 12:24 am

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

Sincerely,
Marko
MarkoSiroki
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu May 24, 2012 2:44 pm

Re: Query problem

Postby Emy » Wed Jun 06, 2012 8:23 pm

have u tried this?
Emy
New php-forum User
New php-forum User
 
Posts: 16
Joined: Sat Jun 02, 2012 8:16 pm

Re: Query problem

Postby MarkoSiroki » Wed Jun 06, 2012 9:31 pm

Emy wrote: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! :D Will checkout previous script query that fills array of product id's now!
MarkoSiroki
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu May 24, 2012 2:44 pm

Re: Query problem

Postby MarkoSiroki » Wed Jun 06, 2012 10:09 pm

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. :-x
MarkoSiroki
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu May 24, 2012 2:44 pm

Re: Query problem

Postby Emy » Fri Jun 08, 2012 1:06 am

$subcategories_id_numbers is an array or a string??

IN Statment
Emy
New php-forum User
New php-forum User
 
Posts: 16
Joined: Sat Jun 02, 2012 8:16 pm

Re: Query problem

Postby MarkoSiroki » Fri Jun 08, 2012 1:16 am

Yes, it is an array of string with merchandise subcategories id numbers. In other script, the same approach works without problems.
MarkoSiroki
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu May 24, 2012 2:44 pm

Re: Query problem

Postby Emy » Fri Jun 08, 2012 2:34 am

we should convert the array to string:

Code: Select all
$in = implode (',', $subcategories_id_numbers);
$sql = 'SELECT ............         WHERE ........... IN (' . $in . ')';
Emy
New php-forum User
New php-forum User
 
Posts: 16
Joined: Sat Jun 02, 2012 8:16 pm

Re: Query problem

Postby MarkoSiroki » Fri Jun 08, 2012 10:40 pm

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
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu May 24, 2012 2:44 pm

Re: Query problem

Postby MarkoSiroki » Sat Jun 09, 2012 11:48 am

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????
MarkoSiroki
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu May 24, 2012 2:44 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: Bing [Bot] and 3 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.