Barand

Found 25 of them SELECT lot.lot_id , lot.dried_in_date , s.complete_date , work_type_id , work_type_description FROM schedule s INNER JOIN lot USING (lot_id) INNER JOIN work_type USING (work_type_id) WHERE dried_in_date > complete_date; +--------+---------------+---------------+--------------+-----------------------+ | lot_id | dried_in_date | complete_date | work_type_id | work_type_description | +--------+---------------+---------------+--------------+-----------------------+ | 1219 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1220 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1221 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1222 | 2015-04-03 | 2015-03-27 | 1 | Tile | | 1223 | 2015-04-03 | 2015-03-27 | 1 | Tile | | 1269 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1270 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1271 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1272 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 11 | 2015-01-05 | 2013-09-03 | 1 | Tile | | 140 | 2015-11-12 | 2015-03-17 | 1 | Tile | | 144 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 145 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 146 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 264 | 2015-03-24 | 2015-03-17 | 1 | Tile | | 386 | 2016-01-30 | 2015-03-17 | 1 | Tile | | 661 | 2015-04-09 | 2015-03-17 | 1 | Tile | | 843 | 2015-08-19 | 2015-03-17 | 1 | Tile | | 1273 | 2015-04-03 | 2015-0317 | 1 | Tile | | 2300 | 2015-08-14 | 2015-05-29 | 1 | Tile | | 843 | 2015-08-19 | 2015-03-17 | 2 | Backsplash | | 1537 | 2015-05-11 | 2015-05-08 | 2 | Backsplash | | 1219 | 2015-04-03 | 2015-03-25 | 4 | Bath tile | | 2300 | 2015-08-14 | 2015-05-19 | 15 | Window sills | | 1932 | 2015-09-01 | 2015-08-24 | 15 | Window sills | +--------+---------------+---------------+--------------+-----------------------+ 25 rows in set (0.05 sec)

By your rules, a lot remains i the dried_in state until the week the tiling is completed. In the case of the first lot (187) there is no such subsequent date and it will therefore remain as dried_in for an infinite period. Right now I am finding it very difficult to summon up any enthusiasm to give up my time when you cannot be bothered to produce test data that is fit for purpose. There is insufficient schedule data to test all situations.

How are you storing the date in your db table? It should be type DATE, format YYYY-MM-DD.

your data would suggest otherwise

What should happen if the tiling is completed before both the slab and the dried in processes happen? (As in your data)

I am using the data you posted in reply #8. I am trying to understand your convoluted WHERE clause regarding the schedule data (with its double NOT NOT's) and how your query moves the dried_in dates from one week to another. Regarding the week start and end dates - did you not read my reply #7?

Looking at the lot data you have provided there is clearly 1 dried_in date in week 1, another in week 2 and none in week 3 so how do you get counts of 0, 1, 1 for those three weeks? SELECT lot_id , slab_date , DATE_FORMAT(slab_date, '%V') as slab_week , dried_in_date , DATE_FORMAT(dried_in_date, '%V') as dried_week , contract_type_id FROM lot INNER JOIN block USING (block_id) INNER JOIN community USING (community_id) ORDER BY lot_id, slab_date, dried_in_date; +--------+------------+-----------+---------------+------------+------------------+ | lot_id | slab_date | slab_week | dried_in_date | dried_week | contract_type_id | +--------+------------+-----------+---------------+------------+------------------+ | 187 | 2015-01-07 | 01 | 2015-01-08 | 01 | 1 | | 192 | 2015-01-12 | 02 | NULL | NULL | 1 | | 193 | 2015-01-13 | 02 | 2015-01-15 | 02 | 3 | | 194 | 2015-01-19 | 03 | NULL | NULL | 3 | | 195 | 2015-01-07 | 01 | NULL | NULL | 1 | | 196 | 2015-01-19 | 03 | NULL | NULL | 3 | | 197 | 2015-01-07 | 01 | NULL | NULL | 2 | +--------+------------+-----------+---------------+------------+------------------+

It looks like your coding is is from the 1990's when register_globals were used. Those param values need to come from the $_POST array. And if your quantity and price columns really are string types then you need to redesign your table.

Can you please explain, in English rather than SQL, how the dried_in count should work and the relationship between dried_in and the schedule. Things became a lot clearer after your previous explanation of slab versus dried_in.

Revised version to give you the week start and end dates for the chart. You need to pass it the date (2015-01-04) of the first day of the first week. SELECT s.week , @slab:=@slab+slabcount-@dried as slab_count , @dried:=driedcount as dried_in_count , @day1 + INTERVAL (s.week-1)*7 DAY as wkcomm , @day1 + INTERVAL (s.week-1)*7 + 6 DAY as wkend FROM ( SELECT COUNT(lot_id) as slabcount , week(slab_date) as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) INNER JOIN (SELECT @wk:=0, @slab:=0, @dried:=0) init WHERE YEAR(slab_date)=2015 GROUP BY week ) s INNER JOIN (SELECT @wk:=0, @slab:=0, @dried:=0, @day1:='2015-01-04') init LEFT JOIN ( SELECT COUNT(lot_id) as driedcount , week(dried_in_date) as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE YEAR(dried_in_date)=2015 GROUP BY week ) d using (week) ;

