Barand

I used a test table of mine which contains 20 types. Each type has 9000+ rows. This query selects 10 of each type. I have annotated the purpose of the subqueries. SELECT type --+ , id | FROM ( --+ | SELECT id | | , @row := IF(@prev=type, @row + 1, 0) as row | | , @prev := type as type | apply | FROM ( --+ | row | get 10 SELECT id, type | select data | count | of FROM votes | order by | within | each JOIN (SELECT @row:=0,@prev:=null) init | type | each | type ORDER BY type | | type | ) votes --+ | | ) data --+ | WHERE row < 10; --+

Perhaps SELECT count(*) as total , sum(case when type = 'Leveler OTH' then 1 else 0 end) LevOTH , sum(case when type = 'Mugger OTH' then 1 else 0 end) MugOTH , sum(case when type = 'Buster OTH' then 1 else 0 end) BustOTH , sum(case when type = 'Leveler OTD' then 1 else 0 end) LevOTD , sum(case when type = 'Mugger OTD' then 1 else 0 end) MugOTD , sum(case when type = 'Buster OTD' then 1 else 0 end) BustOTD , sum(case when type = 'Mobster OTD' then 1 else 0 end) MobOTD FROM ( SELECT userid, type FROM othwinners WHERE userid = 19 UNION ALL SELECT userid, type FROM otdwinners WHERE userid = 19 ) data

JOINS match on key values, UNIONS do not. I think you have some reading to do

That said, if you want a single query, you would use a UNION and not a JOIN in this instance.

I like to use a function in cases like this. Example $current_author = isset($_POST['author_id']) ? $_POST['author_id'] : ''; function authorOptions($db, $author) { $sql = "SELECT id, name FROM author ORDER BY name"; $options = "<option value=''>Select one</option>\n"; foreach ($db->query($sql) as $data) { $sel = $data['id']==$author ? 'selected' : ''; $options .= "<option value='{$data['id']}' $sel>{$data['name']}</option>\n"; } return $options; } Then <select name="author_id" id="author_id"> <?= authorOptions($db, $current_author)?> </select>

As a condition on the date it is complete. RTFM - BETWEEN I don't know your application so there may be other conditions in the WHERE clause.

Use a cron job to run daily. Query your event table for those events SELECT ... WHERE CURDATE() BETWEEN eventdate - INTERVAL 10 DAY AND eventdate

The unique key is the wrong answer. To limit the updates to the single row you need the the same WHERE clause that you have in the delete query. Do not put user data directly into the queries - use prepared statements and pass the data as parameters, otherwise you are vulnerable to SQL injection attacks.

rand

You are using POST method but attempting to access $_GET variables (and it's not $_Get)

test data insert into ranking (id_jogo, id_user, pontuacao, data, tempo) VALUES (5141, 11 , 15, '2016-12-27', '00:00:03'), (7001 , 9 , 10, '2016-12-27', '00:00:06'), (2519 , 7 , 5, '2016-12-27', '00:00:07'), (4585 , 6 , 15, '2016-12-27', '00:00:04'), (4585 , 5 , 10, '2016-12-27', '00:00:05'), (4585 , 4 , 10, '2016-12-27', '00:00:07'), (4585 , 3 , 6, '2016-12-27', '00:00:07'), (4585 , 2 , 7, '2016-12-27', '00:00:07'), (4585 , 1 , 10, '2016-12-27', '00:00:06'); query SELECT id_user , pontuacao , data , tempo , rank FROM ( SELECT id_user , data , @row := @row+1 as row , @rank := IF(@prevpont = pontuacao AND @prevtemp = tempo, @rank, @row) as rank , @prevpont := pontuacao as pontuacao , @prevtemp := tempo as tempo FROM ranking JOIN (SELECT @prevtemp:='0:00:00', @prevpont:=0, @row:=0, @rank:=0) init ORDER BY pontuacao DESC, tempo ) calc ; results +---------+-----------+------------+----------+--------+ | id_user | pontuacao | data | tempo | rank | +---------+-----------+------------+----------+--------+ | 11 | 15 | 2016-12-27 | 00:00:03 | 1 | | 6 | 15 | 2016-12-27 | 00:00:04 | 2 | | 5 | 10 | 2016-12-27 | 00:00:05 | 3 | | 9 | 10 | 2016-12-27 | 00:00:06 | 4 | = | 1 | 10 | 2016-12-27 | 00:00:06 | 4 | = | 4 | 10 | 2016-12-27 | 00:00:07 | 6 | | 2 | 7 | 2016-12-27 | 00:00:07 | 7 | | 3 | 6 | 2016-12-27 | 00:00:07 | 8 | | 7 | 5 | 2016-12-27 | 00:00:07 | 9 | +---------+-----------+------------+----------+--------+

Here's an alternative approach $category = [ 1 => 'kiwi', 2 => 'lemon', 3 => 'apple', 4 => 'pear' ]; asort($category); // sort them, preserving keys list ($id, $cat) = each($category); // get the first echo "$id : $cat"; // --> 3 : apple

If you are storing the time as an integer timestamp then that last line of that query needs to use FROM_UNIXTIME to convert to a DATETIME type ... AND FROM_UNIXTIME(last_action) >= NOW() - INTERVAL 1 YEAR

I would show you if I weren't totally confused by what you are trying to do. First you select last_action time for the first record in the table - why? That record could be several years old When you say "active for the past year" does that mean within the last 12 months (Dec 21st 2015 - Dec 21st 2016) or active during 2016? What is the structure of the "users" table. Is it one record per user, as the name implies?

I remember telling one guy on the forum that he had two parameters in the wrong order. His reply was "What order should they be then?"

Damn!

It's a lot more flexible for sizes and family if you use imagettftext. To centre, get the size of the text using imagettfbbox and calculate where to put it.

Spot the difference?

As Jan 23rd 2017 is a Monday, and there is no "09" anywhere in your data, I know of no function that could give that result from that input.

You started out well with PDO statements, but then you suddenly revert back to mysql_ functions. You can't do that. You have to stick with PDO functions. As you have stored the data in an array with fetchAll() you can now output the results with a foreach() loop on the array.

It looks like you are using LIKE without any wildcard characters. Try $stmt->execute(array(":srch" => $searchq."%")); EDIT Just noticed you use :srch twice - you need to treat those a two parameters

Perhaps check that (pseudocode) count(array_intersect_key) == 3 AND array_diff_key is empty

Instead of a list of 70,000 items it sounds like you might need some sort of cascading selection. So you first select a category, which gives you a list of sub-categories Then select a sub-category which can then give you another shorter list. ... and so on until you have narrowed the options down to a manageable sized list. The way to accomplish this is with AJAX, passing the category and receiving the shorter list etc.

I answered the question that you asked. We were told not to analyze what you were doing, therefore cannot say more. To do so would be to make assumptions about your intentions. You need to ask better questions. What is it that makes you believe there is a problem? What symptoms are you getting? What is happening or not happening?

Yes, there is.