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