how to update if exist else insert in php mysql

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, Dec 05 '16 at 08:11
Your code is wide open to SQL injection attack! https://xkcd.com/327/ Never use input supplied by the user to build an SQL query string. You should use prepared statements instead. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php — GordonM, Dec 05 '16 at 08:17
what should i do to improve my code and ignore the SQL injection attack — jonel timblaco, Dec 05 '16 at 08:47

Manoj Sharma · Answer 1 · 2016-12-05T10:44:48.650

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 05 '16 at 10:44

answered Dec 05 '16 at 07:24

Manoj Sharma

1,467
2
13
20

1

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 – jonel timblaco Dec 05 '16 at 07:44
Hello Jonel, There was an error in insert statement, Please update, this will work. – Manoj Sharma Dec 05 '16 at 08:15
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 – jonel timblaco Dec 05 '16 at 08:39

barudo · Answer 2 · 2016-12-05T11:29:44.267

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 05 '16 at 11:29

answered Dec 05 '16 at 06:00

barudo

665
4
13

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); – jonel timblaco Dec 05 '16 at 07:11
updated code... added $id on the bind... with that warning, you need to troubleshoot your mysql connection... – barudo Dec 05 '16 at 11:32
still have an error, --Error Inserting into the database.: Query was empty-- – jonel timblaco Dec 06 '16 at 01:35

Akshay · Answer 3 · 2016-12-05T05:59:58.597

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 05 '16 at 05:59

answered Dec 05 '16 at 05:22

Akshay

700
9
23

have an error of Warning: mysql_num_rows() expects exactly 1 parameter, 2 given in C:\wamp64 – jonel timblaco Dec 05 '16 at 05:36
Updated answer will solve the issue. @joneltimblaco – Akshay Dec 05 '16 at 05:49
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 – jonel timblaco Dec 05 '16 at 07:34

Roshan Dandgavhal · Answer 4 · 2016-12-05T05:59:12.930

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 05 '16 at 05:59

answered Dec 05 '16 at 05:42

Roshan Dandgavhal

529
3
12

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 – jonel timblaco Dec 05 '16 at 05:55
Is ID is primary key or any thing else? – Roshan Dandgavhal Dec 05 '16 at 05:57
$sql="insert into info AccountName= '$aname', ExecutiveSummary= '$xsummary'"; – Roshan Dandgavhal Dec 05 '16 at 05:57
Table name is missing – Roshan Dandgavhal Dec 05 '16 at 05:58
table name is info – jonel timblaco Dec 05 '16 at 06:57
Yes I know. But u miss it insert query. check your insert query. – Roshan Dandgavhal Dec 05 '16 at 07:25
Use this query. $sql = "INSERT INTO info ('AccountName', 'ExecutiveSummary') VALUES ('$aname', '$xsummary')"; – Roshan Dandgavhal Dec 05 '16 at 07:25
sir still same error as i said on my first comment. 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 – jonel timblaco Dec 05 '16 at 08:00

Pravin Vavadiya · Answer 5 · 2016-12-05T05:49:17.767

-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 05 '16 at 05:49

answered Dec 05 '16 at 05:26

Pravin Vavadiya

3,195
1
17
34

have an error of Warning: mysql_num_rows() expects exactly 1 parameter, 2 given in C:\wamp64 – jonel timblaco Dec 05 '16 at 05:35
I have update my anwser Please check again – Pravin Vavadiya Dec 05 '16 at 05:49
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 – jonel timblaco Dec 05 '16 at 07:25

how to update if exist else insert in php mysql

5 Answers5