1

I have a mysql table with 100,000 locations which I would like to apply the nested set model to so that I can easily generate ordered lists. For obvious reasons, this is bit of a large task to do manually, so I was hoping that considering my data is quite well structured it would be something that could be written in SQL (or PHP as a fallback). Here is the table:

Id             place             county           country  
1              Abberly           Worcestershire   England   
2              Abberton          Worcestershire   England
3              Abbey Field       Essex            England
4              Abbey St Bathans  Scottish Border  Scotland
5              Abbeycwmhir       Powys            Wales

My plans are to transfer the contents to a table something like the following:

id     location           _left   _right
1      England    
2      Wales
3      Scotland
4      Essex
5      Powys
6      Scottish Border
7      Worcestershire
8      Abberyly
9      Abberton
10     Abbey Field
11     Abbey St Bathans
12     Abbeycwmhir

Any help is greatly appreciated!

James
  • 656
  • 2
  • 10
  • 24
  • Thats possible, did you try something yourself? – Tredged Jul 09 '13 at 07:17
  • I'm having trouble getting my head around it. I'm not sure if the best route is to set up a new table with just id, name, _left, _right in there as once this is moved to a nested model it will make it easier to select 'Worcestershire' as a the 'name' and then find all of its children with the L/R values? – James Jul 09 '13 at 07:24

1 Answers1

1

The MySQL table contains 100,000 rows? then do something like with a table before it, it will show everything in a tabel sort of what u wanted it to be.

<?php 
$query = "SELECT id, name, country FROM table";
if ($result = mysqli_query($connect, $query)) {
while ($get = mysqli_fetch_assoc($result)) {
echo"<tr><td>" . $get['id'] . "</td>";
echo"<td>" . $get['name'] . "</td></tr>";   
echo"<td>" . $get['country'] . "</td></tr>";                                      
 }
}
?>

Like if it helpd.

Tredged
  • 586
  • 2
  • 17
  • I should have mentioned that it needs to be nested so that I can query another table like so: SELECT * FROM data WHERE locationId IN (2,3) – James Jul 09 '13 at 07:58