Logical issue with MySQL SELECT statement

[jgrauer]

Hey everyone..

I originally posted this in PHP section, but it seems we identified it as a MYSQL problem, so I figured I should post the question here.

I am attempting to make a search engine which has 4 fields. Username, Search Field, and 2 Date Fields (from and to)

My query is as such

mysql_query("SELECT * FROM leads WHERE (username='$usersearch') AND (company LIKE '%search%' OR contact LIKE '%search%' OR phone LIKE '%search%' OR result LIKE '%search%') AND (date BETWEEN '%date1%' AND '%date2')") or die (mysql_error());

For some reason, this is not working as intended... the ultimate is goal is to select a set of records by a specific username, between a specific date. The last box is to narrow the search results down by a specific section. There are no ERRORs being reported by the mysql_error() function

If I try the query broken down... like so:

1. ("SELECT * FROM leads WHERE (username='$usersearch')")
2. ("SELECT * FROM leads WHERE (company LIKE '%search%' OR contact LIKE '%search%' OR phone LIKE '%search%' OR result LIKE '%search%')")
3. ("SELECT * FROM leads WHERE (date BETWEEN '%date1%' AND '%date2')")

Then it works fine ... how can I go about merging this into 1 query?

Any help would be very appreciated.

Thank you

[Demaestro]

I am stretching here but maybe it is the between that is getting messed up when used with all the ANDs

Try it without the between function

SELECT * FROM leads WHERE (username = '$usersearch') AND (company LIKE '%$search%' OR phone LIKE '%$search%' OR contact LIKE '%$search%' OR result LIKE '%$search%') AND (date <= '$date_now') AND (date >= '$date_then' )

[jgrauer]

okay, i attempted to that, and it seems to kinda be working.
The query pulled up a result that did match my criteria, however, it should have pulled up multiple results and it didnt.

$result = mysql_query("SELECT * FROM leads WHERE (username = '$usersearch') AND (company LIKE '%$search%' OR phone LIKE '%$search%' OR contact LIKE '%$search%' OR result LIKE '%$search%') AND (date <= '$date_now' AND date >= '$date_then')") or die(mysql_error());

I know that the PHP coding that displays the results is correct, the actual query is only pulling up 1 result (the latest in the list it seems)

[coopster]

The quickest way to determine whether or not it is your application logic (PHP code) versus your query is to run the command from the command line. If you are certain you should be retrieving more than one result set, or you want to be certain you will retrieve more than one while testing, the best thing to do is INSERT some rows manually that meet all the criteria of your test query.

[jgrauer]

is it possible to make 2 seperate queries, then merge them together to produce 1 result?

1 query would do the username and date
1 query would do the 'search' bar

is this possible?

[Demaestro]

yes you can merge the results using PHP

I am not great at PHP so I would confirm this but something like

results1 = mysql_query("select * from table1") or die (mysql_error());
results2 = mysql_query("select * from table2") or die (mysql_error());

$result = array_merge($results1, $results2);

[brotherhood of LAN]

you can also merge results using the UNION syntax [dev.mysql.com] in MySQL

SELECT * FROM table1
UNION
SELECT * FROM table2

One thing to note is that both tables must produce the same number of columns, though if there aren't the same number you can do something like this

SELECT col1,col2,col3 FROM table1
UNION
SELECT col1,col2,1 FROM table2

[edited by: brotherhood_of_LAN at 6:55 pm (utc) on Aug. 29, 2008]