How to make query efficient?

[realjumper]

Hi,

In my database table (named kanji) I have a column named 'kanji', which is popluated with unique Japanese charcaters. I also have a column named 'lesson', which is not unique....and a further column named 'kanji_id' which is unique and corresponds to each unique kanji character. There are 45 'lessons' in the table. Any given 'lesson' may have 4 - 9 unique kanji and kanji_id. For example: 'lesson' 1 has 5 unique kanji (and kanji_id of course), 'lesson' 2 has 3 unique kanji (and kanji_id of course) etc etc all the way to 'lesson' 45.....kinda like this:

|  kanji_id  |  lesson  |  kanji    |

        1              1            qw
        2              1            po
        3              1            ma
        4              1            kw
        5              1            oq
        6              2            dl
        7              2            jf
        8              2            sx

What I want to do is to query the database and create an html table of the total number of kanji.....each lesson should have it's own row in the html table, with each kanji linked by 'kanji_id' so that I can display more details about that kanji if a user clicks on it.

I could do it this.....but it's very inefficient....

[code]

// Retrieve all the data from the table
$result1 = mysql_query("SELECT * FROM kanji WHERE lesson= '1' ORDER BY kanji_id")
or die(mysql_error());

echo "<tr>";
while ($row1 = mysql_fetch_assoc($result1)) {
echo "<td align=\"center\" >";
echo "<a href=\"explain.php?kanji_id=$row1[kanji_id]\">$row1[kanji]</a>";
echo "</td>";
echo "</tr>";
}


// Retrieve all the data from the table
$result2 = mysql_query("SELECT * FROM kanji WHERE lesson= '2' ORDER BY kanji_id")
or die(mysql_error());

echo "<tr>";
while ($row2 = mysql_fetch_assoc($result2)) {
echo "<td align=\"center\" >";
echo "<a href=\"explain.php?kanji_id=$row2[kanji_id]\">$row2[kanji]</a>";
echo "</td>";
echo "</tr>";
}


// Retrieve all the data from the table
$result3 = mysql_query("SELECT * FROM kanji WHERE lesson= '3' ORDER BY kanji_id")
or die(mysql_error());

echo "<tr>";
while ($row3 = mysql_fetch_assoc($result3)) {
echo "<td align=\"center\" >";
echo "<a href=\"explain.php?kanji_id=$row3[kanji_id]\">$row3[kanji]</a>";
echo "</td>";
echo "</tr>";
}

// and continue as above all the way to lesson='45'

[/code]

The above code would do the job, but 45 queries to the db is crazy.......

How can I achieve the above in a better fashion? I can't seem to construct a single query that would loop through the result that would enable me to have all the lesson 1 results in a single <tr> with each kanji in it's own <td>......lesson 2 results in the next <tr> with each kanji in it's own <td> etc etc etc

I hope that I have explained this properly.

If there is a better way to do this, I would be very grateful if anyone can help.

Thanks

[GingerRobot]

Im slightly confused as to how this would work. If each lesson is suposed to be an individual row in the table and contain cells for each kanji, aren't you going to end up with all the rows having differant numbers of cells because the number in each lesson varies?

[realjumper]

Yes, that's true, but that's not really an issue at this stage. The db table isn't likely to have anything inserted, so the number of cells won't vary after the initial set up. I can manually hard code <td>&nbsp;</td>'s after the loops to make the table balance. The problem at this stage is how to make this more efficient. If this could be achieved with a single query, I can see that hard coding for empty table cells could be a problem, but that's a problem I haven't come to yet.....but it IS a good point. Thanks

[GingerRobot]

[code]
<?php
$result = mysql_query("SELECT * FROM `kanji` ORDER BY `lesson` DESC `kanji_id` DESC")
or die(mysql_error());
echo '<tr>';
$x = 1;
while($row = mysql_fetch_assoc($result)){
echo '<td align="center" >';
echo "<a href=\"explain.php?kanji_id=$row[kanji_id]\">$row[kanji]</a>";
echo '</td>';
if($row[lesson] != $oldlesson){
while($x <= 9){//add blank cells. 9 being the most in any one lesson
echo '<td>&nbsp;</td>';
$x++;
}
$x = 1;
echo '</tr></tr>';
}else{
$x++;
}
$oldlesson = $row[lesson];
}
?>
[/code]

Completely untested, but you could give it a wirl

[realjumper]

That's an interesting approach...thank you. It's not quite right, but it's damn close and I think I will be able to sort it out from here. Thank you for your idea.... :-)

[realjumper]

Nearly there.......

With the following code....

[code]

<?php

echo "<table border=\"1\">";
$result = mysql_query("SELECT * FROM kanji.kanji ORDER BY lesson ASC, kanji_id ASC")
or die(mysql_error());
echo "<tr>";
$x = 1;
while($row = mysql_fetch_assoc($result)){
echo "<td align=\"center\">";
echo "<a href=\"explain.php?kanji_id=$row[kanji_id]\">$row[kanji]</a>";
echo "</td>";
if($row[lesson] != $oldlesson){
while($x <= 15){//add blank cells. 15 being the most in any one lesson
echo "<td>&nbsp;</td>";
$x++;
}
$x = 1;
echo "</tr>";
}else{
$x++;
}
$oldlesson = $row[lesson];
}
echo "</table>";
?>

[/code]

I get the right information returned, and very nearly in the correct order. The problem, that I can't understand is this....

The first result (kanji_id 1, lesson 1) is in an html table row all by itself. The rest of lesson 1 is in the second html table row......with the first kanji_id of  lesson 2 tagged onto the end and so on....like this...

1st html row:  aa  '&nbsp;  '&nbsp;'  '&nbsp;'  '&nbsp;'  '&nbsp;'  '&nbsp;'  '&nbsp;'  '&nbsp;'  '&nbsp;'
2nd html row: bb   cc         dd         ee         ff          gg         hh          ii           jj          '&nbsp;'

etc etc

But it should be like this:

1st html row:  aa     bb          cc         dd        ee          ff          ii       '&nbsp;'   '&nbsp;'    '&nbsp;'
2nd html row:  jj      kk          ll         mm      nn          oo         pp          qq           rr       '&nbsp;'

Do you see what I mean? The first html row only has one result from lesson 1. The rest of lesson 1 is in the 2nd html table row, with the 1st result from lesson 3 in the last cell.....and so it continues.

I'm not sure what's going on here, and the code supplied GingerRobot is much more efficient.....but not quite right. Any help would be appreciated.

Thanks

[hitman6003]

[code]<?php
$result = mysql_query("SELECT * FROM kanji.kanji ORDER BY lesson ASC, kanji_id ASC") or die(mysql_error());

$x = 1;
$prevlesson = "";

echo '
<table border="1">
<tr>';

while($row = mysql_fetch_assoc($result)){
echo '
<td align="center"><a href="explain.php?kanji_id=' . $row[kanji_id] . '">' . $row[kanji] . '</a></td>';

if ($row[lesson] != $prevlesson){
while ($x <= 15) {
echo "<td>&nbsp;</td>";
$x++;
}
$x = 1;
echo '
</tr>
<tr>';
} else {
$x++;
}

$prevlesson = $row[lesson];
}

echo "
</tr>
</table>";

?>[/code]

[realjumper]

Thanks....I'm still having the same problem though. It seems that the very first result for lesson 1 is alone in html row 1....with the rest of lesson 1 in html row 2....with the first of lesson 3 tagged on the end....etc etc !!! Weird huh? ;-)

[hitman6003]

can you post the first 5 or so rows of html?

[realjumper]

Here we go...I'm not sure if the Japanese kaji characters will display properly in this forum or nor....but anyway......

[code]

