Solved

Litle Sql Query, php - change status to 0 if row['total'] iz zero

Posted on 2014-09-28
3
316 Views
Last Modified: 2014-09-29
My ask now it is:

return $query->row['total']; - returns as results counted products from each categorie.

I want  dezactivate the category who dont have products in, by setup in oc_category table status=0

i wish  a nice answer in same function, i want it modified somehow to have 2 active queries, the second one to setup status o if row['total'] is 0

public function getTotalProductsByCategoryId($category_id) {
            		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_category WHERE category_id = '" . (int)$category_id . "'");
            
            		return $query->row['total'];
            	}

Open in new window


Thank u!

I got this structure for category.

CREATE TABLE oc_category (
  category_id int(11) NOT NULL AUTO_INCREMENT,
  image varchar(255) DEFAULT NULL,
  parent_id int(11) NOT NULL DEFAULT '0',
  top tinyint(1) NOT NULL,
  `new` tinyint(1) NOT NULL,
  `column` int(3) NOT NULL,
  sort_order int(3) NOT NULL DEFAULT '0',
  discounts varchar(255) NOT NULL DEFAULT '',
  `status` tinyint(1) NOT NULL,
  date_added datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  date_modified datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  selected_product_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (category_id),
  KEY parent_id (parent_id),
  KEY top (top),
  KEY sort_order (sort_order),
  KEY `status` (`status`),
  KEY parent_id_2 (parent_id),
  KEY top_2 (top),
  KEY sort_order_2 (sort_order),
  KEY status_2 (`status`),
  KEY parent_id_3 (parent_id),
  KEY top_3 (top),
  KEY sort_order_3 (sort_order),
  KEY status_3 (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Open in new window


and here the next structure which are in corelation with first one.

CREATE TABLE oc_category_description (
  category_id int(11) NOT NULL,
  language_id int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  description text NOT NULL,
  text_left text NOT NULL,
  text_right text NOT NULL,
  meta_description varchar(255) NOT NULL,
  meta_keyword varchar(255) NOT NULL,
  custom_title varchar(255) DEFAULT '',
  PRIMARY KEY (category_id,language_id),
  KEY `name` (`name`),
  KEY language_id (language_id),
  KEY language_id_2 (language_id),
  KEY language_id_3 (language_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window


and

CREATE TABLE `oc_product_to_category` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`product_id`,`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window


I got this query who is used in a function model.

$sql = "SELECT cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR ' > ') AS name, c.parent_id, c.sort_order, c.status FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "category c ON (cp.path_id = c.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (c.category_id = cd1.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (cp.category_id = cd2.category_id) WHERE cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' AND cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'";

Open in new window


and here is function

public function getTotalProductsByCategoryId($category_id) {
            		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_category WHERE category_id = '" . (int)$category_id . "'");
            
            		return $query->row['total'];
            	}

Open in new window

0
Comment
Question by:Eu Telea
  • 2
3 Comments
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40348641
Obviously I cannot test this because I do not have your data model, but it seems like it might be right.  Back Up Your Tables Before Using This Code or just install it in your test system to prove it before you use it in a deployed system.

public function getTotalProductsByCategoryId($category_id)
{
    $categ = (int)$category_id;
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_category WHERE category_id = $categ");
    $total = $query->row['total'];
    if ($total == 0)
    {
        $query = = $this->db->query("UPDATE oc_category SET `status` = 0 WHERE category_id = $categ LIMIT 1");
    }
    return $total;
}

Open in new window

0
 

Author Closing Comment

by:Eu Telea
ID: 40350172
Your solution was good for me.
I just modifie the double equal ==
if ($total == 0) in if ($total = 0)
$query = = in $query =

And my final file it is this and working.

    <file name="admin/model/catalog/category.php">
        <operation info="Change getCategories function query in category model">
            <search position="replace"><![CDATA[
                $sql = "SELECT cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR ' &gt; ') AS name, c.parent_id, c.sort_order FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "category c ON (cp.path_id = c.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (c.category_id = cd1.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (cp.category_id = cd2.category_id) WHERE cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' AND cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'";
            ]]></search>

            <add><![CDATA[
                $sql = "SELECT cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR ' &gt; ') AS name, c.parent_id, c.sort_order, c.status FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "category c ON (cp.path_id = c.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (c.category_id = cd1.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (cp.category_id = cd2.category_id) WHERE cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' AND cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'";
            ]]></add>
        </operation>
       
        <operation info="Add new function for number of products in sepcific category to category model">
            <search position="after"><![CDATA[
                class ModelCatalogCategory extends Model {
            ]]></search>

            <add><![CDATA[
            public function getTotalProductsByCategoryId($category_id)
{
    $categ = (int)$category_id;
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_category WHERE category_id = $categ");
    $total = $query->row['total'];
    if ($total = 0)
    {
        $query = $this->db->query("UPDATE oc_category SET `status` = 0 WHERE category_id = $categ LIMIT 1");
    }
    return $total;
}
            ]]></add>
        </operation>
    </file>
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40350235
Thanks for the points.  In PHP the single equal sign is the assignment operator.  The double equal is the comparison operator for loose comparisons.  The triple equal is the comparison operator for identical comparisons.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Every business owner understands the significance of online customer reviews and the impact it can have on sales and revenues. With technology advancing at such a rapid pace, getting online reviews has never been easier, especially when many regions…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question