XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Modules Support forums / 
  3. Module usage questions 
  4.  / help with mysql query in latest downloads block
Register
1
javier
help with mysql query in latest downloads block

  • 2004/3/17 0:21

  • javier

  • Not too shy to talk

  • Posts: 184

  • Since: 2002/8/6 1


I added a new block to the download module, at the moment is a "latest downloads" clone block.
but i want modify to allow latest downloads by category, i know is very simple, only change a MySQL query.
but i don´t know much about mysql, them i need a little help.

Let me explain better, i will create one block x category, them i not need create code to set options from admin panel,etc.

I want show latest downloads from category 2 (cid=2)

the original query is

$result = $xoopsDB->query("SELECT lid, cid, title, date, hits FROM ".$xoopsDB->prefix("mydownloads_downloads")." WHERE status>0 ORDER BY ".$options[0]." DESC",$options[1],0);


i want modify the query to only show downloads from a specific cid (cid=2)

thanks in advance , sorry for my poor english.



2
Dave_L
Re: help with mysql query in latest downloads block

  • 2004/3/17 2:30

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


$result = $xoopsDB->query("SELECT lid, cid, title, date, hits FROM ".$xoopsDB->prefix("mydownloads_downloads")." WHERE [color=ff0000]cid=2 AND[/color] status>0 ORDER BY ".$options[0]." DESC",$options[1],0);
3
javier
Re: help with mysql query in latest downloads block

  • 2004/3/17 16:26

  • javier

  • Not too shy to talk

  • Posts: 184

  • Since: 2002/8/6 1


Thanks Dave,

yesterday after wrote the question i found the solution too, reading other XOOPS files,etc.

thanks anyways :)
4
javier
Re: help with mysql query in latest downloads block

  • 2004/3/23 22:13

  • javier

  • Not too shy to talk

  • Posts: 184

  • Since: 2002/8/6 1


I forget a little detail :)

what happen when i have subcategorys, how i can make that?

ie:

files (category cid=9)----> themes (subcategory cid=15)
----> hacks (subcategory cid= 17)

graphics (category cid=x)-----> bla
-----> bla

currently the block show the latest files added to cid=9 but i want show the files from cid 9 and all his subcategorys ,cid 15 and 17

how i can make that? how will be the query?

thanks again and sorry for disturb you again with this

grettings :)
5
Dave_L
Re: help with mysql query in latest downloads block

  • 2004/3/23 23:58

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


This is just a quick guess, based on looking at modules/mydownloads/sql/mysql.sql. I don't have that module installed and populated with data, so I can't test it.

SELECT lid, cid, title, date, hits
FROM mydownloads_downloads AS d
LEFT JOIN mydownloads_cat AS c ON c.pid = d.cid
WHERE d.cid = 9 OR c.pid = 9


I don't know if there can be more than one level of subcategory (subcategories within subcategories). If so, it would be more complicated.
6
javier
Re: help with mysql query in latest downloads block

  • 2004/3/24 1:59

  • javier

  • Not too shy to talk

  • Posts: 184

  • Since: 2002/8/6 1


Thanks again Dave for take some of your time for reply me.

the above code not work,i was my fault i give you incorrect data
i was viewing the table estructures and i think the following data can be util:


in the xoops_mydownloads_cat table:

files (Main category cid=9 pid=0)
----> themes (subcategory cid=15 pid=9)
----> hacks (subcategory cid= 17 pid=9)

Them the main category has cid=9 and the pid of course is 0
the subcategorys has his own cid but the pid is the main category cid.

cid | pid | title
------------------
9 | 0 | files
15 | 9 | themes
17 | 9 | hacks

grettings

7
javier
Re: help with mysql query in latest downloads block

  • 2004/3/26 19:18

  • javier

  • Not too shy to talk

  • Posts: 184

  • Since: 2002/8/6 1


please don´t leave me alone just now guys, anyone? i only need these modification for put my site online.
8
javier
Re: help with mysql query in latest downloads block

  • 2004/3/29 13:11

  • javier

  • Not too shy to talk

  • Posts: 184

  • Since: 2002/8/6 1


maybe i made a error when i added the code?

$result = $xoopsDB->query("SELECT lid, cid, title, date, hits FROM ".$xoopsDB->prefix("mydownloads_downloads")." AS d LEFT JOIN ".$xoopsDB->prefix("mydownloads_cat")." AS c ON c.pid = d.cid WHERE d.cid = 9 OR c.pid = 9 AND status>0 ORDER BY ".$options[0]." DESC",$options[1],0);
9
Mithrandir
Re: help with mysql query in latest downloads block

"SELECT d.lid, d.cid, d.title, d.date, d.hits FROM..."

Or what is MySQL debug telling you as the reason why it won't work?
10
Mithrandir
Re: help with mysql query in latest downloads block

Alternate approach

$xt = new XoopsTree('mydownloads_cat', "cid", "pid");
$categories = $xt->getAllChildId($category_id_you_want_on_top);
$categorystring = "(";
foreach ($categories as $key => $categoryid) {
    if ($key != 0) { //Only comma-separate subsequent categories
        $categorystring .= ", ";
    }
    $categorystring .= $categoryid;
}
$categorystring = ")";

$result = $xoopsDB->query("SELECT lid, cid, title, date, hits FROM 'mydownloads_downloads' WHERE cid IN ".$categorystring." ORDER BY ".$options[0]." DESC", $options[1], 0);


That should give you all child categories more than one step down
(1) 2 3 »

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

120 user(s) are online (73 user(s) are browsing Support Forums)

Members: 0

Guests: 120

more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: May 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits