couldn't insert data

[DrunkPanda]

I just couldn't insert data into database
so there must be thing wrong with my code
could somebody take look for me thank you

<? 
------------------build database.php-----------------------
//this the table information 
 $aSQL = "create table user (userID  integer not null auto_increment,
                             firstName   char(35),
                             lastName    char(35),
                             userName    char(20),
                             password    char(10),
                             eMail       char(30),
                             fColor      char(15),
                             fAnimals    char(20),
                             fEngine     char(50),
                             primary key (userID),
                             foreign key (newsSiteID) references newsSite (newsSiteID))";
--------------------comfirm_register.php ----------------------------

$theFirstName = $_POST['theFirstName'];
$theLastName  = $_POST['theLastName'];
$theUserName  = $_POST['theUserName'];
$thePassword1 = $_POST['thePassword1'];
$thePassword2 = $_POST['thePassword2'];
$theEmail     = $_POST['theEMail'];
$theFColor    = $_POST['theFColor'];
$theFAnimals  = $_POST['theFAnimals'];
$theEngine    = $_POST['theEngine'];
$theNewsSite  = $_POST['theNewsSite'];

//some errors checking here

$aSQL = mysql_query("INSERT INTO user VALUES(null,'$theFirstName','$theLastName','$theUserName','$thePassword','$theEMail','$theFColor','$theFAnimals' '$theEngine')")
                    or die (mysql_error());
?>

Error messege: Column count doesn't match value count at row 1

[spikeZ]

Hi,
Two things that stand out.
1/ If the ID column is defined an auto increment, you don't need to put NULL as the insert value.

2/ May just be a typo but - '$the EMail' did you mean to have a space?

try...
$aSQL = mysql_query("INSERT INTO user VALUES('$theFirstName','$theLastName','$theUserName','$thePassword','$theEMail','$theFColor','$theFAnimals' '$theEngine')")
or die (mysql_error());

[DrunkPanda]

Hi,
Two things that stand out.
1/ If the ID column is defined an auto increment, you don't need to put NULL as the insert value.

2/ May just be a typo but - '$the EMail' did you mean to have a space?

try...
$aSQL = mysql_query("INSERT INTO user VALUES('$theFirstName','$theLastName','$theUserName','$thePassword','$theEMail','$theFColor','$theFAnimals' '$theEngine')")
or die (mysql_error());

thanks spikeZ
No the $theMail doesn't suppose to have space,
sorry I just couldn't find where cause problem
could you take look thank you

<? 
<?
include 'db.php';

// Define post fields into simple variables
$theFirstName = $_POST['theFirstName'];
$theLastName  = $_POST['theLastName'];
$theUserName  = $_POST['theUserName'];
$thePassword1 = $_POST['thePassword1'];
$thePassword2 = $_POST['thePassword2'];
$theEmail     = $_POST['theEMail'];
$theFColor    = $_POST['theFColor'];
$theFAnimals  = $_POST['theFAnimals'];
$theEngine    = $_POST['theEngine'];
$theNewsSite  = $_POST['theNewsSite'];

/* Let's strip some slashes in case the user entered
any escaped characters. */

$theFirstName = stripslashes($theFirstName);
$theLastName  = stripslashes($theLastName);
$theEMail     = stripslashes($theEMail);
$theUserName  = stripslashes($theUserName);
$thePassword1 = stripslashes($thePassword1);
$thePassword2 = stripslashes($thePassword2);
$theFColor    = stripslashes($theFColor);
$theFAnimals  = stripslashes($theFAnimals);
$theEngine    =stripslashes ($theEngine);
$theNewsSite  = stripslashes($theNewsSite);


/* Do some error checking on the form posted fields */

