Entry Of Duplicate Rows in Mysql
Question by dotman14
Please I need your help with my script. I’m trying to insert values into maintable, but before I insert, I have to check if a row have an exact match compared to the one I’m inserting. If there’s a match then it should echo Record Exists, and if not it should go ahead and insert into the table. The problem is that it is returning 0 for $duplicate, when actually there is a row that matches the entries that I’ve entered in the HTML page, thereby it goes ahead and inserts the duplicate.
Please what could the problem be?
{
$query = mysql_query("SELECT m.matric_no, m.course_code, m semester_name, m.level, m.session
FROM maintable AS m
WHERE m.matric_no = '".$matric_no."'
AND m.course_code = '".$course_code."'
AND m.semester_name = '". $semester_name."'
AND m.level = '".$level."'
AND m.session = '".$session."'") ;
$duplicate = mysql_num_rows($query);
if ($duplicate > 0 )
echo "$duplicate" ;
{
echo "Record Exists"
}
else
{
$query = "INSERT INTO maintable (matric_no, course_code, semester_nam, session, level)
VALUES ('".$matric_no."',
'".$course_code."',
'".$semester_name."',
'".$session."',
'".$level."')" ;
mysql_query($query) or
die (mysql_error());
echo"Record Inserted";
}
Please forgive my formatting, I typed on a mobile, I didn’t mean disrespect to this community.
Answer by Jamie
Your formatting’s a bit all over the place, which is why you’re finding it so hard to spot errors when they occur. Try sticking to a consistent format style. This should do the trick:
$check = mysql_query("SELECT * FROM maintable WHERE matric_no = '$matric_no' AND course_code = '$course_code' AND m.semester_name = '$semester_name' AND m.level = '$level' AND m.session = '$session'");
if(mysql_num_rows($check))
{
echo 'Record exists!';
}
else
{
$query = "INSERT INTO maintable (matric_no, course_code, semester_nam, session, level) VALUES ('$matric_no','$course_code','$semester_name','$session','$level')";
mysql_query($query) or die (mysql_error());
}
Answer by Starx
You are counting rows, which may return 0 if no records matching your query were found. However you are mixing if code block
if ($duplicate > 0 )
echo "$duplicate" ;
{
echo "Record Exists"
}
Should be
if ($duplicate > 0 )
{
echo "$duplicate" ;
echo "Record Exists"
}
I have checked your code, and it seems valid. Try to simplify it
$result = mysql_query("SELECT * FROM maintable WHERE matric_no = '$matric_no' AND course_code = '$course_code' AND m.semester_name = '$semester_name' AND m.level = '$level' AND m.session = '$session'");
if(!mysql_num_rows($result))
{
$query = "INSERT INTO maintable (matric_no, course_code, semester_nam, session, level) VALUES ('$matric_no','$course_code','$semester_name','$session','$level')";
mysql_query($query) or die (mysql_error());
}
