MYSQL Row not adding!

[Kuraden]

Ok, I'm working on a php news script and I added a table to my mysql database for users to access the script with this query:

CREATE TABLE login (
id int(4) NOT NULL default '0',
group varchar(50) NOT NULL default '',
user varchar(50) NOT NULL default '',
pass varchar(50) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

And I'm trying to add a row for a new user (for a test) with this script:

<?
if($submit)
{
include("config.php");
  mysql_connect("$dbhost", "$dbuser", "$dbpass");
  mysql_select_db("$dbname");
  $result=MYSQL_QUERY("INSERT INTO `login` (`id`, `group`, `user`, `pass`) VALUES ('NULL', 'admin', '$user', '$pass')");
  print "<p>This file has the Database";
}
else
{
?>
<form action='adduser.php' method='POST'>
Username: <input type=text name='user' VALUE='sdffd' size=60><BR>
Password: <input type=text name='pass' VALUE='dsfsdfsd' size=60><BR>
<input type=submit name=submit value='Add Administrator'></form>
<? } ?>

But it's not adding, even thought the script says it does. Can someone help me out here? 😕 😕

[weekender]

k, as far as i can see the table and field names in the INSERT query don't need to be in quotemarks.

also, i think because you are leaving php to print the form, the form will print regardless of the if statement. Put the form code inside the php <? ?> tags, using echo or print to output the html.

not altogether sure on that one, but it may be a start.

[Kuraden]

Nope that didn't work. I've been having problems with this for a long time 🙁

[weekender]

i think then that it's because you're trying to set id to NULL. You may be trying to set it to null to stop it being '' (literally, nothing), but i think mysql is treating the string 'NULL' as a null value, so the row is invalid.

try sending the sql to set id='nothing' or id='foobar' or something.

why do people use foobar, and what does it mean?

[dancahill]

Try this:

<?
if($submit)
{
include("config.php");
mysql_connect("$dbhost", "$dbuser", "$dbpass");
mysql_select_db("$dbname");
$result=MYSQL_QUERY("INSERT INTO login (id, group, user, pass) VALUES ('0', 'admin', '$user', '$pass')");
print "<p>This file has the Database";
}
else
{
?>

I've taken the ' ' out of the field names - don't think you need this. Also put '0' in instead of NULL..... Actually I think I've worked out the problem - in your SQL CREATE statement, you have ID set to NOT NULL & then you're trying to insert a null value - the DBMS won't let you do that so the statement will fail especially as it is the primary key..

Change your table to:

CREATE TABLE login (
id int(4) NOT NULL auto_increment default '0',
group varchar(50) NOT NULL default '',
user varchar(50) NOT NULL default '',
pass varchar(50) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

Then when you pass the '0' to ID it will assign the next number. Then use the amended query statement I've put at the top.

This should work.

Dan.

[weekender]

Originally posted by dancahill

I've taken the ' ' out of the field names - don't think you need this. Also put '0' in instead of NULL..... Actually I think I've worked out the problem - in your SQL CREATE statement, you have ID set to NOT NULL & then you're trying to insert a null value - the DBMS won't let you do that so the statement will fail especially as it is the primary key..

isn't that what i said?

[dancahill]

Yeah I think you did sorry didn't read it properly still maybe the auto_increment bit will help as well??

[Kuraden]

Nope the auto_increment didn't help too much 🙁

[weekender]

ok, start another attempt from scratch. have the form in one page, and have another page that posts the query. in fact, have a third page that lists all the fields in the table.

if the problem, you must find - start from scratch you must!

what do you think dan?

[dancahill]

Yeah I'm unclear how you are accessing the variables used in the SQL statement.

Another thing, it's always also a good idea to echo the SQL in the code if something is not working then you can see what the PHP script is actually passing to the DBMS.

In fact do that first and then post up what you get.

Dan

[weekender]

not sure if this has been realised yet, but clicking a submit button doesn't actually set a $submit variable in php... you would need a hidden field in the html code to trigger this.

i think what's happening is the form is appearing, and being submitted, but as the $submit variable is not defined then the script is bypassing the first half of the script, which is responsible for updating the database.

good to see there are some people alive on phpbuilder tonight tho!!!

[dancahill]

Try this:

Page 1:

<HTML>
<HEAD>
</HEAD>
<TITLE>
</TITLE>
<BODY>
<form action='adduser.php' method='POST'>
Username: <input type=text name='user' VALUE='sdffd' size=60><BR>
Password: <input type=text name='pass' VALUE='dsfsdfsd' size=60><BR>
<input type="submit" name="submit" value="Add Administrator"></form>
</BODY>
</HTML>

PAGE ADDUSER.PHP

<?

include("config.php");

$user = $HTTP_POST_VARS["user"];
$pass = $HTTP_POST_VARS["pass"];

//check you are getting the variables through

echo $user;
echo $pass;

mysql_connect("$dbhost", "$dbuser", "$dbpass");
mysql_select_db("$dbname");

$result=MYSQL_QUERY("INSERT INTO login (id, group, user, pass) VALUES ('0', 'admin', '$user', '$pass')");
print "<p>This file has the Database";

//check SQL
echo $result;
?>

If this doesn't work then I'm afraid I'm giving up.

Dan

[Kuraden]

🙁 That script didn't work..