Jump to content

Client Database Insert Issue


Angeleyezz
 Share

Recommended Posts

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";

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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";
?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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";
?>

Link to comment
Share on other sites

( ! ) 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'

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

( ! ) 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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

<?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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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";
?>

Link to comment
Share on other sites

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

 

<?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";
?>

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.