mackin

trying to do it like this but the update function isnt correct if (strpos($row_update_postcodes['est_postcode'], ' ')===false){ //no space $row_update_postcodes['est_postcode'] = substr_replace($row_update_postcodes['est_postcode'], ' ', -3, 0); //insert space } mysql_select_db($database_contractors, $contractors); $query_update_postcodes2 = "UPDATE hotels SET est_postcode = "$row_update_postcodes['est_postcode']" WHERE id = "$row_update_postcodes['id']")";

I have a table of Postcodes - some of them have no spaces between Outcode and Incode i.e LA138UT instead of LA13 8UT. I need a php code to search the column for no spaced postcodes, and insert a space 3 characters from the end of the postcode string. I have this code to insert the space if (strpos($str, ' ')===false){ //no space $str = substr_replace($str, ' ', -3, 0); //insert space } and i can make it work one postcode at a time using a form, but I want to run the script and it will update them all. Any advice ?

works a treat Kicken thx v much

looks good kicken - ill try that in the AM, Cheers

thx for the reply blackknight can i use a negative number - the count has to start from the end of string. The Part before the space can be 3 or 4 chars, but always has 3 after the space.??

I have a table with postcodes in it. At some point some of the codes will be entered with spaces (LA12 5TH) and some without (LA17HU) what i need is a function to check if there is a space, if not - count back 3 characters from the end of the string and insert a space. I don't know which string function to use - looked at pad but that seems to be for either end of string and not the middle - any ideas?

enabled it on server (didnt think i was allowed to) works perfectly now Thx for advice Dan u maniac

BUGGER!! Warning: file_get_contents() [function.file-get-contents]: URL file-access is disabled in the server configuration in /homepages/27/d374255998/htdocs/ship/bandb.php on line 184 i guess there aint much i can do about this?

i shall do that now maniac thx - ill be back

Hi, I have this code but i cant get it to work. I have broken it down to sections and I think the get file contents may be at fault as when i dump the array it come back bool false. The URL creates a page with CSV values of longitude and latitude from a postcode. I want to give the variables $custlat1 and $custlong1 the values from the geocoded co-ordinates. The CSV values returned from the URL input are 200,5,54.1202442,-3.2078272 $pc1 = 'http://maps.google.com/maps/geo?q=la139hu,+UK&output=csv&sensor=false&key=ABQIAAAAcclaxnepdvvxx5D2PAnHtRSLcuoGw6G6HnB4VN4WqoMz7tmtKhTHGV82iKKTXvAg7YFpCFA6ptRc9g'; $data1 = @file_get_contents($pc1); $result1 = explode(",", $data1); $custlat1 = $result1[2]; $custlong1 = $result1[3]; can anyone help? or point me in the right direction

hahah no - not THAT hard!

$matches = implode("', '", array_unique($postcode_radius)); echo $matches; $sql2 = "SELECT * FROM hotels WHERE (SUBSTRING_INDEX(est_postcode, ' ', 1)) IN ('$matches')"; $result2 = mysql_query($sql2) or die('query failed: ' . mysql_error()); while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) { echo $row["est_postcode"] ."<br>". $row["est_landline"]; } works like a charm!!! thx very much

I had tried variations as i couldnt get it to work with IN() - if i use this code $matches = implode("', '", array_unique($postcode_radius)); echo $matches; $sql2 = "SELECT * FROM hotels WHERE (SUBSTRING_INDEX(est_postcode, ' ', 4)) IN ('$matches')"; $result2 = mysql_query($sql2) or die('query failed: ' . mysql_error()); while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) { echo $row["est_postcode"] ."<br>". $row["est_landline"]; } it echo's LA14', 'LA13', 'LA12', 'LA15', 'LA16', 'LA18 query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '', 'LA13', 'LA12', 'LA15', 'LA16', 'LA18)' at line 1

$matches = implode(array_unique($postcode_radius)); echo $matches; $sql2 = "SELECT * FROM hotels WHERE (SUBSTRING_INDEX(est_postcode, ' ', 4)) LIKE $matches"; $result2 = mysql_query($sql2) or die('query failed: ' . mysql_error()); while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) { echo $row["est_postcode"] ."<br>". $row["est_landline"]; } var_dump($result2); provides LA14LA13LA12LA15LA16LA18query failed: Unknown column 'LA14LA13LA12LA15LA16LA18' in 'where clause'

OK i cant get that to work - and to be honest I am sure there is a simpler solution - so let me put it this way. I have the array array_unique($postcode_radius) - which I use the unique as some of the Outcodes repeat themselves and it isn't necessary (LA14, LA14, LA13). I use implode to get the contents of the array for the SELECT statement. $matches = implode(array_unique($postcode_radius)); - there is the first issue - the correct use of "', '", in the explode function to create the ('LA12', 'LA17', 'LA15', 'LA16') for SELECT - at the moment the echo gives LA14LA13LA12LA15LA16LA18. NEXT The column the SELECT will be searching is called est_postcode which contains full postcodes (LA13 9UT) - so I need the SELECT to search est_postcode using perhaps LIKE $matches% - so it will take the LA14 part and what comes after doesnt matter? How can I create the full SELECT statement to return all the postcodes that contain the OUTCODES from $matches as the start of the full postcodes contained in est_postcode I am sure that I made this sound more complicated than it is....... any ideas???

the output in this example is LA12, LA13, LA14, LA16 no, its not the first 3 characters, because the outcodes can be LA1 or LA12 - i.e. 3 or 4 characters, so the only constant is to remove the last 3 - leaving 3 or 4. I used explode as it was the only way i could find to use the array in the SELECT statement.

