Multiple Table Search

[ziycon]

I have a basic search function working with PHP and mysql, can anyone help me to make a function that takes a single search criteria and searches multiple tables and gives the different results back to the user??

[TheServant]

I'm sure we can help. We however will not write it for you, but rather help you fix your own code. Please post the code in question and any relavent other snippets. Also include a detailed description of what is happening, and what should be happening that is not.

[pradeepjain]

I have a basic search function working with PHP and mysql, can anyone help me to make a function that takes a single search criteria and searches multiple tables and gives the different results back to the user??

Are u clear on to which tables to search or search all tables in a DB is needed

[Markus]

You could use a JOIN, but I'm not quite sure on the specifics of your problem. Elaborate?

[numberwhun]

So let me get this right, you have a search in mysql that you want to perform over a bunch of tables.

Why not put the names of the tables into an array and then cycle through the array, performing the search for each table?

Regards,

Jeff

[Dormilich]

MySQL entry for multiple table selects: MySQL manual - SELECT > JOIN

[ziycon]

Sorry for not getting back sooner. I have this code below for the search so far and it works fine, but it's very crude.
I haven't had much exposure to joins and inner selects which I'm assuming I'm going to need to use to solve my issue. I currently have four table in the database and the below code is just searching the title of these records based on the inputted value and the table selected from the drop down list on the page.
I want the user to just enter a word or phrase and then the search to be run based on the value over the four tables through both the title and body of each record and out put them in a percentage relevant order.
All the fields in the query are the same across all four tables.

function display_details($q,$table) {
  $link = '';
	
  $sql = mysql_query("SELECT id,title,body,date,time FROM ".$table." WHERE title LIKE '%".$q."%' ORDER BY date DESC, time DESC");
  while($row = mysql_fetch_array($sql))
  {
	$link_name = preg_replace('#[^a-zA-Z0-9]+#','-',$row['title']);
    echo '<div class="search_results_wrapper">
		<div class="search_results_link_name"><div class="search_results"><a href="'.$link.$row['id'].'-'.$link_name.'.htm">'.$row['title'].'</a></div></div>
		<div class="search_datetime">'.strftime("%b %d, %Y", strtotime($row['date'])).' at '.wordwrap(substr($row['time'],0,5)).'</div>
		<div class="search_results_body">'.add_dots(format_article_2($row['body']),'|',150).'</div>
		<div class="search_results_url">'.$link.$row['id'].'-'.$link_name.'.htm</div>
    </div>
';
  }
}

[ziycon]

Can anyone advise on this?

[Markus]

Is the seperation of the tables really necessary? If they're holding the same type of data, it makes sense to have it all in one table.

However, you can do multiple joins like so:

SELECT tb1.*, tbl2.*, tbl3.* FROM `table_1` AS tbl1
LEFT JOIN `table_2` AS tbl2
 ON tbl1.id = tbl2.id
LEFT JOIN `table_3` AS tbl3
 ON tbl2.p_type = tbl3.p_type

Something like that.

[ziycon]

There is other fields in these tables relevent to each section hence why they are not in the one table, i'll try the inner joins and let you know, thanks.

[ziycon]

Ok, I'm getting really confused now, this is what i have been trying to convert them both to inner joins to use all three tables(news,rev iews,previws) but i keep getting tons of erros!?!

function getQCount($q,$table) {
  //$type = check_type($type);
  
  $sql = mysql_query("SELECT COUNT(id) AS numrows FROM ".$table." WHERE title LIKE '%".$q."%'");
  while($row = mysql_fetch_array($sql))
  {
    return $row['numrows'];  
  }
}
function display_details($q,$table) {
  $link = '';

  if($table == 'news')
	$link = 'article-news/';
  else if($table == 'reviews')
    $link = 'article-reviews/';
  else if($table == 'previews')
    $link = 'article-previews/';
	
  $sql = mysql_query("SELECT id,title,body,date,time FROM ".$table." WHERE title LIKE '%".$q."%' ORDER BY date DESC, time DESC");
  while($row = mysql_fetch_array($sql))
  {
	$link_name = preg_replace('#[^a-zA-Z0-9]+#','-',$row['title']);
	$title = str_replace($q,'<strong>'.$q.'</strong>',$row['title']);
    echo '<div class="search_results_wrapper">
		<div class="search_results_link_name"><div class="search_results"><a href="'.$link.$row['id'].'-'.$link_name.'.htm">'.$title.'</a></div></div>
		<div class="search_datetime">'.strftime("%b %d, %Y", strtotime($row['date'])).' at '.wordwrap(substr($row['time'],0,5)).'</div>
		<div class="search_results_body">'.add_dots(format_article_2($row['body']),'|',150).'</div>
		<div class="search_results_url">www.ign.ie/'.$link.$row['id'].'-'.$link_name.'.htm</div>
    </div>
';
  }
}

[TheServant]

Please include your errors! It helps so much when we can see what's going wrong without having to proof read code.

I am not sure if COUNT(id) works? I have always used COUNT(*), so if you have an error there try that.

[Markus]

I am not sure if COUNT(id) works? I have always used COUNT(*), so if you have an error there try that.

Yes you can COUNT() on single columns.

@ OP: I don't see any JOINs in your SQL.