MySQL database entry duplication

Question

I have a PHP script search script that logs every query made in a MySQL database starting with 1 in the value column. Currently, if the same terms are searched more than once, 1 is added to the number in the value column. However, after two searches for the same word, the query gets added to the database again. Why could this be?

My PHP code is:

<?php

$database=mysql_connect("localhost","username","password");
mysql_select_db("database",$database);

$query=$_GET['q'];

logQuery($query);
function logQuery($query){
$query="insert into queries (query) values ('$query') on duplicate key update value=value+1";
mysql_query($query);
}

?>

What is the PRIMARY_KEY you base the duplicate key query on? — konsolenfreddy
– konsolenfreddy, Commented Jun 20, 2011 at 23:04

Jonathan Leffler · Accepted Answer · 2011-06-21 03:33:01Z

3

Sounds like either query is not your primary key, or that $query is different from the DB contents. My money is on the key; could it be a composite key? i.e. id AND query?

In this situation/example, using mysql_escape_string(trim($query)) is a MUST to avoid SQL injection.

edited Jun 21, 2011 at 3:33

Jonathan Leffler

760k145 gold badges961 silver badges1.3k bronze badges

answered Jun 20, 2011 at 23:05

Question Mark

3,6261 gold badge27 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James Scriven · Accepted Answer · 2011-06-20 23:09:57Z

0

If the query is getting added a second time, then it is obviously not a primary key, which prevents the 'on duplicate key' from working

answered Jun 20, 2011 at 23:09

James Scriven

8,2341 gold badge34 silver badges37 bronze badges

Comments

Darren · Accepted Answer · 2012-05-04 15:17:57Z

0

Do you define query field as unique? maybe a whitespace or something, try using md5 as unique key

edited May 4, 2012 at 15:17

Darren

71.2k24 gold badges141 silver badges146 bronze badges

answered Jun 20, 2011 at 23:05

academo

2441 silver badge9 bronze badges

Collectives™ on Stack Overflow

MySQL database entry duplication

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related