Client Database Insert Issue

[Angeleyezz]

Hello everyone, I am trying to write a client database in php & mysql, Its going to be a localized system not online, just run through a home server.  really simple stuff not much security, etc.  For some reason it does not want to insert into the database, keeps giving me different errors,  at first i tried the account# as '' and it told me to check my version of mysql for proper syntax, i am using version 5.1.13, but then i tried $_POST, and im getting a parse error, i'm lost here, dont understand why its not matching up, everything is spelled right. 

 

basically i have a customer information table with the fields, account_# {primary key, auto increment}, name_first, name_last, address, city, state, zipcode, telephone, telephone_alt

 

here is my web form code "very very rough draft, just started trying to make it work before it looks pretty lol"

<form action="add_customer.php" method="post">
Account#: <input type="text" name="account_#" />
Firstname: <input type="text" name="name_first" />
Lastname: <input type="text" name="name_last" />
Address: <input type="text" name="address" />
City: <input type="text" name="city" />
State: <input type="text" name="state" />
Zipcode: <input type="text" name="zipcode" />
Telephone: <input type="text" name="telephone" />
Telephone Alt: <input type="text" name="telephone_alt" />
<input type="submit" />
</form>

 

then add_customer.php is

mysql_select_db("terra_elegante_operations", $con);
$sql="INSERT INTO customer_information (account_#, name_first, name_last, address, city, state, zipcode, telephone, telephone_alt)
VALUES
('$_POST[account_#]','$_POST[name_first]','$_POST[name_last]','$_POST[address]','$_POST[address]','$_POST[city]','$_POST[state]','$_POST[zipcode]','$_POST[telephone]','$_POST[telephone_alt]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

[digibucc]

in add_customer.php

 

<?php
$sql="INSERT INTO customer_information (account_#, name_first, name_last, address, city, state, zipcode, telephone, telephone_alt)
VALUES
('$_POST[account_#]','$_POST[name_first]','$_POST[name_last]','$_POST[address]','$_POST[address]','$_POST[city]','$_POST[state]','$_POST[zipcode]','$_POST[telephone]','$_POST[telephone_alt]')";
?>

 

should be

 

<?php
$sql="INSERT INTO customer_information (account_#, name_first, name_last, address, city, state, zipcode, telephone, telephone_alt)
VALUES
($_POST['account_#'],$_POST['name_first'],$_POST['name_last'],$_POST['address'],$_POST['address'],$_POST['city'],$_POST['state'],$_POST['zipcode'],$_POST['telephone'],$_POST['telephone_alt'])";
?>

 

for 2 reasons:

a) values need to be enclosed in single quotes if they are static, you are inserting variables ($_POST) , and so they are not enclosed.

b) the index of the $_POST array needs to be enclosed in single quotes.

[Angeleyezz]

Still no good,  Parse error: syntax error, unexpected T_VARIABLE in Line 11 = starts with the "post" line

 

