Barand

OK. I'll assume we're starting with something like this: mysql> select * from duncan; +----+-------------+--------+ | id | pupil_name | new_id | +----+-------------+--------+ | 1 | John Smith | NULL | | 2 | Jenny Wills | NULL | | 3 | Jenny Wills | NULL | | 4 | John Smith | NULL | | 5 | Jenny Wills | NULL | | 6 | Mark Long | NULL | | 7 | John Smith | NULL | | 8 | Mark Long | NULL | | 9 | John Smith | NULL | +----+-------------+--------+ Then UPDATE duncan JOIN ( SELECT @grp := IF(pupil_name=@prev, @grp, @grp+1) as groupid, @prev:= pupil_name as pupil_name FROM duncan JOIN(SELECT @prev:='', @grp:=0) as init ORDER BY pupil_name ) as grpcalc USING (pupil_name) SET duncan.new_id = grpcalc.groupid Results in mysql> select * from duncan order by pupil_name; +----+-------------+--------+ | id | pupil_name | new_id | +----+-------------+--------+ | 2 | Jenny Wills | 1 | | 3 | Jenny Wills | 1 | | 5 | Jenny Wills | 1 | | 1 | John Smith | 2 | | 4 | John Smith | 2 | | 7 | John Smith | 2 | | 9 | John Smith | 2 | | 6 | Mark Long | 3 | | 8 | Mark Long | 3 | +----+-------------+--------+

1/10 for formatting What results are you hoping to get from that data? New id Pupil name 1 John Smith 1 John Smith 1 John Smith 1 John Smith 2 Jenny Wills 2 Jenny Wills 2 Jenny Wills 3 Mark Long 3 Mark Long 3 Mark Long It would appear that your "new id" is a group id ???

Adding an extra field to the query field list is perhaps the easiest thing you could do in a query and shouldn't be beyond the problem solving capabilities of stradsolutions dot com It does, however raise the question of which table you want to update, your original attendance table or the attendancenew table that my code creates? Are you sticking with your old format or are you intending to continue with the revised version? If you do want to continue with the new version then the conversion in the code should be a one-time operation and not run every time. As there was no mention of "id" column in your original specification the id is not transferred currently with the rest of the data. If you want to update your original table you will need to amend the query transferring the data to ensure the ids match.

What id?

Yes when calling within phpmyadmin etc or from the mysql command line but not when calling a single query using mysqli_query()

You don't want the terminating semi-colon when executing queries from PHP. Also, try error checking on the insert query $csoport_kod->query ("INSERT INTO gf1 VALUES('" . implode("','", $data) . "')") or die($csoport_kod->error);

Seems like you are going to that page twice somehow

+1 I liked to use that after just doing a SELECT COUNT() query - ie a single field, single row result set. Kristoff - I see you aren't checking if any data was actually posted. Is it the absence of data that's causing your blanks?

I had been concentrating on the joins but then had a look at what you were selecting and spotted the COUNT(). If you have an aggregation without a group by then you get a single aggregation for the whole selection. Just sorry I didn't spot it earlier for you to reduce your deadline stress level

You can put the count back if you add the GROUP BY products.id (see my edit to prev post)

Try removing count(customer_img.id) as countimg, You have an aggregation without GROUP BY. But, if you add a group by then you will get only a single row per product even if there are multiple images edit : or add GROUP BY products.id

Do the other 3 have matching records in the product table and those products have matching category records?

Steeve - My car isn't working, can you tell me how to fix it? Or would you need a bit more information on exactly how it isn't working?

I see you have a WHERE condition on a LEFT JOINed table. It needs to be in the JOIN condition Try SELECT `products`.`id`, `products`.`outprice`AS price, `products`.`vat`, `products`.`imgupload`, `products_lang`.`title`, `products_img`.`imgurl`, count(customer_img.id) as countimg, `customer_img`.`imgurl` AS c_imgurl, `categories`.`category` FROM (`products`) JOIN `customer_products`ON `customer_products`.`product` = `products`.`id` LEFT JOIN `customer_img` ON `customer_img`.`product` = `customer_products`.`product` LEFT JOIN `products_lang` ON `products_lang`.`product` = `products`.`id` AND `products_lang`.`language` = '3' LEFT JOIN `products_img` ON `products_img`.`id` = `products`.`firstimg` JOIN `categories` ON `categories`.`id` = `products`.`category` WHERE `customer_products`.`customer` = 3

you can just run it all as a single PHP script $db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE); // use your host and credentials $db->query("DROP TABLE IF EXISTS attendancenew"); // delete existing table $sql = "CREATE TABLE attendancenew ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, MemberId VARCHAR(3), TimeIn DATETIME, TimeOut DATETIME )"; $db->query($sql); $sql = "INSERT INTO attendancenew SELECT null , MemberID , CASE TimeIn WHEN 'Missed' THEN null ELSE STR_TO_DATE(CONCAT_WS(' ', Day, Month, Year, TimeIn), '%d %M %Y %T') END , CASE timeOut WHEN 'Missed' THEN null ELSE STR_TO_DATE(CONCAT_WS(' ', IFNULL(OutDay, Day), Month, Year, TimeOut), '%d %M %Y %T') END FROM attendance"; $db->query($sql); $sql = "SELECT a.MemberId, IF(a.timeout IS NULL,'Missing', a.TimeOut) as TimeOut FROM attendancenew a JOIN ( SELECT MemberId, MAX(COALESCE(TimeIn, TimeOut)) as latest FROM attendancenew GROUP BY MemberId ) as last ON a.MemberId = last.MemberId AND latest IN (a.TimeIn, a.TimeOut) "; $res = $db->query($sql); echo '<pre>'; printf("%-10s%-20s\n\n", 'MemberId', 'TimeOut'); while ($row = $res->fetch_row()) { vprintf("%-10s%-20s\n", $row); } echo '</pre>';

So assuming there's no problem getting those who attended and you have this in your log so far for the 24 Nov +--------------+----------+------------+----------+ | idattendance | idpeople | gamedate | attended | +--------------+----------+------------+----------+ | 302 | 1 | 2013-11-24 | 1 | | 303 | 3 | 2013-11-24 | 1 | | 304 | 5 | 2013-11-24 | 1 | | 305 | 7 | 2013-11-24 | 1 | | 306 | 9 | 2013-11-24 | 1 | | 307 | 10 | 2013-11-24 | 1 | | 308 | 11 | 2013-11-24 | 1 | | 309 | 12 | 2013-11-24 | 1 | | 310 | 13 | 2013-11-24 | 1 | +--------------+----------+------------+----------+ You can now run this script to add the non-attendees to the log for the 24th $gamedate = '2013-11-24'; $sql = "INSERT INTO attendance(idpeople,gamedate,attended) SELECT p.idpeople, '$gamedate', 0 FROM people p LEFT JOIN attendance a ON p.idpeople = a.idpeople AND a.gamedate = '$gamedate' WHERE a.idpeople IS NULL"; $db->query($sql); Which now gives +--------------+----------+------------+----------+ | idattendance | idpeople | gamedate | attended | +--------------+----------+------------+----------+ | 302 | 1 | 2013-11-24 | 1 | | 303 | 3 | 2013-11-24 | 1 | | 304 | 5 | 2013-11-24 | 1 | | 305 | 7 | 2013-11-24 | 1 | | 306 | 9 | 2013-11-24 | 1 | | 307 | 10 | 2013-11-24 | 1 | | 308 | 11 | 2013-11-24 | 1 | | 309 | 12 | 2013-11-24 | 1 | | 310 | 13 | 2013-11-24 | 1 | | 317 | 2 | 2013-11-24 | 0 | | 318 | 4 | 2013-11-24 | 0 | | 319 | 6 | 2013-11-24 | 0 | | 320 | 8 | 2013-11-24 | 0 | +--------------+----------+------------+----------+

Jayden_Blade Did you. You really should take your own advice $gamedate = DateTime('2013-11-24'); //--> Fatal error: Call to undefined function DateTime() However, if you meant $gamedate = new DateTime('2013-11-24'); $sql = "SELECT people.* FROM people LEFT JOIN attendance ON people.idpeople = attendance.idpeople WHERE people.idpeople IS NULL AND attendance.gamedate = $gamedate"; // --> Catchable fatal error: Object of class DateTime could not be converted to string Not helping much, are you?

I've cleaned up the provided data MemberId TimeIn TimeOut Day OutDay Month Year ----------+----------+-----------+-----+-------+---------+--------+ 007 12:32:50 Missed 16 November 2013 007 Missed 12:34:45 16 November 2013 004 11:25:12 Missed 19 November 2013 009 13:39:2 Missed 19 November 2013 006 11:1:44 Missed 20 November 2013 006 11:2:40 Missed 20 November 2013 006 Missed 11:2:57 20 November 2013 007 11:52:42 Missed 23 November 2013 008 15:7:47 Missed 20 November 2013 You certainly don't believe in structuring your data to make life easy for yourself, do you? You data as it is is unusable for this task. 1. You are using month names instead of numbers. If you had a December in there it would sort before November, so sorting by date is out. 2. 11:2:40 would sort after 11:15:00 so time sorting too is out. Store your dates in a single DATE type field (format yyyy-mm-dd) and store your times in TIME type fields (format hh:mm:ss). Then they become usable for sorts and comparisons and use by MySQL date/time functions. Or use combined DATETIME fields This should help. You currently have +----+----------+----------+----------+------+--------+----------+------+ | id | MemberId | TimeIn | TimeOut | Day | OutDay | Month | Year | +----+----------+----------+----------+------+--------+----------+------+ | 1 | 007 | 12:32:50 | Missed | 16 | NULL | November | 2013 | | 2 | 007 | Missed | 12:34:45 | 16 | NULL | November | 2013 | | 3 | 004 | 11:25:12 | Missed | 19 | NULL | November | 2013 | | 4 | 009 | 13:39:2 | Missed | 19 | NULL | November | 2013 | | 5 | 006 | 11:1:44 | Missed | 20 | NULL | November | 2013 | | 6 | 006 | 11:2:40 | Missed | 20 | NULL | November | 2013 | | 7 | 006 | Missed | 11:2:57 | 20 | NULL | November | 2013 | | 8 | 007 | 11:52:42 | Missed | 23 | NULL | November | 2013 | | 9 | 008 | 15:7:47 | Missed | 20 | NULL | November | 2013 | +----+----------+----------+----------+------+--------+----------+------+ If you then run these two queries CREATE TABLE attendancenew ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, MemberId VARCHAR(3), TimeIn DATETIME, TimeOut DATETIME ); INSERT INTO attendancenew SELECT null , MemberID , CASE TimeIn WHEN 'Missed' THEN null ELSE STR_TO_DATE(CONCAT_WS(' ', Day, Month, Year, TimeIn), '%d %M %Y %T') END , CASE timeOut WHEN 'Missed' THEN null ELSE STR_TO_DATE(CONCAT_WS(' ', IFNULL(OutDay, Day), Month, Year, TimeOut), '%d %M %Y %T') END FROM attendance; You get a usable table like this +----+----------+---------------------+---------------------+ | id | MemberId | TimeIn | TimeOut | +----+----------+---------------------+---------------------+ | 1 | 007 | 2013-11-16 12:32:50 | NULL | | 2 | 007 | NULL | 2013-11-16 12:34:45 | | 3 | 004 | 2013-11-19 11:25:12 | NULL | | 4 | 009 | 2013-11-19 13:39:02 | NULL | | 5 | 006 | 2013-11-20 11:01:44 | NULL | | 6 | 006 | 2013-11-20 11:02:40 | NULL | | 7 | 006 | NULL | 2013-11-20 11:02:57 | | 8 | 007 | 2013-11-23 11:52:42 | NULL | | 9 | 008 | 2013-11-20 15:07:47 | NULL | +----+----------+---------------------+---------------------+ Now it is processable for what you want with this query SELECT a.MemberId, IF(a.timeout IS NULL,'Missing', a.TimeOut) as TimeOut FROM attendancenew a JOIN ( SELECT MemberId, MAX(COALESCE(TimeIn, TimeOut)) as latest FROM attendancenew GROUP BY MemberId ) as last ON a.MemberId = last.MemberId AND latest IN (a.TimeIn, a.TimeOut) +----------+---------------------+ | MemberId | TimeOut | +----------+---------------------+ | 004 | Missing | | 009 | Missing | | 006 | 2013-11-20 11:02:57 | | 007 | Missing | | 008 | Missing | +----------+---------------------+

Does the table have a datetime value so you can determine which is the LAST record for 007? What does your table look like?

see http://www.php.net/manual/en/features.file-upload.post-method.php

And you don't seem to realize that the table I am using does not contain the rank either. That is why am using the subquery to calculate @rank dynamically from the scores in a single query. The subquery effectively creates a table that DOES contain rank.

Sounds like you need to select those WHERE city = '$cityvalue' AND something = $dropdownvalue

For the interval format you need format('%a') eg $goalDate = '2013-06-20'; $objToday = new DateTime(); $objStart = new DateTime($goalDate); $objDuration = $objStart->diff($objToday, true); echo 'Days since start date: ' . $objDuration->format('d'); // --> d echo 'Days since start date: ' . $objDuration->format('%d'); // --> 4 (days remaining after months calculated) echo 'Days since start date: ' . $objDuration->format('%a'); // --> 157 (total days) // or echo 'Days since start date: ' . $objDuration->days; // --> 157

You need to normalize the period and category fields in the entries table storing each id (without the b,e and \n) in separate rows in separate tables. CREATE TABLE IF NOT EXISTS `mod_music_entry_periods` ( `id` int(1) NOT NULL AUTO_INCREMENT, `entryid` int(11) NOT NULL DEFAULT '0', `periodid` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; CREATE TABLE IF NOT EXISTS `mod_music_entry_categories` ( `id` int(1) NOT NULL AUTO_INCREMENT, `entryid` int(11) NOT NULL DEFAULT '0', `categoryid` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; So your relationship model would look like the attached

You are using LEFT JOIN so that will return all rows from mod_music_category, matched or not. Where there is a match you will get the data from the matched mod_music_entries records otherwise NULL in the fields from that table. If you only want matched category records returned, use INNER JOIN.