Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > php > answers

php mysql and pdo

Newbie
  
Join Date: Oct 2009
Posts: 31
#1: 4 Weeks Ago
Hi all,

Im busy trying to move from archaic php to lovely OO php, but i have hit a hurdle.

The following two scripts connect, request data to be inserted, and is meant to store it, but it doesnt and i get no errors.

for my own sanity can you advise?

Thank you


connection script

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.     $dbtype     = "mysql"; 
  3.     $dbhost     = "localhost"; 
  4.     $dbname     = "case"; 
  5.     $dbuser     = "root"; 
  6.     $dbpass     = "root"; 
  7.  
  8. try {
  9.     $db = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
  10.     /*** echo a message saying we have connected ***/
  11.     echo 'Connected to database';
  12.     echo '<br/>';
  13.     }
  14. catch(PDOException $e)
  15.     {
  16.     echo $e->getMessage();
  17.     }
  18. ?>
form script

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. include("includes/connectpdomysql.php");
  4.  
  5. if (isset($_POST['user_username']) && isset($_POST['user_password']) && isset($_POST['user_email'])) 
  6. {
  7.  
  8. //Prevent SQL injections
  9. $user_id = mysql_real_escape_string($_POST['user_id']);
  10. $user_username = mysql_real_escape_string($_POST['user_username']);
  11. $user_password = md5($_POST['user_password']);
  12. $user_fname = mysql_real_escape_string($_POST['user_fname']);
  13. $user_surname = mysql_real_escape_string($_POST['user_surname']);
  14. $user_email = mysql_real_escape_string($_POST['user_email']);
  15. $user_number = mysql_real_escape_string($_POST['user_number']);
  16. $user_add1 = mysql_real_escape_string($_POST['user_add1']);
  17. $user_add2 = mysql_real_escape_string($_POST['user_add2']);
  18. $user_county = mysql_real_escape_string($_POST['user_county']);
  19. $user_postcode = mysql_real_escape_string($_POST['user_postcode']);
  20. $user_type = mysql_real_escape_string($_POST['user_type']);
  21.  
  22.  
  23.   $sql = "INSERT INTO users (user_id, user_username, user_password, user_fname, user_surname, user_email, user_number, user_add1, user_add2, user_county, user_postcode, user_type) VALUES (user_id, :user_username ,:user_password, :user_fname, :user_surname, :user_email, :user_number, :user_add1, :user_add2, :user_county, :user_postcode, :user_type)"; 
  24.     $q = $db->prepare($sql); 
  25.     $q->execute(array(':user_id'=>$user_id, 
  26.                       ':user_username'=>$user_username,
  27.                       ':user_title'=>$user_password,
  28.                       'user_fname'=>$user_fname,
  29.                       'user_surname'=>$user_surname,
  30.                       ':user_email'=>$user_email,
  31.                       ':user_number'=>$user_number,
  32.                       ':user_add1'=>$user_add1,
  33.                       ':user_add2'=>$user_add2,
  34.                       ':user_county'=>$user_county,
  35.                       ':user_postcode'=>$user_postcode,
  36.                       ':user_type'=>$user_type));
  37.  
  38.                         echo "record created";
  39.  
  40. if(!$q) 
  41.     { 
  42.       die("Execute query error, because: ". $conn->errorInfo()); 
  43.     } 
  44.  
  45.     $q->setFetchMode(PDO::FETCH_BOTH); 
  46.  
  47.     // fetch 
  48.     while($r = $q->fetch()){ 
  49.       print_r($r); 
  50.     }
  51.  
  52. }
  53.  
  54. ?>
  55.  
  56.  
  57. <html>
  58.  
  59. <form action="newuser.php" method="post">
  60. Username: <input name="user_username" type="text" /><br/>
  61. Password: <input type="user_password" name="password" /><br/>
  62. First name: <input name="user_fname" type="text" /><br/>
  63. Surname: <input name="user_surname" type="text" /><br/>
  64. Email: <input name="user_email" type="text" /><br/>
  65. Contact number: <input name="user_number" type="text" /><br/>
  66. Address line 1: <input name="user_add1" type="text" /><br/>
  67. Address line 2: <input name="user_add2" type="text" /><br/>
  68. County: <input name="user_county" type="text" /><br/>
  69. Post Code: <input name="user_postcode" type="text" /><br/>
  70. User Type: <input name="user_type" type="text" /><br/>
  71.  
  72. <input type="submit" value="Submit" /><br/>
  73.  
  74. </form>
  75.  
  76.  </html>
  77.  
