PHP - Updating MySQL Database

Question

I'm trying to update a MySQL database with PHP.

Here is my code:

$tableName = "Licenses";
$searchVariable = "used";
$selectVariable = "verCode";
$verTime = date('Y-m-d H:i:s');
$userUUID = "test_string";
$verID = "79A4D";

mysql_connect("localhost", "my_user", "my_pass") or die(mysql_error());
mysql_select_db("licenses_db") or die(mysql_error());
$data = mysql_query("SELECT * FROM `{$tableName}` WHERE `{$searchVariable}`='{$verID}'") or die(mysql_error());

while($info = mysql_fetch_array($data)) {
    //Verification ID unused, so verify the user
    foreach($info as $key => $value) {
        echo "$key: $value</br>";
        }
    if ($info['used'] == 0) {
        echo "<br/>UPDATE `{$tableName}` SET '{$selectVariable}'=1,'time'=`{$verTime}`,'UUID'=`{$userUUID}`; WHERE `{$searchVariable}`='{$verID}'<br/>";
            // the above is to see what command is used
        mysql_query("UPDATE `{$tableName}` SET '{$selectVariable}'=1,'time'=`{$verTime}`,'UUID'=`{$userUUID}`; WHERE `{$searchVariable}`='{$verID}'");
        echo "data updated";
        return 'Success';
        }
    //Verification ID was used already
    else {
        echo "found but used";
        return 'Error Message';
        }
    }
echo "not found";
return 'Error Message';

However, the database doesn't update. I have the table Licenses created in license_db. In addition, I have one row with the following values:

verCode = 79A4D
used = 0
UUID = NULL
time = NULL

If I run the program the first time, it should update the database. This is printed out:

0: 79A4D
verCode: 79A4D
1: 
used: 
2: 
UUID: 
3: 
time: 

UPDATE `Licenses` SET 'used'=1,'time'=`2014-02-15 19:14:13`,'UUID'=`test_string`; WHERE `verCode`='79A4D'
data updated

When I run it the second time, used is now 1, and so it should print out found but used. However, the data updated section (with the UPDATE ...) is printed out.

So, the database is not updating. How can I solve this? Thanks!

The [mysql functions are deprecated](http://stackoverflow.com/questions/13944956/the-mysql-extension-is-deprecated-and-will-be-removed-in-the-future-use-mysqli) and therefore not a great place to start learning how to access a MySQL database from PHP. — Matt Gibson, Feb 16 '14 at 00:26
You noticed you entered a `;` delimiter ? try removing it any see what is gonna happen.. — Orel Eraki, Feb 16 '14 at 00:27
@F3AR3DLEGEND, Can you put `or die(mysql_error());` after every `mysql_query` you got, and run so we could see the errors ? — Orel Eraki, Feb 16 '14 at 00:34
You should use backtick for table name and columns and quotes for values, you inverted them in last query — Fabio, Feb 16 '14 at 00:38
@Fabio Yeah that fixed the issue. However, it still updates every time. I don't think I'm getting the value correctly for the column `used`. Using phpAdmin on cPanel, I can see that `used` is set to 1, but the `if ($info['used'] == 0)` conditional is still executed. — Rushy Panchal, Feb 16 '14 at 00:41
@Fabio Sort of. It's a bit, because it's only supposed to be a Boolean value. — Rushy Panchal, Feb 16 '14 at 00:44

score 1 · Accepted Answer · answered Feb 16 '14 at 01:23

1

e.g.

"
UPDATE `$tableName`  
   SET `$selectVariable` = 1
     , `time` = '$verTime'
     , `UUID` = '$userUUID'
 WHERE `$searchVariable` = '$verID';
";

answered Feb 16 '14 at 01:23

Strawberry

33,750
13
40
57

I had another issue. `$info['user']` == 0 is always True, even once the user = 1 (I checked the database using phpAdmin). If I try `== '0'`, it's always False... – Rushy Panchal Feb 16 '14 at 01:25

PHP - Updating MySQL Database

1 Answers1