Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

MySQL query to connect to multiple tables

New Here ,
Jul 16, 2011 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'];

TOPICS
Server side applications
325
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 16, 2011 Jul 16, 2011
LATEST

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%')

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines