running a mysql query using PDO.

[dottedquad]

Hello all, Bellow is my second attempt where I'm trying to insert values into my DB which isn't working as expected. I really can't seem to find an issue as to why the values aren't getting inserted:

			try {
    				$dbh = new PDO('mysql:host=localhost;dbname=disability_listing', 'foo', 'bar');
    				$dbh->query("INSERT INTO disability_listing.listing (id, state_key, state_county_key, org_type, org_name, contact_name, address, phone, website, start_date, end_date, enabled) VALUES (NULL, '$state_value', '$county_key', '$org_type_values', '$org_name_value', '$address_value', '$city_value', '$state_value', '$zip_code_value', '$phone_value', '$web_address_value', '$start_date_value', '$end_date_value', '$enabled_value')");

				unset($dbh);
			} catch (PDOException $e) {
				die ($e->getMessage());
			}

I'm completely stumped. I changed the query to see if it would throw an exception and it did. I also did the same to the connection string which also threw an exception. So, I do know the pdo is getting executed. Any suggestions as to why this isn't working and how to fix this? Any help is greatly appreciated.

-Thanks,
Rich

[igorlopez]

It looks like you should get some mysql errors:

$dbh->query("INSERT INTO disability_listing.listing 
(id,    state_key,     state_county_key, org_type,          org_name,          contact_name,      address,                                           phone,           website,              start_date,         end_date,          enabled) VALUES 
(NULL, '$state_value', '$county_key',   '$org_type_values', '$org_name_value', '$address_value', '$city_value', '$state_value', '$zip_code_value', '$phone_value', '$web_address_value', '$start_date_value', '$end_date_value', '$enabled_value')");

since your value list contains more items than your field list.
Personally I always use the PDO help functions to avoid these types of errors. A little bit more clumsy but a few more rows in the file is maybe not an issue depending on load.

/* Set up all table fields 
 * $myPDOList -> field name
 * $myPDOMark -> PDO prepare marker name (must be unique for each prepare statement)
 * $PDOValue  -> Corresponding value to put into this field
 */
$myPDOList[] = "state_key";
$myPDOMark[] = ":state";
$PDOValue[]  = $state_value;

$myPDOList[] = "state_county_key";
$myPDOMark[] = ":county";
$PDOValue[]  = $county_key;

$myPDOList[] = "org_type";
$myPDOMark[] = ":orgT";
$PDOValue[]  = $org_type_values;

$myPDOList[] = "org_name";
$myPDOMark[] = ":orgN";
$PDOValue[]  = $org_name_value;

/* Note, missing php variable
$myPDOList[] = "contact_name";
$myPDOMark[] = ":cont";
$PDOValue[]  = ;
*/

$myPDOList[] = "phone";
$myPDOMark[] = ":phone";
$PDOValue[]  = $phone_value;

$myPDOList[] = "website";
$myPDOMark[] = ":web";
$PDOValue[]  = $web_address_value;

$myPDOList[] = "start_date";
$myPDOMark[] = ":startD";
$PDOValue[]  = $start_date_value;

$myPDOList[] = "end_date";
$myPDOMark[] = ":endD";
$PDOValue[]  = $end_date_value;

$myPDOList[] = "enabled";
$myPDOMark[] = ":enabled";
$PDOValue[]  = $enabled_value;

/* Note, missing field names
$myPDOList[] = "????";
$myPDOMark[] = ":???";
$PDOValue[]  = $address_value;

$myPDOList[] = "????";
$myPDOMark[] = ":???";
$PDOValue[]  = $city_value;

$myPDOList[] = "????";
$myPDOMark[] = ":???";
$PDOValue[]  = $zip_code_value;
*/

/* Build sql part strings */
$listDelim = ", ";
$markDelim = ", ";
for($mark=0; $mark<count($myPDOMark); $mark++)
{
	$myPDOListStr .= $myPDOList[$mark] . $listDelim;
	$myPDOMarkStr .= $myPDOMark[$mark] . $markDelim;
}
/* strip last delimiter */
$myPDOListStr = substr($myPDOListStr, 0, strlen($myPDOListStr)-strlen($listDelim));
$myPDOMarkStr = substr($myPDOMarkStr, 0, strlen($myPDOMarkStr)-strlen($markDelim));
/* create sql string */
$sqlPDO = "INSERT INTO disability_listing.listing (id, ". $myPDOListStr . ") VALUES (NULL, " . $myPDOMarkStr . ")";
/* execute the prepare statement */
$stmt = $dbh->prepare($sqlPDO);
/* bind each parameter */
for($mark=0; $mark<count($myPDOMark); $mark++)
{
	$stmt->bindParam($myPDOMark[$mark], $PDOValue[$mark]);
}
/* perform query */
$stmt->execute();
/* close connection */
$dbh = NULL;

Be prepared if testing this. I have not made any tests on my server expect checking that the string creation works out.

[dottedquad]

igorlopez;10889868 wrote:

It looks like you should get some mysql errors:

$dbh->query("INSERT INTO disability_listing.listing 
(id,    state_key,     state_county_key, org_type,          org_name,          contact_name,      address,                                           phone,           website,              start_date,         end_date,          enabled) VALUES 
(NULL, '$state_value', '$county_key',   '$org_type_values', '$org_name_value', '$address_value', '$city_value', '$state_value', '$zip_code_value', '$phone_value', '$web_address_value', '$start_date_value', '$end_date_value', '$enabled_value')");

since your value list contains more items than your field list.
Personally I always use the PDO help functions to avoid these types of errors. A little bit more clumsy but a few more rows in the file is maybe not an issue depending on load.

/* Set up all table fields 
 * $myPDOList -> field name
 * $myPDOMark -> PDO prepare marker name (must be unique for each prepare statement)
 * $PDOValue  -> Corresponding value to put into this field
 */
$myPDOList[] = "state_key";
$myPDOMark[] = ":state";
$PDOValue[]  = $state_value;

$myPDOList[] = "state_county_key";
$myPDOMark[] = ":county";
$PDOValue[]  = $county_key;

$myPDOList[] = "org_type";
$myPDOMark[] = ":orgT";
$PDOValue[]  = $org_type_values;

$myPDOList[] = "org_name";
$myPDOMark[] = ":orgN";
$PDOValue[]  = $org_name_value;

/* Note, missing php variable
$myPDOList[] = "contact_name";
$myPDOMark[] = ":cont";
$PDOValue[]  = ;
*/

$myPDOList[] = "phone";
$myPDOMark[] = ":phone";
$PDOValue[]  = $phone_value;

$myPDOList[] = "website";
$myPDOMark[] = ":web";
$PDOValue[]  = $web_address_value;

$myPDOList[] = "start_date";
$myPDOMark[] = ":startD";
$PDOValue[]  = $start_date_value;

$myPDOList[] = "end_date";
$myPDOMark[] = ":endD";
$PDOValue[]  = $end_date_value;

$myPDOList[] = "enabled";
$myPDOMark[] = ":enabled";
$PDOValue[]  = $enabled_value;

/* Note, missing field names
$myPDOList[] = "????";
$myPDOMark[] = ":???";
$PDOValue[]  = $address_value;

$myPDOList[] = "????";
$myPDOMark[] = ":???";
$PDOValue[]  = $city_value;

$myPDOList[] = "????";
$myPDOMark[] = ":???";
$PDOValue[]  = $zip_code_value;
*/

/* Build sql part strings */
$listDelim = ", ";
$markDelim = ", ";
for($mark=0; $mark<count($myPDOMark); $mark++)
{
	$myPDOListStr .= $myPDOList[$mark] . $listDelim;
	$myPDOMarkStr .= $myPDOMark[$mark] . $markDelim;
}
/* strip last delimiter */
$myPDOListStr = substr($myPDOListStr, 0, strlen($myPDOListStr)-strlen($listDelim));
$myPDOMarkStr = substr($myPDOMarkStr, 0, strlen($myPDOMarkStr)-strlen($markDelim));
/* create sql string */
$sqlPDO = "INSERT INTO disability_listing.listing (id, ". $myPDOListStr . ") VALUES (NULL, " . $myPDOMarkStr . ")";
/* execute the prepare statement */
$stmt = $dbh->prepare($sqlPDO);
/* bind each parameter */
for($mark=0; $mark<count($myPDOMark); $mark++)
{
	$stmt->bindParam($myPDOMark[$mark], $PDOValue[$mark]);
}
/* perform query */
$stmt->execute();
/* close connection */
$dbh = NULL;

Be prepared if testing this. I have not made any tests on my server expect checking that the string creation works out.

As you pointed out my value list contains more items than my field list. After looking over my sql query and correcting that issue, it now works as expected. I wonder why there wasn't any exceptions thrown? Also, I've been all over google looking for an extensive manual/tutorial on PDO and mysql and haven't came across any well written tutorials with examples. Would you happen to know of any websites?

-Thanks,
Rich

[igorlopez]

Good, as far as tutorials, I am a Manual type of guy, i.e PHP: PDO - Manual but I know others here in the forum has pointed out Examples and Tutorials but I don't remember which posts, sorry.

[Roger_Ramjet]

If you are using PDO then the best thing is to use Prepared Statements

Here are some good articles/tutes by Alejandro Gervasio on php and pdo
http://www.devshed.com/c/a/PHP/Using-PDO-Objects-in-PHP-5/
http://www.devshed.com/c/a/PHP/Handling-Result-Sets-and-More-with-PDO-Objects-in-PHP-5/
http://www.devshed.com/c/a/PHP/Working-with-Prepared-Queries-with-PDO-Objects-in-PHP-5/

and here is a pdo singleton

<?php
/*
David Soussan 2008

singleton pdo database class
use as follows:

get PDO object to work with
$pdo = PDODB::getInstance();


run a select query and get the results as assoc array
$sql = "SELECT * FROM tbl WHERE col1 = ? AND col2 = ?";
$params = array('test1', 'test2');
$result = $pdo->getData($sql, $params);

run an insert/update/delete query and get the rows affected
$sql = "INSERT INTO tbl (col1, col2) VALUES (?, ?)";
$params = array('val1', 'val2');
$rowcount = $pdo->editData(sql, $params);


*/

class PDODB {
  static protected $instance = null;
  protected $conn;  

  protected $host = '';
  protected $user = '';
  protected $pass = '';
  protected $database = '';
  protected $stmt;

  private function __construct() {
    // fill the connection vars from wherever you choose
    $this->connectDB();
  }

  private function connectDB() {  

    // Connect to the database
    try {
        $this->conn = new PDO('mysql:host=' . $this->host . ';dbname=' . $this->database, $this->user, $this->pass);
        $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch(PDOException $e) {
        throw new Exception('DATABASE ERROR: ' . $e->getMessage());
    }
  }

  public static function getInstance() {
    // method for returning db 
    if(self::$instance == null)
     {
      self::$instance = new PDODB();
     }
     return self::$instance;
  }

  public function getData($sql,$params) {
       // run a select query
    try {
      $this->stmt = $this->conn->prepare($sql);
      $this->stmt->execute($params);
      return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
    } 
    catch(PDOException $e) {
      throw new Exception('DATABASE ERROR: ' . $e->getMessage());
    }      

  }

  public function editData($sql,$params) {
    // run an insert/update/delete query
    try {
      $this->conn->beginTransaction();
      $this->stmt = $this->conn->prepare($sql);
      $this->stmt->execute($params);
      $this->conn->commit();
      return $this->stmt->rowCount();
    }
    catch(PDOException $e) {
      $this->conn->rollBack();
      throw new Exception('DATABASE ERROR: ' . $e->getMessage());
    }
  }

  public function __destruct() {
        $this->conn = null;
  }
}

?>