how to update if exist else insert in php mysql [duplicate]

Question

I want to update my database if ID is already exist else insert if ID is not exist.

process.php:

$servername = "localhost"; $username = "root"; $password = ""; $dbname = "maindata"; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) { die("<center>Connection failed: " . mysqli_connect_error() . "</center"); } $id = $_POST['id']; $aname = $_POST['aname']; $xsummary = $_POST['xsummary']; $sql=mysql_query("SELECT * FROM info WHERE ID = $id"); if (mysql_num_rows($sql) == $id) { $sql="update info set AccountName= '$aname', ExecutiveSummary='$xsummary'"; } else { $sql="insert into AccountName= '$aname', ExecutiveSummary= '$xsummary'"; }

Some one help me on my problem.

Thanks in advance.

can you give me an example on how to use the INSERT IGNORE ... ON DUPLICATE KEY UPDATE... i'm only new in php and still watching a tutorial — jonel timblaco
– jonel timblaco, Commented Dec 5, 2016 at 8:11
Your code is wide open to SQL injection attack! xkcd.com/327 Never use input supplied by the user to build an SQL query string. You should use prepared statements instead. php.net/manual/en/mysqli.quickstart.prepared-statements.php — GordonM
– GordonM, Commented Dec 5, 2016 at 8:17
what should i do to improve my code and ignore the SQL injection attack — jonel timblaco
– jonel timblaco, Commented Dec 5, 2016 at 8:47

Manoj Sharma · Accepted Answer · 2016-12-05 10:44:48Z

Please have a look on below code.

$servername = "localhost"; $username = "root"; $password = ""; $dbname = "maindata"; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) { die("<center>Connection failed: " . mysqli_connect_error() . "</center"); } $id = $_POST['id']; $aname = $_POST['aname']; $xsummary = $_POST['xsummary']; $sql=mysqli_query($conn, "SELECT * FROM info WHERE ID = $id"); if (mysqli_num_rows($sql) > 0) { $sqlUpdate="update info set AccountName= '$aname', ExecutiveSummary= '$xsummary'"; mysqli_query($conn,$sqlUpdate); } else { $sqlInsert="insert into info set AccountName= '$aname', ExecutiveSummary= '$xsummary'"; mysqli_query($conn,$sqlInsert); }

Your actual issue in code, you were connection database with mysqli, but fetching data from table with mysql. Please use this code and will work fine.

*Note :- There was an error in insert statement, Please update and this will work.

thanks man the update is working but if the id is not exist on the database the sqlInssert is not working... thumbs up to you
Hello Jonel, There was an error in insert statement, Please update, this will work.
Sir still not working the insert i also added the "set" before AccountName.... i think the table name is missing in $sqlInsert... but i already added the table name and still not working

barudo · Accepted Answer · 2016-12-05 11:29:44Z

You can use INSERT ... ON DUPLICATE KEY UPDATE...

$servername = "localhost"; $username = "root"; $password = ""; $dbname = "maindata"; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) { die("<center>Connection failed: " . mysqli_connect_error() . " </center"); } $id = isset($_POST['id']) && $_POST['id'] ? $_POST['id'] : 0; $aname = $_POST['aname']; $xsummary = $_POST['xsummary']; $stmt=mysqli_prepare($conn, "INSERT into info (`ID`, `AccountName`, `ExecutiveSummary`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `AccountName`=VALUES(`AccountName`), `ExecutiveSummary`=VALUES(`ExecutiveSummary`) "); mysqli_stmt_bind_param($stmt, 'iss', $id, $aname, $xsummary); mysqli_stmt_execute($stmt);

I added parameter binding here for you to learn. Don't insert information coming from your inputs directly to the query. Its unsafe.

have an error Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in C:\wamp64 in mysqli_stmt_bind_param($stmt, 'iss', $aname, $xsummary); and mysqli_stmt_execute($stmt);
updated code... added $id on the bind... with that warning, you need to troubleshoot your mysql connection...
still have an error, --Error Inserting into the database.: Query was empty--

Roshan Dandgavhal · Accepted Answer · 2016-12-05 05:59:12Z

0

Try Following code

$sql = @mysql_query("SELECT * FROM info WHERE ID = $id"); $num_rows = @mysql_num_rows($sql); if ($num_rows > 0) { //update query $sql="update info set AccountName= '$aname', ExecutiveSummary='$xsummary'"; } else { //insert query $sql = "INSERT INTO info ('AccountName', 'ExecutiveSummary') VALUES ('$aname', '$xsummary')"; }

edited Dec 5, 2016 at 5:59

answered Dec 5, 2016 at 5:42

Roshan Dandgavhal

5593 silver badges12 bronze badges

8 Comments

jonel timblaco Over a year ago

Error Inserting into the database.: INSERT INTO info ---- have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

Roshan Dandgavhal Over a year ago

Is ID is primary key or any thing else?

Roshan Dandgavhal Over a year ago

$sql="insert into info AccountName= '$aname', ExecutiveSummary= '$xsummary'";

Roshan Dandgavhal Over a year ago

Table name is missing

jonel timblaco Over a year ago

table name is info

|

Akshay · Accepted Answer · 2016-12-05 05:59:58Z

0

All is good need to replace this

 if (mysql_num_rows() == $id) { $sql="update info set AccountName= '$aname', ExecutiveSummary='$xsummary'"; }

with

mysql_query($sql); if (mysql_num_rows()>0) { $sql="update info set AccountName= '$aname', ExecutiveSummary='$xsummary' where ID= $id"; }

edited Dec 5, 2016 at 5:59

answered Dec 5, 2016 at 5:22

Akshay

7109 silver badges23 bronze badges

3 Comments

jonel timblaco Over a year ago

have an error of Warning: mysql_num_rows() expects exactly 1 parameter, 2 given in C:\wamp64

Akshay Over a year ago

Updated answer will solve the issue. @joneltimblaco

jonel timblaco Over a year ago

still have an 3 error Warning: mysqli_query() expects at least 2 parameters, 1 given in C in $sql=mysqli_query and Warning: mysqli_query() expects at least 2 parameters, 1 given in C in mysqli_query($sql); and Warning: mysqli_num_rows() expects exactly 1 parameter, 0 given in C in if (mysqli_num_rows

Pravin Vavadiya · Accepted Answer · 2016-12-05 05:49:17Z

Try to something like this

 $servername = "localhost"; $username = "root"; $password = ""; $dbname = "maindata"; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) { die("<center>Connection failed: " . mysqli_connect_error() . "</center"); } $id = $_POST['id']; $aname = $_POST['aname']; $xsummary = $_POST['xsummary']; $sql=mysql_query("SELECT * FROM info WHERE ID = $id"); $result = mysqli_query($conn, $sql); $count=mysql_num_rows($result); if ($count > 0) { $sql="update info set AccountName= '$aname', ExecutiveSummary='$xsummary' where ID =".$id; } else { $sql = "INSERT INTO info ('AccountName', 'ExecutiveSummary') VALUES ('$aname', '$xsummary')"; }

have an error of Warning: mysql_num_rows() expects exactly 1 parameter, 2 given in C:\wamp64
still have an 3 error Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\wamp64 in $sql=mysqli_query and Warning: mysqli_query(): Empty query in C in $result and Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C in $count

Collectives™ on Stack Overflow

how to update if exist else insert in php mysql [duplicate]

5 Answers 5

3 Comments

3 Comments

8 Comments

3 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

8 Comments

3 Comments

3 Comments

Linked

Related