Atli's Avatar
Moderator
  
Join Date: Nov 2006
Location: Iceland
Posts: 3,736
#2: 4 Weeks Ago

re: php mysql and pdo

Hey.

Do you have error messages turned on?

On lines #45 and #48 in your form script you use the $q variable as an object, but the PDOStatement::execute method only returns TRUE or FALSE.

Also, on line #38 you prematurely print a success message, before you actually verify that the record has been created successfully.

I must admit, I am not very experienced with PDO, so there may be something I am overlooking.

O, and P.S.
Expand|Select|Wrap|Line Numbers
  1. if (isset($var1) && isset($var2) && isset($var3))
  2. // Can be written:
  3. if (isset($var1, $var2, $var3))
Just to save you a couple of key-strokes in the future ;-)
Newbie
  
Join Date: Oct 2009
Posts: 31
#3: 4 Weeks Ago

re: php mysql and pdo

Hi,

Thanks for the response.

Ive transferred it to my live hosting and the same happens.

I enter something into all of the fields but when i click submit all of the fields empty.
Member
  
Join Date: Jan 2009
Location: USA
Posts: 118
#4: 4 Weeks Ago

re: php mysql and pdo

so what your trying ot do is press submit and keep the actual values that you just submitted?

you will need to do a results query to bring the data back to the screen. currently you have designed for entry not editing/processing/view.

hope this helps!
Atli's Avatar
Moderator
  
Join Date: Nov 2006
Location: Iceland
Posts: 3,736
#5: 4 Weeks Ago

re: php mysql and pdo

Ahh ok. I though you were talking about the data not being inserted into the database.

Yea, it's like wizardry says; that form is only designed to insert data and reset.
If you want it to restore the data to the fields after it is submitted, you will have to actually add the values to the fields.

Like:
Expand|Select|Wrap|Line Numbers
  1. <insert type="text" name="user_name" value="<?php echo htmlentities($_POST['user_name']); ?>">
Newbie
  
Join Date: Oct 2009
Posts: 31
#6: 4 Weeks Ago

re: php mysql and pdo

Hi all, thanks for the responses.

I have done a little work.

Wizardry, your absolutly right about only wanting to insert data.

What i have done is to put the input form in a seperate file, so on clicking submit, it then calls the php script.

my understanding is

1. the form posts values to the "form action" file.
2. Isset takes those values and turns them into variables
3. The sql injection function is used
4. The password is MD5'd
5. The sql script inserts the values to the database.

Ive tried putting echos in but the only thing in having echoed is the "connected to database" message

connection script
Expand|Select|Wrap|Line Numbers
  1.   <?php
  2.     $dbtype     = "mysql"; 
  3.     $dbhost     = "localhost"; 
  4.     $dbuser     = "root"; 
  5.     $dbpass     = "root"; 
  6.  
  7. try {
  8.     $db = new PDO("mysql:host=$dbhost;dbname=case",$dbuser,$dbpass);
  9.     /*** echo a message saying we have connected ***/
  10.     echo 'Connected to database';
  11.     echo '<br/>';
  12.     }
  13. catch(PDOException $e)
  14.     {
  15.     echo $e->getMessage();
  16.     }
  17. ?>  