<table border="1">
<tr>
<td align="center"><a href="explain.php?kanji_id=1">日</a></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>
</tr>
<tr>
<td align="center"><a href="explain.php?kanji_id=2">月</a></td>
<td align="center"><a href="explain.php?kanji_id=3">木</a></td>
<td align="center"><a href="explain.php?kanji_id=4">山</a></td>
<td align="center"><a href="explain.php?kanji_id=5">川</a></td>
<td align="center"><a href="explain.php?kanji_id=6">田</a></td>
<td align="center"><a href="explain.php?kanji_id=7">人</a></td>
<td align="center"><a href="explain.php?kanji_id=8">口</a></td>
<td align="center"><a href="explain.php?kanji_id=9">車</a></td>
<td align="center"><a href="explain.php?kanji_id=10">門</a></td>
<td align="center"><a href="explain.php?kanji_id=11">火</a></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>
</tr>
<tr>
<td align="center"><a href="explain.php?kanji_id=12">水</a></td>
<td align="center"><a href="explain.php?kanji_id=13">金</a></td>
<td align="center"><a href="explain.php?kanji_id=14">土</a></td>
<td align="center"><a href="explain.php?kanji_id=15">子</a></td>
<td align="center"><a href="explain.php?kanji_id=16">女</a></td>
<td align="center"><a href="explain.php?kanji_id=17">学</a></td>
<td align="center"><a href="explain.php?kanji_id=18">生</a></td>
<td align="center"><a href="explain.php?kanji_id=19">先</a></td>
<td align="center"><a href="explain.php?kanji_id=20">私</a></td>
<td align="center"><a href="explain.php?kanji_id=21">一</a></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>
</tr>
<tr>
<td align="center"><a href="explain.php?kanji_id=22">二</a></td>
<td align="center"><a href="explain.php?kanji_id=23">三</a></td>
<td align="center"><a href="explain.php?kanji_id=24">四</a></td>
<td align="center"><a href="explain.php?kanji_id=25">五</a></td>
<td align="center"><a href="explain.php?kanji_id=26">六</a></td>
<td align="center"><a href="explain.php?kanji_id=27">七</a></td>
<td align="center"><a href="explain.php?kanji_id=28">八</a></td>
<td align="center"><a href="explain.php?kanji_id=29">九</a></td>
<td align="center"><a href="explain.php?kanji_id=30">十</a></td>
<td align="center"><a href="explain.php?kanji_id=31">百</a></td>
<td align="center"><a href="explain.php?kanji_id=32">千</a></td>
<td align="center"><a href="explain.php?kanji_id=33">万</a></td>
<td align="center"><a href="explain.php?kanji_id=34">円</a></td>
<td align="center"><a href="explain.php?kanji_id=35">年</a></td>
<td align="center"><a href="explain.php?kanji_id=36">上</a></td><td>&nbsp;</td>
</tr>
<tr>
<td align="center"><a href="explain.php?kanji_id=37">下</a></td>
<td align="center"><a href="explain.php?kanji_id=38">中</a></td>
<td align="center"><a href="explain.php?kanji_id=39">大</a></td>
<td align="center"><a href="explain.php?kanji_id=40">小</a></td>
<td align="center"><a href="explain.php?kanji_id=41">本</a></td>
<td align="center"><a href="explain.php?kanji_id=42">半</a></td>
<td align="center"><a href="explain.php?kanji_id=43">分</a></td>
<td align="center"><a href="explain.php?kanji_id=44">力</a></td>
<td align="center"><a href="explain.php?kanji_id=45">何</a></td>
<td align="center"><a href="explain.php?kanji_id=46">明</a></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>
</tr>
<tr>
<td align="center"><a href="explain.php?kanji_id=47">休</a></td>
<td align="center"><a href="explain.php?kanji_id=48">体</a></td>
<td align="center"><a href="explain.php?kanji_id=49">好</a></td>
<td align="center"><a href="explain.php?kanji_id=50">男</a></td>
<td align="center"><a href="explain.php?kanji_id=51">林</a></td>
<td align="center"><a href="explain.php?kanji_id=52">森</a></td>
<td align="center"><a href="explain.php?kanji_id=53">間</a></td>
<td align="center"><a href="explain.php?kanji_id=54">畑</a></td>
<td align="center"><a href="explain.php?kanji_id=55">岩</a></td>
<td align="center"><a href="explain.php?kanji_id=56">目</a></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>
</tr>

