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.

Instead of breaking down and actually learning how to use ajax, I'm thinking about trying to do something so I don't need a page refresh every time, and the filtered data is nearly instant. I just use the session array var, and build a new array from the rows that meet the conditions. I'm thinking of loading a two dimensional array into a session var, then use some input buttons, and use the post vars to filter the array based on a set of predefined conditions. ie status = 0, status = 1. It's a fairly small number of conditions applied to two different fields. So, I need to access specific fields within a row, and essentially create a new array either including or excluding that row depending on whether it met the condition. I'm struggling on several parts. 1. not sure how to build the array so that I can access specific fields, then not sure how to access those fields. Do I do this..... to build the array? .... while($row = mysql_fetch_assoc($result)){ $array[] = array($row['field1'], $row['field2']); } I was thinking about using a foreach and is_array() to get to each row... foreach($array as $key => $value){ if(is_array){ foreach($value as $k => $v){ if($v[0] == 1){ //stuck here $v[0] is not a field, it's the first char of the string. not sure how to access a field $new_array[] = $value; //stuck here. I need to put the whole row back into the array if the condition was true. seems like I would have to use the field selectors and rebuild the array. } } } } What would you do? Open to any ideas.

Thanks for the input. I like the idea of the return-path. Hadn't considered that it might return delivery failure errors. So... I'm going to add a return-path header. Grab the return var and make three attempts on error with a small sleep() in between. (anything wrong with this?) Write the failed mail vars to a log file and/or mail the errors to me so at least I can run interference on failure. In theory, doing this... I would know if the mail function failed to pass the vars to smtp and be able to distinguish that from a delivery failure. Am I correct in my assumptions?

We're running a foreclosure bid service that sends a lot of email status notifications. (ie bid received, high number, outbid, sale results etc) Most days it works great. However, occasionally, there are a handful of emails that people claim they never got. I have a log file I write to immediately after the mail function. It is not conditioned on the return var... it just writes the mail vars to the log. So according to the log it APPEARS that all the emails were sent. I've asked the host to check if there are any smtp errors. He says he doesn't see any. So..... is there a best practice for attempting to send again on failure? Should I just try a few times if it returns false? Should I write it all to the db, use a pending/success flag and cron to work through the pendings? That way it continues to attempt failures and I can run a failure report. I don't know. Any bright ideas?

ahahahaha! You're a genius. Never occurred to me that had to be inside the function. That worked great. I'm very encouraged to hear you say that mysql_real_esacpe_strings() is very effective. In the few tests I did, it didn't seem like it removed enough of the scarry stuff. I should probably give up on this and just make sure mysql_real_escape_strings is working, cuz that would be easy. thanks again.

I haven't had a problem with sql injection yet, but I'm scarred to death. I didn't do any form data validation as I was building my site. I'm just now starting to learn how. Magic_quotes is turned on at my host. I know about htmlspecialchars and mysql_real_escape_string and stripslashes and htmlentities. In testing each of these, it seems they all miss one thing or another. so, I created an array of words and characters that I can't for the life of me imagine anyone would ever need on any form in my site, that I THINK addresses most if not all of the really bad things. But hey... I'm new to this. So here is my array and using print_r() it looks pretty good. $badstuff = array('select','delete','update','insert','drop','=',';','"','\'','<','>','/'); Array ( [0] => select [1] => delete [2] => update [3] => insert [4] => drop [5] => select [6] => delete [7] => update [8] => insert [9] => drop [10] => = [11] => ; [12] => " [13] => ' [14] => < [15] => > [16] => / ) My str_ireplace() function works fine within the code, but I'd like to create a function using str_ireplace(). I am failing miserably. Here is my function that doesn't work... function strip($string){ return str_ireplace($badstuff,"",$string); } Here below..... the first line, that uses the function does NOT work. The second line that just uses str_replace() function works fine. echo strip($string).'<br>'; echo str_ireplace($badstuff,"",$string) Can anyone tell me why my function does not work? I've read and watched 20 tutorials and just can't see the problem. Thanks for any input.

yea, that worked great. Thank you very much for your help. I just insert the comma separated values in the db. (cleaner that way anyway) Then when I build the query, I applied your str_replace right before building the array. if ($row['city'] !='') $city = str_replace(',',"','",$row['city']); $whereClause[]="prop_city in ('".$city."')"; Thanks again.

well it's not going to let me insert that, so I'll apply that script when I take the values out of the db. That should work.

You're correct. That statement works great. But the moment I add the insert statement back in, I get that same error. Is there something about this value ......"city1',' city2',' city3 " that would cause a problem that you're aware of?

I get the following error... A fatal MySQL error occured. Query: Error: (1136) Column count doesn't match value count at row 1 Not completely sure why. My insert statement works fine without the str_replace. I've been able to get it to insert several city names with the commas. Is there something about the apostrophe that makes mysql think it's an additional column? The field is varchar 200 (innodb)

It's probably not the right way, but it'll do for now. This is part of a query builder. I want to replace a comma with apostraphe comma apostraphe. in other words this...... city1, city2, city3 becomes city1', 'city2', 'city3 Ultimately this is helping me build a dynamic query that will read..... select * from table where city in ('city1','city2','city3') the surrounding parens and apostraphes are in the whereclause[] statement, so I don't need them here. I've tried a multiple iterations, but they all end up looking similar to this, which obviously doesn't work... $city= preg_replace(",", "', '", $city); I appreciate any help.