Deleting selected rows

Question

KazumaKarasu 0 Newbie Poster

9 Years Ago

I hvae been working on this code for hours and can't figure out how to delete information from a database. It is currently claiming that result and checkbox aren't variables... I have looked at countless websites but can't seem to get this to work.
Thank you ahead of time.

    <head>
        <title>Lost Furry Friends</title>
        <link type="text/css" rel="stylesheet" href="LFF.css">
    </head>
    <body>

        <table border="0" width="100%">
            <tbody>
                <tr>
                    <td colspan="4"><div id="csl_site_title" ><br /><center>Lost Furry Friends (LFF) Program</center><br /></div></td>
                </tr>
                <tr>
                    <td width="25%"><center><div id="csl_navigation"><a href="intro.html">Introduction</a></div></center></td>
                    <td width="25%"><center><div id="csl_navigation"><a href="Lost.php">Lost</a></div></center></td>
                    <td width="25%"><center><div id="csl_navigation"><a href="Found.php">Found</a></div></center></td>
                    <td width="25%"><center><div id="csl_navigation"><a href="New_Entry.php">New Entry</a></div></center></td>
                </tr>
                <tr>
                    <td colspan="4">
                        <div id="csl_page_title">
                            <br /><p><center>Administration Page</center><p>
                            <center><br><input name="delete" type="submit" id="delete" value="Delete"></td><br></center>
                        </div>
                    </td>
                </tr>
            </tbody>
        </table>
        <table border="1" style="width:100%">
            <tr>
            <?php
    //Create a new mysqli connection
    @ $db = new mysqli('localhost', 'root', 'secret', 'Final');
    if ($db->connect_error) { 
        echo 'ERROR: Could not connect to database, error is '. $db->connect_error;
        exit;
    } 

    //Make a query against the MySQL server, and store that result
    //in resultset $res
    $res = $db->query("SELECT * FROM PET RIGHT JOIN Pet_Color USING (Pet_ID) 
        JOIN Color USING (Color_ID) 
        LEFT JOIN Species USING(Species_Number) 
        Left Join location using(PET_ID)");

    //Check to make sure the query succeeded 
    if (!$res) {
        echo $db->error . '<br/>';
        exit;
    }

    //Store the result in an associative array
    $result_array = $res->fetch_all(MYSQLI_ASSOC);

    //Create the table to report results
    ?>
        <table border="1">
            <tr>
                <td><b>Delete?</b></td>
                <td><b>ID</b></td>
                <td><b>Picture</b></td>
                <td><b>Name</b></td>
                <td><b>Colour</b></td>
                <td><b>Gender</b></td>
                <td><b>Behaviour</b></td>
                <td><b>Date</b></td>
                <td><b>Birthday</b></td>
                <td><b>Status</b></td>
                <td><b>Specie</b></td>
                <td><b>Address</b></td>
            </tr>
    <?php
    foreach ($result_array as $row){
        $i=0;
        ?>
            <tr>
                <td><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['Pet_ID']; ?>"></td>
                <td><?php echo $row['Pet_ID']?></td>
                <td><?php echo $row['Pet_Image']?></td>
                <td><?php echo $row['Pet_Name']?></td>
                <td><?php echo $row['Color_Description']?></td>
                <td><?php echo $row['Pet_Gender']?></td>
                <td><?php echo $row['Pet_Behavior']?></td>
                <td><?php echo $row['Pet_Date']?></td>
                <td><?php echo $row['Pet_Birthday']?></td>
                <td><?php echo $row['Pet_Status']?></td>
                <td><?php echo $row['Species_Name']?></td>
                <td><?php echo $row['Location_Address']?> <?php echo $row['Location_State']?>, <?php echo $row['Location_Zip']?></td>
            </tr>
        <?php
    }
    ?></table><?php
    if(isset($_POST['delete']))
{
    if(count($_POST['checkbox']) !=0)
    {
        $array = array("checkbox" => $_POST['checkbox']);
        $ids = implode(',', $array);
        $result = mysql_query("DELETE FROM $Pet WHERE Pet_ID IN($ids) ") or die(mysql_error());
    }
}
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete_multiple.php\">";
}


    //Clean up
    $res->close();

    //Close the connection
    $db->close();