if((!$theFirstName) || (!$theLastName) || (!$theEMail) || (!$theUserName) || (!$thePassword1)){
        echo 'You did not submit the following required information! <br />';
        if(!$theFirstName){
                echo "First Name is a required field. Please enter it below.<br />";
        }
        if(!$theLastName){
                echo "Last Name is a required field. Please enter it below.<br />";
        }
        if(!$theEmail){
                echo "Email Address is a required field. Please enter it below.<br />";
        }
        if(!$theUserName){
                echo "Desired User Name is a required field. Please enter it below.<br />";
        }
        if(($thePassword1) != ($thePassword2)){
                echo "The two passwords are not match. Please type again.<br/>";
        }
        else {
              $thePassword = $thePassword1;
        }
        if(!$theFColor){
                echo "Favourite Color is a required field. Please enter it below.<br />";
        }
        if(!$theFAnimals){
                echo "Favourite Animals is a required field. Please enter it below.<br />";
        }
        //Check that user name and password only contain allowed characters
        //lookUp:preg_match(regex)
        if(preg_match('/[^a-z0-9_-]/i',$theUserName)){
           echo ' Please make sure that the User Name contains only valid characters';
        }
        include 'register_form.html'; // Show the form again!
        /* End the error checking and if everything is ok, we'll move on to
         creating the user account */
        exit(); // if the error checking has failed, we'll exit the script!
}

/* Let's do some checking and ensure that the user's email address or username
 does not exist in the database */

 $sql_email_check = mysql_query("SELECT eMail FROM user WHERE eMail ='$theEMail'");
 $sql_username_check = mysql_query("SELECT userName FROM user WHERE userName='$theUserName'");

 $email_check = mysql_num_rows($sql_email_check);
 $username_check = mysql_num_rows($sql_username_check);

 if(($email_check > 0) || ($username_check > 0)){
         echo "Please fix the following errors: <br />";
         if($email_check > 0){
                 echo "<strong>Your email address has already been used by another member in our database. Please submit a different Email address!<br />";
                 unset($theEMail);
         }
         if($username_check > 0){
                 echo "The username you have selected has already been used by another member in our database. Please choose a different Username!<br />";
                 unset($theUserName);
         }
         include 'register_form.html'; // go back to register_form.html
         exit();  // exit, the account will not be created
 }

/* Everything has passed both error checks that we have done.
It's time to create the account! */
// Enter create a user account

$aSQL = mysql_query("INSERT INTO user VALUES('$theFirstName','$theLastName','$theUserName','$thePassword','$theEMail','$theFColor','$theFAnimals' '$theEngine')")
                    or die (mysql_error());

$aSql = mysql_query("insert into newssite values('$theNewsSite')")
                    or die (mysql_error());
?>
?>

[1337-Dev]

You have 2 <? and 2 ?> at the beginning and end of your script?

Also, the LAST insert query, make insert into capitalised (INSERT INTO) and values the same (VALUES).

Can you post what is being outputted by the browser aswell please. Anything that is outputted will be helpfull.

[frezno]

I[php]
$aSQL = mysql_query("INSERT INTO user VALUES('$theFirstName','$theLastName','$theUserName','$thePassword','$theEMail','$theFColor','$theFAnimals' '$theEngine')")

you are using a wrong syntax for INSERT'ing
You can use either:

INSERT INTO table (field1, field2, field3) VALUES ('value1', 'value2', 'value3');

or

INSERT INTO table SET field1='value1', field2='value2', field3='value3';

i.e. you have to assigne a value to your field and what you did is just providing the value.
Doing this, the database doesn't know how to handle these data and that results into ignoring it.

So you should change the query to:

INSERT INTO user SET firstname = '$theFirstName', lastname = '$theLastName', // etc.

[1337-Dev]

Or

$aSql = "INSERT INTO user (firstname, lastname, username, password...) VALUES ('$firstName', '$lastName', '$userName', '$passWord'...)";
 
$aSql_1 = mysql_query($aSql)or die("Some error.."); 

[frezno]

Just to make sure there's no confusion about what i said in the above post.
The syntax you are using is allowed and possible but should be avoided - too many pitfalls:

The VALUES list must contain one value for every column (field) in the table.
Furthermore, the values must be listed in the same order in which the columns are named in the table's definition.
This makes it pretty unhandy.

[frezno]

$aSQL = mysql_query("INSERT INTO user VALUES(null,'$theFirstName','$theLastName','$theUserName','$thePassword',
'$theEMail','$theFColor','$theFAnimals' '$theEngine')")
or die (mysql_error()); 

To finalize all this, cause for the error message is a missing comma between '$theFAnimals' and '$theEngine'
So change it to:

