Mysqli join query problems

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

kgp43Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

skijCommented:
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

0
kgp43Author 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

0
Ray PaseurCommented:
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
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

F IgorDeveloperCommented:
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

0
hieloCommented:
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

1
PortletPaulfreelancerCommented:
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.
1
kgp43Author 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)
0
PortletPaulfreelancerCommented:
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.
1
PortletPaulfreelancerCommented:
>>"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;
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgp43Author 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

0
PortletPaulfreelancerCommented:
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.
1
PortletPaulfreelancerCommented:
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.
1
kgp43Author 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.
0
PortletPaulfreelancerCommented:
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
0
kgp43Author 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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.