12 Replies - 756 Views - Last Post: 15 July 2013 - 03:06 PM Rate Topic: -----

#1 rjolitz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 110
  • Joined: 17-May 08

PDO question - Query isn't working

Posted 14 July 2013 - 12:02 PM

Greetings All.

First! The code................

<?php 

include ('config.php');

$dsn = "mysql:host=$server; dbname=$database";


$opt = array( 
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
);

$pdo = new PDO($dsn, $username, $password);

$firstname = 'Richard';
$lastname = 'Jolitz';
$email = 'northshorerich@gmail.com';
$address1 = '123 ANY STREET';
$address2 = '.';
$city = 'Everytown';
$state = 'wi';
$zip = '12345';
$password = 'DUMMYPASSWORD';
$phone = '578-123-0125';

$passwordh = hash(md5, $password);





$setup = $pdo->prepare(" INSERT INTO student VALUES (firstname, lastname,email,address1,address2,city,state,zipcode,password,phonenumber) values (:first, :last, :email, :addr1, :addr2, :city, :state, :zip, :pass, :phone");


$setup->bindParam(':first', $firstname, PDO::PARAM_INT);
$setup->bindParam(':last', $lastname, PDO::PARAM_INT);
$setup->bindParam(':email', $email, PDO::PARAM_INT);
$setup->bindParam(':addr1', $address1, PDO::PARAM_INT);
$setup->bindParam(':addr2', $address2, PDO::PARAM_INT);
$setup->bindParam(':city', $city, PDO::PARAM_INT);
$setup->bindParam(':state', $state, PDO::PARAM_INT);
$setup->bindParam(':zip', $zip, PDO::PARAM_INT);
$setup->bindParam(':pass', $passwordh, PDO::PARAM_INT);
$setup->bindParam(':phone', $phone, PDO::PARAM_INT);




if ($setup->execute()) {

echo "Query Succeeded!!";
}
else
{
echo "Query Failed!!";
}






?>






Whenever I execute this, the query fails. The connection to the DB works, I checked that first. I'm sure I"m missing something obvious.

Maybe fresh eyes will see it as after a couple hours I don't!!

This is my first attempt using PDO too.

Is This A Good Question/Topic? 0
  • +

Replies To: PDO question - Query isn't working

#2 Kruithne  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 99
  • View blog
  • Posts: 442
  • Joined: 28-July 09

Re: PDO question - Query isn't working

Posted 14 July 2013 - 12:04 PM

Where you are using bindParam just use bindValue and forget the third parameter. Also, stray away from MD5 for password storing, look into use of the PHP cyrpt() function.

This post has been edited by Kruithne: 14 July 2013 - 12:05 PM

Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: PDO question - Query isn't working

Posted 14 July 2013 - 12:13 PM

PDO::PARAM_INT

You are declaring all parameters as integers. This may not be the problem as PHP may still convert these to strings, but I would use the correct constants.

http://php.net/manua...o.constants.php

Add these lines

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

at the top of your script to see all errors (during development).

This post has been edited by andrewsw: 14 July 2013 - 12:15 PM

Was This Post Helpful? 1
  • +
  • -

#4 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3530
  • View blog
  • Posts: 10,173
  • Joined: 08-June 10

Re: PDO question - Query isn't working

Posted 14 July 2013 - 01:34 PM

and you forgot to pass your options to the PDO instantiation.
Was This Post Helpful? 1
  • +
  • -

#5 rjolitz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 110
  • Joined: 17-May 08

Re: PDO question - Query isn't working

Posted 14 July 2013 - 04:39 PM

View PostDormilich, on 14 July 2013 - 04:34 PM, said:

and you forgot to pass your options to the PDO instantiation.


I'll look this up but I'm not sure what you mean by this.
Was This Post Helpful? 0
  • +
  • -

#6 Kruithne  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 99
  • View blog
  • Posts: 442
  • Joined: 28-July 09

Re: PDO question - Query isn't working

Posted 14 July 2013 - 05:11 PM

$pdo = new PDO($dsn, $username, $password);

Here you don't pass through the $opt variable which you declared. Below is correct.
$pdo = new PDO($dsn, $username, $password, $opt);

This post has been edited by andrewsw: 14 July 2013 - 05:20 PM
Reason for edit:: Removed paren

Was This Post Helpful? 1
  • +
  • -

#7 rjolitz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 110
  • Joined: 17-May 08

Re: PDO question - Query isn't working

Posted 15 July 2013 - 03:24 AM

Thanks Guys!

Finally got it functioning. Appreciate the help.
Was This Post Helpful? 0
  • +
  • -

#8 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2957
  • View blog
  • Posts: 10,181
  • Joined: 08-August 08

Re: PDO question - Query isn't working

Posted 15 July 2013 - 06:52 AM

