Produce zero rows when the password is incorrect

When an incorrect password is used in this query, it still produces a row, but all the columns are null. What is the best way to overcome this so no row is returned at all? I think I screwed up one of my joins.

Thanks!

SELECT 
	m.memberID, 
	m.firstName, 
	m.lastName, 
	count(distinct t.testimonialID) as testimonials,
	count(distinct. f.dateAdded) as favorites,
	b.brandName,
	b.brandWebsite,
FROM 
	members m
LEFT JOIN
	brands b
ON
	m.brandID = b.brandID
LEFT JOIN 
	testimonials t
ON 
	m.memberID = t.memberID
LEFT JOIN
	favorites f
ON
	t.memberID = f.memberID
WHERE
	email = 'validUser@example.com'
	and password = 'incorrectPassword'

Thanks for your help!

if email and password are in the members table, then it’s not a problem with the joins, it’s a problem either with your GROUP BY clause or your php handler (or whatever language you’re using)

But I’m not using a GROUP BY clause. This is what I’m using in my PHP code. It’s failing because a row is indeed being returned, even though all the columns are null:

if (mysql_num_rows($result) > 0) {

  // Code goes here
}

I see a few things that most likely have absolutely nothing to do with the problem, but are not what I would do.

The LEFT JOIN testimonials ON “skips” the previous table brands and uses the one before that, members

You use alias names that are also table names.

count(distinct t.testimonialID) as testimonials,
count(distinct. f.dateAdded) as favorites,

The WHERE does not qualify which table(s) those fields are in

email = 'validUser@example.com'
and password = 'incorrectPassword'

Good catch Mittineague. I fixed the alias names and specified what table the columns are in the WHERE clause. However, the LEFT JOIN testimonials ON is like that because the brands table does not have a memberID column.

Thanks!

You also need to look at your PHP code as the mysql_* extension that you’ve used was removed from versio 7 of php

maybe i was a bit too subtle

when you use aggregate functions like COUNT() in your SELECT clause, along with non-aggregate expressions like memberID and brandName, you must use a GROUP BY clause

2 Likes

Problem solved. Bless you r937!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.