Mysql for each different result in query, run another query

Question

How do you write a query that executes for each different result in a query? To explain, I run one query to find all the "punch_periods" within a date range:

$search_date = mysql_query("SELECT punch_period 
                            FROM timecards 
                            WHERE emp_id ='1' && 
                            punch_time BETWEEN '$start' AND '$end' 
                            ORDER BY punch_time"); 

while($periods = mysql_fetch_array($search_date)) {

    // run another query for each different punch_period

    }

The result might be 1,1,1,1,2,2. Instead of running a second query 6 times (for each result), I need to run a second query 2 times (for each DIFFERENT punch_period). It will be something like:

$punches = mysql_query("SELECT * FROM timecards 
                        WHERE emp_id ='1' && 
                        punch_period = [RESULT FROM 1ST QUERY]");

The reason I'm doing it this way is because I can't search by date alone- someone may clock in at 11:30pm one day and clock out at 1:00am the following "day". By searching by unique punch periods, I can get total hours worked in a "workday" even if it overlaps a true calendar "day".

use group by punch_period in your query so that it can produce the record once for repeated punch_period. — Punit
– Punit, Commented Oct 10, 2011 at 18:17

Johan · Accepted Answer · 2011-10-10 18:30:30Z

3

I fail to see the purpose of the while loop.
You are just pinging the database server.
It be much faster to get all rows in one query using a subselect

SELECT DISTINCT tc.* FROM timecards tc
WHERE tc.emp_id = '1' 
  AND tc.punch_period IN (SELECT DISTINCT tc2.punch_period 
                       FROM timecards tc2
                       WHERE tc2.emp_id ='1' 
                         AND tc2.punch_time BETWEEN '$start' AND '$end')
ORDER BY punch_time

Obviously you can simplify this query to:

SELECT DISTINCT tc.* FROM timecards tc
WHERE tc.emp_id = '1'
  AND tc.punch_time BETWEEN '$start' AND '$end'

SELECT * is not recommended
Note that SELECT * is considered an anti-pattern and it is better to explicitly name the fields that you want to select.

SELECT tc.id, tc.punch_time, tc.punch_period ....

Mixing && and AND
Don't mix && and AND, just use AND the mix is confusing and && is not standard SQL IIRC.

answered Oct 10, 2011 at 18:30

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

phil Over a year ago

+1 for SELECT * anti-pattern, you cannot stress that enough :)

Oseer Over a year ago

I don't get all these prefixes everyone is putting on their examples. What's with the tc., tc2., etc.? I'm just trying to understand what these prefixes are for...

Johan Over a year ago

They are not prefixes, they are aliases. You use aliases for a table because otherwise it would be impossible to refer to table1 twice, now you can refer to table1 (first instance) by calling it t1 and refer to the 2nd instance by calling that t2. It also allows you to shorten the tablenames, so you can type tc, instead of timecards, much shorter. And because all good programmers are lazy this is vital for our mental health.

Johan · Accepted Answer · 2011-10-10 18:20:58Z

0

You can simply use 'in' operator.

$punches = mysql_query("SELECT * FROM timecards 
                        WHERE emp_id ='1' && 
                        punch_period in ( RESULT FROM 1ST QUERY)");

edited Oct 10, 2011 at 18:20

Johan

77.4k28 gold badges204 silver badges346 bronze badges

answered Oct 10, 2011 at 18:13

Nishu Tayal

20.9k8 gold badges53 silver badges105 bronze badges

2 Comments

Oseer Over a year ago

Wouldn't that still do (in the example above) 6 queries?

Nishu Tayal Over a year ago

SELECT * FROM timecards WHERE emp_id ='1' && punch_period in (SELECT punch_period FROM timecards WHERE emp_id ='1' && punch_time BETWEEN '$start' AND '$end' ORDER BY punch_time))................... see it, here u have got all punch_period value, that is passed in "in" and in this query, you can get all the result... is it same that you need?

Collectives™ on Stack Overflow

Mysql for each different result in query, run another query

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related