selecting multiple tables

DeathfireD

Hi, I'm running into a small problem. I want to select the title, address, and description from three different tables. So heres what I came up with.

<form name="form" id="searchform" action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
  <input type="text" id="s" name="searchdata" value="<?php if(isset($_POST['searchdata'])) echo htmlspecialchars($_POST['searchdata'], ENT_QUOTES); ?>" />
  <input type="submit" name="Submit" value="Search" />
  <input type="hidden" name="submitted" value="TRUE" /></p>
</form>

<?php

//create short variable names
if (isset($_POST['submitted'])) {
    include('database.php');

if (empty($_POST['searchdata'])) {
echo '<p><font color="red">You need to enter search term.</font></p>';
} else {
    $searchterm = mysql_real_escape_string(htmlspecialchars($_POST['searchdata'], ENT_QUOTES));
}

if ($searchterm) {

$query =("SELECT title, address, description FROM headlines, articles, casestudies WHERE title LIKE '%$searchterm%' ORDER BY `date` DESC LIMIT 0, 40") or die(mysql_error());

//query DB
        $result = mysql_query($query);

//get number or rows
        $num_rows = mysql_num_rows($result);
           echo '<p><b>Number of items found: '.$num_rows.'</b></p>';

        while ($s = mysql_fetch_array($result)){

		//Variables

 $info .="";

  }
  	echo $info;
}
}

?>

The code works when I'm only selecting from one table but as soon as I add two more tables headlines, articles, casestudies it gives off warnings
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\blabla\search.php on line 30

Number of items found:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\blabla\search.php on line 33

How would I select more then one table?

NogDog

The "best" answer may be that a database re-design is called for here so that all that data is in one table, perhaps with additional relational tables for whatever it is that makes those 3 tables different.

But if that is not feasible, then take a look at the UNION syntax.

DeathfireD

I really cant redesign the database at this point, If I could I would.

hmm you cant have more then one UNION can you?

For example

(SELECT title, address, description FROM headlines WHERE title LIKE '&#37;$searchterm%' )
UNION
(SELECT title, address, description FROM articles WHERE title LIKE '%$searchterm%' )
UNION
(SELECT title, address, description FROM casestudies WHERE title LIKE '%$searchterm%' )
ORDER BY `date` DESC LIMIT 0, 40;

So how would I make it select 3 tables?

Weedpacket

DeathfireD wrote:
hmm you cant have more then one UNION can you?

Good question. Try it out and see? According to MySQL's documentation, "UNION is used to combine the result from multiple SELECT statements into a single result set. "

NogDog

I think for the "order by" to work, the date will need to be part of the selected column list in each select.

Weedpacket

Besides, even if UNION only works on two queries, then it can work on more: if you have a union of two queries where one of the queries is a union of two queries, then you have a union of three queries.

DeathfireD

hmm I tried just

$query1 =("(SELECT title FROM headlines WHERE title LIKE '&#37;$searchterm%' )
UNION
(SELECT title FROM articles WHERE title LIKE '%$searchterm%' )
UNION
(SELECT title FROM casestudies WHERE title LIKE '%$searchterm%' )
ORDER BY `date` DESC LIMIT 0, 40") or die(mysql_error());

But that didn't work. Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 41

Line 41:
while ($s = mysql_fetch_array($result1)){

In the end I just made 3 separate query's which works but I'd still like to know how I could do this using UNION or JOIN.

walterquez

If you want to ORDER BY date, then you need to make it available in the SELECT statement when using UNION as far as I know.

For example your last query string would be something like:

(SELECT title, date FROM headlines WHERE title LIKE '%$searchterm%' )
UNION
(SELECT title, date FROM articles WHERE title LIKE '%$searchterm%' )
UNION
(SELECT title, date FROM casestudies WHERE title LIKE '%$searchterm%' )
ORDER BY date DESC

DeathfireD

walterquez;10898886 wrote:
If you want to ORDER BY date, then you need to make it available in the SELECT statement when using UNION as far as I know.

For example your last query string would be something like:
(SELECT title, date FROM headlines WHERE title LIKE '%$searchterm%' )
UNION
(SELECT title, date FROM articles WHERE title LIKE '%$searchterm%' )
UNION
(SELECT title, date FROM casestudies WHERE title LIKE '%$searchterm%' )
ORDER BY date DESC

I'm not really following what you mean.

I tried inserting each ORDER BY into each SELECT statment

$query1 =("(SELECT title FROM headlines WHERE title LIKE '%$searchterm%' ORDER BY `date` DESC )
UNION
(SELECT title FROM articles WHERE title LIKE '%$searchterm%' ORDER BY `date` DESC )
UNION
(SELECT title FROM casestudies WHERE title LIKE '%$searchterm%' ORDER BY `date` DESC )") or die(mysql_error());

The error that I was getting went away but now the output is just a white page. I did a var_dump to see if it was even getting anything and sure enough it says NULL.

I'm guessing that method doesn't work 🙁.