PHP/MYSQL COUNT Function optimzation help

bateati · August 30, 2011

I know there has to be a better way to count how many entries I have in my mysql db than what I'm currently using. Let me just show you what I'm trying to do and it will make better sense.

http://batads.com/display.php

You see how I am echoing the number of each category on one page?

I am sure the current script I am using is insanely inefficient and as my DB grows I think it will cause me problems. Any suggestions on how to do this more efficiently?

Below is what is currently running that page.

Thank you.


<td><h4>Public Ads</h4><br>
<?php echo '<a href="browse1.php?cat=1">Bill Boards<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='1'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }?>
<?php echo '<a href="browse1.php?cat=2">Transportation<a/>';
 $query = "SELECT category, COUNT(*) FROM postlisting WHERE category='2'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }?>
<?php echo '<a href="browse1.php?cat=4">Shopping Centers<a/>';
 $query = "SELECT category, COUNT(*) FROM postlisting WHERE category='4'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=3">Posters / Signs<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='3'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }?>
        <?php echo '<a href="browse1.php?cat=5">Bus Stops<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='5'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }?>
<?php echo '<a href="browse1.php?cat=6"> Other-Public<a/>';
	 $query = "SELECT category, COUNT(*) FROM postlisting WHERE category='6'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }  ?></td>
	<td><h4>Event Sponsorship</h4><br>
<?php echo '<a href="browse1.php?cat=7">Concerts<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='7'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=8">Fairs<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='8'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }  ?>
<?php echo '<a href="browse1.php?cat=10">Sports<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='10'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } 
 ?>
<?php echo '<a href="browse1.php?cat=9">Trade Shows<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='9'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }  ?>
<?php echo '<a href="browse1.php?cat=11">Other-Event<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='11'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }  ?></td>

	<td><h4>Online Ads</h4><br>
<?php echo '<a href="browse1.php?cat=12">Banner Space<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='12' ";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=13">Social Media<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='13' ";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=14">Email Lists<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='14'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=15">Blog Articles<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='15'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=16">Ezine Articles<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='16'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1area.php?cat=17">Other-Online<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='17'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?></td>
</tr>
<tr>
	<td><h4>Print Ads</h4><br>
<?php echo '<a href="browse1.php?cat=18">Magazine<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='18'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=19">Newspaper<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='19'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=20">Brochure<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='20'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
  
        <?php echo '<a href="browse1.php?cat=21">Press Release Print<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='21'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=22">Other-Print<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='22'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?></td> 
	<td><h4>Broadcasting Ads</h4><br>
<?php echo '<a href="browse1.php?cat=23">TV / Commercial<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='23'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=24">Radio<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='24'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
        <?php echo '<a href="browse1.php?cat=27">Movies<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='27'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
        <?php echo '<a href="browse1.php?cat=25">Mobile Phone<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='25'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=28">Cinema<a/>'; 
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='28'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }?>
        <?php echo '<a href="browse1.php?cat=26">Press Release TV<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='26'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=29">Other-Broadcast<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='29' ";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?></td>
        		
	<td><h4>Commercial Space</h4><br>
<?php echo '<a href="browse1.php?cat=33">Private Property<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='33'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=34">Vehicle Wrap<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='34'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
        <?php echo '<a href="browse1.php?cat=35">Front Yard<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='35'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
        <?php echo '<a href="browse1.php?cat=36">Self Promotion<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='36'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=37">Other-Commercial<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='37'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?></td>
</tr>
<tr>
	<td><h4>Business Ads</h4><br>
<?php echo '<a href="browse1.php?cat=30">Window Display<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='30'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=31">Interior Ads<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='31'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=32">Point of Sale Ads<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='32'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?>
<?php echo '<a href="browse1.php?cat=41">Other-Business <a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='41'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; } ?></td>
	</td>

	<td><h4>Misc</h4><br>
<?php echo '<a href="browse1.php?cat=38">Creative Advertising<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='38'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }  ?>
        <?php echo '<a href="browse1.php?cat=39">Video Games<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='39'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }  ?>
<?php echo '<a href="browse1.php?cat=40">Other-Misc<a/>';
$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='40'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {echo '('.$row[COUNT(category)].')<br>' ; }  ?>
	</td>


</tr>
</table>

trq · August 30, 2011

Sorry, but that is probably some of the worst code I have ever seen.

You can use one query and simply GROUP BY category.

bateati · August 30, 2011

Sorry, but that is probably some of the worst code I have ever seen.

You can use one query and simply GROUP BY category.

Yes I know it is horrible, but at the time I just needed something that would work. I think the problem with GROUP BY was if one of the categories was 0 it would screw up the order in which the numbers were displayed in. But maybe I wasn't using it in the way you're suggesting.

Could you give me a quick example of what you mean by that?

trq · August 30, 2011

$sql = "SELECT category, COUNT(category) AS cnt FROM postlisting GROUP BY category";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {
      echo "There are {$row['cnt']} items in {$row['category']}<br />";
    }
  }
}

