First Time using PDO

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 2760 Views - Last Post: 08 July 2011 - 08:41 AM Rate Topic: -----

#1 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

First Time using PDO

Posted 07 July 2011 - 03:21 PM

Hey everyone of the PHP forum!

I'm working on a project and am having some troubles with PDO.

Here is my code:
		try {

			$dbc = new PDO("mysql:host=$host;dbname=$dbname",$username,$password);
			$dbc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			//Database is now connected. Let's prepare the statement next.
			$stmt = $dbc->prepare("INSERT INTO users (username, password, email, gender) VALUES (?, ?, ?, ?)");
			//Next, bind parameters.
			$stmt->bindParam(1, $input_username);
			$stmt->bindParam(2, $input_password);
			$stmt->bindParam(3, $input_email);
			$stmt->bindParam(4, $input_gender);
			$stmt->execute();
			//We just added information into the members table. Done!

			//We need to get the new user's ID, and save it in a variable so we can JOIN the tables later.
			$userID;
			$stmt2 = $dbc->prepare("SELECT id FROM users WHERE username='$input_username'");
			$stmt2->execute();
			$stmt2->setFetchMode(PDO::FETCH_ASSOC);
			while($row = $stmt2->fetch()) {
				$userID = $row['id'];
			}
			//We now how the ID of the user. Done!

			//Now, let's insert information into the birthdates table.
			$stmt3 = $dbc->prepare("INSERT INTO birthdates (id, month, day, year) VALUES (?, ?, ?, ?)");
			$stmt3->bindParam(1, $userID);
			$stmt3->bindParam(2, $input_dateofbirth_month);
			$stmt3->bindParam(3, $input_dateofbirth_day);
			$stmt3->bindParam(4, $input_dateofbirth_year);
			$stmt3->execute();
			//Done adding the date of birth to the birthdates table!
		
			//Let's add the postal code and location to the location table.
			$stmt4 = $dbc->prepare("INSERT INTO location (id, postalcode, location) VALUES (?, ?, ?)");
			$stmt4->bindParam(1, $userID);
			$stmt4->bindParam(2, $input_postalcode);
			$stmt4->bindParam(3, $input_location);
			$stmt4->execute();
			//BOOM! All of the information is now done and in a database.

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


My code doesn't update all of the tables everytime. Sometimes it doesn't update birthdates, and sometimes it doesn't update users, etc.

Is there something I'm missing? Surely it must be a setting or something. :P

Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: First Time using PDO

#2 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2984
  • Posts: 10,315
  • Joined: 08-August 08

Re: First Time using PDO

Posted 07 July 2011 - 04:14 PM

I don't see where you update at all. I do see INSERT queries, but no UPDATE.

What is this line doing???
	$stmt2 = $dbc->prepare("SELECT id FROM users WHERE username='$input_username'");


$input_username should not be there.
Was This Post Helpful? 0
  • +
  • -

#3 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: First Time using PDO

Posted 07 July 2011 - 04:20 PM

I don't try to use the UPDATE SQL keyword anytime within my script.

I try to use INSERT in order to insert brand new registration information to the database.

The line you pointed out will select the id of the new user. I need this because I split up the registration into three tables: users, birthdates, and location. All of them have to be able to connect via a JOIN statement later when necessary. So, I retrieved the user's id element (it's unique, and auto-increments), so I can copy that into to the other two tables, so the id's match up with a particular user.

$input_username was the username that the user chose as their username. If it doesn't go there, what does? Because that's the only thing that will specify them in the WHERE clause.
Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2984
  • Posts: 10,315
  • Joined: 08-August 08

Re: First Time using PDO

Posted 07 July 2011 - 04:41 PM

Have you read this?

Try it this way:
$stmt2 = $dbc->prepare("SELECT id FROM users WHERE username=?");
$stmt2->bindParam(1, $input_username);
$stmt2->execute();


I'm not sure if that will fix your problem, but it will fix a problem.
Was This Post Helpful? 1
  • +
  • -

#5 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: First Time using PDO

Posted 07 July 2011 - 06:25 PM

Yes, I did read that.

And okay, I made the changes. However, it's still not updating all of the columns. It updates two of them. It changes which one doesn't get updated randomly, or at least it seems that way.

[edit]
Updated Code:
			$dbc = new PDO("mysql:host=$host;dbname=$dbname",$username,$password);
			$dbc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			//Database is now connected. Let's prepare the statement next.
			$stmt = $dbc->prepare("INSERT INTO users (username, password, email, gender) VALUES (?, ?, ?, ?)");
			//Next, bind parameters.
			$stmt->bindParam(1, $input_username);
			$stmt->bindParam(2, $input_password);
			$stmt->bindParam(3, $input_email);
			$stmt->bindParam(4, $input_gender);
			$stmt->execute();
			//We just added information into the members table. Done!

			//We need to get the new user's ID, and save it in a variable so we can JOIN the tables later.
			$userID;
			$stmt2 = $dbc->prepare("SELECT id FROM users WHERE username=?");
			$stmt2->bindParam(1, $input_username);
			$stmt2->execute();
			$stmt2->setFetchMode(PDO::FETCH_ASSOC);
			while($row = $stmt2->fetch()) {
				$userID = $row['id'];
				echo $userID;
			}
			//We now how the ID of the user. Done!

			//Now, let's insert information into the birthdates table.
			$stmt3 = $dbc->prepare("INSERT INTO birthdates (id, month, day, year) VALUES (?, ?, ?, ?)");
			$stmt3->bindParam(1, $userID);
			$stmt3->bindParam(2, $input_dateofbirth_month);
			$stmt3->bindParam(3, $input_dateofbirth_day);
			$stmt3->bindParam(4, $input_dateofbirth_year);
			$stmt3->execute();
			//Done adding the date of birth to the birthdates table!
		
			//Let's add the postal code and location to the location table.
			$stmt4 = $dbc->prepare("INSERT INTO location (id, postalcode, location) VALUES (?, ?, ?)");
			$stmt4->bindParam(1, $userID);
			$stmt4->bindParam(2, $input_postalcode);
			$stmt4->bindParam(3, $input_location);
			$stmt4->execute();


[/edit]

This post has been edited by maniacalsounds: 07 July 2011 - 07:31 PM

Was This Post Helpful? 0
  • +
  • -

#6 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2984
  • Posts: 10,315
  • Joined: 08-August 08

Re: First Time using PDO

Posted 07 July 2011 - 07:41 PM

What you've shown looks ok. Where do you set the variables you're using in your queries?
Was This Post Helpful? 0
  • +
  • -

#7 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: First Time using PDO

Posted 07 July 2011 - 07:44 PM

I got them at the beginning of the script via POST requests.

$input_email = $_POST['email'];
$input_username = $_POST['username'];
$input_password = $_POST['password'];
$input_location = $_POST['location'];
$input_postalcode = $_POST['postalcode'];
$input_dateofbirth_month = $_POST['dateofbirth_month'];
$input_dateofbirth_day = $_POST['dateofbirth_day'];
$input_dateofbirth_year = $_POST['dateofbirth_year'];
$input_gender = $_POST['gender'];


I echoed all of those variables on screen, and they're all the correct values.

This post has been edited by maniacalsounds: 07 July 2011 - 07:45 PM

Was This Post Helpful? 0
  • +
  • -

#8 eZACKe  Icon User is offline

  • Garbage Collector

Reputation: 120
  • View blog
  • Posts: 1,278
  • Joined: 01-June 09

Re: First Time using PDO

Posted 07 July 2011 - 08:48 PM

Your queries look fine from what I can tell, but that's not how I usually do it.

