[RESOLVED] execute not working after prepare

[klyxmaster]

without going into long details, the following script has been working for years. I had to make some changes due to the parent company removing some fields. Now its not working - its not giving me an error, its just not populating the db. i put affected rows to watch, and it yeilds a big fat zero.
i had to remove 2 fields - and as you can see, they balance out (should be 31 fields now). worked last night, had to make some more changes this morning, and boom - not working
I really hate to change this to a bind param, as this was working just fine for what I want it to do.

Please help a blind man LOL I am sure its simple, as I have looked at it too long

 foreach($game->game as $g)
                {
                   echo 'Adding: '.$g->gamename.'<br/>';
                    try
                    {
                        $sql = "
                            INSERT INTO games
                            (
                                gameid,gamename,family,familyid,productid,genreid,allgenreid,shortdesc,meddesc,longdesc,
                                bullet1,bullet2,bullet3,bullet4,bullet5,foldername,price,hasdownload,macgameid,
                                hasvideo,hasflash,hasdwfeature,dwwidth,dwheight,releasedate,gamesize,
                                pc_sysreqos,pc_sysreqmhz,pc_sysreqmem,pc_sysreqdx,pc_sysreqhd
                            )
                            VALUES
                            (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                    $query = $db->prepare($sql);
                    $query->execute(array(
                        $g->gameid,     $g->gamename,   $g->family,     $g->familid,    $g->productid,  $g->genreid,    $g->allgenreid, $g->shortdesc,
                        $g->meddesc,    $g->longdesc,   $g->bullet1,    $g->bullet2,    $g->bullet3,    $g->bullet4,    $g->bullet5,    $g->foldername,
                        $g->price,      $g->hasdownload,$g->macgameid,  $g->hasvideo,   $g->hasflash,   $g->hasdwfeature,$g->dwwidth,   $g->dwheight,
                        $g->releasedate,$g->gamesize,   $g->pc_sysreqos,$g->pc_sysreqdx,$g->pc_sysreqmhz,$g->pc_sysreqmem,$g->pc_sysreqdx,$g->pc_sysreqhd
                    ));
                    echo 'affected rows: '.$query->rowcount().'<br/>';

                }
                catch(PDOException $e)
                {
                    echo "Game insertion error<br/>".$e->getMessage();
                }
            }// loop through xml

[pbismad]

Have you set the PDO error mode to exceptions so that an error would cause the try/catch block to work? Also, have you turned off emulated prepared queries? Is the posted code inside of some html markup where any echoed error messages (which usually are within their own html tags) might not be rendered (the error output may be visible in the 'view source' in the browser)?

Next, you have missed one of the points of a prepared query. You prepare them once, before the start of any loop, to save the time needed to send, parse, and plan the execution of the query, then just populate the set of data and execute the query inside of the loop.

Edit: I see the likely cause of the problem. The number of supplied data values is more than the number of place-holders in the sql query statement, and I have seen NO errors thrown in this case.

[klyxmaster]

hi THANKS FOR the quick reply.

here is my database.php

  try
    {
		$db = new PDO($config['mysql_type'].':host='.$config['mysql_host'].';dbname='.$config['mysql_database'], $config['mysql_user'], $config['mysql_pass']);
		$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }
    catch(PDOException $e)
    {
        echo 'Message: ' .$e->getMessage();
    }
	

As mentioned, nothing else in the code was disturbed, just had to adjust a few of the fields. I did a recount, I still come up with 31/31
Also, it worked fine last night, it just when I deleted the db and started fresh with the new updated fields. (minus "gamerank")

[klyxmaster]

interesting, i changed the ATTR_EMULATE_PREPARES, to true, got my errors.. let me investigate that - it does say wrong number of tokens...brb

[klyxmaster]

well i made the nessasary adjustments, it was 32 instead of 31, but stil has the following erros

SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1

[klyxmaster]

found it
was an extra

,$g->pc_sysreqdx