RJP1

Ignore the user table, it's simply a check to see if the user exists...

Well, they dont have to be different, I just need a little help to multiply 2 counts from 2 different tables really.

Essentially I need to combine these 2 queries: SELECT p.userid, COUNT(p.status) AS score FROM table1 AS p WHERE (SELECT COUNT(*) FROM usertable WHERE id = p.userid)>0 AND p.date_joined >= 0 AND p.date_joined <= ".time(); GROUP BY p.userid ORDER BY score DESC LIMIT 10"; And SELECT userid, (1 + (COUNT(*) * 0.1)) AS score2 FROM table2 WHERE time >= 0 AND time <= FROM_UNIXTIME($now) GROUP BY userid ORDER BY score2 DESC Basically, I'd like to get score and score2 and multiply them together in 1 query... Doable? Cheers, RJP1

Hi guys, I'm looking to get a little help on a complex issue... I have 2 tables. Both have user ids. I'd like to make a leader board from the data within each table... I need to count each row in one table and get that as a simple number per user. Then for each user id, get the count from another table and then multiply these 2 figures together. Then order by the "score" DESC... Can anyone help me find the best way to do this? Thanks! RJP1

Well I'd love to, I just don't know how - hence my asking here! Cheers, RJP1

Can anyone help with this one? I'm struggling big time! RJP1

How do you suggest I do that within my existing query? Could you possibly point me in the right direction? Cheers! RJP1

Not as such, I want the days that haven't got a value to have the value of the first day previous to the zero day if that makes sense! In this case 2011-03-11, 2011-03-12, 2011-03-13, 2011-03-14 would = 10000 (because 2011-03-10 = 10000) and 2011-03-16, 2011-03-17, 2011-03-18, 2011-03-19 and 2011-03-20 to = 10345.47 (because 2011-03-15 = 10345.47). Doable? RJP1

Hi guys, I have a simple table like so: id timestamp value -------------------------------------- 30 1299784130 10000.00 31 1300168969 10500.00 Using the following SQL query I am able to get the higest value per day in a date range including days that don't exist in the database (for graphing): SELECT Y.day, MAX(COALESCE(Z.value,0)) as value FROM (SELECT DATE_ADD( '2011-01-01', INTERVAL a.i + b.i *10 + c.i *100 DAY ) AS DAY FROM integers a CROSS JOIN integers b CROSS JOIN integers c HAVING DAY BETWEEN '2011-03-10' AND '2011-03-20') Y LEFT OUTER JOIN `table` Z ON Y.day = DATE(FROM_UNIXTIME(Z.timestamp)) GROUP BY Y.DAY ORDER BY Y.DAY ASC This yields: day value -------------------------------- 2011-03-10 10000.00 2011-03-11 0.00 2011-03-12 0.00 2011-03-13 0.00 2011-03-14 0.00 2011-03-15 10345.47 2011-03-16 0.00 2011-03-17 0.00 2011-03-18 0.00 2011-03-19 0.00 2011-03-20 0.00 Which is lovely. However, can someone advise how I alter the query to ensure the dates between 2011-03-10 and 2011-03-15 have 10000.00 as their value rather than zero? In other words, if the value comes out as zero, can you help me make it check backwards in time to check for the highest previous value? Thanks! RJP1

Because that's how I did it. I wasn't able to do it in one, hence my asking here.

Sure, this is what I did: function getCategories() { $db =& JFactory::getDBO(); $query = 'SELECT DISTINCT c.parentid FROM #__vault as i,jos_vault_categories as c WHERE i.catid=c.id AND c.parentid>0 AND i.published=1'; $db->setQuery( $query ); $parentidswithchildren = $db->loadObjectList(); if ($parentidswithchildren) { $cat_ids = array(); foreach ( $parentidswithchildren AS $c ) { $cat_ids[] = $c->parentid; } $in = "c.id IN (".implode(',',$cat_ids).") OR "; } else { $in = null; } $query = "SELECT c.* FROM #__vault_categories AS c WHERE [b]$in[/b](SELECT COUNT(catid) FROM #__vault WHERE catid=c.id AND published=1) > 0 AND parentid=0"; $db->setQuery( $query ); $categories = $db->loadObjectList(); return $categories; } If there are children, it puts their id's into a comma separated list as $in. Works well. What do you think? RJP1

What I meant by runaway categorisation was: Parent - child 1 - child 1's child - etc - child 2 But I have figured out how to prevent this in my application, but I have yet to figure out how to get a list of parents then each parents child categories like so: - parent - child - child - parent - parent - parent - child etc I'll have a look at it now and see if I can figure it out, I managed the harder query in my other post you thought this was like. They are similar and I apologise for the seemingly double post. Cheers, RJP1

Yes, but it's not. This issue I figured out. Cheers, RJP1

Hi guys, I have a sql query that selects categories as long as they are a parent and they have items published within it: SELECT c.* FROM #__categories AS c WHERE (SELECT COUNT(catid) FROM #__items WHERE catid=c.id AND published=1) > 0 AND parentid=0 ORDER BY name ASC However, I'm having difficulty combining the above query to also get subcategories that are within a parent category but only when the subcategory has items in it and not its parent. I want to get these "empty" parents if they contain subcategories with items in them... possible? Cheers, RJP1

Hi guys, I'm building a category table in Joomla and have this so far: id | name | parentid ------------------------- 1 | cat1 | 2 2 | cat2 | 0 3 | cat3 | 0 As you can see, categories 2 and 3 are parents (parentid = 0) but cat 1 is categorised under cat 1. What is the best way to select subcategories straight after it's parent? E.g. cat2, cat1, cat3 to then go on to put them in a table. Would it be best to prevent runaway categorisation and just allow categories and subcategories? Cheers for the guidance. RJP1

Thanks kickstart, I got the query working nicely with timestamps and managed to use it successfully with pChart to plot data by date on a nice graph. Thanks a lot, RJP

Hi guys, I have this MySQL query: SELECT DATE(FROM_UNIXTIME(submitted)) day, COUNT(*) count FROM table GROUP BY day ORDER BY day ASC It works well and counts the rows by day using each rows timestamp. However, I'd like it to count zero on days without any rows. At the moment it gives: day | count ----------------------------- 2011-02-16 | 5 2011-02-17 | 10 2011-02-19 | 2 2011-02-20 | 1 2011-02-21 | 4 2011-02-22 | 2 2011-02-23 | 1 2011-02-24 | 3 I'd like: day | count ----------------------------- 2011-02-16 | 5 2011-02-17 | 10 2011-02-18 | 0 <<< Possible to include these zero count days? 2011-02-19 | 2 2011-02-20 | 1 2011-02-21 | 4 2011-02-22 | 2 2011-02-23 | 1 2011-02-24 | 3 Thanks for the help guys!

Hi guys, I'm using this jQuery demo - http://jqueryui.com/demos/droppable/#photo-manager Images can be dragged from the #gallery <ul> to the trash. I have this working correctly. What I want to do is have a few different "gallery" lists so that when an image is "recycled" back to the main area, it goes to the correct <ul>. Does anyone know how to adapt their demo to allow images to be added to the "trash" box but only allowed to get dragged back to their original list? Thanks for any tips or ideas to get me going! Cheers, RJP100

Nice, thanks a lot Keith, this has helped me to learn what can be done no end! Cheers!

Thanks kickstart that seems to work! By the way, what is the "fred" about? Cheers! RJP1

Hmm, I think I got my question wrong last night... What I need it to do is this: Table 1: id | userid | name | email | number_x Table 2 id | userid Count the number of records in table2 that have the same userid as table1, then divide number_x by this count and then output all data from table 1 (with the calculation) in ASC order... That's was what I meant because the number_y doesn't exist as such, it is actually the count of rows in the second table. Cheers, RJP

Hi guys, I was wondering the best way to do this... I'd like to output the result of number_x divided by number_y along with the data that is in Table 1 in ASC order of the division result. Is that easily doable? Table 1: id | userid | name | email | number_x Table 2 id | userid | number_y Thanks for any help guys, would be very useful! RJP

Thanks a lot silkfire, That really helped! Good idea! Cheers, RJP1

Hi guys, Could someone help me please? How do I check the following data foreach [id] and check to see if it the whole array has a certain ID in it? I can access individual data like this: $array['data'][0]['id']; but I'm not sure of the best way to check the array for a particular ID. Any ideas? Thanks, RJP1 Array ( [data] => Array ( [0] => Array ( [name] => sdajskdhsajd [category] => Application [id] => 323635478887920 [created_time] => 878978978 ) [1] => Array ( [name] => askdjakdjasd [category] => Application [id] => 36741566509099181 [created_time] => 2011-02-11T15:41:14+0000 ) [2] => Array ( [name] => asdasdasdasds [category] => Interest [id] => 1150572090878508639 [created_time] => 2011-02-11T15:38:40+0000 )

There's a very good reason. It's for user interface simplicity. Basically, my system has 2 text boxes for users to write in to add listings to my site. All this text is then output on their page. The add/edit page therefore has 2 boxes and it's just an extra input box for the sake of it, especially as I don't use the intro text for any other special reason. I may as well just have one box. But to save me from having to copy and paste everyones 2 boxes worth of data into the intro box i just wondered if there was a quicker MySQL way. And you helped me out a treat. I just need to backup and try it out later. Thanks again, RJP1