Get most popular items within a 30 day time span?

jack55 · October 17, 2018, 7:15pm

How would I go about creating a most popular sql query where it looks in the transaction table, looks at the item id and sees how many times it appears within 30 days using the date and then order all the items by most popular LMIT 20?

Another SQL question is how do i get all data if someone views a parent category and then it basically gets the data from all other ctaegories that have X listed as the parent_id? Thanks so for example if they go to category/3/ it gets all item data associated with that category but is it possible to make it so that you go to category/1 and then it finds and gets all the data that shows for other categories so in this case all the categories with parent_id 1?

Thanks in advance.

donboe · October 19, 2018, 10:29pm

@jack55. For your first question you could use something like:

  SELECT *
       , count(item_id) as count
    FROM `transaction`
   WHERE `date` >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
GROUP BY item_id
ORDER BY item_id DESC
   LIMIT 20

however, I would not use date as field name since that is a reserved word.

For your second question: Maybe it’s just me, but I don’t have a clue what you try to reach. Try to describe it a bit better please

jack55 · October 19, 2018, 11:57pm

@donboe

Thankyou very much, that worked well on it’s own but can I also ask how I would do an innner join because for some reason it is not working for me…

        $sql = 'SELECT i.id, t.*, count(t.item_id) as count FROM `m_items` i INNER JOIN m_transactions t ON i.id = t.item_id WHERE `t.date` >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY t.item_id ORDER BY t.item_id DESC LIMIT 8';

To better explain, I have made a shop experiment with categories. In the items table there is a cat_id which is a category that is below a parent. In that category there is a parent_id which links to the parent category as shwon in the image above.

So say item 1 is in category 4 which has a parent of category 1. Right now, by going to categories/4 it gets all items associated with category 4 but then when you to go to categories/1 you get no items so i’m wondering how you do a middle man where the parent category selects all the categories with category 1 as parent and then load all the items assicated with the id just in the parent.

Here is a live example:
Parent Category:
https://www.codester.com/categories/43/scripts-code
Child category:
https://www.codester.com/categories/10/php-scripts-php-code

When you go to the parent category it gets all the data from the children. So when you visit Scripts & Code it gets all the data from PHP Scripts, Javascript etc. but if you only go to PHP Scripts, it only gets PHP Script. Right now mine only does the child items.

Thanks

donboe · October 20, 2018, 7:19am

@jack55. Are you able to show the table structure of both tables?

jack55 · October 20, 2018, 11:47am

Here are the tables:

Category Table:

Transaction Table:

Items Table:

So what I currently do to get all the data but it for some reason does not want to work when it comes to the most popular:
SELECT i.*, c.name AS catname, c.icon AS icon FROM m_items i INNER JOIN m_items_categories c ON i.cat_id = c.id WHERE i.status = 1 AND i.featured = 1 LIMIT 8

And then with the categories hopefully i explained it well enough. Thanks again!

donboe · October 20, 2018, 9:25pm

@jack55 I don’t want to be a pain, and I don’t ask for the content. Please do a dump of those two tables

donboe · October 20, 2018, 9:40pm

@jack55. You realy have to be clear. Nobodies going to steal your idea here on Sitepoint. If you need help, please be open

jack55 · October 21, 2018, 10:21am

Yeah of course sorry it’s an old habbit and it’s an experiment project so it doesnt matter if someone takes it anyway

I have converted sql to html otherwise it does not let me upload. Thanks again for your help.

chewi-merge(4).html (7.5 KB)

donboe · October 21, 2018, 10:51am

@jack55. You don’t have to send a download. Just show the structure from your tables. Someting like:

CREATE TABLE `m_items` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `parent_id` tinyint(4) NOT NULL,
  `name` varchar(128) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `m_items_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` tinyint(4) NOT NULL,
  `name` varchar(128) NOT NULL,
  `price` decimal(10,0) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Other question. So what you want is when someone clicks on for example PHP that you go to a productpage with only PHP related items OR do you want to show the PHP related items in the menu as well when clicking on PHP?

jack55 · October 21, 2018, 10:54am

I’m tryinig to make it so that when you go to a parent category it gets all the data associated with its children.

jack55 · October 21, 2018, 10:56am

So here is the important data:

