How to delete a specific entry

tommy168 · March 15, 2011, 1:45pm

Hey guys

I have trouble picking the specific id of an entry i want to delete

so please take a look at the codes first

Here’s the function for making the form:

function am_query_display_quick($page,$order ='DATE DESC'){
global $email;
$conn = db_connect_2();
$color1 = "tableRowOdd";
$color2 = "tableRowEven";
$RowCount = 0;

$result = $conn->query("SELECT pro,pro_update,ana,ana_update,cell,cell_update,cellother,cellother_update,gen,gen_update,genother,genother_update,author,author_update,other,other_update,id
FROM mailing_list
WHERE email = '$email'
ORDER BY $order LIMIT $start_row, $max_per_page;");

echo "<form method = \\"post\\" action=\\".{$_SERVER['PHP_SELF']}.\\">";
echo "<table class=\\"sortable\\" id=\\"query_quick2\\" width=\\"100%\\" >\\r\
";
echo "\	<tr><th></th><th>Promoter
Locus</th><th>Update?</th><th>Anatomical Area</th><th>Update?</th><th>Cell
Type</th><th>Update?</th><th>Other Cell Type</th><th>Update?</th><th>Genetic
Background</th><th>Update?</th><th>Other Gen.
Back.</th><th>Update?</th><th>Author</th><th>Update?</th><th>Other</th><th>Update?</th><th></th>\\r\
";

if($result->num_rows){
while ($row = $result->fetch_array()){
$RowCount ++;
$row_color = ($RowCount % 2) ? $color1 : $color2;

echo "\	<tr class=\\"$row_color\\" ><td><input type=\\"submit\\" name=\\"edit_mail\\" value = \\"Edit\\"/></td>
<td>{$row['pro']}</td><td>{$row['pro_update']}</td><td>{$row['ana']}</td>
<td>{$row['ana_update']}</td><td>{$row['cell']}</td><td>{$row['cell_update']}</td><td>{$row['cellother']}</td>
<td>{$row['cellother_update']}</td><td>{$row['gen']}</td><td>{$row['gen_update']}</td><td>{$row['genother']}</td>
<td>{$row['genother_update']}</td><td>{$row['author']}</td><td>{$row['author_update']}</td><td>{$row['other']}</td><td>{$row['other_update']}</td>
<td><input type=\\"submit\\" name=\\"delete_mail\\" value =\\"Delete\\"/></td>
<td><input type =\\"hidden\\" name = \\"id\\" value=\\"{$row['id']}\\"/></td>
</tr>";


}
}

echo "</table>";
echo "</form>";

And this is where the delete command comes into place (excerpt):


} elseif(isset($_SESSION['user_id']) AND isset($_POST['delete_mail'])){
//user is deleting existing queries
$conn=db_connect_2();
$id = mysqli_real_escape_string($conn, $_POST['id']);
echo $id;

$sql2 = "DELETE FROM mailing_list WHERE id='$id'";
$result = mysqli_query($conn, $sql2) or mysqli_error($conn);

$msgs[] = "Query deleted successfully.";
$body = "account.php";

}

yes it can delete entries all right

but it doesn’t delete a specific entry when i click the “delete” button near it

somehow, it ALWAYS deletes the last row of entry at the table

as you can see, i echoed out $id as it’s referring to the LAST entry as well

So my question is, how to delete a particular entry when i click the corresponding “delete” button??

Thanks

smftre · March 15, 2011, 2:52pm

What is the value of:

tommy168 · March 15, 2011, 3:01pm

What is the value of:

‘id’ column of the table ‘mailing_list’

it turns out that the entry’s id in LAST row of this table is selected every single time

how to fix it up so i can choose to delete only the specific id ?

smftre · March 15, 2011, 3:08pm

From the code it seems that you will have multiple entries in the form?

So there is not just one of these: <input type=hidden name=id value={$row[‘id’]} />

Is that correct?

You should have a form element for each row returned and echo’d, otherwise it won’t know what $_POST[“id”] to use…

tommy168 · March 15, 2011, 3:25pm

yes indeed there are multiple entries in the form

so do i create another form element in the above function just for id?

and is the script that has the delete command sufficient or more needs to be added to correspond to the changes in the function?

it will be great if you could elaborate on that thanks

smftre · March 15, 2011, 3:32pm

A form can have as many submit buttons as you like, but they all do the same thing, they just submit the same form with the same elements, so you should rather have a form around each row of items, that way when you click submit or delete rather, it will process that specific item. Otherwise it will just use the last form name as the only one.

e.g.

function am_query_display_quick($page,$order =‘DATE DESC’){
global $email;
$conn = db_connect_2COLOR=#007700;
$color1 = “tableRowOdd”;
$color2 = “tableRowEven”;
$RowCount = 0[COLOR=#007700];

[/COLOR]$result = $conn->query(“SELECT pro,pro_update,ana,ana_update,cell,cell_update,cellother,cellother_update,gen,gen_update,genother,genother_update,author,author_update,other,other_update,id
FROM mailing_list
WHERE email = ‘$email’
ORDER BY $order LIMIT $start_row, $max_per_page;”);

echo "<table class=\“sortable\” id=\“query_quick2\” width=\“100%\” >\r
";
echo "\ <tr><th></th><th>Promoter
Locus</th><th>Update?</th><th>Anatomical Area</th><th>Update?</th><th>Cell
Type</th><th>Update?</th><th>Other Cell Type</th><th>Update?</th><th>Genetic
Background</th><th>Update?</th><th>Other Gen.
Back.</th><th>Update?</th><th>Author</th><th>Update?</th><th>Other</th><th>Update?</th><th></th>\r
"[COLOR=#007700];
echo “</table>”;

if([/COLOR]$result->num_rows){
while ($row = $result->fetch_arrayCOLOR=#007700){
[/COLOR]$RowCount ++;
$row_color = ($RowCount % 2) ? $color1 : $color2[COLOR=#007700];

[/COLOR][/COLOR]echo “<form method = \“post\” action=\”.{$_SERVER[‘PHP_SELF’]}.\“>”;
echo "<table width=\“100%\” >";
echo “\ <tr class=\”$row_color\" ><td><input type=\“submit\” name=\“edit_mail\” value = \“Edit\”/></td>
<td>{$row[‘pro’]}</td><td>{$row[‘pro_update’]}</td><td>{$row[‘ana’]}</td>
<td>{$row[‘ana_update’]}</td><td>{$row[‘cell’]}</td><td>{$row[‘cell_update’]}</td><td>{$row[‘cellother’]}</td>
<td>{$row[‘cellother_update’]}</td><td>{$row[‘gen’]}</td><td>{$row[‘gen_update’]}</td><td>{$row[‘genother’]}</td>
<td>{$row[‘genother_update’]}</td><td>{$row[‘author’]}</td><td>{$row[‘author_update’]}</td><td>{$row[‘other’]}</td><td>{$row[‘other_update’]}</td>
<td><input type=\“submit\” name=\“delete_mail\” value =\“Delete\”/></td>
<td><input type =\“hidden\” name = \“id\” value=\“{$row[‘id’]}\”/></td>
</tr>";
echo “</table>”;
echo “</form>”;
[COLOR=#000000][COLOR=#007700]
}
}

[/COLOR][/COLOR]

tommy168 · March 15, 2011, 3:48pm

THANK YOU smftre !!!

man this is the best php forum existed on the internet,

i can ALWAYS get great help from here

Appreciate it !

smftre · March 15, 2011, 3:54pm

No problem tommy168!

SpikeZ · March 15, 2011, 4:01pm

One more thing that you should get into the habit of doing is indenting your code.
Its a personal preference but it does make it easier to read and debug.


function am_query_display_quick($page,$order ='DATE DESC'){
    global $email;
    $conn = db_connect_2();
    $color1 = "tableRowOdd";
    $color2 = "tableRowEven";
    $RowCount = 0;

    $result = $conn->query(
        "SELECT 
              pro
            , pro_update
            , ana
            , ana_update
            , cell
            , cell_update
            , cellother
            , cellother_update
            , gen,gen_update
            , genother
            , genother_update
            , author
            , author_update
            , other
            , other_update
            , id
        FROM
            mailing_list
        WHERE 
            email = '$email'
        ORDER BY 
            $order 
        LIMIT 
            $start_row, $max_per_page
            ");

    if($result->num_rows){
        while ($row = $result->fetch_array()){
            $RowCount ++;
            $row_color = ($RowCount % 2) ? $color1 : $color2;
            
            echo '
            <form method = "post" action=" '. $_SERVER['PHP_SELF'] .'">
                <table class="sortable" id="query_quick2" width="100%" >
                    <tr>
                        <th></th>
                        <th>Promoter Locus</th>
                        <th>Update?</th>
                        <th>Anatomical Area</th>
                        <th>Update?</th>
                        <th>Cell Type</th>
                        <th>Update?</th>
                        <th>Other Cell Type</th>
                        <th>Update?</th>
                        <th>Genetic Background</th>
                        <th>Update?</th>
                        <th>Other Gen.Back.</th>
                        <th>Update?</th>
                        <th>Author</th>
                        <th>Update?</th>
                        <th>Other</th>
                        <th>Update?</th>
                        <th></th>
                    </tr>
                    <tr class="'. $row_color .'" >
                        <td><input type="submit" name="edit_mail" value ="Edit"></td>
                        <td>'. $row['pro'] .'</td>
                        <td>'. $row['pro_update'] .'</td>
                        <td>'. $row['ana'] .'</td>
                        <td>'. $row['ana_update'] .'</td>
                        <td>'. $row['cell'] .'</td>
                        <td>'. $row['cell_update'] .'</td>
                        <td>'. $row['cellother'] .'</td>
                        <td>'. $row['cellother_update'] .'</td>
                        <td>'. $row['gen'] .'</td>
                        <td>'. $row['gen_update'] .'</td>
                        <td>'. $row['genother'] .'</td>
                        <td>'. $row['genother_update'] .'</td>
                        <td>'. $row['author'] .'</td>
                        <td>'. $row['author_update'] .'</td>
                        <td>'. $row['other'] .'</td>
                        <td>'. $row['other_update'] .'</td>
                        <td><input type="submit" name="delete_mail" value="Delete"/></td>
                        <td><input type="hidden" name="id" value="'. $row['id'] .'"/></td>
                    </tr>
                </table>
            </form>';
            
        
        }
    }
            
}

tommy168 · March 15, 2011, 4:03pm

Very true, thanks