Issues with updating vs. entering into a SQL table

0
votes

I'm working with a freely distributed package called Raspberry Pints to measure flow of beer through keg taps. I wanted to update a few of the features and have done quite well so far. The following problem is all that is currently making me scratch my head.

How it works: I input a beer with a number of specifics for the brew (name, color, alcohol, ibu, etc...) and it is supposed to update a sql table via php. If is use the .php script and html forms as released in the package everything works.

I made changes to add new beer characteristics which required updating a lot of files and adding new columns to the sql table. I've tested every combination of new vs. old format files and have narrowed my issues down to the code below. Currently if I use the default entry form I can create a new beer entry into my extended table without problem. Only after entering the beer with old form can I use my new form to do update and enter the final variables into the table. Because I can enter the final variables into the table I know the code below is at fault.

Can anyone identify where I'm going wrong in it? Basically if the beer exists, update its ID number. If not, make a new entry. I cannot make a new entry no matter what I've tried thus far.

class BeerManager{

function Save($beer){
    $sql = "";
    if($beer->get_id()){
        $sql =  "UPDATE beers " .
                "SET " .
                    "name = '" . encode($beer->get_name()) . "', " .
                    "beerStyleId = '" . encode($beer->get_beerStyleId()) . "', " .
                    "notes = '" . encode($beer->get_notes()) . "', " .
                    "ogEst = '" . $beer->get_og() . "', " .
                    "fgEst = '" . $beer->get_fg() . "', " .
                    "srmEst = '" . $beer->get_srm() . "', " .
                    "ibuEst = '" . $beer->get_ibu() . "', " .
                    "water = '" . $beer->get_water() . "', " .
                    "salts = '" . $beer->get_salts() . "', " .
                    "finings = '" . $beer->get_finings() . "', " .
                    "yeast = '" . $beer->get_yeast() . "', " .
                    "modifiedDate = NOW() ".
                "WHERE id = " . $beer->get_id();

    }else{      
        $sql =  "INSERT INTO beers(`name`, `beerStyleId`, `notes`, `ogEst`, `fgEst`, `srmEst`, `ibuEst`, `water`, `salts`, `finings`, `yeast`, `createdDate`, `modifiedDate`) " .
                "VALUES(" . 
                "'" . encode($beer->get_name()) . "', " .
                $beer->get_beerStyleId() . ", " .
                "'" . encode($beer->get_notes()) . "', " .
                "'" . $beer->get_og() . "', " . 
                "'" . $beer->get_fg() . "', " . 
                "'" . $beer->get_srm() . "', " . 
                "'" . $beer->get_ibu() . "' " .
                "'" . $beer->get_water() . "' " .
                "'" . $beer->get_salts() . "' " .
                "'" . $beer->get_finings() . "' " .
                "'" . $beer->get_yeast() . "' " .
                ", NOW(), NOW())";
    }       
    mysql_query($sql);
}

---EDITED---

Working fix is below. I deleted all and re-wrote key by key. After comparison I saw that commas were missing for all new variables I added in. This is because I simply copied the get_ibu line which didn't contain a comma as the last line before NOW()

function Save($beer){
    $sql = "";
    if($beer->get_id()){
        $sql =  "UPDATE beers " .
                "SET " .
                    "name = '" . encode($beer->get_name()) . "', " .
                    "beerStyleId = '" . encode($beer->get_beerStyleId()) . "', " .
                    "notes = '" . encode($beer->get_notes()) . "', " .
                    "ogEst = '" . $beer->get_og() . "', " .
                    "fgEst = '" . $beer->get_fg() . "', " .
                    "srmEst = '" . $beer->get_srm() . "', " .
                    "ibuEst = '" . $beer->get_ibu() . "', " .
                    "water = '" . $beer->get_water() . "', " .
                    "salts = '" . $beer->get_salts() . "', " .
                    "finings = '" . $beer->get_finings() . "', " .
                    "yeast = '" . $beer->get_yeast() . "', " .
                    "modifiedDate = NOW() ".
                "WHERE id = " . $beer->get_id();
    }else{
        $sql =  "INSERT INTO beers(name, beerStyleId, notes, ogEst, fgEst, srmEst, ibuEst, water, salts, finings, yeast, createdDate, modifiedDate ) " .
                "VALUES(" . 
                    "'". $beer->get_name() . "', " . 
                    $beer->get_beerStyleId() . ", " . 
                    "'". $beer->get_notes() . "', " . 
                    "'". $beer->get_og() . "', " . 
                    "'". $beer->get_fg() . "', " . 
                    "'". $beer->get_srm() . "', " . 
                    "'". $beer->get_ibu() . "', " . 
                    "'". $beer->get_water() . "', " . 
                    "'". $beer->get_salts() . "', " . 
                    "'". $beer->get_finings() . "', " .  
                    "'". $beer->get_yeast() . "', " . 
                    "NOW(), NOW())";
    }
    mysql_query($sql);
}
2
phpmysql
Yes the id field is primary and autoincremented. My concern with adding id is the original/default doesn't include this as a column. – mredhwk10
Would be nice if you could edit your question and remove the unrelevant code from it. – Cyclonecode

2 Answers

0
votes

My first guess would be that the beerStyleId column contains text and that you don't quote the value in your insert query. Try updating your code to:

$sql =  "INSERT INTO beers(`name`, `beerStyleId`, `notes`, `ogEst`, `fgEst`, `srmEst`, `ibuEst`, `water`, `salts`, `finings`, `yeast`, `createdDate`, `modifiedDate`) " .
        "VALUES(" . 
        "'" . encode($beer->get_name()) . "', " .
        "'" . encode($beer->get_beerStyleId()) . "', " .
        "'" . encode($beer->get_notes()) . "', " .
        "'" . $beer->get_og() . "', " . 
        "'" . $beer->get_fg() . "', " . 
        "'" . $beer->get_srm() . "', " . 
        "'" . $beer->get_ibu() . "' " .
        "'" . $beer->get_water() . "' " .
        "'" . $beer->get_salts() . "' " .
        "'" . $beer->get_finings() . "' " .
        "'" . $beer->get_yeast() . "' " .
        ", NOW(), NOW())";

I would also recommend that you enable error reporting by adding the following to the top of your script:

ini_set('display_errors', true);
error_reporting(E_ALL);

Note that you shouldn't use the old mysql_ extension since its deprecated, switch over to mysqli or pdo.

0
votes

I am not sure how much code you edited, or how that interfaces with the rest of the platform, but in your case I think it may be worth checking out the "INSERT ... ON DUPLICATE KEY UPDATE Syntax" which is documented here: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Insead of checking for id, you could simply run one command i.e.

$sql = "INSERT INTO beers('name', 'beerStyleId', 'notes', 'ogEst', 'fgEst', 'srmEst', 'ibuEst', 'water', 'salts', 'finings', 'yeast', 'createdDate', 'modifiedDate') " . "VALUES(" . "'" . encode($beer->get_name()) . "', " . $beer->get_beerStyleId() . ", " . "'" . encode($beer->get_notes()) . "', " . "'" . $beer->get_og() . "', " . "'" . $beer->get_fg() . "', " . "'" . $beer->get_srm() . "', " . "'" . $beer->get_ibu() . "' " . "'" . $beer->get_water() . "' " . "'" . $beer->get_salts() . "' " . "'" . $beer->get_finings() . "' " . "'" . $beer->get_yeast() . "' " . ", NOW(), NOW())". 'ON DUPLICATE KEY UPDATE '. "name = '" . encode($beer->get_name()) . "', " . "beerStyleId = '" . encode($beer->get_beerStyleId()) . "', " . "notes = '" . encode($beer->get_notes()) . "', " . "ogEst = '" . $beer->get_og() . "', " . "fgEst = '" . $beer->get_fg() . "', " . "srmEst = '" . $beer->get_srm() . "', " . "ibuEst = '" . $beer->get_ibu() . "', " . "water = '" . $beer->get_water() . "', " . "salts = '" . $beer->get_salts() . "', " . "finings = '" . $beer->get_finings() . "', " . "yeast = '" . $beer->get_yeast() . "', " . "modifiedDate = NOW()";