Barand

Like this? You need to include those "unknown columns" in the intermediate subqueries SELECT yearid , semesterid , regno , subjectid , rank , total , grade , comment FROM ( SELECT yearid , semesterid , subjectid , @seq := CASE WHEN yss <> @prev THEN 1 ELSE @seq + 1 END as seq , @rank := CASE WHEN total = @prevtot THEN @rank ELSE @seq END as rank , @prevtot := total as total , @prev := yss as yss , regno , armsLevelId -- added , armsid -- added FROM ( SELECT yearid , semesterid , subjectid , concat(yearid, semesterid, subjectid) as yss , regno , total , armsLevelId -- added , armsid -- added FROM subject_position ORDER BY yearid, semesterid, subjectid, total DESC ) sorted JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init ) ranked JOIN grade ON total BETWEEN grade.lomark AND grade.himark WHERE regNo='4663' -- A and armsLevelId='1' -- D and armsId='1' -- D and semesterid='1' -- E and yearid='1' -- D ORDER BY subjectid -- . Giving +--------+------------+-------+-----------+------+-------+-------+-------------+ | yearid | semesterid | regno | subjectid | rank | total | grade | comment | +--------+------------+-------+-----------+------+-------+-------+-------------+ | 1 | 1 | 4663 | 1 | 2 | 72 | B2 | V Good | | 1 | 1 | 4663 | 2 | 2 | 47 | D7 | Pass | | 1 | 1 | 4663 | 3 | 1 | 82 | A | Excellent | | 1 | 1 | 4663 | 4 | 1 | 99 | A* | Distinction | | 1 | 1 | 4663 | 5 | 2 | 70 | B2 | V Good | | 1 | 1 | 4663 | 6 | 1 | 69 | C1 | Good | | 1 | 1 | 4663 | 7 | 2 | 77 | B1 | V Good | | 1 | 1 | 4663 | 8 | 2 | 58 | D2 | Pass | | 1 | 1 | 4663 | 9 | 1 | 96 | A* | Distinction | | 1 | 1 | 4663 | 10 | 1 | 78 | B1 | V Good | | 1 | 1 | 4663 | 11 | 2 | 48 | D7 | Pass | | 1 | 1 | 4663 | 12 | 2 | 69 | C1 | Good | +--------+------------+-------+-----------+------+-------+-------+-------------+

