Jump to content

How to make query efficient?


realjumper

Recommended Posts

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

[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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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

:)
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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 :)
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.