Display results from 2 tables

Question

kelley82879 0 Light Poster

12 Years Ago

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>

mysql php

2 Contributors
22 Replies
288 Views
4 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by kelley82879

Dani AI

Generated 9 Months Ago

Short answer: run two separate SELECTs (one per table) and render each result set into its own HTML table — that avoids UNION merging rows and avoids exhausting a single result resource when you try to loop twice. As pointed out, UNION combines results (and requires matching columns), and as discovered a misspelled table name will make the query fail.

Why you saw that warning and an empty second table: your SQL failed so the query returned false (not a result resource), which makes the row-count call complain. Also reading the same result resource in two separate while loops will exhaust it the first time, so the second loop finds nothing. Finally, the empty-term check in the original code used an assignment instead of a comparison.

Recommended, minimal approach (use prepared statements — PDO or MySQLi — not deprecated mysql_* functions). Run two safe queries, fetch results into arrays, then print two tables. Example (illustrative):

<?php
$pdo = new PDO('mysql:host=localhost;dbname=DBNAME;charset=utf8mb4','USER','PASS',[
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$term = trim($_POST['term'] ?? '');
if ($term === '') { /* handle missing input */ }

$like = "%{$term}%";

$stmt = $pdo->prepare("SELECT Cater, Part, Gid FROM `Cater Gids` WHERE Cater LIKE ?");
$stmt->execute([$like]);
$gids = $stmt->fetchAll(PDO::FETCH_ASSOC);

$stmt = $pdo->prepare("SELECT Cater, Dish, Gid FROM `Cater Food` WHERE Cater LIKE ?");
$stmt->execute([$like]);
$food = $stmt->fetchAll(PDO::FETCH_ASSOC);

// render $gids as first table, $food as second table

Extra tips: use backticks for table names that contain spaces, check exact spelling, use PDO exceptions or inspect the DB error when a query fails, and consider UNION ALL (or add a literal "source" column) only if you truly want merged results and will separate them in PHP. For faster text searches than leading-wildcard LIKE, consider fulltext indexing or redesigning the search pattern.

All 22 Replies

urtrivedi 276 Nearly a Posting Virtuoso

12 Years Ago

 $sql = mysql_query("SELECT * FROM `Cater Gid` where Cater like '%$term UNION SELECT * FROM `Cater Food` where Cater like '%$term")  ;

Edited 12 Years Ago by urtrivedi

urtrivedi 276 Nearly a Posting Virtuoso

12 Years Ago

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

urtrivedi 276 Nearly a Posting Virtuoso

12 Years Ago

can u paste sturcture of both table here

urtrivedi 276 Nearly a Posting Virtuoso

12 Years Ago

what error it gives?

urtrivedi 276 Nearly a Posting Virtuoso

12 Years Ago

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

Reply to this topic

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.

kelley82879 0 Light Poster · Answer 1 · 2013-10-06T12:58:32+00:00

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

kelley82879 0 Light Poster · Answer 2 · 2013-10-06T13:09:12+00:00

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

kelley82879 0 Light Poster · Answer 3 · 2013-10-06T13:19:45+00:00

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

kelley82879 0 Light Poster · Answer 4 · 2013-10-06T13:27:20+00:00

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

kelley82879 0 Light Poster · Answer 5 · 2013-10-06T13:32:33+00:00

kelley82879 0 Light Poster

12 Years Ago

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

urtrivedi 276 Nearly a Posting Virtuoso · Answer 6 · 2013-10-06T13:38:01+00:00

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

kelley82879 0 Light Poster · Answer 7 · 2013-10-06T14:07:51+00:00

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

urtrivedi 276 Nearly a Posting Virtuoso · Answer 8 · 2013-10-06T14:11:56+00:00

urtrivedi 276 Nearly a Posting Virtuoso

12 Years Ago

remove quotes around

urtrivedi 276 Nearly a Posting Virtuoso · Answer 9 · 2013-10-06T14:15:19+00:00

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

kelley82879 0 Light Poster · Answer 10 · 2013-10-06T14:32:42+00:00

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

urtrivedi 276 Nearly a Posting Virtuoso · Answer 11 · 2013-10-06T14:39:52+00:00

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

kelley82879 0 Light Poster · Answer 12 · 2013-10-06T14:58:57+00:00

kelley82879 0 Light Poster

12 Years Ago

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

urtrivedi 276 Nearly a Posting Virtuoso · Answer 13 · 2013-10-06T15:07:36+00:00

urtrivedi 276 Nearly a Posting Virtuoso

12 Years Ago

what is ur target, what u want to achive

kelley82879 0 Light Poster · Answer 14 · 2013-10-06T15:10:38+00:00

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

urtrivedi 276 Nearly a Posting Virtuoso · Answer 15 · 2013-10-06T15:16:31+00:00

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

kelley82879 0 Light Poster · Answer 16 · 2013-10-06T15:42:27+00:00

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

kelley82879 0 Light Poster · Answer 17 · 2013-10-06T17:12:38+00:00

kelley82879 0 Light Poster

12 Years Ago

search from 2 tables and display as 2 tables

Display results from 2 tables

Dani AI

Recommended Answers Collapse Answers

All 22 Replies

Recommended Answers