[SOLVED] if !empty do not duplicate record

[thesaleboat]

I have code which inserts a record into a persons database.  The person never sees this.  I am trying to make the code work so that if an identical entry already exists the record will not duplicated, but my second if statement is causing it to never insert that record even if it doesn't exist... help please!

 

<?php

require_once('dbconnect.php');

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");

$myusername = $_SESSION['myusername'];

$rowalreadyexists = mysql_query("SELECT * FROM `users_subcategories` WHERE `users_subcategories`.`SubCategoryId` = $subcategory and `users_subcategories`.`username` = $myusername;");

if (!empty($_SESSION['myusername']) && $subcategory >= 1){

if (!empty($rowalreadyexists)){

mysql_query("INSERT INTO `users_subcategories` (`username`, `SubCategoryId`) VALUES ('$myusername','$subcategory');") or die(mysql_error());

}

}

?>

 

:-\

[rhodesa]

Are these the only two columns in the table? If so, make the primary key be for both columns:

ALTER TABLE `users_subcategories` ADD PRIMARY KEY ( `username` , `SubCategoryId` )

 

Then, use a REPLACE instead of an insert:

mysql_query("REPLACE INTO `users_subcategories` (`username`, `SubCategoryId`) VALUES ('$myusername','$subcategory');") or die(mysql_error());

 

Edit: And then you can get rid of all the code to see if the category is already there...

[thesaleboat]

Thank you for the fast reply but, I am not sure if this will work. This is for an online training website, the way the person completes it is by going to every page of the training, and then there is a test at the end.  So at the top of every page, there is a unique subcategory number eg. <?php session_start(); $subcategory = 4; ?>.

[DarkWater]

The mysql_query will always return something, so do this:

 

$rowalreadyexists = mysql_query("SELECT * FROM `users_subcategories` WHERE `users_subcategories`.`SubCategoryId` = $subcategory and `users_subcategories`.`username` = $myusername;");

$rowcount = mysql_num_rows($rowalreadyexists);

 

Then do:

if ($rowcount > 0) {

....

}

[thesaleboat]

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\xamp\xampplite\htdocs\SOSI\History&Mission.php on line 149

This is what happened when i tried that, but I was very excited to try that i thought it would work!? 

[DarkWater]

You don't do any error checking for your query.  Change:

$rowalreadyexists = mysql_query("SELECT * FROM `users_subcategories` WHERE `users_subcategories`.`SubCategoryId` = $subcategory and `users_subcategories`.`username` = $myusername;");

To:

$rowalreadyexists = mysql_query("SELECT * FROM `users_subcategories` WHERE `users_subcategories`.`SubCategoryId` = $subcategory and `users_subcategories`.`username` = $myusername;") OR die(mysql_error());

[thesaleboat]

DarkWater you are the man! Thank you alot everything works good now, i did have to change one thing tho if ($rowcount == 0)  but that was it, for anyone reading this forum here is my now working code if it can help you at all, then let it be of use to you. Thanks again DarkWater.

                   

<?php

session_start();

$subcategory = 4;

?>

 

<?php

require_once('dbconnect.php');

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");

$myusername = $_SESSION['myusername'];

$rowalreadyexists = mysql_query("SELECT * FROM `users_subcategories` WHERE `users_subcategories`.`SubCategoryId` = '$subcategory' and `users_subcategories`.`username` = '$myusername'") or die(mysql_error());

$rowcount = mysql_num_rows($rowalreadyexists);

 

if (!empty($_SESSION['myusername']) && $subcategory >= 1){

  if ($rowcount == 0) {

    mysql_query("INSERT INTO `users_subcategories` (`username`, `SubCategoryId`) VALUES ('$myusername','$subcategory');") or die(mysql_error());

    }

}

?>

[DarkWater]

Any time.  Please mark this topic as solved. =)