This is driving me nuts. I just started using it today as an alternative to ADODB as PDO looks and feels more natural for me and what Im doing.

Anywho, I have created a DB connection, that works perfect. I can do my first select statement:

$sql = 'SELECT * FROM pc';

foreach($dbh->query($sql) as $a_row){

But my real issue is getting my variables to populate...I am using the following:


$sth = $dbh->prepare('UPDATE stats SET
						name                = :name,
						downloaded     = :downloaded,
						duedate           = :duedate,
						progress     = :progress
					WHERE id            = 1');

  $sth->bindValue(':name', $PARSE->name, PDO::PARAM_STR);
  $sth->bindValue(':downloaded', $PARSE->downloaded, PDO::PARAM_STR);
  $sth->bindValue(':duedate', $PARSE->duedate, PDO::PARAM_STR);
  $sth->bindValue(':progress', $PARSE->progress, PDO::PARAM_INT);
  $passed = $sth->execute();

  if($passed){
      echo "passed";
  } else {
      echo "failed: ". $sth->errorInfo();
      echo var_dump($sth);
  }

I set id to 1 manually for testing purposes. $PARSE is an instance of another class but it is just returning data...which var_dump shows is ok. The error I get from having echo "failed: ". $sth->errorInfo(); echo var_dump($sth); in there is this:

failed: Arrayobject(PDOStatement)#4 (1) { ["queryString"]=> string(176) "UPDATE stats SET name = :name, downloaded = :downloaded, duedate = :duedate, progress = :progress WHERE wuid = 1" }

This is driving me nuts as I have tried everything in the php manual...the different methods and such. If I put my variables right in the SQL statement (e.g. $PARSE->name) the true values show up but I get a warning stating that nothing has been bound (bind).

    May I recommend:

    1. $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    2. Are you sure that your PDO driver supports named parameters? I've only ever used positional parameters with MySQL (Other drivers do support named parameters I'm sure).

    Your current error reporting tactic appears to be non-useful, which is why I suggested ERRMODE_EXCEPTION (Which is in practice, ALWAYS what you want anyway)

    Mark

      MarkR wrote:

      May I recommend:

      1. $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      2. Are you sure that your PDO driver supports named parameters? I've only ever used positional parameters with MySQL (Other drivers do support named parameters I'm sure).

      Your current error reporting tactic appears to be non-useful, which is why I suggested ERRMODE_EXCEPTION (Which is in practice, ALWAYS what you want anyway)

      Mark

      Yeah I just started playing with it yesterday...I didn't even think about driver support etc. I'll give it a shot and see what happens...thx.

        Ok I figured out what it was...the error msg that came back said that I can't run a query while one is already in progress...which I remember reading about...I think Im going to switch over and use fetchAll() to get all my items from MySQL first, and then iterate over the array to do my SQL updates.

        Thanx! And any other suggestions are welcome to a PDO n00b 😉

          So for those who are interested this is what I did...and it works 😉

          $sql = 'SELECT * FROM pc';
$sth = $dbh->prepare($sql);
$sth->execute();
$result = $sth->fetchAll();

foreach($result as $a_row){
$PARSE = new LOG_parse($a_row['log_path']);

if(count($PARSE->errors()) == 0){
      $sth = $dbh->prepare('UPDATE workunit SET
						name        = :name,
						downloaded    = :downloaded,
						duedate       = :duedate,
						progress    = :progress
					WHERE id      = 1');

  $sth->bindValue(':name', $PARSE->protien, PDO::PARAM_STR);
  $sth->bindValue(':downloaded', $PARSE->downloaded, PDO::PARAM_STR);
  $sth->bindValue(':duedate', $PARSE->duedate, PDO::PARAM_STR);
  $sth->bindValue(':progress', $PARSE->percent, PDO::PARAM_INT);

  $sth->execute();
}

          With my error trapping from before, I get "Passed" and checking my DB it did in fact update the records. I'm sure there is fine tuning I should do, but I will refine that another day.

            Write a Reply...