if specific row = null, execute query

Question

how do i check if the value of a column is null, and only then execute the query? for example:

col1 col2 col3
01   abc  

i run a query which first checks if the record exists or not; if it exists, it should execute the update query and if it doesn't exist, it executes the insert query. how do i check if col3 is null and if it is null, it should execute the update query. .

$sql = "SELECT uid FROM `users` WHERE uid = '" . $user_id . "'";
    $result = mysql_query($sql,$conn) or die('Error:' .mysql_error());

    $totalrows = mysql_num_rows($result);
    if($totalrows < 1)
    {
        insertUser($user_id,$sk, $conn);

    }
    else
    {
        updateSessionKey($user_id,$sk,$conn);
    }

Answer 1

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

Not really checking a value of the column, but I don't think you actually need that.

You need to have uid as a UNIQUE column. You try to insert a row for a new user with the given uid; if it finds the user with the same uid, then you do the update instead.

UPDATE:

I guess you did not bother to read the link.

I did not test it, but it should be something like this:

INSERT INTO users (uid, name, session)
VALUES ('login', 'Real Name', 'SeSsIoN_iD')
ON DUPLICATE KEY UPDATE session='SeSsIoN_iD'

This will insert the user if he does not exist, and if he does, it will set a new session key. OR, if you want to preserve the old session key if he already has one,

INSERT INTO users (uid, name, session)
VALUES ('login', 'Real Name', 'SeSsIoN_iD')
ON DUPLICATE KEY UPDATE session=IFNULL(session, 'SeSsIoN_iD')

One query, not three. You were not already doing it.

Answer 2

Is this what you mean?

• If the record does not exist -> insert.
• If the record does exist and its col3 is null -> update
• If the record does exist, but its col3 is not null -> do nothing?

That could be achieved like this (untested):

$sql = "SELECT uid, col3 FROM `users` WHERE uid = '" . $user_id . "'";
$result = mysql_query($sql,$conn) or die('Error:' .mysql_error());

$totalrows = mysql_num_rows($result);
if($totalrows < 1)
{
    insertUser($user_id,$sk, $conn);
}
else
{
    $col3value = mysql_result($result, 0, 'col3');
    if (is_null($col3value))
    {
        updateSessionKey($user_id,$sk,$conn);
    }
}

Answer 3

$sql = "SELECT * FROM `users` WHERE uid = '" . $user_id . "'";
$result = mysql_query($sql,$conn) or die('Error:' .mysql_error());

$totalrows = mysql_num_rows($result);
if($totalrows < 1)
{
    $res = mysql_fetch_array($sql);

    if(!empty($res['col3'])) {

         insertUser($user_id,$sk, $conn);

    }

}
else
{
    updateSessionKey($user_id,$sk,$conn);
}