sptrsn

Barand, Thanks so much. I can make this work. How would add a sum total as at the bottom? Would you even do it in mysql? If I'm doing this in a vacuum, I would probably use javascript just do it in the browser. And at which point would you convert from int to date format?

Sorry. Got distracted and forgot to include table info. Here is the create with a little test data. And here is a simplified version of the query. (fewer fields. If I can make this work... I can add the additional columns) select * from ( select `date`, sum(created) created, sum(offers) offers from ( select DATE(from_unixtime(created)) `date`, 0 as created, 0 as offers from properties union all select DATE(from_unixtime(created)) `date`, count(*) created, 0 as offers from properties union all select DATE(from_unixtime(offer_date)) `date`, 0 as created, count(*) as offers from properties )a group by `date` )b CREATE TABLE IF NOT EXISTS `properties` ( `id` int(11) NOT NULL AUTO_INCREMENT, `created` int(11) NOT NULL DEFAULT '0', `offer_date` int(11) NOT NULL DEFAULT '0', `contract_date` int(11) NOT NULL DEFAULT '0', `purchase_date` int(11) NOT NULL DEFAULT '0', `sale_date` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; INSERT INTO `properties` (`id`, `created`, `offer_date`, `contract_date`, `purchase_date`, `sale_date`) VALUES (1, 1441065600, 1441152000, 1441238400, 1442016000, 1442534400), (2, 1441152000, 1441152000, 1441324800, 1445817600, 1450137600), (3, 1441238400, 1441324800, 1441324800, 1444953600, 1451433600), (4, 1441324800, 1441324800, 1441324800, 1446768000, 1452816000), (5, 1441411200, 1441411200, 1441411200, 1448928000, 1454284800);

I'm stuck like a wheel barrow in the mud. Wondering if someone might be able to help me write this query. I have a table where status change dates are held. I'm trying to build a query that sums each of the columns by date. In the end.... I should have a column of dates in the first column. several columns as listed below. (created, offers, contracts, closed, sold) Then the total number of records with dates that match the row date. (It think that makes sense) So I can tell ... how many records were created on each day. how many offers were made on each day etc etc. Here is the query I have so far which doesn't quite work. Display is attached. select * from ( select `date`, sum(created) created, sum(offers) offers, sum(contracts) contracts, sum(closed) closed, sum(sold) sold from ( select DATE(from_unixtime(created)) `date`, count(*) created, 0 as offers, 0 as contracts, 0 as closed, 0 as sold from properties union all select DATE(from_unixtime(offer_date)) `date`, 0 as created, count(*) as offers, 0 as contracts, 0 as closed, 0 as sold from properties union all select DATE(from_unixtime(contract_date)) `date`, 0 as created, 0 as offers, count(*) as contracts, 0 as closed, 0 as sold from properties union all select DATE(from_unixtime(purchase_date)) `date`, 0 as created, 0 as offers, 0 as contracts, count(*) as closed, 0 as sold from properties union all select DATE(from_unixtime(sale_date)) `date`, 0 as created, 0 as offers, 0 as contracts, 0 as closed, count(*) as sold from properties )a group by a.`date` )b

Thanks requinix. I reworked the URL, it returned a true json object and now it parses it just fine. That was a goat rodeo! Thanks for the input.

I was actually just stripping the renderOptions portion of the string. Here is the entire result.. renderOptions({"results":[{"locations":[{"latLng":{"lng":-112.35984,"lat":34.58752},"adminArea4":"Yavapai County","adminArea5Type":"City","adminArea4Type":"County","adminArea5":"Prescott Valley","street":"3037 N Kings Hwy W","adminArea1":"US","adminArea3":"AZ","type":"s","displayLatLng":{"lng":-112.3596,"lat":34.58752},"linkId":40959430,"postalCode":"86314-2815","sideOfStreet":"R","dragPoint":false,"adminArea1Type":"Country","geocodeQuality":"POINT","geocodeQualityCode":"P1AAA","mapUrl":"http://www.mapquestapi.com/staticmap/v3/getmap?type=map&size=225,160&pois=purple-1,34.58752,-112.35984,0,0|¢er=34.58752,-112.35984&zoom=12&key=Fmjtd|luua2duynh,rw=o5-hr1xq&rand=1015257776","adminArea3Type":"State"}],"providedLocation":{"location":"3037 N KINGS HWY W,PRESCOTT VALLEY, AZ 86314"}}],"options":{"ignoreLatLngInput":false,"maxResults":-1,"thumbMaps":true},"info":{"copyright":{"text":"© 2012 MapQuest, Inc.","imageUrl":"http://api.mqcdn.com/res/mqlogo.gif","imageAltText":"© 2012 MapQuest, Inc."},"statuscode":0,"messages":[]}}); I trimmed the "renderOptions(" and the closing ");" then tried json_decode. still nothing.

I'm trying the fetch some geocode data from mapquest. Google doesn't like me right now. I keep running into the query_limit. So I want to set a back up that will go into production when I get that error from the big G. I have my api key and I can put the string in a var. $geocode = file_get_contents($url) I selected json as the output and when I echo $geocode... here is the first portion of the string....(I want lat & lng) renderOptions({"results":[{"locations":[{"latLng":{"lng":-112.35984,"lat":34.58752},..... Now for the life of me, I can't do anything with it. I've tried json_decode() with the true argument there and not. $output = json_decode($geocode, true); $output = json_decode($geocode); vardump($output) is NULL. So, I thought, well maybe I don't need to decode it, but I can't figure out how to parse it. I'm lost. This admittedly is my first attempt as trying to work with a jason object, but jeesh, it doesn't seem like it should be that hard. Can someone start me in the right direction?

I got it. and concat(sale_date,' ',sale_time) > now()

I have separate date and time fields in my table. I need a where clause that will check if these two fields together are greater than now. This is an auction item where the end date and time are in these two fields and don't want to include the record if the auction has expired for this item. I've tried scads of different things. With the searching I've done... it seems like it should be something like this.... and DATE_ADD(n.sale_date, INTERVAL n.sale_time HOUR_SECOND) > now() Additionally, once this base clause works, I need to figure out how to add a 3 hour server time adjustment. Appreciate any input.

ok. that whole single quote/ double quote thing is still a pain in my back side. I was able to build that url using double quotes and make it work. So Thank you for that. Is there something you're aware of that I could study, and at the end I would actually understand all the quirks surrounding quotes? Thanks again.

Can someone tell me why the first example works, but the second example does not? file_get_contents('http://pinalcountyaz.gov/Departments/Treasurer/Pages/TaxBillSearch.aspx?p=10006015E-*'); $url = 'http://pinalcountyaz.gov/Departments/Treasurer/Pages/TaxBillSearch.aspx?p=10006015E-*'; file_get_contents($url);

To answer a couple questions. This little snippet of data is on a county website and contains the back taxes for a specific property. So... yes.... it is a consistent and unique ID on a page full of all kinds of data. The only thing I'm interested in is the back taxes... which... I intend to either fetch it live on page load when you view the detail page on my site for that property, or I'll run a job to fetch them all for the day and put it in the database. Depends on how much drag there is on my detail page. (foreclosure data etc) So using the string function suggested by AyKay47 won't work for me, since I'm interested on one specific span tag, not all the tags. I did not notice the ~~ on either side of the expression. My bad. I'm going to give that a try and see that works. Silkfire asked who taught me to use regex for scraping. I simply found several examples of people using regex. I'd love to figure how to use dom and xpath, but in all the searches I did, I couldn't find an example of how to go about using it. I simply attempted using the stuff I could find examples for. However, upon your suggestion, I'm going to do a little searching and see if that might work for me. I appreciate the suggestion. At the moment.... I do have a working solution. But just for my education, I'm going to attempt to get it working with these other ideas. Looking forward to learning more. Thanks, Steve

Well I found something else that worked. (Gotta love Google) $str = '<span id="TotalDue">$1,004.28</span>'; $doc = new DOMDocument(); $doc->loadHtml($str); $el = $doc->getElementById('TotalDue'); echo $el->textContent; Hadn't ever even heard of that function, but I found an example that looked like it might do the trick.. and voila!

ok. That helps some. So then should this work? I've obviously got something wrong as it doesn't work. $str = '<span id="TotalDue">$1,004.28</span>'; $pattern = '/<span id="TotalDue">([^<]+)</span>/'; preg_match_all($pattern, $str, $matches); print_r($matches[0]);

After going through a dozen php scraper classes, I finally got one to give me back the html in a string. Woohoo!! I only need one little piece of data from this page. And the data that I want is in a span tag with an id.. Like so.... <span id="TotalDue">$1,004.28</span> I Want that dollar figure from between the tags. "$1,004.28" I've tried um-teen different ways. But, as you well know, regular expressions can be hard for newbs. Is there some kind soul out there that can help me with this?

This is great. Thank you for your input. I'm going to take another crack at this.