Help with PHP ... Anyone?`

NackelShipley · Post by **NackelShipley** » Fri 09 Jul, 2004 8:27 pm

Hey everyone out there, I need some help with my PHP, MYSQL database "login/signup script". Well here's the script and the error I keep getting when I test the signup.

Error:
Could not match data because Unknown column 'id' in 'field list'

(I assume it has something to do with my MYSQL database.)

Here's the whole script I'm using:

Page: config.php

Code: Select all

<?php
$server = "localhost";	// server to connect to.
$database = "";	// the name of the database.
$db_user = "";	// mysql username to access the database with.
$db_pass = "";	// mysql password to access the database with.
$table = "users";		// the table that this script will set up and use.
?>

Page: create.php

Code: Select all

<?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());

// create table on database
$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)
);";

mysql_query($create)
or die ("Could not create tables because ".mysql_error());
echo "Complete.";
?>

Page: login.html

Code: Select all

<html><head>
<title>User Registration</title>
</head><body>

<form action="login.php" method="post">
Username: <input type="text" name="username" size="20"><br>
Password: <input type="password" name="password" size="20"><br>
<input type="submit" value="Log In">
</form>

</body></html>

Page: login.php

Code: Select all

<?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());

$match = "select id from $table where username = '".$_POST['username']."'
and password = '".$_POST['password']."';"; 

$qry = mysql_query($match)
or die ("Could not match data because ".mysql_error());
$num_rows = mysql_num_rows($qry); 

if ($num_rows <= 0) { 
echo "Sorry, there is no username $username with the specified password.<br>";
echo "<a href=login.html>Try again</a>";
exit; 
} else {

setcookie("loggedin", "TRUE", time()+(3600 * 24));
setcookie("mysite_username", "$username");
echo "You are now logged in!<br>"; 
echo "Continue to the <a href=members.php>members</a> section.";
}
?>

Page: register.html

Code: Select all

<html><head>
<title>User Registration</title>
</head><body>

<form action="register.php" method="post">
Pick a Username: <input type="text" name="username" size="20"><br>
Pick a Password: <input type="password" name="password" size="20"><br>
<input type="submit" value="Sign Up">
</form>

</body></html>

Page: register.php

Code: Select all

<?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 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 To Play TENUR</a>"; 
}

?>

If anyone can help me and tell me why I am getting this message, I would appreciate it greatly.

Post by **Pikachu3828** » Fri 09 Jul, 2004 8:46 pm

Could you please edit your post and put the code inside the

Code: Select all

[code]

[/code]BBCode tags. It makes for easier readability. It sounds from the error like the field in the error stated does not exist in the database and you need to create it

NackelShipley · Post by **NackelShipley** » Fri 09 Jul, 2004 8:56 pm

I put in the tags sorry 'bout that...And yeah I think I tried that...in my database I made called 'users' (like the above code states) I put 2 tables 'username' and 'password' is this not what I am supposed to do?

Post by **Pikachu3828** » Fri 09 Jul, 2004 8:59 pm

NackelShipley wrote:I put in the tags sorry 'bout that...And yeah I think I tried that...in my database I made called 'users' (like the above code states) I put 2 tables 'username' and 'password' is this not what I am supposed to do?

You've made two columns, username and password? You need to creatae another one and call it "id", I would presume you'd need to make it auto increment

NackelShipley · Post by **NackelShipley** » Fri 09 Jul, 2004 10:07 pm

Ah, that works thank you for your help.

NackelShipley · Post by **NackelShipley** » Sat 10 Jul, 2004 9:22 pm

Sorry about this everyone I'm pretty new to PHP and SQL, anyways I have another problem with my login script.

I used this code:

Code: Select all

setcookie("loggedin", "TRUE", time()+(3600 * 24));
setcookie("mysite_username", "$username");
echo "You are now logged in!<br>"; 
echo "Continue to the <a href=members.php>members</a> section.";

At the end of a file I called login.php. So far the script works great. The problem comes in when I click the link to take me to the members page.

I am using this code on the members.php page:

Code: Select all

<font face="verdana" size="1">
<?php if (!isset($_COOKIE['loggedin'])) die("You are not logged in!");
$mysite_username = $HTTP_COOKIE_VARS["mysite_username"]; 
echo "you are logged in as $mysite_username."; ?>

Now if I'm not mistaken the page login.php should have set a login cookie to my username. And the script on the page members.php should locate that cookie if I logged in with the correct informatioin (which I did) and should display a message saying 'you are logged in as (username here).'
but even though I log in correctly with valid informationg it still brings up the message "You are not logged in!" It's like it's either not finding the login cookie or it's not setting one...Can someone help me?