Stopping a database entry based on IF NULL! Topic is solved

[donnan]

Hi guys,

I am trying to stop 2 tables from being updated based on an update on the first table.

Here is what I have:

Code: Select all

//add user to pif_table//
$query = $db->getQuery(true);
$query="UPDATE `pif_table` SET $g = '$uname', @success = 1 WHERE $g IS NULL OR $g = '' AND id = '$id' ";
$db->setQuery($query);
$result = $db->execute();

So, the first table, above is updated only if the $g column is empty.

I only wish the next 2 tables to be updated IF, $g column was empty and was updated above.

I receive a syntax error from the @success = 1 part of the command.

Code: Select all

if ($success == 1){
$query = $db->getQuery(true);
$query="UPDATE `jospk_users` SET tables = tables +1, payments = payments +1, tbl".$pifbase."= tbl".$pifbase." +1 WHERE id = '$uid'";
$db->setQuery($query);
$result = $db->execute();


$query = $db->getQuery(true);
$query = "INSERT INTO `pif_payments` (`table_num`, `r_1`, `r_2`, `r_3`, `r_4`, $g, `amount`) VALUES ('$id', '$r1', '$r2', '$r3', '$r4', '$uname', '$pifbase') ON DUPLICATE KEY UPDATE $g = '$uname'";
$db->setQuery($query);
$result = $db->execute();
}

Your help is most appreciated.

[donnan]

this must be a hard one to come up with an answer. Still waiting patiently!

[Per Yngve Berg]

Code: Select all

.$pifbase." +1 WHERE id = '$uid'";

+1 is inside the string.

Code: Select all

.$pifbase+1." WHERE id =".$uid.";"

Is $uid a number?

[donnan]

Hi Per,

thanks for replying. Much appreciated.

Yes, $uid is a number.

[donnan]

this is where the problem lies.

Code: Select all

$query = $db->getQuery(true);
$query="UPDATE `pif_table` SET $g = '$uname' WHERE $g IS NULL OR $g = '' AND id = '$id' ";
$db->setQuery($query);
$result = $db->execute();

How can I get either a 'true or a false' or a 1 or a 0 if the UPDATE was carried out?

$result returns 1, even if the UPDATE was or wasn't done.

[Per Yngve Berg]

$result will be false only when the query fails, not if the where clause does not returns any rows.

I would have used a trigger routine in the database that fires for each row updated, rather than php.

https://dev.mysql.com/doc/refman/5.6/en ... views.html

[donnan]

SOLVED -

Ended up writing a SELECT statement to check to see if the field was empty first, then if, yes UPDATE.