A Tad Bit Confuzed(understatement) and in need of help

DrunkCentaur

I'm trying to make a simple registration script for my website. I've made an html form, the person sticks writes their username, password, and e-mail address... and the page in turn sends it to a php script that sticks it into a table in mysql. Problem seems to be that it'll only let me insert one record... then won't let me insert anymore till the first one is deleted. Gives me an error message of

Duplicate entry '' for key 2

What ever that means...

Also, the one record that it does let me put in shows up blanks...

I'm guessing it's a problem with the db, but I have no idea what I'd have to change with it... so I was hoping somebody would have some clue as to what I should do. And also, heres the php script in you might notice a problem with that.

<?php

include("charts.php");

$connection=mysql_connect($host, $user, $password)
or die ("couldn't connect to server.");
$db = mysql_select_db("honor", $connection)
or die ("couldn't connect to database.");

$username = $http_post_vars['username'];
$password = $http_post_vars['password'];
$email = $http_post_vars['email'];

$sql = "INSERT INTO accounts (id, username, password, email) VALUES ('', '$username', '$password', '$email')";

mysql_query($sql, $connection) or die(mysql_error());

?>

Thank you

planetsim

Either change this

$sql = "INSERT INTO accounts (id, username, password, email) VALUES ('', '$username', '$password', '$email')";

$sql = "INSERT INTO accounts (id, username, password, email) VALUES ('NULL', '$username', '$password', '$email')";

Or Change the id in accounts to PRIMARY KEY, auto_increment and INT(11)

DrunkCentaur

Well considering the id is already primary key, auto incriment, with int(11) guess I'll do the prior.

crosses fingers

Thank you

DrunkCentaur

