cant search from multiple tables in database

Question

berserk 60 Junior Poster

12 Years Ago

I need help trying to get my mysqli_query() function to work right. I have this so far:

$raw_results = mysqli_query($con, "SELECT * FROM numdata WHERE numb='%".$k."%'
UNION
SELECT * FROM numdata2 WHERE numb='%".$k."%'
UNION
SELECT * FROM numdata3 WHERE numb='%".$k."%'
UNION
SELECT * FROM numdata4 WHERE numb='%".$k."%'
UNION
SELECT * FROM numdata5 WHERE numb='%".$k."%'
UNION
SELECT * FROM numdata6 WHERE numb='%".$k."%'
UNION
SELECT * FROM numdata7 WHERE numb='%".$k."%'
UNION
SELECT * FROM numdata8 WHERE numb='%".$k."%'
UNION
SELECT * FROM numdata9 WHERE numb='%".$k."%'
UNION
SELECT * FROM numdata10 WHERE numb='%".$k."%'");

if(mysqli_num_rows($raw_results) > 0)

I found on the internet that this works but i tried it and i recieved a boolean error like so:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\AppServ\www\newsearch\search.php on line 63

it says the error is in the last line of that code above, which means to me that the query has something wrong because its always passing a false which it shouldnt, im not sure if UNION was the correct thing to use in this case but i cant find much else that works for me. Any help is greatly appreciated.

mysql php

3 Contributors
15 Replies
289 Views
3 Days Discussion Span
Latest Post 12 Years Ago Latest Post by Member #120589

Dani AI

Generated 9 Months Ago

The mysqli warning here means your SQL failed and PHP returned FALSE instead of a result set. Three common causes in this thread are: (a) bad WHERE syntax (you need LIKE with wildcards or = for exact matches), (b) a UNION where the SELECTs don’t have the same number and types of columns, and (c) using mysqli_multi_query without the special loop to retrieve each result. was right about UNION requiring matching columns, and ’s note about UNION vs UNION ALL on duplicates/performance is worth keeping in mind. ’s boolean error is the classic symptom — always inspect the error string from MySQL to see exactly what failed.

A practical, safe fix is to run a single SELECT that UNIONs explicitly-matched columns and use prepared statements to avoid injection. Example pattern (adjust columns to match your tables):

$sql = "
  SELECT id, numb, label FROM numdata WHERE numb LIKE ?
  UNION ALL
  SELECT id, numb, label FROM numdata2 WHERE numb LIKE ?
  UNION ALL
  SELECT id, numb, label FROM numdata3 WHERE numb LIKE ?
";
$stmt = mysqli_prepare($con, $sql);
$term = "%{$k}%";
mysqli_stmt_bind_param($stmt, 'sss', $term, $term, $term);
mysqli_stmt_execute($stmt);
$res = mysqli_stmt_get_result($stmt);
while ($row = mysqli_fetch_assoc($res)) {
  // handle each row
}

If you really want to run multiple separate SELECTs with mysqli_multi_query, you must loop with mysqli_store_result/mysqli_next_result to fetch each result set — otherwise mysqli_num_rows will be called on a boolean. Always check failures with:

if (!$result) { echo mysqli_error($con); }

Longer-term: if these ten tables store the same kind of data, merge them or create a view that unionizes them once. Also add an index on numb and use EXPLAIN if queries are slow.

All 15 Replies

Member #120589

12 Years Ago

You don't want an union - use OR. Union is v slow as each one searches the whole set for hits.

If you are searching for exact matches (not LIKE), use IN syntax

... WHERE $k IN (numdata, numdata2, numdata3 ...)

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.

berserk 60 Junior Poster · Answer 1 · 2014-06-21T06:48:41+00:00

update, i now know that i need to use mysqli_multi_query but i still have no idea what to do with it, im using this format now:

$query = "SELECT * FROM numdata WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata2 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata3 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata4 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata5 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata6 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata7 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata8 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata9 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata10 WHERE numb='".$k."'";

$raw_results = mysqli_query($con, $query);

but its still giving me the same exact error. am i using this all wrong?

here is the complete execution of the code:

$query = "SELECT * FROM numdata WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata2 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata3 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata4 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata5 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata6 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata7 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata8 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata9 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata10 WHERE numb='".$k."'";

$raw_results = mysqli_query($con, $query);

    if(mysqli_num_rows($raw_results) > 0)
    {
        while($results = mysqli_fetch_array($raw_results))
        {
            echo "<tr align='center' bgcolor='#FF0000'> <td height='25px'><font size='6'>is in database</font></td> </tr>" ;
        }
    }
    else{
        echo "<tr align='center' bgcolor='#00FF00'> <td colspan='2' height='25px'><font size='6'>is not in database</font></td> <tr>";
        echo "</table>";
    }

if there is anything wrong with this, which im sure this is, please let me know. Thanks in advance!

berserk 60 Junior Poster · Answer 2 · 2014-06-21T06:49:47+00:00

I apologize :

$raw_results = mysqli_query($con, $query);

needs to be:

$raw_results = mysqli_multi_query($con, $query);

berserk 60 Junior Poster · Answer 3 · 2014-06-21T23:40:06+00:00

i tried that but it still gives me that boolean error, lol i figured this problem out be it a tedious solution, i just had it pass multiple queries for each table i have available, then using elseif statements i searched through all that had values in them, only catch is that i had to have as many tables in my database as there were statements cause then it would attempt to read from nothing and give an error.

berserk 60 Junior Poster · Answer 4 · 2014-06-21T23:40:38+00:00

thanks again for helping, i appreciate all help given :D

berserk 60 Junior Poster · Answer 5 · 2014-06-21T23:41:15+00:00

should i post the whole code for others? or at least my finshed solution?

Member #120589 · Answer 6 · 2014-06-21T23:58:54+00:00

You have 10 number fields. You're searching for a specific value which could be in either field. So you run ONE query - it is not appropriate to run multiple queries here NOR use an UNION/UNION ALL. You should use OR or IN as I suggested. If you have an error in your code, please show what you have. Also show the table structure.

Wrt solutions - yes please - always handy for others. :)

