Enhancing link functionality on geographical entrance page

I have this page: https://kroweb.dk/gfdev/canvas2/

It is basically showing a clickable map of Denmark, and when clicking a section on the map (a county) the page lists the shires and the parishes located in the clicked county.

As it tis now, the individual parishes in the parish list is linking to a collecting page. This part is working precisely as it should and is not to be changed.

What I would like to add, is to "activate" the shire list too. This list is at this moment a "dead" text list, and I would like to activate it in a way so when clicking a shire in the shire list, the parish list below will display only the parishes within this particular shire and still keeping every other present function af the parish list as the are.  I am actually talking about the posibillity to reduce the parish list so it will be displaying fewer parishes - namely the parishes located in the shire clicked.

This is the logic that makes the lists as they are now, and to which I would like to add the new functionality:

<?php
  error_reporting(E_ALL);
  ini_set('display_errors', 1);

  if ((isset($_POST['myQuery']))) {

require_once('data_connection_canvas.php') ;
    
$amt = trim($_POST['myQuery']);
$sql = "SELECT DISTINCT Sogn, Herred, URL FROM sogne WHERE Amt = '$amt' ORDER BY Sogn ASC";
//var_dump($amt);

$result = $con->query($sql);
$rows = $result->fetch_all(MYSQLI_ASSOC);

// Get the Parishes along with the Links
foreach ($rows as $key => $row) {
    $sogne[$row['Sogn']] = $row['URL'];
}

$herreder = array_unique(array_column($rows, 'Herred'));
?>
<div class="container">

<div class="herredscont">
    <h3>Herreder i <?php echo $amt ?> amt, <?php echo count($herreder) ?> stk.</h3>
</div>

<?php foreach ($herreder as $herred): ?>
    <span class="countHerred"style="font-family:Arial; font-size:14px;background-color:white;;"><?php echo $herred ?>, </span>
<?php endforeach; ?>


<div class="sognecont"style ="background-color:white; text-align: left;">

    <h3>Sogne (anvendt) i  <?php echo $amt ?> amt, <?php echo count($sogne) ?> stk.</h3>
</div>


<?php foreach ($sogne as $sogn => $link): ?>
    <span class="countSogn"style="font-family:Arial; font-size:13px;background-color:white;"><a href="<?php echo $link ?>"target="_blank"> <?php echo $sogn ?></a>, </span>
<?php endforeach; ?>

<?php

    mysqli_close($con);
  }else{  print ("POST is not set");
}

?>
</div>

Open in new window

Peter KromanSenior Proposal SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
Hey Peter,

Should be fairly straight forward, but just need to clarify a few things. In your current page, I'm assuming the first list (Herreder i Bornholm amt, 4 stk.) are the Shires, and the second list (Sogne (anvendt) i Bornholm amt, 28 stk.) are the Parishes.

What you want is that when the image is clicked, you only populate the First List (the Shires) and instead of these being static text, they are links. When you click one of these links, the second list (the Parishes) are populated with links, and when you click on those links the hidden search form is submitted.

So would need several queries to be run:

When clicking on the Map, it runs a simple query to retrieve the Shires from the Database, based on the part of the Map clicked.
When clicking on a Shire link, it runs a simple query to retrieve the Parishes from the Database, based on the Shire you clicked.

Is that undetsanding correct ?
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Hi Chris,

It is almost correct.

What I want is when clicking the map it displays the list of shires (Herreder) and the list of parishes (Sogne) in the county clicked just as it is doing now.

The only difference is that I want the shires to be links too, and when clicking a link in the shire list the sogne list reduces to the parishes within the clicked shire.
0
Chris StanyonWebDevCommented:
OK. First thing to do is to identify the query that you'll need to run. Basically, what query will give you the filtered Parishes when you pass in a Shire. Something like this maybe:

SELECT DISTINCT Sogn, URL FROM sogne WHERE Herred = '$herred' ORDER BY Sogn ASC.

You will also need to turn your Shire list into links, so in your code above, lines 29-31 will need to create a link:

<?php foreach ($herreder as $herred): ?>
    <span class="countHerred"><?php printf("<a href='#'>%s</a>", $herred) ?>, </span>
<?php endforeach; ?>

Open in new window

What we'll be doing is binding the Click event of those links to an AJAX call back to a server-side script. It will pass the $herred back to the server, run your query using that into, build a new list of Parishes and return it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Peter KromanSenior Proposal SpecialistAuthor Commented:
OK - but where in the code do I put in the query?
0
Chris StanyonWebDevCommented:
We haven't got there yet. I just need you to know what the query will be. Once you've identifed the query, it will go in a new script, for example getParishes.php

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
 
