Avatar of kgp43
kgp43
Flag for Denmark asked on

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

PHPSQL

Avatar of undefined
Last Comment
kgp43

8/22/2022 - Mon
skij

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

kgp43

ASKER
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

Ray Paseur

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.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Francisco Igor

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

hielo

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

PortletPaul

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
kgp43

ASKER
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)
PortletPaul

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.
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
kgp43

ASKER
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

Your help has saved me hundreds of hours of internet surfing.
fblack61
PortletPaul

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.
PortletPaul

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.
kgp43

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PortletPaul

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
kgp43

ASKER
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