[Bossk]

Got this very simple Mysql problem that I think some of you could give a simple solution to.

I want to execute two queries in one connection but the problem is: the second query is based on the result of the first one? How would I perform this?

[Dialog]

You will have to use subqueries.

Let's say we have a table `users` and a table `posts`, and we want to get all posts created by users that have not been blocked.

Your first option is using a join (https://dev.mysql.com/doc/refman/5.7/en/join.html):

Code

SELECT p.title FROM posts p
INNER JOIN users u ON p.user_id=u.id
WHERE u.blocked=0;

You could also use a subquery (https://dev.mysql.com/doc/refman/5.7/en/subqueries.html), like demonstrated below.

Code

SELECT p.title FROM posts p
WHERE p.user_id IN (
   SELECT u.id FROM users u
   WHERE u.blocked=0
);

---

Using subqueries is more "flexible" - easier to read, and easier to modify when requirements change. Benefit of joins is that the database server is more likely to optimise those.

[Bossk]

You will have to use subqueries.

Let's say we have a table `users` and a table `posts`, and we want to get all posts created by users that have not been blocked.

Your first option is using a join (https://dev.mysql.com/doc/refman/5.7/en/join.html):

Code

SELECT p.title FROM posts p
INNER JOIN users u ON p.user_id=u.id
WHERE u.blocked=0;

You could also use a subquery (https://dev.mysql.com/doc/refman/5.7/en/subqueries.html), like demonstrated below.

Code

SELECT p.title FROM posts p
WHERE p.user_id IN (
   SELECT u.id FROM users u
   WHERE u.blocked=0
);

---

Using subqueries is more "flexible" - easier to read, and easier to modify when requirements change. Benefit of joins is that the database server is more likely to optimise those.

Thanks, but this was not what I was looking for lol. My question is probably very unclear with wrong terms but here is what I want:

I got this query right here:

Code

$sql = "SELECT MAX(queue) FROM nations WHERE nation_name = '$nation'";

Then I want to INSERT the given result into a table. Sorry for the unclear question  🤮

[Dialog]

Thanks, but this was not what I was looking for lol. My question is probably very unclear with wrong terms but here is what I want:

I got this query right here:

Code

$sql = "SELECT MAX(queue) FROM nations WHERE nation_name = '$nation'";

Then I want to INSERT the given result into a table. Sorry for the unclear question  🤮

Ah, I understand.

The following should do what you need:

Code

INSERT INTO my_table (my_column) SELECT MAX(queue) FROM nations WHERE nation_name = '$nation';

See also: https://dev.mysql.com/doc/refman/5.7/en/...elect.html

-

Some unsolicited advice: it looks like you're using raw (user?) input in your query. This is very bad practice and makes your application vulnerable to SQL injection. It'd be good to read up on preparing your SQL statements. It seems you're using PHP, so I recommend going over this when using PDO: http://php.net/manual/en/pdo.prepared-statements.php (or for mysqli: http://php.net/manual/en/mysqli.quicksta...ements.php).

[Bossk]

Ah, I understand.

The following should do what you need:

Code

INSERT INTO my_table (my_column) SELECT MAX(queue) FROM nations WHERE nation_name = '$nation';

See also: https://dev.mysql.com/doc/refman/5.7/en/...elect.html

-

Some unsolicited advice: it looks like you're using raw (user?) input in your query. This is very bad practice and makes your application vulnerable to SQL injection. It'd be good to read up on preparing your SQL statements. It seems you're using PHP, so I recommend going over this when using PDO: http://php.net/manual/en/pdo.prepared-statements.php (or for mysqli: http://php.net/manual/en/mysqli.quicksta...ements.php).

Ah I didn't know that I could actually merge them into one query, thanks a lot man. Will also look into security once I got the functionality working.  👍 🎉