Matrixkid

Hi there, I'm looking to get the AVG's of multiple rows, multiple times. I want to get the AVG(price) from the last 10 rows, 20 rows, 50 rows, 100 rows, 200 rows Is there a way to do this without having to use multiple SELECTS? I was thinking maybe theres a way to pull the results using LIMIT 200, and then for each of the AVG's use that result set? Simple table setup. ID - Big Int,20 Price - Decimal 10,2 Entry_Time - Timestamp Thanks, MK

Perfect. Definitely was the table structure. Works like it should now. Thanks for the guidance.

Yeah, as I hit the button it occurred to me that it might be better to get rid of the option_id field and use the primary key as: ID,Symbol,Expiration,Strike, Category But the reason behind my current key is that option_id represents all of the above fields except for ID, and the ID field is not necessarily unique since some entries are at the exact same time. What would you suggest as the key? Here is some test data: INSERT INTO `chains` (`id`, `symbol`, `expiration`, `strike`, `category`, `last`, `volume`, `openint`, `stock_price`) ' VALUES ('2012-01-06 15:23:16', 'SPY', '2012-01-13', '115.00', 'C', '0.00', 0, 0, '127.88'), ('2012-01-06 15:23:16', 'SPY', '2012-01-13', '115.00', 'P', '0.02', 834, 510, '127.88'), ('2012-01-06 15:24:16', 'SPY', '2012-01-13', '115.00', 'C', '0.02', 981, 0, '127.99'), ('2012-01-06 15:24:16', 'SPY', '2012-01-13', '115.00', 'P', '0.03', 1098, 510, '127.99');

