[RESOLVED] PHP's PDO

This site is best viewed in a modern browser with JavaScript enabled.

UAL225

So as you may know I am creating a multiple database connect and writing to multiple databases using PHP's PDO set. Im rather new to the whole PDO scene so its a learning curve with me. Here is my code, and below are my questions.

<?php
require_once('../wp-includes/class-phpass.php');

//Create var names
$first_name = $_POST['F_Name'];
$last_name = $_POST['L_Name'];
$phone_number_1 = $_POST['Pnumber1'];
$phone_number_2 = $_POST['Pnumber2'];
$address = $_POST['Address'];
$state = $_POST['State'];
$city = $_POST['City'];
$zip_code = $_POST['Zip'];
$email_1 = $_POST['Email1'];
$email_2 = $_POST['Email2'];
$username = $_POST['Uname'];
$password_1 = $_POST['Pass'];
$jed = $_POST['Jednostka'];
$dob = $_POST['DOB'];
$stopien = $_POST['Stopien'];
$funk = $_POST['FUNK'];
$hasher = new PasswordHash(8, True);
$password = $hasher->HashPassword($password_1);
$ENC =MD5($password_1);
$ERRmsg = "";

	//Checks if any feilds were left empty and creates an error message to display
		if(empty($first_name)) $ERRmsg .= 'You did not enter a First Name!  Pleaase go back and try again! <br/>';
		if(empty($last_name)) $ERRmsg .= 'You did not enter a Last Name!  Please go back and try again! <br/>';
		if(empty($phone_number_1)) $ERRmsg .= 'You did not enter a Phone Number!  Please go back and try again! <br/>';
		if(empty($address)) $ERRmsg .= 'You did not enter an Address!  Please go back and try again! <br/>';
		if(empty($state)) $ERRmsg .= 'You did not choose a State!  Please go back and try again! <br/>';
		if(empty($city)) $ERRmsg .= 'You did not enter a City!  Please go back and try again! <br/>';
		if(empty($zip_code)) $ERRmsg .= 'You did not enter a Zip Code.  Please go back and try again!<br/>';
		if(empty($email_1)) $ERRmsg .= 'You did not enter a Email.  Please go back and try again!<br/>';
		if(empty($email_2)) $ERRmsg .= 'You did not re-enter you Email.  Please go back and try again!<br/>';
		if(empty($username)) $ERRmsg .= 'You did not enter a Username.  Please go back and try again!<br/>';
		if(empty($password_1)) $ERRmsg .= 'You did not enter a password!  Please go back and try again!<br/>';
		if(empty($jed)) $ERRmsg .= 'You did not choose a Jednostka!  Please go back and try again!<br/>';
		if(empty($dob)) $ERRmsg .= 'You did not enter a Date of Birth!  Please go back and try again!<br/>';
		if(empty($stopien)) $ERRmsg .= 'You did not choose a Stopien!  Please go back and try again!<br/>';
		if(empty($funk)) $ERRmsg .= 'You did not enter a Funkcjia!  Please go back and try again!<br/>';
		if($email_1 !== $email_2) $ERRmsg .= 'Your emails did not match!  Please go back and try again!<br/>';

			//Checks to see if error message is empty, if empty does rest of code
			if(empty($ERRmsg))
			{
				try {
				$wdp = new PDO('mysql:harcerze_central; host=localhost', 'harcerze_cuser', 'Czuwaj_99999');
					echo "Connected to Warta Database</p>\n";
				$wdp->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
						//prepare the SQL statement
							$wdp_insert = $wdp->prepare
							("INSERT INTO users( 
							ID,
							F_NAME,
							L_Name,
							P_number1,
							P_number2,
							Address,
							City,
							State,
							Zip,
							Email,
							username,
							password,
							Jednostka,
							DOB,
							Stopien,
							Funkcjia,
							High,
							Active,
							user_registered,
							user_email,
							user_login,
							user_pass,
							user_nicename,
							display_name,
							groupID,
							prefs_list_item)
						VALUES (
							:id,
							:fname,
							:lname,
							:pnumber1,
							:pnumber2,
							:address,
							:city,
							:state,
							:zip,
							:email,
							:username,
							:password,
							:jednostka,
							:DOB,
							:Stopien,
							:funkcjia,
							:jednostka,
							:0,
							:timestamp,
							:email,
							:username,
							:password,
							:CONCAT('$First_name', ' ', '$Last_name'),
							:CONCAT('$First_name', ' ', '$Last_name'),
							:auto insert,
							:prefs_list_item
						)"
						);

								//Bind the paramaters
								$wdp_insert->bindParam(':fname', $first_name);
								$wdp_insert->bindParam(':lname', $last_name);
								$wdp_insert->bindParam(':pnumber1', $phone_number_1);
								$wdp_insert->bindParam(':pnumber2', $phone_number_2);
								$wdp_insert->bindParam(':address', $address);
								$wdp_insert->bindParam(':city', $city);
								$wdp_insert->bindParam(':state', $state);
								$wdp_insert->bindParam(':zip', $zip_code);
								$wdp_insert->bindParam(':email', $email_1);
								$wdp_insert->bindParam(':username', $username);
								$wdp_insert->bindParam(':password', $password);
								$wdp_insert->bindParam(':jednostka', $jed);
								$wdp_insert->bindParam(':DOB', $dob);
								$wdp_insert->bindParam(':Stopien', $stopien);
								$wdp_insert->bindParam(':funkcjia', $funk);

						//Execute the prepared statement
							$wdp_insert->execute();
							echo ("<p>Insert complete</p>\n");
				} catch (PDOException $ex) {
				$msg = $ex->errorInfo;
				error_log(var_export($msg, true));
				die("<p>Sorry, there was an unrecoverable database error. Debug data has been logged.</p>");
				};

			}
			else {
				echo ($first_name .' '. $ERRmsg);
				exit;
			}

