Mysqli join query problems

kgp43
kgp43 used Ask the Experts™
on
Hi,

I have a problem with this query.
What I want is to get the Category Name based on Category ID, each in different tables.

This is what I made so far, but it does not work:

<?php
# Output last 5 used categories
$sql = "SELECT
	inquiries.category, categories.name, categories.id
FROM
	inquiries, categories
WHERE
	userid='$userid'
JOIN
	category
ON
	inquiries.category = categories.id
ORDER BY
	inquiries.createdate
DESC LIMIT 5";
								
$res = $mysqli->query($sql);

while($cat = $res->fetch_array()) {
								
	echo '<option value="'.$cat['categories.id'].'">'.$cat['categories.name'].'</option>';
								
}
?>

Open in new window


Error:
<b>Fatal error</b>:  Call to a member function fetch_array() on a non-object in <b>/home/d/i/ftp_dinethdk/m/inquiry.php</b> on line <b>196</b><br />

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try this to see information about the error:
$res = $mysqli->query($sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error(), E_USER_ERROR);

Open in new window

Author

Commented:
I get this error message now:

<b>Warning</b>:  mysqli_error() expects exactly 1 parameter, 0 given in <b>/home/d/i/ftp_dinethdk/m/inquiry.php</b> on line <b>194</b><br />
<br />
<b>Fatal error</b>:  Query Failed! SQL: 
SELECT
	inquiries.category, categories.name, categories.id
FROM
	inquiries, categories
WHERE
	userid='6'
JOIN
	category
ON
	inquiries.category = categories.id
ORDER BY
	inquiries.createdate
DESC LIMIT 5 - Error:  in <b>/home/d/i/ftp_dinethdk/m/inquiry.php</b> on line <b>194</b><br />

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
This article can show you how to run queries, test for success or failure, and visualize the error information, if any.  It has parallel examples in the familiar but obsolete MySQL along with MySQLi and PDO.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

F IgorDeveloper

Commented:
Change in your query the WHERE part after JOIN/ON


SELECT
	inquiries.category, categories.name, categories.id
FROM
	inquiries, categories
JOIN
	category
ON
	inquiries.category = categories.id
WHERE
	userid='$userid'
ORDER BY
	inquiries.createdate
DESC LIMIT 5

Open in new window

Expert of the Year 2008
Top Expert 2008

Commented:
Try:
$sql = "SELECT
	inquiries.category, categories.name, categories.id
FROM
	inquiries
INNER JOIN 
	categories
ON
	inquiries.category = categories.id
WHERE
	userid='$userid'
ORDER BY
	inquiries.createdate
DESC LIMIT 5";

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
afraid this is fundamental SQL syntax that you need to get a firm grasp on
(i.e. does not matter if it is PHP's mysqli or pdo or even if it was C#)
Try to remember this: "do NOT use commas in the FROM line" this will help you from making mistakes.

Then also note that: JOINS belong INSIDE the FROM clause
(or, "joins are part of the from clause")

The following indentation may make it more obvious:

SELECT  inquiries.category , categories.name, categories.id
FROM inquiries
       INNER JOIN categories ON inquiries.category = categories.id
WHERE userid = '$userid'
ORDER BY inquiries.createdate

-----------
Why should I not use commas in the "from line"?

It is allowed syntax to do this BUT it causes potential problems. e.g.

select * from inquiries, categories

that is allowed in SQL, but it MULTIPLIES every row in the inquiries tables with the number of rows in the categories table. This is known as a Cartesian product and it is a nightmare if done accidentally.

So, "do NOT use commas in the FROM line" this will help you from making mistakes.

Author

Commented:
How does GROUP BY fit into it? Where is the correct "location"?
(i'm trying to get a list of recently used categories, with no category-name duplicates - the same category name only appear ones in the list)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
SQL Clauses are always written in a predetermined sequence:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Some folks will attempt to learn "Sweaty Feet Will Give Horrible Odours" but I've not found it useful.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
>>"i'm trying to get a list of recently used categories, with no category-name duplicates"

Try this:

SELECT  inquiries.category , categories.name, categories.id
FROM inquiries
       INNER JOIN categories ON inquiries.category = categories.id
WHERE userid = '$userid'
GROUP BY  inquiries.category , categories.name, categories.id
ORDER BY MAX(inquiries.createdate) DESC
LIMIT 5;

Author

Commented:
Can't get it to work, sadly.
It's like the list contain 2 random category names and definitely not the last two unique category names.

Maybe I need to use DISTINCT instead? But I'm not sure how to use that in this example.

$sql = "SELECT
	inquiries.category , categories.name, categories.id
FROM
	inquiries
	INNER JOIN categories ON inquiries.category = categories.id
WHERE
	userid = '$userid'
GROUP BY
	inquiries.category
ORDER BY
	inquiries.createdate
DESC
LIMIT 2";

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
you did not follow what I gave you

the MySQL database (not the php api of the same name) allows really sloppy syntax for group by, and this can actually produce weird results.

the PROPER ANSI syntax for GROUP BY must have ALL non-aggregating columns specificed.

So, do NOT just use :
GROUP BY inquiries.category

use:
GROUP BY  inquiries.category , categories.name, categories.id

Now, also, please look at the order by I provided above.
Order on the calculation MAX(inquiries.createdate) DESC

This will then give you the most recently used categories.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
By the way "select distinct" doesn't do a thing that GROUP BY doesn't do, but the reverse is not true. GROUP BY is way more powerful.

For when you have time: Select Distinct is returning duplicates ...
In truth, “select distinct” is a rather dull animal with minimal decision making powers.
"select distinct" is the most abused, and most misunderstood, SQL feature IMO. Use it sparingly.

Author

Commented:
Ahh.... I didn't see the previous message from you - sorry about that.
Yes, everything works fine now :)

Going to read up on MAX, not something I have been using before.

Thanks a lot for your help.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
by the way "DESC" is descending and it "qualifies" a column within the order by clause.

The way you have it written in your SQL (on a separate line) is likely to get you into trouble one day. Put it beside the column it refers to, not on the next line

$sql = "SELECT
	inquiries.category , categories.name, categories.id
FROM
	inquiries
	INNER JOIN categories ON inquiries.category = categories.id
WHERE
	userid = '$userid'
GROUP BY
	inquiries.category
ORDER BY
	MAX(inquiries.createdate) DESC
LIMIT 2";

Open in new window


Note this example is valid syntax

ORDER BY col1 DESC, col2 ASC, col3 DESC, col4 DESC, col5 ASC

i.e., it works BY COLUMN

Author

Commented:
Learning something new everyday :)
I have changed the syntax, so it match your suggestion.

$sql = "SELECT inquiries.category , categories.name, categories.id
FROM inquiries
	INNER JOIN categories ON inquiries.category = categories.id
WHERE userid = '$userid'
GROUP BY inquiries.category , categories.name, categories.id
ORDER BY MAX(inquiries.createdate) DESC LIMIT 2";

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial