Page 1 of 1

Help me reduce the load on sql db

Posted: Thu Jun 11, 2009 4:25 am
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

Re: Help me reduce the load on sql db

Posted: Thu Jun 11, 2009 4:38 am
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/>';
}
 

Re: Help me reduce the load on sql db

Posted: Thu Jun 11, 2009 4:43 am
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.