PHP mysql multi-table search returning different table and field data row after row

[kkjay]

Hi Guys,
I am building a social networking site that is dedicated to nightclubs and night events.

Among other tables, I have a users table, events table and establishments table.

I am really intrigued with how Facebook in particular is able to query and return matches of not just users but also pages, ads etc row after row. Im sure most who are reading this have tried the facebook search

My question is in my case, should I:

1. Perform 3 separate LIKE %search% on each of the tables on search.php.

2. Draw up 3 separate tables to show the results of what matches in the relevant queries which are collapsed when empty(on the same search.php) ie

table to show matched users

user_thumb name addfriend

table to show matched events(table does not show if no event match result)

event thumb, event name etc.

I need help.

[diafol]

It depends how you want to show your data.

You can join up different tables with the UNION clause if all returned fields are the same.

$rs = mysql_query("(SELECT field1 AS url, field2 AS info FROM table1 WHERE info LIKE '%whatever%')
UNION
(SELECT field7 AS url, field10 AS info FROM table2 WHERE info LIKE '%whatever%')
ORDER BY url LIMIT 30");

This will give you a result set which is ordered by the url (obviously useless in this example), but just to give you an idea. You could give each select a field for type identification as well if you wanted.

$rs = mysql_query("(SELECT field1 AS url, field2 AS info, 'person' AS s_type FROM table1 WHERE info LIKE '%whatever%')
UNION
(SELECT field7 AS url, field10 AS info, 'page' AS s_type FROM table2 WHERE info LIKE '%whatever%')
ORDER BY url LIMIT 30");

You could use this to provide a search result set, where people and page rows were mixed up - not all people first and then all pages.

[kkjay]

Thank you ardav for your reply. This is what I have:

SEARCH.PHP

//query users table
$query_user = "SELECT user_first_name, user_last_name, username, picture_thumb_url, avatar FROM users JOIN picture ON users.user_id = picture.user_id
AND picture.avatar=1 ORDER BY users.user_id";
$result_users = mysql_query($query_user, $connections) or die(mysql_error());
$row_result_users = mysql_fetch_assoc($wid_updates);

//query events table
$query_event = "SELECT event_thumb_url, event_name, event_venue, event_date FROM event WHERE event_name LIKE '%".$search_value."%'";
$event = mysql_query($query_event, $connections) or die(mysql_error());
$row_event = mysql_fetch_assoc($event);
$totalRows_event = mysql_num_rows($event);

//query establishments table
$query_establishment = "SELECT establishment_thumb_url, establishment_name, location_id, establishment_pricing FROM establishment 
WHERE establishment_name LIKE '%".$search_value."%'";
$establishment = mysql_query($query_establishment, $connections) or die(mysql_error());
$row_establishment = mysql_fetch_assoc($establishment);
$totalRows_establishment = mysql_num_rows($establishment);

My html:

<table max-width="500" name="users" border="0">
  <tr>
    <td width="50" height="50"></td>
    <td width="150"></td>
    <td width="150"></td>
    <td width="150"></td>
  </tr>
 </table> 
<table width="500" name="events" border="0">
  <tr>
    <td width="50" height="50"><a href="#profile.php"><img src="Images/<?php echo  $row_event['event_thumb_url']; ?>" 
    border="0" height="50" width="50"/></a></td>
    <td width="150"><?php echo $row_event['event_name']; ?></td>
    <td width="150"><?php echo $row_event['event_venue']; ?></td>
    <td width="150"><?php echo $row_event['event_date']; ?></td>
  </tr>
</table>
<table width="500" name="establishments" border="0">
  <tr>
    <td width="50" height="50"><a href="#profile.php"><img src="Establishment_Images/<?php echo $row_establishment['establishment_thumb_url']; ?>" 
                        border="0" height="50" width="50"/></a></td>
  <td width="150"><?php echo $row_establishment['establishment_name']; ?></td>
  <td width="150"><?php echo $row_establishment['location_id']; ?></td>
  <td width="150"><?php echo $row_establishment['establishment_pricing']; ?></td>
  </tr>
</table>

I haven't populated the PHP echo's for the user table.

Thsi is just to give you an idea of what I am trying to do. Any assistance?

[diafol]

> Any assistance?

I thought I just gave you some!

[kkjay]

Thanks for the help. I'll try to work on in and see how it goes