$aSQL = mysql_query("INSERT INTO user VALUES(null, '$theFirstName', '$theLastName', '$theUserName', '$thePassword',
'$theEMail', '$theFColor', '$theFAnimals', '$theEngine')")
or die (mysql_error()); 

and it should work

[frezno]

1/ If the ID column is defined an auto increment, you don't need to put NULL as the insert value.

In this case he must use NULL to fit the number of values to the number of table columns (fields).

[DrunkPanda]

thank you very much Frezon and all other helpers
but sorry I still cann't get it work
Error messenge:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user(null,firstName,lastName,userName,password,eMail,fColor,fAn

php version 4.3.6
P.S: There is no space - '$the EMail' - I don't why it print out like that

<?

$aSQL = "insert into user(userID,firstName,lastName,userName,password,eMail,fColor,fAnimals,fEngine)
                   values(null,'$theFirstName','$theLastName','$theUserName','$thePassword','$theEMail','$theFColor','$theFAnimals','$theEngine')";
$aSQL_1 = mysql_query($aSQL) or die (mysql_error());

?>

[spikeZ]

Hi,
Just ran your query and got the error.
try the following code to correct it,


//connect
$hostname_vse = "xxx.com";
$database_vse = "xxx";
$username_vse = "vxx";
$password_vse = "xxx";
$vse = mysql_pconnect($hostname_vse, $username_vse, $password_vse) or die(mysql_error());
//

mysql_select_db($database_vse, $vse);
$theFirstName = $_POST['theFirstName'];
$theLastName = $_POST['theLastName'];
$theUserName = $_POST['theUserName'];
$thePassword1 = $_POST['thePassword1'];
$thePassword2 = $_POST['thePassword2'];
$theEmail = $_POST['theEMail'];
$theFColor = $_POST['theFColor'];
$theFAnimals = $_POST['theFAnimals'];
$theEngine = $_POST['theEngine'];
$theNewsSite = $_POST['theNewsSite'];


$pdsql = "INSERT INTO user (userID, firstName, lastName, userName, password, eMail , fColor, fAnimals, fEngine) VALUES ('', '$theFirstName','$theLastName','$theUserName','$thePassword','$theEMail','$theFColor','$theFAnimals','$theEngine')";
$pdresult = mysql_query($pdsql, $vse);

See if that helps.

Regards
SpikeZ

[DrunkPanda]

SpikeZ, thank you for your enthusiastic
did you run the code Successfully?

no, still not right, there must be something wrong but I just couldn't find it , so sad
well I guess I will rebuild the code in other way
I think I have wasted too much time on finding errors
and the wasted time is enough for me to build another which might be working, but I really want to know where goes wrong
once again thank you for your help SpikeZ

[spikeZ]

DOn't give up!
Just noticed something, I had to change the insert password variables and forgot to change it back. Check the password insert is '$thePassword1' !

Cheers

SpikeZ

[DrunkPanda]

I actually done this in the middle of error checking

<?
if(($thePassword1) != ($thePassword2)){
                echo "The two passwords are not match. Please type again.<br/>";
        }
        else {
              $thePassword = $thePassword1;
        }

?>

cheers

[1337-Dev]

Ok, DrunkPanda, can you put all the problems your getting, then the current code you have, into 1 thread, nice and neatly, and the problem should be solved , because i am just lost.

[DrunkPanda]

Ok, DrunkPanda, can you put all the problems your getting, then the current code you have, into 1 thread, nice and neatly, and the problem should be solved , because i am just lost.

Hi 1337-Dev
Sorry about that, below is my current code and error
messenge and some information you might want to know.

Error Messenge form mysql_error()
---------------------------------------------------------------
You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'user(userID,firstName,lastName,userName,password,eMail,fColor,f
---------------------------------------------------------------
Just in case you want to know
PHP Version 4.3.6
register_globals On
Apache Version Apache/1.3.29 (Win32) PHP/4.3.6
and I don't know why everytime I post code on this thread there is always a space like this: "$theE Mail'
it looks ok on my editor

<?
include 'db.php';

// Define post fields into simple variables
$theFirstName = $_POST['theFirstName'];
$theLastName  = $_POST['theLastName'];
$theUserName  = $_POST['theUserName'];
$thePassword1 = $_POST['thePassword1'];
$thePassword2 = $_POST['thePassword2'];
$theEmail     = $_POST['theEMail'];
$theFColor    = $_POST['theFColor'];
$theFAnimals  = $_POST['theFAnimals'];
$theEngine    = $_POST['theEngine'];
$theNewsSite  = $_POST['theNewsSite'];

/* Let's strip some slashes in case the user entered
any escaped characters. */

$theFirstName = stripslashes($theFirstName);
$theLastName  = stripslashes($theLastName);
$theEMail     = stripslashes($theEMail);
$theUserName  = stripslashes($theUserName);
$thePassword1 = stripslashes($thePassword1);
$thePassword2 = stripslashes($thePassword2);
$theFColor    = stripslashes($theFColor);
$theFAnimals  = stripslashes($theFAnimals);
$theEngine    =stripslashes ($theEngine);
$theNewsSite  = stripslashes($theNewsSite);


/* Do some error checking on the form posted fields */

if((!$theFirstName) || (!$theLastName) || (!$theEMail) || (!$theUserName) || (!$thePassword1)){
        echo 'You did not submit the following required information! <br />';
        if(!$theFirstName){
                echo "First Name is a required field. Please enter it below.<br />";
        }
        if(!$theLastName){
                echo "Last Name is a required field. Please enter it below.<br />";
        }
        if(!$theEmail){
                echo "Email Address is a required field. Please enter it below.<br />";
        }
        if(!$theUserName){
                echo "Desired User Name is a required field. Please enter it below.<br />";
        }
        if(($thePassword1) != ($thePassword2)){
                echo "The two passwords are not match. Please type again.<br/>";
        }
        else {
              $thePassword = $thePassword1;
        }
        if(!$theFColor){
                echo "Favourite Color is a required field. Please enter it below.<br />";
        }
        if(!$theFAnimals){
                echo "Favourite Animals is a required field. Please enter it below.<br />";
        }
        //Check that user name and password only contain allowed characters
        //lookUp:preg_match(regex)
        if(preg_match('/[^a-z0-9_-]/i',$theUserName)){
           echo ' Please make sure that the User Name contains only valid characters';
        }
        include 'register_form.html'; // Show the form again!
        /* End the error checking and if everything is ok, we'll move on to
         creating the user account */
        exit(); // if the error checking has failed, we'll exit the script!
}

/* Let's do some checking and ensure that the user's email address or username
 does not exist in the database */

 $sql_email_check = mysql_query("SELECT eMail FROM user WHERE eMail ='$theEMail'");
 $sql_username_check = mysql_query("SELECT userName FROM user WHERE userName='$theUserName'");

 $email_check = mysql_num_rows($sql_email_check);
 $username_check = mysql_num_rows($sql_username_check);

 if(($email_check > 0) || ($username_check > 0)){
         echo "Please fix the following errors: <br />";
         if($email_check > 0){
                 echo "<strong>Your email address has already been used by another member in our database. Please submit a different Email address!<br />";
                 unset($theEMail);
         }
         if($username_check > 0){
                 echo "The username you have selected has already been used by another member in our database. Please choose a different Username!<br />";
                 unset($theUserName);
         }
         include 'register_form.html'; // go back to register_form.html
         exit();  // exit, the account will not be created
 }

/* Everything has passed both error checks that we have done.
It's time to create the account! */
// Enter create a user account

$aSQL   = "insert into user(userID,firstName,lastName,userName,password,eMail,fColor,fAnimals,fEngine)
                     values(null'$theFirstName','$theLastName','$theUserName','$thePassword','$theEMail','$theFColor','$theFAnimals','$theEngine')";
$aSQL_1 = mysql_query($aSQL) or die (mysql_error());

$aSQL   = "insert into newssite(newsSiteID,newsSite) values(null,'$theNewsSite')";
$aSQL_1 = mysql_query($aSQL) or die (mysql_error());
?>

Please help me go through this much much appreciated
DrunkPanda

[Helge]

I think you're missing a comma. Try

$aSQL   = "insert into user(  userID
                            , firstName
                            , lastName
                            , userName
                            , password
                            , eMail
                            , fColor
                            , fAnimals
                            , fEngine) 
                     values(  null
                            , '$theFirstName'
                            , '$theLastName'
                            , '$theUserName'
                            , '$thePassword'
                            , '$theEMail'
                            , '$theFColor'
                            , '$theFAnimals'
                              '$theEngine')";

