Use result in query

Question

Hi im trying to show the group name on my site but im having trouble connecting both querys. The users class is in the user table while the group name is in the group table.

ive managed to show the class user number from the user table, i just need to add a second query now to pull from groups the group name

this works for the users class number

        $res3 = SQL_Query_exec("SELECT class FROM `users` WHERE id=$posterrid ");
        $arr3 = mysqli_fetch_row($res3);
        $group = $arr3[0];

so it would be

        $res3 = SQL_Query_exec("SELECT class FROM `users` WHERE id=$posterrid ");
        $res4 = SQL_Query_exec("SELECT level FROM `groups` WHERE group_id=$res3 ");
        $arr3 = mysqli_fetch_row($res4);
        $group = $arr3;

score 2 · Answer 1 · answered Apr 29 '19 at 15:25

I would try to join the tables and select your desired result in 1 query directly. Something like this:

"SELECT groups.level FROM groups JOIN users ON groups.group_id=users.class WHERE users.id=$posterrid"

You could also do it in 2 queries but in your 2nd code piece your 2nd query takes $res3 as an argument. $res3 is not a string though which is why your $res4.. line doesn't give the result you want. You need to get the result string ($group) first and then use that in your second query.

Buh13246 · Answer 2 · 2019-04-29T15:36:12.177

0

OUTDATED PLEASE USE JOIN (In MySQL queries, why use join instead of where?)

Like @Shadol sayed You can directly combine the 2 Querys. But I would do it like this:

"SELECT users.*, groups.* FROM `users`, `groups` WHERE `users`.id=$posterrid AND `groups`.group_id = `users`.class"

edited Apr 29 '19 at 15:36

answered Apr 29 '19 at 15:29

Buh13246

173
7

1

Right idea, but using proper modern join syntax would be better – ADyson Apr 29 '19 at 15:30
Whenever you see a "WHERE" in your query be aware that there is a better solution using "JOIN" – B001ᛦ Apr 29 '19 at 15:32
Okay your right. I never used the JOIN Syntax (I just had SQL in 10. class...) so my SQL is maybe a little bit outdated.... – Buh13246 Apr 29 '19 at 15:35

score 0 · Answer 3 · answered Apr 29 '19 at 15:38

This is not the right approach. You should do a single query that joins the user and group tables, so you get the data you require in your result set with a single query.

Your question doesn't provide important information like the structure of your tables, so I have to make an educated guess. The assumptions I had to make:

users.class is the foreign key for groups.group_id
groups.level is actually the "Group Name"

Just as an aside that is very confusing (re)naming of a foreign key. It is not helpful to anyone trying to figure out the relationships between your tables to use a completely different name ("class") when you could have named the column group_id.

So, to get this in one query:

SQL_Query_exec("SELECT g.level as level
                FROM `users` u JOIN `groups` g ON (g.group_id = u.class)
                WHERE id = $posterrid");

The primary take away for you should be this:

You will benefit from studying relational database design/database normalization, and how you join tables together.

Additionally, whatever database class you are using is antiquated and opens you up to SQL injection and escaping issues.

You should not be using string interpolation for queries, but rather, you should be using parameters and prepared statements. The way you are

Thank you for the reply I'll read up on using join for the tables. Can you point me in the right direction on how I would go about preventing sql injection on a php site — M-jay, Apr 29 '19 at 16:25
My sincere highly opinionated advice is to use PDO. [Read this unofficial manual](https://phpdelusions.net/pdo) and you will be a PDO expert with a minimum of time/effort. If you feel more comfortable with a database class that wraps PDO, then I'd recommend the [Doctrine ORM's DBAL component](https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/index.html), which can be used essentially in the same way you are using whatever class you have now. — gview, Apr 29 '19 at 16:33

Use result in query

3 Answers3