mackin

sorted changed $updCount = mysql_affected_rows($update_postcodes); to $updCount = mysql_affected_rows(); and it works just fine Many thanks

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

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;

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

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

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?

Hi, I have a table that I accidentally added 100's of incorrect phone numbers - its set to Integer - the number had a space in it - so only half the number inserted. Instead of starting over - its time I learned how to update it properly. The affected ID's are 11084789 - 11085411 - and the column is called 'est_landline' at the moment those affected have the first 3 digits of the phone number - I have the correct number now in a spreadsheet - and want to know how I can UPDATE the affected ID's without damaging the rest of the Table. Any advice people?

hi, i am using a database to populate a webpage with text. How do I make a function to add line breaks to the text? an example page I want to use it on it http://www.stayneartheairport.com/bandb.php?id=11084507&airport=manchester You can see the two text areas of description and facilities have no paragraphs or other formatting. I want the code to scan the text - and if there is say <br> - it will remove it an replace it with a line break.

thx dan

thank u

is this code correct? I never know where the \ and ' go in relation to the % $sqlstring = "SELECT * FROM town_coords WHERE town_name ='\%'.$airport.'%\'";

HI - I am dynamically populating a drop down form list from the table 'hotels', and the 'est_town' column. It is supposed to only list each town once, but several towns appear multiple times. Am I using the DISTINCT command correctly and if so, what can i do to stop multiple appearances of towns? $query_hotels_select = "SELECT DISTINCT est_town FROM hotels ORDER BY est_town ASC"; $hotels_select = mysql_query($query_hotels_select, $contractors) or die(mysql_error()); $row_hotels_select = mysql_fetch_assoc($hotels_select); $totalRows_hotels_select = mysql_num_rows($hotels_select); FORM BIT <select name="digs" id="digs"> <?php do { ?> <option value="<?php echo $row_hotels_select['est_town']?>"<?php if (!(strcmp($row_hotels_select['est_town'], $row_hotels_select['est_town']))) {echo "selected=\"selected\"";} ?>><?php echo ucwords(strtolower($row_hotels_select['est_town']))?></option> <?php } while ($row_hotels_select = mysql_fetch_assoc($hotels_select)); $rows = mysql_num_rows($hotels_select); if($rows > 0) { mysql_data_seek($hotels_select, 0); $row_hotels_select = mysql_fetch_assoc($hotels_select); } ?> </select>

Hi, I am trying to use this code to take all the unique town names from one table and populate a new table with those towns. this code doesnt add town names to the new table and deletes any that are there with blank space - any clues? mysql_select_db($database_contractors, $contractors); $query_Recordset3 = "SELECT DISTINCT est_town FROM hotels"; $res3 = mysql_query($query_Recordset3, $contractors) or die(mysql_error()); $num_rows = mysql_num_rows($res3); echo $num_rows . "<br>"; while( $row = mysql_fetch_assoc($res3) ) { $town = $row['est_town']; echo $town . "<br />" ; mysql_query('UPDATE town_coords SET town_name = '.$town.''); }

Hi, I am using the Google map API to display some businesses on a map - the sequence is map and below it a list of the establishments from a DB. Before I put the google map in, the list of establishments in the while table loop worked correctly. Now the map is there - that works, but the looping table doesnt display - I feel that it may be something to do with <?php while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) { ?> new google.maps.LatLng(<? echo $row['latitude'] ?>, <? echo $row['longitude']?>), <?php ; } ?> and the second fetch array - while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) Any Advice? <?php // *******************************below is bit that selects hotels based on outcode of postcode and prints table with links to details pages $sql2 = "SELECT * FROM hotels WHERE (SUBSTRING_INDEX(est_postcode, ' ', 1)) IN ('$matches') "; $result2 = mysql_query($sql2) or die('query failed: ' . mysql_error()); $num_rows = mysql_num_rows($result2); echo "<p> Your search has found ".$num_rows . " establishment(s) </p>"; ?> <script type="text/javascript"> var berlin = new google.maps.LatLng(<? echo $lat2 ?>, <? echo $lng2 ?>); var neighborhoods = [ <?php while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) { ?> new google.maps.LatLng(<? echo $row['latitude'] ?>, <? echo $row['longitude']?>), <?php ; } ?> ]; var markers = []; var iterator = 0; var map; function initialize() { var mapOptions = { zoom: 12, mapTypeId: google.maps.MapTypeId.ROADMAP, center: berlin }; map = new google.maps.Map(document.getElementById("map_canvas"), mapOptions); } function drop() { for (var i = 0; i < neighborhoods.length; i++) { setTimeout(function() { addMarker(); }, i * 200); } } function addMarker() { markers.push(new google.maps.Marker({ position: neighborhoods[iterator], map: map, title:"Hello World!", draggable: false, animation: google.maps.Animation.DROP })); iterator++; } </script> <div id="map_canvas" style="width: 614px; height: 400px;">map div</div> <button id="drop" onclick="drop()">Drop Markers</button> <?php echo "<table id ='results' border=0>"; while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) { echo "<tr>"; echo "<td><a class='blueboldtitle' href='bandb.php?id=". $row["id"] . "'>" . ucwords(strtolower($row["est_name"])) ."</a></td><td>" . $row["est_postcode"] ."</td><td> ". $row["est_areacode"]." ". $row["est_landline"] ."</td><td><a href='bandb.php?id=". $row["id"] . "'>[More Info] </a></td>"; echo "</tr>"; } echo "</table>"; ?>

