CODEWITHSUNDEEP
phpmysqlsql
Kraken
Kraken

Reputation: 24213

Query inside a query

I am using php with Mysql. I want to know what really is wrong with query inside a query, what if i am not that much comfortable with using the INNER JOIN , and rather prefer writing multiple queries. Am i doing something terribly wrong, or is it just to make it visually sound?

What I mean is...

$query="SELECT * FROM TABLE WHERE USER_ID=1";
$data=mysqli_query($dbc,$query);
$row=mysqli_fetch_array($data);
$query2="select * from TAble2 where some_id='{$row['user_id']}'";
$data1=mysqli_query($dbc,$query);
$row1=mysqli_fetch_array($data1);
// and then use $row1['column_name']

Upvotes: 0

Views: 280

Answers (2)

Phil
Phil

Reputation: 164736

The main benefit to combining as much as possible into a single query as opposed to iterating nested cursors is that round trips to the DB are relatively expensive.

Using the nested cursors method in your question, you are preparing and executing n + 1 SQL statements, where n is the number of records in the initial result set.

Where you to combine those queries with a join, you would only be preparing and executing one statement, no matter how large the result set.

SELECT T1.foo, T1.bar, T2.baz
FROM `TABLE` T1
INNER JOIN `Table2` T2 ON T1.user_id = T2.some_id
WHERE T1.user_id = 1

You're also placing less load on the database itself, particularly where statement caching is enabled.

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191729

You should post some code before we can help you with your specific problem.

However, I would say that not being comfortable with doing something is generally not an excuse not to do it. Instead, you should read all you can about JOINs so that you are comfortable, and then there won't be a problem.

JOINs are very powerful, and there are many things that you cannot do in MySQL without them. When you say query within a query, I assume you mean doing something like

SELECT
   postid
FROM
   Post
WHERE
   userid IN (
      SELECT
         userid
      FROM
         User
      WHERE
         username = ?
   )

Isn't this much more complicated than

SELECT
   postid
FROM
   Post
   NATURAL JOIN User
WHERE
   username = ?

Upvotes: 4

Related Questions