Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a search script here, that needs to search multiple tables for a phrase/word using MySQL and PHP. The column name column that is being searched is all the same across tables. Any help would be appreciated and here is my code below.

<?php
    $filter = $_REQUEST['query'];
    mysql_connect($hostname,$username, $password) or die ("<script language='JavaScript'>alert('Unable to connect to database! Please try again later.'),history.go(-1)</script>");
    mysql_select_db($dbname);
    $query = 'SELECT * FROM (SELECT `name` FROM `voxmoviesfilestable`) a UNION (SELECT `name` FROM `voxadultfilestable`) b UNION (SELECT `name` FROM `voxmixesfilestable`) c UNION (SELECT `name` FROM `voxconcertsfilestable`) d UNION (SELECT `name` FROM `voxmp3filestable`) e UNION (SELECT `name` FROM `voxfilestable`) WHERE `name` REGEXP \''.$filter.'\' ORDER BY `name`;';
    $maxquery = 'SELECT count(*) as cnt FROM `voxmoviesfilestable`, `voxadultfilestable`, `voxmixesfilestable`, `voxconcertsfilestable`, `voxmp3filestable`, `voxfilestable` WHERE `name` REGEXP \''.$filter.'\';';

    $result = mysql_query($query) or die ('Error accessing Database');
?>
share|improve this question
Not a solution but you have a lot of syntax errors in $query = 'SELECT * FROM WHERE REGEXP \''.$filter.'\' ORDER BY name;'; – asprin Mar 9 at 6:29
any chance you can put all your data into one table? – Your Common Sense Mar 9 at 6:30
Your first query isn't valid, there is no table name SELECT * FROM WHERE REGEXP .... Is there any relation between these tables in the second query? – Mahmoud Gamal Mar 9 at 6:34
Sorry about that, I edited my code just before I pasted it. I have the latest code which still errors. – jack2230 Mar 9 at 7:09
Maybe try this. Put a set of parentheses around the union selects. SELECT * FROM ( (SELECT name ... voxfilestable) ) WHERE name REGEXP...` – Buttle Butkus Mar 9 at 9:03

1 Answer

up vote 0 down vote

The command "select ... from table1, table2, table3..." does not do what it appears you think it does. It does a massive join of every row of every table to every row of every other table. That is very bad. It looks like you want union. But, it isn't that easy as you must get the columns to look alike. It would look like:

select * from ((select id, name from table1) a union (select id, name from table2) b union ...) where regexp '$filter' order by name

NOTE: I added parenthesis around the union section about 12 hours after initially writing this.

The union will take time, but I believe it would be faster than querying each table separately in PHP.

share|improve this answer
I have updated my code and I still get a database error. – jack2230 Mar 9 at 7:09
I added parenthesis around the union section to ensure it does union first and then does the select outside the parenthesis. You will probably have to add a table alias to the union section by putting some letter or name after the closing parenthesis. It helps to know WHAT the error is. Use: if(!$result=mysql_query($query)) echo "$query\n".mysql_error()."\n"; – kainaw Mar 9 at 15:48
Thanks, I will take a look at it and get back to you. – jack2230 Mar 11 at 5:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.