inserting by pdo object

[narjis]

I am passing an array in the following function and want my data to be added in the table .Here is the statement

public function insert_data($user_info)
{
try{
$dbh = new PDO("mysql:host=localhost;dbname=phpfaqproject",'root','');
//echo "connected";
}
catch(PDOException $e){
echo $e-> getMessage();
} 
$sql ="INSERT INTO account (username, userpassword, email, role, location, interest, homepage)
       VALUES ('" .$user_info['username']."','".$user_info['password'] 
      ."','".$user_info['email']."', 'user','".$user_info['location']."','".$user_info['interests']
      ."','".$user_info['homepage']."')";
          echo $query."<br />";
          $stmt = $dbh->prepare($query);
        
          $stmt->execute();
}

I don't know why it is not updating the tble although echo $query gives correct results.

[guyfromfl]

In PDO You have to bind the data first

 

public function insert_data($user_info)
{
try{
$dbh = new PDO("mysql:host=localhost;dbname=phpfaqproject",'root','');
//echo "connected";
}
catch(PDOException $e){
echo $e-> getMessage();
} 
$sql ="INSERT INTO account (username, userpassword, email, role, location, interest, homepage)
       VALUES (':username, :userpass, :useremail, 'user', :userlocation, :userintersets, :userhomepage)";

$stmt = $dbh->prepare($sql);

if($stmt->bindParam(':username', $user_info['username'], PDO::PARAM_STR) &&
  $stmt->bindParam(':userpass', $user_info['password'], PDO::PARAM_STR) &&
  // The rest of the data in the array
){
  $stmt->execute();
  $insertId = $dbh->lastInsertId();
  $stmt = null;
} else {
   echo "<p>Problem Binding Data</p>";
}

 

You could also insert by numeration then foreach through, but you would have to make sure all the data is in the correct order throughout your application.  Thats why I  usually use the reference method...

 

Also, why don't you set the role field in the database to default as user?

[narjis]

I am passing astriung value 'user' in one of the as the following code.

($stmt->bindParam(':username', $user_info['username'], PDO::PARAM_STR) &&
  $stmt->bindParam(':userpass', $user_info['password'], PDO::PARAM_STR) &&
  $stmt->bindParam(':email', $user_info['email'], PDO::PARAM_STR) &&
  $stmt->bindParam(':role', 'user', PDO::PARAM_STR) &&
  $stmt->bindParam(':location', $user_info['location'], PDO::PARAM_STR) &&
  $stmt->bindParam(':interests', $user_info['interests'], PDO::PARAM_STR) &&
  $stmt->bindParam(':homepage', $user_info['homepage'], PDO::PARAM_STR))

it is giving this error:

Cannot pass parameter 2 by reference in C:\xampp\htdocs\EduProject\model.php on line 110

[guyfromfl]

hmm...

 

I copied that directly from my code but edited your db and vars into it...

 

What line is 110?

[guyfromfl]

I put an ' in the SQL on accident...

 

$sql ="INSERT INTO account (username, userpassword, email, role, location, interest, homepage)
       VALUES (:username, :userpass, :useremail, 'user', :userlocation, :userintersets, :userhomepage)";