[SOLVED] checking table for existing data

[eon201]

Hi,

 

Im passing information to a mysql table with three rows - ip - url - date - like so...

 

mysql_query("INSERT INTO logtable
(ip, url, date) VALUES('$ip', '$url', '$date' ) ") 
or die(mysql_error());
echo "Data inserted<br/><br/>";

 

Does anyone know how to check the table to see if this data (as in the whole row) already exists within the table??

If the data does not exist then add it.

If the data does exist then ignore.

 

Im not to versed on the mysql side of php so any help will be apprecaitted very much.

 

Thanks. Eon201

[obsidian]

If you are checking to see if the entire row exists (or individual elements for that matter), just run a SELECT on the data first. If a record is returned, don't insert:

$sql = mysql_query("SELECT * FROM logtable WHERE ip = '$ip' AND url = '$url' AND date = '$date'");
if ($sql === FALSE)
{
  die("Could not complete query");
}

if (mysql_num_rows($sql) == 0) // no records found
{
  // do your insert here
}

[eon201]

ok thanks for that.

 

Just to clarify your code (and so that I fully understand what is happening here!)

 

We search for ip , url, and date.

 

If they match the current values then the first if statement happens.

die("Could not complete query");

 

If they dont match the current values then the second if statement occurs.

// do your insert here

 

??? Have I got that right??

[eon201]

No its all good. I just worked it out. Thanks for the help!!! ;D

[aschk]

There is another way to do this using purely MySQL.

 

INSERT INTO logtable(ip, url, date)
VALUES('$ip', '$url', '$date' )
ON DUPLICATE KEY UPDATE ip=VALUES(ip)

 

If you're going to use the above then it might be handy to have another field (like "numberOfUpdates") thats get incremented each time a duplicate is found... ON DUPLICATE KEY UPDATE numberOfUpdates = numberOfUpdates+1

I should also point out that you NEED an unique key (index) on (ip,url,date).