Grouping or Joining Search Query?

Question

I'm trying to have my websites search box be able to search multiple tables in one query.

So if a user searches for the users display_name which is stored in ptb_profiles they get any users with the matching name or if they search by nationality which is stored in ptb_stats they get all the users with that nationality displayed.

When i did this originally by using SELECT * FROM ptb_users, ptb_stats, ptb_profiles I got duplicate search results and the same results were being displayed multiple times, so I tried to do avoid this and have each result only displayed once by Grouping and using union all but that didnt work.

now ive tried this:

$query_for_result=mysql_query("SELECT display_name, location, gender, contact_number FROM ptb_profiles WHERE display_name LIKE '%".$query."%' OR location LIKE '%".$query."%' OR gender LIKE '%".$query."%' OR contact_number LIKE '%".$query."%' JOIN SELECT email, subscription FROM ptb_users WHERE email like '%".$query."%' OR subscription like '%".$query."%' JOIN SELECT nationality, hobbies, local_station FROM ptb_stats WHERE nationality like '%".$query."%' OR hobbies like '%".$query."%' OR local_station like '%".$query."%' 
        LIMIT 5");

but this just brings up the following error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /Applications/XAMPP/xamppfiles/htdocs/PTB1/includes/mod_sidebar/search.php on line 31

Can someone please tell me where I'm going wrong?

You've not shown us your schema, nor any code nor explain plans. The error you are getting is because your code is failing *BEFORE* it gets to mysql_fetch_array() and you've not included adequate checking. — symcbean, Dec 10 '12 at 21:30
Possible duplicate [mysql_fetch_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select?answertab=votes#tab-top) — John Conde, Dec 30 '12 at 05:20
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — John Conde, Dec 30 '12 at 05:21

score 0 · Answer 1 · answered Dec 10 '12 at 21:03

0

This wouldn't be the only way to do it. But you can do your search on multiple joined tables and fetch a distinct list of users easily if you apply a GROUP BY to your query. Assuming all your tables relate via an ID column something like this might work...

SELECT u.* FROM ptb_users u
INNER JOIN ptb_stats s ON s.user_id=u.id
INNER JOIN ptb_profiles p ON p.user_id=u.id
WHERE "this or that"
GROUP BY u.id

Change your joins to LEFT JOINS if its possible a user doesn't have a record in ptb_stats or ptb_profiles...

answered Dec 10 '12 at 21:03

lifo

2,791
1
25
32

SELECT u.* FROM ptb_users AS u – v0d1ch Dec 10 '12 at 21:06
If you're trying to state that the "AS" keyword is missing, please understand that the "AS" keyword is not actually required in SQL syntax. It's more of a relic from decades past. – lifo Dec 10 '12 at 21:15

score 0 · Answer 2 · answered Dec 10 '12 at 21:33

mysql_fetch_array() complains about $query_for_result not being a query result. You must check the return value from mysql_query(). It returns false, if there's some problem with your SQL statement. So do at least

$sql = "select ...";
$result = mysql_query($sql) or die("Error in $sql: " . mysql_error());

to prevent and diagnose such errors.

Grouping or Joining Search Query?

2 Answers2