Help me reduce the load on sql db
Posted: Thu Jun 11, 2009 4:25 am
Hi everyone, any help on this would be greatly appreciated.
So I have a page that displays all records held in a table and groups them. All records starting with the letter A inside a div, then all starting with B in another div and so on to Z. It is all working however to get it working with my limited knowledge of php and mysql, I think it puts to much of a load on the database as it calls 26 seperate select commands one for each letter of the alphabet. What do you think is it to resource heavy calling this many times to the database. Heres the code...
Any advice on how to stream line this code would be greatly appreciated if you need any more info I am more than happy to oblige.
Kai
So I have a page that displays all records held in a table and groups them. All records starting with the letter A inside a div, then all starting with B in another div and so on to Z. It is all working however to get it working with my limited knowledge of php and mysql, I think it puts to much of a load on the database as it calls 26 seperate select commands one for each letter of the alphabet. What do you think is it to resource heavy calling this many times to the database. Heres the code...
Code: Select all
<?php
include("dbconnectioninfo.php");
$con = mysql_connect(localhost,$username,$password);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("db_", $con);
$a = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'a%' ORDER BY title ASC");
$b = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'b%' ORDER BY title ASC");
$c = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'c%' ORDER BY title ASC");
$d = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'd%' ORDER BY title ASC");
$e = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'e%' ORDER BY title ASC");
$f = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'f%' ORDER BY title ASC");
$g = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'g%' ORDER BY title ASC");
$h = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'h%' ORDER BY title ASC");
$i = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'i%' ORDER BY title ASC");
$j = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'j%' ORDER BY title ASC");
$k = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'k%' ORDER BY title ASC");
$l = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'l%' ORDER BY title ASC");
$m = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'm%' ORDER BY title ASC");
$n = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'n%' ORDER BY title ASC");
$o = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'o%' ORDER BY title ASC");
$p = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'p%' ORDER BY title ASC");
$q = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'q%' ORDER BY title ASC");
$r = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'r%' ORDER BY title ASC");
$s = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 's%' ORDER BY title ASC");
$t = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 't%' ORDER BY title ASC");
$u = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'u%' ORDER BY title ASC");
$v = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'v%' ORDER BY title ASC");
$w = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'w%' ORDER BY title ASC");
$x = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'x%' ORDER BY title ASC");
$y = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'y%' ORDER BY title ASC");
$z = mysql_query("SELECT DISTINCT title FROM db_main_details WHERE title LIKE 'z%' ORDER BY title ASC");
echo "<div class='alphcont'><h1>A</h1><br />";
while($result = mysql_fetch_array( $a )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>B</h1><br />";
while($result = mysql_fetch_array( $b )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>C</h1><br />";
while($result = mysql_fetch_array( $c )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>D</h1><br />";
while($result = mysql_fetch_array( $d )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>E</h1><br />";
while($result = mysql_fetch_array( $e )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>F</h1><br />";
while($result = mysql_fetch_array( $f )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>G</h1><br />";
while($result = mysql_fetch_array( $g )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>H</h1><br />";
while($result = mysql_fetch_array( $h )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>I</h1><br />";
while($result = mysql_fetch_array( $i )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>J</h1><br />";
while($result = mysql_fetch_array( $j )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>K</h1><br />";
while($result = mysql_fetch_array( $k )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>L</h1><br />";
while($result = mysql_fetch_array( $l )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>M</h1><br />";
while($result = mysql_fetch_array( $m )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>N</h1><br />";
while($result = mysql_fetch_array( $n )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>O</h1><br />";
while($result = mysql_fetch_array( $o )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>P</h1><br />";
while($result = mysql_fetch_array( $p )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>Q</h1><br />";
while($result = mysql_fetch_array( $q )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>R</h1><br />";
while($result = mysql_fetch_array( $r )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>S</h1><br />";
while($result = mysql_fetch_array( $s )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>T</h1><br />";
while($result = mysql_fetch_array( $t )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>U</h1><br />";
while($result = mysql_fetch_array( $u )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>V</h1><br />";
while($result = mysql_fetch_array( $v )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>W</h1><br />";
while($result = mysql_fetch_array( $w )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>X</h1><br />";
while($result = mysql_fetch_array( $x )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>Y</h1><br />";
while($result = mysql_fetch_array( $y )) {
echo $result['title']."<br />";
}
echo "</div>";
echo "<div class='alphcont'><h1>Z</h1><br />";
while($result = mysql_fetch_array( $z )) {
echo $result['title']."<br />";
}
echo "</div>";
mysql_close($con);
?>Kai