Thx for the reply - but I dont need to go that far - all i really need to do is add latitude and longitude information to my postcodes - using google geocode to create them. $pc1 = 'http://maps.google.com/maps/geo?q='.$postcode1.',+UK&output=csv&sensor=false&key=ABQIAAAAcclaxnepdvvxx5D2PAnHtRSLcuoGw6G6HnB4VN4WqoMz7tmtKhTHGV82iKKTXvAg7YFpCFA6ptRc9g'; $data1 = file_get_contents($pc1); $result1 = explode(",", $data1); $custlat1 = $result1[2]; $custlong1 = $result1[3]; I used this code to do single entries - but i want to do 1100 postcodes in a similar fashion

I am using Googlemaps to show a group of businesses around a central point. I can dynamically generate the map based on the location. I have a DB of postcodes that I need to geocode into LAT and LONG - and store them in the same field as postcode - so I can use lat&long to plot points on the map. Does anyone have a script for this or know where I should begin? Cheers

Brilliant Keith - just had to change = to LIKE but worked perfectly - thanks for your patience. Regards Stu (thx to all)

gives me this keith SELECT * FROM hotels WHERE est_name = %'abbey'%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 '%'abbey'%' at line 1

$colname_hotel_name_RS = "-1"; if (isset($_POST['name'])) { $colname_hotel_name_RS = $_POST['name']; } mysql_select_db($database_contractors, $contractors); $query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name LIKE '\%%s\%'", GetSQLValueString($colname_hotel_name_RS, "text")); print_r($query_hotel_name_RS); exit(); $hotel_name_RS = mysql_query($query_hotel_name_RS, $contractors) or die(mysql_error()); $row_hotel_name_RS = mysql_fetch_assoc($hotel_name_RS); $totalRows_hotel_name_RS = mysql_num_rows($hotel_name_RS); That gives me SELECT * FROM hotels WHERE est_name LIKE '\%s\ - with same abbey search term

with the original code I get SELECT * FROM hotels WHERE est_name = 'abbey' with the amended codes the query wont display because of the syntax errors

Hi Keith, when I use your code I get this error 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 '%'abbey'%' at line 1 That is with a search for abbey muddy - same search with code verbatim gives 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 ''\%s\' at line 1 removing the escaping gives 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 ''%s' at line 1 Cheers people

Is it possible to change the code below so the search uses LIKE - so as not to be so exact with the results - I assume I need to put est_name LIKE.... but where do the % % go? if I put them around "text" the search doesnt work. Thx in Advance Stu $colname_hotel_name_RS = "-1"; if (isset($_POST['name'])) { $colname_hotel_name_RS = $_POST['name']; } mysql_select_db($database_contractors, $contractors); $query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name = %s", GetSQLValueString($colname_hotel_name_RS, "text")); $hotel_name_RS = mysql_query($query_hotel_name_RS, $contractors) or die(mysql_error()); $row_hotel_name_RS = mysql_fetch_assoc($hotel_name_RS); $totalRows_hotel_name_RS = mysql_num_rows($hotel_name_RS);

Thx people - changed code to this <?php $rooot = $_SERVER['DOCUMENT_ROOT']; $frontimage = "/hotelimages/". $img_dir . "/" . $front . $ext; if (!file_exists($rooot . $frontimage)) { ?> <img src="/image.php?width=245&height=245&cropratio=1:1&image=/<?php echo $img_folder.$image ;?>" alt="<?php echo ucwords($row_rs_properties['est_name']);?>" class="fltrt" border="1"/> <?php } else { ?> <img src="/image.php?width=245&height=245&cropratio=1:1&image=<?php echo "/hotelimages/". $img_dir . "/" . $front . $ext; ?>" alt="<?php echo ucwords($row_rs_properties['est_name']);?>" class="fltrt" border="1"/> <?php } ?> seems the document root was at issue thx PFMaBiSmAd - also removed the semi colons

I have directories containing images. If an Image called front.jpg exists then I want that to display - if it doesn't then I want it to display any random image from the directory. All the bits works individually. but for some reason, even when front.jpg exists - it still displays a random image from the else statement. Can you see an error? $frontimage = "/hotelimages/". $img_dir . "/" . $front . $ext; ?> <div id="topframe"> <?php if (file_exists($frontimage)) {?><img src="/image.php?width=245&height=245&cropratio=1:1&image=/<?php echo $img_folder.$image ;?>" alt="<?php echo ucwords($row_rs_properties['est_name']);?>" class="fltrt" border="1"/><? ;} else { ?> <img src="/image.php?width=245&height=245&cropratio=1:1&image=<?php echo "/hotelimages/". $img_dir . "/" . $front . $ext; ?>" alt="<?php echo ucwords($row_rs_properties['est_name']);?>" class="fltrt" border="1"/> <?php ;} ?> </div>

Thank you both - I went with mikosiko's version and it worked perfectly