Barand

You should NOT be storing them with comma separators, you should be storing them as numeric types. If there are no decimals, you could use INT, Commas and any other formatting should be added on output.

You need to use the DATEDIFF() function http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_datediff but the best way to do it is not to do it at all. Derived data should not be stored in your database. Calculate it when required instead of continually updating the table.

That earlier post was an example. Here's another http://forums.phpfreaks.com/topic/262473-pivot-table-like-output-indefinite-rows-and-columns-from-flat-data/?do=findComment&comment=1345109 Or do you mean an example which uses your data so that I write your code for you?

With my limited knowledge of your processes and data there is little help I can offer. You need sufficient common data in the two tables to be able to match one with the other so you can determine if a record is present or not.

Reply #13 above demonstrates the method. Dynamic values would come from your db data.

I don't know what you have on on lines 1 and 2 but suspect you may be missing ";" at end of a line.

That's right. I suggest you read my first reply (#2) again and study the attached diagram. You have a search period From - To. A room is already booked if it was checked in before the end of the search period (To) AND it is checked out after the start (From) of the search period In other words, if the booking period of the room overlaps the search period.

Should be. These are my reservations +----+---------+------------+------------+------+---------------------+ | id | room_id | checkin | checkout | ip | date | +----+---------+------------+------------+------+---------------------+ | 1 | 6 | 2016-03-01 | 2016-03-05 | NULL | 2016-03-10 18:08:16 | | 2 | 2 | 2016-03-02 | 2016-03-06 | NULL | 2016-03-08 15:12:34 | | 3 | 4 | 2016-03-03 | 2016-03-07 | NULL | 2016-03-10 18:08:16 | <-booked | 4 | 3 | 2016-03-06 | 2016-03-07 | NULL | 2016-03-10 18:08:16 | <-booked | 5 | 5 | 2016-03-07 | 2016-03-08 | NULL | 2016-03-08 16:07:57 | <-booked | 6 | 1 | 2016-03-08 | 2016-03-10 | NULL | 2016-03-10 18:08:16 | | 7 | 7 | 2016-03-08 | 2016-03-09 | NULL | 2016-03-08 15:12:34 | | 8 | 8 | 2016-03-09 | 2019-03-10 | NULL | 2016-03-08 15:12:34 | +----+---------+------------+------------+------+---------------------+ If I want to check in on the 6th and out on the 8th then the rooms indicated are already booked (rooms 3, 4 and 5) SELECT * FROM rooms WHERE room_id NOT IN ( SELECT room_id FROM reservations WHERE checkin < '2016-03-08' AND checkout > '2016-03-06' ); +---------+-----------+-------------+---------------+-------------+ | room_id | room_name | room_number | room_capacity | room_status | +---------+-----------+-------------+---------------+-------------+ | 1 | Deluxe | 101 | 2 | 1 | | 2 | Standard | 102 | 2 | 2 | rooms 3,4,5 | 6 | Executive | 203 | 1 | 1 | not available | 7 | Executive | 301 | 2 | 2 | | 8 | Standard | 302 | 2 | 3 | | 9 | Executive | 303 | 1 | 3 | | 10 | Suite | 401 | 6 | 5 | | 11 | Suite | 501 | 4 | 2 | +---------+-----------+-------------+---------------+-------------+

No - it is occupied from 12th to 15th. It becomes available from the 15th

OK, once again I had Sn < To AND En > From You have Sn < From AND En > To Can you spot the difference yet?

I was thinking you could match on idtimetable but I notice it is autoincrement in both tables and not a foreign key, so that is out of the question. Plus you have no dates to match on. Sorry but I cannot see any obvious way that your data structure supports the processing that you require.

you are still making exactly the same mistake

Not instantly obvious, as there is data entered in the form that is not in that table and data in that table that is not in the form. So the extra data in the table has to come from somewhere. I guess there is more to this process than "The teacher fills in a form". More confusing as "grupo" has become a time field in the "apoios" table. You can probably do something with day of week from apoios date field but not sure what your day data looks like in the timetable data. From what you have said so far (not much) there is little data in common between those two tables from the process as described so far. Where, for instance, does the timetable (primary key ???) appear from. If you wanted someone to write a program to handle this process, do you think that they could do it from the description you have given me, namely

