Checkbox selection problem with MySQL

[dashawk]

Hi guys, I have a php code that displays a list of dishes where a user can check on those dishes and then save the selection to the database. My code works find when selecting and saving the data. But the problem is that when I try to uncheck one of the dishes that is previously been saved, it won't remove the unchecked dish in the database.

Here is the class file I am using:

class.php

<?php

    class Food {
        private $_con;

        function __construct(){
            $this->_con = mysql_connect('localhost','root','') or die(mysql_error());
            mysql_select_db('food', $this->_con) or die(mysql_error());
        }

        function getDishes(){
            $query = 'SELECT * FROM dish';
            $result = mysql_query($query);

            $data = array();

            if($result){
                while($row = mysql_fetch_assoc($result)){
                    $data[] = $row;
                }
                return $data;
            }
        }
        function getPersons(){
            $query = 'SELECT * FROM person';
            $result = mysql_query($query);

            $data = array();

            if($result){
                while($row = mysql_fetch_assoc($result)){
                    $data[] = $row;
                }
            }
            return $data;
        }

        function getPersonData($personID){
            $query = 'SELECT * FROM person WHERE personid='.$personID;
            $result = mysql_query($query);

            $data = array();

            if($result){
                while($row = mysql_fetch_assoc($result)){
                    $data[] = $row;
                }
            }
            return $data;
        }
        function insertPersonDish($personID, $dishID){
            $query = 'SELECT * FROM persondish WHERE personID='.$personID.' AND dishid='.$dishID;
            $result = mysql_query($query);
            if(mysql_num_rows($result) > 0){
                return true;
            } else {
                $insert = 'INSERT INTO persondish(personid, dishid) VALUES('.$personID.','.$dishID.')';
                $result = mysql_query($insert);
                return mysql_insert_id();               
            }
        }
        function deletePersonDish($personID, $dishID){
            $query = 'DELETE FROM persondish WHERE personid='.$personID.' AND dishid='.$dishID;
            return (mysql_query($query));
        }
    }

And here is my view file:

view.php

<?php
    require_once 'class.php';

    $food = new Food;

    $dishes = $food->getDishes();
    $persons = $food->getPersons();

    if(isset($_POST['choices'])){
        $personID = $_POST['personID'];


        foreach ($dishes as $dish) {
            $checked = false;
            if(isset($_POST['food'])){
                $checked = true;
                foreach ($_POST['food'] as $key => $value) {
                    echo 'Inserting Record ID: '.$food->insertPersonDish($_POST['personID'], $value).' with dish ID: '.$value.'<br />';
                }

            } else {
                $checked = false;
            }
            if(!$checked){
                $food->deletePersonDish($_POST['personID'], $dish['dishid']);
                echo 'Removing Data from person ID: '.$_POST['personID'].', Dish ID: '.$dish['dishid'].'<br />';
            }
        }

    }
?>

<form method="post">
    <p><select name="personID">
        <?php
            foreach ($persons as $person) {
                ?><option value="<?php echo $person['personid']; ?>"><?php echo $person['personname']; ?></option><?php
            }
        ?>
    </select></p>
    <?php 
        foreach($dishes as $dish){
            ?><input type="checkbox" name="food[]" value="<?php echo $dish['dishid']; ?>" /><?php echo $dish['dishname']; ?><br /><?php
        }
    ?>

    <input type="submit" name="choices" value="Choose" />
</form>

Do you have any idea how to solve this? Thanks

[bradly.spicer]

Just a hunch here, have you tried doing update rather than delete? So do an if statement (If none are checked then UPDATE to '')

[bops]

Have you tried debugging it by putting echos or var_dumps etc inside your deletePersonDish function? Personally I would echo out the query to see if it what you are expecting, i.e. your are recieving the expected values through the parameters:

function deletePersonDish($personID, $dishID){
    $query = 'DELETE FROM persondish WHERE personid='.$personID.' AND dishid='.$dishID;
    echo $query;
    return (mysql_query($query));
}

This helps you better pinpoint where you are going wrong.

[dashawk]

@bradly I don't need to update the record. an Example if the selected dish is already in the database, it will not do anything thus if the user clicks on the button but did not click 2 dishes (where the 2 dishes is in the database) then it will be deleted because it is not checked or chosen by the user.

@bops I already did echo the query and it did return the right sql statement for delete. I don't need to var_dump it because it is just a delete query. The think is that when I check only the 3rd dish, it will insert all the dish where not what I want.

Example if I check only 1 dish, that 1 dish should be inserted and the other dish that is not checked should be deleted.

Thanks

[code739]

can you give me the tables and data so i could try it.(sql)

[dashawk]

Ok @code739 I have uploaded the files and the sql dump for you to try.

Here is the link http://www.mediafire.com/?f98lvac9zc19k98 for the file.

[diafol]

Perhaps it's best to post your stuff here?

[dashawk]

Hi @diafol, I tried uploading the files here but it failed. I will just try uploading my stuff here again... Failed again

I can't seem to upload a php file or an archive file or an sql dump file here

[code739]

you can just paste the sql code here for the required table.

[dashawk]

Ok here is the sql

