Check If Row Exists [Archive]

Meltdown

07-11-2004, 05:50 PM

When users submit things from a form into a mysql db, I want to make somehting that will check if their submission (row) already exists and if it does, then don't submit. Anyy suggestions?

raf

07-11-2004, 08:02 PM

you just run a select to see if that row already exists. This means you need to have at least 1 unique index you can check against.

if you wan't to create a new row for new users, but update the row of existing users, then you can use the replace-statement. Which works like a regular insert, but if there is a key violation (if the unique inex contains a duplicte) then the old row is deleted and a new one is inserted.

Meltdown

07-11-2004, 08:40 PM

Ok, heres what I have. I'm pretty sure I'm on the right track, I just need to know how to do a few little things that I don't know how to do..
<html>
<head>
<META HTTP-EQUIV="Refresh" CONTENT="2; URL=index.php">
<link href="style.css" type="text/css" rel="stylesheet">
<title>-</title>
</head>
<body topmargin="250">
<?php
@ $db = mysql_pconnect('', '', '');

if (!$db){echo"ERROR: Cannot connect to database.";exit;}

$Artist = $_POST['Artist'];
$Song = $_POST['Song'];
$Lyric = $_POST['Lyric'];
$Email = $_POST['Email'];
$todayis = date("l, F j, Y, g:i a") ;

mysql_select_db("") or die("Could not select database");
if (mysql_query("SELECT ".$Artist.", ".$Song." FROM Lyrics WHERE approved=1")) {
echo "The lyrics you submitted already exist!<br/>";
} else {
mysql_query("INSERT INTO Lyrics (Artist, Song, Lyric, Email, id) VALUES ('$Artist','$Song','$Lyric','$Email','$todayis')") or die("Error inserting lyrics! ".mysql_error());
echo '
<div align=\'left\'>
<table border=\'0\' width=\'100%\' height=\'100%\' cellspacing=\'0\' cellpadding=\'0\'>
<tr>
<td width=\'730\' height=\'473\' valign=\'middle\'><center><table width=\'75%\' border=\'1\' cellspacing=\'0\' cellpadding=\'5\' bordercolor=\'#808C90\'><a class=\'submittext\'>
<td bgcolor=\'#707C80\'>
<p align=\'center\'>Your lyrics were successfully added!<br>You are now being redirected back to -</a></table></td>
</tr>
</table>
</div>
';
}
?>
this takes input from a forn and puts it into the db. all i need to know is what's wrong with the if else part of my code

raf

07-12-2004, 12:22 AM

if (mysql_query("SELECT ".$Artist.", ".$Song." FROM Lyrics WHERE approved=1")) {
} else {

should be

$result=mysql_query("SELECT count(*) as numrecords FROM Lyrics WHERE approved=1 and Artist='" . $Artist . "' and Song='" . $Song . "'") or die ('Queryproblem checing esisting record.');
$row=mysql_fetch_assoc($result);
if ($row['numrecords'] >= 1){
echo 'The lyrics you submitted already exist!<br/>';
} else {

Meltdown

07-12-2004, 01:04 AM

raf

07-12-2004, 07:41 AM

Thanks! Worked wonderful.. If you have the time, could you explain to me exactly what this does:
($row['numrecords'] >= 1)
the >=1 just doesnt seem to me that it fits in there. thanks for the help man. :thumbsup:
You're welcome.

The query will count the number of rows with that artist and song that have approved=1. The result of this count, is stored in variable "numrecords".
This query always returns one record with one variable-value pair in it.
If this song isn't submitted yet then numrecords==0.
So if numrecords is 1 or is higher then 1 then the song has been submitted before.
Normaly $row['numrecords'] == 1 would do, buth $row['numrecords'] >= 1 is a bot safer.
You could of course also use
$row['numrecords'] === 0 and then move the error to the else-clause, but i find that harder to read

Meltdown

07-12-2004, 03:30 PM

thanks i understand now... :)

jakerbug

01-15-2006, 05:05 AM

coding forums saves me again! i've searched ALL DAY for this solution, and i finally found it here.

i see that the thread is more than a year old, but this is honestly the only thing i can find online that provides a clear-cut answer to the question "how do i check if a row exists, update it if it does, and create it if it doesn't".

beautiful and simple.