// Echo out the SQL so it's easier to see the errors.
echo "<p>$aSQL</p>";

$aSQL_1 = mysql_query($aSQL) or die (mysql_error()); 

-Helge

[DrunkPanda]

thanks Helge, I have done the comma thing, but the problem is still there
and I used --- echo "<p>$aSQL</p>"; shows below
--------------------------------------------------------------------
insert into user(userID,firstName,lastName,userName,password,eMail,fColor,fAnimals,fEngine)
values(null,'Mark','Li','DoCoMo','1234','php@yahoo.com','white','Mouse','www.google.com')

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user(userID,firstName,lastName,userName,password,eMail,fColor,f
---------------------------------------------------------------------
it look like I have get all the data but it just couldn't insert into database
so that make me think its there any wrong with this
would the foreign key be a problem?
------------------------BuildDataBase.php-----------------------

<?
//this the table information 
$aSQL = "create table user (userID  integer not null auto_increment, 
                             firstName   char(35), 
                             lastName    char(35), 
                             userName    char(20), 
                             password    char(10), 
                             eMail       char(30), 
                             fColor      char(15), 
                             fAnimals    char(20), 
                             fEngine     char(50), 
                             primary key (userID), 
                             foreign key (newsSiteID) references newsSite (newsSiteID))"; 
