MySQL query to connect to multiple tables

Report · Jul 16, 2011

The following is a code I made for a search page to search my database "***_members". I want the code to not only take the information from the table "bands", but also the table "venues".

I would also like the search to not only look for "name" but also other things in the database like "genre" or "pricing".

Here is part of my code, I have put */*/* around the stuff that I tried that out but didn't help.

if(isset($_REQUEST['submit'])) {

$search = $_GET['search'];
$terms = explode(" ", $search);
$query = "SELECT * FROM bands WHERE ";
*/*/*$query = "SELECT * FROM venues WHERE ";*/*/*

$i=0;
foreach($terms as $each){
$i++;
if($i==1){
$query .= "name */*/*AND genre*/*/* LIKE '%$each%' ";
}else{
$query .= "OR name */*/*AND genre*/*/* LIKE '%$each%' ";
}
}

mysql_connect("*********","************","**********") or die("Could Not Connect To Server..!");
mysql_select_db("*******_members");

$query = mysql_query($query);
$num = mysql_num_rows($query);

if($num > 0 && $search!=""){
echo "$num result(s) found for <b>$search</b>!<br />";
while($row = mysql_fetch_assoc($query)){
$id = $row['id'];
$name = $row['name'];
$ctsp = $row['ctsp'];
$image = $row['image'];
$genre = $row['genre'];
$influ = $row['influ'];
$location = $row['location'];
$pricing = $row['pricing'];

Report · Jul 16, 2011

Here's the thing...if you want to create dynamic sites then you MUST learn SQL basics. SQL sytax is very specificy - you can't simply guess at it.

Here's a good place to start:

http://www.w3schools.com/SQl/default.asp

If you want to select from more than one table, then you must join them. To join tables, they must have some relationship. You can either join them in the From clause, or as I prefer, in the Where clause. Also, you conditions in the where clause are incorrect.

Here's an example:

SELECT * FROM bands, venues

WHERE bands.venue_id = venues.venues_id AND

(bands.genre LIKE '%$each%' OR bands.name LIKE '%$each%')