I'm having an issue with a SELECT statement. I find that if I have a single row of data as the result and using ORDER BY it slows it right down. As soon as I either remove the ORDER BY or add another row of data it returns it in reasonable time. My data returned for the most part will have more than 1 result (hence the LIMIT 2) but there are some cases where there is only an individual row and it's stalling things. Profiling Status Time starting 0.000082 checking permissions 0.000007 Opening tables 0.000020 System lock 0.000016 init 0.000043 optimizing 0.000021 statistics 0.000016 preparing 0.000016 executing 0.000004 Sorting result 0.000005 Sending data 15.276259 end 0.000013 query end 0.056590 closing tables 0.000030 freeing items 0.000156 logging slow query 0.000008 logging slow query 0.000007 cleaning up 0.000009 Showing rows 0 - 0 (1 total, Query took 15.3337 sec) SELECT expiration, strike, category, last, volume, stock_price FROM chains WHERE option_id = 'AMZN^^120203P00165000' AND DATE( id ) = DATE( NOW( ) ) AND TIME( id ) BETWEEN '09:30:00' AND '16:00:00' ORDER BY id DESC LIMIT 2 As soon as I insert a new row with the same option_id value and a different id(timestamp) value the results is this: Showing rows 0 - 1 (2 total, Query took 0.2272 sec) OR if I remove the ORDER BY id DESC it returns a similar timed result, less than a second: Showing rows 0 - 0 (1 total, Query took 0.5024 sec) The SQL EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE chains index NULL PRIMARY 27 NULL 2 Using where Lastly, The table structure: CREATE TABLE IF NOT EXISTS `chains` ( `id` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `option_id` varchar(21) NOT NULL, `symbol` char(4) NOT NULL, `expiration` date NOT NULL, `strike` decimal(10,2) NOT NULL, `category` char(1) NOT NULL, `last` decimal(10,2) NOT NULL, `volume` int(50) NOT NULL, `openint` int(50) NOT NULL, `stock_price` decimal(12,2) NOT NULL, `stock_volume` int(20) NOT NULL, PRIMARY KEY (`id`,`option_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; In the end, im trying to grab the "latest" two results. Thanks ahead of time for the help! It's much appreciated!

Hi there, I have a table as outlined below and I'm trying to get the MAX and MIN values for a specific Id but only if the MAX entry date is greater than the MIN entry date. table: ID ENTRY_DATE NAME PRICE 1 2012-01-23 16:09:35 MONKEY 99.33 2 2012-01-23 11:04:09 MONKEY 97.65 3 2012-01-23 09:31:19 MONKEY 93.05 4 2012-01-23 15:12:14 DICE 30.49 5 2012-01-23 12:01:24 DICE 32.00 6 2012-01-23 08:01:24 DICE 31.00 So what im trying to do is grab the MAX(price) and MIN(price) when the MAX(price) entry_date is greater than the MIN(price) entry_date. From the table above, my result would be: NAME MaxPrice MinPrice PriceDiff MONKEY 99.33 93.05 6.28 'Dice' would not show up because the MAX date/time happens before the MIN. I have tried a LEFT JOIN but I cant seem to figure it out. Here is what I have right now, but it obviously grabs all results as I cant seem to figure out how to compare the entry dates... SELECT name, MAX(price) as MaxPrice, MIN(price) as MinPrice, MAX(price)-MIN(price) AS PriceDiff, FROM products WHERE DATE(entry_date) = DATE(NOW()) GROUP BY name ORDER BY PriceDiff DESC Thanks for the help!

Perfect. Thanks!

Im currently using load to make the request. I believe load has callback functionality. Thanks!

Hey Rajiv, That didnt work. The element, input.calendarSelectDate doesnt exist upon page load, and therefore is bound to nothing. It only exists upon an ajax request. I hope that helps clarify it. Thanks MK

Hi there, Im running into an issue with the jquery ui datepicker plugin. I have it working and functioning correctly, but I need to change the way it operates. here is my current code: $("input.calendarSelectDate").live('click',function(){ $(this).datepicker({inline: true, showButtonPanel: true, buttonImage: '/cal.gif', buttonImageOnly: false, showButtonPanel: true, showOn:'both'}).datepicker('show'); }); the problem is is that the input field is loaded from an ajax call. and so the button doesnt appear next to the input box until the input field has been clicked. Is there a way to display the button before the click action is made? What about loading the the datepicker function with another event? Is there a way to bind it after the ajax call, so its almost like it runs on document.ready, with just $('#inputfield').datepicker(); - without the 'click' action binding it? Thanks for the help. MK

Oh I see, i have to replace the bracket and the double quote. Thanks for the help, its much appreciated!

yep, here it is: $html = '[ "\x3cdiv class=\"medium-content-below-item\"\x3e\n \x3cdiv class=\"desc\"\x3e\n \x3ch3 class=\"medium-content-below-item-title\"\x3e\x3ca class=\"title_link\" href=\"music/albums/1633820\"\x3e\n \x3cspan \x3eEmpire State Of Mind\x3c/span\x3e\n \x3c/a\x3e\x3c/h3\x3e\n \n \x3ca href=\"music/artists/195154/jay-z\"\x3e\n \x3cdiv class=\"medium-content-below-item-subtitle\"\x3eJay-z\x3c/div\x3e\n \x3c/a\x3e\n \n \x3cdiv class=\"medium-content-below-item-time\"\x3e10:32pm\x3c/div\x3e\n \n \n \x3c/div\x3e\n\x3c/div\x3e\n" ]'; $string = str_replace("]","",$html); $string = str_replace("[","",$string); echo strip_tags(html_entity_decode($string));

For me it shoots out the exact same thing. Looks like its the square brackets messing things up. thanks MK

Hey guys Ive been trying for a couple hours now to parse a data string but Im having no luck. I have the following: [ "\x3cdiv class=\"medium-content-below-item\"\x3e\n \x3cdiv class=\"desc\"\x3e\n \x3ch3 class=\"medium-content-below-item-title\"\x3e\x3ca class=\"title_link\" href=\"music/albums/1633820\"\x3e\n \x3cspan \x3eEmpire State Of Mind\x3c/span\x3e\n \x3c/a\x3e\x3c/h3\x3e\n \n \x3ca href=\"music/artists/195154/jay-z\"\x3e\n \x3cdiv class=\"medium-content-below-item-subtitle\"\x3eJay-z\x3c/div\x3e\n \x3c/a\x3e\n \n \x3cdiv class=\"medium-content-below-item-time\"\x3e10:32pm\x3c/div\x3e\n \n \n \x3c/div\x3e\n\x3c/div\x3e\n" ] Ive tried preg_replace, str_replace and nothing works. if i try using ">" or "<" it doesnt replace, nor does "\x3c" or "\x3e" In the end, im looking to just replace everything so im left with Empire State of Mind - Jay-z - 10:32pm If someone could help me just getting a str_replace or something like that to work that would be great. Thanks MK

thanks for the help Zanus, I appreciate it. Unfortunately the revision doesnt work either. I have to remove the ) as songCheck part as it returns an error. This statement returns no results: SELECT * FROM TESTDB WHERE song NOT IN ( SELECT DISTINCT song FROM TESTDB WHERE weeknum <= 49 AND yearnum <= 2009 ) This statement returns the correct result: SELECT * FROM TESTDB WHERE song NOT IN ( SELECT DISTINCT song FROM TESTDB WHERE weeknum < 49 AND yearnum <= 2009 ) I can run the NOT IN on the TESTDB and get the results i need, but as soon as I apply the query to my active db the query lags and goes nowhere. The database has over 190,000 records and grows every 3 hours. heres the error: [Err] 1053 - Server shutdown in progress Im not really sure where to go from here. Thanks again. MK

Affected rows: 0 Time: 0.032ms Nope. Should return 1 row, JVC (from the table in the first post). If I change NOT EXISTS to EXISTS, it gives me the opposite results. All the songs played prior to week 49. But as soon as I make it NOT then it returns nothing.

Im trying the NOT EXISTS but keep getting 0 results. SELECT Count(song) AS CountSong, artist , song FROM TESTDB WHERE weeknum = 49 AND yearnum = 2009 AND NOT EXISTS (SELECT artist,song FROM TESTDB WHERE weeknum < 49 AND yearnum <= 2009 GROUP BY song) GROUP BY song ORDER BY `CountSong` DESC

I think you misunderstood what results im looking for. If i remove the not in part I am selecting all songs from week 49, in year 2009. I want songs only that appeared in week 49, year 2009 and NEVER before. thanks MK

how would i got about constructing that statement? thanks for the help

Hi there, I need help constructing a select statement that in short selects songs from a database that have not been stored in any prior weeks or years. Below is the table: artist | song | weeknum | yearnum lg ab 49 2009 jvc cd 49 2009 sony ef 49 2009 lg ab 31 2009 song ef 22 2008 Lets assume the current week is 49. From the above example, my desired result would be jvc. Im trying to select all songs that were stored in week 49 of this year and never before. So, anything less than week 49, or any year less than 2009. I had this statement working before but because of the amount of data it no longer works. SELECT Count( song ) AS CountSong, artist , song FROM TESTDB WHERE weeknum =49 AND yearnum = 2009 AND song NOT IN (SELECT song FROM TESTDB WHERE weeknum < 49 AND yearnum <= 2009) GROUP BY song ORDER BY `CountSong` DESC I welcome all advice and opinions. I am aware that the database structure isnt the greatest. Anything to make this sort of select statement work and i'll listen. Thanks MK

Got it all. I just ran the following once the json reponse filled the div (after the $().html statement) var $lists = $('.rss-elem').find("div:contains(' minutes ago')"); $lists.parent().css("background","#E5806C"); Cheers

Hey, I was wondering if someone could push me in the right direction with an issue im having, actually two. First of all, im trying to change the background of the 1st parent div that contains a string. Heres what im using: $("div:contains(' minutes ago')").css("background", "rgb(229, 128, 108)"); Basically what is happening is that this is highlighting the parent, of the parent, of the parent divs. Where as I just want the first element highlighted. I was wondering how this could do be done? Secondly, I want to run this once my page has loaded - I have several elements that depend on a json response, and therefore when I run this jquery statement it does nothing, yet, if i run it in the javascript console in chrome or firebug, it works - since the divs are filled with information. How do I go about running the statement once the divs are filled with text from the json response? Thanks MK

reg = /(.*), /

Hi there, I have a date format like this right now: Sat, 17 Oct 2009 17:04:00 I need to turn that into: 2009-10-17T17:04:00Z My thought process was to remove with regex "Sat," and then just split the string up. So I was wondering then what the regex would be for "Sat, " This would apply to all cases. Fri, 16 Oct 2009 15:00:00 Thu, 15 Oct 2009 22:01:00 So I just need the first 3 letters with the comma and space after the comma to be removed. thanks! MK

thanks

Hi there, I have a string that looks like this; 2-Name,3-Name,4-Name,5-Name,6-Name,7-name Im trying to preg_split the string into an array so that all im left with is name, I can remove the comma no problem once its split up I have tried the following: $array_name = preg_split("/[0-9][-]/",$names); $array_name = preg_split("/[0-9-]/",$names); $array_name = preg_split("/[0-9]-]/",$names); Thanks for your help! Cheers MK