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

2

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.

edited Dec 5, 2016 at 10:44

answered Dec 5, 2016 at 7:24

Manoj Sharma

1,4552 gold badges13 silver badges20 bronze badges

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

3 Comments

jonel timblaco Over a year ago

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

Manoj Sharma Over a year ago

Hello Jonel, There was an error in insert statement, Please update, this will work.

jonel timblaco Over a year ago

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

1

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.

edited Dec 5, 2016 at 11:29

answered Dec 5, 2016 at 6:00

barudo

6651 gold badge4 silver badges13 bronze badges

3 Comments

jonel timblaco Over a year ago

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);

barudo Over a year ago

updated code... added $id on the bind... with that warning, you need to troubleshoot your mysql connection...

jonel timblaco Over a year ago

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

-1

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')";

    }

edited Dec 5, 2016 at 5:49

answered Dec 5, 2016 at 5:26

Pravin Vavadiya

3,2071 gold badge19 silver badges34 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

Pravin Vavadiya Over a year ago

I have update my anwser Please check again

jonel timblaco Over a year ago

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