Count results during a loop

TheBrandon · November 5, 2010

Hello all,

I've built a database of coupons. Each coupon is assigned to a category (so all electronics coupons display under electronics). On my category list I'd like to display how many coupons is in each category.

Currently this function displays my category list:

	function fetch_categories() {
		mysql_connect(SQL_HOST_NAME, SQL_USER_NAME, SQL_PASSWORD) or die(mysql_error());
		mysql_select_db(SQL_DATABASE) or die(mysql_error());
		$result = mysql_query("SELECT * FROM categories ORDER BY category_name")or die(mysql_error());

		echo '<div id="category_list">';
		echo '<ul class="cat_list">';

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

			//get total of discounts in each category
			$count_result = mysql_query("SELECT * FROM category_relations, discounts WHERE category_relations.member_of = '$row[id]' AND discounts.active = '1'")or die(mysql_error());
			$num_rows = mysql_num_rows($count_result);
			echo '<span class="numrows">('.$num_rows.')</span>';

			echo '<li><a href="index.php?cat_ID='.$row['id'].'">'.$row['category_name'].'  </a></li>';
		}

		echo '</ul>';
		echo '</div>';
}

The above is a debug version so it's not all formatted correctly.

However right now, that exports "3" for Airlines when right now, I have only one discount in the entire database.

This is my category_relations table:

--
-- Table structure for table `category_relations`
--