berserk 60 Junior Poster · Answer 7 · 2014-06-23T00:40:53+00:00

well my problem is that the last time i tried something different from what i have it failed but it shouldnt have cause i have seen it work fro others in their code.

$query = "SELECT * FROM numdata WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata2 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata3 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata4 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata5 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata6 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata7 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata8 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata9 WHERE numb='".$k."';";
$query .= "SELECT * FROM numdata10 WHERE numb='".$k."'";

the only thing i havent tried yet would be OR as i used IN and it gave me the exact same error. How would one correctly go about using OR with the ten queries i have listed above and just make it into one query?

Member #120589 · Answer 8 · 2014-06-23T06:53:31+00:00

Hold on I just realised that you're searching 10 tables, not 10 fields. My apologies. Doh! heh heh, I really am getting old.

lol its all good, u still helped me a lot and again i appreciate all of it. :) thanks again

Bin_2 0 Light Poster · Answer 9 · 2014-06-23T13:44:48+00:00

union generally selects equal number of columns. so u cant use
`select * from........'
unless u have equal numbers of fields on all the tables. so select one by one columns and make all the column aliases same for all the tables...
like

SELECT t1col1 as one FROM numdata WHERE numb='%".$k."%'
UNION
SELECT t2col1 as one FROM numdata2 WHERE numb='%".$k."%'
UNION
SELECT t3col1 as one FROM numdata3 WHERE numb='%".$k."%'
UNION
SELECT t4col1 as one FROM numdata4 WHERE numb='%".$k."%'
UNION
SELECT t5col1 as one FROM numdata5 WHERE numb='%".$k."%'
UNION
SELECT t6col1 as one FROM numdata6 WHERE numb='%".$k."%'
UNION
SELECT t7col1 as one FROM numdata7 WHERE numb='%".$k."%'
UNION
SELECT t8col1 as one FROM numdata8 WHERE numb='%".$k."%'
UNION
SELECT t9col1 as one FROM numdata9 WHERE numb='%".$k."%'
UNION
SELECT t10col1 as one FROM numdata10 WHERE numb='%".$k."%'

berserk 60 Junior Poster · Answer 10 · 2014-06-23T17:46:49+00:00

i tried that but im still getting the boolean error, i found a way to do it but i admit it is a bit unorthodox however it works for what i need so thank you anyway. I will try to learn more about union so that next time i can have cleaner code.

Bin_2 0 Light Poster · Answer 11 · 2014-06-23T18:30:49+00:00

Bin_2 0 Light Poster

12 Years Ago

can u submit ur table structure?

berserk 60 Junior Poster · Answer 12 · 2014-06-24T01:34:16+00:00

berserk 60 Junior Poster

12 Years Ago

sure, this is a snippit of it

Bin_2 0 Light Poster · Answer 13 · 2014-06-24T02:39:27+00:00

check this sqlfiddle, i think it works.
since i dont know ur $k value for wildcard, i've remved where clause
http://sqlfiddle.com/#!2/5a64bf/1/0

Member #120589 · Answer 14 · 2014-06-24T08:33:15+00:00

Now that I finally understand what you're doing (! - can't believe I was so stupid, heh heh) - make sure that you understand the difference between UNION and UNION ALL. Basically, UNION removes all duplicates, so this is probably the one you want, however, if you need to count total entries, then you may need to go for UNION ALL.

cant search from multiple tables in database

Dani AI

Recommended Answers Collapse Answers

All 15 Replies

Recommended Answers