how to insert a record into mysql when no records found

[impact]

Hello,

I have a database which stores users preferences along with their personal data into separate tables.

This is secondary database and at the time of user registration I am not creating a unique records for that user. So for every user there would be a time when there would be no records for them in that database.

Here is the thing i want to know, when i look for a particular user's preferences, if there is none. I would like to create it. I have this code, obviously, this dont work. So help on this please.

$sql = "SELECT * FROM can_settings WHERE can_email='$can_email'";
 $getAccount2=mysql_query($sql) or die("Could not query for user");
 $getAccount3=mysql_fetch_row($getAccount2);
 // When there is no such account in the setting database, create an account
 if(($getAccount3 == null) ¦¦ ($getAccount3 == "")){
   $sql = "INSERT INTO can_settings (address,mobile,landline,profile,self_mail,can_email) VALUES ('n','n','n','y','$can_email')";
   if(!$sql){
    print "This does not work";
   }
 }

Thank you,
sd ghosh

[smatts9]

Try using mysql_num_rows.

[rocknbil]

num rows is pretty common . . . however you shouldn't need it. You also shouldn't need to select all records, just the unique record id. There are also methods to insert if not found/update if found using strictly mysql commands, for various related reasons I don't use them.

Try something like this, but note that your original select has a mismatched number of columns and values.

$sql = select unique_id from can_settings where can_email='$can_email'"; 
$getAccount2=mysql_query($sql) or die("Could not query for user"); 
$row=mysql_fetch_row($getAccount2); 
// this will always be a number . . . right? :-P 
if ($row[0] > 0) { 
 $sql = "update can_settings set address = 'n' mobile ='n', landline='n',"; 
 $sql .= "profile='y',self_mail = '',can_email='$can_email' where unique_id='$row[0]'"; 
} 
else { 
 $sql = "insert into can_settings (address,mobile,landline,profile,self_mail,can_email)"; 
 $sql .= " values ('n','n','n','y','missing value','$can_email')"; 
} 
mysql_query($sql) or die("Could not insert or update data"); 

[impact]

Thanks rocknbil for being in a rare form. It is very helpful.