?>
            </tr>
        </table>
    </body>
</html>

php

3 Contributors
15 Replies
440 Views
3 Hours Discussion Span
Latest Post 9 Years Ago Latest Post by jj.dcruz

edbr 22 Junior Poster

9 Years Ago

      $result = mysql_query("DELETE FROM $Pet WHERE Pet_ID IN($ids) ") or die(mysql_error());
    }

    0r
          $result = mysqli_query("DELETE FROM $Pet WHERE Pet_ID IN($ids) ") or die(mysql_error());
    }

edbr 22 Junior Poster

9 Years Ago

also here was a similar post https://www.daniweb.com/web-development/php/threads/325005/delete-multiple-rows-using-checkboxes

jj.dcruz 0 Junior Poster

9 Years Ago

your missing the very important thing on your code

<form method="POST">

I would like to point out that one you cant POST your DATA without that command

jj.dcruz 0 Junior Poster

9 Years Ago

that should be on the upper part before the html codes
also i would like to change your query to this format if its ok with you
DELETE FROM Pet WHERE Pet_ID=$ids

Edited 9 Years Ago by jj.dcruz

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

KazumaKarasu 0 Newbie Poster · Answer 1 · 2015-04-23T04:49:38+00:00

joshuajames.delacruz, thanks for pointing that out. It is able to run through the code now without errors. However, it still isn't deleteing any of the rows.

    if(isset($_POST['delete']))
{
    if(count($_POST['checkbox']) !=0)
    {
        $array = array("checkbox" => $_POST['checkbox']);
        $ids = implode(',', $array);
        $result = $db->query("DELETE FROM Pet WHERE Pet_ID=($ids)") or die(mysql_error());
    }
}
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete_multiple.php\">";
}


    //Clean up
    $res->close();

    //Close the connection
    $db->close();
?>

KazumaKarasu 0 Newbie Poster · Answer 2 · 2015-04-23T05:09:03+00:00

Thanks!
It's deleteing now but for some reason it's taking out the entire table.

jj.dcruz 0 Junior Poster · Answer 3 · 2015-04-23T05:12:51+00:00

lets debug the code insert some data i think 3 is enough to know if its calling all the ids

comment this one for now

$result = $db->query("DELETE FROM Pet WHERE Pet_ID=($ids)") or die(mysql_error());

echo this one put this before the $result

echo $query = DELETE FROM Pet WHERE Pet_ID=$ids

KazumaKarasu 0 Newbie Poster · Answer 4 · 2015-04-23T05:19:19+00:00

Like this?

    if(isset($_POST['delete']))
    {
    if(count($_POST['checkbox']) !=0)
        {
        $array = array("checkbox" => $_POST['checkbox']);
        $ids = implode(',', $array);
        echo $db->$query = "DELETE FROM Pet WHERE Pet_ID=$ids";
        //$result = $db->query("DELETE FROM Pet WHERE Pet_ID=$ids") or die(mysql_error());
    }
}

jj.dcruz 0 Junior Poster · Answer 5 · 2015-04-23T05:22:51+00:00

just this

if(isset($_POST['delete']))
    {
    if(count($_POST['checkbox']) !=0)
        {
        $array = array("checkbox" => $_POST['checkbox']);
        $ids = implode(',', $array);
        //using this format makes it easy to identify the problems.
        //but when we echo it before the query you will notice all the id's
        //that will be inserted throug the query
        echo $query = "DELETE FROM Pet WHERE Pet_ID=$ids";

        // the echo result will be like this 
        //DELETE FROM Pet WHERE Pet_ID=1
       // DELETE FROM Pet WHERE Pet_ID=2

        //if we echo the result it will be [obj_some text]
        //$result = $db->query($query) or die(mysql_error());
    }
}

