data is not inserting into dataase- newbee

[mochine]

Hi

I cant seem to get any data from a basic form to insert into a mysql db. When i run the query by clicking the submit button, it will act like the query has been inserted but when the select runs (the next query on the same index.php script) that query will not display the newly insert information at all it will only show what has been added through php my admin only. The first column in the table is a primary key id set to auto increment too.

here is code from index.php

if(isset($_POST['$firstname'])) {

try{

$sql = "INSERT INTO customer SET
firstname = :firstname,
lastname = :lastname,
address = :address,
city = :city,
state = :state,
zip = :zip,
phone = hone,
email = :email";
$s = $pdo->prepare($sql);
$s->bindValue(':firstname', $_POST['firstname']);
$s->bindValue(':lastname', $_POST['lastname']);
$s->bindValue(':address', $_POST['address']);
$s->bindValue(':city', $_POST['city']);
$s->bindValue(':state', $_POST['state']);
$s->bindValue(':zip', $_POST['zip']);
$s->bindValue('hone', $_POST['phone']);
$s->bindValue(':email', $_POST['email']);
$s->execute(array(':firstname'=>$_POST['firstname'], ':lastname'=>$_POST['lastname'], ':address'=>$_POST['address']));

} catch (PDOException $e){
$output = 'Error adding submitted customer info: ' . $e->getMessage();
include 'error.html';
}
header ('Location: .');
exit();
}

thanx in advance for your help i hope i explained this fairly well if not i will be glad to give more information if needed.

[Serenarules]

BindValue already takes the value you specify and binds it to the param. Doing it this way, you do not need to pass an array to Execute(). You may also want to test the individual value for null or cast them before use. Using $_POST values directly isn't a very good idea.

[mochine]

should i give $_POST a different variable name? like $_POST['firstname'] = $firstname and so on

and should i do the $s->execute()? not $s->execute the array like above?

and i am not sure what you mean about testing the variable for null or cast please explain a little more.

Thanx for the quick response too.

[Serenarules]

Ok, you to validate that the user actually enters data. If you don't, $_POST['whatever'] might have nothing (null) or an empty string "". To check this, you might do something like this:

// set $firstname = the value in $_POST only if there's a value, otherwise, set a default
$firstname = is_null($_POST['firstname']) ? 'default name' : $_POST['firstname'];

// lines skipped...

// bind the value
$statement->bindValue(":firstname", $firstname);
// or bind the value with data type info
$statement->bindValue(":firstname", $firstname, PDO::PARAM_STR); 

Then just execute(). Do not pass another array in. The values are already set by this point via bindValue. If you do NOT call bindValue, then you WOULD need to pass in the array. So what's happened in your example is, you've bound all the params using bindvalue, then passed in some (but not all) params via execute. This rebinds things, and since the new array is incomplete, some placeholders have no value. Thus, it fails.

From the manual:

"You cannot bind more values than specified; if more keys exist in input_parameters than in the SQL specified in the PDO:repare(), then the statement will fail and an error is emitted. "

[mochine]

i changed my query to

if(isset($_POST['$firstname'])) {

try{

$sql = 'INSERT INTO customer SET
firstname = :firstname,
lastname = :lastname,
address = :address,
city = :city,
state = :state,
zip = :zip,
phone = hone,
email = :email';

$s = $pdo->prepare($sql);
$s->bindValue(":firstname", $_POST['$firstname'], PDO :: PARAM_STR);
$s->bindValue(":lastname", $_POST['$lastname'], PDO :: PARAM_STR);
$s->bindValue(":address", $_POST['$address'], PDO :: PARAM_STR);
$s->bindValue(":city", $_POST['$city'], PDO :: PARAM_STR);
$s->bindValue(":state", $_POST['$state'], PDO :: PARAM_STR);
$s->bindValue(":zip", $_POST['$zip'], PDO :: PARAM_STR);
$s->bindValue("hone", $_POST['$phone'], PDO :: PARAM_STR);
$s->bindValue(":email", $_POST['$email'], PDO :: PARAM_STR);
$s->execute();

} catch (PDOException $e){
$output = 'Error adding submitted customer info: ' . $e->getMessage();
include 'error.html';
exit();
}
header ('Location: .');
exit();
}

