Searching Multiple Tables

[conker87]

I have a database with 4 tables

 

article, musicvideo, review, video

 

All of these have the title and data columns. I'm wanting to make a query that allows me to search those tables and columns with the supplied search parameter. I have already made the form, as follows:

 

       <input name="search_query" class="textbox" type="text" /> 
       <input name="search" class="searchbutton" value="Item Search" type="submit" /><br>
       <input type="radio" value="*" name="searchresult" id="titledata"> <label for="titledata" title="Search in both titles and content of all items">Title and Content</label> -
       <input type="radio" value="title" name="searchresult" id="title"> <label for="title" title="Search in titles of all items">Title only</label> -
       <input type="radio" value="data" name="searchresult" id="data"> <label for="data" title="Search in content of all items">Content only</label>

 

Added to the above, I'm also wanting to be able to choose whether to only search the title, data or both columns of the tables.

 

I know this is possible as I'd accomplished it before, but all of that data was lost due to a disk failure (on both my PC and the host's, what rotten luck!)

 

Anyone out there that can help?

[jworisek]

I'm confused by what is so difficult here...  all you need are if/else statements on the next page to determine which query to apply...

[conker87]

I'm confused as to the syntax.

 

I have, as follows:

SELECT * FROM article, musicvideo WHERE musicvideo.* = '$search_query' OR article.* = '$search_query'

Where $search_query is the POST data from the form.

 

After which is:

   while ($row = mysql_fetch_array($result))
    {
     $title  = $row['title'];
     $author = $row['author'];
     $date   = $row['date'];
    }
?>

 

This, however, is generating a warning:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\foxserv\www\s.all.php on line 22

 

Obviously the syntax of my query is incorrect, I just cant figure out how to get it to work  ???

[trecool999]

$sql = 'SELECT * FROM article, musicvideo WHERE musicvideo = ' . $search_query . ' OR article = ' . $search_query;
$result = mysql_query($sql) or die('ERROR! ' . mysql_error());

while ($row = mysql_fetch_array($result)) {
    $title  = $row['title'];
    $author = $row['author'];
    $date   = $row['date'];
}

 

Try that. If it doesn't work, try replacing 'mysql_fetch_array($result)' with 'mysql_fetch_assoc($result)' .

 

 

[conker87]

That generates:

 

ERROR! Unknown column 'musicvideo' in 'where clause'

 

So I added ".*"'s:

 

$sql = 'SELECT * FROM article, musicvideo WHERE musicvideo.* = ' . $search_query . ' OR article.* = ' . $search_query;

 

Now the error is:

 

ERROR! You have an error in your SQL syntax near '* = test OR article.* = test' at line 1

[conker87]

OK.

 

I've now, almost, cracked it:

 

SELECT * FROM article, musicvideo WHERE article.title OR article.data OR musicvideo.title OR musicvideo.author LIKE '%$search_query%'

 

Is there anyway of making it simpler? Perhaps using the wildcard to choose the tables in the WHERE clause.

[fenway]

That won't work -- you need to repeat the comparison for EACH AND EVERY column.

[trecool999]

$sql = 'SELECT * FROM article, musicvideo WHERE musicvideo.title = ' . $search_query . ' OR musicvideo.author = ' . $search_query . ' article.title = ' . $search_query OR article.data = ' . $search_query;
$result = mysql_query($sql) or die('ERROR! ' . mysql_error());

while ($row = mysql_fetch_array($result)) {
    $title  = $row['title'];
    $author = $row['author'];
    $date   = $row['date'];
}

 

?