PHP mysql multi-table search returning different table and field data row after row
- by Kinyanjui Kamau
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:
Perform 3 separate LIKE %search% on each of the tables on search.php.
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
In 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.
This is just to give you an idea of what I am trying to do. Any assistance?