?>

So now my questions are as follows:

1.On the insert I have multiple values, One of them is an ID that has A_I (Auto Incriment) how would I go about leaving that feild blank? The way I did???

2.I have a feild "Active". That is Automatically 0 untill they confirm their email. Do I simply do :0, or leave that blank?

I have a time stamp that is handeled by the SQL database. So I also leave this blank on the insert?
Im joining two things using a CONCAT, will that work the way i have it set up?
What else do you see that may be incorrect? It seems to me that this DBO way is just easy to inject SQL with. But I think thats just me being used to mysql_real_escape_string.....
And I get an error of "array (0 => 'HY093', 1 => 0,)". Could this be due to the fact of the ID feild?

NogDog

The bindParam() method takes care of any quoting and escaping as needed -- one of the benefits of prepared statements with bound input parameters. However, you don't want to make the CONCAT() function a place-holder; instead you want its variable arguments to be place-holders:
```
"INSERT . . .
:password,
CONCAT(:fname, ' ', :lname),
CONCAT(:fname, ' ', :lname),
...
";
// . . .
$wdp_insert->bindParam(':fname', $First_name);
$wdp_insert->bindParam(':lname', $Last_name);
```
Although I'd probably just concatenate them in PHP:
```
$fullName = $_POST['F_name'].' '.$_POST['L_name'];
```
...then you could just set a place-holder and bind it to that one string variable.

Also, it looks like you just want to set the Active field to 0, so you don't need a place-holder, there -- just enter the zero without the leading ":" at that point in the values list.

UAL225

Thanks! I concated the names in PHP then binded them. Although I still dont understand the whole ID thing. Since the ID is set to auto-incriment via SQL how do I insert this? just omit it? in the old way you would just leave a space and single quotes. Also I get this error when I try to run this code.

[24-Nov-2012 16:25:35] array (
  0 => 'HY093',
  1 => 0,
)

I am gussing that has to do with the whole ID thing.

UAL225

Actually I figured out the HY093 error. That was my mistake, by simply trying to input into feilds that I didnt have parameters for. But now a werid issue comes up saying it cannot connect to the Database since no database is selected. Yet when I tested this code prior, just the connect it connected fine. Any thoughts? Here is the revised code.

<?php
require_once('../wp-includes/class-phpass.php');

//Create var names
$first_name = $_POST['F_Name'];
$last_name = $_POST['L_Name'];
$phone_number_1 = $_POST['Pnumber1'];
$phone_number_2 = $_POST['Pnumber2'];
$address = $_POST['Address'];
$state = $_POST['State'];
$city = $_POST['City'];
$zip_code = $_POST['Zip'];
$email_1 = $_POST['Email1'];
$email_2 = $_POST['Email2'];
$username = $_POST['Uname'];
$password_1 = $_POST['Pass'];
$jed = $_POST['Jednostka'];
$dob = $_POST['DOB'];
$stopien = $_POST['Stopien'];
$funk = $_POST['FUNK'];
$fullName = $_POST['F_name'].' '.$_POST['L_name'];  

