Searching and querying an entire database

programming
1

Hi everyone! I tried a search on this topic in the forums to prevent double-posting but I couldn’t find a post nor a solution.

What I do know
I’m fairly familiar with MySQL databases in that I know how to use phpMyAdmin. I know how to pull data from a database and display.

What I don’t know
I don’t know how to create a working search function that does the following:

  • Search all the columns within the tables within the database and displays the results

For example, I have a database called “french_destination” with about 8 tables, those tables are:

  • accommodations
  • sports
  • towns
  • restaurants
  • beaches
  • parks
  • villas
  • agencies

If a user searched for “colonial” it should search all the columns within those 8 tables and then display all the matching results.

I’d like to know if this was possible?

My search form:


<form method="post" action="search.php">
<input type="text" name="search" size=25 maxlength=25>
<input type="Submit" name="Submit" value="Submit">
</form>

This is what I had that only searched a certain table and a certain column: The problem though was that it only searched a certain table and certain column. I need it to search ALL columns within ALL tables and display ALL matching results.


<?php
$db = mysql_connect ("localhost", "french_user","french123")  or die (mysql_error());
mysql_select_db ("french_destination");

$search = $_POST['search'];

$result = mysql_query("SELECT * FROM accommodations WHERE caption LIKE '%$search%'");

while ($row = mysql_fetch_array($result)){
    echo 'ID: '.$row['accommodations_name'];
    echo '<br/><br/>';
    }
    
    

?>

I researched online and the only things that were semi-useful (but I couldn’t grasp them fully) were:

Using UNION (I already know OR)
Using show_tables and then querying results from the show_tables results

Any help would be greatly appreciated!