add new member details to mysql database

[ckerr27]

Hello everyone, I am trying to have a function on my website where the administrator can add a new member to the database. Their details are to be stored in the table memberdetails, I have posted the code below, the error i recieve is "Error: Column count doesn't match value count at row 1"

 

Can anybody help me please?

 

form code:

 

<form action="insert.php" method="post">

Username: <input type="text" name="username" /><br><br>

Firstname: <input type="text" name="firstname" /><br><br>

Surname : <input type="text" name="surname" /><br><br>

Date Birth: <input type="text" name="dob" /><br><br>

Total Wins: <input type="text" name="wins" />

Total Loses: <input type="text" name="loses" /><br><br>

Email Add: <input type="text" name="email" /><br><br>

Country : <input type="text" name="born" /><br><br>

Other Info: <input type="text" name="other" /><br><br>

<input type="submit" name="Submit" value="Create" align="right"></td>

</form>

 

 

insert.php

 

<?php

mysql_connect ("localhost","root","")

or die("Cannot connect to Database");

mysql_select_db ("test");

 

$sql="INSERT INTO memberdetails (username, firstname, surname, dob, totalwins, totalloses, email, country, info)

VALUES

('$_POST[username]''$_POST[firstname]','$_POST[surname]','$_POST[wins]''$_POST[loses]''$_POST''$_POST[born]''$_POST[other]''$_POST[dob]')";

 

if (!mysql_query($sql))

  {

die('Error: ' . mysql_error());

  }

echo "1 record added";

 

?>

[Jessica]

You need commas in between ALL of your values. Also you don't have them in the right order, and you have some weird names. Why is country = born???

In the future, build your query into a string so when you echo your error you can also echo the query and see what it's trying to run.

[Muddy_Funster]

You need commas, change to this:

VALUES(
'{$_POST['username']}','{$_POST['firstname']}','{$_POST['surname']}','{$_POST[dob]}','{$_POST['wins']}','{$_POST['loses']}','{$_POST['email']}','{$_POST['born']}','{$_POST['other']}'
)";

Also, be casrefull the order of things, you were off in your original code, I have fixed it. 

When addressing array keys in a parsed string use { and } around them to help the parser know what your doing.

 

You really shouldn't EVER send raw user data to your database from an end user form.

[ckerr27]

Thanks Muddy_funster it added the new member to the table. I am getting an error which i have attached an image of.

 

Also, the new user has his/her username. I have another table in the database containing username and passwords for each member, the passwords are MD5 (phpmyadmin) is there anyway the user can be given a login password with their username when the new member is added?

 

Thanks a million!

[Muddy_Funster]

I can't find your image, so I don't know what the error is.

Guessing it could be a "Notice: Undifined Constant dob assuming 'dob' ..." because I forgot to put the single quotes around the array key for $_POST['dob'] (you'll see it's all read in the code I psted, rather than changing the dob part to blue like the rest of them.)

 

I don't understand what your are asking about "giving" a password.  If you already have a table with user information in it, why are you making another one?

[ckerr27]

Yes that was it thanks.

 

I have attacthed an image of my members table, it is for logging in and the other is simply information about the user, the code above was adding the information about the new user to the memberdetails table. is there anyway i can add the username of the new member to the members table and generate a password to allow them to login?

[Muddy_Funster]

As MySQL is a relational database you don't need to.  You can use your memberID column to relate the login table to the information table, this reduces whats called redundant data. 

 

You already have the password and the user name stored in one talbe, there is no sense storing it in another one too. All you need is to include a refference field (also called a relational field) in each table that can be used to match the records in one to the other.

 

Using your memberID PK from your member table and adding a memberID field to your memberdetails table will let to match the details to the member according to each members unique memberID. This saves storing the username and password in multiple tables.

[ckerr27]

Thanks I understand, I only have the password stored in the members table, the other table is attached.

 

I am trying to use code to allow the logged in member to update their personal details (only their own) I have started with this code:

 

form:

 

<?php

echo $_SESSION['myusername'];

?>

<br><br>

<form action="updated.php" method="post">

Firstname: <input type="text" name="firstname" /><br><br>

Surname : <input type="text" name="surname" /><br><br>

Date Birth: <input type="text" name="dob" /><br><br>

Total Wins: <input type="text" name="wins" />

Total Loses: <input type="text" name="loses" /><br><br>

Email Add: <input type="text" name="email" /><br><br>

Country : <input type="text" name="born" /><br><br>

Other Info: <input type="text" name="other" /><br><br>

<input type="submit" name="Submit" value="Update" align="right"></td>

</form>

 

updated.php

 

<div id="body">

<?php

echo $_SESSION['myusername'];

?>

<br><br>

<?php

mysql_connect ("localhost","root","")

or die("Cannot connect to Database");

mysql_select_db ("test");

 

$sql="UPDATE memberdetails WHERE username=['myusername'] (firstname, surname, dob, totalwins, totalloses, email, country, info)

VALUES(

'{$_POST['firstname']}','{$_POST['surname']}','{$_POST['dob']}','{$_POST['wins']}','{$_POST['loses']}','{$_POST['email']}','{$_POST['born']}','{$_POST['other']}'

)";

 

if (!mysql_query($sql))

  {

die('Error: ' . mysql_error());

  }

echo "Details Updated";

 

?>

 

I know this is similar to the previous code, Its the WHERE that i cannot get to work, should the WHERE be placed into the code to ensure the record being updated belongs to which ever user is logged in?

[Muddy_Funster]

UPDATE syntax is very different to INSERT syntax.  and yes, you MUST use a WHERE in an update, or it will update every value in that field.

 

INSERT :

INSERT INTO tableName (field1, field2, field3, field4) VALUES ('value1', 'value2', 'value3', 'value4')

UPDATE :

UPDATE tableName SET field1='value1', field2='value2', field3='value3', field4='value4' WHERE (condition=met)

[ckerr27]

Do you mean something like this?I have attached the error i am getting from this code:

 

$sql="UPDATE memberdetails SET firstname='{$_POST['firstname']}', surname='{$_POST['surname']}', dob='{$_POST['dob']}', totalwins='{$_POST['wins']}', totalloses='{$_POST['loses']}', email='{$_POST['email']}', country='{$_POST['born']}', info='{$_POST['other']} WHERE username=$_SESSION['myusername'];}

[wigwambam]

End of line should be:

 

$sql="UPDATE memberdetails SET firstname='{$_POST['firstname']}', surname='{$_POST['surname']}', dob='{$_POST['dob']}', totalwins='{$_POST['wins']}', totalloses='{$_POST['loses']}', email='{$_POST['email']}', country='{$_POST['born']}', info='{$_POST['other']} WHERE username=$_SESSION['myusername']";