MySQL Update doesn't works out as i expected

Question

I've been working on a project for school but for some time now i got stuck. So we need to make a management system for the staff and guests of a simulated hotel, this is pretty much fun but the problem is:

When i try to update a value in my database, it doesn't work, here the UPDATE part of the code:

if (!empty ($_GET['id']))
    {$id = $_GET['id'];
    $query = "SELECT * FROM gast WHERE id=$id";
    $result = mysql_query($query);
    if (!$result)

    {$message .=  "Error msg<br>";}
    $aantal = mysql_num_rows($result);
    if ($aantal==1)
    {$row = mysql_fetch_array($result);
    $row['actief'];
    $actief = $row['actief'];
    if ($aantal==1)
    {$query = "UPDATE gast SET actief= 1 WHERE id=$id";
    mysql_query($query);

else if ($actief == 1){
    $query = "UPDATE gast SET actief= 0 WHERE id=$id";
    $result = mysql_query($query);

So what i am trying to create here, is a "toggle" to give a value in my database (called actief). All i got it to do till now is to actually send the value $id within the url, but the rest of the idea is just not working. Now the chance of me making just a stupid mistake is pretty big since i haven't worked with php all that long.

Anyways below you will find the code that sends the ID value to associate the action with the right guest account.

 while ($row = mysql_fetch_array($result)){
        $actief = $row['actief'];
        $id = $row['id'];
    if ($actief==1){ 
        echo
        '<tr><td>'  . $row['id'] .
        '</td><td>' . $row['voornaam'] .
        '</td><td>' . $row['tussenvoegsel'] .
        '</td><td>' . $row['achternaam'] .
        '</td><td>' . $row['tel'] .
        '</td><td>' . $row['mobiel'] .
        '</td><td>' . $row['nationaliteit'] .
        '</td><td>' . $row['kamer'] .
        '</td><td>' . $row['email'] .
        '</td><td>' . "<a href='list_users.php?id=$id'><font color='lime'>" . "<i class='fa fa-thumbs-up'></i>" . "</font>" .
        '</td></tr>';
       }
   else{
        $actief = $row['actief'];
        $id = $row['id'];
            echo
            '<tr><td>'  . $row['id'] .
        '</td><td>' . $row['voornaam'] .
        '</td><td>' . $row['tussenvoegsel'] .
        '</td><td>' . $row['achternaam'] .
        '</td><td>' . $row['tel'] .
        '</td><td>' . $row['mobiel'] .
        '</td><td>' . $row['nationaliteit'] .
        '</td><td>' . $row['kamer'] .
        '</td><td>' . $row['email'] .
        '</td><td>' . "<a href='list_users.php?id=$id'><font color='red'>" . "<i class='fa fa-thumbs-o-up'></i>" . "</font>" . '</a>' .
        '</td></tr>';
       }
   }
   $message .= '</table><br>';
  } 
?>

Right so here is a picture of the layout (ain't really important but you never know). Layout of user editor, green thumbs up indicates the account is activated, red deactivated

So whenever i click on the Thumbs up icon, the page will be reloaded, and the url will change to what it should be, nothing more, nothing less is happening here. If anyone has an idea, please let me know!

Greetings,
Pimmy

What is your error? You really should be able to narrow it down to something more specific. You really shouldn't need to post this much code. — John Conde, Mar 25 '14 at 20:37
Are you getting specific error messages? Is error reporting turned on for your code: http://stackoverflow.com/questions/6575482/how-do-i-enable-error-reporting-in-php ? — Maximus2012, Mar 25 '14 at 20:38
Your code is vulnerable to SQL injections. You should read on [how to prevent them in PHP](http://stackoverflow.com/q/60174/53114). — Gumbo, Mar 25 '14 at 20:47
Thank you both for the feedback, i will short it down to the specific problem! About the error's, sadly no error's where found. — Ioncap, Mar 25 '14 at 20:49
@Gumbo, Thank you for your input! i will dig into it some time later (if i still have time before i need this to be done). but the security (may sound stupid) isn't a high priority during this project since it's only for a simulated company. But still a good idea to add some security! — Ioncap, Mar 25 '14 at 20:59

score 0 · Answer 1 · edited Mar 25 '14 at 21:03

0

For your update statement in the first block of the if statement after you have set a value for the $id, have you tried

UPDATE gast SET actief= 1 WHERE id=".$id;

or alternatively,

$query='UPDATE gast SET actief= 1 WHERE id='.$id;

or

$query=sprintf("UPDATE gast SET actief= 1 WHERE id='$id'");

It could be that the value being passed to your database is just the string $id instead of the value.

edited Mar 25 '14 at 21:03

Sanoob

2,466
4
30
38

answered Mar 25 '14 at 20:43

Howard Combs

133
10

Why are you calling `sprintf` with only one parameter? – gen_Eric Mar 25 '14 at 20:56
Thank you for your input, i tried your first suggestion first, this made it possible to activate the account. Sadly it doesn't seem to work in reverse. The second method didn't work either, same with the 3th method. – Ioncap Mar 25 '14 at 21:12
Apologies I am new here. Could you please elaborate? I thought the only parameter being passed is the $id. – Howard Combs Mar 25 '14 at 21:12
Well the only parameter being actually updated is $actief, $id is the only parameter being used to get the right row for updating the $actief parameter. – Ioncap Mar 25 '14 at 21:15
Pimmy if you haven't already, you should add some echo statements after your queries to make sure that the right statements are being passed to your database. – Howard Combs Mar 25 '14 at 21:16
HFC, i added some echo's as you said, here is the outcome: SELECT * FROM gast WHERE id=233 (amount of found rows matching id) 1 (the update) UPDATE gast SET actief= 1 WHERE id=233 SELECT * FROM gast (result of query) Resource id #6 To me it seems like it should work, but it still doesn't. – Ioncap Mar 25 '14 at 21:28

score 0 · Answer 2 · answered Mar 25 '14 at 22:11

So here i got the answer, please don't laugh at me, haha.

The problem was as easy as changing 2 variables, before:

if ($aantal==1)
{
$row = mysql_fetch_array($result);
$row['actief'];
$actief = $row['actief'];
if ($aantal==0)
{
$query ='UPDATE gast SET actief= 1 WHERE id='.$id;
echo $query . '<br>';
$result = mysql_query($query);

And after:

if ($aantal==1)
{
$row = mysql_fetch_array($result);
$row['actief'];
$actief = $row['actief'];
if ($actief==0) /* this is what went wrong*/
{
$query ='UPDATE gast SET actief= 1 WHERE id='.$id;
echo $query . '<br>';
$result = mysql_query($query);

Alright sorry people for wasting your time, i feel pretty stupid now haha.
Thanks again for all the input!

MySQL Update doesn't works out as i expected

2 Answers2