views:

153

answers:

3

Hi, I am using the php mysqli extension with prepared statements, but on one query it is not creating the query object.

Here is the code:

$sqlq = "SELECT id,name FROM productcategories JOIN products ON productcategories.productid = products.id WHERE categoryid=?";
     if($allsubcats)
     {
      foreach($allsubcats as $key => $data)
      {
       $sqlq .= " OR categoryid=?";
      }
     }
 echo $sqlq;
     if($query = $this->mysqli->connection->prepare($sqlq))
      {
       $query->bind_param("i", $cat);
       if($allsubcats)
       {
        foreach($allsubcats as $key => $data)
        {
         $query->bind_param("i", $data[0]);
        }
       }
       $query->execute();
       $query->bind_result($id,$name);
       $query->store_result();
       if($query->num_rows > 0)
       {
        while($row = $query->fetch())
        {
         $allprods["id"] = $id;
         $allprods["name"] = $name;
        }
       }
      $query->close();
      }

The problem:

The line "if($query = $this->mysqli->connection->prepare($sqlq))"
The if() is returning false, and therefore not creating the $query object, and not executing any of the code inside the if.

The "echo $sqlq;" returns

"SELECT id,name FROM productcategories JOIN products ON productcategories.productid = products.id WHERE categoryid=? OR categoryid=? OR categoryid=? OR categoryid=? OR categoryid=? OR categoryid=?"

Which I don't see anything wrong with.

Any help would be greatly appreciated,

Thanks, Nico

A: 

I don't think you actually call the prepare on the connection object rather just the mysqli object itself.

i.e.


if($query = $this->mysqli->connection->prepare($sqlq))

should just be


if($query = $this->mysqli->prepare($sqlq))

Edit
Checkout the first example:
http://php.net/manual/en/mysqli.prepare.php

Edit2:

Ahh yes i see.

As a side note you should consider aliasing your tables:


SELECT pc.id,p.name,p.id
FROM productcategories pc
JOIN products p ON pc.productid = p.id
WHERE categoryid=? OR categoryid=? OR categoryid=? OR categoryid=? OR categoryid=? OR categoryid=?
rezzif
In general yes, but in this case $mysqli is an object of a custom class, where $connection is the $mysqli connection.
Nico Burns
+2  A: 

Typical, I worked it out myself as soon as I posted this, does anyone else see things better as soon as they ask for help??

Anyway,

SELECT id,name FROM productcategories JOIN products ON productcategories.productid = products.id WHERE categoryid=? OR categoryid=? OR categoryid=? OR categoryid=? OR categoryid=? OR categoryid=?

Should have been

SELECT productcategories.id,products.name,products.id FROM productcategories JOIN products ON productcategories.productid = products.id WHERE categoryid=? OR categoryid=? OR categoryid=? OR categoryid=? OR categoryid=? OR categoryid=?

Nico Burns
You're not the only one, if that can reassure you ^^ And you had an excellent reflex, coming back to give the solution :-)
Pascal MARTIN
Getting up voted for answering your own question whoda thunk it :D
rezzif
Rubber ducking ftw. http://www.c2.com/cgi/wiki?RubberDucking
hobodave
I'm definitely buying one.
Mercer Traieste
@hobodave, yes exactly, although I tend to use my unfortunate friends as "rubber ducks".
Nico Burns
+1  A: 

Hey Nico. This isn't an answer to your question, as you already answered it. Just an unsolicited suggestion. I'm not sure how often that query will be run, or how many categories could be appended to it, but you may want to consider using the WHERE IN syntax.

Instead of:

WHERE foo = ? OR foo = ? OR foo = ? OR foo = ?

Use:

WHERE foo IN (?,?,?,?)

You'll make the queries more readable, and save a miniscule amount of time in your application. (sending less data to MySQL, smaller strings in PHP)

hobodave
Hi, thanks for that, ill change it. I think I'm getting good at something, and it turns out there's so much more to learn. I've upvoted you, but I won't mark it as the accepted answer, seeing as it doesn't answer the question.
Nico Burns