COUNT() issues, similar to ODESK.com DMOZ.org

[bateati]

Hello everybody,

 

I'm trying to write some code similar to Odesk.com or DMOZ.ORG.

 

I need to count the number of listings in each category.  Look here "http://ibatty.org/browse.php" to get a better idea of what I'm doing. That is the page I'm trying to add this function to.

 

I've been having major issues with the COUNT function.

 

$query = "SELECT category, COUNT(*) FROM postlisting WHERE category='billboards'";
        $result = mysqli_query($dbc,$query);
        while($row = mysqli_fetch_array($result))
        {
        echo $row[COUNT(category)];
        }

 

As you can see this works, but I'm trying to add State, Area code and Zip functuality to search through the data more precisely. This has been giving me problems though and the way I currently have it setup . . .

I'd have 25+ SELECT COUNT() querys going on 1 page. (I don't know if that is very good?)

 

5$ via paypal to whoever can help me solve this.

 

If I did not explain what I needed very well, please ask questions for me to clarify.

 

Thank you in advance

[gizmola]

To get all the counts by category:

 

SELECT category, COUNT(*) as countof FROM postlisting GROUP BY category.

 

This will still require a tablescan of the entire postlisting table.

 

The problem of counts is a common one.  For any site with sufficient size of database and user traffic, caching of things like statistics is typically cached for a period of time.  Take a look at youtube as an example, and notice that when you watch a video it doesn't immediately show you "views+1". 

[bateati]

The problem I had with GROUP BY was I couldn't find the code to output each category listing specifically.

 

I figured out I could store all the information into an array and it would work. . .

but if a selection was made that had 0 data in it,

my array count would be screwed up and it didn't work correctly.

 

 

An example of what I want

    * Accounting (2,854)

    * Business and Society (45)

    * Cooperatives (66)

    * Customer Service (275)

    * E-Commerce (732)

    * Education and Training (768)

    * Employment (1,036)

    * Human Resources (873)

 

A more specific link of how its being done

http://www.dmoz.org/Business/

[gizmola]

The query i provided gives you the category and the count in a single result set.  Fetch that and format it, and you have your result.  However itt will not display any category that has no postlistings.  You can remedy that by LEFT OUTER JOINing the category table to the postlisting table. 

 

I am of course, assuming you have a category table, although it's not clear to me what the structure is.  In your sample query it implies that in the "postlisting" table category is a varchar() that has the actual name of the category in it rather than the category table having a key, and postlisting storing that as a foreign key.

 

Using the GROUP BY query I provided, you would simply fetch the results.  You can get them in category name order by tacking on an ORDER BY category at the end, as well.  Using  a while ($row = mysql_fetch_assoc()) loop is all you really need here. 

 

However, in regards to dmoz and other catalogs like that, there is an entire level of complexity involved in the fact that they are hierarchical trees.