remove leading spcae from postcode

[mackin]

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?

[Muddy_Funster]

looks spot on

[mackin]

Thx Muddy I shall backup Table and try it - any idea how I would echo a result to show how many had been changed?

[Muddy_Funster]

$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.

 

[smerny]

just checking... did you also add trimming for new data so this doesn't happen again?

[mackin]

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

[mackin]

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;

[mackin]

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

[mackin]

sorted

changed

$updCount = mysql_affected_rows($update_postcodes);

to

$updCount = mysql_affected_rows();

and it works just fine

 

Many thanks