PHP/MySQL - SELECT from various tables, with a list of indexes

Question

Hi folks... im with a hugh question...

Here is the scenario.

Im developing a timeclock system, i have these tables:

-punch (id_punch,date)
-in1 (id_in1,time,id_punch,...)
-in2 (id_in2,time,id_punch,...)
.
-in6  (id_in6,time,id_punch,...)
-out1 (id_out1,time,id_punch,...)
-out2 (id_out2,time,id_punch,...)
.
-out6 (id_out6,time,id_punch,...)

My question is, how i can with only one query in PHP to get all values from in and out table, from a list of id_punch values, for exemple:

Get all punchs of september. or Get all punchs of july to dezember,

I mean... from a list of id_punch between two dates, get all the results from the in, out table.

The only way i think is to do a query with each id_punch variable, but in a month its about 20-25 queries... to much dont? Tkz Roberto

Answer 1

Your database schema is a little unclear, but if you're asking how to get the results corresponding to a list of ids you already have this should work (assuming your ids are 1,3,5,7,9)

SELECT * FROM table1, table2, table 3
WHERE table1.punch_id = table2.punch_id AND table2.punch_id = table3.punch_id AND table3.punch_id IN (1,3,5,7,9)

you'll probably need to modify it just make sure every table's punch_id is joined to that IN constraint

Answer 2

To get all the data from the tables you'll need to join them with JOIN MySQL JOIN But from what I can gather by looking at you tables, you probably should be thinking about making this into one table rather than the multiple tables you have here.

Answer 3

I can't use one table cause i have some informations in each punch, as ipaddress, and other information.

Neil, the answer was in my nose, i already saw a solution like yours, but my doubt is how to put the list in the query, answer for my own question = use a foreach() in php to "populate" this list...

Something like:

> SELECT * FROM table1, table2, table 3 WHERE table1.punch_id = table2.punch_id AND table2.punch_id = table3.punch_id AND table3.punch_id IN (<? foreach($query->results() as $row) echo $row->id_punch;?>)

im using codeigniter

Answer 4

You really need to store all the in/out data in one table that is a child of punch:

CREATE TABLE punch (
  id_punch SERIAL PRIMARY KEY,
  punch_date DATE NOT NULL,
  ip_address INT UNSIGNED NOT NULL
  -- plus other attributes
) ENGINE=InnoDB;

CREATE TABLE inout (
  id_punch BIGINT UNSIGNED,
  in_time TIME NOT NULL,
  out_time TIME NULL,
  PRIMARY KEY (id_punch, in_time),
  FOREIGN KEY (id_punch) REFERENCES punch (id_punch)
) ENGINE=InnoDB;

Now you can query very easily for all punches in September:

SELECT *
FROM punch LEFT OUTER JOIN inout USING (id_punch)
WHERE EXTRACT(YEAR_MONTH FROM punch_date) = '200909';