CODEWITHSUNDEEP
mysql
Neha
Neha

Reputation: 149

Merge two sql query for multiple output

I have these two query :

$query = mysql_query("SELECT * from chapter where chapterID =1")or die('Query failed');
$query1 = mysql_query("SELECT * from page where chapterID =1")or die('Query failed');

my php

<?php 
						$con=mysql_connect('localhost','root','') or die ('could not connect to db:'. mysql_error());
						mysql_select_db('learn') or die('No db found');
						$query = mysql_query("SELECT chapter.cDescription, page.pageName, page.pageIcon, chapter.cTitle FROM chapter LEFT JOIN page ON chapter.chapterID=page.chapterID GROUP BY page.pageID,page.chapterID")or die('Query failed');
						
						while ($result = mysql_fetch_array($query) )
						{
					
						echo "

                        <tr>
                            <td>
								<hr>
								<div class='chapter-container'>
								<div class='chapter-title' style='display: inline-block;float: left;'>
								<h3 style='margin-bottom: 22px;'>";
								
								echo $result['cTitle'];
							 
								echo "</h3>
								<div class='chapter-description'>";
								
								echo $result['cDescription']; 
								echo "
								</div>
								</div>
				
								<div class='chapter-contents' style='display: inline-block;float: left'>
								<div class='contents-box' style='margin: 15px 20px;'>
							
                                        <a href='networking_terms.html' style='display: block'>";
                                           
											echo "<span class='".$result['pageIcon']."' ></span>";
                                      
											echo "<span class='progress-title' data-toggle='tooltip' data-placement='right' title='Get familiar with the terms!'>";
											
											echo $result['pageName']; 
											
											echo "</span>
                                        </a>";
						}
				
					?>

I want to merge these two in one query. How can i merge the two query? Note: that 1 chapter has many pages

What i want to achieve enter image description here

What i am actually getting enter image description here

Upvotes: 1

Views: 66

Answers (2)

devpro
devpro

Reputation: 16117

UPDATED:

Try this:

// make an array
$yourData
while ($result = mysql_fetch_array($query) )
{

$yourData[$result['chapterId']][] = $result;

}

Than print it in your HTML, idea is:

foreach( $yourData as $key => $value ){

  //your stuff
  echo $value[0]['chapterName']."<br>". $value[0]['chapDesc']; // print chaprer name
  foreach( $value as $final ){

  //your html stuff
  echo $final["pageName"]; // print page name

  }
}

UPDATE 3 (with original HTML):

$myArr = array();
foreach ($array as $key => $value) {
    $myArr[$value['cTitle']][] = $value;
}

foreach ($myArr as $key2 => $value) {
echo "

<tr>
<td>
<hr>
<div class='chapter-container'>
<div class='chapter-title' style='display: inline-block;float: left;'>
<h3 style='margin-bottom: 22px;'>";

echo $value[0]['cTitle'];

echo "</h3>
<div class='chapter-description'>";

echo $value[0]['cDescription']; 
echo "
</div>
</div>

";

foreach ($value as $key => $value2) {
echo "
<div class='chapter-contents' style='display: inline-block;float: left'>
<div class='contents-box' style='margin: 15px 20px;'>

<a href='networking_terms.html' style='display: block'>";

echo "<span class='".$value2['pageIcon']."' ></span>";

echo "<span class='progress-title' data-toggle='tooltip' data-placement='right' title='Get familiar with the terms!'>";

echo $value2['pageName']; 

echo "</span>
</a>";
}
}
?>

Side Note:

I suggest you to use mysqli_* or PDO instead of mysql_* becuase its deprecated and not available in PHP 7

Upvotes: 1

David Bachmann Jeppesen
David Bachmann Jeppesen

Reputation: 395

Sounds like you want a dataset containing the result from both queries? To do this, you should use a UNION:

$query = mysql_query("SELECT * FROM Chapter WHERE ChapterID = 1
    UNION
    SELECT * FROM Page WHERE ChapterID = 1") or die('Query failed');

For this to work, the columns of the tables must match in both number and type. If they don't, you must select the spicific columns in both select statements and those should match.

Upvotes: 0

Related Questions