Count results in left join?

[Kristoff1875]

Hi guys, trying to make the move to MySQLi and some of it is baffling me. Quick question as my query is currently saying there's 1 result even if there isn't one...

 

How do I go about finding the number of rows returned for this:

$sql = <<<SQL
    SELECT u.*, t.*
    FROM Users u
	LEFT JOIN Transactions t USING (UserID) 
	WHERE UserID = $UserID
	ORDER BY Date DESC
	LIMIT 5
SQL;

Thanks in advanced!

[Psycho]

" . . . my query is currently saying there's 1 result even if there isn't one..."

 

Where is the code that is reporting that there was 1 result?

[Kristoff1875]

$row_cnt = mysqli_num_rows($result);
echo $row_cnt;
while($row = $result->fetch_array()) { 
}

It's echoing number 1 and also showing 1 row with blank values and 01/01/1970 as the date.

[Psycho]

 

$row_cnt = mysqli_num_rows($result);
echo $row_cnt;
while($row = $result->fetch_array()) { 
}

It's echoing number 1 and also showing 1 row with blank values and 01/01/1970 as the date.

 

 

So, it is echoing 1, meaning there was one record in the result. Then the while loop, apparently, is displaying the values of one row where most values are null and a data value of '01/01/1970'. I'm not seeing the problem. The fact that some of the values in the record that is returned are NULL doesn't mean there wasn't a record in the result. You must be leaving out some important information here.

[Kristoff1875]

There are no rows in the table at all, so it can't return anything, yet it is saying the result is 1.

Edited January 19, 2014 by Kristoff1875

[Barand]

There are no rows in the table at all

Which table? No transactions or no users?

[Kristoff1875]

There's no transactions currently, but there are users, but there can't be any transactions by the logged in user as there are no transactions at all. Sorry, when I said no rows in the table I meant no rows related to the query, although there are no rows in transactions.

Edited January 19, 2014 by Kristoff1875

[Kristoff1875]

Ok, I think I understand why it's returning 1 row, because it's seeing that the user exists, but not any transactions. So the result does exist, but the transactions don't... Gazumped now though as I don't know how to check for results that match both.

[Barand]

A LEFT JOIN B ---> Returns all records in A with data from B where there is a match, otherwise null values from B

A INNER JOIN B ---> Ony returns rows where there is matching data in both A and B

[jazzman1]

By the way, if you use COUNT() function with a column name like count(colname) as a single argument, mysql counts the rows only with non-null values and returns 0 (zero) if the value of the column is null. However, if you use count(*) mysql returns the total number of rows in the result set.

Edited January 19, 2014 by jazzman1

[Kristoff1875]

A LEFT JOIN B ---> Returns all records in A with data from B where there is a match, otherwise null values from B

A INNER JOIN B ---> Ony returns rows where there is matching data in both A and B

Perfect thank you, you're a star. Amazing how something so simple can throw you off in such a way!