Cross join query and how to set limit

[Anonymous]

hi,
I have one cross join query in mysql as given below,
SELECT tblmodule.module_id,tblmodule.module_name,tblmodule.module_desc, tblmodule.module_priority, tblarticles.article_id,
tblarticles.article_title FROM tblmodule LEFT JOIN tblarticles ON tblmodule.module_id=tblarticles.article_module_id
WHERE tblmodule.module_type = 5 ORDER BY tblmodule.module_priority, tblarticles.article_id DESC;


module_name have one to many article_title . What I want to do is set a limit in query or if it not possible in php script that it selects module_name and only six article_title.
Any way to set limit for article_title in this query or
if someone can do in php like one module_name and six of its article_titles.


thnks for your help in advance

 

[Alexej Kubarev]

http://www.mysql.com/join
http://www.mysql.com/limit

basicaly you set LIMIT 5 to fetch five records and so on.. more information is in the manual

 

[Anonymous]

I know how to set limit. but this data is from two tables. First table have one headings(like modules in this query) and second tables have sub headings(like in this case are titles). If I set limit to 5 then it will get only 5 records.
But I want all headings and only six titles

Like you can see coding here how I am using query in php and what I am geting result

<?
$query3 = "SELECT tblmodule.module_id,tblmodule.module_name,tblmodule.module_desc, tblmodule.module_priority, tblarticles.article_id,tblarticles.article_title FROM tblmodule LEFT JOIN tblarticles ON tblmodule.module_id=tblarticles.article_module_id
WHERE tblmodule.module_type = 5 ORDER BY tblmodule.module_priority, tblarticles.article_id DESC tblarticles.article_title LIMIT 6";
$result3 = do_query($query3, $dbcnx);
$heading;
$counter2=1;
$cells_per_row2=2;
?><tr><td>
<table width="100%" cellpadding="0" cellspacing="0"><?
while($row3 = mysql_fetch_array($result3))
{
if($heading!=$row3['0']){
if($counter2!=1) echo "<tr><td colspan=2><hr></td></tr>";
?>
<tr><td class="box_head"><a href="./index.php?do=box&id=<?=$row3['0']?>">
<font color="#FFFFFF"><?=$row3['1']?></font></a></td><td align="right"><img src="img/side.gif" /></td></tr>
<?
$heading=$row3['0'];
}
$counter2++;
if(($counter2 % $cells_per_row2) == 0) { echo '<tr>'; } ?>
<td width="33%" id="pagebottom"><img src="img/arrow2.gif" />
  <a href="./index.php?do=article&id=<?=$row3['4']?>"><?=$row3['5']?></a></td><?
if(($counter2 % $cells_per_row2) == 1) { echo '</tr>'; }
}
?>



The result I am getting here is like this

heading1
sub heading1 .1 sub heading1.2
sub heading1. 3 sub heading1.4
...................... ...................
...................... ...................
...................... ...................

heading2
sub heading2.1 sub heading2.2
sub heading 2.3 sub heading2.4
...................... ...................
...................... ...................
...................... ...................


But I want result that only one heading and its 6 sub headings like this
heading1
sub heading1 .1 sub heading1.2
sub heading1. 3 sub heading1.4
sub heading1. 5 sub heading1.6

heading2
sub heading2.1 sub heading2.2
sub heading 2.3 sub heading2.4
sub heading2.5 sub heading2.6

and so on

thnks for your help in advance