add file html form
Expand|Select|Wrap|Line Numbers
  1.  <html>
  2.  
  3. <form action="newuser.php" method="post">
  4. Username: <input name="user_username" type="text" /><br/>
  5. Password: <input type="user_password" name="password" /><br/>
  6. First name: <input name="user_fname" type="text" /><br/>
  7. Surname: <input name="user_surname" type="text" /><br/>
  8. Email: <input name="user_email" type="text" /><br/>
  9. Contact number: <input name="user_number" type="text" /><br/>
  10. Address line 1: <input name="user_add1" type="text" /><br/>
  11. Address line 2: <input name="user_add2" type="text" /><br/>
  12. County: <input name="user_county" type="text" /><br/>
  13. Post Code: <input name="user_postcode" type="text" /><br/>
  14. User Type: <input name="user_type" type="text" /><br/>
  15.  
  16. <input type="submit" value="Submit" /><br/>
  17.  
  18. </form>
  19.  
  20.  </html>  
Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. require("includes/connectpdomysql.php");
  4.  
  5. if (isset($_POST['user_username']) && isset($_POST['user_password']) && isset($_POST['user_fname'])&& isset($_POST['user_surname'])&& isset($_POST['user_email'])&& isset($_POST['user_number'])&& isset($_POST['user_add1'])&& isset($_POST['user_add2'])&& isset($_POST['user_county'])&& isset($_POST['user_postcode'])&& isset($_POST['user_type']))
  6.  
  7.  
  8. {
  9.  
  10. //Prevent SQL injections
  11.  
  12. $user_username = mysql_real_escape_string($_POST['user_username']);
  13. $user_fname = mysql_real_escape_string($_POST['user_fname']);
  14. $user_surname = mysql_real_escape_string($_POST['user_surname']);
  15. $user_email = mysql_real_escape_string($_POST['user_email']);
  16. $user_number = mysql_real_escape_string($_POST['user_number']);
  17. $user_add1 = mysql_real_escape_string($_POST['user_add1']);
  18. $user_add2 = mysql_real_escape_string($_POST['user_add2']);
  19. $user_county = mysql_real_escape_string($_POST['user_county']);
  20. $user_postcode = mysql_real_escape_string($_POST['user_postcode']);
  21. $user_type = mysql_real_escape_string($_POST['user_type']);
  22.  
  23.  
  24. //Get MD5 hash of password
  25.  
  26. $user_password = md5($_POST['user_password']);
  27.  
  28.  
  29.     $sql = "INSERT INTO users (user_username, user_password, user_fname, user_surname,  user_email, user_number, user_add1, user_add2, user_county, user_postcode, user_type) VALUES (:user_username,:user_password,:user_fname, :user_surname,:user_email, :user_number, :user_add1, :user_add2, :user_county, :user_postcode, :user_type)"; 
  30.     echo $sql;
  31.     $q = $db->prepare($sql); 
  32.     $q->execute(array(':user_username'=>$user_username, 
  33.                       ':user_password'=>$user_password,
  34.                       ':user_fname'=>$user_fname,
  35.                       ':user_surname'=>$user_surname,
  36.                       ':user_email'=>$user_email,
  37.                       ':user_add1'=>$user_add1,
  38.                       ':user_add2'=>$user_add2,
  39.                       ':user_county'=>$user_county,
  40.                       ':user_postcode'=>$user_postcode,
  41.                       ':user_type'=>$user_type)); 
  42. echo $q;
  43. }
  44. ?>
  45.  
Newbie
  
Join Date: Oct 2009
Posts: 31
#7: 4 Weeks Ago

re: php mysql and pdo

atli, im fine with calling and displaying. Im just in this instance wanting to use PDO to insert into a database
Member
  
Join Date: Jan 2009
Location: USA
Posts: 118
#8: 4 Weeks Ago

re: php mysql and pdo

no prob.

i see and understand what your trying to do. what i dont see is a variable that is defined for:

echo $e-> {getMessage();} that method/function your calling

i see that $e is defined in the function/method. i dont know much about php still learning but i do know c/c++.

define the object your calling.

hope that helps!
Newbie
  
Join Date: Oct 2009
Posts: 31
#9: 4 Weeks Ago

re: php mysql and pdo

if you consider
Expand|Select|Wrap|Line Numbers
  1. $q = $db->prepare($sql);
$q is defined by it being equalled to something
$db is defined within the database class
$sql is also defined by its value

Also all of the entries are turned into variables through isset and the injection thing.

ill get there, but im stumped
Member
  
Join Date: Jan 2009
Location: USA
Posts: 118
#10: 4 Weeks Ago

re: php mysql and pdo

sorry ignore my last post i thought your were talking about not being able to receive your test message but you can get your test message.

i will repost once i look through the code some more
Newbie
  
Join Date: Oct 2009
Posts: 31
#11: 4 Weeks Ago

re: php mysql and pdo

thank you very much for your help
Member
  
Join Date: Jan 2009
Location: USA
Posts: 118
#12: 4 Weeks Ago

re: php mysql and pdo

no problem

try changing this:

Expand|Select|Wrap|Line Numbers
  1. $sql = "INSERT INTO users (user_username, user_password, user_fname, user_surname,  user_email, user_number, user_add1, user_add2, user_county, user_postcode, user_type) VALUES (:user_username,:user_password,:user_fname, :user_surname,:user_email, :user_number, :user_add1, :user_add2, :user_county, :user_postcode, :user_type)"; 
  2.  
