更新和删除特定记录

Question

I have written code to retrieve all the images from database for a specific city, and I want to be able to delete a specific image or to change the caption.

The problem is:

the code always work on the last image only! I hope you guys will be able to help me with this problem.

Retrieve code:

<?php
$City_name=$_REQUEST['id'];
$Image_query = "SELECT * FROM image where City_name ='".$City_name."' ";
$Image_result = mysqli_query($dbcon,$Image_query); 

echo "<table>";
while ($row = mysqli_fetch_array($Image_result))
{
    $image_id = $row['Image_id'];
    $image = $row['Image_url'];
    $Caption = $row['Caption']; 

    echo "<tr style='float:right;'>";
    echo "<td>"; ?> <img  src="<?php echo $image ; ?>"/>  <br> 
        <input type="text" name="caption" value="<?php echo $Caption ;?>" /> 
        <br> <input name="delete" type="submit" value="Delete picture" />
        <br> <input name="Update_caption" type="submit" value="change caption" />
    <?php echo "</td>"; 
    echo "<td>"; ?> <input class="input-image" type="hidden" name="id" value="<?php echo $image_id ;?>" /> 
    <?php echo "</td>"; 
} /* End of while loop */
echo "</tr>"; 
echo"</table>";     
?>

Update code :

if (isset($_POST['Update_caption'])) {
    $ImageID = $_POST['id'];
    $ImageCaption = $_POST['caption'];
    $sql = mysqli_query ($dbcon,"UPDATE `image` SET `Caption`='".$ImageCaption."' WHERE `Image_id`='".$ImageID."' ");
    if ($sql) {
        echo "done";
    } else { echo "error"; } 
}

Delete code :

if (isset($_POST['delete'])) {
    $ImageID = $_POST['id'];
    $sql = mysqli_query ($dbcon,"DELETE FROM `image` where `Image_id` = '".$ImageID."' ");
    if ($sql) {
        echo "done";
    } else { echo "error"; } 
}

Answer 1

$_POST['id'] (sql injection alert!) contains the contents of the input element that has a name attribute id.

You are echoing out your input elements in a loop, all with the same name so the last one will overwrite all the previous ones.

You should use an array like for example:

<input class="input-image" type="hidden" name="id[<?php echo $image_id ;?>]" value="<?php echo $image_id ;?>" />

So that your $_POST['id'] is an array containing all elements.

The same applies to other input elements like the caption.

An alternative, especially for your delete option, would be to wrap every image in its own form. But keep in mind that you will need valid html for that to work, you can't have a form that opens in a row element and spans different columns.

And note that you should really use a prepared statement to close the sql injection hole you have now.