Mysql subquery or something better

46
February 12, 2019, at 1:40 PM

I am somewhat new to mysql and I am having an issue on how I should best write the following query. Say I have a table that has a datetime column as well as a few others I want to search on. Since this is just one table, I don't think a join statement would be appropriate here (but I may be wrong since I have not done much in the way of join statements) and I think a subquery is what I need here. So my initial query is to search the table based on a search string the user entered and then I want to limit that on a datetime (start date and end date) also specified by the user in an HTML form.

Table Schema

id, datetime, host, level, message

I want to select any rows that contain $searchstring first so something like ...

SELECT * FROM $table WHERE (level LIKE '%$searchstring%') OR (message LIKE '%$searchstring%') LIMIT $offset,$limit

If I want to limit the above results also by the datetime column, the query would look something like this ...

SELECT * FROM $table WHERE (datetime >='$startdate') AND (datetime < '$enddate')

How can I best merge these queries into one so I can first get any rows that match the search query and then further limit the rows by the start and end datetime?

TIA

Answer 1

You can achieve that by using a single where condition. In your case:

SELECT * FROM $table WHERE ((level LIKE '%$searchstring%') OR (message LIKE '%$searchstring%')) AND (datetime >='$startdate') AND (datetime < '$enddate') LIMIT $offset,$limit
Answer 2

You don't have to use a JOIN but only add a condition

SELECT * 
FROM $table 
WHERE (level LIKE '%$searchstring%' OR message LIKE '%$searchstring%')
AND
datetime >='$startdate'
AND datetime < '$enddate'
LIMIT $offset,$limit
Answer 3

You could simply use a UNION:

SELECT *
FROM $table
WHERE (level LIKE '%$searchstring%')
OR (message LIKE '%$searchstring%')
LIMIT $offset,$limit
UNION
SELECT *
FROM $table
WHERE (datetime >='$startdate')
AND (datetime < '$enddate')

Mysql will handle the duplicated rows for you and bring the ones with the search string first. If you need to stipulate a limit to the entire resultset or do additional ordering based on your criteria you can turn that into a subquery and select/limit from it.

READ ALSO
How to insert data into two tabels?

How to insert data into two tabels?

I have two tables FILM(Id, Title, Director, Year, Category) and Record(Id, Film_id)

48
Connect to node.js after installing MySQL

Connect to node.js after installing MySQL

When I type var mysql=require('mysql');, I get an error that mysql module is not found

57
(2059,“Authentication Plugin &#39;caching_sha2_password&#39;”) when running server connected with MYSQL database on Django

(2059,“Authentication Plugin 'caching_sha2_password'”) when running server connected with MYSQL database on Django

I want to configure my django project in order to connect it with database in MYSQL I created with workbench 80, and then I want to run the server by running

25