Mysqli join query problems

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);

Select all Open in new window

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 />

Select all Open in new window

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

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

Select all Open in new window

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";

Select all Open in new window

afraid this is fundamental SQL syntax that you need to get a firm grasp on
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.

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)

SQL Clauses are always written in a predetermined sequence:

Some folks will attempt to learn "Sweaty Feet Will Give Horrible Odours" but I've not found it useful.

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";

Select all Open in new window

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.

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.

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.

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";

Select all Open in new window

Note this example is valid syntax

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";

Select all Open in new window