CODEWITHSUNDEEP
pythonmysqlformsflask
Kenny Powers
Kenny Powers

Reputation: 1274

Dynamic search of returned MySQL data

How does one go about dynamically searching MySQL data? To be specific, I want to query a table, for example:

mysql> select * from points where LAT != 0.0

After that I want to query only the information returned from the previous query:

mysql> select LAT, LON, FILE from points where LAT > 30.457835

How do I query my previous result instead of searching the entire database each time?

What I'm trying to reach is integrating a dynamic search form in Flask where each field selected in the form narrows down your results displayed on the web page. It seems to me that this kind of thing happens all the time, for example in library databases, or even amazon for that matter. Does each new criteria that you add create an entire new search string to bang at the database, or can you store this info in memory temporarily and query against your previous result?

I can see how that would be problematic with monstrous databases, but my project will rarely ever hit a Gig in size.

If my question is off base here, please let me know so that I can rephrase. I ultimately will be working this through flask / python, but I felt I should understand how the MySQL piece worked first. A simple search form in flask and returning results is simple, but making it user friendly in a sensible way is escaping me right now.

Thanks.

Upvotes: 2

Views: 769

Answers (2)

Lucas
Lucas

Reputation: 378

Use subqueries:

select sq.LAT, sq.LON, sq.FILE 
from (select * from points where LAT != 0.0) as sq
where sq.LAT > 30.457835

Upvotes: 1

James Anderson
James Anderson

Reputation: 27478

There are three options here:

Firstly (and not so great a solution) is to select from the query results:

select * from (
 select LAT, LON, FILE from points where LAT > 30.457835
)
 where LAT != 0.0;

Second (much more efficient) is to combine the search criteria into a single query:

select LAT, LON, FILE from points where LAT > 30.457835 and  LAT != 0.0;

However in you example this would be rather pointless as anything greater than 30.457835 will definitely be not equal to zero.

A third ( and drastically inefficient ) option would be to write the results of the first query to a temporary table, then query the temporary table. The SQL for this is somewhat complex and I would recommend it only if you are running several queries against the resulting temporary table.

Upvotes: 1

Related Questions