php mysql insert query having trouble

Question

I have a query which is not inserting if i use the where clause, without the where clause it inserts data. this is weird and I have never seen it happen before in WAMP

$key=substr(md5(rand(0, 1000000)), 0, 5);

$key1="INSERT INTO login(key_id) VALUES('$key') 
WHERE (email_id = '" . mysql_real_escape_string($_POST['email_id']) . "')"

if(mysql_query($key1))  
{
   $message = 'User Added!.';
   echo "<SCRIPT>
   alert('$message');
   location='forgotpassword.php';
   </SCRIPT>";     
}

If I echo $_POST['email_id'] it does return valid result

You cannot `INSERT`an existing row (the `WHERE` tries to select an existing row(s) ) — Jens Meinecke, Jan 28 '16 at 11:48
Thanks saty and ubercoder. I am not even sure how i forgot this. i need to use update to update existing row instead .. urrgghhhh full day gone in this simple piece of code | — php_javascript_html_dev, Jan 28 '16 at 11:52
Also since nobody mentioned it I will. Stop using the mysql_* extension as it is deprecated as of PHP version 5.5.0 and is deleted as of PHP version 7.0 instead use [mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) and also your code is open to [SQL-injections](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) use prepared statements. — BRoebie, Jan 28 '16 at 11:53

score 7 · Accepted Answer · answered Jan 28 '16 at 11:41

7

INSERT and WHERE do not mix. when INSERTing, you are creating a new record. WHERE is used with SELECTing DELETEing or UPDATEing, when you have to specify a filter which rows you want to SELECT, DELETE or UPDATE.

if you want to INSERT a row, do not use WHERE. if you want to change a row, use

$key1="UPDATE login SET key_id = '$key' WHERE
 (email_id = '" . mysql_real_escape_string($_POST['email_id']) . "')";

answered Jan 28 '16 at 11:41

Franz Gleichmann

3,420
4
20
30

That said, it should be noted that mysql_functions are deprecated, and in PHP7 removed - you should switch to mysqli_ or PDO instead – Franz Gleichmann Jan 28 '16 at 11:43
Franz, Thanks for the update. I am really dumb. I need the update query as mentioned by saty above and also by you that insert and where dont go together. I had a question will my code stop wroking if php is updated to 7 ? – php_javascript_html_dev Jan 28 '16 at 11:51
1

all mysql_ functions are removed in PHP7, so: yes. but mysqli_ offers everything mysql_ does and more, while PDO offers a very powerful abstraction layer with which you can use the same functions for mysql, pgsql, mssql, CSV-files and many, many, more. – Franz Gleichmann Jan 28 '16 at 11:54
@ABI: if its work than please accept this answer. it will help for others who facing the same issue. good work Franz... – devpro Jan 28 '16 at 12:04

Divakarcool · Answer 2 · 2016-01-28T11:54:27.530

1

Insert is only used on creating new record and where clause is only used if want to set any condition it is used with like select,update,delete.

Try this it will help:-

  $key1="update login set key_id ='$key' WHERE 
           (email_id = '" . mysql_real_escape_string($_POST['email_id']) . "')";

edited Jan 28 '16 at 11:54

answered Jan 28 '16 at 11:51

Divakarcool

473
6
20

3

guess you forgot to replace INSERT with UPDATE? [comment obsolete after answer got edited] – Franz Gleichmann Jan 28 '16 at 11:52

score 0 · Answer 3 · answered Jan 28 '16 at 12:03

I know @Franz-Gleichmann is already explained very well, whats wrong in your code.

You need to use UPDATE for updating data modified code:

$key1 = "UPDATE login SET key_id = '$key' WHERE
(email_id = '" . mysql_real_escape_string($_POST['email_id']) . "')";

Now i am adding two more points:

Please use mysqli_* or PDO, because mysql_* is deprecated and not available in PHP 7.
You missed the termination semi colon on the same line, i hope this is typo error.

php mysql insert query having trouble

3 Answers3