Sorry for confusing you, its like the link i sent you. https://www.codester.com/categories/43/scripts-code - This is the parent category which loads in data for all its children.
This link is the child category and only loads in php scripts: https://www.codester.com/categories/10/php-scripts-php-code
I just can’t figure out how to do the parent one where it loads all of its childrens data in. And in the items table it only stores the child category id or php not scripts and code.
Also if you can’t figure it out dw but this i’m sure would be much easier for you and its doing an inner join for your most popular statment as for some reason it is not working thanks
$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'
@jack55. I think you make it yourself way do difficult. The first link you send (scripts-code) returns all data from children (PHP, Javascript, CSS etc) right. Which in your case are all items from the table chewi_m_items right? What you could do is to have two foreign keys in your items table. One linking to the main category (Scripts & Code and one to the sub category PHP
That is what i’m trying to get it to do. The link is a sample of a website I found.
All these items link up the either the PHP, JS or CSS etc and then those categories link up to Scripts & Code via the parent_id which is where the relationship is.
But whatever way you think would be easier can you give an example of the two foreign keys? Thanks very much again.
@jack55 To start wit you should link your cat_id to the main category instead of to the sub_category . Which will make your initianal query way easier. also add a index to the cat_id in the chewi_m_items table. You maybe should also consider to use a string instead of a number for the category. i.e.
/category/scripts/php-scripts
After that we should ask a MOD to replace the question to the PHP forum.
And before it gets moved to the PHP can you help with the most popular sql query:
$sql = 'SELECT i.id, i.name, i.price AS item_price, 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';
I’m trying to do an inner join where i get the name and price etc but it is not working. Am i doing something wrong?
@jack55. What is the role of the m_transactions table in the most popular query? Also what I notice is that you change te table names all the time. Is the table name chewi_m_items or chewi_m_items
This is your original which works great but i need an inner join to get the item name and price etc
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
@jack55 . The problem is in the WHERE clause with the ticks arround t.date
WHERE `t.date`
That should be t.date without the back ticks
SELECT i.id
, i.name
, i.price AS item_price
, t.*
, count(item_id) as count
FROM `m_items` i
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 count DESC
LIMIT 8
t.date is not a column name but rather an alias from the table followed by a column name. With the back ticks arround it mysql presumes it’s a column name
$sql = 'SELECT i.id, i.name, i.price AS item_price, t.*, count(t.item_id) AS count FROM `chewi_m_items` i JOIN chewi_m_transactions t ON i.id = t.item_id WHERE t.payment_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY t.item_id ORDER BY count DESC LIMIT 8';
Can i ask tho why JOIN & not INNER JOIN? Thanks