bateati · August 30, 2011

Thanks thorpe using your code I was able to figure it out.

In case anybody is curious, here is what I did.

include "array2.php";
  
$sql = "SELECT category, COUNT(category) AS cnt FROM postlisting GROUP BY category";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {

$c = $row['category'] ;
$t = $row['cnt'];


$inf2[$c] =$t;


                 }
  }
}

and array1.php looks like this, in order to display a 0 if no count comes back true.

<?php

	         $inf2["0"] = "";
		 $inf2["1"] = "0";
		 $inf2["2"] = "0";
		 $inf2["3"] = "0";
		 $inf2["4"] ="0" ;
		 $inf2["5"] ="0";
		 $inf2["6"] ="0";
		 $inf2["7"] = "0" ;
		 $inf2["8"] = "0";
		 $inf2["9"] ="0"  ;
		 $inf2["10"] = "0" ;
		 $inf2["11"] ="0";
		 $inf2["12"] ="0" ;
		 $inf2["13"] ="0" ;
		 $inf2["14"] ="0" ;
		 $inf2["15"] = "0" ;
		 $inf2["16"] = "0";
		 $inf2["17"] = "0";
		 $inf2["18"] ="0" ;
		 $inf2["19"] ="0" ;
		 $inf2["20"] = "0"		;
		 $inf2["21"] ="0";
		 $inf2["22"] = "0" ;
		 $inf2["23"] = "0" ;
		 $inf2["24"] ="0" ;
		 $inf2["25"] ="0"		 ;
		 $inf2["26"] = "0";
		 $inf2["27"] ="0" ;
                         $inf2["28"] = "0";
                 $inf2["29"] = "0";
                 $inf2["30"] ="0";
		 $inf2["31"] = "0";
		 $inf2["32"] ="0";
                 $inf2["33"] = "0";
                         $inf2["34"] = "0";
                 $inf2["35"] ="0" ;
                 $inf2["36"] = "0";
		 $inf2["37"] ="0";
		 $inf2["38"] = "0" ;
                         $inf2["39"] ="0";
		 $inf2["40"] = "0"  ;
	         $inf2["41"] = "0"  ;
	         
	         ?>

To display, i'm doing this

<td><h4>Public Ads</h4><br>
<?php echo '<a href="browse1.php?cat=1">Bill Boards<a/>';
        echo "($inf2[1])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=2">Transportation<a/>';
 echo "($inf2[2])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=4">Shopping Centers<a/>';
echo "($inf2[4])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=3">Posters / Signs<a/>';
        echo "($inf2[3])<br>"  ;  ?>
        <?php echo '<a href="browse1.php?cat=5">Bus Stops<a/>';
echo "($inf2[5])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=6"> Other-Public<a/>';
        echo "($inf2[6])<br>"  ;  ?>   		 
	<td><h4>Event Sponsorship</h4><br>
<?php echo '<a href="browse1.php?cat=7">Concerts<a/>';
        echo "($inf2[7])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=8">Fairs<a/>';
        echo "($inf2[8])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=10">Sports<a/>';
        echo "($inf2[10])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=9">Trade Shows<a/>';
         echo "($inf2[9])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=11">Other-Event<a/>';
 echo "($inf2[11])<br>"  ;  ?>

	<td><h4>Online Ads</h4><br>
<?php echo '<a href="browse1.php?cat=12">Banner Space<a/>';
 echo "($inf2[12])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=13">Social Media<a/>';
 echo "($inf2[13])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=14">Email Lists<a/>';
 echo "($inf2[14])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=15">Blog Articles<a/>';
 echo "($inf2[15])<br>"  ;  ?>
<?php echo '<a href="browse1.php?cat=16">Ezine Articles<a/>';
 echo "($inf2[16])<br>"  ;  ?>
<?php echo '<a href="browse1area.php?cat=17">Other-Online<a/>';
echo "($inf2[17])<br>"  ;  ?>
</tr>

Way more efficient than before

Thankyou again thorpe

amg182 · August 30, 2011

Interesting, good job guys.

Quick question but, would the actual performance of the page be increased? or is it just to tidy the actual script up?

Curious cause my own script is a bit on the sloppy side, but works a treat... if i tidied it up a little would the actual page perform better? or are we just looking for pretty scripting!

Thanks

xyph · August 30, 2011

It's far more efficient.

jcbones · August 31, 2011

Is your category names held in the database? It would make it much easier for you to code, because you could reduce a lot of the hand coding.

<?php
include "array2.php";

$break_ad_selection = array(1=>'Public Ads',7=>'Event Sponsorship',12=>'Online Ads'); //<-lines where you want your new columns.
$categories = array(1=>'Bill Boards',2=>'Transportation',3=>'Shopping Centers',4=>'Posters / Signs',5=>'Bus Stops',6=>'Other-Public', //<-From database.
						7=>'Concerts',8=>'Fairs',9=>'Trade Shows',10=>'Sports',11=>'Other-Event',12=>'Banner Space',13=>'Social Media',
						14=>'Email Lists',15=>'Blog Articles',16=>'Ezine Articles',17=>'Other-Online');
  
