Barand

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.

You can't do correct comparisons on dates in that format (for example 15th Dec 15 > 14th Dec 16 whereas you need it to be less than). Always store dates in a database as yyyy-mm-dd format (type DATE, DATETIME or TIMESTAMP). To make it usable you have the overhead of using STR_TO_DATE() function to do a compare in a query.

Benchmark rerun with REPLACE also: +----------------------------------------------------------+ | Single query, multiple data | Time | +-----------------------------------------------+----------+ | Insert records | 0.1552 | | Update records | 0.1669 | | Replace records | 0.1449 | +-----------------------------------------------+----------+ +----------------------------------------------------------+ | Single prepare, multiple execute | Time | +-----------------------------------------------+----------+ | Insert records | 66.9068 | | Update records | 69.7397 | | Replace records | 67.3186 | +-----------------------------------------------+----------+

I have just done a benchmark. Each test inserts 1000 records then updates every record. The first pass uses single queries with multiple data values, the updates using the ON DUPLICATE hack. The second pass uses multiple executes of the prepared queries for both update and inserts. The results were +----------------------------------------------------------+ | Single query, multiple data | Time | +-----------------------------------------------+----------+ | Insert records | 0.1222 | | Update records | 0.1441 | +-----------------------------------------------+----------+ +----------------------------------------------------------+ | Single prepare, multiple execute | Time | +-----------------------------------------------+----------+ | Insert records | 62.3395 | | Update records | 68.0629 | +-----------------------------------------------+----------+ The code $N=1000; // set # of records for the benchmark; function resetData($pdo) { $pdo->exec("DROP TABLE IF EXISTS person"); $pdo->exec("CREATE TABLE person ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(40), lname VARCHAR(40) )"); } function showTimings($desc, $t1, $t2, $t3) { echo '<pre>'; echo "+----------------------------------------------------------+\n"; printf("| %-45s | Time |\n", $desc); echo "+-----------------------------------------------+----------+\n"; printf("| %-45s | %8.4f |\n", 'Insert records', $t2-$t1); printf("| %-45s | %8.4f |\n", 'Update records', $t3-$t2); echo "+-----------------------------------------------+----------+\n"; echo '</pre>'; } // // start the tests // resetData($pdo); $t1 = microtime(1); // pdo multiple insert $data=[]; $params=[]; for ($i=1; $i<=$N; $i++) { $fname = 'aaaaa'.$i; $lname = 'bbbbb'.$i; $data[]="(?,?)"; array_push($params,$fname,$lname); } $sql = "INSERT INTO person (fname,lname) VALUES ".join(',', $data); $stmt=$pdo->prepare($sql); $stmt->execute($params); $t2 = microtime(1); // pdo multiple insert..on duplicate updates $data=[]; $params=[]; for ($i=1; $i<=$N; $i++) { $fname = 'ccccc'.$i; $lname = 'ddddd'.$i; $data[]="(?,?,?)"; array_push($params,$i,$fname,$lname); } $sql = "INSERT INTO person (id,fname,lname) VALUES ".join(',', $data) . " ON DUPLICATE KEY UPDATE fname = VALUES(fname), lname = VALUES(lname)"; $stmt=$pdo->prepare($sql); $stmt->execute($params); $t3 = microtime(1); showTimings('Single query, multiple data', $t1, $t2, $t3) ; // // Method 2 - prepare then execute many // resetData($pdo); $t1 = microtime(1); // PDO multiple insert executes $sql = "INSERT INTO person (fname,lname) VALUES (?,?)"; $stmt = $pdo->prepare($sql); for ($i=1; $i<=$N; $i++) { $fname = 'aaaaa'.$i; $lname = 'bbbbb'.$i; $stmt->execute([$fname,$lname]); } $t2 = microtime(1); // PDO multiple update executes $sql = "UPDATE person SET fname = ? , lname = ? WHERE id = ?"; $stmt = $pdo->prepare($sql); for ($i=1; $i<=$N; $i++) { $fname = 'ccccc'.$i; $lname = 'ddddd'.$i; $stmt->execute([$fname,$lname,$i]); } $t3 = microtime(1); showTimings('Single prepare, multiple execute', $t1, $t2, $t3) ;

For a start, too many "UNIONS". SELECT MONTH(due_date) as month , SUM(amount_paid) as total FROM ( SELECT due_date, amount_paid FROM table1 UNION ALL SELECT due_date, amount_paid FROM table2 )x GROUP BY month If you are having to do that, it looks like your table1 and table2 should be a single table with an additional identifier column.

I see you are still adhering to the mantra "Why run one query when six will do?" https://forums.phpfreaks.com/topic/302526-divide-by-zero-warning/?do=findComment&comment=1539284

Yes, that's OK. So long as they are both on the same server. The connection is to the server. And, as you said, you have privileges to access all databases in the query.

Not according to the manual