$hostname = 'localhost';
$username = 'user';
$password = 'pass';
$database = "db";

$mysqli = new mysqli($hostname, $username, $password, $database);
    
if ($mysqli->connect_error) {
    die(sprintf('Connect Error (%s) %s', $mysqli->connect_errno, $mysqli->connect_error));
}

$mysqli->set_charset("utf8");

if ($stmt = $mysqli->prepare("SELECT DISTINCT Sogn, URL FROM sogne WHERE Herred = ? ORDER BY Sogn ASC")):

    $stmt->bind_param("s", $_POST['shire']);
    $stmt->bind_result($sogn, $link);

    $stmt->execute();

    while ($stmt->fetch()):

        printf("<span class='countSogn'><a href='%s'>%s</a>, </span>" . PHP_EOL, $link, $sogn);

    endwhile;

endif;

Open in new window

Then you'll need the following jQuery in your page (make sure it's in a document ready block!):

$('#herredssogne_list').on('click', '#herredList span a', function(e) {
    e.preventDefault();
    $.ajax({
        url: 'getParishes.php',
        data: { 'shire' : $(this).text() },
        method: 'post'
    })
    .done(function(data) {
        $('#sognList').html(data);
    });
});

Open in new window

You'll notice that we're passing the link's text back to the getParishes,php script as a POST key called 'shire'. Once the AJAX call returns it's data, we're dropping that data into an element with an ID of 'sognList'. Also notice that the click event is bound to all links with an element with an id of 'herredList', so you need to make sure they're being generated in your original page:

<div id="herredList">
<?php foreach ($herreder as $herred): ?>
    <span class="countHerred"><?php printf("<a href='#'>%s</a>", $herred) ?>, </span>
<?php endforeach; ?>
</div>

Open in new window

and
<div id="sognList">
<?php foreach ($sogne as $sogn => $link): ?>
    <span class="countSogn"><a href="<?php echo $link ?>"target="_blank"> <?php echo $sogn ?></a>, </span>
<?php endforeach; ?>
</div>

Open in new window

0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Yes - the query is right.

Now working on this page: https://kroweb.dk/gfdev/canvas3/

I have made new file - getParishes.php looking like this:
<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
 
require_once('data_connection_canvas.php') ;

$mysqli->set_charset("utf8");

if ($stmt = $mysqli->prepare("SELECT DISTINCT Sogn, URL FROM sogne WHERE Herred = ? ORDER BY Sogn ASC")):

    $stmt->bind_param("s", $_POST['shire']);
    $stmt->bind_result($sogn, $link);

    $stmt->execute();

    while ($stmt->fetch()):

        printf("<span class='countSogn'><a href='%s'>%s</a>, </span>" . PHP_EOL, $link, $sogn);

    endwhile;

endif;

Open in new window


I have mad a jquery sicript in the index file looking like this:
    <script>
    $(document).ready(function(){
    $('#herredssogne_list').on('click', '#herredList span a', function(e) {
    e.preventDefault();
    $.ajax({
        url: 'getParishes.php',
        data: { 'shire' : $(this).text() },
        method: 'post'
    })
    .done(function(data) {
        $('#sognList').html(data);
    });
});
});
    </script>

Open in new window


and I have made to divs in the index file as well looking like this:

<div id="herredList">
<?php foreach ($herreder as $herred): ?>
    <span class="countHerred"><?php printf("<a href='#'>%s</a>", $herred) ?>, </span>
<?php endforeach; ?>
</div>

<div id="sognList">
<?php foreach ($sogne as $sogn => $link): ?>
    <span class="countSogn"><a href="<?php echo $link ?>"target="_blank"> <?php echo $sogn ?></a>, </span>
<?php endforeach; ?>
</div>

Open in new window


There are still no links, and the functionality is the same. What am I missing?
0
Chris StanyonWebDevCommented:
When you click on the map, an AJAX call is made to ajaxSognHerredHtml.php which generates the HTML to show the 2 lists. That page is still returning the old HTML - it looks like you haven't made any changes to it which is why you're not getting any links.

Also, in the getParishes.php file, you need to change the name of the connection to whatever you call it. I called it $mysqli in my code example, but it looks like you're probably calling it $con, so make sure that's changed as well.
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Yeahhhh I have got it working :)

Just had to move the two divs to another file and replace two other divs.

Just a few things.

I would like when clicking a Herred (shire) it is only the name of that Herred that remains in the headline over the Herreds list.
Now all the shires (Herreder) is still  displayed there.

