scotchegg78

ok ok, I have noticed the mysql admin run has two regionID columns, one from my region table and one from my office table. So its allowing ambiguity?! i guess i have to address them via row direct not assoc.

Hi guys. My code works fine locally just not on the live server. When i run the sql qry on the live server in mysql admin it also works fine!? Anway its not getting the last row regionID, but its getting everything else? row regionName etc.. $sql = "SELECT * FROM cr_region LEFT JOIN cr_office USING (regionID) ORDER BY cr_region.regionID"; $result = $dblink->query($sql); $lastid = ''; while($row = $result->fetch_assoc()){ if($lastid != $row['regionID']){ $array[$row['regionID']] = array('regionID'=>$row['regionID'],'regionName'=>$row['regionName'],'address1'=>$row['regionaddress1'],'address2'=>$row['regionaddress2'],'address3'=>$row['regionaddress3'],'address4'=>$row['regionaddress4'],'town'=>$row['regiontown'],'county'=>$row['regioncounty'],'country'=>$row['regioncountry'],'postcode'=>$row['regionpostcode'],'webaddress'=>$row['webaddress'],'regionEmail'=>$row['regionEmail'],'regionTelephone'=>$row['regionTelephone'],'message'=>$row['message']); $lastid = $row['regionID']; } echo $row['regionID']; if(!is_null($row['officeID'])){ $array[$row['regionID']]['offices'][] = array('address1'=>$row['address1'],'address2'=>$row['address2'],'address3'=>$row['address3'],'address4'=>$row['address4'],'town'=>$row['town'],'county'=>$row['county'],'country'=>$row['country'],'postcode'=>$row['postcode']); } } Whats going on!? only thing is we are on mysql 5 local and mysql 4 live? So again, It gets everything else except for the last region ID from the last row? but the qry in admin seems fine, when I also print the array its all there except the last $row[regionID]? Any ideas?

I would add i think i have the regular expression ..

guys I have a form, and inparticular an input box .. <p class='label_field_pair'> <label for='postcode'>Postcode</label> <input type='text' name='postcode' id='postcode' value='".$carerregistrant->postcode."'/> </p> now on submit of this form a 2nd page come and based on the users post code a radio list appears and the user selects their closest postcode, this is all done already. But i need it to instead of apperaring when a user submits the form a live validation of postcode and when valid the radio options drop into view. I understand i may need to use JS and regualr expressions to validate the postcode, and then somehow drop down a new form option into view? Anyone done anything like this before? thanks

thanks nightsly i am going to take a lok at this now, only issue i still see is that i need them not to hide on mouse / hover out. instead when a new one is hovered over i replaces the previous one. and the previous is hiden

HI guys. I have 3 tables, region(regionID,name) event(eventID,desc) rating(ratingID,desc) and one data table... (regionID,eventID,ratingID) It is simply used to assign ratings of events to regions, there are other fields but not relevent here. Whats the best way to create the input to db data table? I am allowing one region to be added a time, so I assume the form needs to loop though all events and then also pull from the ratings table for the select options. Is it a good idea to name the rating select boxes the eventID ? so in my mysql statement i know what the region is from the form(also drop down), but more importantly I will then have a select box with an event ID and value. Is this the best way to do it? Only issue is the mysql code then needs to know how many events there are and their ids to also then search for the form posts names of all the rating selects ? with me?! cheers for any guidance

Sadly no! the r"some number" is actaully a DB ID number, and so it could go r1,r2,r3,r5,r7,r90 I guess.

Hi guys I have 3 columns inside a main container div. They all have a border, so if they are not all the same height then i have 3 varying looking heights. What i would like is for the 3 columns to all drop in height to the longest one? eg <div id="content_container"> <div id="column_left">some small content in 1 column</div> <div id="column_center"> Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Nam tempus, mi et sagittis tincidunt, massa urna fermentum est, id imperdiet erat turpis at neque. Nullam sit amet magna. Maecenas arcu leo, tempor at, condimentum et, placerat in, urna. Fusce sed nisl id est lobortis porta. Aenean commodo lectus eu augue </div> <div id="column_right">Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Nam tempus, mi et sagittis tincidunt </div> </div> ignoring width colour etc what do i need to ensure the 3 columns all stay the same height as the tallest? thanks EDIT if it helps this is the code so far... #content_container { float:left; display:inline; /* god damn IE! */ } #column_center { /*263 total width */ width:230px; float:left; margin-right:5px; border:1px solid #CCCCCC; text-align:left; padding:15px; } * html #column_center {height:443px;} #column_left { /*263 total width */ width:228px; float:left; margin-right:5px; border:1px solid #CCCCCC; text-align:left; padding:15px; } * html #column_left {height:443px;} #column_right { /*258 total width */ width:228px; float:left; margin-right:0px; border:1px solid #CCCCCC; text-align:left; padding:15px; }

Hi Nightsly sorry I am not sure what you mean by 1:1 correlation. Do you mean are my are tags named r1 r2 etc in id? my code is as you see it above, nothing else?