$hasher = new PasswordHash(8, True);
$password = $hasher->HashPassword($password_1);
$ENC =MD5($password_1);
$ERRmsg = "";

	//Checks if any feilds were left empty and creates an error message to display
		if(empty($first_name)) $ERRmsg .= 'You did not enter a First Name!  Pleaase go back and try again! <br/>';
		if(empty($last_name)) $ERRmsg .= 'You did not enter a Last Name!  Please go back and try again! <br/>';
		if(empty($phone_number_1)) $ERRmsg .= 'You did not enter a Phone Number!  Please go back and try again! <br/>';
		if(empty($address)) $ERRmsg .= 'You did not enter an Address!  Please go back and try again! <br/>';
		if(empty($state)) $ERRmsg .= 'You did not choose a State!  Please go back and try again! <br/>';
		if(empty($city)) $ERRmsg .= 'You did not enter a City!  Please go back and try again! <br/>';
		if(empty($zip_code)) $ERRmsg .= 'You did not enter a Zip Code.  Please go back and try again!<br/>';
		if(empty($email_1)) $ERRmsg .= 'You did not enter a Email.  Please go back and try again!<br/>';
		if(empty($email_2)) $ERRmsg .= 'You did not re-enter you Email.  Please go back and try again!<br/>';
		if(empty($username)) $ERRmsg .= 'You did not enter a Username.  Please go back and try again!<br/>';
		if(empty($password_1)) $ERRmsg .= 'You did not enter a password!  Please go back and try again!<br/>';
		if(empty($jed)) $ERRmsg .= 'You did not choose a Jednostka!  Please go back and try again!<br/>';
		if(empty($dob)) $ERRmsg .= 'You did not enter a Date of Birth!  Please go back and try again!<br/>';
		if(empty($stopien)) $ERRmsg .= 'You did not choose a Stopien!  Please go back and try again!<br/>';
		if(empty($funk)) $ERRmsg .= 'You did not enter a Funkcjia!  Please go back and try again!<br/>';
		if($email_1 !== $email_2) $ERRmsg .= 'Your emails did not match!  Please go back and try again!<br/>';

			//Checks to see if error message is empty, if empty does rest of code
			if(empty($ERRmsg))
			{
				try {
				$wdp = new PDO('mysql:********; host=localhost', '*********', '*********');
					echo "<p>Connected to Warta Database</p>\n";
				$wdp->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
						//prepare the SQL statement
							$wdp_insert = $wdp->prepare
							("INSERT INTO Users( 
							F_NAME,
							L_Name,
							P_number1,
							P_number2,
							Address,
							City,
							State,
							Zip,
							Email,
							username,
							password,
							Jednostka,
							DOB,
							Stopien,
							Funkcjia,
							High,
							Active,
							user_email,
							user_login,
							user_pass,
							user_nicename,
							display_name,
						)
						VALUES (
							:fname,
							:lname,
							:pnumber1,
							:pnumber2,
							:address,
							:city,
							:state,
							:zip,
							:email,
							:username,
							:password,
							:jednostka,
							:DOB,
							:Stopien,
							:funkcjia,
							:jednostka,
							0,
							:email,
							:username,
							:password,
							:fullname,
							:fullname,
						)"
						);

								//Bind the paramaters
								$wdp_insert->bindParam(':fname', $first_name);
								$wdp_insert->bindParam(':lname', $last_name);
								$wdp_insert->bindParam(':pnumber1', $phone_number_1);
								$wdp_insert->bindParam(':pnumber2', $phone_number_2);
								$wdp_insert->bindParam(':address', $address);
								$wdp_insert->bindParam(':city', $city);
								$wdp_insert->bindParam(':state', $state);
								$wdp_insert->bindParam(':zip', $zip_code);
								$wdp_insert->bindParam(':email', $email_1);
								$wdp_insert->bindParam(':username', $username);
								$wdp_insert->bindParam(':password', $password);
								$wdp_insert->bindParam(':jednostka', $jed);
								$wdp_insert->bindParam(':DOB', $dob);
								$wdp_insert->bindParam(':Stopien', $stopien);
								$wdp_insert->bindParam(':funkcjia', $funk);
								$wdp_insert->bindParam(':fullname', $fullName);

						//Execute the prepared statement
							$wdp_insert->execute();
							echo ("<p>Insert complete</p>\n");
				} catch (PDOException $ex) {
				$msg = $ex->errorInfo;
				error_log(var_export($msg, true));
				die("<p>Sorry, there was an unrecoverable database error. Debug data has been logged.</p>");
				};

			}
			else {
				echo ($first_name .' '. $ERRmsg);
				exit;
			}

?>

UAL225

Figured out the connection issue, had to change the localhost and dbname. Werid. Anyways, now Im trying to insert the data into the database. It progress fine through the first name, last name and when it gets to the first number it throws out this error.

array (
  0 => '42000',
  1 => 1064,
  2 => 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \')

When i look further into this, it omits the last number of the phone number even though the database can accept 12 varchar. (so its 555-555-5555). Why does it throw it out?