Using multiple SELECT statements to narrow results

Question

I have a database with some properties that have been sold. They each have a date associated with it which is what I need to use to narrow the search results.

Basically I have a query:

$query = "SELECT * from newsales WHERE city = '".$_GET['location']."'";

And what I need to do is, from the results returned from the above query, I need to further narrow it down to be within the past 90 days.

So it'll find the city, and then it needs to ONLY get the ones from the last 90 days. How do I combine SELECT statements to narrow the results down?

Thanks!

score 3 · Accepted Answer · edited Aug 12 '13 at 17:51

3

You could use the SQL AND operator.

Doc: http://dev.mysql.com/doc/refman/5.1/en/logical-operators.html

Your query would be than:

$query = "SELECT * from newsales WHERE city = '".$_GET['location']."' AND date > '".$oldestdate."';

Set $date to the oldes date, in your case 90 days before today. According to your dateformat in your mysql database you have to calculate this in a timestamp or datetime.

edited Aug 12 '13 at 17:51

Sylvain Leroux

50,096
7
103
125

answered Aug 12 '13 at 17:15

Reflic

1,411
15
25

3

In case it's not clear, this is in no way MySQL-specific syntax, it's one of the most basic parts of SQL. – IMSoP Aug 12 '13 at 17:17
1

@IMSoP I took the liberty to edit the answer clarify that point. – Sylvain Leroux Aug 12 '13 at 17:52

score 3 · Answer 2 · edited May 23 '17 at 10:28

3

You don't need to combine SELECT statements, just make a more complex WHERE clause using the boolean AND operator:

$query = "
    SELECT * 
    FROM newsales 
    WHERE 
        city = '".$_GET['location']."'
        AND date > '".$oldestdate."'
";

I'd advise you to read up on SQL injection as well - if you use $_GET directly like that, someone can come to your website and basically type in any SQL statement they want.

The easiest way, assuming you are using mysqli_* functions (which have replaced the mysql_* functions but can be used mostly interchangeably) is mysqli_real_escape_string(), e.g. city = '" . mysqli_real_escape_string($_GET['location']) . "'.

edited May 23 '17 at 10:28

Community

1
1

answered Aug 12 '13 at 17:16

IMSoP

89,526
13
117
169

added some saver code that is less prone to SQL injections like MSoP talked about it. this way is more clear what to do if the topicstart is using mysql_ functions. PDO and prepare for select query is not always better in terms of performance two round trips are needed now to select simple data – Raymond Nijland Aug 12 '13 at 17:46
Well, they shouldn't be using `mysql_*` functions anyway, as they're deprecated, but `mysqli_*` can be used in basically the same way as I understand it. Not sure whether performance is a particularly strong argument against parameterised queries (they can massively improve things by caching a query plan) but definitely agree they're not a silver bullet, and nor is it necessary to move to PDO if you're not interested in its other features. – IMSoP Aug 12 '13 at 19:09

score -1 · Answer 3 · answered Aug 12 '13 at 17:14

-1

Combining results of multiple SELECT statements means you need to use UNION. Please see this : http://dev.mysql.com/doc/refman/5.0/en/union.html

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

answered Aug 12 '13 at 17:14

Ali Gajani

14,762
12
59
100

True if combining select results is actually what is needed, but the question makes clear the OP is actually trying to narrow results down. – IMSoP Aug 12 '13 at 17:18
Try to avoid UNION's as much as possible because they always require a temp table to process. it's bad if this will result into a disk based temp table and this will kill MySQL performance – Raymond Nijland Aug 12 '13 at 17:42

Using multiple SELECT statements to narrow results

3 Answers3