My PHP form is POSTing to a php file that connects to my database and supposed to INSERT the record.
As of now I have tested the connection and it is working, my SELECT statement is working. But my INSERT is not. I did an echo $sql->rowCount(); before the execution and it returns "0". Should there be a row per value?
My code:
try {
$db = new PDO('mysql:host=localhost;dbname=x;charset=utf8', 'x', 'x');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$db = new PDO('mysql:host=localhost;dbname=x;charset=utf8', 'x', 'x');
$ipaddress = $_SERVER['REMOTE_ADDR'];
$email = $_POST['email'];
$sql = $db->prepare("INSERT INTO ucm_signup (company, address1, address2, city, province, zip, fname,
lname, email, phone, session, iama, buyfrom, ipaddress)
VALUES (:company, :address1, :address2, :city, :province, :zip, :fname, :lname, :email, :phone, :session, :iama, :buyfrom, :ipaddress)");
$sql->bindParam(":company", $_POST['company'],PDO::PARAM_STR);
$sql->bindParam(":address1", $_POST['address1'],PDO::PARAM_STR);
$sql->bindParam(":address2", $_POST['address2'],PDO::PARAM_STR);
$sql->bindParam(":city", $_POST['city'],PDO::PARAM_STR);
$sql->bindParam(":province", $_POST['province'],PDO::PARAM_STR);
$sql->bindParam(":zip", $_POST['zip'],PDO::PARAM_STR);
$sql->bindParam(":fname", $_POST['fname'],PDO::PARAM_STR);
$sql->bindParam(":lname", $_POST['lname'],PDO::PARAM_STR);
$sql->bindParam(":email", $_POST['email'],PDO::PARAM_STR);
$sql->bindParam(":phone", $_POST['phone'],PDO::PARAM_STR);
$sql->bindParam(":session", $_POST['session'],PDO::PARAM_STR);
$sql->bindParam(":iama", $_POST['iama'],PDO::PARAM_STR);
$sql->bindParam(":buyfrom", $_POST['buyfrom'],PDO::PARAM_STR);
$sql->bindParam(":ipaddress", $_POST['ipaddress'],PDO::PARAM_STR);
$sql->execute();
//$db = null;
//header( "Location: http://www.x.com/company/ucm_signup2" );
Any ideas on why my execute is not correct? Thank you
You have your parameters mixed
......
$ipaddress = $_SERVER['REMOTE_ADDR'];
......
Later
$sql->bindParam(":ipaddress", $_POST['ipaddress'],PDO::PARAM_STR);
There is no $_POST['ipaddress']
Your code has several logical errors. First error is relying on PDO to provide you with rowCount(). It is not reliable with SELECT statements.
The second error you have is checking for duplicate records on PHP's side. That's wrong. Imagine yourself registering with twice with the same email. There's a brief lag between PHP and MySQL and during that time the server can accept two requests, both carrying the same payload (email), and before any insert occurred - you (wrongly) inspected that there indeed isn't such an email in the database.
What you end up are duplicates. Now expand the example to more than 2 requests.
You can't perform data integrity checks in PHP.
Third error you did was perform a SELECT and then count rows. Instead, you should have done
SELECT COUNT(*) as num_records FROM table WHERE condition = :condition
You will always have the result from that query. And there's less data to transfer.
So what should you do? Simply - add the UNIQUE constraint to the email field, try to insert the data and catch the Exception. If the exception message says "duplicate key for entry..." then you know the email is taken and you can send the appropriate message to the client.
You don't need to query with SELECT first. You don't need to check number of rows. You are 100% sure that data integrity is ok. You end up with less code.