Mysql username issue.

[pngtest]

I am creating a set of pages to establish connection to mysql.

 

i create a page called mysqlcfg.php with this code

<?php
$server = 'localhost'
$db_user = 'admin'
$db_pass = 'admin'
$database = 'test'
$table = 'users'
?>

and i have a log in script such as

<?php 

include("config.php"); 

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());

// check if the username is taken
$check = "select id from $table where username = '".$_POST['username']."';"; 
$qry = mysql_query($check) or die ("Could not match data because ".mysql_error());
$num_rows = mysql_num_rows($qry); 
if ($num_rows != 0) { 
echo "Sorry, there the username $username is already taken.<br>";
echo "<a href=register.html>Try again</a>";
exit; 
} else {

// insert the data
$insert = mysql_query("insert into $table values ('NULL', '".$_POST['username']."',
'".$_POST['password']."')")
or die("Could not insert data because ".mysql_error());

// print a success message
echo "Your user account has been created!<br>"; 
echo "Now you can <a href=login.html>log in</a>"; 
}

?>

 

but i get an error saying that the username when some one attempts to log in does not meet the construction set in my version of Mysql or something to that nature... don't remember and a different computer. but is there anything wrong with the code or is it the way i setup the mysql database.

[darkfreaks]

please post the exact error so we can help you

 

also putting POST variables in MYSQL is bad practice.

 

you should put POST variables inside a variable then put it in MYSQL

 

like

 

$variable= $_POST[variable];

mysql_query"(INSERT  INTO table ( values here) VALUES ($variable)");

 

 

-thanks

[pngtest]

thanks as soon as i get the error i will let you know.

 

[hitman6003]

you should put POST variables inside a variable then put it in MYSQL

 

like

 

$variable= $_POST[variable];

mysql_query"(INSERT  INTO table ( values here) VALUES ($variable)");

 

Why would you do that?  Assigning it to a intermediate variable, like you have above, is no different than using the $_POST array.  It doesn't matter if you address $_POST directly or assign it to an intermediate variable, use mysql_real_escape_string to prevent SQL injection...

 

$result = mysql_unbuffered_query("INSERT INTO table (column_name) VALUES ('" . mysql_real_escape_string($_POST['value']) . "')") or die(mysql_error());

 

http://www.php.net/mysql_real_escape_string

[pngtest]

so if i get this right i would use the way you are talking instead of the $check code that i am using. would that produce that same results or would i have to change the $qry line to get the same information.

[hitman6003]

regardless of what query you are issuing, anytime you use user input (i.e. the $_POST array) in a query, you should sanitize the variables using mysql_real_escape_string.

[pngtest]

thanks

got it

will try

[pngtest]

Hey thanks what you advised helped but i get the error

 

could not insert data because column count does not match value count at row 1

 

and when i try to create a new table through php i get the error

 

could not create table because you have an error sql syntax check the manual that corresponds to your mysql server version for the right syntax to use near 'username(username) at line 1

 

 

[lemmin]

That error would mean that your table name that is in $table doesn't have exactly three columns.

 

This is probably just a mistake in your post, but you said you named the config file 'mysqlcfg.php', but you are including 'config.php'. Make sure you aren't mixing the two up.

[pngtest]

yes that was a typo sorry.... but how would i fix that error i set up the table with this script if that helps

 

$create = create table $table (
id smallint(5) NOT NULL auto_increment,
username varchar(30) NOT NULL,
password varchar(32)NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY username (username)
);";

[lemmin]

If that is the same table I would expect a different error. You declare all fields as NOT NULL and you try to put NULL in as a value. Try changing it to this:

$insert = mysql_query("INSERT INTO $table (username, password) VALUES ('".$_POST['username']."', '".$_POST['password']."')")