error code on login-check

[alan6566]

When I log in on my web-site it takes me to a php login-check page

 

This is the error code that I am getting;

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in D:\xampp\htdocs\login-check.php on line 26

 

This is the php code that i am using;

 

<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="deliverpizza"; // Database name 
$tbl_name="customer, admin, staff"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from form 
$myusername=$_POST['myusername']; 
$mypassword=$_POST['mypassword'];

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
session_register("myusername");
session_register("mypassword"); 
header("location:login_privelage.php");
}
else {

}
?>

[AyKay47]

The error indicates that the query is returning a boolean FALSE due to an error in the SQL.

 

1. check for the $_POST values being set before using them using isset

 

2. Add some debugging in your script, echo the SQL and mysql_error upon query failure.

 

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql) or die($sql . "<br />" . mysql_error());

 

3. session_register() is deprecated, use the $_SESSION superglobal array to set session values instead.

[blacknight]

its errorintg because $result just says weather the query worked or not

run mysql_fetch_assoc( then count that

[kojote]

I see you are trying to find out if the user already exists in 3 different tables, but the way you handle this is a bit incorrect. This is what the SQL query would look like generated by your php:

 

SELECT * FROM customer, admin, staff WHERE username='$myusername' and password='$mypassword'";

 

But you are handling this query in a wrong way as you would now get the cartesian result (for every row in table1 - show every row in table2). You should instead union the tables together like this:

 

SELECT * FROM  customer UNION ALL SELECT * FROM  admin UNION ALL SELECT * FROM  staff WHERE username='$myusername' and password='$mypassword'";

 

And as said, keep your information in a global $_SESSION array.

 

Also, not that this is faulty perse, but there is no need to use double quotes around your variables in the following lines of code, as those variables are already declared as strings.

 

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

[AyKay47]

its errorintg because $result just says weather the query worked or not

run mysql_fetch_assoc( then count that

 

What? This has nothing to do with the triggered error.

[kojote]

its errorintg because $result just says weather the query worked or not

run mysql_fetch_assoc( then count that

 

No, mysql_query() returns the resulting table from the SELECT query, not a boolean. Those parts of the code are correct.

[AyKay47]

its errorintg because $result just says weather the query worked or not

run mysql_fetch_assoc( then count that

 

No, mysql_query() returns the resulting table from the SELECT query, not a boolean. Those parts of the code are correct.

 

mysql_query returns a boolean FALSE upon error, and a mysql result resource otherwise.

[kojote]

its errorintg because $result just says weather the query worked or not

run mysql_fetch_assoc( then count that

 

No, mysql_query() returns the resulting table from the SELECT query, not a boolean. Those parts of the code are correct.

 

mysql_query returns a boolean FALSE upon error, and a mysql result resource otherwise.

 

Pardon, what I meant to say was that using that function on a correct SELECT query, it returns the result of the query, and not just a "did it work?" boolean. But I forgot that in this error, the mysql_num_rows() function has no resource as parameter, which means the result of the mysql_query() was false anyway and the error lies in the SQL query. I think my edit of the query should fix this.

[Pikachu2000]

That code is obviously from phpeasystep.com. Their code "examples" phpeasystep.com are about 10 years out of date, and shouldn't be used as an actual learning resource.

[alan6566]

Thanks for the help so far,

 

I still seem to be struggling to get this to work (fairly new to PHP),

 

Pikachu2000, could you recommend a more suitable learning resource?

[alan6566]

I now get the error:

 

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, string given in D:\xampp\htdocs\login-check.php on line 26

 

and this is the code:

 

<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="deliverpizza"; // Database name 
$tbl_name="customer, admin, staff"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from form 
$myusername=$_POST['myusername']; 
$mypassword=$_POST['mypassword'];

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM  customer UNION ALL SELECT * FROM  admin UNION ALL SELECT * FROM  staff WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_fetch_assoc($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
session_register("myusername");
session_register("mypassword"); 
header("location:login_privelage.php");
}
else {

}
?>

[AyKay47]

Refer to my first reply, you have not changed anything according to my recommendations.

You need to debug your query and use $_SESSION instead of session_register()

[alan6566]

aykay47: i have changed the session to the one you said. Thanks

 

I am now getting a diffrent error:

 

SELECT * FROM customer UNION ALL SELECT * FROM admin UNION ALL SELECT * FROM staff WHERE username='alan6566@hotmail.co.uk' and password='test'

The used SELECT statements have a different number of columns

 

Below is the code that i am using now:

 

<?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="deliverpizza"; // Database name 
$tbl_name="customer, admin, staff"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from form 
$myusername=$_POST['myusername']; 
$mypassword=$_POST['mypassword'];

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM  customer UNION ALL SELECT * FROM  admin UNION ALL SELECT * FROM  staff WHERE username='$myusername' and password='$mypassword'";
//$result=mysql_query($sql);

//$sql="SELECT * FROM $tbl_name WHERE userName='$myusername' and password='$mypassword'";
$result=mysql_query($sql) or die($sql . "<br />" . mysql_error());

// Mysql_num_row is counting table row
$count=mysql_fetch_assoc($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
$_SESSION['myusername'] = $myusername;
$_SESSION['mypassword'] = $mypassword;
header("location:login_privelage.php");
}
else {

}
?>

 

and here are the screen shots of the three different tables that i am using to log into the database. I have attached them.

 

[kojote]

Oh, I assume that error is because your 3 tables have a different numbers of columns and using UNION ALL on them would first make a big table with all three of those tables in, but because "SELECT * FROM" returns all columns, and there's a different number of columns, it errors. Change it to this instead

 

$sql="SELECT userName  FROM  customer UNION ALL SELECT userName  FROM admin UNION ALL SELECT userName FROM staff WHERE userName ='$myusername' and password='$mypassword;'";

 

It's recommended that you always try out your SQL by, in your database in phpmyadmin, selecting SQL and pasting your query there (with variables changed to test values), then running and see what it returns. In this case you'd have to try something like

SELECT userName FROM  customer UNION ALL SELECT userName  FROM admin UNION ALL SELECT userName FROM staff WHERE userName ='admin' and password='test';

 

You still have a variable $tablename declared in the beginning btw, but now you are using those names directly in your query so you might as well remove that. =P

[alan6566]

Thanks kojote for all your help so far.

 

I have done what you said and i have gone into phpmyadmin and test the sql.

 

when I insert the code into my sql i get all the user names from all the tables

 

Insted of the one i am wanting.

[PFMaBiSmAd]

You shouldn't have three different tables for your users. You should have one table for your users. If you need to distinguish between the different types of users and what they can access once logged in, you need to use an Access Control List (ACL) system.

[PFMaBiSmAd]

To get your UNION query to work, you need to add a WHERE clause to each part -

SELECT userName FROM  customer WHERE userName ='admin' and password='test'
UNION ALL
SELECT userName  FROM admin WHERE userName ='admin' and password='test'
UNION ALL
SELECT userName FROM staff WHERE userName ='admin' and password='test';

[PFMaBiSmAd]

You are also storing the passwords as plain text. At a minimum you should be hashing the passwords so that if someone gains access to or displays all the records in your table(s), they don't automatically know all the actual passwords.

[alan6566]

yahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 

 

Thank you all for your help.

 

i will say a very big thank you to PFMaBiSmAd. the bit of code that you gave me work.