Inserting/updating data into MySql database using php

Question

I am trying to insert/update the MySql database depending on whether a post already exists on the database (I am checking this with a unique user_id). The following works:

$select_query = "SELECT * ";
$select_query .= "FROM test ";
$select_query .= "WHERE user_id = '$user_id'";

$check_user_id = mysqli_query($connection, $select_query);

$query  = "INSERT INTO test (";
$query .= "  user_id, name, message";
$query .= ") VALUES (";
$query .= "  '{$user_id}', '{$name}', '{$message}'";
$query .= ")";

$result = mysqli_query($connection, $query);

if ($result) {
    echo "Success!";
} else {
    die("Database query failed. " . mysqli_error($connection));
}

However, when I use the following code with an if/else statement, it does not work anymore, although the console reports "Success!" (meaning $result has a value). Any help would be greatly appreciated. Thanks.

$select_query = "SELECT * ";
$select_query .= "FROM test ";
$select_query .= "WHERE user_id = '$user_id'";

$check_user_id = mysqli_query($connection, $select_query);

if (!$check_user_id) {
    $query  = "INSERT INTO test (";
    $query .= "  user_id, name, message";
    $query .= ") VALUES (";
    $query .= "  '{$user_id}', '{$name}', '{$message}'";
    $query .= ")";
} else {
    $query  = "UPDATE test SET ";
    $query .= "name = '{$name}', ";
    $query .= "message = '{$message}' ";
    $query .= "WHERE user_id = '{$user_id}'";
}

$result = mysqli_query($connection, $query);

if ($result) {
    echo "Success!";
} else {
    die("Database query failed. " . mysqli_error($connection));
}

user2593560 · Accepted Answer · 2013-08-10 07:29:41Z

3

As i understand your code. you are trying to check if the user_id is existing in your database.. i made a simple code and i think its works for me..

    $select_query = mysql_query("SELECT * FROM test WHERE user_id = '$user_id'") or die (mysql_error());
$result = mysql_num_rows($select_query);

if(!$result){
    $query = mysql_query("INSERT INTO test (user_id, name, message) VALUES ('$user_id', '$name', '$message')");
        if($query){
            echo "Success!";
        }
        else
        {
            die (mysql_error());
        }
}
else{
    $query2 = mysql_query("UPDATE test SET name='$name', message='$message' WHERE user_id = '$user_id'")
}

answered Aug 10, 2013 at 7:29

user2593560

1621 gold badge1 silver badge11 bronze badges

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

Comments

lejlot · Accepted Answer · 2013-08-10 07:23:54Z

0

mysql_query returns the operation identifier, not the actual result. This is why $check_user_id is always true, so you are always trying to update (even not existing!) rows.

you have to "read" the result ofmysql_queryby for example using

$check_user_id = mysql_num_rows( mysql_query($connection, $select_query) );

now it returns 0 (false) iff there were no results for q $select_query

answered Aug 10, 2013 at 7:23

lejlot

67k9 gold badges138 silver badges168 bronze badges

Comments

woofmeow · Accepted Answer · 2013-08-10 07:24:13Z

0

This statement is giving you a resource to the result

$check_user_id = mysqli_query($connection, $select_query);

next you are checking for if(!$check_user_id) : this condition evaluates to false because of the negation !. Thus your condition goes to the else part and and never enters the if.

The $result always has value because you are calling it towards the end of the script.

answered Aug 10, 2013 at 7:24

woofmeow

2,41818 silver badges14 bronze badges

Comments

juanra · Accepted Answer · 2013-08-10 10:13:35Z

0

Since you previously know the user_id, and assuming that is a primary key in the table, you could use "ON DUPLICATE KEY UPDATE" clause:

$query = mysql_query("INSERT INTO test (user_id, name, message) 
                     VALUES ('$user_id', '$name', '$message')
                     ON DUPLICATE KEY 
                        UPDATE name='$name', message='$message';
");

Same result with only one query.

Ref: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

answered Aug 10, 2013 at 10:13

juanra

1,66219 silver badges18 bronze badges

Comments

Ashley Medway · Accepted Answer · 2014-02-21 12:09:28Z

0

Use Code for data inserting in mysql.

$query = mysql_query("INSERT INTO test set user_id = '$user_id', name = '$name', message = '$message'");
if($query){
    echo "Success!";
}

edited Feb 21, 2014 at 12:09

Ashley Medway

7,3197 gold badges54 silver badges73 bronze badges

answered Feb 21, 2014 at 11:47

Indrajeet Singh

2,99927 silver badges28 bronze badges

Collectives™ on Stack Overflow

Inserting/updating data into MySql database using php

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related