CREATE TABLE `chewi_m_items` (
  `id` int(11) NOT NULL,
  `name` varchar(99) NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `description` varchar(200) DEFAULT NULL,
  `content` text NOT NULL,
  `features` text NOT NULL,
  `icon_img` varchar(99) DEFAULT NULL,
  `preview_img` varchar(255) DEFAULT NULL,
  `item_file` varchar(99) DEFAULT NULL,
  `item_scr` varchar(250) DEFAULT NULL,
//////////////  `cat_id` int(3) NOT NULL DEFAULT '0',  /// this will be the child category id
  `user_pin` varchar(50) NOT NULL,
  `support` int(11) NOT NULL DEFAULT '0',
  `featured` int(11) NOT NULL DEFAULT '0',
  `on_sale` int(11) DEFAULT '0',
  `sale_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `on_stock` int(11) NOT NULL DEFAULT '0',
  `stock_amount` int(11) NOT NULL DEFAULT '1',
  `tags` text NOT NULL,
  `demo` varchar(255) DEFAULT NULL,
  `video` varchar(255) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `status` int(11) NOT NULL DEFAULT '1',
  `right_to_sell` int(11) NOT NULL DEFAULT '0',
  `update_request` int(11) NOT NULL DEFAULT '0',
  `update_request_on` datetime NOT NULL,
  `review_message` text NOT NULL,
  `free` int(11) NOT NULL DEFAULT '0',
  `apply_free` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `chewi_m_items_categories` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `icon` varchar(50) DEFAULT NULL,
  ////////////`parent_id` int(11) DEFAULT NULL,   //// This here will be blank if parent and if child it will have parent id
  `status` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

jack55 · October 21, 2018, 10:58am

So i go to a parent category which is id 1 for example and then it gets all the data from the categories with id 1 as it’s parent. As shown here. Here is a live example:

Parent Category:
https://www.codester.com/categories/43/scripts-code
Child category:
https://www.codester.com/categories/10/php-scripts-php-code

So as PHP Scripts is part of Scripts & Code when you go to Scripts & Code it gets all of PHP Scripts data as well as Javascript etc.

Here is my own experiment example:
This works fine this way as its the child category as the item table is associated with that category id

But when I go to the parent one it loads nothing so i want it to load all the children data inside the parent

Hope that makes sense thanks

donboe · October 21, 2018, 11:40am

@jack55. Maybe I still don’t understand you in a right way, but isn’t it way easier to have the (grand)children of the children in the same table.:

INSERT INTO `m_items` (`id`, `parent_id`, `name`, `status`) VALUES
(1, NULL, 'Scripts & Code', 1),
(2, NULL, 'App & Source code', 1),
(3, 1, 'PHP Scripts', 1),
(4, 1, 'Javascript', 1),
(5, 1, 'CSS', 1),
(6, 1, 'Pyton', 1),
(7, 3, 'Affiliate', 1),
(8, 3, 'Chat', 1),
(9, 3, 'Countdown', 1),
(10, 3, 'Database', 1);

jack55 · October 21, 2018, 11:42am

The children are in the same table as their parent! I just simply want to make it so that if you go to a parent category it gets all the data from the children categories:

So when you go to Scripts & Code, it gets all the data in blue or from its children

donboe · October 21, 2018, 11:50am

@jack55. So when you click on Script & Code, you should get all data from PHP, Javascript ec…Right?

jack55 · October 21, 2018, 11:51am

Yes thats correct, i just cannot figure out how to do the sql in order to do that Thanks again

donboe · October 21, 2018, 11:53am

You use cat_id in the chewi_m_items table so there is no need for a join statement:

SELECT * 
  FROM `chewi_m_items`
 WHERE `cat_id` = 1

jack55 · October 21, 2018, 11:54am

no because the items table cat_id is the child category id. I need it so that when you go to the parent id it gets all the data from the children.

jack55 · October 21, 2018, 11:56am

go to parent category > find the children in same table > look in item table for cat_id (child cat id) then load all that data into the parent category

Right now this is the statment:

SELECT i.*, c.name AS catname, c.icon AS icon FROM chewi_m_items i INNER JOIN chewi_m_items_categories c ON i.cat_id = c.id WHERE i.status = 1 AND i.cat_id = :cat_id ORDER BY i.created DESC and it works great for getting data for cat_id i just want it so that when you go to a parent id it gets all the children so it’s basically another step where it knows its a parent category and gets the children data

donboe · October 21, 2018, 12:01pm

Now you are realy confusing me.

You have Scripts & Code

One of the children of Script & Code is PHP

PHP has children as well (the example link) which are Affiliate, Chat, etc