and it still will not post to the database @ all. I get no error either. I echoed out all of the $_POST[' '] values and it shows up on the html page that i designate it too. I also re-did my database table again (just to make sure it wasnt causing a problem too. I even changed from single quotes to double quotes to no avail. Do i need to add a entry for the primary id key too. I want and will do entry validation but i would like to get past this error part first. I am sorry that i am having a hard time understanding this but any help would be nice please bear with me. Thanx again for your help and quick response too.

[Serenarules]

Are you getting an error message? Does the exception throw? If so, post the exact message. I'll be on in the morning to revisit this. In the meantime, another mentor may step in.

[mochine]

thanx for the the response this throws no exception error, and no message comes up @ all. All parts of this codes works except the insert part. Here is a copy of the whole code

<?php

include 'magic_quotes.php';
include 'connect.php';

if(isset($_GET['addcustomer'])){
include 'CustomerInfoForm.html';
exit();
}

if(isset($_POST['$firstname'])) {

try{

$sql = 'INSERT INTO customer SET
firstname = :firstname,
lastname = :lastname,
address = :address,
city = :city,
state = :state,
zip = :zip,
phone = hone,
email = :email';

$s = $pdo->prepare($sql);
$s->bindValue(":firstname", $_POST['$firstname'], PDO :: PARAM_STR);
$s->bindValue(":lastname", $_POST['$lastname'], PDO :: PARAM_STR);
$s->bindValue(":address", $_POST['$address'], PDO :: PARAM_STR);
$s->bindValue(":city", $_POST['$city'], PDO :: PARAM_STR);
$s->bindValue(":state", $_POST['$state'], PDO :: PARAM_STR);
$s->bindValue(":zip", $_POST['$zip'], PDO :: PARAM_STR);
$s->bindValue("hone", $_POST['$phone'], PDO :: PARAM_STR);
$s->bindValue(":email", $_POST['$email'], PDO :: PARAM_STR);
$s->execute();

} catch (PDOException $e){
$output = 'Error adding submitted customer info: ' . $e->getMessage();
include 'error.html';
exit();
}
header ('Location: .');
exit();
}

try {
$sql = 'SELECT id, firstname, lastname, address,
city, state, zip, phone, email
FROM customer';
$result = $pdo->query($sql);

}
catch(PDOException $e) {
$output = 'Error fetching jokes from the database: '.$e->getMessage();
include 'error.html';
exit();
}

foreach ($result as $row) {
$customers[] = array('id'=>$row['id'], 'firstname'=>$row[firstname], 'lastname'=>$row[lastname],
'address'=>$row[address], 'city'=>$row[city], 'state'=>$row[state], 'zip'=>$row[zip],
'phone'=>$row[phone], 'email'=>$row[email]);
}
include 'CustomerList.html';

?>

[Serenarules]

It looks as if you're trying to see if something exists in the database. If not present, insert it?

You're overall methodology is wrong. In the try block, you have a select statement. This block may succeed, even if no records are returned. It will just be a record set with 0 rows. Your insert will never be triggered.

What you need to do is select on unique keys and get a count. "select count(id) as id_count where firstname = :firstname, lastname = :lastname" (add all your keys here, you shouldn't have too many of these).

Then test the count.

// btw... execute returns a bool. test it (not shown here)
$statement->execute()

// btw...fetch returns multiple types, check the manual
$firstrecord = $statement->fetch(PDO::FETCH_ASSOC);

if ($firstrecord['id_count'] == 0)
{
// now insert the record because we didn't have it
} 

[mochine]

yes and no yes the initial page will display what info is currently there and if new information needs to be added there is a link for the enter new data form. Getting the data from the form into the database is the problem right now @ least and i cant see to figure out what it is.

to summarize:
1.the first isset is to check for add new customer/person
2.the second isset is to add new customer if there no listing of that person in the list
3.the 3rd (the select statement) is to just display the current customer/people that is currently in the database (the first page that will be seen) and if not there will be a link to insert new data into the data base (#2 is to insert into the database) and then redisplay the main page with new info added(which it doesnt).

#1 and #3 are not an issue they work fine and does what it is suppose to do the only problem i am having is # 2. It will not insert any new data from the form fields that were entered by me (testing) to see if the database will add the data entry i took the post info and echoed them out and they show up on the list page but it will not go into the database @ all. also the table that this is being inserted into has a id key that is primary too. This is the problem that i am having. I cant seem to see where the problem lies @, should i use something different in #2/should i change it? if so how/what should i change it to? Thanx for your time and patience and thanx for your effort and quick responses too.

[Serenarules]

As I explained, the reason issue 2 is failing is because your test for the presence of an existing user is flawed. The select in your try block will always suceed. Even if a user is not found, the statement will succeed and return 0 rows. Therefore, the exception is never thrown. Use the sample code I gave you above as a basis for altering your code. At least attempt it yourself, and if it doesn't work, then post the code in question, in it's entirety, with any errors recieved. Then we can help you better.

[mochine]

i figured it out i thought about what you said and i alos noticed that there was a $ in the $_POST['$whatever'] i removed the $whatever and called it whatever and made a few slight corrections and voila it started to work Thanx agian for you help in regards to the sample code and the time spent on helping me resolve the issue that i was up against