KazumaKarasu 0 Newbie Poster · Answer 6 · 2015-04-23T05:47:54+00:00

DELETE FROM Pet WHERE Pet_ID=Array is the result. This means it's not even getting the number correct? Would this flaw be with the checkbox then?

Currently the code is

    <head>
        <title>Lost Furry Friends</title>
        <link type="text/css" rel="stylesheet" href="LFF.css">
    </head>
    <body>
    <form method="POST">
        <table border="0" width="100%">
            <tbody>
                <tr>
                    <td colspan="4"><div id="Admin_site_title" ><br /><center>Lost Furry Friends (LFF) Program</center><br /></div></td>
                </tr>
                <tr>
                    <td width="25%"><center><div id="csl_navigation"><a href="intro.html">Introduction</a></div></center></td>
                    <td width="25%"><center><div id="csl_navigation"><a href="Lost.php">Lost</a></div></center></td>
                    <td width="25%"><center><div id="csl_navigation"><a href="Found.php">Found</a></div></center></td>
                    <td width="25%"><center><div id="csl_navigation"><a href="New_Entry.php">New Entry</a></div></center></td>
                </tr>
                <tr>
                    <td colspan="4">
                        <div id="csl_page_title">
                            <br /><p><center>Administration Page</center><p>
                            <center><br><input name="delete" type="submit" id="delete" value="Delete"></td><br></center>
                        </div>
                    </td>
                </tr>
            </tbody>
        </table>
        <table border="1" style="width:100%">
            <tr>
            <?php
    //Create a new mysqli connection
    @ $db = new mysqli('localhost', 'root', 'secret', 'Final');
    if ($db->connect_error) { 
        echo 'ERROR: Could not connect to database, error is '. $db->connect_error;
        exit;
    } 

    //Make a query against the MySQL server, and store that result
    //in resultset $res
    $res = $db->query("SELECT * FROM PET RIGHT JOIN Pet_Color USING (Pet_ID) 
        JOIN Color USING (Color_ID) 
        LEFT JOIN Species USING(Species_Number) 
        Left Join location using(PET_ID)");

    //Check to make sure the query succeeded 
    if (!$res) {
        echo $db->error . '<br/>';
        exit;
    }

    //Store the result in an associative array
    $result_array = $res->fetch_all(MYSQLI_ASSOC);


if(isset($_POST['delete']))
    {
    if(count($_POST['checkbox']) !=0)
        {
        $array = array("checkbox" => $_POST['checkbox']);
        $ids = implode(',', $array);
        //using this format makes it easy to identify the problems.
        //but when we echo it before the query you will notice all the id's
        //that will be inserted throug the query
        echo $query = "DELETE FROM Pet WHERE Pet_ID=$ids";
        // the echo result will be like this 
        //DELETE FROM Pet WHERE Pet_ID=1
       // DELETE FROM Pet WHERE Pet_ID=2
        //if we echo the result it will be [obj_some text]
        //$result = $db->query($query) or die(mysql_error());
    }
}
if($result){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=delete_multiple.php\">";
}

    //Create the table to report results
    ?>
        <table border="1">
            <tr>
                <td><b>Delete?</b></td>
                <td><b>ID</b></td>
                <td><b>Picture</b></td>
                <td><b>Name</b></td>
                <td><b>Colour</b></td>
                <td><b>Gender</b></td>
                <td><b>Behaviour</b></td>
                <td><b>Date</b></td>
                <td><b>Birthday</b></td>
                <td><b>Status</b></td>
                <td><b>Specie</b></td>
                <td><b>Address</b></td>
            </tr>
    <?php


    foreach ($result_array as $row){
        $i=0;

        ?>
            <tr>
                <td><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['Pet_ID']; ?>"></td>
                <td><?php echo $row['Pet_ID']?></td>
                <td><?php echo $row['Pet_Image']?></td>
                <td><?php echo $row['Pet_Name']?></td>
                <td><?php echo $row['Color_Description']?></td>
                <td><?php echo $row['Pet_Gender']?></td>
                <td><?php echo $row['Pet_Behavior']?></td>
                <td><?php echo $row['Pet_Date']?></td>
                <td><?php echo $row['Pet_Birthday']?></td>
                <td><?php echo $row['Pet_Status']?></td>
                <td><?php echo $row['Species_Name']?></td>
                <td><?php echo $row['Location_Address']?> <?php echo $row['Location_State']?>, <?php echo $row['Location_Zip']?></td>
            </tr>
        <?php
    }

    ?></table><?php



    //Clean up
    $res->close();

    //Close the connection
    $db->close();
?>
            </tr>
        </table>
        </form>
    </body>
</html>

jj.dcruz 0 Junior Poster · Answer 7 · 2015-04-23T06:02:30+00:00

lets just focus on the delete function of your button no need to put all the codes

try this one: i havent tested it. please notify me about the warning or errors also the one really needed DELETE FROM Pet WHERE Pet_ID=Array

if(isset($_POST['delete']))
    {
        if(isset($_POST['checkbox'])){

            if (is_array($_POST['checkbox'])) {
                foreach($_POST['checkbox'] as $ids){
                  //get the values
                  echo $ids;
                  echo $query = "DELETE FROM Pet WHERE Pet_ID=$ids";
                }

            } else {
                //error
                $value = $_POST['checkbox'];
                echo $value;
            }
}
}

KazumaKarasu 0 Newbie Poster · Answer 8 · 2015-04-23T06:15:41+00:00

DELETE FROM Pet WHERE Pet_ID=
Notice: Undefined variable: result in C:\xampp\htdocs\Final_Project\Admin.php on line 74

These are the errors and prints that comes from the code:

if(isset($_POST['delete']))
    {
        if(isset($_POST['checkbox'])){
            if (is_array($_POST['checkbox'])) {
                foreach($_POST['checkbox'] as $ids){
                  //get the values
                  echo $ids;
                  echo $query = "DELETE FROM Pet WHERE Pet_ID=$ids";
                }
            } else {
                //error
                $value = $_POST['checkbox'];
                echo $value;
            }
}
}

jj.dcruz 0 Junior Poster · Answer 9 · 2015-04-23T06:22:40+00:00

sorry I notice that my code has errors

if(isset($_POST['delete']))
    {
        if(isset($_POST['checkbox'])){
            if (is_array($_POST['checkbox'])) {
                foreach($_POST['checkbox'] as $ids){
                  //get the values
                  echo $ids;
                  echo $query = "DELETE FROM Pet WHERE Pet_ID=$ids";
                }
            } else {
                //error
                //should be $ids not $value
                $ids = $_POST['checkbox'];
                echo $value;
            }
}
}

KazumaKarasu 0 Newbie Poster · Answer 10 · 2015-04-23T06:30:48+00:00

It still has the same results.
DELETE FROM Pet WHERE Pet_ID=
Is there a way to see what the checkbox is returning?

jj.dcruz 0 Junior Poster · Answer 11 · 2015-04-23T06:38:27+00:00

i tried this one but without the query it runs. i think your code about the checkbox values you can inspect the elements to know if it has value

<?php 
if(isset($_POST['delete']))
    {
        if(isset($_POST['checkbox'])){

            if (is_array($_POST['checkbox'])) {
                foreach($_POST['checkbox'] as $ids){
                  //get the values
                  echo $ids;
                }

            } else {
                //error
                $ids = $_POST['checkbox'];
                echo $ids;
                echo "error";
            }
}
}
 ?>

 <!DOCTYPE html>
 <html>
 <head>
     <title></title>
 </head>
 <body>
 <form method="POST">
    <input type="checkbox" name="checkbox[]" value="1">
    <input type="checkbox" name="checkbox[]" value="2">
    <input type="checkbox" name="checkbox[]" value="3">
    <input type="checkbox" name="checkbox[]" value="4">
    <input type="submit" name="delete">
 </form>
 </body>
 </html>