CODEWITHSUNDEEP
phpmysql
Rjdlee
Rjdlee

Reputation: 779

You have an error in your SQL syntax. MySQL Selectgiving error with LIKE and

I'm trying to make a MySQL select for use in a mysql_query function, but I keep getting this error:

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 'LEFT JOIN Orgs ON Jobs.CustOrgID = Orgs.ID 
            LEFT JOIN Persons ON Jobs.CustPer' at line 11

I've tried everything and searched everywhere, but nothing seems to be working. All help is appreciated. 

 $Qry = "SELECT 

     Jobs.ID, 
     'Jobs.Status',
     Jobs.JobNum, 
     'Orgs.Nme', 
     'Persons.FirstNme',
     'Persons.LastNme',
     'JobTypes.JobType',
     'Jobs.Dsc',
     'Jobs.Notes'
     FROM Jobs ";

     if($column !== null && $text !== null) {
        $Qry .= "WHERE " . $column . " LIKE '%" . $text . "%' ";
     }

     $Qry .= "LEFT JOIN Orgs ON Jobs.CustOrgID = Orgs.ID 
         LEFT JOIN Persons ON Jobs.CustPersonID = Persons.ID
         LEFT JOIN JobTypes ON Jobs.JobTypeID = JobTypes.ID 
         ORDER BY JobNum";

SOLUTION:

     SELECT ...
     FROM ...
     LEFT JOIN ...
     LEFT JOIN ...
     WHERE ...
     ORDER BY ...

My WHERE was in the wrong place, it should come after the two LEFT JOINS.

Upvotes: 1

Views: 87

Answers (2)

echo_Me
echo_Me

Reputation: 37233

you should use backticks instead of single quotes , or you dont have to use backticks if they are not reserved keywords or separated strings .

try this :

     $Qry = "SELECT 

 `Jobs`.`ID`, 
 `Jobs`.`Status`,
 `Jobs`.`JobNum`, 
 `Orgs`.`Nme`, 
 `Persons`.`FirstNme`,
 `Persons`.`LastNme`,
 `JobTypes`.`JobType`,
 `Jobs`.`Dsc`,
 `Jobs`.`Notes`
 FROM Jobs ";

Upvotes: 3

Marc B
Marc B

Reputation: 360672

You're inserting the WHERE in the wrong place. It must come AFTER the joins:

SELECT ...
FROM ...
LEFT JOIN ...
LEFT JOIN ...
WHERE ...
ORDER BY ...

Upvotes: 5

Related Questions