PDO MySql insert not working

[gilgil2]

Hi I have the following code being used to insert some data into a MySql database, all the error checks etc. are done before and those are fine, and the code returns connected successfully but nothing is put into the database, nor does the database check for duplicate usernames, because if you enter a duplicate username it does not come up as an error.

So I'm not entirely sure what the problem is, if someone could tell me what the problem is, or at least how to put one of those error things in so it can tell me the problem I would be very grateful.

$a = md5(uniqid(rand(), true));
$username = $_POST['username'];
$email = $_POST['email'];
$password = $_POST['password'];

try {
$dbname = "unsignedgigs";
$dbhost = "unsignedgigs.fatcowmysql.com";
$dbuser = "unsignedgigs";
$dbpass ="Harlequins13";

        $db=new PDO("mysql:dbname=$dbname;host=$dbhost",$dbuser,$dbpass);
        echo 'Connected successfully<br />'; 
    } catch (PDOException $e) {
        die ('Connection failed: '.$e->getMessage());
    }




$sql = "INSERT INTO users (username, email, password, active) VALUES (:username,:email,:password,:active)";

$q = $db->prepare($sql);

$q->execute(array(':username'=>$username,':email'=>$email,':password'=>$password,':active'=>$a));


                theme_header('Registration Successful');
                echo '<p>Form was filled out correctly</p>';
                theme_footer();
            }

Also I know i need to encrypt the password, do I need to escape the Mysql string using PDO? Do I use the same function?

Thanks
Gilgil2

[garyjohnson]

I have never seen your form of connection to a database but I'm new at this but I have mine set up like this and it workds, so you can give it a try,

$dbname = "unsignedgigs";
$dbhost = "unsignedgigs.fatcowmysql.com";
$dbuser = "unsignedgigs";
$dbpass ="Harlequins13";

    $con = mysql_connect("$dbhost","$dbuser","$dbpass");
    if (!$con){
      die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("$dbname", $con);

    $r = mysql_query("INSERT INTO `users` SET `username` = '$username', `email` = '$email', `password`= '$password' `active`='$a' ");
    if(mysql_affected_rows()){
        echo "1 record added";
    }else{
        echo "problem";
    }
}else{
    echo "Invalid file";
}

[jstfsklh211]

password is mysql reserved word
in order to tell mysql that you are using it as a field name you need to encase it in backticks

`password`

[extjac]

this is how i do it, based on great feedback from the forum:

DB CONNECTION CLASS:

class db {

/*** Declare instance ***/
private static $instance = NULL;

/**
*
* the constructor is set to private so
* so nobody can create a new instance using new
*
*/
private function __construct() {
  /*** maybe set the db name here later ***/
}

/**
*
* Return DB instance or create intitial connection
*
* @return object (PDO)
*
* @access public
*
*/
    public static function getInstance() 
    {
        if (!self::$instance)
            {
            self::$instance = new PDO("mysql:host=ip_Addred_or_hostname;dbname=your_bd_name", 'your_db_username', 'your_db_password');;
            self::$instance-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            }
    return self::$instance;
    }

/**
*
* Like the constructor, we make __clone private
* so nobody can clone the instance
*
*/
private function __clone(){
}

} /*** end of class ***/

PAYMENT CLASS

class payment
{

    function __construct()
    {
        $this->table = 'YOUR_TABLE_NAME';
    }


    /**
    * @param string $this->table Table name
    * @param array $data Array with columns and values
    * @return mixed Last inserted ID if insert succeeded, false otherwise
    */
    public function insert($data) 
    {
        $result  = false;
        $columns = array ();
        $values  = array ();

        foreach ($data as $column => $value) 
        {
            $columns[]  = $column;
            $values[]   = ":$column";
        }
        $columns = implode(',', $columns);
        $values = implode(',', $values);

        $query = " INSERT INTO $this->table ($columns) VALUES ($values)";
        $statement = db::getInstance()->prepare($query);

        foreach ($data as $column => $value) 
        {
            $statement->bindValue(":$column", $value );
        }

        if ($statement->execute() ) 
        {
            $result = db::getInstance()->lastInsertId();
        }
    return $result;
    }




    /**
    * @param string $this->table Table name
    * @param $data Array with columns and values
    * @param $conditions Array Conditions to use
    * @return int Number of affected rows
    */
    public function update($data, $conditions = array ()) 
    {
        $result = 0;
        $query = " UPDATE $this->table SET ";
        $columns = array ();

        foreach ($data as $column => $value) 
        {
        $columns[] = "$column=:$column";
        }

        $columns = implode(', ', $columns);
        $query .= "$columns ";

        //WHERE
        $where = array ();
        foreach ($conditions as $column => $value) 
        {
        $where[] = "$column = :$column ";
        }   
        if (count($where) > 0) 
        {
            $where = implode(' AND ', $where);
            $query .= "WHERE $where ";
        }
        //END WHERE

        $statement =  db::getInstance()->prepare($query);

        //BindValues
        foreach ($data as $column => $value) 
        {
            $statement->bindValue(":".$column, $value );
        }

        foreach ($conditions as $column => $value) 
        {
            $statement->bindValue(":".$column, $value );
        }
        //End binValues

        if ($statement->execute() ) 
        {
            $result = $statement->rowCount();
        }
    return $result;
    }


} /*** end of class ***/

HOW TO USE IT

require_once(dirname(__FILE__).'CLASSES_FILE.php');    

 /* ADD NEW RECORD */
if(isset($_GET['action']) && $_GET['action']=='insert') 
{ 
    $payment = new payment();

    $data = array(
                 'status'           => $_POST['status']  
                ,'customer_id'      => $_POST['customer_id'] 
                ,'product_id'       => $_POST['product_id']
                ,'payment_method'   => $_POST['payment_method']
                ,'payment_amount'   => $_POST['payment_amount']
                ,'payment_currency' => $_POST['payment_currency']

            );
    $payment->insert($data);
}


/* UPDATE RECORD*/
if(isset($_GET['action']) && $_GET['action']=='update') 
{ 
    $payment = new payment();
    $data = array(
                 'customer_id'      => $_POST['customer_id'] 
                ,'product_id'       => $_POST['product_id']
                ,'payment_method'   => $_POST['payment_method']
                ,'payment_amount'   => $_POST['payment_amount']
                ,'payment_currency' => $_POST['payment_currency']
                ,'payment_date'     => $_POST['yyyy'].'-'.$_POST['mm'].'-'.$_POST['dd']
            );
    $where = array( 'pr_id' =>  $_POST['pr_id'] );
    $payment->update($data, $where );
}

I DID A QUICK COPY/PASTE SO I HOPE I DIDNT NOT FORGET ANYTHING....

[extjac]

BY THE WAY....i think you forgot to bindValue() on our code..

[iamthwee]

Yep just look up the php PDO reference manual. There should be thorough documentation.