Try this SELECT yearid , semesterid , subjectid , regno , rank , total , grade , comment FROM ( SELECT yearid , semesterid , subjectid , @seq := CASE WHEN yss <> @prev THEN 1 ELSE @seq + 1 END as seq , @rank := CASE WHEN total = @prevtot THEN @rank ELSE @seq END as rank , @prevtot := total as total , @prev := yss as yss , regno FROM ( SELECT yearid , semesterid , subjectid , concat(yearid, semesterid, subjectid) as yss , regno , total FROM subject_position ORDER BY yearid, semesterid, subjectid, total DESC -- LIMIT 18446744073709551615 -- MariaDB requires this line uncommented * ) sorted JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init ) ranked JOIN grade ON total BETWEEN grade.lomark AND grade.himark; (I invented my own grades) mysql> select * from grade; +----+-------+--------+--------+-------------+ | id | grade | lomark | himark | comment | +----+-------+--------+--------+-------------+ | 1 | A* | 91 | 100 | Distinction | | 2 | A | 80 | 90 | Excellent | | 3 | B1 | 75 | 79 | V Good | | 4 | B2 | 70 | 74 | V Good | | 5 | C1 | 68 | 69 | Good | | 6 | C2 | 66 | 67 | Good | | 7 | C3 | 64 | 65 | Good | | 8 | C4 | 61 | 63 | Good | | 9 | D1 | 59 | 60 | Pass | | 10 | D2 | 57 | 58 | Pass | | 11 | D3 | 55 | 56 | Pass | | 12 | D4 | 53 | 54 | Pass | | 13 | D5 | 51 | 52 | Pass | | 14 | D6 | 49 | 50 | Pass | | 15 | D7 | 40 | 48 | Pass | | 16 | E | 21 | 39 | Poor | | 17 | F | 0 | 20 | Fail | +----+-------+--------+--------+-------------+ Query results... +--------+------------+-----------+-------+------+-------+-------+-------------+ | yearid | semesterid | subjectid | regno | rank | total | grade | comment | +--------+------------+-----------+-------+------+-------+-------+-------------+ | 1 | 1 | 1 | 6073 | 1 | 73 | B2 | V Good | | 1 | 1 | 1 | 4663 | 2 | 72 | B2 | V Good | | 1 | 1 | 2 | 6073 | 1 | 61 | C4 | Good | | 1 | 1 | 2 | 4663 | 2 | 47 | D7 | Pass | | 1 | 1 | 3 | 4663 | 1 | 82 | A | Excellent | | 1 | 1 | 3 | 6073 | 2 | 61 | C4 | Good | | 1 | 1 | 4 | 4663 | 1 | 99 | A* | Distinction | | 1 | 1 | 4 | 6073 | 2 | 95 | A* | Distinction | | 1 | 1 | 5 | 6073 | 1 | 100 | A* | Distinction | | 1 | 1 | 5 | 4663 | 2 | 70 | B2 | V Good | | 1 | 1 | 6 | 4663 | 1 | 69 | C1 | Good | | 1 | 1 | 6 | 6073 | 2 | 67 | C2 | Good | | 1 | 1 | 7 | 6073 | 1 | 80 | A | Excellent | | 1 | 1 | 7 | 4663 | 2 | 77 | B1 | V Good | | 1 | 1 | 8 | 6073 | 2 | 77 | B1 | V Good | | 1 | 1 | 8 | 4663 | 2 | 58 | D2 | Pass | | 1 | 1 | 9 | 4663 | 1 | 96 | A* | Distinction | | 1 | 1 | 9 | 6073 | 1 | 96 | A* | Distinction | | 1 | 1 | 10 | 4663 | 1 | 78 | B1 | V Good | | 1 | 1 | 10 | 6073 | 2 | 77 | B1 | V Good | | 1 | 1 | 11 | 6073 | 1 | 88 | A | Excellent | | 1 | 1 | 11 | 4663 | 2 | 48 | D7 | Pass | | 1 | 1 | 12 | 6073 | 1 | 94 | A* | Distinction | | 1 | 1 | 12 | 4663 | 2 | 69 | C1 | Good | | 1 | 2 | 1 | 6073 | 1 | 70 | B2 | V Good | | 1 | 2 | 1 | 4663 | 2 | 28 | E | Poor | | 1 | 2 | 2 | 4663 | 1 | 68 | C1 | Good | | 1 | 2 | 2 | 6073 | 2 | 59 | D1 | Pass | | 1 | 2 | 3 | 6073 | 1 | 70 | B2 | V Good | | 1 | 2 | 3 | 4663 | 2 | 68 | C1 | Good | | 1 | 2 | 4 | 4663 | 1 | 81 | A | Excellent | | 1 | 2 | 4 | 6073 | 2 | 72 | B2 | V Good | | 1 | 2 | 5 | 4663 | 1 | 84 | A | Excellent | | 1 | 2 | 5 | 6073 | 2 | 72 | B2 | V Good | | 1 | 2 | 6 | 6073 | 2 | 72 | B2 | V Good | | 1 | 2 | 6 | 4663 | 2 | 58 | D2 | Pass | | 1 | 2 | 7 | 4663 | 1 | 71 | B2 | V Good | | 1 | 2 | 7 | 6073 | 2 | 70 | B2 | V Good | | 1 | 2 | 8 | 6073 | 1 | 55 | D3 | Pass | | 1 | 2 | 8 | 4663 | 2 | 48 | D7 | Pass | | 1 | 2 | 9 | 4663 | 1 | 66 | C2 | Good | | 1 | 2 | 9 | 6073 | 2 | 51 | D5 | Pass | | 1 | 2 | 10 | 6073 | 1 | 58 | D2 | Pass | | 1 | 2 | 10 | 4663 | 2 | 37 | E | Poor | | 1 | 2 | 11 | 6073 | 1 | 59 | D1 | Pass | | 1 | 2 | 11 | 4663 | 2 | 57 | D2 | Pass | | 1 | 2 | 12 | 6073 | 1 | 69 | C1 | Good | | 1 | 2 | 12 | 4663 | 2 | 67 | C2 | Good | | 1 | 3 | 1 | 4663 | 1 | 94 | A* | Distinction | | 1 | 3 | 1 | 6073 | 2 | 82 | A | Excellent | | 1 | 3 | 2 | 6073 | 1 | 76 | B1 | V Good | | 1 | 3 | 2 | 4663 | 2 | 69 | C1 | Good | | 1 | 3 | 3 | 6073 | 1 | 81 | A | Excellent | | 1 | 3 | 3 | 4663 | 2 | 63 | C4 | Good | | 1 | 3 | 4 | 4663 | 1 | 81 | A | Excellent | | 1 | 3 | 4 | 6073 | 2 | 77 | B1 | V Good | | 1 | 3 | 5 | 6073 | 1 | 83 | A | Excellent | | 1 | 3 | 5 | 4663 | 2 | 72 | B2 | V Good | | 1 | 3 | 6 | 6073 | 1 | 83 | A | Excellent | | 1 | 3 | 6 | 4663 | 2 | 78 | B1 | V Good | | 1 | 3 | 7 | 4663 | 1 | 77 | B1 | V Good | | 1 | 3 | 7 | 6073 | 2 | 75 | B1 | V Good | | 1 | 3 | 8 | 6073 | 1 | 82 | A | Excellent | | 1 | 3 | 8 | 4663 | 2 | 74 | B2 | V Good | | 1 | 3 | 9 | 6073 | 1 | 95 | A* | Distinction | | 1 | 3 | 9 | 4663 | 2 | 56 | D3 | Pass | | 1 | 3 | 10 | 4663 | 1 | 87 | A | Excellent | | 1 | 3 | 10 | 6073 | 2 | 79 | B1 | V Good | | 1 | 3 | 11 | 6073 | 1 | 71 | B2 | V Good | | 1 | 3 | 11 | 4663 | 2 | 70 | B2 | V Good | | 1 | 3 | 12 | 6073 | 1 | 90 | A | Excellent | | 1 | 3 | 12 | 4663 | 2 | 82 | A | Excellent | +--------+------------+-----------+-------+------+-------+-------+-------------+ [edit] * The idiots at Maria maintain that as the subquery effectively creates a table, and tables are inherently unordered, then the ORDER BY clause should be ignored. The rest of the world think this is a resultset and there can be ordered. The LIMIT 264-1 forces it to use an ordered temporary table.

Sounds like you need some code other than that above. If it doesn't produce what you need, don't use it. Find one which will return an array or rewrite that one.

Could it be that you are setting it to none-existent value?

It probably works even when you do redirect. You don't see it because it immediately goes to another page.

I hope you weren't planning on shooting the messenger.

I am a little confused by your ajax query. Your code produces a calendar for the current month and your ajax code sends the date. All OK so far, but then your query searches by account only. I would have expected you to get the events just for that current month as those are the only ones you need. Having got an array of this month's events (as in my example above) I would for each element in array find <td> with data-day == element key change its background color add a tooltip title attribute with element value end for each That will highlight days with an event and display the event on hovering over the day. EG But then, you haven't really explained how you envisage it working.

1 ) Why are you searching for "amprsand" when there are no ampersands (&) in your code. 2) the numeric value of a string is value of the numeric chars up to the first non-numeric character echo intval('12@3'); // 12 echo intval('@123'); // 0

I would create the event array with the days as the keys and the event descriptions as the values [ "18":"Carol service", "25":"Christmas Day", "31":"New Years Eve" ] and give each <td> a data-day attribute <tr><td data-day='01'>1</td> ... <td data-day='31'>31</td></tr> Now it's easy to match them.

The sort code worked for me Array ( [0] => Array ( [name] => DSCN0025.JPG [time] => 1399035071 ) [1] => Array ( [name] => DSCN0055.JPG [time] => 1453121551 ) [2] => Array ( [name] => DSCN0052.JPG [time] => 1575540845 ) [3] => Array ( [name] => DSCN0043.JPG [time] => 1330013161 ) [4] => Array ( [name] => DSCN0062.JPG [time] => 1527770426 ) ) Original array of files DSCN0025.JPG 2014-05-02 DSCN0055.JPG 2016-01-18 DSCN0052.JPG 2019-12-05 DSCN0043.JPG 2012-02-23 DSCN0062.JPG 2018-05-31 Sort the array DSCN0052.JPG 2019-12-05 DSCN0062.JPG 2018-05-31 DSCN0055.JPG 2016-01-18 DSCN0025.JPG 2014-05-02 DSCN0043.JPG 2012-02-23 Code: <?php $arr = glob('Pictures/Nikon Transfer 2/001/*.JPG'); shuffle($arr); $files = []; foreach ($arr as $k => $f) { $files[$k]['name'] = basename($f); $files[$k]['time'] = filemtime($f) ; } echo '<pre>', print_r($files, 1); echo "Original array of files\n\n"; foreach ($files as $f) { printf("%-15s %15s\n", $f['name'], date('Y-m-d', $f['time'])); } echo "\n\nSort the array\n\n"; uasort($files, function($a, $b) { return $b['time'] <=> $a['time']; }); foreach ($files as $f) { printf("%-15s %15s\n", $f['name'], date('Y-m-d', $f['time'])); } echo '</pre>'; ?>

