cant search from multiple tables in database

[berserk]

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.

[berserk]

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]

I apologize :

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

needs to be:

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

[diafol]

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 ...)

[berserk]

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]

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

[berserk]

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

[diafol]

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]

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?

[diafol]

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

[Bin_2]

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]

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]

can u submit ur table structure?

[berserk]

sure, this is a snippit of it

[Bin_2]

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

[diafol]

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.