Jump to content

Scooby08

Members
  • Posts

    244
  • Joined

  • Last visited

Everything posted by Scooby08

  1. I'm sorry, the example I sent should be like so: (if this contains all numbers (civic#), no match)\s(lot|unit|spc|space|ste|suite|bldg|building) Thanks..
  2. It seems so simple, but just cannot get this figured out.. In the following I'm trying to match the word "space" that does not contain numbers in the word before it.. 161 space rd <-- no match 209 summit ave space 6 <-- match Ultimately am trying to match (lot|unit|spc|space|ste|suite|bldg|building) that do not have numbers in the word before them.. Close example.. (if this contains a number, no match)\s(lot|unit|spc|space|ste|suite|bldg|building) Thanks so much!
  3. Alrighty DavidAM.. Good stuff! I appreciate the response.. In my case, it's definitely necessary to store aggregated data for summary reports (being there's so many records), so I created a child table like so.. But then again you said to remove anything that can be calculated by other rows so I then broke it down to this Am I on the right track here? Or have I misread your suggestions.. Thanks!
  4. MySQL Version 5.5.28 I am working on storing summary data such as counts, averages, rates, times for each hour of the day, grouped by "account_id", "affiliate_id", "hour" for each day.. I've included the start of a table to give an idea as to what I'm trying to do so far, but as you can see the table has many columns and will have many more when I add the rest of my columns.. The purpose is for faster page loading of reports. I guess what I'm looking for is any advice on alternative solutions that may work for me here.. Or is this the proper way to store hourly data for faster page loads?? Thanks all!!
  5. Thanks for replying Barand! The query you provided is keeping the Joined records.. We do not want to keep those, just want to keep posts_inbound "a" if there are no records with the same ssn within the 10 minute timeframe, otherwise move on and keep nothing from that record.. Want to end up with 1 record per ssn per 10 mintues from that record.. I'm having troubles trying to explain my objective clearly.. Maybe this might help: if the current rows social_security_number does not exist between now and 10 minutes ago (no prior posts within 10 mins) keep the current row in the result else do not keep the row and move on to the next row and repeat Trying to filter out 10 minute uniques..
  6. MySQL 5.1.46 The Goal: On each row, check if there are any records having the same ssn within the previous 10 minutes using the "created_on" datetime field.. If a record *is* found within the previous 10 minutes, don't collect the current row and move onto the next row and do the same thing. If a record *is not* found, collect the current row and move onto the next row and do the same thing. SHOW CREATE TABLE posts_inbound | posts_inbound | CREATE TABLE `posts_inbound` ( `id` int(11) unsigned NOT NULL auto_increment, `publisher_id` int(11) unsigned default NULL, `transaction_id` varchar(32) default NULL, `api_key` varchar(64) default NULL, `score_version` char(10) default NULL, `score` int(11) NOT NULL default '0', `sold` char(1) default NULL, `price` decimal(5,2) NOT NULL default '0.00', `post_status` varchar(32) default NULL, `post_errors` text, `postback_status` varchar(32) default NULL, `postback_errors` text, `reference_id` varchar(64) default NULL, `affiliate_id` varchar(64) default NULL, `affiliate_sid` varchar(64) default NULL, `lead_id` varchar(64) default NULL, `buyer_id` varchar(64) default NULL, `buyer_sid` varchar(64) default NULL, `redirected` tinyint(1) NOT NULL default '0', `source_url` varchar(64) default NULL, `ip_address` varchar(16) default NULL, `user_agent` varchar(255) default NULL, `time_stamp` datetime NOT NULL default '0000-00-00 00:00:00', `is_mobile` tinyint(1) NOT NULL default '0', `loan_amount` decimal(6,2) NOT NULL default '0.00', `first_name` varchar(64) default NULL, `last_name` varchar(64) default NULL, `email` varchar(64) default NULL, `gender` char(1) default NULL, `date_of_birth` char(10) default NULL, `marital_status` char(1) default NULL, `drivers_license_number` varchar(30) default NULL, `drivers_license_state` char(2) default NULL, `social_security_number` char(9) default NULL, `is_military` char(1) default NULL, `best_contact_time` char(1) default NULL, `mother_maiden_name` varchar(64) default NULL, `home_phone` varchar(32) default NULL, `cell_phone` varchar(32) default NULL, `address` varchar(40) default NULL, `city` varchar(32) default NULL, `state` char(2) default NULL, `zip` char(5) default NULL, `rent_or_own` tinyint(1) default NULL, `monthly_rent_or_mortgage` decimal(6,2) NOT NULL default '0.00', `time_at_current_address` tinyint(2) default NULL, `previous_address` varchar(40) default NULL, `previous_city` varchar(32) default NULL, `previous_state` char(2) default NULL, `previous_zip` char(5) default NULL, `employer_name` varchar(64) default NULL, `employer_address` varchar(40) default NULL, `employer_city` varchar(32) default NULL, `employer_state` char(2) default NULL, `employer_zip` char(5) default NULL, `work_phone` varchar(32) default NULL, `time_at_job` tinyint(2) default NULL, `monthly_income` decimal(7,2) NOT NULL default '0.00', `income_source` tinyint(1) default NULL, `pay_frequency` tinyint(1) default NULL, `first_pay_day` char(10) default NULL, `second_pay_day` char(10) default NULL, `last_pay_amount` decimal(7,2) NOT NULL default '0.00', `is_direct_deposit` tinyint(1) default NULL, `bank_name` varchar(64) default NULL, `bank_phone` varchar(32) default NULL, `bank_account_type` char(1) default NULL, `bank_account_length` tinyint(2) default NULL, `bank_routing_number` char(9) default NULL, `bank_account_number` varchar(17) default NULL, `reference_first_name` varchar(64) default NULL, `reference_last_name` varchar(64) default NULL, `reference_phone` varchar(32) default NULL, `reference_relation` varchar(20) default NULL, `time_start` double default NULL, `time_end` double default NULL, `time` double default NULL, `created_date` date NOT NULL default '0000-00-00', `created_on` datetime NOT NULL default '0000-00-00 00:00:00', `updated_on` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `publisher_id` (`publisher_id`), KEY `transaction_id` (`transaction_id`), KEY `api_key` (`api_key`), KEY `created_date` (`created_date`), KEY `social_security_number` (`social_security_number`), KEY `idx_bank_account_type` (`bank_account_type`), KEY `idx_email` (`email`), KEY `ssn_id` (`social_security_number`,`id`) ) ENGINE=MyISAM AUTO_INCREMENT=7874256 DEFAULT CHARSET=latin1 | Here's sort of a pseudo query of what I'm trying to collect: SELECT * FROM posts_inbound WHERE (social_security_number NOT BETWEEN (created_on - INTERVAL 10 MINUTE) AND created_on) Here's a query that works, but is very very slow: SELECT social_security_number AS ssn, created_on AS timeframe_start, (SELECT COUNT(id) FROM posts_inbound WHERE social_security_number=ssn AND (created_on >= (timeframe_start - INTERVAL 10 MINUTE) AND created_on < timeframe_start)) AS dup_count FROM posts_inbound WHERE (created_date BETWEEN '2012-08-09' AND '2012-08-10') GROUP BY social_security_number, id HAVING dup_count = 0 The explain for the above query: mysql> explain SELECT social_security_number AS ssn, created_on AS timeframe_start, (SELECT COUNT(id) FROM posts_inbound WHERE social_security_number=ssn AND (created_on >= (timeframe_start - INTERVAL 10 MINUTE) AND created_on < timeframe_start)) AS dup_count FROM posts_inbound WHERE (created_date BETWEEN '2012-08-09' AND '2012-08-10') GROUP BY social_security_number, id HAVING dup_count = 0; +----+--------------------+---------------+-------+-------------------------------+------------------------+---------+------+--------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------------+-------+-------------------------------+------------------------+---------+------+--------+-----------------------------+ | 1 | PRIMARY | posts_inbound | range | created_date | created_date | 3 | NULL | 198107 | Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | posts_inbound | ref | social_security_number,ssn_id | social_security_number | 10 | func | 9 | Using where | +----+--------------------+---------------+-------+-------------------------------+------------------------+---------+------+--------+-----------------------------+ I'm game to either write an entirely new query or optimize the one above that works.. Thanks to all who reply!
  7. I ended up using the following: Changing count(*) to count(id) did help to speed it up a bit, but my overall issue ended up being in the surrounding code outside of that query.. There's about 2,500,000 rows of data and now it's quick as can be and is not lagging in any way.. Thanks to all!
  8. I have a query that seems to lag a bit and although I'm not entirely sure the issue is this query, but it's a starting point.. Is there any way to optimize this query any further? I have an index on the column "created" and the created date is in the following format "2012-07-13". When running the query I get a count of 43551, but when I run an explain it says the following.. I've also tried the following variations for the heck of it, but they all come up the same.. Any ideas??
  9. I'm just looking for a few recommendations of encrypting/decrypting data such as ssn's, bank account #'s, routing #'s, stuff like that.. I've never had to do this in the past and when browsing I have ran across various ways to do so such as, crypt(), mcrypt(), base64_encode() to name a few.. Just don't know which is best to choose and was hoping someone with experience could send some suggestions or advice.. Thanks all..
  10. Thanks again Josh.. I'll get er' from here.. By the way, if anybody can answer just the regex question to match "Box 12" and not match "1555 pine box elder rd", regardless as to what I'm using it for, that would be most helpful!!
  11. You are correct Josh, except for the "Box 12" one.. I am posting these to a third party and they run a validation on these as well and they are saying that it is a po box address.. All I'm trying to do is create a custom filter that will work for this third party so I don't post them po box address as they do not want them.. It's really close to being where I need it.. I am already using the code and it's working great, but the "Box 12" types still get by and in the even that an address actually has a "p" in front of the work "box" it'll treat it as a po box when it really is not.. (1555 pine box elder rd) I really do appreciate the help Josh! Thank you
  12. Yes I did Josh.. The code I need would be more like so... if (is po box regex that is valid or not) { // throw it out } else { // "good" address, do something } And yes, that's exactly right Pikachu2000...
  13. Thanks but that will not work in this case.. I only want addresses that aren't po boxes.. and I have no control as to how they enter the po box address so I have to work with what I have and this is the only way.. The above are all examples of po box addresses I have received..
  14. Hello again Josh.. I don't want valid po box formats.. I'm trying to filter out any address that has anything to do with a po box.. I have been playing around some more and am pretty close to what my goal is.. Here's the regex: /(p(.*)?o(.*?)?)(\s+)?(bo?x)?(\s+)?[#]?(\d+)?/i Now that matches all of the following except for the marked ones with explanations next to them.. Thank you for replying Josh!
  15. Hello Josh... I cannot let you know all of the possible situations that the P.O. Box address can show up, but I can let you know that each day I receive at least 2-4 more possibilities.. Here are todays: POB 533 pob 1598 P O Box 1805 Po bo, 444 Please help as this will be the ultimate P.O. Box regex!!
  16. I have the following regex: /([pP]{1}(.*?)[oO]{1}(.*?))?([bB][oO]?[xX])(?![a-zA-Z])(\s+)?[#]?(\d+)?/i I'm trying to also get it to match the following line: P.O. 863 Also, I'm open for any pointers on tidying it up as well.. Thanks!
  17. I'm just looking for an opinion on a recommended way to search a database for a specific value.. Would it be one of the following or perhaps there is a better approach?? <?php $count = mysql_result(mysql_query("SELECT count(*) FROM bank_routing_numbers WHERE blacklist='091000019'"),0); if ($count > 0) { // exists } else { // doesnt exist } ?> or <?php $num_rows = mysql_num_rows(mysql_query("SELECT blacklist FROM bank_routing_numbers WHERE blacklist='091000019'")); if ($num_rows > 0) { // exists } else { // doesnt exist } ?>
  18. That worked perfectly! Thanks abareplace! jQuery.validator.addMethod('phoneUS', function(phone_number, element){ phone_number = phone_number.replace(/\s+/g, ''); return this.optional(element) || phone_number.length > 9 && phone_number.match(/^(1-?)?(\([2-9]\d{2}\)|[2-9]\d{2})-?[2-9]\d{2}-?\d{4}$/) && !phone_number.match(/^(?:1-?)?(\d)\1\1-?\1\1\1-?\1\1\1\1$/); }, 'Please specify a valid phone number.');
  19. I'm using the following code to validate US Phone Numbers: jQuery.validator.addMethod('phoneUS', function(phone_number, element){ phone_number = phone_number.replace(/\s+/g, ''); return this.optional(element) || phone_number.length > 9 && phone_number.match(/^(1-?)?(\([2-9]\d{2}\)|[2-9]\d{2})-?[2-9]\d{2}-?\d{4}$/); }, 'Please specify a valid phone number.'); I would like to add a check to not allow phone numbers like 222-222-2222 that have all the same digits.. I think it would be something like below, but that could check all numbers rather than just 222-222-2222.. phone_number.match(/^(1-?)?(\([2-9]\d{2}\)|[2-9]\d{2})-?[2-9]\d{2}-?\d{4}$/) && !phone_number.match(/^222-222-2222$/); So I need a regex like this but for all digits.. ^222-222-2222$ Thanks!
  20. Here's an example of my issue with the dollar sign.. This one treats the dollar sign like an expression.. <?php $input = '<div>stuff</div>'; $replace = '<div>$600,000. Test? Test!</div>'; $output = preg_replace('/<div>(.*?)<\/div>/',$replace,$input); echo $output; // <div>0,000. Test? Test!</div> ?> So I tried out preg_quote and now the dollar sign is fixed, but the punctuation now has slashes which are not wanted.. <?php $input = '<div>stuff</div>'; $replace = '<div>'.preg_quote('$600,000. Test? Test!').'</div>'; $output = preg_replace('/<div>(.*?)<\/div>/',$replace,$input); echo $output; // <div>$600,000\. Test\? Test\!</div> ?> I guess I'm looking for the best alternative to this situation so that dollar signs aren't treated like expressions and the punctuation does not have backslashes.. Thanks!
  21. There's the winner! So simply put.. Thank you very much mjdamato!
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.