CREATE TABLE IF NOT EXISTS `dish` (
  `dishid` int(11) NOT NULL AUTO_INCREMENT,
  `dishname` varchar(30) NOT NULL,
  PRIMARY KEY (`dishid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `dish` (`dishid`, `dishname`) VALUES
(1, 'Lamb Chop Stew'),
(2, 'Grilled Chicken Breast'),
(3, 'Crispy Baked Fish');

CREATE TABLE IF NOT EXISTS `person` (
  `personid` int(11) NOT NULL AUTO_INCREMENT,
  `personname` varchar(30) NOT NULL,
  PRIMARY KEY (`personid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `person` (`personid`, `personname`) VALUES
(3, 'Jake'),
(4, 'Bob'),
(5, 'Karen'),
(6, 'Spartakus');

CREATE TABLE IF NOT EXISTS `persondish` (
  `personid` int(11) NOT NULL,
  `dishid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[code739]

this is your updated code
view.php

    <?php
    require_once 'class.php';
    $food = new Food;
    $dishes = $food->getDishes();
    $persons = $food->getPersons();
    if(isset($_POST['choices'])){
        $personID = $_POST['personID'];

        foreach ($dishes as $dish) {
            $checked = false;
            if(isset($_POST['food'])){
                $checked = true;
                foreach ($_POST['food'] as $key => $value) {
                    echo 'Inserting Record ID: '.$food->insertPersonDish($_POST['personID'], $value).' with dish ID: '.$value.'<br />';
                }
            } else {
                $checked = false;
            }
            if(!$checked){
                $food->deletePersonDish($_POST['personID'], $dish['dishid']);
                echo 'Removing Data from person ID: '.$_POST['personID'].', Dish ID: '.$dish['dishid'].'<br />';
            }
        }

        if($food->personDishCounter($_POST['personID'])> count($_POST['food'])){
                $dishstr = implode($_POST['food'],',');
                $food->deleteNotSelectedDish($_POST['personID'],$dishstr);
                echo 'Removing Data Person ID :'.$_POST['personID'].', Dish ID: '.$dishstr.'<br />';
        }
    }
    ?>
    <form method="post">
    <p><select name="personID">
    <?php
    foreach ($persons as $person) {
    ?><option value="<?php echo $person['personid']; ?>"><?php echo $person['personname']; ?></option><?php
    }
    ?>
    </select></p>
    <?php
    foreach($dishes as $dish){
    ?><input type="checkbox" name="food[]" value="<?php echo $dish['dishid']; ?>" /><?php echo $dish['dishname']; ?><br /><?php
    }
    ?>
    <input type="submit" name="choices" value="Choose" />
    </form>

class.php

    <?php
    class Food {
        private $_con;
        function __construct(){
        $this->_con = mysql_connect('localhost','root','') or die(mysql_error());
        mysql_select_db('test', $this->_con) or die(mysql_error());
        }
        function getDishes(){
        $query = 'SELECT * FROM dish';
        $result = mysql_query($query);
        $data = array();
        if($result){
        while($row = mysql_fetch_assoc($result)){
        $data[] = $row;
        }
        return $data;
        }
        }
        function getPersons(){
        $query = 'SELECT * FROM person';
        $result = mysql_query($query);
        $data = array();
        if($result){
        while($row = mysql_fetch_assoc($result)){
        $data[] = $row;
        }
        }
        return $data;
        }
        function getPersonData($personID){
        $query = 'SELECT * FROM person WHERE personid='.$personID;
        $result = mysql_query($query);
        $data = array();
        if($result){
        while($row = mysql_fetch_assoc($result)){
        $data[] = $row;
        }
        }
        return $data;
        }
        function insertPersonDish($personID, $dishID){
        $query = 'SELECT * FROM persondish WHERE personID='.$personID.' AND dishid='.$dishID; //previous
        $result = mysql_query($query);
        $rowlen = mysql_num_rows($result);
        if($rowlen > 0){
            return true;
        } else {
        $insert = 'INSERT INTO persondish(personid, dishid) VALUES('.$personID.','.$dishID.')';
        $result = mysql_query($insert);
            return mysql_insert_id();
        }
        }
        function deletePersonDish($personID, $dishID){
        $query = 'DELETE FROM persondish WHERE personid='.$personID.' AND dishid='.$dishID;
        return (mysql_query($query));
        }
        function personDishCounter($pid){
            $sql = "SELECT count( dishid ) as count FROM persondish WHERE personid =".$pid;
            $query = mysql_query($sql);
            $result = mysql_fetch_array($query);
            return $result['count'];
        }
        function deleteNotSelectedDish($personID,$dishID){
            $query = 'DELETE FROM persondish WHERE personid='.$personID.' AND dishid not in('.$dishID.')';
            mysql_query($query);
        }
    }
?>  

[code739]

also at view.php @line 25 of my post

if($food->personDishCounter($_POST['personID'])> count($_POST['food']))

plese put a if($checked) condition before ,so that there will be no error once you dint select atleast 1 dish

[dashawk]

Thanks code739 it worked fine now. I should have put the delete function after the foreach.

thanks for the solutions, you helped me a lot.

[dashawk]

salamat bai