I have an array of the outcodes of UK postcodes - so LA12, LA13 etc..... I also have a table of businesses, one of the columns being est_postcode - which has full UK postcodes - i.e - LA13 9UT etc. so the variable $matches is created from the data stored in $postcode_radius - which works fine. (OUTCODES) I then want to use the array to SELECT matching Postcodes from the businesses - but need to trim the last 3 characters from the est_postcode - i used the code below but nothing happens - i feel that the SUBSTRING part isnt correct - any advice?? $matches = implode(', ', array_unique($postcode_radius)); //echo $matches; // just checking that the variable looks right $sql2 = "SELECT * FROM businesses WHERE SUBSTRING(est_postcode , -3) = '".$matches."'"; $result2 = mysql_query($sql2) or die('query failed: ' . mysql_error()); while ($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)) { echo $row2["est_postcode"], $row2["est_landline"]; }

ah i see that about echoing $row - and that was just to see if anything was there - and i see that was wrong - changed is to var_dump($str) now and i get string(126) "" - what does this mean. I ran the query in phpmyadmin and it returns no results i am thinking its a problem with the 2nd query

I am using a script to take postcodes and give distances between them and list all postcodes within a radius. Where it should list all the postcodes in the radius it provides and empty array - any clues?? the table is 'geodb' relevant fields are postcode place latitude longitude and the code is if(isset($_GET['Search'])) { $postcode = strtoupper($_GET['postcode']); $radius = $_GET['radius']; echo $postcode . '<br>'; echo $radius . '<br>'; $conn = mysql_connect(/*credentials removed*/) or die('db connect error: ' . mysql_error()); mysql_select_db('db379121', $conn) or die('could not select database'); $sqlstring = "SELECT * FROM geodb WHERE postcode = '".$postcode."'"; $result = mysql_query($sqlstring); $row = mysql_fetch_assoc($result); ++++++++++++++++++++++++++++++++++++++++++ var_dump($row); // seeing whqat is in the array - returns one result (array(9) { ["id"]=> string(1) "1" ["countrycode"]=> string(2) "GB" ["postcode"]=> string(4) "AB10" ["place"]=> string( "Aberdeen" ["postaltown"]=> string( "Aberdeen" ["region1"]=> string( "Scotland" ["region2"]=> string(13) "Aberdeen City" ["latitude"]=> string(12) "57.135813090" ["longitude"]=> string(12) "-2.121224030" })// ++++++++++++++++++++++++++++++++++++++++++ $lng = $row["longitude"] / 180 * M_PI; $lat = $row["latitude"] / 180 * M_PI; echo $lat . '<br>'; echo $lng . '<br>'; ++++++++++++++++++++++++++++++++++++++++++ echo to check that there is a value here - i get lat 0.99720805922458 lng -0.037022343495923 ++++++++++++++++++++++++++++++++++++++++++ mysql_free_result($result);} $sqlstring2 = "SELECT DISTINCT geodb.postcode,geodb.place,(6367.41*SQRT(2*(1-cos(RADIANS(geodb.latitude))*cos(".$lat.")*(sin(RADIANS(geodb.longitude))*sin(".$lng.")+cos(RADIANS(geodb.longitude))*cos(".$lng."))-sin(RADIANS(geodb.latitude))* sin(".$lat.")))) AS Distance FROM geodb AS geodb WHERE (6367.41*SQRT(2*(1-cos(RADIANS(geodb.latitude))*cos(".$lat.")*(sin(RADIANS(geodb.longitude))*sin(".$lng.")+cos(RADIANS(geodb.longitude))*cos(".$lng."))-sin(RADIANS(geodb.latitude))*sin(".$lat."))) <= '".$distance."') ORDER BY Distance"; $result = mysql_query($sqlstring2) or die('query failed: ' . mysql_error()); $str = "<table width=\"300\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">"; $str .= "<tr>"; $str .= "<th>postcode</th>"; $str .= "<th>city</th>"; $str .= "<th>distance</th>"; $str .= "</tr>"; while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $str .= "<tr><td>".$row["postcode"]."</td><td>".$row["place"]."</td><td>".round($row['Distance'])."km</td></tr>"; } $str .= "</table>"; ++++++++++++++++++++++++++++++++++++++++++ var_dump($row["place"]); // this produces null ++++++++++++++++++++++++++++++++++++++++++ mysql_free_result($result); mysql_close($conn); echo $str;

SELECT * FROM hotels WHERE (est_town LIKE %1\$s OR est_county LIKE %1\$s OR est_postcode LIKE %1\$s) AND featured = 'N' AND premier = 'N'", GetSQLValueString($colname_standard_listing, "text") Cheers David - I have gone with the above code - had to remove the apostrophes around the %1\$s for it to work but now seems to be working like a dream - will have to test it to death now - thanks very much for your help

$searchterm_standard_listing = "-1"; if (isset($_POST['searchterm'])) { $searchterm_standard_listing = $_POST['searchterm']; } mysql_select_db($database_contractors, $contractors); $query_standard_listing = sprintf("SELECT * FROM hotels WHERE est_town LIKE %s AND featured = 'N' AND premier = 'N'", GetSQLValueString($searchterm_standard_listing, "text")); $standard_listing = mysql_query($query_standard_listing, $contractors) or die(mysql_error()); $row_standard_listing = mysql_fetch_assoc($standard_listing); $totalRows_standard_listing = mysql_num_rows($standard_listing); [quote][/quote] I have this code which dreamweaver "helped" me create - i want it to use the searchterm to return the contents of 2 more columns (est_county and est_postcode) I have tried numerous ways to make this work such as WHERE est_town OR est_county OR est_postcode LIKE ........ but that returns Warning: sprintf() [function.sprintf]: Too few arguments. Any suggestions to make this work?