This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.
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:
Error:
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>';
}
?>
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 />
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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);
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 />
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
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
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";
afraid this is fundamental SQL syntax that you need to get a firm grasp on
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.
(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.
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)
(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:
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.
FROM
WHERE
GROUP BY
HAVING
ORDER BY
>>"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;
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;
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.
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";
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.
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 ...
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.
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
Note this example is valid syntax
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";
Note this example is valid syntax
ORDER BY col1 DESC, col2 ASC, col3 DESC, col4 DESC, col5 ASC
i.e., it works BY COLUMN
i.e., it works BY COLUMN
Learning something new everyday :)
I have changed the syntax, so it match your suggestion.
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";
Premium Content
You need an Expert Office subscription to comment.Start Free Trial