[/code]

FWIW....kanji_id 1 - 10 is lesson 1
          kanji_id 11- 20 is lesson 2
          kanji_id  21 - 35 is lesson 3
          kanji_id 36 - 45 is lesson 4
          kanji_id 46 55 is lesson 5

[realjumper]

Narrowing it down......it seems that '$prevlesson = $row[lesson];' is somehow part of the cause but I'm not sure......

[realjumper]

If I change the query to simplify things like this...

[code]
$result = mysql_query("SELECT * FROM kanji.kanji where kanji_id < '4' ORDER BY lesson ASC, kanji_id ASC") or die(mysql_error());
[/code]


The result should be.....

[code]
<table border="1">
<tr>
<td align="center"><a href="explain.php?kanji_id=1">日</a></td>
<td align="center"><a href="explain.php?kanji_id=2">月</a></td>
<td align="center"><a href="explain.php?kanji_id=3">木</a></td>
</tr>
</table>
[/code]

But the result is......

[code]
<table border="1">
<tr>
<td align="center"><a href="explain.php?kanji_id=1">日</a></td>
</tr>
<tr>
<td align="center"><a href="explain.php?kanji_id=2">月</a></td>
<td align="center"><a href="explain.php?kanji_id=3">木</a></td>
</tr>
</table>
[/code]

And I just can't see a new table row has started. Can anyone shed any light on this please?

Thanks

[realjumper]

Well...I'm going home now and I'm no closer to resolving this. I have taken home the code from GingerRobot and hitman6003. Hopefully we can resolve this pesky issue! :-)

[GingerRobot]

Oh i got it, didn't assign anything to the prevlesson variable to start with so the first time it runs it thinks its a new lesson

[code]
<?php
$result = mysql_query("SELECT * FROM kanji.kanji ORDER BY lesson ASC, kanji_id ASC") or die(mysql_error());

$x = 1;
$prevlesson = "";

echo '
<table border="1">
<tr>';
$prevlesson = '';
while($row = mysql_fetch_assoc($result)){
echo '
<td align="center"><a href="explain.php?kanji_id=' . $row[kanji_id] . '">' . $row[kanji] . '</a></td>';

if ($row[lesson] != $prevlesson && $prevlesson != ''){//make sure prevlesson has some contentes
while ($x <= 15) {
echo "<td>&nbsp;</td>";
$x++;
}
$x = 1;
echo '
</tr>
<tr>';
} else {
$x++;
}

$prevlesson = $row[lesson];
}

echo "
</tr>
</table>";

?>
[/code]

[realjumper]

Thank you for that....I reached the same conclusion but I couldn't get it right. This in part of the solution but part of the problem remains......at the end of the first html row, in the last cell, is what should be in the first cell of the 2nd html row.....and at the end of that row, is what should be in the first cell of the 3rd html row etc etc....like this....