Make the date format Y-m-d and NOT d-m-Y

this should give you both the slab count and the dried_in count SELECT s.week , @slab:=@slab+slabcount-@dried as slab_count , @dried:=driedcount as dried_in_count FROM ( SELECT COUNT(lot_id) as slabcount , week(slab_date) as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) INNER JOIN (SELECT @wk:=0, @slab:=0, @dried:=0) init WHERE YEAR(slab_date)=2015 GROUP BY week ) s INNER JOIN (SELECT @wk:=0, @slab:=0, @dried:=0) init LEFT JOIN ( SELECT COUNT(lot_id) as driedcount , week(dried_in_date) as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE YEAR(dried_in_date)=2015 GROUP BY week ) d using (week) ;

You have $stocktakename=$_POST['StocktakeName]; but that does not match the name in your form <input name="stocktakename" type="text" required="required" id="stocktakename" size="50">

I also rewrote it as a single select but, as I got counts of 2,2,2 for each week and couldn't reproduce the "correct" results of 2,3,4 I gave up.

SELECT whatever FROM mytable WHERE mycol BETWEEN '1A' AND '5D'

You need to put in some debug code, such as this to check the POST data echo '<pre>',print_r($_POST, true),'</pre>'; and also check if the query was successful if (!$resultSet) die($mysqli->error);

Sorry, I have no psychic powers, and, as I cannot see your screen from here, you'll have to post the query and the exact error message. Also, post your table structures with exact column names. Use this type of query for each table SHOW CREATE TABLE users;

given the structure is +-------------+ +-------------+ +--------------+ | rank | | user | | group | +-------------+ +-------------+ +--------------+ | rank_id(PK) |-----+ | user_id(PK) |------+ +-------| group_id(PK) | | rankname | | | username | | | | groupname | +-------------+ +----<| user_rank | | +--------------+ | +--------------+ +-------------+ | | user_group | | | +--------------+ | +----<| user_id(PK) | | | group_id(PK) |>--+ +--------------+ then you could SELECT g.groupname , r.rank_id , r.rankname , u.username FROM user_group ug INNER JOIN group g USING (group_id) INNER JOIN user u USING (user_id) INNER JOIN rank r ON u.user_rank = r.rank_id WHERE ug.group_id = 34 ORDER BY u.rank_id, u.username

If you want help we still need to know the structure of your db tables. The deprecated code you are using is the mysql_ library. You need to change to mysqli_ or PDO functions, both of which should be available in your version of PHP

I'd start by checking that your SESSION variables are storing what you expect them to be storing.

Yes. SELECT member_name , studs.member_id , GROUP_CONCAT(school_name, '(', s.school_id, ')' SEPARATOR ', ') as schools FROM ( SELECT member_name , member_id FROM members WHERE created_by = 10 AND role_id = 2 UNION -- -- select students from schools -- created by admin #10 -- SELECT member_name , m.member_id FROM members m INNER JOIN school_members sm USING (member_id) INNER JOIN schools s USING (school_id) WHERE s.created_by=10 AND m.role_id = 2 UNION -- -- select students from schools -- where admin #10 is a member -- SELECT member_name , m.member_id FROM members m INNER JOIN school_members sm1 ON m.member_id = sm1.member_id INNER JOIN school_members sm2 ON sm1.school_id = sm1.school_id WHERE sm2.member_id = 10 AND m.role_id = 2 ) studs LEFT JOIN school_members USING (member_id) LEFT JOIN schools s USING (school_id) GROUP BY member_name ;

The second "WHERE" in the query needs to be "AND"

I agree with Benanamen. The model I used assumes a member can have only one role (Admin or Student) and is attached. The query to get your list would be a union of the three types of selection that you want. -- -- select students created by Admin #10 -- SELECT member_name FROM members WHERE created_by = 10 AND role_id = 2 UNION -- -- select students from schools -- created by admin #10 -- SELECT member_name FROM members m INNER JOIN school_members sm USING (member_id) INNER JOIN schools s USING (school_id) WHERE s.created_by=10 AND m.role_id = 2 UNION -- -- select students from schools -- where admin #10 is a member -- SELECT member_name FROM members m INNER JOIN school_members sm1 ON m.member_id = sm1.member_id INNER JOIN school_members sm2 ON sm1.school_id = sm1.school_id WHERE sm2.member_id = 10 AND m.role_id = 2 ;

In that case you need to connect twice to the user_role table SELECT users.uid, users.`name`, users.mail, users.created, ur1.rid as rid1, ur2.rid as rid2, field_data_field_first_name.field_first_name_value, field_data_node_venue_region.node_venue_region_tid FROM users INNER JOIN users_roles ur1 ON users.uid = ur1.uid AND ur1.rid = 7 INNER JOIN users_roles ur2 ON users.uid = ur2.uid AND ur2.rid = 8 INNER JOIN field_data_field_first_name ON field_data_field_first_name.entity_id = users.uid INNER JOIN field_data_node_venue_region ON field_data_node_venue_region.entity_id = users.uid