MySQL COUNT question - Need a guru

Question

sallecpt 0 Junior Poster in Training

14 Years Ago

Hey guys.

First of all, its been 2 days, and already 2:30am in the morning. I've eaten a steel fork, jumped over the moon, broke an arm, and cant get it to work. Will somebody please assist me in this;

The end result sound simple, and maybe it is, but I dont get it.

Okay, let me start.

I am building a small site, where local people can post ads in categories. Example :

LOCATION
--City One (2)
--City Two (4)
-- City Three (7)

Dwelling Type
--Townhouse (1)
--Estate (4)
--Land (8)

Now the only thing that I need to achieve, is to count and echo the total of SQL entries in each sub category, next to each location.

I am using about 4 SQL table,s but here is the important ones. I've joint these tables in all possible ways, and tried everything.

CREATE TABLE IF NOT EXISTS `item` (
  `itemID` int(11) NOT NULL AUTO_INCREMENT,
  `adID` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `CreationDate` date NOT NULL,
  `Expiration` date NOT NULL,
  `Views` int(11) NOT NULL DEFAULT '1',
  `Responses` int(11) NOT NULL DEFAULT '0',
  `UserEmail` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `IP` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Title` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Promote` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'None',
  `catID` int(11) NOT NULL DEFAULT '0',
  `SEO` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`itemID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

INSERT INTO `item` (`itemID`, `adID`, `CreationDate`, `Expiration`, `Views`, `Responses`, `UserEmail`, `IP`, `Title`, `Promote`, `catID`, `SEO`, `Description`) VALUES
(6, '20100406192824830999764294722976', '2010-05-10', '2010-07-25', 1, 1, 'vanwykfj@gmail.com', '::1', 'Big home to let', 'None', 1, 'big-home-to-let', 'this is a big home to let in, lets say, new york, cause you wont recognise my small-town towns here'),
(7, '20100406192916499097777902080358', '2010-05-10', '2010-07-25', 1, 1, 'vanwykfj@gmail.com', '::1', 'Medium home to let', 'None', 1, 'medium-home-to-let', 'this is a medium home to let in, lets say, Chicago'),
(5, '20100406192731929426053392405412', '2010-05-10', '2010-07-25', 1, 1, 'vanwykfj@gmail.com', '::1', 'Small home to let', 'None', 1, 'small-home-to-let', 'this is a small home to let');

CREATE TABLE IF NOT EXISTS `itemfield` (
  `fID` int(11) NOT NULL AUTO_INCREMENT,
  `adID` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `fieldID` int(11) NOT NULL DEFAULT '1',
  `FieldValue` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `catID` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`fID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

INSERT INTO `itemfield` (`fID`, `adID`, `fieldID`, `FieldValue`, `catID`) VALUES
(20, '20100406192916499097777902080358', 1, '3', 1),
(19, '20100406192916499097777902080358', 2, '2', 1),
(18, '20100406192824830999764294722976', 8, 'New York', 1),
(17, '20100406192824830999764294722976', 1, '2', 1),
(16, '20100406192824830999764294722976', 2, '1', 1),
(15, '20100406192731929426053392405412', 8, 'New York', 1),
(14, '20100406192731929426053392405412', 1, '2', 1),
(13, '20100406192731929426053392405412', 2, '1', 1),
(21, '20100406192916499097777902080358', 8, 'Chicago', 1);

--> I'm trying to keep it short!!! Almost done.... So here is the current code - well, one of the many tries - that I tried.

<?php require_once('../Connections/classi.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="common.css" rel="stylesheet" type="text/css" />
<?php
mysql_select_db($database_classi, $classi);
$query_filters = "SELECT Title FROM filter WHERE catID = '".$_GET['catID']."' ORDER BY Title DESC";
$filters = mysql_query($query_filters, $classi) or die(mysql_error());
$row_filters = mysql_fetch_assoc($filters);
$totalRows_filters = mysql_num_rows($filters);

?>
</head>

<body>
<table width="270" border="0" cellpadding="0" cellspacing="0">
  <tr>
    <td><div class="filter_top">Your total ads found</div></td>
  </tr>
<?php
do { ?>
  <tr>
    <td><div class="filter_title"><?php echo $row_filters['Title']; ?></div></td>
  </tr>
  <tr>
    <td><div class="filter_subtitle">
<?php
$query_subfilters = "SELECT * FROM filter WHERE Title = '".$row_filters['Title']."' ORDER BY Title DESC";
$subfilters = mysql_query($query_subfilters, $classi) or die(mysql_error());
$row_subfilters = mysql_fetch_assoc($subfilters);
$totalRows_subfilters = mysql_num_rows($subfilters);
?>
<?php 
$arr = explode(",", $row_subfilters['Values']);
reset($arr);
foreach ($arr as $line)
{
mysql_select_db($database_classi, $classi);
$query_countitems = "SELECT fID FROM itemfield WHERE FieldValue = '$line' AND catID = '".$_GET['catID']."'";
$countitems = mysql_query($query_countitems, $classi) or die(mysql_error());
$totalRows_countitems = mysql_num_rows($countitems);

 echo $line.' ('.$totalRows_countitems.')<br>'; }
?>
    </div></td>
  </tr>
  <tr>
    <td><div class="filter_hr"></div></td>
  </tr>
<?php } while ($row_filters = mysql_fetch_assoc($filters)); ?>
  <tr>
    <td><div class="filter_bottom"></div></td>
  </tr>
</table>
</body>
</html>

#########################################

I will so appreciate anybody's help

:-/

ps: after lots and lots of gazzillions tries - i thought i got it to work - but only the first entry/location's total entries displays. and, i guess there might be a better, more improved and easier way?

thanx guys

mysql php

Edited 14 Years Ago by sallecpt because: n/a

3 Contributors
7 Replies
151 Views
3 Days Discussion Span
Latest Post 14 Years Ago Latest Post by sallecpt

cwarn23 387 Occupation: Genius

14 Years Ago

Do you mean to select from the database and count how many are selected. If that is the case then use the following:

<?php
$result=mysql_query('SELECT * FROM `table` WHERE `Location`="City1"') or die(mysql_error());
echo mysql_num_rows($result);

Hope that helps.

johny_d 23 Junior Poster in Training

14 Years Ago

Counting for locations:

SELECT COUNT(*), fieldID, fieldValue 
FROM itemfield 
WHERE fieldID = 8
GROUP BY fieldValue

just change the fieldID with the ID of other counting criteria (dwelling type, make, etc)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sallecpt 0 Junior Poster in Training · Answer 1 · 2010-04-07T07:22:55+00:00

DROP TABLE IF EXISTS `filter`;
CREATE TABLE IF NOT EXISTS `filter` (
  `filterID` int(11) NOT NULL AUTO_INCREMENT,
  `catID` int(11) NOT NULL DEFAULT '0',
  `ShowFilter` tinyint(1) NOT NULL DEFAULT '0',
  `Title` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Values` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`filterID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `filter` (`filterID`, `catID`, `ShowFilter`, `Title`, `Values`) VALUES
(1, 1, 1, 'Location', 'Windhoek, Swakopmund, Walvisbay, Langstrand, Tsumeb, Mariental, Keetmanshoop, Okahandja, Karibib, Grootfontein, Otavi, Hentiesbay, Otjiwarongo, Rundu, Katima Mulilo'),
(2, 1, 1, 'For Rent By', 'Owner, Agency'),
(3, 1, 1, 'Dwelling Type', 'House, Townhouse, Estate, Small Holding, Villa, Pondok, Flat / Apartment'),
(4, 2, 1, 'Make', 'Toyota, Izusu, GWM, Nissan, Volkswagen, Other');

sallecpt 0 Junior Poster in Training · Answer 2 · 2010-04-08T05:56:48+00:00

Hey guys.

First of all, thank you for your help. I've spent many many hours today, trying to get this thing to work.

I even tried all the methods on W3 Schools using COUNT - but it doesnt work. If I use the method as suggested by CWARN23, only the first 'filter' works.

I'm going to try a different angle of explanation;

I have a SQL table, with data, (cities) listed in the table, comma seperated. Like in; Memphis, Robinsonville, Chicago etc etc.

So what I want to achieve, is to explore the values in the single field, and list it below eachother - and I have achieved that by using the following :

$arr = explode(",", $row_subfilters['Values']);
reset($arr);
foreach ($arr as $line)
{  echo $line.'<br>';  }

Now - I have got another SQL table, with data in it - like explained in my initial post. Example :
Field One
ID, City, Description

This said table contains data in random order of the cities, where I used the explode function.

So, what I want to achieve, is to list the total records which include the name of the city.

I use it in a loop. The end end result would be using this as a 'search' filter, where one can click on a city, and only ads will be displayed of the relevant town.

So, I have achieved it to explore the values of the single sql field, but I want to list the total ads next to each city. I think I must use a different loop.

Example of what I have :
Towns
-- Memphis
-- Robinsonville
-- Chicago

But what I want to achieve, is this :
-- Memphis (33)
-- Robinsonville (7)
-- Chicago (129)

The values will then ben the total town count where the FieldValue = xxx (town)

Again - thanx for helping!

johny_d 23 Junior Poster in Training · Answer 3 · 2010-04-08T08:04:17+00:00

I've just gave you the full query with the solution in the post above!
Did you even run that query?

cwarn23 387 Occupation: Genius Team Colleague Featured Poster · Answer 4 · 2010-04-08T13:50:24+00:00

I've just gave you the full query with the solution in the post above!
Did you even run that query?

Yes and to phpise it the code would be as follows

mysql_query('SELECT COUNT(*), fieldID, fieldValue FROM itemfield WHERE fieldID = 8 GROUP BY fieldValue') or die(mysql_error());

sallecpt 0 Junior Poster in Training · Answer 5 · 2010-04-10T01:25:28+00:00

I've just gave you the full query with the solution in the post above!
Did you even run that query?

hey - thanx for the reply

yes I did run the query - and I've chopped and changed it thereafter, to seek other solutions. it only displays the first count - in other words - only the first count of the first result in the loop are displayed.

any suggestions?

thanx again