2

Possible Duplicate:
“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

Hello peoples

$sql1="SELECT * FROM `table` WHERE `user` = '$user'";
$res1=mysql_query($sql1);
if(!$res1||mysql_num_rows($res1)<1){
$sql2="INSERT INTO `table` (`user`) VALUES ('$user')";
$res2=mysql_query($sql2);
if(!$res2){echo 'Yes';}else{echo 'No';}
}
else{echo 'user already exists!';}

Can to combine the two queries into one?

Community
  • 1
  • 1
John Smith
  • 107
  • 1
  • 10

2 Answers2

3

Use this query .

INSERT INTO USER SELECT * FROM TABLE WHERE user='$user'

the number of column in table table and user table must be same ,their type also.

you can also use

INSERT into user
SELECT * from table 
WHERE user='$user'
AND 
   NOT EXISTS (SELECT * FROM USER 
              WHERE USER = '$user')
Arun Killu
  • 13,581
  • 5
  • 34
  • 61
3

If you set your column user to UNIQUE, there'll be a maximum of ONE entry of each user. To ALTER TABLE and make the column UNIQUE:

ALTER TABLE `table`
    ADD UNIQUE INDEX `user` (`user`);

And then, a single query like this would be good:

$res = mysql_query("INSERT IGNORE INTO `table`(`user`) VALUES( '".mysql_real_escape_string($user) . "')";
echo ( mysql_affected_rows() == 0 ) ? "No" : "Yes";

mysql_affected_rows().

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
  • if i have dublicte value me need automatic set new value $user, if i will be use ignore, row not insert only... if i have dublicate, me need auto generate new value $user. – John Smith Sep 29 '12 at 03:20
  • then insert table user ,without giving any value to the auto increment field sql will do it for you..or else you post your table structure @john – Arun Killu Sep 29 '12 at 03:30
  • problem! i give 'Yes' every time. Whow i can see that `user` with this value already exists in `table` ? – John Smith Sep 29 '12 at 05:55
  • @JohnSmith Edited the reply. Included [mysql_affected_rows](http://php.net/manual/en/function.mysql-affected-rows.php) – hjpotter92 Sep 29 '12 at 10:58