:-( Didn't work... cries

matt_4013

Hi, just leave out the id in your INSERT query altogether.
eg:

$sql = "INSERT INTO accounts (username, password, email) VALUES ('$username', '$password', '$email')";

DrunkCentaur

Greetings and Salutations,
And thank you as well... unfortunately this still hasn't solved my dilemma... I should probably mention what I've tried so far...

The script started out something to the effect of

<?php

include("charts.php");

$connection=mysql_connect($host, $user, $password)
or die ("couldn't connect to server.");
$db = mysql_select_db("honor", $connection)
or die ("couldn't connect to database.");

$sql = INSERT INTO accounts(username, password, e-mail) VALUES ('$post[username]', '$post[password]', '$_post[e-mail]')

if (mysql_query($sql, $connection)) {
echo "record added!";
} else {
echo "something went wrong";
}

?>

To begin with there was no id table at all... the main problem with that was a t-string error that was fixed by changing by putting it in quotations around the...

$sql = "INSERT stuff into some table or something"

Now I had an error that said

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting ']' in /usr/local/psa/home/vhosts/elf-honor.com/httpdocs/registration.php on line 10

which seemed to be fixed or transformed at least when I changed

$post[username] to '{$post['username']}'

That was the first time that it showed anything besides an error... it now said(as my script had told it to say when it didn't work)

something went wrong!

Then I took out the (username, password, e-mail) in

$sql = "INSERT INTO accounts(username, password, e-mail) VALUES ('{$post['username']}', '{$post['password']}', '{$_post['e-mail']}')";

And it worked once.... it let me put in the one empty insert while telling me "record added!" then gave me the error

Something went wrong

Then I read somewhere that if the php version was younger than 4.1.x I'd have to change the "$_post" to "$http_post_vars". Figuring that php 4 was older than 4.1.x I did that... still no help

So I went to another friend for help and he had me change

$sql = "INSERT INTO accounts VALUES ('{$http_post_vars['username']}', '{$http_post_vars['password']}', '{$http_post_vars['e-mail']}')";

$sql = "INSERT INTO accounts (username, password, e-mail) VALUES ('{$http_post_vars['username']}', '{$http_post_vars['password']}', '{$http_post_vars['e-mail']}')";

He also had me change

if (mysql_query($sql, $connection)) {
echo "record added!";
} else {
echo "something went wrong";
}

mysql_query($sql, $connection) or die(mysql_error();

which gave me a parse error of

"Parse error: parse error, unexpected ';' in /usr/local/psa/home/vhosts/elf-honor.com/httpdocs/registration.php on line 12"

Tried switching the post values to variables making the new code look like this

<?php

include("charts.php");

$connection=mysql_connect($host, $user, $password)
or die ("couldn't connect to server.");
$db = mysql_select_db("honor", $connection)
or die ("couldn't connect to database.");

$username = $http_post_vars['username'];
$password = $http_post_vars['password'];
$email = $http_post_vars['e-mail'];

$sql = "INSERT INTO accounts (username, password, e-mail) VALUES ('$username', '$password', '$email')";

mysql_query($sql, $connection) or die(mysql_error();

?>

"Parse error: parse error, unexpected ';' in /usr/local/psa/home/vhosts/elf-honor.com/httpdocs/registration.php on line 16"

which was fixed by adding an extra ')" at the end of the code

"You have an error in your SQL syntax near '-mail) VALUES ('', '', '')' at line 1"

So I took out the "-" in the e-mail of my accounts table... and in any code related to it.

Now I was back to it inserting one empty entry... but at least it was giving me an error for it...

Duplicate entry '' for key 1

so baffled I just stared at the damn thing forever and ever and ever until I added the id column in my accounts table as my primary key with int(11) and auto incriment starting at 1

I didn't put it in the php code at first... because I thought it would just automatically fill in the ones I didn't mention with the default(which it did-it was the only value that had anything in it for that one row that it let me create that was completely blank otherwise)

The error was now changed to

Duplicate entry '' for key 2

Then I decided to change the part of the code that said

$sql = "INSERT INTO accounts (username, password, e-mail) VALUES ('$username', '$password', '$email')";

$sql = "INSERT INTO accounts (id, username, password, e-mail) VALUES ('', '$username', '$password', '$email')";

And then after the advice by Planetsim, changed it to

$sql = "INSERT INTO accounts (id, username, password, e-mail) VALUES ('NULL', '$username', '$password', '$email')";

And now just for kicks followed matt's plan, and took out the id completely... nods

OhLordy

Is username a key as well?
Can you look at what you have in the database and check what did get put in for that first one?
Thanks
Rob

DrunkCentaur

Not exactly sure what you're asking for on the second part of your statement... but I'll try anyway... and no the username is not set to primary... but I set it to unique... because I don't want duplicates.

I've got the following

id, int(11) auto incriment, primary
username, varchar(25), not null
password, varchar(25), not null
email, varchar(25), not null

If I input from the form no matter what values I use(I used the values DrunkCentaur for the username, holygrail for password, and TheGryphon_Dai@Yahoo.com for the e-mail) then look at the table(I'm using phpmyadmin btw) I see the following

/////////////id username password email

Edit Delete 1

Then the second time I get the error until I delet the first one.

DrunkCentaur

Also I don't seem to be having any trouble adding entries using phpmyadmin.... unfortunately that kind of defeats the idea of having a user sign up via the form, nods

siwis

in phpmyadmin, do a structure dump for your table and post it here please.

siwis

Also, don't have your username field set to unique. Use your php to check for the existence of a duplicate username to prevent it from occuring.

Here is my suggestion for your table:

CREATE TABLE accounts (
id int(11) NOT NULL auto_increment,
username varchar(16) NOT NULL,
password varchar(16) NOT NULL,
email varchar(128) NOT NULL,
PRIMARY KEY (user_id),
UNIQUE user_id (user_id),
KEY user_id_2 (user_id)
);

Note, I've increeased the size of your email field to 128. Your original setting of 25 was way too short for an email address.

DrunkCentaur

I tried to redraw the table to your suggestions except I didn't understand the "KEY user_id_2 (user_id)". Either way the Structure dump is as follows

Database honor - table accounts running on localhost

phpMyAdmin MySQL-Dump

version 2.2.6

http://phpwizard.net/phpMyAdmin/

http://www.phpmyadmin.net/ (download page)

Host: localhost

Generation Time: Apr 22, 2003 at 07:49 PM

Server version: 3.23.55

PHP Version: 4.2.2

Database : `honor`

--------------------------------------------------------

Table structure for table `accounts`

CREATE TABLE accounts (
id int(11) NOT NULL auto_increment,
username varchar(16) NOT NULL default '',
password tinyint(16) NOT NULL default '0',
email tinyint(128) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;

DrunkCentaur

WOOHOO!!! I don't know what your suggestions did... but umm... it's different...

It's now excepting more than one entry... not filled with what I want... but with something...

/////////////id username password email

Edit Delete 1 0 0
Edit Delete 2 0 0
Edit Delete 3 0 0

0's are under the password and email collumns and the username is empty

siwis

Delete your table "accounts" and run the full sql I posted.

phpmyadmin will create the table for you.

As it happens, you've now applied intval field types to email and password which won't work for the type of data you will want to store!

DrunkCentaur

Database honor running on localhost
Error

SQL-query :

CREATE TABLE accounts (

id int(11) NOT NULL auto_increment,

username varchar(16) NOT NULL,

password varchar(16) NOT NULL,

email varchar(128) NOT NULL,

PRIMARY KEY (user_id),

UNIQUE user_id (user_id),

KEY user_id_2 (user_id)

)

MySQL said:

Key column 'user_id' doesn't exist in table

Back

Did I put it in the wrong place probably?

siwis

oops, sorry, my mistake

CREATE TABLE accounts (
id int(11) NOT NULL auto_increment,
username varchar(16) NOT NULL,
password varchar(16) NOT NULL,
email varchar(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE id (id),
KEY id_2 (id)
);

DrunkCentaur

sighs It's still all blanks(except for the id) but it's letting me insert as many as I want now, nods

siwis

OK, so now your database structure is correct, you now need to start doing some debugging of your code.

The first (and most obvious) thing to do is to actually check that your sql is indeed being constructed correctly.

Simple echo $sql; (after you've created it) should show whether this is working or not.

You should see the proper sql with the values from your form inserted into the relevant places. If those places are empty then you are trying to insert empty variable values. So you then need to debug why they are empty.

If you follow my regime (ie the way I do things), I simply echo everything out to screen and see where I draw a blank. Make sure (if Register_Globals = OFF) that your form method matches the fieldname associations you are calling, ie $HTTP_POST_VARS['fieldname'] for a POST form and $HTTP_GET_VARS['fieldname'] for a GET form.