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 INSERTan existing row (the WHERE tries to select an existing row(s) ) — Jens Meinecke
– Jens Meinecke, Commented Jan 28, 2016 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
– php_javascript_html_dev, Commented Jan 28, 2016 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 or PDO and also your code is open to SQL-injections use prepared statements. — BRoebie
– BRoebie, Commented Jan 28, 2016 at 11:53

Franz Gleichmann · Accepted Answer · 2016-01-28 11:41:54Z

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, 2016 at 11:41

Franz Gleichmann

3,6004 gold badges24 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Franz Gleichmann Over a year ago

That said, it should be noted that mysql_functions are deprecated, and in PHP7 removed - you should switch to mysqli_ or PDO instead

php_javascript_html_dev Over a year ago

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 ?

Franz Gleichmann Over a year ago

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.

devpro Over a year ago

@ABI: if its work than please accept this answer. it will help for others who facing the same issue. good work Franz...

Divakarcool · Accepted Answer · 2016-01-28 11:54:27Z

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, 2016 at 11:54

answered Jan 28, 2016 at 11:51

Divakarcool

4811 gold badge7 silver badges21 bronze badges

1 Comment

Franz Gleichmann Over a year ago

guess you forgot to replace INSERT with UPDATE? [comment obsolete after answer got edited]

devpro · Accepted Answer · 2016-01-28 12:03:30Z

0

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.

answered Jan 28, 2016 at 12:03

devpro

16.1k3 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

php mysql insert query having trouble

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related