Look more carefully at the condition dates in my post You have Sn < From AND En > To Use DATE types and not DATETIME as unwanted time values can distort comparisons

So having filled in a form, what happens to that data?

If you are using LEFT JOIN, yes. Try it with an index on factor_times.status PS. shouldn't this be "<"

Good question, and one that is impossible for us to answer from the scant information you have given us. Define "register a summary". What are the processes and data involved in the registration?

You would put it in the SELECT clause. Also note you cannot use aliases in WHERE clauses SELECT groups.grid , groups.code1 , products.codeDX , products.CodeSX , products.prodid , products.groupid , CONCAT (code1, ".", codeDX) AS op1 , CONCAT (code1, ".", codeSX) AS op2 FROM groups LEFT JOIN products ON groups.grid = products.groupid WHERE (CONCAT(code1, ".", codeDX) LIKE '%$trimm%' OR CONCAT(code1, ".", codeSX) LIKE '%$trimm%' ) ORDER BY groups.grid

None of these values you mention in the results (12869, CST, HWH, Weekly, DURG, KHARAGPUR) appear in the data dump you provided, so there is no way your query produced those results. You say stationid1 = DURG, yet the ids are numeric in the data. And what about the other 5 values in the query? So, when you can you can provide accurate, meaningful information I will help. Until then I am not going to waste any more of my time

You still haven't said what values you used when your query doesn't work

A couple of notes on your tables you should have a table containing "room_status", each row having and an id and status description the id of the status should be stored in the room table, not the description. the status should not be in the reservation table (it's a property of a room) You would use the status table to construct your room status menu.

If you look at the attached diagram, it shows that room 2, 3, 4 and 5 are not available between dates From and To. The condition that these rooms have in common is Sn < To AND En > From

As we have have no idea about how your data is stored what data you have in your table what values you pass to the query what makes you think we can help? We cannot look over your shoulder to see what you are doing. If you post an SQL dump of "entry_ir_route" table then I'll have a look. [edit] Also what are the values in those variables you are using in the query?

Does that make it clearer what he means by duplicate json_decode() ?

From what you said I would expect tables like this +-----------+ +-----------+ +-----------+ | station | | timetable | | train | +-----------+ +-----------+ +-----------+ | stationid |---+ | tt_id | +-----| trainid | | name | +-----<| stationid | | | type | +-----------+ | trainid |>---+ +-----------+ | time | +-----------+ so if your train schedule looks like this +-------------+-------+-------+-------+ | Station | T1 | T2 | T3 | +-------------+-------+-------+-------+ | Station A | 10:00 | 11:00 | 12:00 | | Station B | 10:20 | - | - | | Station C | 10:50 | - | 12:45 | | Station D | 11:10 | - | - | | Station E | 11:30 | 12:15 | 13:25 | +-------------+-------+-------+-------+ then the ttable data would be +-------+-----------+---------+----------+ | tt_id | stationid | trainid | time | +-------+-----------+---------+----------+ | 1 | A | 1 | 10:00:00 | | 2 | B | 1 | 10:20:00 | | 3 | C | 1 | 10:50:00 | | 4 | D | 1 | 11:10:00 | | 5 | E | 1 | 11:30:00 | | 6 | A | 2 | 11:00:00 | | 7 | E | 2 | 12:15:00 | | 8 | A | 3 | 12:00:00 | | 9 | C | 3 | 12:45:00 | | 10 | E | 3 | 13:25:00 | +-------+-----------+---------+----------+ and your query would be SELECT trainid FROM ttable a INNER JOIN ttable b USING (trainid) WHERE a.stationid = 'A' AND b.stationid = 'C'; +---------+ | trainid | +---------+ | 1 | | 3 | +---------+