I usually do it like this for example (this is just a random code snippet I pulled from a project I'm working on, but you should see how yours could relate):
$query = "INSERT INTO members VALUES(:email, :token, :fName, :lName, CURDATE(), 0)";
$ps = $pdo->prepare($query);
$params = array(
'email' => $emailA,
'token' => $token,
'fName' => $fName,
'lName' => $lName
);
$ps->execute($params);



Try it this way and see if it makes a difference? That way we can probably narrow down to see if the problem is with the variables.

Also in your previous post, that's 9 wasted lines of code. There's not point in assigning $_POST variables to something else because they are already VARIABLES.

Hopefully this helps.

This post has been edited by eZACKe: 07 July 2011 - 08:51 PM

Was This Post Helpful? 0
  • +
  • -

#9 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: First Time using PDO

Posted 07 July 2011 - 09:04 PM

I understand what you're saying. I have also seen that approach in Dormilich's tutorial about Introduction to PDO. However, I don't want to "fix something that isn't broke."

For the most part on D.I.C, I see people approaching this method the way I did it. I figure that way it has more wide-spread support.

As for the $_POST[] variables, yes. However, it makes it easier to read, organize the project, and it gives it a more thoughtful name that will allow other developers to quickly pick up on the variables in this project. Thanks for the advice, and you are correct, but no one can convince me that those are 100% "wasted" lines of code.
Was This Post Helpful? 0
  • +
  • -

#10 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 947
  • View blog
  • Posts: 2,355
  • Joined: 15-February 11

Re: First Time using PDO

Posted 07 July 2011 - 09:21 PM

What's the data type of `location`.`id` and `birthdates`.`id`? Since you didn't specify the data type when binding the parameters they're considered to be type string.
Was This Post Helpful? 0
  • +
  • -

#11 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: First Time using PDO

Posted 07 July 2011 - 09:52 PM

I hadn't thought of that codeprada. I made the changes, and at first, I thought it solved my problem. However, it doesn't. At first, I had four successful logins. Then I deleted those results, and tried again. I then had three times in a row where it didn't update one of the three tables.
Try one: didn't update "birthdates" table
Try two: didn't update "location" table
Try three: didn't update "birthdates" table

Any other suggestions? Updated code:
			$dbc = new PDO("mysql:host=$host;dbname=$dbname",$username,$password);
			$dbc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			//Database is now connected. Let's prepare the statement next.
			$stmt = $dbc->prepare("INSERT INTO users (username, password, email, gender) VALUES (?, ?, ?, ?)");
			//Next, bind parameters.
			$stmt->bindParam(1, $input_username, PDO::PARAM_STR);
			$stmt->bindParam(2, $input_password, PDO::PARAM_STR);
			$stmt->bindParam(3, $input_email, PDO::PARAM_STR);
			$stmt->bindParam(4, $input_gender, PDO::PARAM_STR);
			$stmt->execute();
			//We just added information into the members table. Done!

			//We need to get the new user's ID, and save it in a variable so we can JOIN the tables later.
			$userID;
			$stmt2 = $dbc->prepare("SELECT id FROM users WHERE username=?");
			$stmt2->bindParam(1, $input_username, PDO::PARAM_STR);
			$stmt2->execute();
			$stmt2->setFetchMode(PDO::FETCH_ASSOC);
			while($row = $stmt2->fetch()) {
				$userID = $row['id'];
			}
			//We now how the ID of the user. Done!

			//Now, let's insert information into the birthdates table.
			$stmt3 = $dbc->prepare("INSERT INTO birthdates (id, month, day, year) VALUES (?, ?, ?, ?)");
			$stmt3->bindParam(1, $userID, PDO::PARAM_INT);
			$stmt3->bindParam(2, $input_dateofbirth_month, PDO::PARAM_STR);
			$stmt3->bindParam(3, $input_dateofbirth_day, PDO::PARAM_INT);
			$stmt3->bindParam(4, $input_dateofbirth_year, PDO::PARAM_INT);
			$stmt3->execute();
			//Done adding the date of birth to the birthdates table!
		
			//Let's add the postal code and location to the location table.
			$stmt4 = $dbc->prepare("INSERT INTO location (id, postalcode, location) VALUES (?, ?, ?)");
			$stmt4->bindParam(1, $userID, PDO::PARAM_INT);
			$stmt4->bindParam(2, $input_postalcode, PDO::PARAM_STR);
			$stmt4->bindParam(3, $input_location, PDO::PARAM_STR);
			$stmt4->execute();

This post has been edited by maniacalsounds: 07 July 2011 - 09:52 PM

Was This Post Helpful? 0
  • +
  • -

#12 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2984
  • Posts: 10,315
  • Joined: 08-August 08

Re: First Time using PDO

Posted 08 July 2011 - 03:57 AM

Could `id` be an auto-incrementing key in the table? That would give you trouble.

Other than that, I suggest you use PDO's exception class to find out what error is occurring.
Was This Post Helpful? 0
  • +
  • -

#13 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: First Time using PDO

Posted 08 July 2011 - 04:37 AM

Hey.

I also see nothing wrong with the code. It looks fine. - Personally I would set up XDebug and use Netbeans to set up breakpoints and step through the code. Would allow you to see exactly what is happening and what data the PDO statements are receiving.


On another note, assuming your users.id column is AUTO_INCREMENT, instead of doing:
//We need to get the new user's ID, and save it in a variable so we can JOIN the tables later.
$userID;
$stmt2 = $dbc->prepare("SELECT id FROM users WHERE username=?");
$stmt2->bindParam(1, $input_username, PDO::PARAM_STR);
$stmt2->execute();
$stmt2->setFetchMode(PDO::FETCH_ASSOC);
while($row = $stmt2->fetch()) {
	$userID = $row['id'];
}
//We now how the ID of the user. Done!


You could use the PDO::lastInsertID method.
//We need to get the new user's ID, and save it in a variable so we can JOIN the tables later.
$userID = $dbc->lastInsertId();
//We now how the ID of the user. Done!


Was This Post Helpful? 1
  • +
  • -

#14 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: First Time using PDO

Posted 08 July 2011 - 07:32 AM

Thanks Atloi! I switched my key method, and it hellped dramatically reduce the amount of lines necessary to get the ID.

I switched my error mode and got the following error:
Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY' in C:\x\xampp\htdocs\waxblog\register.php on line 82

Here is my line 82:
	}


So that line has nothing to do with adding the ID to the table, which I think is what the error is? (Sorry, again, first time using PDO and its error system.)
Was This Post Helpful? 0
  • +
  • -

#15 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: First Time using PDO

Posted 08 July 2011 - 07:58 AM

Could you show us more details about your tables?

Looks like you are trying to put the user ID you fetch from the first INSERT into the PK of the other two? That's not really a good idea. 1:1 relationships are kind of pointless, unless it's done for performance optimization (which is not the case here.) - If there will only ever be one row in the birthdates and location tables that corresponds to each user, you may as well just put the columns from those tables into the users table. Otherwise you are just fragmenting what is in essence one table, making querying it that more complex.

But if there will be multiple birthdates and locations attached to each user, then you would want those tables having their own PK, with a separate FK column to store the user ID in.


However, if you are intent on using 1:1 relations for the birthdates and locations, you need to make sure the PK in those tables are not AUTO_INCREMENT (as they will have to match the PK of the users table) and that you always sync the tables when you delete or update users records. - If the tables are out of sync, you will get constraint errors like the one you posted. Or worse; new users will adopt orphan records from previously deleted users.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2