A small change to $stmt made login fail, any ideas why?

General PHP Help
#1

I have a PDO login system for online classes. Works fine! (normally)

The students register with an email and a password, and the login page asks for both.

This statement checks if the email is in the database:
1.
$stmt = $pdo->prepare('SELECT * FROM allstudents20EAP WHERE email = :email');

Today, I thought, "Actually, you only need to SELECT 1, so I changed this to:
2.
$stmt = $pdo->prepare('SELECT 1 FROM allstudents20EAP WHERE email = :email');

After that, the login failed every time, with the login error “Incorrect password”

On a homework page, I also have this to check if the student number is in the database before the homework is uploaded:
3.
$stmt = $pdo->prepare('SELECT 1 FROM allstudents20BE WHERE studentnr = ?');

I have no column header 1 in any table, and this statement 3. works fine.

Any ideas why this statement 2. causes the login to fail??

I changed 1 back * to again and it works fine again.

#2

Well, normally with a login in script you need to pull in the username (or email address) and password to check against what the user inputted. Statement 2 doesn’t do that and that is the reason it’s failing.

#3

Thanks for your reply!

The password is also there.

I’m pretty sure that it was you, quite a while ago, here, who advised me to put 1 when I only wanted 1 result.

Why does it work in 3., but not in 2.???

First, the PHP checks if the email is in the database. If not, go back to the login.

If the email is in the database, go forward and check the password given in the login form.

$password = $_POST['password'];

Like I said, using SELECT * no problem. Use SELECT 1 problem.

#4

That’s not what SELECT 1 does. SELECT 1 literally selects the value 1. You want to use LIMIT 1, like so:

SELECT * FROM allstudents20EAP WHERE email = :email LIMIT 1

You could also use:

SELECT EXISTS(SELECT * FROM allstudents20EAP WHERE email = :email)

but you’d need to change your surrounding code to handle the different result.

1 Like
#5

Actually no. The DB should already be enforcing a unique email, therefore there can only be one result. The LIMIT is not needed whatsoever.

OP, do not SELECT *, Specify the columns by name that you want.

1 Like
#6

Thanks for the advice!

I was just wondering why statement 2. failed whereas 3. works!

I will try with the name of the email column, cunningly named: email

This statement gets used a lot when uploading homework or classwork, to make sure the student number is in the db. I’ll change that to the column name too!

  1. never fails! How is that possible, when the email one fails??
#7

You have a logic problem. You should not be checking if the email exists first. You need to set a UNIQUE constraint on the email field and then attempt the insert and catch the DB error if there is one.

1 Like
#8

Well, I can’t really write PHP much, I just borrow ideas from others and adapt for my purposes.
This is what I do at the moment:

//get the user with email
		$stmt = $pdo->prepare('SELECT * FROM allstudents20EAP WHERE email = :email');
		
		try{
			$stmt->execute(['email' => $email]);
			//echo 'email is ' . $email. '<br>';

			//check if email in this instance exists
			if($stmt->rowCount() > 0){
				//get the row
				$user = $stmt->fetch();
				//echo 'encrypted password is ' . $user['password'] . '<br>';

				//validate the password with $user password
				if(password_verify($password, $user['password'])){
					//action after a successful login
					//for now just message a successful login
					$_SESSION['success'] = 'User verification successful';

You advised me to put SELECT email where I have SELECT *

But that results in the same problem as putting SELECT 1: I get a message “incorrect password”

When I put SELECT * my login works fine and I see the page I expect.

Any ideas what is wrong??

This query in phpMyAdmin gives me the correct answers:

SELECT studentnr, password FROM allstudents20EAP WHERE email = ‘[email protected]’

I use pretty much the latest version of PHP on my laptop. My webpage has a slightly older version.

This little bit in register.php prevents more than 1 of the same email:

//check if the email is already taken
			$stmt = $pdo->prepare('SELECT * FROM allstudents20EAP WHERE email = :email');
			$stmt->execute(['email' => $email]);

			if($stmt->rowCount() > 0){							
				//display error
				$_SESSION['error'] = '邮箱地址已注册 Email already taken';
				header('location: register_form.php');
				exit();
			}
#9

The SELECT clause determines what fields you get back from the table. SELECT * means “get every field in the table”.

SELECT 1 won’t actually get any fields from the table; it will just return a row with the number 1 in it.

The code you’ve shown depends on at least the fields email and password, so if you’re being specific you need to have at least those two fields:

SELECT email, password FROM allstudents20EAP WHERE email = :email:

You could just leave it as * and not have any problems.

Regarding your only wanting to fetch one record - if your emails are unique you’ll only fetch one record anyway, so don’t worry about it.

1 Like
#10

Thanks for your reply.

I’m just curious why this works with a 1 in there. But I don’t understand PHP very well!
It has worked for years. Never any problems:

include '../../includes/studentdbWriteto.inc.php';
		//check if the studentnr exists in this course names and numbers list
		$stmt = $pdo->prepare('SELECT 1 FROM allstudents20EAP WHERE studentnr = ?');
		try{
			$stmt->execute([$studentnr]);
			//echo 'row count is ' . $stmt->rowCount();
				if($stmt->rowCount() == 0){
					//echo 'this student is not in the database';
				$_SESSION['error'] = 'This student number is not in the students\' database for this class: <b>' . $studentnr . '</b>. Please try again!';
				//echo $_SESSION['error'];
				header('Location: /20EAPcw/20EAPwW1.html.php');
				exit();
				// if the student number exists, save the answers			
			}
		}

What does this SELECT return? Just a row count?

It must normally find the student number and then proceed.

Is it:
look for a row with this student number
if we find 0 row with this student number go back to the webpage
if we find 1 row with this student number proceed

#11

If you ran it in PHPMyAdmin or on the command line and the student number exists, you’d probably get something like:

+---+
| 1 |
+---+
| 1 |
+---+

There’s one column whose name is 1; and one row, whose content is 1. PDO then gives you a rowCount of 1.

If there was no student with that student number, you’d get something like Empty set (0.00 sec) on the command line. PDO then gives you a rowCount of 0.

If you somehow had more than one student with the same student number, you’d get something like:

+---+
| 1 |
+---+
| 1 |
| 1 |
+---+

PDO would give you a rowCount of 2.

The code in your reply works because you don’t care what’s in the result; you’re just using the row count. Your login code actually needs what’s in the result, so just selecting 1 doesn’t work.

#12

Weird, I ran

SELECT 1 FROM allstudents20EAP WHERE studentnr = 1234567890

in phpMyAdmin. I get nothing. (1234567890 is the number I give myself, for testing)

#13

What do you get if you run

SELECT * FROM `allstudents20EAP` WHERE studentnr = 1234567890
#14

I get exactly what I should get, my data, because 1234567890 is my “student number” for testing.

id, studentnr, name, email, password, max_attendance, attendance, has_been_inc, logon_time

#15

Pedroski, you are not listening to these expert programmers. I will recap for you.

In a SELECT query, you are pulling out data. If you SELECT *, you get all the data for that one query.
If you SELECT 1, you are telling it to return “1” which can be the same as TRUE.
It is NOT data from your database, but, a constant.
If you get NOTHING back from the query, meaning the WHERE failed to match something, then you get a FALSE, NULL, NOTHING back and therefore you can not display it.

The problem here is that there is never any reason to SELECT 1 !!! You are asking a mute question!

1 Like
#16

That’s pretty weird. My first guess would be a phpmyadmin config weirdness. What happens if you try them both directly through the command line?

#17

Skawid, he is NOT querying anything. He is setting the results to “1” or TRUE not really looking for data.
This is just a waste of time testing an invalid query !

#18

The above isn’t an invalid query; if the id exists (which it evidently does, because SELECT * works) he should be getting the value 1.

#19

This SELECT 1 idea was originally suggested to me here by Strider64.

It has worked for me so long, I can’t think why you would say there is something wrong with it.

The fact that I don’t understand how it works is just my ignorance.

I must be getting something back from the query, because, as long as the student number is in the table, my PHP proceeds. That means WHERE finds the number, even if

SELECT 1 FROM allstudents20EAP WHERE studentnr = 1234567890

in phpMyAdmin returns nothing.

#20

Well, Pedroski, we understand. We are trying to explain why this happens. Let’s try a different way…

When you run a query, it returns either nothing or something. Simple so far.
You check the results by either counting the rows it found (0 meaning none found) and if some do something with the results. You can count the results using MySQL’s functions such as num_rows(). Or if you use PDO which is the suggested way to go, you can just check if the results is an object or not…

Normally, you would do it this way. You would not select TRUE or 1 to see if you got a result. Perhaps Strider was thinking of something else when he suggested it. He is one of our best programmers here!

So, using your code in MySQLi, you would do it this way: ( Loosely… )

$query = "SELECT * FROM allstudents20EAP WHERE studentnr = 1234567890";
$results = $mysqli->query($query);
if (mysqli_num_rows($result)==0) {
    NO RESULTS throw out a message or whatever...
} else {
    Data Found, Do something...
}

Just off the top of my head… You check to see if you get results. If none, you handle it however is needed for your site. If you get something, you handle the data. This handles problems and handles data. The query runs live so if you get results, you have the data all ready to fetch and use…

Sponsor our Newsletter | Privacy Policy | Terms of Service