1,105,226 Community Members

Checkbox selection problem with MySQL

Member Avatar
dashawk
Light Poster
39 posts since Jul 2011
Reputation Points: 4 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
bradly.spicer
Posting Whiz in Training
238 posts since Oct 2012
Reputation Points: 47 [?]
Q&As Helped to Solve: 20 [?]
Skill Endorsements: 6 [?]
 
0
 

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

Member Avatar
bops
Posting Whiz
301 posts since Aug 2005
Reputation Points: 1 [?]
Q&As Helped to Solve: 17 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
dashawk
Light Poster
39 posts since Jul 2011
Reputation Points: 4 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

@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

Member Avatar
code739
Posting Whiz in Training
213 posts since May 2012
Reputation Points: 17 [?]
Q&As Helped to Solve: 30 [?]
Skill Endorsements: 6 [?]
 
0
 

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

Member Avatar
dashawk
Light Poster
39 posts since Jul 2011
Reputation Points: 4 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
diafol
Where are my eyes?
12,941 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

Perhaps it's best to post your stuff here?

Member Avatar
dashawk
Light Poster
39 posts since Jul 2011
Reputation Points: 4 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
code739
Posting Whiz in Training
213 posts since May 2012
Reputation Points: 17 [?]
Q&As Helped to Solve: 30 [?]
Skill Endorsements: 6 [?]
 
0
 

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

Member Avatar
dashawk
Light Poster
39 posts since Jul 2011
Reputation Points: 4 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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;
Member Avatar
code739
Posting Whiz in Training
213 posts since May 2012
Reputation Points: 17 [?]
Q&As Helped to Solve: 30 [?]
Skill Endorsements: 6 [?]
 
1
 

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);
        }
    }
?>  
Member Avatar
code739
Posting Whiz in Training
213 posts since May 2012
Reputation Points: 17 [?]
Q&As Helped to Solve: 30 [?]
Skill Endorsements: 6 [?]
 
0
 

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

Member Avatar
dashawk
Light Poster
39 posts since Jul 2011
Reputation Points: 4 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
dashawk
Light Poster
39 posts since Jul 2011
Reputation Points: 4 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
1
 

salamat bai

Question Answered as of 1 Year Ago by code739, bradly.spicer, bops and 1 other
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: