Select Query inside another Select Query

[Riu 2009]

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");

[LastMitch]

@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.

[madCoder]

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}'"

[diafol]

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

[Riu 2009]

@diafol and @madCoder 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

[LastMitch]

@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]

@LastMtich thnx for help :)