CODEWITHSUNDEEP
phpmysql
Muhammad Umar
Muhammad Umar

Reputation: 11782

Search query involving two tables in mysql

I have two tables, users and sales. sales.userId = user.id I am trying to use following query. To search parameters in both users and sales.

SELECT * FROM sales INNER JOIN users ON users.id = sales.userId WHERE 'users.fullname' like '%Cloud%' OR 'users.storename' like '%cloud%' order by sales.id DESC

No result is showing up. What could be wrong in query.

e.g one user['fullname'] is CloudBuck Abc

if(isset($_GET["q"]) && $_GET["q"]!="")
{
    $q = trim($_GET["q"]);
    $where = "WHERE users.fullname like '%".$q."%' OR users.storename like '%".$q."%'";
}

$query = "SELECT * 
FROM sales 
INNER JOIN users ON users.id = sales.userId ".$where." order by sales.id DESC";

Upvotes: 0

Views: 40

Answers (3)

Jens
Jens

Reputation: 69495

Remove the single qoutes around the column names:

SELECT * 
FROM sales 
INNER JOIN users ON users.id = sales.userId 
WHERE users.fullname like '%Cloud%' OR users.storename like '%cloud%' order by sales.id DESC

Because mysql will use it as string if the single quote is set.

Upvotes: 0

SonalPM
SonalPM

Reputation: 1335

SELECT * FROM sales INNER JOIN users ON users.id = sales.userId
 WHERE (users.fullname like '%Cloud%' OR users.storename like '%cloud%') 
 order by sales.id DESC

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You are quoting the field names and this is not allowed, either use backticks if its a reserved word or do not use it.

SELECT * FROM sales 
INNER JOIN users ON users.id = sales.userId 
WHERE `users`.`fullname` like '%Cloud%' 
OR `users`.`storename` like '%cloud%' 
order by sales.id DESC

Upvotes: 1

Related Questions