Show us your working code so we can tell you if it's right. Working code isn't proof that it is.
Was This Post Helpful? 0
  • +
  • -

#9 rjolitz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 110
  • Joined: 17-May 08

Re: PDO question - Query isn't working

Posted 15 July 2013 - 10:11 AM

Here is the working code. It is writing to the table.

I know I shouldn't use MD5, working on changing that.

 <?php 
error_reporting(E_ALL);  
ini_set('display_errors', '1'); 


include ('config.php');

$dsn = "mysql:host=$server; dbname=$database";



$opt = array( 
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
);

$pdo = new PDO($dsn, $username, $password, $opt);




$firstname = 'Richard';
$lastname = 'Jolitz';
$email = "northshorerich@gmail.com";
$address1 = "1 this old st";
$address2 = 'no secont address';
$city = 'This old town';
$state = 'CA';
$zip = '123456';
$password = '########';
$phone = '578-123-0125';

$passwordh = hash('md5', $password);

$setup = $pdo->prepare('INSERT INTO student(studentid, firstname, lastname,email,address1,address2,city,state,zipcode,password,phonenumber) VALUES (:id, :first, :last, :email, :addr1, :addr2, :city, :state, :zip, :pass, :phone)');



 $setup->bindValue(':id', '');
 $setup->bindValue(':first', $firstname);
 $setup->bindValue(':last', $lastname);
 $setup->bindValue(':email', $email); 
 $setup->bindValue(':addr1', $address1);
 $setup->bindValue(':addr2', $address2);
 $setup->bindValue(':city', $city);
 $setup->bindValue(':state', $state);
 $setup->bindValue(':zip', $zip);
 $setup->bindValue(':pass', $passwordh);
 $setup->bindValue(':phone', $phone);

 
 


if ($setup->execute()) {

echo 'Query Worked';
}
else
{
echo 'Query Failed';
}


echo '<br><br>';

var_dump($setup);



?>




Was This Post Helpful? 0
  • +
  • -

#10 Kruithne  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 99
  • View blog
  • Posts: 442
  • Joined: 28-July 09

Re: PDO question - Query isn't working

Posted 15 July 2013 - 10:29 AM

Some suggestions for the code you've posted.
  • Don't use MD5 for password hashing (as you said, you're aware, just making a point) and look into using the PHP crypt() function which is documented here ( http://php.net/manua...ction.crypt.php ).
  • The $opt array does not need to be a seperate variable, simply declare it inside the PDO constructor.
    $pdo = new PDO($dsn, $username, $password, array(
    	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    	PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
    ));
    

  • Move the $dsn value to your config.php file and make it a constant alone with the $username and $password
  • Rather than constructing the PDO object in your code I would create a static DB wrapper that sits outside and is included in all your files, that way you are not creating multiple connections to your database. ( See here: https://gist.github....ruithne/6001759 )
  • When writing large queries, don't let them become one really long line as it makes things harder to read, try tidying them up a little.

This post has been edited by Kruithne: 15 July 2013 - 10:31 AM

Was This Post Helpful? 0
  • +
  • -

#11 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3530
  • View blog
  • Posts: 10,173
  • Joined: 08-June 10

Re: PDO question - Query isn't working

Posted 15 July 2013 - 11:01 AM

View PostKruithne, on 15 July 2013 - 07:29 PM, said:

  • Rather than constructing the PDO object in your code I would create a static DB wrapper that sits outside and is included in all your files, that way you are not creating multiple connections to your database. ( See here: https://gist.github....ruithne/6001759 )

or use Dependency Injection.

@Kruithne:
public static function prepareObjects(PDOStatement $query)
{
    return $query->fetchAll(PDO::FETCH_OBJ);
}

but even that is not really necessary, since a PDOStatement instance can be used in a foreach() loop as is.
Was This Post Helpful? 0
  • +
  • -

#12 Kruithne  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 99
  • View blog
  • Posts: 442
  • Joined: 28-July 09

Re: PDO question - Query isn't working

Posted 15 July 2013 - 11:07 AM

The prepareObjects function was actually rather irrelevant, I wasn't aware I'd left that in there when I uploaded that Gist, but yes!
Was This Post Helpful? 0
  • +
  • -

#13 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3718
  • View blog
  • Posts: 5,989
  • Joined: 08-June 10

Re: PDO question - Query isn't working

Posted 15 July 2013 - 03:06 PM

View PostKruithne, on 15 July 2013 - 05:29 PM, said:

Don't use MD5 for password hashing (as you said, you're aware, just making a point) and look into using the PHP crypt() function which is documented here ( http://php.net/manua...ction.crypt.php ).

PHP 5.5 also has these lovely new password hashing functions that make creating secure password hashes a trivial thing. There are also 3rd party implementations of that out there that can be used on earlier versions, if PHP 5.5 is not available to you yet. Like this one. - These are far easier to use than the crypt() function itself.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1