to this:
Expand|Select|Wrap|Line Numbers
  1.  $sql = ("INSERT INTO users (user_username, user_password, user_fname, user_surname,  user_email, user_number, user_add1, user_add2, user_county, user_postcode, user_type) VALUES (:user_username,:user_password,:user_fname, :user_surname,:user_email, :user_number, :user_add1, :user_add2, :user_county, :user_postcode, :user_type)"); 
  2.  
Newbie
  
Join Date: Oct 2009
Posts: 31
#13: 4 Weeks Ago

re: php mysql and pdo

well, after putting an echo statement on every line and adding your code change in ive fixed it.

here it is for you to look at. thanks again

Expand|Select|Wrap|Line Numbers
  1.  
  2. <?php
  3.  
  4. require("includes/connectpdomysql.php");
  5. echo "hello";
  6. if (isset($_POST['user_username']) && isset($_POST['user_password']) && isset($_POST['user_fname'])&& isset($_POST['user_surname'])&& isset($_POST['user_email'])&& isset($_POST['user_number'])&& isset($_POST['user_add1'])&& isset($_POST['user_add2'])&& isset($_POST['user_county'])&& isset($_POST['user_postcode'])&& isset($_POST['user_type']))
  7.  
  8.  echo "hello";
  9. {
  10.  
  11. //Prevent SQL injections
  12. $user_id = '';
  13. $user_username = mysql_real_escape_string($_POST['user_username']);
  14. $user_password = mysql_real_escape_string($_POST['password']);
  15. $user_fname = mysql_real_escape_string($_POST['user_fname']);
  16. $user_surname = mysql_real_escape_string($_POST['user_surname']);
  17. $user_email = mysql_real_escape_string($_POST['user_email']);
  18. $user_number = mysql_real_escape_string($_POST['user_number']);
  19. $user_add1 = mysql_real_escape_string($_POST['user_add1']);
  20. $user_add2 = mysql_real_escape_string($_POST['user_add2']);
  21. $user_county = mysql_real_escape_string($_POST['user_county']);
  22. $user_postcode = mysql_real_escape_string($_POST['user_postcode']);
  23. $user_type = mysql_real_escape_string($_POST['user_type']);
  24.  
  25.  
  26. //Get MD5 hash of password
  27.  
  28. //$user_password = md5($_POST['user_password']);
  29.  
  30.  
  31.    $sql = ("INSERT INTO users (user_id, user_username, user_password, user_fname, user_surname, user_email, user_number, user_add1, user_add2, user_county, user_postcode, user_type) VALUES (:user_id, :user_username,:user_password,:user_fname, :user_surname,:user_email, :user_number, :user_add1, :user_add2, :user_county, :user_postcode, :user_type)"); 
  32.  
  33.     $q = $db->prepare($sql); 
  34.     $q->execute(array(':user_id'=>$user_username,
  35.                       ':user_username'=>$user_username, 
  36.                       ':user_password'=>$user_password,
  37.                       ':user_fname'=>$user_fname,
  38.                       ':user_surname'=>$user_surname,
  39.                       ':user_email'=>$user_email,
  40.                       ':user_number'=>$user_number,
  41.                       ':user_add1'=>$user_add1,
  42.                       ':user_add2'=>$user_add2,
  43.                       ':user_county'=>$user_county,
  44.                       ':user_postcode'=>$user_postcode,
  45.                       ':user_type'=>$user_type)); 
  46. }
  47. ?>
  48.  
Member
  
Join Date: Jan 2009
Location: USA
Posts: 118
#14: 4 Weeks Ago

re: php mysql and pdo

no problem glad i could help
Dormilich's Avatar
Moderator
  
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,629
#15: 4 Weeks Ago

re: php mysql and pdo

just a notice: if you use prepared statements, you don’t need mysql_real_escape_string(). It’s the prepared statement itself, that makes up for the security (besides that even mysql_real_escape_string() can be hacked).
Newbie
  
Join Date: Oct 2009
Posts: 31
#16: 4 Weeks Ago

re: php mysql and pdo

so i could just get rid and have the variable = $_POST?
Dormilich's Avatar
Moderator
  
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,629
#17: 4 Weeks Ago

re: php mysql and pdo

if you’re using the prepared statements, yes. (you should disable magic_quotes_gpc, tho)

mind, that this will work against SQL Injection, not HTML/Script Injection (but that’s also true for mysql_real_escape_string())
Reply

« previous question | next question »

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,223 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved