PHP: Why can't I use WHERE in this SQL query?

Question

Im having a problem with a sql query. This is the original function:

function selectAllSorted($active)
{
    return $this->selectObjects("SELECT bp.*, p.title as product_title
                                 FROM $this->_table bp 
                                 INNER JOIN ?_product p USING (product_id)
                                 ORDER BY 0+sort_order,p.title");
}

Which i modified it to look like this:

function selectAllSorted($active)
{
    return $this->selectObjects("SELECT bp.*, p.title as product_title
                                 FROM $this->_table bp
                                 WHERE product_id =$active
                                 INNER JOIN ?_product p USING (product_id)
                                 ORDER BY 0+sort_order,p.title");
}

But i get an unknown error and since i cant see the logs i cant pinpoint where it is or why is it wrong to use WHERE here.

This is how the function selectObjects is defined:

function selectObjects($sql, $param1 = null)
    {
        $args = func_get_args();
        $q = call_user_func_array(array($this->_db, 'queryResultOnly'), $args);
        $ret = array();
        while ($row = $this->_db->fetchRow($q))
        {
            $obj = new $this->_recordClass($this);
            $obj->fromRow($row);
            $ret[] = $obj;
        }
        return $ret;
    }

Can you guys figure out whats wrong?

Thank you.

Move the WHERE clause and place it after the JOIN

Hanky Panky
– Hanky Panky

2014-04-07 05:44:58 +00:00
Commented Apr 7, 2014 at 5:44 — Hanky Panky
– Hanky Panky, Commented Apr 7, 2014 at 5:44

Mureinik · Accepted Answer · 2014-04-07 05:43:44Z

3

Your syntax is wrong - the where clause comes after the join clause(s):

function selectAllSorted($active)
{
    return $this->selectObjects("SELECT bp.*, p.title as product_title
                                 FROM $this->_table bp
                                 INNER JOIN ?_product p USING (product_id)
                                 WHERE bp.product_id =$active
                                 ORDER BY 0+sort_order,p.title");
}

answered Apr 7, 2014 at 5:43

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tomas Pastircak Over a year ago

It will probably be necessary to select product_id from one of the tables, i.e. WHERE p.productid =..., won't it?

Mureinik Over a year ago

@TomasPastircak yup, good catch! Added the bp prefix to my answer.

Cain Nuke Over a year ago

Thank you! That was fast! I though the order didnt matter.

Vikram Jain · Accepted Answer · 2014-04-07 05:48:37Z

0

Join Query syntex is:

Select t1.* from table1 t1
inner join table2 t2  on t1.col1=t2.col1
where t1.col2> 0 and t2.col3>4

Now, your query look like :

function selectAllSorted($active)
{
    return $this->selectObjects("SELECT bp.*, p.title as product_title
                                 FROM $this->_table bp
                                 INNER JOIN ?_product p on pb.product_id=p.product_id
                                 WHERE bp.product_id =$active                                
                                 ORDER BY 0+sort_order,p.title");
}

answered Apr 7, 2014 at 5:48

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

1 Comment

Cain Nuke Over a year ago

Thank you, that was useful too.

Collectives™ on Stack Overflow

PHP: Why can't I use WHERE in this SQL query?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related