I would like to have my users type in a term and display results from two different tables. I tried using union but i'm getting errors can you help please?

<?php
mysql_connect ("localhost", "","")  or die (mysql_error());
mysql_select_db ("");
if(isset($_POST['submit']) && !empty($_POST['submit'])){
    $result = ""; //USED LATER
    /*$term = $_POST['term']; THIS WORKS BUT FOR SECURITY ISSUES USE:*/
    $term = mysql_real_escape_string($_POST['term']);//AVOID MYSQL INJECTION
    $sql = mysql_query("SELECT * FROM `Cater Gid` where Cater like '%$term") UNION ("SELECT * FROM `Cater Food` where Cater like '%$term")  ;
     if (mysql_num_rows($sql) <= 0) {
        // no results
        //echo 'No results found.'; BETTER ECHO LATER
        $error = "No result found";
    } else if ($term ="") {
        $error = "No name entered!";
    } else {
        $result .= "<table border='1'>";
        $result .="<tr><td>Cater</td><td>Part</td><td>Gids</td></tr>";
        while ($row = mysql_fetch_array($sql)){
            $result .= '<tr>';
            $result .= '<td>'.$row['Cater'].'</td>';
            $result .= '<td>'.$row['Part'].'</td>';
            $result .= '<td>'.$row['Gid'].'</td>';
            $result .= '</tr>';
        }
        $result .= "</table>"; 
        $result .= "<table border='1'>";
        $result .="<tr><td>Cater</td><td>Dish</td><td>Gids</td></tr>";
        while ($row = mysql_fetch_array($sql)){
            $result .= '<tr>';
            $result .= '<td>'.$row['Cater'].'</td>';
            $result .= '<td>'.$row['Dish'].'</td>';
            $result .= '<td>'.$row['Gid'].'</td>';
            $result .= '</tr>';
        }
        $result .= "</table>";
    }
     mysql_close();
}
?>
<html>

    <head>
        <title>FIND YOUR GID</title>
    </head>
    <body color="" bgcolor="#634673" text="white"><font size="7"></font>
<center><font size="7"> </font><br><br><br><br>
<h1> <center>MORE DATA COMING SOON! </center></h1>
<h2> <center>ENTER NAME OF CATER! </center></h2>
    <form action="caterfood.php" method="post">
     Search: <input type="text" name="term" /><br />
    <input type="submit" name="submit" value="Submit" />
    </form>
    <div name="Results">
    <?php
    if(isset($error)){echo $error;}
    if(isset($result)){echo $result;}
    ?>
    </div>
    </body>
</html>
<script type="text/javascript">
var arrRequiredFields = [ "term" ];
window.onload = function() {
   document.forms[0].onsubmit = function() {
      for (var i = 0; i < arrRequiredFields.length; i++) {
         var field = document.forms[0].elements[arrRequiredFields[i]];
         if (field && field.value.length == 0) {
            alert("Missing Name of Food");
            field.focus();
            return false;
         }
      }
      return true;
   };
};
</script>
 $sql = mysql_query("SELECT * FROM `Cater Gid` where Cater like '%$term UNION SELECT * FROM `Cater Food` where Cater like '%$term")  ;

thanks now i've error here: if (mysql_num_rows($sql) <= 0) {

cater gid and cater food, must have same number of columsn and of same data type.
If it is not the case
then select column name properly in both queries

both cater gid and cater food have 3 columns with the first column both being the same (cater)

can u paste sturcture of both table here

Cater Food

Column Type Null Default
Cater varchar(255) No
Dish varchar(255) Yes NULL
Gid int(11) No

Cater Gids

Column Type Null Default
Cater varchar(255) No
Part varchar(255) Yes NULL
Gid int(11) No

what error it gives?

when i search for the cater i get: Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in
then it also tells me no results found

u can chat on web development room, i m there if u wish

thanks I'm a newbie don't have that privilege yet.

i dont think so, on web devlpment link on top
if you click it,
then there is blue button lauch chat room.

I guess, its allowd for all users.

but your query is having problem,
copy it from queto to quote, and run it in phpmyadmin,

see error and solve that mysql error first

When I click launch chat room its says: Sorry, you don't have suffieient privileges to chat.
When I ran the query in phpmyadmin it gave me this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT * FROM Cater Gid where Cater like '%$term UNION SELECT * FROM `Cater F' at line 1

remove quotes around

SELECT * FROM `Cater Gid` where Cater like '%$term' UNION SELECT * FROM `Cater Food` where Cater like '%$term'

you forgot single qute near like word on right side

Thanks for that I forgot a "s" in the table name. Its not dipalying as 2 different tables its show as one table.

union will merge 2 result in one, if common rows showed only once

so do i have to use join to see two separate tables?

what is ur target, what u want to achive

I want my users to type in the name of the cater and then diplay both tables one to display the part and gid and the other table showing the dish and gid

yoru query is fine for that purpose, so what your query do not show or what wrong it shows?

I want it to diplay like this:

Cater Part Gid
""    ""    ""
""    ""    ""
""    ""    ""
Cater Dish Gid
""    ""    ""
""    ""    ""
""    ""    ""

But it is displaying like this:

Cater Part Gid
""    ""    ""
""    ""    ""
""    ""    ""
""    ""    ""
""    ""    ""
""    ""    ""
Cater Dish Gid

search from 2 tables and display as 2 tables

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.