$sql = "SELECT category, COUNT(category) AS cnt FROM postlisting GROUP BY category";
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {
	$c = $row['category'] ;
	$t = $row['cnt'];
	if(array_key_exists($c,$break_ad_selection)) { // if we want a new column in the current line:
                        echo ($c > 1) ? '</td>' : NULL; //if number is greater than 1, we need to end the previous column.
		echo '<td><h4>' . $break_ad_selection[$c] . '</h4><br />'; //print out column title.
	}
	if(array_key_exists($c,$categories)) { //if there is a name for the category.
		echo '<a href="browse1.php?cat=' . $c . '">' . $categories[$c] . "<a/>($t)<br />\n"; //print it.
	}
    }
echo '</td></tr>'; //end our column, and our row.
  }
}

bateati · August 31, 2011

Is your category names held in the database? It would make it much easier for you to code, because you could reduce a lot of the hand coding.

Categories in my database are defined by numbers and I use this array if I want to display the name.

 <?php

		 $inf1["1"] = "Billboards ";
		 $inf1["2"] = "Transportation";
		 $inf1["3"] = "Poster / Signs  ";
		 $inf1["4"] ="Shopping Centers" ;
		 $inf1["5"] ="Bus Stops";
		 $inf1["6"] ="Other-Public Advertising";
		 $inf1["7"] = "Concerts" ;
		 $inf1["8"] = "Fair";
		 $inf1["9"] ="Trade Shows "  ;
		 $inf1["10"] = "Sports " ;
		 $inf1["11"] ="Other-Events";
		 $inf1["12"] ="Banner Space " ;
		 $inf1["13"] ="Social Media " ;
		 $inf1["14"] ="Email Lists 	" ;
		 $inf1["15"] = "Blog Articles  " ;
		 $inf1["16"] = "Ezine Articles ";
		 $inf1["17"] = "Other-Online Advertising ";
		 $inf1["18"] ="Magazine  " ;
		 $inf1["19"] ="Newspaper " ;
		 $inf1["20"] = "Brochure"		;
		 $inf1["21"] ="Press Release Print ";
		 $inf1["22"] = "Other-Print Advertising " ;
		 $inf1["23"] = "TV / Commercial" ;
		 $inf1["24"] ="Radio " ;
		 $inf1["25"] ="Mobile Phone"		 ;
		 $inf1["26"] = "Press Release tv";
		 $inf1["27"] ="Movies " ;
                         $inf1["28"] = "Cinema ";
                 $inf1["29"] = "Other-Broadcast";
                 $inf1["30"] ="Window Display";
		 $inf1["31"] = "Interior Advertising";
		 $inf1["32"] ="Point of Sale   ";
                 $inf1["33"] = "Private Property";
                         $inf1["34"] = "Vehicle Wrap";
                 $inf1["35"] ="Frontyard " ;
                 $inf1["36"] = "Self Promotion  ";
		 $inf1["37"] ="Other-Commercial Space";
		 $inf1["38"] = "Creative Advertising " ;
                         $inf1["39"] ="Video Game ";
		 $inf1["40"] = "Other-Misc "  ;
	         $inf1["41"] = "Other-Business"  ;

             

		 php?>

I'm trying to make my database as small as possible and it has been easier to manage this way because if one of the phrases wasn't spelled exactly right, I'd have problems finding data in search queries.

Here is a quick example of what I do to change from number to phrase.


$ar = $row['category'];
     include "array.php";


echo 'Category:'. $inf1[$ar] .'<br>';

bateati · August 31, 2011

Interesting, good job guys.

Quick question but, would the actual performance of the page be increased? or is it just to tidy the actual script up?

Curious cause my own script is a bit on the sloppy side, but works a treat... if i tidied it up a little would the actual page perform better? or are we just looking for pretty scripting!

Thanks

I just decreased my queries by 40 each time that page loads, which would make my site extremely more efficient. As it was, I probably would of crashed my site if I had heavy traffic.

jcbones · August 31, 2011

The code I provided could be retrofitted to help you reduce the handcoding needed. Just change all references to $categories to $infl.

trq · August 31, 2011

I would seriously consider creating a categories table then simply joining on that to get your category names. It would be simple, easier to add new categories and practically zero overhead on your query.

trq · August 31, 2011

I should probably post an example:

$sql = "
  SELECT category, COUNT(category) AS cnt, name
  FROM postlisting
  LEFT JOIN categories ON (postlisting.category = categories.id)
  GROUP BY category
";

This assumes a new categories table that holds both an id (relating to postlisting) and a name.

Sign In

PHP/MYSQL COUNT Function optimzation help

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information