CREATE TABLE IF NOT EXISTS `category_relations` (
  `id` int(10) NOT NULL,
  `member_of` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `category_relations`
--

INSERT INTO `category_relations` (`id`, `member_of`) VALUES
(2, 2),
(1, 1),
(3, 1),
(4, 1);

And this is my discounts table:

--
-- Table structure for table `discounts`
--

CREATE TABLE IF NOT EXISTS `discounts` (
  `id` smallint(5) NOT NULL AUTO_INCREMENT,
  `redeem` tinyint(3) NOT NULL,
  `discount_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `short_description` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `eligibility` blob NOT NULL,
  `url` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `coupon_code` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `long_description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `logo` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `business_name` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(80) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_street` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_city` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_state` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_zip` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `categories` blob NOT NULL,
  `level` tinyint(5) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `user_id` varchar(2555) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `level` (`level`,`active`),
  KEY `user_id` (`user_id`(333))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `discounts`
--

INSERT INTO `discounts` (`id`, `redeem`, `discount_title`, `short_description`, `eligibility`, `url`, `coupon_code`, `start_date`, `end_date`, `long_description`, `logo`, `business_name`, `phone`, `store_location_street`, `store_location_city`, `store_location_state`, `store_location_zip`, `categories`, `level`, `active`, `user_id`) VALUES
(1, 0, '$2.00 Off Transportation', '', 0x4172726179, 'www.baysideshuttle.com', '', '2010-01-01 00:00:00', '2011-01-01 00:00:00', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Integer varius ipsum id justo pellentesque laoreet. Curabitur tempus tincidunt...', NULL, 'Bayside Airport Shuttle', '850-123-3456', NULL, NULL, NULL, NULL, 0x4172726179, 0, 1, '24');

It seems to be completely ignoring my active check. How can I fix this, or is there a better way to do this query?

Psycho · November 5, 2010

Never run queries in a loop! It is a huge overhead on your server and will cause long page loads as you get more records. I'll take a look at the code and see about reducing it to one query and ficing the problem you are having.

TheBrandon · November 5, 2010

Thanks. I'd love to learn a more efficient way of doing this as I do it a lot in programming.

AbraCadaver · November 5, 2010

You need to join the tables, so either a join or another WHERE condition. Unfortunately, it seems that discounts.categories holds multiples and its a blob so you can't join on that. You would need another table maybe discounts_categories that holds the relations between discounts_id and categories_id for example.

TheBrandon · November 5, 2010

You need to join the tables, so either a join or another WHERE condition. Unfortunately, it seems that discounts.categories holds multiples and its a blob so you can't join on that. You would need another table maybe discounts_categories that holds the relations between discounts_id and categories_id for example.

That's what category_relations is, isn't it?

It's simply the ID of the category and the ID of the discount. That's all that is in the table.

The discounts.categories field isn't used at all. In fact, I need to remove it.

Psycho · November 5, 2010

That's what category_relations is, isn't it? It's simply the ID of the category and the ID of the discount. That's all that is in the table.

That is the foreign key which you need to use when you actually do the JOIN in the query. Not 100% sure if this is right since I'm not going to recreate your database to test with. If not, it will be close and we can tweak it once you describe what the results are.

function fetch_categories()
{
    mysql_connect(SQL_HOST_NAME, SQL_USER_NAME, SQL_PASSWORD) or die(mysql_error());
    mysql_select_db(SQL_DATABASE) or die(mysql_error());
    $query = "SELECT c.id, c.category_name, COUNT(c.id) as active_count
              FROM categories as c
              LEFT JOIN category_relations as cr
                ON cr.member_of = c.id AND cr.active = 1
              GROUP BY c.id
              ORDER BY category_name";
    $result = mysql_query($query)or die(mysql_error());

    while($row=mysql_fetch_assoc($result))
    {
        $listHTML .= "<li>";l
        $listHTML .= "<span class=\"numrows\">({$row['active_count']}</span>";
        $listHTML .= "<a href=\"index.php?cat_ID={$row['id']}\">{$row['category_name']}</a>";
        $listHTML .= "</li>\n";
    }
    echo "<div id=\"category_list\">\n";
    echo "<ul class=\"cat_list\">\n";
    echo $listHTML;
    echo "</ul>\n";
    echo "</div>\n";
}

TheBrandon · November 5, 2010

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 05, 2010 at 10:03 PM
-- Server version: 5.1.44
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `discountdemo`
--

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `id` smallint(4) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `category_name`) VALUES
(1, 'Airlines'),
(2, 'Arts & Collectibles'),
(3, 'Automotive'),
(4, 'Baby & Maternity'),
(5, 'Other'),
(6, 'Apparel, Shoes & Jewelry'),
(8, 'Computers & Office'),
(9, 'Books, Music & Video'),
(10, 'Electronics'),
(11, 'Finance'),
(12, 'Flowers & Gifts'),
(13, 'Food & Beverage'),
(14, 'Health & Beauty'),
(15, 'Home & Garden'),
(16, 'Kids & Baby'),
(17, 'Online Services'),
(18, 'Pets'),
(19, 'Phone & Services'),
(20, 'Seasonal'),
(21, 'Sports & Outdoors'),
(22, 'Toys & Games'),
(23, 'Travel & Gear');

-- --------------------------------------------------------

--
-- Table structure for table `category_ads`
--

CREATE TABLE IF NOT EXISTS `category_ads` (
  `cat_id` smallint(4) NOT NULL,
  `vendor_id` smallint(4) unsigned NOT NULL,
  `filename` varchar(80) NOT NULL,
  `priority` varchar(80) NOT NULL,
  `url` varchar(80) NOT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `category_ads`
--

INSERT INTO `category_ads` (`cat_id`, `vendor_id`, `filename`, `priority`, `url`) VALUES
(1, 0, './img/category_ads/bayside_shuttle.jpg', '', '');

-- --------------------------------------------------------

--
-- Table structure for table `category_relations`
--

CREATE TABLE IF NOT EXISTS `category_relations` (
  `id` int(10) NOT NULL,
  `member_of` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `category_relations`
--

INSERT INTO `category_relations` (`id`, `member_of`) VALUES
(2, 2),
(1, 1),
(3, 1),
(4, 1);

-- --------------------------------------------------------

--
-- Table structure for table `discounts`
--

CREATE TABLE IF NOT EXISTS `discounts` (
  `id` smallint(5) NOT NULL AUTO_INCREMENT,
  `redeem` tinyint(3) NOT NULL,
  `discount_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `short_description` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `eligibility` blob NOT NULL,
  `url` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `coupon_code` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `long_description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `logo` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `business_name` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(80) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_street` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_city` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_state` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_zip` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `categories` blob NOT NULL,
  `level` tinyint(5) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `user_id` varchar(2555) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `level` (`level`,`active`),
  KEY `user_id` (`user_id`(333))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `discounts`
--

INSERT INTO `discounts` (`id`, `redeem`, `discount_title`, `short_description`, `eligibility`, `url`, `coupon_code`, `start_date`, `end_date`, `long_description`, `logo`, `business_name`, `phone`, `store_location_street`, `store_location_city`, `store_location_state`, `store_location_zip`, `categories`, `level`, `active`, `user_id`) VALUES
(1, 0, '$2.00 Off Transportation', '', 0x4172726179, 'www.baysideshuttle.com', '', '2010-01-01 00:00:00', '2011-01-01 00:00:00', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Integer varius ipsum id justo pellentesque laoreet. Curabitur tempus tincidunt...', NULL, 'Bayside Airport Shuttle', '850-123-4567', NULL, NULL, NULL, NULL, 0x4172726179, 0, 1, '24');

-- --------------------------------------------------------

--
-- Table structure for table `eligibility`
--

CREATE TABLE IF NOT EXISTS `eligibility` (
  `eligibility_id` mediumint( unsigned NOT NULL,
  `discount_id` mediumint( unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `eligibility`
--

INSERT INTO `eligibility` (`eligibility_id`, `discount_id`) VALUES
(1, 4),
(3, 4),
(5, 4),
(1, 6),
(3, 6),
(5, 6),
(1, 2),
(1, 3),
(1, 4);

-- --------------------------------------------------------

--
-- Table structure for table `eligibility_labels`
--

CREATE TABLE IF NOT EXISTS `eligibility_labels` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `eligibility_labels`
--

INSERT INTO `eligibility_labels` (`id`, `label`) VALUES
(1, 'Active Duty'),
(2, 'Veteran'),
(3, 'Retiree'),
(4, 'Reserve'),
(5, 'National Guard'),
(6, 'Government Employee'),
(7, 'Military Families');

-- --------------------------------------------------------

--
-- Table structure for table `front_page_ads`
--

CREATE TABLE IF NOT EXISTS `front_page_ads` (
  `id` smallint(4) NOT NULL AUTO_INCREMENT,
  `vendor_id` smallint(4) unsigned NOT NULL,
  `filename` varchar(80) NOT NULL,
  `priority` smallint(4) NOT NULL,
  `url` varchar(80) NOT NULL,
  `ad_type` tinyint(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `front_page_ads`
--

INSERT INTO `front_page_ads` (`id`, `vendor_id`, `filename`, `priority`, `url`, `ad_type`) VALUES
(1, 0, './img/front_page_ads/support_our_troops.jpg', 1, 'http://www.google.com/', 0),
(2, 0, './img/front_page_ads/revive_media_services.jpg', 0, 'http://revivemediaservices.com', 0),
(3, 0, './img/front_page_ads/fwb_chamber.jpg', 0, 'http://fwbchamber.org', 0);

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int( NOT NULL AUTO_INCREMENT,
  `username` varchar(70) NOT NULL,
  `password` varchar(32) NOT NULL,
  `level` int( NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=29 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `password`, `level`) VALUES
(1, 'admin', 'admin', 9);

I'm not understanding some of your lines such as FROM categories as c? Can you please apply the proper names to your example so I can see what you're going for?

BlueSkyIS · November 5, 2010

the example uses table aliases, which can make things cleaner, simpler, less error-prone, etc.

http://www.brainbell.com/tutorials/MySQL/Using_Table_Aliases.htm

Psycho · November 6, 2010

I'm not understanding some of your lines such as FROM categories as c? Can you please apply the proper names to your example so I can see what you're going for?

the example uses table aliases, which can make things cleaner, simpler, less error-prone, etc.

Yeah, I take a little offense at the fact that because you don't understand something you seem to think it is not "proper". What is not proper was your previous logic of running queries in a loop instead of using a simple JOIN. Did you even try the code I posted?

Here is the SAME query without using aliases

SELECT categories.id, categories.category_name, COUNT(categories.id) as active_count
FROM categories
LEFT JOIN category_relations
  ON category_relations.member_of = categories.id AND category_relations.active = 1
GROUP BY categories.id
ORDER BY category_name

That's definitely mpre c;uttered and less readable than what I originally posted:

SELECT c.id, c.category_name, COUNT(c.id) as active_count
FROM categories as c
LEFT JOIN category_relations as cr
  ON cr.member_of = c.id AND cr.active = 1
GROUP BY c.id
ORDER BY c.category_name

TheBrandon · November 11, 2010

I'm not understanding some of your lines such as FROM categories as c? Can you please apply the proper names to your example so I can see what you're going for?

the example uses table aliases, which can make things cleaner, simpler, less error-prone, etc.

Yeah, I take a little offense at the fact that because you don't understand something you seem to think it is not "proper". What is not proper was your previous logic of running queries in a loop instead of using a simple JOIN. Did you even try the code I posted?

Here is the SAME query without using aliases
SELECT categories.id, categories.category_name, COUNT(categories.id) as active_count
FROM categories
LEFT JOIN category_relations
  ON category_relations.member_of = categories.id AND category_relations.active = 1
GROUP BY categories.id
ORDER BY category_name
That's definitely mpre c;uttered and less readable than what I originally posted:
SELECT c.id, c.category_name, COUNT(c.id) as active_count
FROM categories as c
LEFT JOIN category_relations as cr
  ON cr.member_of = c.id AND cr.active = 1
GROUP BY c.id
ORDER BY c.category_name

Proper wasn't meant as an insult. It's the English side of me and I apologize.

Yeah I did try it and it gave the error "#1054 - Unknown column 'cr.active' in 'on clause'" I think because the "active" field is in the discounts field not the other one.

I'm sorry again for offending. I didn't mean to. I've never used aliases in queries before.

Sign In

Count results during a loop

Recommended Posts

TheBrandon

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

TheBrandon

Link to comment

Share on other sites

AbraCadaver

Link to comment

Share on other sites

TheBrandon

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

TheBrandon

Link to comment

Share on other sites

BlueSkyIS

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

TheBrandon

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information