Select Query inside another Select Query

Question

Riu 2009 0 Junior Poster

13 Years Ago

hi everyone :)

i have two tables (learningmaterial & evaluations) in mysql DB and i want to display all the learning material (from learningmaterial table ) and evaluations (if any) from evaluations table.. here is my code and i just want to ask how will i insert another select query inside the select query of learning material (i have learningmaterial_id in the evaluations table as a foreign key)

<?php
   $con=mysql_connect($dbserver,$dbusername,$dbpassword);
if (!$con)
    {
   die('Could not connect: ' . mysql_error());
    }
mysql_select_db($dbname, $con);
$result = mysql_query("SELECT * FROM learningmaterial WHERE (coursesid=".$cid.") ORDER BY sequence ASC");


echo "<table border='1' style='width:500px;'> <br />";

while($row = mysql_fetch_array($result))
  {
    echo "<tr>";
    echo " <h5>" . $row['title'] . "</h5>";
    echo " <p>" . $row['description'] . "</p>";

        if ($row['type'] === 'youtube video')
            {
                echo "<img src='graphics/youtube-icon.png'/>";
            }
        elseif ($row['type'] === 'vimeo video')
            {
                echo "<img src='graphics/vimeo-icon.png'/>";
            }
        elseif ($row['type'] === 'youtube audio')
            {
                 echo "<img src='graphics/youtube-icon.png'/>";
            }
        elseif ($row['type'] === 'Flickr')
            {
                echo "<img src='graphics/flickr-icon.png'/>";
            }
        elseif ($row['type'] === 'Picasa')
            {
                echo "<img src='graphics/picassa-icon.png'/>";
            }
        elseif ($row['type'] === 'video')
            {
                echo "<img src='graphics/local-icon.png'/>";
            }
        elseif ($row['type'] === 'audio')
            {
                echo "<img src='graphics/local.png'/>";
            }
        elseif ($row['type'] === 'image')
            {
                echo "<img src='graphics/local.png'/>";
            }
        elseif ($row['type'] === 'ppt')
            {
                echo "<img src='graphics/local.png'/>";
            }
        elseif ($row['type'] === 'pdf')
            {
                echo "<img src='graphics/local.png'/>";
            }
        else
            {
                echo " ";
            } 

    echo "</tr>";

  }

and the query i want to run inside this query is:

$result1 = mysql_query("SELECT learningmaterial.id, evaluation.id,evaluation.name,evaluation.description,evaluation.learningmaterialid,evaluation.coursesid 
                            FROM learningmaterial,evaluation
                            WHERE learningmaterial.id=evaluation.learningmaterialid");

mysql php

4 Contributors
6 Replies
283 Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by Riu 2009

Dani AI

Generated 9 Months Ago

A single, prepared LEFT JOIN is the simplest, safest way to show every learning material and any evaluations that belong to it. was correct to recommend a left join; and are also right to warn about subqueries and performance. Do not run a separate SELECT for evaluations inside the learning-material loop (the N+1 query problem). Also avoid the deprecated mysql_* API shown earlier — use mysqli or PDO with prepared statements and escape output for HTML.

Here is a compact PDO example that fetches materials and their evaluations in one query, then groups the results so each material prints once with a list of evaluations below it:

<?php
$pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$sql = "
  SELECT lm.id AS lm_id, lm.title, lm.description, lm.type,
         e.id AS ev_id, e.name AS ev_name, e.description AS ev_description
  FROM learningmaterial AS lm
  LEFT JOIN evaluation AS e
    ON e.learningmaterialid = lm.id
  WHERE lm.coursesid = :cid
  ORDER BY lm.sequence ASC, e.id ASC
";

$stmt = $pdo->prepare($sql);
$stmt->execute([':cid' => $cid]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$materials = [];
foreach ($rows as $r) {
  $id = $r['lm_id'];
  if (!isset($materials[$id])) {
    $materials[$id] = [
      'title' => $r['title'], 'description' => $r['description'],
      'type' => $r['type'], 'evaluations' => []
    ];
  }
  if ($r['ev_id'] !== null) {
    $materials[$id]['evaluations'][] = ['name'=>$r['ev_name'],'desc'=>$r['ev_description']];
  }
}
?>

Render with htmlspecialchars() to prevent XSS. If you prefer not to de-duplicate rows in PHP, an alternative is two queries: fetch all learning materials, then fetch all evaluations WHERE learningmaterialid IN (...) and map them by id — that avoids repeated JOIN rows and can be easier for pagination. For performance, index evaluation.learningmaterialid and learningmaterial.coursesid, select only needed columns (not SELECT *), and replace long if/elseif icon blocks with a simple lookup array for type->icon mapping.

All 6 Replies

Member #949455

13 Years Ago

@Riu 2009

i have two tables (learningmaterial & evaluations) in mysql DB and i want to display all the learning material (from learningmaterial table ) and evaluations (if any) from evaluations table.. here is my code and i just want to ask how will i insert another select query inside the select query of learning material (i have learningmaterial_id in the evaluations table as a foreign key)

You are try to used a subquery.

http://www.tutorialspoint.com/sql/sql-sub-queries.htm

I don't have a db to test out your code but this is the only way to help solve your issue.

Member #120589

13 Years Ago

Agree with mC - LEFT JOIN it is. Always try to avoid subqueries whenever possible - they're slower AFAIK.

Member #949455 commented: Good to know ! I didn't know that. +6

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

madCoder 17 Junior Poster · Answer 1 · 2012-11-07T22:21:32+00:00

Guessing by your description, what you need is a left join: display all learning materials and their evaluations even when there are no evaluations available. A left join lets you select ALL relevant entries from the first (left) table to a second (right) table even some corresponding entries don't exist in the right table.

The result will be at least one row for each learningMaterial. You'll get additional rows for each ADDITIONAL evaluation.

"SELECT learningmaterial.*, evaluation.id, evaluation.name, evaluation.description, evaluation.learningmaterialid, evaluation.coursesid 
FROM learningmaterial LEFT JOIN evaluation
WHERE learningmaterial.id=evaluation.learningmaterialid
AND learningmaterial.coursesid='{$cid}'"

Riu 2009 0 Junior Poster · Answer 2 · 2012-11-08T05:56:00+00:00

@diafol and i just want to show the learning material and the evaluations if present... if there is no evaluation after some learning material just dont display any thing simply next learning material should be displayed...
@LastMitch yes some what u r getting my point... but i want to put the query after while statement of first query to display the rows of evaluations after the learningmaterial with which the evaluation is associated

Member #949455 · Answer 3 · 2012-11-08T18:26:59+00:00

@Riu 2009

but i want to put the query after while statement of first query to display the rows of evaluations after the learningmaterial with which the evaluation is associated

You know there's a big difference between this query:

$result = mysql_query("SELECT * FROM learningmaterial WHERE (coursesid=".$cid.") ORDER BY sequence ASC");

than this query:

$result1 = mysql_query("SELECT learningmaterial.id, evaluation.id,evaluation.name,evaluation.description,evaluation.learningmaterialid,evaluation.coursesid FROM learningmaterial,evaluation WHERE learningmaterial.id=evaluation.learningmaterialid");

This is gonna be a bit of challenge.

If you are using the JOIN it will be more simpler to using the sub query.

You know there won't be $result1 anymore?

That query will be part of $result query

I'm figuring out the query in my head so the query should look something like this :

$result = mysql_query("SELECT * FROM learningmaterial WHERE learningmaterial.id,evaluation.id,evaluation.learningmaterialid,evaluation.coursesid ORDER BY sequence ASC IN (SELECT learningmaterial.id, evaluation.id,evaluation.name,evaluation.description,evaluation.learningmaterialid,evaluation.coursesid FROM learningmaterial,evaluation WHERE learningmaterial.id=evaluation.learningmaterialid)");

Riu 2009 0 Junior Poster · Answer 4 · 2012-11-09T09:51:31+00:00

Riu 2009 0 Junior Poster

13 Years Ago

@LastMtich thnx for help :)

Select Query inside another Select Query

Dani AI

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers