Help me reduce the load on sql db

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
vtvstv
Forum Newbie
Posts: 10
Joined: Sun May 24, 2009 3:19 am

Help me reduce the load on sql db

Post by vtvstv »

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...

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);
?>
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
globezone
Forum Newbie
Posts: 7
Joined: Wed Jun 10, 2009 4:57 am

Re: Help me reduce the load on sql db

Post by globezone »

Code: Select all

 
$query = mysql_query('SELECT * FROM `db_main_details` ORDER BY `title`') or die(mysql_error());
$current_char='';
while($row = mysql_fetch_assoc($query)){
    $tmp_char = strtolower(substr($row['title'], 0, 1));
    if($tmp_char!=$current_char){
        $current_char=$tmp_char;
        echo '<h3>'.$current_char.'</h3><br/>';
    }
    echo $row['title'].'<br/>';
}
 
vtvstv
Forum Newbie
Posts: 10
Joined: Sun May 24, 2009 3:19 am

Re: Help me reduce the load on sql db

Post by vtvstv »

globezone wrote:

Code: Select all

 
$query = mysql_query('SELECT * FROM `db_main_details` ORDER BY `title`') or die(mysql_error());
$current_char='';
while($row = mysql_fetch_assoc($query)){
    $tmp_char = strtolower(substr($row['title'], 0, 1));
    if($tmp_char!=$current_char){
        $current_char=$tmp_char;
        echo '<h3>'.$current_char.'</h3><br/>';
    }
    echo $row['title'].'<br/>';
}
 
Your a star I've been tryin to get that all night and you did it in 10 mins :banghead: Thanks for the help mate.
Post Reply