Get a pencil and read through the code line by line as if it were being executed. For each iteration, note the values of i and d and the values that would be output. You should get this (try it yourself before peeping)

There is a syntax error in my code - there's a missing ' character uasort($files, function($a, $b) { return $b['time'] <=> $a['time']; } ); ^ Add

"continue" says "Stop processing this iteration and continue with the next. Just a single "continue (1)" would take you to the start of the inner loop and start with the next value of $d. Because you have "continue 2" it goes to the start of the outer loop and continues with the next value of $i.

I thought I would check out the impact of the larger key. I creted two tables, one with a smallint PK and the other using bigint. CREATE TABLE `keytest_si` ( `id` smallint(5) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `keytest_bi` ( `id` bigint(20) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Then I looked for my largest table that would fit in a smallint. I then wrote the ids from this table to each of the two new tables. mysql> select count(*) from production_status; +----------+ | count(*) | +----------+ | 60000 | +----------+ 1 row in set (0.01 sec) mysql> insert into keytest_si (id) select id from production_status; Query OK, 60000 rows affected (1.67 sec) Records: 60000 Duplicates: 0 Warnings: 0 mysql> insert into keytest_bi (id) select id from production_status; Query OK, 60000 rows affected (1.86 sec) Records: 60000 Duplicates: 0 Warnings: 0 That's a difference of 0.2 seconds over 60,000 records. The added cost per record insert is therefore 0.000003 seconds. Do you really think that's worth the worry and the extra processing it's causing you? (Which probably takes far longer than the time saved) And Merry Christmas to you too.

Have you looked in this thread - about an hour ago?

Then why the **** do you want to set the auto_increment back to 1?

You have a foreach () loop in your code which process the array $files You need to put that sort code before that loop

Then, as @benanamen said, truncate the tables. That will remove all the data and reset the auto-inc to 1. It's also far faster the a delete query. And to answer your question... SQL file (atest.sql) TRUNCATE TABLE player TRUNCATE TABLE roster PHP foreach (file('atest.sql') as $sql) { $conn->query($sql); }

It doesn't look like the sort of query you would ever need to run more than once, so why store it at all?

If the files data is coming from DB query, do the sort in the query, otherwise use a custom sort function. uasort($files, function($a, $b) { return $b['time'] <=> $a['time]; } ); (For ascending sort, switch a and b in the return)

Unfortunately (for you) both those are things you will have to check for yourself.

I can't be definite because there does not not appear to be an end } to your while loop, but could it be because you are sending the mail inside a loop? I'm guessing, therefore, that isn't your actual code, so not much we can do.

generate the calendar. make an AJAX request which returns a JSON encoded array of your events from the database. process the array, adding the events and colours to the calendar