Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I am trying to insert/update the MySql database depending on whether a post already exists on the database (I am check 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));
}
share|improve this question

4 Answers

up vote 0 down vote accepted

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'")
}
share|improve this answer
up vote 0 down vote

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

share|improve this answer
up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.