PHP and MYSQL with updating a row by link

Question

Hey guys, I am working on a webpage and I don't know why I can't update a value from my database and display it.

This is my code for the PHP page to display the link. When clicked it will call another PHP program to do the update and then be redisplayed in the display PHP program.

echo "<td class='text pad center'>".$row['deleted']."&nbsp;&nbsp;</td>";
if ( $row['deleted'] == 'y' ) {
    echo '<td class="text center"><a href="delete.php?id='.$row["id"].'">Restore</a>;&nbsp;&nbsp;</td>';
} else {
    echo '<td class="text center"><a href="delete.php?id='.$row["id"].'">Delete</a>;&nbsp;&nbsp;</td>';
}

And in my update program I have this code that will perform the update in my database and then send the new value to be redisplayed.

$id=$_GET['id'];

$sql_query = "SELECT * FROM tablename WHERE id = '$id'";
//Run our sql query
$result = mysqli_query($link, $sql_query) or die('select query failed'. mysqli_error($link));

while ($row = mysqli_fetch_assoc($result)) {
    if ( $row['deleted'] == 'y' ) {
        $change = "UPDATE inventory SET DELETED = 'n' WHERE id = '$id'";
    } else {
        $change = "UPDATE inventory SET DELETED = 'y' WHERE id = '$id'";
    }
    echo "$change";
    mysqli_query($link, $change) or die('select query failed'. mysqli_error($link));
}

//Free resultset (optional)
mysqli_free_result($result);

//Close the MySQL Link
mysqli_close($link);

header("Location: display.php");

I can't find my error.

By the way, don't use y and n for boolean values, use 1 and 0 [true and false respectively]. — moonwave99
– moonwave99, Commented Mar 11, 2013 at 23:04
Or use an ENUM. Or you could use a SET field with "deleted" as one of the flags. 0 or 1 is not the only option ;) — landons
– landons, Commented Mar 11, 2013 at 23:09

Niet the Dark Absol · Accepted Answer · 2013-03-11 23:11:39Z

Your code is currently at great risk for two reasons. First of all, the classic SQL Injection problem, and second never use GET to change things. In addition, your code violates DRY quite a bit.

Try this rewrite:

echo "<td class='text pad center'>".$row['deleted']."&nbsp;&nbsp;</td>";
echo '<td class="text center"><a href="delete.php?id='.$row["id"].'">'.($row['deleted']=='y'?'Restore':'Delete').'</a>;&nbsp;&nbsp;</td>';

And:

// IMPORTANT: Make sure you didn't forget to connect!
$id=mysqli_real_escape_string($link,$_GET['id']);
mysqli_query($link,"UPDATE tablename SET deleted=IF(deleted='y','n','y') WHERE id='$id'")
    or die('update query failed'. mysqli_error($link));
header("Location: display.php");

Note that you should really use 0 and 1 for boolean values, not n and y. If you do this, you can replace the deleted=IF(...) piece with deleted=1-deleted to toggle.

Collectives™ on Stack Overflow

PHP and MYSQL with updating a row by link

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related