[code]
<table border="1">
<tr>
<td align="center"><a href="explain.php?kanji_id=1">日</a></td>
<td align="center"><a href="explain.php?kanji_id=2">月</a></td>
<td align="center"><a href="explain.php?kanji_id=3">木</a></td>
<td align="center"><a href="explain.php?kanji_id=4">山</a></td>
<td align="center"><a href="explain.php?kanji_id=5">川</a></td>
<td align="center"><a href="explain.php?kanji_id=6">田</a></td>
<td align="center"><a href="explain.php?kanji_id=7">人</a></td>
<td align="center"><a href="explain.php?kanji_id=8">口</a></td>
<td align="center"><a href="explain.php?kanji_id=9">車</a></td>
<td align="center"><a href="explain.php?kanji_id=10">門</a></td>
<td align="center"><a href="explain.php?kanji_id=11">火</a></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>
</tr>
<tr>
<td align="center"><a href="explain.php?kanji_id=12">水</a></td>
<td align="center"><a href="explain.php?kanji_id=13">金</a></td>
<td align="center"><a href="explain.php?kanji_id=14">土</a></td>
<td align="center"><a href="explain.php?kanji_id=15">子</a></td>
<td align="center"><a href="explain.php?kanji_id=16">女</a></td>
<td align="center"><a href="explain.php?kanji_id=17">学</a></td>
<td align="center"><a href="explain.php?kanji_id=18">生</a></td>
<td align="center"><a href="explain.php?kanji_id=19">先</a></td>
<td align="center"><a href="explain.php?kanji_id=20">私</a></td>
<td align="center"><a href="explain.php?kanji_id=21">一</a></td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>
</tr>
<tr>
<td align="center"><a href="explain.php?kanji_id=22">二</a></td>
<td align="center"><a href="explain.php?kanji_id=23">三</a></td>
<td align="center"><a href="explain.php?kanji_id=24">四</a></td>
<td align="center"><a href="explain.php?kanji_id=25">五</a></td>
<td align="center"><a href="explain.php?kanji_id=26">六</a></td>
<td align="center"><a href="explain.php?kanji_id=27">七</a></td>
<td align="center"><a href="explain.php?kanji_id=28">八</a></td>
<td align="center"><a href="explain.php?kanji_id=29">九</a></td>
<td align="center"><a href="explain.php?kanji_id=30">十</a></td>
<td align="center"><a href="explain.php?kanji_id=31">百</a></td>
<td align="center"><a href="explain.php?kanji_id=32">千</a></td>
<td align="center"><a href="explain.php?kanji_id=33">万</a></td>
<td align="center"><a href="explain.php?kanji_id=34">円</a></td>
<td align="center"><a href="explain.php?kanji_id=35">年</a></td>
<td align="center"><a href="explain.php?kanji_id=36">上</a></td><td>&nbsp;</td>
</tr>
[/code]

kanji_id=11 should be in the first cell of the next row
kanji_id 21 should be in the first cell of the next row
kanji_id 36 should be in the first cell of the next row etc etc etc

Thanks for any help....I'll also see what I can come up with

:)

[GingerRobot]

Ah, i know, its just the order its done. It checks to see if it should start a new row after it echos the cell...

[code]
<?php
$result = mysql_query("SELECT * FROM kanji.kanji ORDER BY lesson ASC, kanji_id ASC") or die(mysql_error());

$x = 1;
$prevlesson = "";

echo '
<table border="1">
<tr>';
$prevlesson = '';
while($row = mysql_fetch_assoc($result)){
if ($row[lesson] != $prevlesson && $prevlesson != ''){//make sure prevlesson has some contentes
while ($x <= 15) {
echo "<td>&nbsp;</td>";
$x++;
}
$x = 1;
echo '
</tr>
<tr>';
                          echo '<td align="center"><a href="explain.php?kanji_id=' . $row[kanji_id] . '">' . $row[kanji] . '</a></td>';
} else {
                        echo '<td align="center"><a href="explain.php?kanji_id=' . $row[kanji_id] . '">' . $row[kanji] . '</a></td>';
$x++;
}

$prevlesson = $row[lesson];
}

echo "
</tr>
</table>";

?>
[/code]

Try that

[realjumper]

That works perfectly...thank you :)

Just so that I understand this though.....if you don't mind, when you say "It checks to see if it should start a new row after it echos the cell", how does this work? Sorry if I sound 'thick'!.......I just want to understand this properly so I don't get sucked into this inefficient query trap again.

Thank you :)

[GingerRobot]

Of course i dont mind...

Basically, i switched the order of the code around.

Previously, it would echo the table cell with the link to the kanji in first, then check to see if a new row was needed, if so it would end the current row and create the new one. If you imagine the last kanji in a lesson, it would have already echoed the cell with the kanji in BEFORE it created the new row, resulting in the first kanji of a new lesson being on the previous row.

I have swapped it around so that it checks if a new row is requried and then echoes the the table cell containing the kanji.

Hope thats clear, if not just say :p

[realjumper]

Ahhhh....got it!!!!

Thank you very much for your patience and help, I really appreciate it  :)