heres the new code that generated the crash, with double quotes outside main post, and without like you showed, same thing =(

 

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("terra_elegante_operations", $con);
$sql="INSERT INTO customer_information (account_#, name_first, name_last, address, city, state, zipcode, telephone, telephone_alt)
VALUES
("$_POST['account_#'],$_POST['name_first'],$_POST['name_last'],$_POST['address'],$_POST['address'],$_POST['city'],$_POST['state'],$_POST['zipcode'],$_POST['telephone'],$_POST['telephone_alt']");

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
?>

[Proletarian]

Take away the double paranthesis in your VALUES list.

[PFMaBiSmAd]

Using a # as part of your column name, html field name and perhaps even the php array index name is a problem (requires special handling.) Use something else, like account_no

 

To put a php array variable directly inside of a double-quoted string requires that you put {} around each array variable.

[digibucc]

since your field names are the same as your input names, what about this?

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("terra_elegante_operations", $con);

foreach ($_POST as $key=>$value){
if ($value != '' && $value != 'Submit'){
$cols .= mysql_real_escape_string($key). ', ';
$vals .= '\''. mysql_real_escape_string($value). '\', ';
$message .= mysql_real_escape_string($key). ' = '. mysql_real_escape_string($value). ' <br>';
}
}

$columns = substr($cols,0,-2);
$values = substr($vals,0,-2);

$sql="INSERT INTO customer_information( $columns )VALUES ( $values )";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
?>

[Angeleyezz]

( ! ) Notice: Undefined variable: cols in C:\wamp\www\terraelegante.com\Operations\add_customer.php on line 12

Call Stack

# Time Memory Function Location

1 0.0005 688728 {main}( ) ..\add_customer.php:0

 

( ! ) Notice: Undefined variable: vals in C:\wamp\www\terraelegante.com\Operations\add_customer.php on line 13

Call Stack

# Time Memory Function Location

1 0.0005 688728 {main}( ) ..\add_customer.php:0

 

( ! ) Notice: Undefined variable: message in C:\wamp\www\terraelegante.com\Operations\add_customer.php on line 14

Call Stack

# Time Memory Function Location

1 0.0005 688728 {main}( ) ..\add_customer.php:0

Error: Unknown column 'submit' in 'field list'

[Angeleyezz]

it added the record, but it gave me this error now

 

 

( ! ) Notice: Undefined variable: cols in C:\wamp\www\terraelegante.com\Operations\add_customer.php on line 12

Call Stack

# Time Memory Function Location

1 0.0027 688728 {main}( ) ..\add_customer.php:0

 

( ! ) Notice: Undefined variable: vals in C:\wamp\www\terraelegante.com\Operations\add_customer.php on line 13

Call Stack

# Time Memory Function Location

1 0.0027 688728 {main}( ) ..\add_customer.php:0

 

( ! ) Notice: Undefined variable: message in C:\wamp\www\terraelegante.com\Operations\add_customer.php on line 14

Call Stack

# Time Memory Function Location

1 0.0027 688728 {main}( ) ..\add_customer.php:0

1 record added

 

yet the record showed up in the phpmyadmin...  i dont get it

[digibucc]

that was my fault, i left an old var in there

 

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("terra_elegante_operations", $con);

foreach ($_POST as $key=>$value){
if ($value != '' && $value != 'Submit'){
$cols .= mysql_real_escape_string($key). ', ';
$vals .= '\''. mysql_real_escape_string($value). '\', ';
}
}

$columns = substr($cols,0,-2);
$values = substr($vals,0,-2);

$sql="INSERT INTO customer_information( $columns )VALUES ( $values )";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
?>

 

should do the same with no error.

 

this line

<?php 	$message .= mysql_real_escape_string($key). ' = '. mysql_real_escape_string($value). ' <br>'; ?> 

was trying to continue a variable that didn't exist, and you had no need of. this is reused code, and i forgot to remove that part.

[Angeleyezz]

I just realized it didnt even put the correct zipcode in for some reason, it came out as 255 lol.  there has to be a much easier way to do this.

[PFMaBiSmAd]

Unconditionally looping over every submitted $_POST key/value is asking for trouble. You should instead have an array of expected index/column names that you loop over and access the corresponding $_POST values.

[Angeleyezz]

( ! ) Notice: Undefined variable: cols in C:\wamp\www\terraelegante.com\Operations\add_customer.php on line 12

Call Stack

# Time Memory Function Location

1 0.0027 687024 {main}( ) ..\add_customer.php:0

 

( ! ) Notice: Undefined variable: vals in C:\wamp\www\terraelegante.com\Operations\add_customer.php on line 13

Call Stack

# Time Memory Function Location

1 0.0027 687024 {main}( ) ..\add_customer.php:0

1 record added

 

Same thing with the zipcode with your new code.  its coming out as 255 instead of the actual zipcode, i have that tab setup as a tinyint(5) unsigned

[Angeleyezz]

Unconditionally looping over every submitted $_POST key/value is asking for trouble. You should instead have an array of expected index/column names that you loop over and access the corresponding $_POST values.

 

Thats what we were trying to do but for some reason my code above isnt working correctly, i cant even get the information to post there at all.  just errors.

[Angeleyezz]

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("terra_elegante_operations", $con);

$sql="INSERT INTO customer_information (account_#, name_first, name_last, address, city, state, zipcode, telephone, telephone_alt)
VALUES
($_POST['account_#'],$_POST['name_first'],$_POST['name_last'],$_POST['address'],$_POST['address'],$_POST['city'],$_POST['state'],$_POST['zipcode'],$_POST['telephone'],$_POST['telephone_alt'])";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
?>

 

This is where we were at with the $_POST attempts.

[digibucc]

it's an undefined variable error, because my code has it continuing cols/vals ($cols .=) , before cols is created.

 

put

 

$cols = '';

$vals = '';

 

before the foreach.

 

but mabismad is right so i'll let them help you your original problem was syntax

[PFMaBiSmAd]

Zip codes are not integer numbers. They are formatted strings consisting of numerical characters. Also, the maximum unsigned tinyint is 255. You should be using a VARCHAR column to hold zip codes.

[Angeleyezz]

Ok i fixed the zipcode problem, but the $_POST problem still remains, is there anything you can see wrong with my code, i think the problem is lying with sql not auto incrementing and assigning the account number when i add the entry from the website.  the account number is the primary key, then the address and telephone number are unique.

 

i really have no idea whats wrong with this code lol

[Angeleyezz]

i fixed on problem i saw, i had $_POST['address'] listed twice like an idiot. but now without it, this is the error i am recieving

 

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:\wamp\www\terraelegante.com\Operations\add_customer.php on line 12

 

line 12 is the begining of the $_POSTS

 

New code below:

 

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("terra_elegante_operations", $con);

$sql="INSERT INTO customer_information (account_#, name_first, name_last, address, city, state, zipcode, telephone, telephone_alt)
VALUES
($_POST['account_#'],$_POST['name_first'],$_POST['name_last'],$_POST['address'],$_POST['city'],$_POST['state'],$_POST['zipcode'],$_POST['telephone'],$_POST['telephone_alt'])";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
?>

[Angeleyezz]

I fixed it.  This is the code I used and it works perfectly.  Now on to the next part that I can screw up 

 

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$account_number=$_POST['account_number'];
$name_first=$_POST['name_first'];
$name_last=$_POST['name_last'];
$address=$_POST['address'];
$city=$_POST['city'];
$state=$_POST['state'];
$zipcode=$_POST['zipcode'];
$telephone=$_POST['telephone'];
$telephone_alt=$_POST['telephone_alt'];

mysql_select_db("terra_elegante_operations", $con);

$sql="INSERT INTO customer_information (account_number, name_first, name_last, address, city, state, zipcode, telephone, telephone_alt)
VALUES
('$account_number', '$name_first', '$name_last', '$address', '$city', '$state', '$zipcode', '$telephone', '$telephone_alt')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
?>

[kicken]

First, a little clean up to make the code easier to read and understand.

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("terra_elegante_operations", $con);

$escaped = array_map('mysql_real_escape_string', $_POST);
$sql="
INSERT INTO customer_information 
(
`account_#`, 
`name_first`, 
`name_last`, 
`address`, 
`city`, 
`state`, 
`zipcode`, 
`telephone`, 
`telephone_alt`
) VALUES (
'{$escaped['account_#']}',
'{$escaped['name_first']}',
'{$escaped['name_last']}',
'{$escaped['address']}',
'{$escaped['address']}',
'{$escaped['city']}',
'{$escaped['state']}',
'{$escaped['zipcode']}',
'{$escaped['telephone']}',
'{$escaped['telephone_alt']}'
)";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
?>

 

Now, in order to prevent against any problems with quotes, you need to escape all your values.  mysql_real_escape_string will take care of this for you, using array_map() is a convenient way to apply it to the entire post array.  Normally you wouldn't want to do that, in the case of a quick script for your own personal use only though it works well.

 

As mentioned when your putting array values directly into a string you have to enclose them with {} or PHP will not parse it correctly.  The problem is the quote characters around the key names.  Also as mentioned, using special characters in column names should be avoided.  In order for mysql to recognize your account_# column it has to be surrounded by backticks (`).  I surrounded all columns with them as it does not hurt.  Ideally, you should stick to a-z, 0-9, and _ when you name your fields.

 

When putting values into a query, string values have to be quoted.  numeric values can be put in without quotes however including quotes does no harm so you can just put quotes around all the values to be sure.  You can see how I surrounded each variable with single-quotes above.

 

edit: apparently I missed page two.

 

[Angeleyezz]

Thank you kicken, I changed over account_# to account_number.  Now I seem to be having a problem with case sensativity and the search functions i designed.  im going to make another post about that now.