Update mysql field with addition users if duplicate search is performed

Question

I have a site where user's searches are tagged with their user id. Right now, if a different user searches a duplicate, it adds +1 to a count field I have, yet, it still keeps the original user's id who searched it.

How can I start making an "array" of user ID's for the userId field in my table on a duplicate search.

    $sql="INSERT INTO trending (searchstring, timecount, userSearch) VALUES('$_POST[contentVar]', NOW(), '$uid') ON DUPLICATE KEY UPDATE count = count + 1";

searchstring is the user search, timecount is time, userSearch is the user ID of the user who searched it.

It looks like you are vulnerable to SQL injection attacks. :( — Mark Byers, Oct 03 '11 at 20:09
its a bad way to insert the $_POST like this , check about sql injection — salahy, Oct 03 '11 at 20:12

score 0 · Accepted Answer · edited May 23 '17 at 11:43

0

SQL-injection
Never inject super-globals directly into SQL. With the update part you mustn't forget the set keyword.

Do it like this:

$content = mysql_real_escape_string($_POST['contentvar']);
$uid = mysql_real_escape_string($uid);
$sql="INSERT INTO trending (searchstring, timecount, userSearch)
      VALUES('$contentvar', NOW(), '$uid') 
      ON DUPLICATE KEY UPDATE SET count = count + 1";

See: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
And: How does the SQL injection from the "Bobby Tables" XKCD comic work?

edited May 23 '17 at 11:43

Community

1
1

answered Oct 03 '11 at 20:16

Johan

74,508
24
191
319

Thank you for the help in regards to the sql injection. As for the update, I need to add the other user id to the already existing id. – Michael Oct 03 '11 at 20:36

score 0 · Answer 2 · answered Oct 03 '11 at 20:20

0

Have you considered inserting a new row for each and every search, then you can query the table when you need by $uid and form an array that way.

answered Oct 03 '11 at 20:20

digout

4,041
1
31
38

yes, but I want to keep a count of the duplicates to make a trending function. – Michael Oct 03 '11 at 20:35

score 0 · Answer 3 · answered Oct 03 '11 at 21:08

0

There are two possible approaches: 1. create a new table where the ids of the users will be kept or modify the userSearch column to hold a serialized array. Both cannot be done by simply modifying the UPDATE query.

answered Oct 03 '11 at 21:08

Martin Dimitrov

4,796
5
46
62

Update mysql field with addition users if duplicate search is performed

3 Answers3