And accordingly I would like when clicking a Herred the the headline over the Sogne list displays which Herred it is and is counting the Sogne in that Herred.
Now the name of the Amt (county) and the total number of Sogne in the County is still displayed when clicking a Herred.
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
The total code that is generating the lists and the texts look like this right now. (in addition with the getParishes of course ...)

<?php
  error_reporting(E_ALL);
  ini_set('display_errors', 1);

  if ((isset($_POST['myQuery']))) {

require_once('data_connection_canvas.php') ;
    
$amt = trim($_POST['myQuery']);
$sql = "SELECT DISTINCT Sogn, Herred, URL FROM sogne WHERE Amt = '$amt' ORDER BY Sogn ASC";
//var_dump($amt);

$result = $con->query($sql);
$rows = $result->fetch_all(MYSQLI_ASSOC);

// Get the Parishes along with the Links
foreach ($rows as $key => $row) {
    $sogne[$row['Sogn']] = $row['URL'];
}

$herreder = array_unique(array_column($rows, 'Herred'));
?>
<div class="container">

<div class="herredscont">
    <h3>Herreder i <?php echo $amt ?> amt, <?php echo count($herreder) ?> stk.</h3>
</div>


<div id="herredList">
<?php foreach ($herreder as $herred): ?>
    <span class="countHerred"><?php printf("<a href='#'>%s</a>", $herred) ?>, </span>
<?php endforeach; ?>
</div>


<div class="sognecont"style ="background-color:white; text-align: left;">

    <h3>Sogne (anvendt) i  <?php echo $amt ?> amt, <?php echo count($sogne) ?> stk.</h3>



<div id="sognList">
<?php foreach ($sogne as $sogn => $link): ?>
    <span class="countSogn"><a href="<?php echo $link ?>"target="_blank"> <?php echo $sogn ?></a>, </span>
<?php endforeach; ?>
</div>
</div>

<?php

    mysqli_close($con);
  }else{  print ("POST is not set");
}

?>
</div>

Open in new window

0
Chris StanyonWebDevCommented:
Not sure what you mean by the first part of your comment, but the second part should be fairly straight forward.

Currently you have this in your code:

<div class="sognecont">
    <h3>Sogne (anvendt) i  <?php echo $amt ?> amt, <?php echo count($sogne) ?> stk.</h3>
    <div id="sognList">
        <?php foreach ($sogne as $sogn => $link): ?>
            <span class="countSogn"><a href="<?php echo $link ?>"target="_blank"> <?php echo $sogn ?></a>, </span>
        <?php endforeach; ?>
    </div>
</div>

Open in new window

Make sure all of that is wrapped in the sognList div:

<div id="sognList">
    <div class="sognecont">
        <h3>Sogne (anvendt) i  <?php echo $amt ?> amt, <?php echo count($sogne) ?> stk.</h3>
    </div>
    <?php foreach ($sogne as $sogn => $link): ?>
        <span class="countSogn"><a href="<?php echo $link ?>"target="_blank"> <?php echo $sogn ?></a>, </span>
    <?php endforeach; ?>
</div>

Open in new window

Then you will need to change your getParishes.php code to replace that whole code block, and not just the list:

if ($stmt = $mysqli->prepare("SELECT DISTINCT Sogn, URL FROM sogne WHERE Herred = ? ORDER BY Sogn ASC")):

    $stmt->bind_param("s", $_POST['shire']);
    $stmt->execute();
 
    $result = $stmt->get_result();

    echo "<div class='sognecont'>";
    printf("    <h3>Sogne (anvendt) i %s amt, %s stk.</h3>", $_POST['shire'], $result->num_rows);
    echo "</div>";

    while ($data = $result->fetch_assoc()):
        printf("<span class='countSogn'><a href='%s'>%s</a>, </span>" . PHP_EOL, $data['ref'], $data['firstname']);
    endwhile;

endif;

Open in new window

0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Something is wrong:

what does this mean:
$data['ref'], $data['firstname'])
in this function in getParishes
while ($data = $result->fetch_assoc()):
        printf("<span class='countSogn'><a href='%s'>%s</a>, </span>" . PHP_EOL, $data['ref'], $data['firstname']);
    endwhile;

Open in new window

0
Chris StanyonWebDevCommented:
Haha - sorry. I was working on something else at the same time - the keys of the $data array should match your own fields from the database:

$data['URL']
$data['Sogn']
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Peter KromanSenior Proposal SpecialistAuthor Commented:
Yeahh - that's much nicer :)

Everything is in place now - just forget about my first issue - it is not relevant.

Thanks a lot.
0
Peter KromanSenior Proposal SpecialistAuthor Commented:
Thanks again Chris for great help getting the last things in place.
Great job and great help.
Thanks.
0
Chris StanyonWebDevCommented:
No worries Peter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.