Forum Moderators: open
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
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' )
$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)
1 query would do the username and date
1 query would do the 'search' bar
is this possible?
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);
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]