Hello guys I have tried to modify this original source code ... <script language="javascript"> <!-- var state = 'none'; function showhide(layer_ref) { if (state == 'block') { state = 'none'; } else { state = 'block'; } if (document.all) { //IS IE 4 or 5 (or 6 beta) eval( "document.all." + layer_ref + ".style.display = state"); } if (document.layers) { //IS NETSCAPE 4 or below document.layers[layer_ref].display = state; } if (document.getElementById &&!document.all) { hza = document.getElementById(layer_ref); hza.style.display = state; } } //--> </script> to display only one at a time,and when another is triggered it turns off the rest.... <script language="javascript"> <!-- var state = 'none'; function show(layer_ref) { for(i=0;i<20;i++){ if (document.all) { //IS IE 4 or 5 (or 6 beta) eval( "document.all.r" + i + ".style.display = none"); } if (document.layers) { //IS NETSCAPE 4 or below document.layers["r"+i].display = none; } if (document.getElementById &&!document.all) { hza = document.getElementById("r"+i); hza.style.display = none; } } if (document.all) { //IS IE 4 or 5 (or 6 beta) eval( "document.all." + layer_ref + ".style.display = block"); } if (document.layers) { //IS NETSCAPE 4 or below document.layers[layer_ref].display = block; } if (document.getElementById &&!document.all) { hza = document.getElementById(layer_ref); hza.style.display = block; } } //--> </script> the area maps for hover trigger are like .. <area shape ="circle" coords ="157,139,8" onMouseOver="show('r11')" href ="#nogo" target ="_blank" alt="Scotland" /> and divs .. <div id="r11" style="display: none;">This is the Scotland content</div> but for some reason the orginal works fine, but not the modified, can you see where I have gone wrong? thanks for any eagle eyes!

Ok then i think i nearly have a plan!! One last possible issue, I want to show the areas on the image,HTML area tags are normally hidden, how can i add an image to the area? i have tried something like <style type="text/css"> .spot{ background-image:url(spot.gif); </style> } <area ... class="spot"> But this does not work

but can i use JS on a HTML map to create my rollover effect and pop up my DB content?! mouseover etc?

I need to load my coords from a db, conver the Lats and Longs etc. Hovre is i use a CSS map i can not do this can i? as they are in the CSS file as style properties? So i need to use a HTML image map right? But can i use JS mouse over with this?! oh so stressed!

the css maps i have seen so far could be a bit of a stress. i have 18 locations to plot on my map. Doing these my hand working out coords and then the coords to display a block of text in teh same place could be a bit of a stress! JS may be the answer after all, and I deally if i could find a way to place pins on a map based on thier Lat and Long it would be great!

hi thanks alot for taking the time to reply. I can not use flash in this instance. So its JS or CSS image map. JS is still alien to me! so i will look into CSS image maps and see how i get on thanks

Done. Can I ask a question Ben, What motivates you guys to help people out on here? You must i of saved so many people in the past, its amazing when you think about it.

Many Many thanks ginger! Seesm to work out right. I susepct i could of done it alot simplier using php to sort the data from simplier queries, but this should be great. Now just got to figure out how to handle the data to display on rollovers of a map region! lol

HI guys. I need to create a contact us page where the user rolls over a map of the british isles and when they hover over one of many spots on the map where their is an office marked the details of the office pop up somewhere on screen. In dreamweaver i can create shap maps on jpegs and then have to play with JS to get it working i guess. Just wondering if this is the best way or if there is anything easier to use? thanks

thanks again ginger. I am playing with it but get this... Array ( [r.regionid] => Array ( [offices] => Array ( [] => Array ( [o.address1] => [o.address2] => ) ) ) ) the SQL seems to work and returns the 58 offce rows, but then adds the region details to every row, is this right? Had to modify your code a little, to this.. dont think i can see an error.. $sql = "SELECT o.*,r.* FROM cr_office as o,cr_region as r WHERE o.regionID=r.regionID ORDER BY o.regionID"; $result = $dblink->query($sql); $lastid = ''; while($row = $result->fetch_assoc()){ if($lastid != $row['r.regionID']){ $array['r.regionid'] = array('r.address1'=>$row['r.address1'],'r.address2'=>$row['r.address2']); //fill in with all other fields from region table (e.g. r.*); $lastid = $row['r.regionID']; } $array['r.regionid']['offices'][$row['o.officeID']] = array('o.address1'=>$row['o.address1'],'o.address2'=>$row['o.address2']); //fill in with all other fields from office table (e.g. o.*); } echo '<pre>'.print_r($array,1).'</pre>';

HI thanks for the quick reply ginger. That does get the offices sorted via regions nicely thanks. Just wondering if if there is a nice way to add the regions addresses to this array aswell? So its arranged under each regionID the region addresses and then the array of offices? You with me? thanks again

Hi guys I believe I need to sort into a multi array offices and regions addresses, Regions are offices(sort of a HQ), and manage the area offices. e.g. tables cr_region(regionID,address1,address2,town,...) cr_office(officeID,regionID,address1,address2,town...) simple stuff really! Anyway whats the best way to gt this into an array so that i can later set things up so when someone hovers over a map the relevant region office details and its sub office details pop up. ? I will tackle the magin bit with JS, but a littel unsure of the array side of things for the data arrangement? any help or advice on creating my 2/3D php array of regions and offices? thanks for any input

Hi I have the qry working now, it was a few silly things missing like an as for the table c and o. I am not sure its doing it right, but i have done it all another way now using 2 qrys and handling it with php If anyone else can benfit from this the way I have done it is ... (full php function included..) function get_closest_postcode(){ $db = new CRDatabase(); $link = $db->connect(); $query = 'SELECT `Lat`, `Long` FROM `ukpostcodes` WHERE `PostCode`="'.$this->trimmedpostcode[0].'";'; $result = $link->query($query); $num_results = $result->num_rows; if($num_results > 0) { $first = $result->fetch_row(); $temp3officesql = "SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(".$first[0].")) * SIN(RADIANS(ukpostcodes.Lat)) + COS(RADIANS(".$first[0]."))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((".$first[1].")-ukpostcodes.Long)))) as distance, cr_region.regionEmailAdmin,cr_office.address1,cr_office.address2,cr_office.address3,cr_office.town,cr_office.postcode FROM ukpostcodes,cr_region, cr_office WHERE ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_office.postcode,' ',1)) AND cr_region.regionID = cr_office.regionID ORDER BY distance ASC LIMIT 3"; $temp3regionsql = "SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(".$first[0].")) * SIN(RADIANS(ukpostcodes.Lat)) + COS(RADIANS(".$first[0]."))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((".$first[1].")-ukpostcodes.Long)))) as distance, cr_region.regionEmailAdmin,cr_region.regionaddress1 as address1,cr_region.regionaddress2 as address2,cr_region.regionaddress3 as address3,cr_region.regiontown as town,cr_region.regionpostcode as postcode FROM ukpostcodes,cr_region WHERE ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_region.regionpostcode,' ',1))ORDER BY distance ASC LIMIT 3"; $result2 = $link->query($temp3officesql)or die("Error : Failed to Get 3 offices"); $result = $link->query($temp3regionsql)or die("Error : Failed to Get 3 regions"); $count = 0; while($row = $result->fetch_row()) { $count ++; $closestoffices[] = array('distance'=>$row[0],'email'=>$row[1],'address1'=>$row[2],'address2'=>$row[3],'address3'=>$row[4],'town'=>$row[5],'postcode'=>$row[6]); } while($row = $result2->fetch_row()) { $count ++; $closestoffices[] = array('distance'=>$row[0],'email'=>$row[1],'address1'=>$row[2],'address2'=>$row[3],'address3'=>$row[4],'town'=>$row[5],'postcode'=>$row[6]); } foreach ($closestoffices as $key => $row) { $distance[$key] = $row['distance']; } array_multisort($distance, SORT_ASC, $closestoffices); } $this->closestoffices = $closestoffices; // exit(); } Thanks for your help fenway.

I dont, the statement fails and i can not see the error

Also if it helps.. these are the two seperate qrys i want to combine to one! $temp3officesql = "SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(".$first[0].")) * SIN(RADIANS(ukpostcodes.Lat)) + COS(RADIANS(".$first[0]."))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((".$first[1].")-ukpostcodes.Long)))) as distance, cr_region.regionEmailAdmin,cr_office.address1,cr_office.address2,cr_office.address3,cr_office.town,cr_office.postcode FROM ukpostcodes,cr_region, cr_office WHERE ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_office.postcode,' ',1)) AND cr_region.regionID = cr_office.regionID ORDER BY distance ASC LIMIT 3"; $temp3regionsql = "SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(".$first[0].")) * SIN(RADIANS(ukpostcodes.Lat)) + COS(RADIANS(".$first[0]."))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((".$first[1].")-ukpostcodes.Long)))) as distance, cr_region.regionEmailAdmin,cr_region.regionaddress1 as address1,cr_region.regionaddress2 as address2,cr_region.regionaddress3 as address3,cr_region.regiontown as town,cr_region.regionpostcode as postcode FROM ukpostcodes,cr_region WHERE ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_region.regionpostcode,' ',1))ORDER BY distance ASC LIMIT 3";

Sorry I do not know how best to style it? Is this any better? SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(52.4780)) * SIN(RADIANS(ukpostcodes.Lat)) + COS(RADIANS(52.4780))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((1.7410)-ukpostcodes.Long)))) as distance, cr_region.regionEmailAdmin,a.address1,a.address2,a.address3,a.town,a.postcode FROM ((SELECT c.regionID as regionID,c.regionaddress1 as address1,c.regionaddress2 as address2,c.regionaddress3 as address3,c.regiontown as town,c.regionpostcode as postcode FROM fcasvn.cr_region c) union (SELECT o.regionID as regionID,o.address1 as address1,o.address2 as address2,o.address3 as address3,o.town as town,o.postcode as postcode FROM fcasvn.cr_office o); )as a, ukpostcodes,cr_region WHERE ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(a.postcode,' ',1)) AND cr_region.regionID = a.regionID ORDER BY distance ASC LIMIT 3"; If i can not figure out this union join i suspect i may have to just run two seperate querys for offices and regions and then compare them in php to get my top 3. thanks for any input or time