mackin Posted April 4, 2012 Share Posted April 4, 2012 Hi, I have a table of UK postcodes - in the format SA99 1AF etc. I have noticed that some entries have an added leading space that shouldnt be there and is affecting a radius search. How do i alter this code to check for a leading space and if so remove it.? mysql_select_db($database_contractors, $contractors); $query_update_postcodes2 = "UPDATE hotels SET est_postcode = ltrim(est_postcode) "; $update_postcodes = mysql_query($query_update_postcodes2, $contractors) or die(mysql_error()); Is this anywhere near? Link to comment https://forums.phpfreaks.com/topic/260339-remove-leading-spcae-from-postcode/ Share on other sites More sharing options...
Muddy_Funster Posted April 4, 2012 Share Posted April 4, 2012 looks spot on Link to comment https://forums.phpfreaks.com/topic/260339-remove-leading-spcae-from-postcode/#findComment-1334330 Share on other sites More sharing options...
mackin Posted April 4, 2012 Author Share Posted April 4, 2012 Thx Muddy I shall backup Table and try it - any idea how I would echo a result to show how many had been changed? Link to comment https://forums.phpfreaks.com/topic/260339-remove-leading-spcae-from-postcode/#findComment-1334337 Share on other sites More sharing options...
Muddy_Funster Posted April 4, 2012 Share Posted April 4, 2012 $updCount = mysql_affected_rows($update_postcodes); echo $updCount; mysql_affected_rows() returns the number of rows affected by an update/insert query, kind of like mysql_num_rows() does for select queries. Link to comment https://forums.phpfreaks.com/topic/260339-remove-leading-spcae-from-postcode/#findComment-1334349 Share on other sites More sharing options...
smerny Posted April 4, 2012 Share Posted April 4, 2012 just checking... did you also add trimming for new data so this doesn't happen again? Link to comment https://forums.phpfreaks.com/topic/260339-remove-leading-spcae-from-postcode/#findComment-1334354 Share on other sites More sharing options...
mackin Posted April 4, 2012 Author Share Posted April 4, 2012 cheers muddy - i shall do that shortly Smerny - the information is added by importing spreadsheets, as i have a few people doing them the errors slipped through - but ill run the update query every week or so to change them Thanks Link to comment https://forums.phpfreaks.com/topic/260339-remove-leading-spcae-from-postcode/#findComment-1334378 Share on other sites More sharing options...
mackin Posted April 4, 2012 Author Share Posted April 4, 2012 I got an error - have i got a comma in wrong place!! Warning: mysql_affected_rows(): supplied resource is not a valid MySQL-Link resource in /homepages/27/d374255998/htdocs/ship/update_postcode.php on line 35 mysql_select_db($database_contractors, $contractors); $query_update_postcodes2 = "UPDATE hotels SET est_postcode = ltrim(est_postcode) "; $update_postcodes = mysql_query($query_update_postcodes2, $contractors) or die(mysql_error()); $updCount = mysql_affected_rows($update_postcodes); echo $updCount; Link to comment https://forums.phpfreaks.com/topic/260339-remove-leading-spcae-from-postcode/#findComment-1334383 Share on other sites More sharing options...
mackin Posted April 4, 2012 Author Share Posted April 4, 2012 The Update code itself works - but the report on how many rows affected doesn't - just gonna check its not due to my version of mysql Link to comment https://forums.phpfreaks.com/topic/260339-remove-leading-spcae-from-postcode/#findComment-1334411 Share on other sites More sharing options...
mackin Posted April 4, 2012 Author Share Posted April 4, 2012 sorted changed $updCount = mysql_affected_rows($update_postcodes); to $updCount = mysql_affected_rows(); and it works just fine Many thanks Link to comment https://forums.phpfreaks.com/topic/260339-remove-leading-spcae-from-postcode/#findComment-1334413 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.