MYSQL COUNT function (somewhat like Odesk)

[bateati]

mysql version : 5.1.52

 

What I'm trying to do:

User comes to my browser page that has a list of category's. The user can sort through the listings in my categories by state or city.

 

By using the <select> and $_POST to provide my mysql query with the proper data,

The COUNT function will let my users know how many listings are in each category, according to the state and city the user supplied.

 

I'm trying to use the mysql COUNT function to count how many entries I have in each column. In which I can't solve myself.

 

I want it like this http://www.odesk.com/jobs/?clear_button=1

Only difference is, I also will have a state/city search bar at top to sort the data more specifically.

 

 

So far I have:

 

table = postlisting

column = category

 

$result = mysql_query("SELECT COUNT(*) FROM postlisting ");

while ($row= mysql_fetch_array($result)){

$row['COUNT(category)']

}

 

Now if I have a column called "category" and in it is listed red 3 times, blue 2 times, green 4 times

 

How do I get it to list

 

Red(3)

Blue(2)

Green(4)

 

I am trying to describe my issue the best I can.

If I need to provide any more information please ask.

 

Thank you in advance for anyone who may be able to help me

 

 

 

[bateati]

I suppose the main problem is I cannot figure out how to display the specific rows.

 

when I add

 

$row['COUNT(*)']

 

It displays 31 because I have 4 entries in all in my database.

 

I just need to know how to have it display

1

 

and

 

3

 

in different parts of my page, where I can control the column the number is being displayed of.

[bateati]

$query = "SELECT category, COUNT(*) FROM postlisting GROUP BY category";
$result = mysqli_query($dbc,$query);

while($row= mysqli_fetch_array($result))
{
$array[]= $row[COUNT(category)];
}

 

Here is what I have now.

I can pick certain lines from the array, technically I got it to work.

 

But the problem is if somebody was to browse a city that had 0 listings in the category, it would throw my array count off.

 

I'm trying to keep the search function dynamic.

 

5$ USD via paypal to whoever can help me.

 

If my posts aren't making it exactly clear what I want please ask.

 

This little function is stopping me from launching my site, very annoying.

 

Thank you in advanced

[fenway]

If you want to pay, donate to the forums.

[ngreenwood6]

I am a little confused as to what you are trying to do but I am assuming that you are trying to do something like this:

 

Category(10)
Another Category(20)

 

So assuming that you are trying to list categories and show how many entries are in that category I would assume you have an array of the categories with possibly an id for the category. So in assuming that this would be the code that I would write:

 

//this is just here because I am assuming you have a categories array
$categories = array();

//make sure there are categories
if(is_array($categories)){
foreach($categories as $cat){
//get the number of entries in category
$result = mysql_query("SELECT COUNT(*) FROM entries");
$count = mysql_result($result,0);

//echo the category name with the number of entries in there
echo $cat['name'].'('.$count.')<br />';
}
}

 

Now if you really wanted to be good at what you do I would do it all in just one query:

 

SELECT *,(SELECT COUNT(*) FROM entries WHERE category_id=categories.id) AS entry_count FROM categories

 

This will select all of the data from each category and it will provide a key in the array for the number of entries with that category id. So you could then echo the count by doing something like this $categories['entry_count'];

 

Hopefully that was clear enough for you and was what you were trying to accomplish. If not let me know and maybe I can help further.