CODEWITHSUNDEEP
phpmysql
Tom
Tom

Reputation: 160

mysql multiple WHERE from different tables

I want to output a nice user table. But my query needs a WHERE from multiple tables.

At the moment... my query looks like:

$statsTable = "someTable";
$userTable = "someOtherTable";

$someData = "SELECT stats.* FROM $statsTable stats, $userTable user 
WHERE user.some_status = '0' 
AND (stats.some_value BETWEEN $rangeFrom AND $rangeTo) 
ORDER BY stats.some_value ASC 
LIMIT 0,10";

then mysqli_query and so on...

The output(array) has 2 times the data from $statsTable and the WHEREs are not working. I just want to select the $statsTable...

How to proceed? Thanks :)

Upvotes: 1

Views: 53

Answers (2)

AleMelo
AleMelo

Reputation: 128

$statsTable = "someTable";
$userTable  = "someOtherTable";

$someQueryForData = "SELECT stats.* 
                     FROM $statsTable stats
                     JOIN $userTable user
                     ON (user.id_stats = stats.id)
                     AND (user.some_status = '0')
                     WHERE (stats.some_value BETWEEN $rangeFrom AND $rangeTo) 
                     ORDER BY stats.some_value ASC LIMIT 0,10";

Edit: explaining you're basically need a join, building query's the way you are doing makes them not as readable and you can't really associate your tables.

Using joins after you made your "ON" statement you may just add an "AND" And use that conjunction as a where which is way faster the using the where ITSELF

Upvotes: 3

Andrew Goldenberg
Andrew Goldenberg

Reputation: 21

Just use a join.

Join the tables on a unique ID and then you will have the values from both tables.

W3 Schools Joins

Should look like this

SELECT stats.* as stats, user.* as user
FROM statsTable
INNER JOIN userTable
ON stats.userId=user.userId
WHERE user.some_status = 0 AND (stats.some_value BETWEEN $rangeFrom AND     $rangeTo)
LIMIT 0,10;

Upvotes: 0

Related Questions