?>

---------------------------------------------------------------------
thank you
DrunkPanda

[1337-Dev]

Your trying to insert a userID into the userID but its set to auto_increment.

Also try this instead for the insert query:

$aSQL="INSERT INTO user ('userID','firstName','lastName','userName','password','eMail','fColor','fAnimals','fEngine') VALUES (null, '$theFirstName','$theLastName','$theUserName','$thePassword','$theEMail','$theFColor','$theFAnimals','$theEngine')"; 

What i would do, is get rid of all of thise capitalised stuff:

$aSQL="INSERT INTO user ('userid','firstname','lastname','username','password','email','fcolor','fanimals','fengine') VALUES (null, '$thefirstName','$thelastname','$theusername','$thepassword','$theemail','$thefcolor','$thefanimals','$theengine')"; 

but thats up to you.. .

[frezno]

Your trying to insert a userID into the userID but its set to auto_increment.

No, he isn't. Although it would be less error prone to skip this field, if it's set to '' or NULL it will be incremented by default.

BTW, field names shouldn't be quoted as you did in your example:

$aSQL="INSERT INTO user ('userid','firstname' /* etc */) VALUES ( /* etc */ )"; 

$aSQL="INSERT INTO user (userid, firstname /* etc */) VALUES ( /* etc */ )"; 

[DrunkPanda]

although I still couldn't find the problem
I appreciated all your guys who have tried to help me
and at this stage I think the cause of the problem is my limited
knowledge of PHP which make a small error become such a big problem
so I think the best way for me now is to do some more tutorials rather
then hanging there with the error

once again thank you all your help ^_^

[spikeZ]

Okay Last one!
Copied your full code and created the form, altered the query slightly and ran the code (below). This works fine and inserts all data into the two tables.

<? 
include 'db.php'; 

// Define post fields into simple variables 
$theFirstName = $_POST['theFirstName']; 
$theLastName  = $_POST['theLastName']; 
$theUserName  = $_POST['theUserName']; 
$thePassword1 = $_POST['thePassword1']; 
$thePassword2 = $_POST['thePassword2']; 
$theEmail     = $_POST['theEMail']; 
$theFColor    = $_POST['theFColor']; 
$theFAnimals  = $_POST['theFAnimals']; 
$theEngine    = $_POST['theEngine']; 
$theNewsSite  = $_POST['theNewsSite']; 

/* Let's strip some slashes in case the user entered 
any escaped characters. */ 

$theFirstName = stripslashes($theFirstName); 
$theLastName  = stripslashes($theLastName); 
$theEMail     = stripslashes($theEMail); 
$theUserName  = stripslashes($theUserName); 
$thePassword1 = stripslashes($thePassword1); 
$thePassword2 = stripslashes($thePassword2); 
$theFColor    = stripslashes($theFColor); 
$theFAnimals  = stripslashes($theFAnimals); 
$theEngine    =stripslashes ($theEngine); 
$theNewsSite  = stripslashes($theNewsSite); 


/* Do some error checking on the form posted fields */ 

if((!$theFirstName) || (!$theLastName) || (!$theEMail) || (!$theUserName) || (!$thePassword1)){ 
        echo 'You did not submit the following required information! <br />'; 
        if(!$theFirstName){ 
                echo "First Name is a required field. Please enter it below.<br />"; 
        } 
        if(!$theLastName){ 
                echo "Last Name is a required field. Please enter it below.<br />"; 
        } 
        if(!$theEmail){ 
                echo "Email Address is a required field. Please enter it below.<br />"; 
        } 
        if(!$theUserName){ 
                echo "Desired User Name is a required field. Please enter it below.<br />"; 
        } 
        if(($thePassword1) != ($thePassword2)){ 
                echo "The two passwords are not match. Please type again.<br/>"; 
        } 
        else { 
              $thePassword = $thePassword1; 
        } 
        if(!$theFColor){ 
                echo "Favourite Color is a required field. Please enter it below.<br />"; 
        } 
        if(!$theFAnimals){ 
                echo "Favourite Animals is a required field. Please enter it below.<br />"; 
        } 
        //Check that user name and password only contain allowed characters 
        //lookUp preg_match(regex) 
        if(preg_match('/[^a-z0-9_-]/i',$theUserName)){ 
           echo ' Please make sure that the User Name contains only valid characters'; 
        } 
        include 'register_form.html'; // Show the form again! 
        /* End the error checking and if everything is ok, we'll move on to 
         creating the user account */ 
        exit(); // if the error checking has failed, we'll exit the script! 
} 

/* Let's do some checking and ensure that the user's email address or username 
does not exist in the database */ 

$sql_email_check = mysql_query("SELECT eMail FROM user WHERE eMail ='$theEMail'"); 
$sql_username_check = mysql_query("SELECT userName FROM user WHERE userName='$theUserName'"); 

$email_check = mysql_num_rows($sql_email_check); 
$username_check = mysql_num_rows($sql_username_check); 

if(($email_check > 0) || ($username_check > 0)){ 
         echo "Please fix the following errors: <br />"; 
         if($email_check > 0){ 
                 echo "<strong>Your email address has already been used by another member in our database. Please submit a different Email address!<br />"; 
                 unset($theEMail); 
         } 
         if($username_check > 0){ 
                 echo "The username you have selected has already been used by another member in our database. Please choose a different Username!<br />"; 
                 unset($theUserName); 
         } 
         include 'register_form.html'; // go back to register_form.html 
         exit();  // exit, the account will not be created 
} 

/* Everything has passed both error checks that we have done. 
It's time to create the account! */ 
// Enter create a user account 

$aSQL   = "INSERT INTO user (userID, firstName, lastName, userName, password, eMail , fColor, fAnimals, fEngine) VALUES ('', '$theFirstName','$theLastName','$theUserName','$thePassword1','$theEMail','$theFColor','$theFAnimals','$theEngine')"; 
$aSQL_1 = mysql_query($aSQL) or die (mysql_error()); 

$aSQL   = "insert into newssite(newsSiteID,newsSite) values(null,'$theNewsSite')"; 
$aSQL_1 = mysql_query($aSQL) or die (mysql_error()); 
?>

The only things that I have changed were:
VALUES('',

AND

'$thePassword1',


Hope this works for you!

SpikeZ

[DrunkPanda]

spikeZ, this is unbelievable it works, thank you very much

you saved

<?
// now I understand the symtax would be like this 

...............VALUES ( ' ', '$theFirstName' ....................

?>

All the Best to you

Drunkpanda

[1337-Dev]

YAAAAAAAY . We all deserve a pat on the back .

[spikeZ]

No Problem DrunkPanda,
1337-Dev consider your back well patted my friend!

SpikeZ