Collapsed rows in a table is not displayed correctly

Hi,

http://kroweb.dk/gfdev/canvas/sognefakta_k.php

On this page I am planning to gather data from different sources based on activating a link holding the parish name (right now the activating source is not a link but a search field). Actually I am making three of these pages showing data from three different types of parishes, but for now I will concentrate on this page (and populate lager :) .

 I will, at a later stage, post a question regarding the general function of this, but before I get to that I need to get a detailed problem solved first.

The problem is about the table in the middle of the page (the second table). Try to type in "Haderslev" in the searchfield and hit Return. Then the first table is showing churchbooks for Haderslev parish and the second table is showing censuses for Haderslev parish.

The problem here is actually a problem we have had up in this forum before, but I need to find a way to solve it, so I try again, and hope .... :)

Problem is, that the second table shows a collapsible table which is needed because many censuses often are made by street in the cities, which makes the total listing very long, and in that collapsable table the collapsiple rows are not always shown correctly. E. g in this example "Haderslev" parish you will notice, if you look a little down the table, that the the year 1921 is represented many times down through the list starting with "Haderslev Haderslev Præstegade". All these entries down to "Haderslev Haderslev Østergade" are marked as collapsible rows in the database, but are displayed outside the parent that they belong to. If you look further down the table you will se a parent row for "Haderslev Haderslev 1921". That row holds the rest of the streets in Haderslev up to "Præstegade" and that is collapsing in and out nicely.

My question is if there is any way that we can fix this so the collapsible rows stick with their parent rows ( I can guarantee that they are all marked and placed correctly in the database :) ).

The code in question is this, and the second table begin at line 162

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


?>


<!DOCTYPE html>
<html lang="da">
<head>
    <!-- Check for title--> 
    <title>Sognefakta</title>

    <meta charset="utf-8" lang="da">





    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
      <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    <link rel="stylesheet" href="style_sognefakta.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>


 

  <script type="text/javascript">
    $(function(){
        $('#FtKTable').on('click', '.toggle', function() {
            $(this).parents('tr').nextAll('tr.child').toggle();
        });
    });
    </script>



</head>


<body>
   <!-- Page content start-->        
   <div class="w3-row" style="overflow: auto">





    <!-- Main page start 4 --> 

    <div class="w3-row" >

            <form action=""  form title=""  style="padding-top:10px; padding-bottom:10px;" method="POST" >


             <input type="search" placeholder= "Søg " style="width: 300px; height: 35px; border: 1px solid #666666;border-radius:2px;color:#666666; color: #9F5584; font-weight: ligther; font-size: 13px; padding-left:10px;" name="soger" id="soger"  />

             <!--<input type="submit"> -->

         </form>  
           <br>

        <!-- Column1 start-->         
        <div class="w3-col w3-mobile" style="width:28%; margin-top: 30px; margin-left: 10px; margin-bottom:10px; padding-bottom: 10px; text-align: left;"> 
            <p>Kirkebøger</p>


            <?php

            function cityVariations($city) {
                $substitute = array(
                    "æ" => "ae",
                    "ø" => "oe",
                    "å" => "aa",
                    "ae" =>"æ",
                    "oe" =>"ø",
                    "aa" => "å",
                    );

            $flatCity = mb_convert_case($city, MB_CASE_LOWER);

            $cities[] = $flatCity;
            $cities[] = strtr($flatCity, $substitute); 

            return $cities;
    }


            $con=mysqli_connect("db credentials") or die("Error connecting to database: ".mysql_error());

            mysqli_select_db($con,"genealogiskforum_dk_db9");
            mysqli_set_charset($con,"utf8");
            

            $soger = trim($_POST['soger']);


                // Get the city variations
            $cities = cityVariations($soger);

                // Prepare the cities for the LIKE clause by wrapping each in %...%
            array_walk($cities, function (&$city) { $city = $city.'%'; });

            ?>


            <?php

            $raw_results = mysqli_query($con, "SELECT * FROM kirkebogssogne WHERE (Sogn = '$soger' OR (Sogn LIKE '$cities[0]' OR Sogn LIKE '$cities[1]' ))");
    
            //var_dump($raw_results);

            ?>

            <?php if ($raw_results->num_rows): ?>

            <table id="KbTable" class="table">


                <tr>
                    <th width="35%"> Sogn </th> <th width="15%">Amt</th> <th width="35%">Kirkebog</th> 
                </tr>

                <?php while ($row = $raw_results->fetch_object()): ?>


                <!-- Links for the individual rows -->    
                <?php
                $link2 = sprintf("<a href='%s' target='_blank'>%s</a>", $row->URL, $row->Kirkebog); 
                ?>

                <tr>
                    <td><?php echo $row->Sogn ?></td>      
                    <td><?php echo $row->Amt ?></td>        
                    <!-- Kirkebog, link -->
                    <td><?php echo $link2 ?></td>

                </tr>


            <?php endwhile; ?>    
        </table>

    <?php else: ?>

    <p>Der er ingen resultater for denne søgning</p>

<?php endif; ?>

<?php mysqli_close($con);?>                    
</div>

<!-- Column1, end-->


<!-- Columns2, start--> 
<div class="w3-col w3-mobile" style="width:28%; margin-top: 30px; margin-left: 45px; padding-left: 00px; padding-right: 0px; padding-bottom: 10px;  text-align: left; "> 
    <p>Folketællinger </p> 

   


 <?php                    
    // Connect to the database is we have a POST
    $mysqli = mysqli_connect("db credentials") or die("Error connecting to database!");
    $mysqli->set_charset("utf8");


    $query = $mysqli->prepare("SELECT * FROM ft WHERE Sogn = '$soger' OR (Sogn LIKE '$cities[0]' OR Sogn LIKE '$cities[1]' )ORDER By Aar");
//var_dump($query);

    // Do we have a query to run
    if (isset($query) && $query):
        $query->execute();
    $results = $query->get_result();
    endif;

    ?>  

    <?php if ($results->num_rows): ?>

    <table id="FtKTable" class="table">
     

     <tr>

        <th>&nbsp;</th> <th>Amt By</th> <th>Aar</th>
    </tr> 

    <!-- Loop through the results of the query -->
    <?php while ($row = $results->fetch_object()): ?>


    <!-- Links for the individual rows -->    
    <?php
   $link1 = sprintf("<a href='%s' target='_blank'>%s</a>", $row->URL, $row->AmtHerredSognGade);
    $link4 = sprintf($row->AmtHerredSognGade); 
    ?>



                <?php if ($row->Collapse == 'p'): ?>
                <!-- We have a parent row -->

                <tbody class='toggle' >
                    <tr class="expander">
                        <td><span class='toggle'></td>
                        <!--<td>&nbsp;</td> -->                     
                        <!-- link 1 -->
                        <td><?php echo (!empty($row->URL)) ? $link1 : $link4 ?></td><td>
                        <?php echo $row->Aar ?></td>  
 
                    </tr>

                <?php elseif ($row->Collapse == 'c'): ?>
                <!-- We have a child row -->

                <tr class="child">
                        <td>&nbsp;</td>
                       <!-- link 1 -->
                        <td><?php echo (!empty($row->URL)) ? $link1 : $link4 ?></td><td>
                        <?php echo $row->Aar ?></td>  
                    </tr> 

                <?php else: ?>
                <?php if($row->Collapse == '')?>
                <!-- We have a non-collapsible row -->

                <tr class="non">
                    <td>&nbsp;</td>
                    <!-- Område, link -->
                       <!-- link 1 -->
                        <td><?php echo (!empty($row->URL)) ? $link1 : $link4 ?></td><td>
                        <?php echo $row->Aar ?></td>  
         
                </tr>

        <?php endif; ?>




<?php endwhile; ?>

</table>

<?php else: ?>

    <p>Der er ingen resultater for denne søgning</p>

<?php endif; ?>

<?php mysqli_close($mysqli);?>  

</div>



<!-- Column2 end--> 



<!-- Column3 start--> 
<div class="w3-col w3-mobile" style="width:28%; margin-top: 30px; margin-left: 10px; margin-bottom:10px;  text-align: left;"> 
    <p>Skifter</p> 


    <!-- Column3 end-->  
</div>

<!-- Main page end--> 
</div>  
<!-- Page content end-->  



</body>   
</html>

Open in new window

Peter KromanSales ExecutiveAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Peter KromanSales ExecutiveAuthor Commented:
Update line 168-174:

<?php                    
    // Connect to the database is we have a POST
    $mysqli = mysqli_connect("mysql31.unoeuro.com", "genealogisk_dk1", "AevleBaevle194287Bum", "genealogiskforum_dk_db8") or die("Error connecting to database!");
    $mysqli->set_charset("utf8");


    $query = $mysqli->prepare("SELECT * FROM ft WHERE Collapse <> '' AND Sogn = '$soger' OR (Sogn LIKE '$cities[0]' OR Sogn LIKE '$cities[1]' ) AND Type = 'Købstad' ORDER By Aar");

Open in new window

0
Chris StanyonWebDevCommented:
Hey Peter,

I see we're re-visiting this one again :)

Firstly, let me just run through a couple of things which may affect the way you think about this problem. Your data, in concept, is hierarchical (a parent / child relationship). Now in most situations, a database representation of this data has 2 tables - a parent table and a child table. That makes it very easy to maintain the relationship (a relational database). You would then have something like this:

// Parent Table
ParentId = 1, Name = Record 1
ParentId = 2, Name = Record 2
ParentId = 3, Name = Record 3

// Child Table
ChildId  = 1, Name = Child Record 1, ParentId = 1
ChildId  = 2, Name = Child Record 2, ParentId = 1
ChildId  = 3, Name = Child Record 3, ParentId = 2
ChildId  = 4, Name = Child Record 4, ParentId = 1
ChildId  = 5, Name = Child Record 5, ParentId = 2
ChildId  = 6, Name = Child Record 6, ParentId = 3

Open in new window

This is a one-to-many relationship between the parent and the child - 1 parent can have many children, and you can easily see which children belong to which parent.

The overall problem you're having is down to the fact that you are attempting to represent this hierachy in a single flat table, and you indicate the parent / child relationship by having a P or a C in the collapse column:

Parent Record 1, Collapse = P
Child Record 1, Collapse = C 
Child Record 2, Collapse = C 
Child Record 3, Collapse = C 
Parent Record 2, Collapse = P
Child Record 4, Collapse = C 
Child Record 5, Collapse = C 
Child Record 6, Collapse = C

Open in new window

This sort of works if you only ever show all records in a given order. The real problem with this approach is that a collection of DB records doesn't really have a static, pre-defined sort order. If at any point you filter or sort the data, then your hierarchy starts to fail. You could easily end up with something like this:

Child Record 1, Collapse = C 
Child Record 3, Collapse = C 
Parent Record 1, Collapse = P
Child Record 6, Collapse = C 
Child Record 4, Collapse = C 
Child Record 5, Collapse = C
Parent Record 2, Collapse = P

Open in new window

When that happens, your entire approach to the parent / child relationship fails, and in your case you end up with some odd results for the collapsible headers, as the correct children no longer follow the correct parents.

I suspect this is what's happening with your data. I can't test it to be certain because I don't have your dataset, but your query ...

SELECT * FROM ft WHERE Sogn = '$soger' OR (Sogn LIKE '$cities[0]' OR Sogn LIKE '$cities[1]' )ORDER By Aar

Open in new window

... is definitely filtering and sorting.

Just as a quick test, try running the query directly in your Database client (phpymyadmin etc), and checking the results. You'll likely see that the P and C rows are out of sync.
0
Peter KromanSales ExecutiveAuthor Commented:
OK. Understood.

So your suggestion is to split the table into two tables like this:
Parents:
Sk-rmbillede-2017-12-20-07.53.19.png

Children:
Sk-rmbillede-2017-12-20-07.54.14.png

Well - I have exactly 1.000 parents and I have 47.715 children, so I have a little job to perform here :)
But I wiil do it, and when that is done I might need some assistance to get the queries right in this new database environment :)
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Chris StanyonWebDevCommented:
Hey Peter,

I definitely think you should be splitting your table into 2, but ultimately that's a design decision based on your own data, and you know it better than anyone.

The table structures will mean that you only have the Parent info in one table, and all the Child info in the other, along with a ID linking to the parent record ( a foreign key ).

What you've shown looks like a good start, although in Parent, you have an id field and a Parent field - you only need the id field. In the Child table, you have an id, Parent, and Child field - you only need the id and Parent.

You may also want to split the AmtHerredSognGade data into their own fields - this is part of the Db Normalisation process.

Once it's all in place, then the query you run to select your data will need to be a JOIN query.

It makes sense to keep your current copy of the database and create a new one with the new schema. When you have all your data in place, you can then run test queries using your Db management tool of choice (phpmyadmin / mysqlyog / workbench etc).

What you'll end up with is something resembling this:

Parent / Child Relationship
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,

I think I will keep on having the id columns as an integer with auto_increase, and then keep the parent/child id columns as non auto_increase fields. This because I need to be able to manually control and edit  these two id-columns in case missing lines or errors in one of the tables are discovered later.

The AmtHerredSognGade field I can not split out more than I have allready done because the structure of the datasources is not unique. I have drawn out the Sogn and the Amt out in their own columns for the Types where it is possible to do that which is "Købstæder" (Cities) and "Landdistrikter" (Countryside). It is is in this context I have to work :)

With this structure I need to create at least three tables:
  • One for Parents
  • One for Children
Parent and Children lines are represented in the Types "Købstæder" (Cities) and "Hovedstaden" (Capitol)
  • One for none of the above
Which holds lines within any of the 6 Types I am working with

The six Types are:
Hovedstaden (Capitol)
Købstæder (Cities)
Landdistrikter (Countryside)
Slesvig (southern part of the country which has bees danish but where a part of it today is german)
Særtællinger (special records completely out of structure)
Diverse (count-lists and other "out-of-structure" stuff)

And we need to get the three tables and the six Types to work together afterwards - so I believe that we need to make more than one JOIN :)
But let's get back to that when I am ready with the new datastructure.
0
Chris StanyonWebDevCommented:
Hey Peter,

All makes sense. Not entirely sure you need a third table though. Could you not just add these records into the child table but without a PaarentId column set. This way when we do a JOIN query on the 2 tables, those records will simply have a ParentId of NULL.

Not really sure I understand your point about manually setting the parent / child IDs, as this is usually a way that errors creep in. These ids are simply used for the relationship. If you add a new Parent record, it get's a new AutoId. If you then add new Children, they each get there own new AutoId, and you can assign the ParentId accordingly.

Maybe I'm misunderstanding you on this and like I said - you know the data better than anyone.

As soon as you start getting into 3 JOIN, just be careful that your data still makes sense.
0
Peter KromanSales ExecutiveAuthor Commented:
My concern is that if I - for some reason - should get the need to remove a parent row or to add a parent row in the middle of the table, then my "hard-coding" if child relations to parent id's would slide. Perhaps this is groundless - but now I am making it as described, then we can decide later which of the columns it is best to use :)

And yes - it makes sense to add the non-collapsible rows to the child table - so I believe that I can manage with two tables :)

I will get back when I am finished splitting and re-ordering the table:)
0
Chris StanyonWebDevCommented:
No worries Peter,

Whatever works best for you is fine.

Earlier I pointed out that a table of data doesn't really have a static sort order, so adding a row into the 'middle' of the table doesn't really happen. If you delete a Parent row, then the ParentId of the child record would be set to null because there would no longer be a parent for it, so unless you intend to orphan the children, deleting a parent row probably won't be the way you go.

Not trying to influence you either way, just trying to point out why we do things the way we do, and help you to acheive a robust solution.

Data design can get quite tricky sometimes :)
0
Peter KromanSales ExecutiveAuthor Commented:
Now I have the new data structure in place.

I have a ft_parents table with this table structure:
Sk-rmbillede-2017-12-21-09.35.32.pngand this design (collapsed a bit):
Sk-rmbillede-2017-12-21-09.37.04.png
I have a ft_children table with this table structure:
Sk-rmbillede-2017-12-21-09.38.31.pngand this design (again collapsed):
Sk-rmbillede-2017-12-21-09.40.19.png
I have decided that I will keep and use the column Child_id in the ft_parents table even if it at this moment shows the same values ad the primary id column in the table does. I'll feel a little safer this way - perhaps this is pure nonsense - but anyway :) :)

What I need a little help to now is to create the JOIN and to make those two tables work together. First regarding the code this post starts with, an afterwards in the primary census page supporting all the ways tables might be searched. But I believe that if I get a little help to make it work in the first instance here, I can manage to get it working in other instances my self (hoping :) ).
0
Chris StanyonWebDevCommented:
OK Peter,

As long as your happy with it, that's fine. It already seems confusing to me having a field in the parent table called child*, as that field has nothing to do with the child table - the relationship is the other way around, but ...

This is probably going to get quite tricky, so I think it's best if we work through it part by part. Hopefully you have a decent Db editor as it will make this a whole lot easier. Basically, there are several types of JOIN in sql. We need to be joining the parent table to the child table. Normally if you have matching records in both tables, this is a straight forward INNER JOIN (or simply JOIN). We have data where there may be records in the child table that don't relate to the parent table, so we need a RIGHT JOIN.  Here's the basic syntax for the query:

SELECT p.*, c.*
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id

Run that in your Db IDE and see what you get.

You should get a list of all records from the child table, and with it, all the info from the related record in the parent table. If the child doesn't have a record in the parent table, then those columns would show as NULL.
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,

I use SequelPro as SQL editor which I am very happy with :)  

When I run your query on the parent table I get exactly the result you describe :)

Here is a scr. shot of the place where children and none children splits.
Sk-rmbillede-2017-12-21-19.55.23.png
0
Chris StanyonWebDevCommented:
Perfect. Looks good.

OK. Next step and this one might be a bit mundane. Because of the way your table columns are named, you'll notice you have several columns in your query results named the same, for example both tables have Herred, Sogn and Amt columns etc, so when you do the query in PHP, you wil have a $row result, but you won't get both columns. You can't have $row['Herred'] and $row['Herred'] in the same array, so it will only pull in one of them.

Because of this, you'll need to alias your column names. The reason I said it will be mundane is because you have a lot of columns to alias (basically ALL of them). You've already seen the alias syntax in the query above - it's basically the AS keyword, so your query would need to start looking something like this:

SELECT
p.Herred AS pHerred, p.Sogn AS pSogn, p.Amt as pAmt, 
c.Herred AS cHerred, c.Sogn AS cSogn, c.Amt as cAmt, 
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id

Open in new window


Now when we run the query, your column names will differ - pHerred and cHerred for example, and when we get to the PHP code you will have your $row representing the entire record and $row['pHerred'] and $row['cHerred'] for the 2 difference columns. You can alias them any way that makes sense to you: parentHerred, p_herred etc. Just remember, whatever you alias them to is what you'll refer to them as in PHP.

You don't have to pull in all columns (like you would with *). In fact best-practice is to only pull in the columns you actually need for the task at hand, so that may make your life a little easier. It may also be worth looking at the query results above and starting to think about whether you can normalise the data some more. For example, do you really need ALL the data for a parent record and ALL the data for a child record. Do you really need the Year on the parent records when you've already got it on the child records? Take a look at the duplication in your query results and really start to think about the structure of your data. The better your data is, the easier your coding will be :)
0
Peter KromanSales ExecutiveAuthor Commented:
Now I have to rest a bit. I'll get back to morrow :)

But the last query returns an error in Sequel Pro:
Sk-rmbillede-2017-12-21-20.37.15.png
0
Chris StanyonWebDevCommented:
Sorry Peter - there's an extra comma after AS cAmt on line 2. Should be:

SELECT
p.Herred AS pHerred, p.Sogn AS pSogn, p.Amt as pAmt, 
c.Herred AS cHerred, c.Sogn AS cSogn, c.Amt as cAmt
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,
That works.

But a couple of questions for my better understanding :)

1. How come that we are using p.'s and c.'s - since we have removed the p's and c's form the tables - is p. and c. special parameters which are used with parent/child tables?
2. I had the understanding that the tables should have the exact same structure and column-naming to be used for JOIN, but as I understand you, this is absolutely not necessary. Is that correct? If yes, wouldn't it be better if I just renamed the columns to be unique in stead of using the same column names in both tables? And perhaps at the same time removed all those columns in the parents table that is not used?
0
Peter KromanSales ExecutiveAuthor Commented:
Just a little more for my understanding :)

In the ft_children table there are 447 entries with the Sogn Haderslev.

When I pull this query I get the 447 entries with Haderslev sogn in the ft_children table but none from the ft_parents table - where there are 4.
SELECT
p.Sogn AS pSogn, p.Aar as pAar, 
c.Sogn AS cSogn, c.Aar as cAar
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE c.Sogn = 'Haderslev'

Open in new window

Sk-rmbillede-2017-12-22-07.42.57.png
And when I pull this query I get 435 entries with Haderselv sogn - now without the 12 ones that is not related to anything in the Parent_id column in the ft_children table but still no entries from the ft_parents table.
SELECT
p.Sogn AS pSogn, p.Aar as pAar, 
c.Sogn AS cSogn, c.Aar as cAar
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE p
.Sogn = 'Haderslev'

Open in new window

Sk-rmbillede-2017-12-22-07.50.21.png
In both cases they are listed in identical columns except for the column name, but what I need is really the parents listed separately and the children listed separately. I assume that this is controlled when we get to the PHP part of this, but I need to understand the SQL mechanism a little better before we get to that :) :)
0
Peter KromanSales ExecutiveAuthor Commented:
Sorry for my spamming :)

But when I do like this I get the right parents:
SELECT DISTINCT
p.Sogn AS pSogn, p.Aar as pAar
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE p.Sogn = 'Haderslev'

Open in new window

Sk-rmbillede-2017-12-22-08.07.52.png
And here I get the right children:
SELECT
c.Sogn AS cSogn, c.Aar as cAar
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE c.Sogn = 'Haderslev'

Open in new window

0
Chris StanyonWebDevCommented:
Hey Peter,

I'll try and address your questions one by one :)

Firstly, the p and c's. When you run the SELECT part of your query, we need to show which columns we need to pull out:

SELECT column1, column2, column3 etc

In your tables, you have columns that are called the same in each table, so you can't just do:

SELECT Herred, Herred, Amt, Amt etc

because that simply won't work, so you need to explicitly state which table each column is coming from:

SELECT ft_parents.Herred, ft_children.Herred, ft_parents.Amt, ft_children.Amt,

It gets tiresome typing ft_parents and ft_children over and over again, so I aliased them to p and c:

FROM ft_parents AS p RIGHT JOIN ft_children as c

so then it becomes easier to refer to the ft_parents table as p and the ft_children table as c:

SELECT p.Herred, c.Herred, p.Amt, c.Amt,

You can alias tables to whatever you like. If I were to have 2 tables called customer_details and customer_orders I could do:

SELECT det.name, det.address, ord.id, ord.date
FROM customer_details as det JOIN customer_order as ord
ON det.id = ord.id;

instead of:

SELECT customer_details.name, customer_details.address, customer_order.id, customer_order.date
FROM customer_details JOIN customer_order
ON customer_details.id = customer_order.id;

Regarding the exact same structure - they most definitely do not have to have the same structure and columns names. In fact, I've never joined 2 tables that do have the same structure. Usually you're joining tables that have very different, but related structure and data - Customers/Orders/OrderDetails, Categories/Products, Countries/Cities, Authors/Books for example.

You should definitely remove all the columns in the Parent table that aren't used. That's part of a process called Normalisation. A table should only hold information relevant to it.
0
Chris StanyonWebDevCommented:
When I pull this query I get the 447 entries with Haderslev sogn in the ft_children table but none from the ft_parents table - where there are 4.
That is correct. Although you are joining the 2 tables together, you are filtering based on the value in the child table:

WHERE c.Sogn = 'Haderslev'

It doesn't search the parent table at all. It pulls in all the records that match from the child table along with the related records from the parent table

And when I pull this query I get 435 entries with Haderselv sogn - now without the 12 ones that is not related to anything in the Parent_id column in the ft_children table but still no entries from the ft_parents table.
Again this is correct, because you're pulling in data based on a search from the parent table, not the child table.

To understand why this is happening, you need to understand the different types of JOIN. In simple terms, you have 4 types of join (there are others but I won't go into that here). Image you tables sitting side by side - the parent on the left and the child on the right.

A normal JOIN will pull in records that only have a matching record in each table
A LEFT join will pull in ALL the records from LEFT table and those matching in the right
A RIGHT join will pull in ALL the records from the RIGHT table and those matching in the left
A FULL join will pull in all records from both

We have a RIGHT join, so it will pull in all Child records plus those that have a match in the Parent table. This means it will pull in Child records, even if they don't have a match in the Parent table, effectively creating your orphaned or non-toggle rows (rows that don't have a parent).

Because we have a RIGHT join, it won't pull in Parent records that don't have Child records. As I understand it, that wouldn't make any sense. Why would you want a Parent row that you could toggle, with no children to actually toggle.

Have a quick look at this. It's a very visual way to understand the different joins - http://wikiwebpedia.com/join-in-microsoft-sql
0
Peter KromanSales ExecutiveAuthor Commented:
That makes a lot of sense :) Thanks.

I will remove all the columns not used in the parents table. And will make the JOIN proces easier if I change the remaining column names so they are not the same in both tables?
0
Chris StanyonWebDevCommented:
When you pull the data down in your queries, you will end up with the data from BOTH tables in each row:

pHerred, pAmt, cHerred, cAmt etc

ParentHerred1, ParentAmt1, ChildHerred1, ChildAmt1
ParentHerred1, ParentAmt1, ChildHerred2, ChildAmt2
ParentHerred1, ParentAmt1, ChildHerred3, ChildAmt3
ParentHerred2, ParentAmt2, ChildHerred4, ChildAmt4
ParentHerred2, ParentAmt2, ChildHerred5, ChildAmt5
NULL, NULL, ChildHerred6, ChildAmt6

These are effectively ALL rows from the Child table, but include the related data from the Parent table. If the Child does not have a related Parent record, the pHerred, pAmt, etc will have NULL values. This is what indicates that it's an orphaned record.

Once we've got that flat data, in PHP we'll loop through the records and build a proper hierarchy, so that you will have a list of Parent records, each with their own Child records attached. The orphaned records obviously won't have a parent. This will allow us to simply loop through the data and create the HTML.

Parent1
   Child1
   Child2
   Child3
Parent2
   Child4
   Child5
Orphan1 (Child6)
0
Peter KromanSales ExecutiveAuthor Commented:
Yes - I am beginning to understand - Beginning :)

But you say that the query pulls out data from both tables. But this query only pulls 447 entries matching the exact number of entries in the children table. Should in not pull out 451 rows since there are 4 rows int he parents table that match the query? Or am I just a little heavy on this :)
SELECT
p.Sogn AS pSogn, p.Aar as pAar, 
c.Sogn AS cSogn, c.Aar as cAar
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE c.Sogn = 'Haderslev'

Open in new window

0
Chris StanyonWebDevCommented:
Hey Peter,

It does pull out data from both tables. It pulls out ALL the data from the child tables and if there is MATCHING data in the parent table for that child record it also pulls that in - hence the 'pulling data from both tables'

The reason it won't pull in the 4 records from the Parent table is because they don't have any related children - there is no record in the child table that matches.

Let's assume you have 3 records in Parent:

ParentId1: Name of Parent 1
ParentId2: Name of Parent 2
ParentId3: Name of Parent 3

And in the child table you have 5 records:

Child1, ParentId1
Child2, ParentId1
Child3, ParentId2
Child4, ParentId1
Child5, 0 (or empty)

Now when you run the query you will get 5 records back - which is all the records from the Child Table. Each of these records will also contain the related data from the Parent Table:

Child1, Name of Parent 1
Child2, Name of Parent 1
Child3, Name of Parent 2
Child4, Name of Parent 1
Child5, NULL

You'll see that Child 5 has no related record in the Parent table, so the value is NULL, indicating an orphaned record (no Parent).

You also won't see any reference to Parent3 in the results, because Parent3 has no related records in the Child Table, and as we're pulling from the Child table it won't get included.

Have a quick look at this:

Right JoinYou'll see the Parent table on the left and the Child table on the right. We are doing a RIGHT join so the records selected in your query will be ALL the records from the Right table (Child table) and they'll also include the related info from the Parent Table if it exists. Records that exist in the Parent Table, but have no related records in the child table are represented by the white section on the left and they won't be selected at all.

Hope that makes it a little clearer :)
0
Peter KromanSales ExecutiveAuthor Commented:
Hey - now I think I can see the Christmas lights :)

The parent rows are not actually shown, but every children row that has a parent is marked with a pSomething value in the query result.
Am I on track now ....
0
Chris StanyonWebDevCommented:
Yep - you got it !

Your query is basically searching the child table and returning records from that child table, but because of the JOIN, if it finds a related record in the Parent, it will include that information alongside it's own.

Understanding JOINS is one thing, but explaining them is quite something else :)
0
Peter KromanSales ExecutiveAuthor Commented:
You are SO right - I am working hard to understand ... perhaps I will reach a point somewhere in a far future where I also can explain it :)

Well - in the mean time I have reworked the data-structure a little.

Parents table looks now like this
Sk-rmbillede-2017-12-22-14.12.07.png
And children table looks like this
Sk-rmbillede-2017-12-22-14.13.19.png
How do we move on from here :)
0
Chris StanyonWebDevCommented:
OK. Looking better :)

There are a few moving parts to this, so it makes sense to start simple and build on it. Firstly I would get the query working in PHP - don't worry about forms or POST data just yet. We need to know the query is working and the flat data is being turned into hierarchical data. We can worry about the actual search functions in a bit.

A simple example of what I'm taking about is this:

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

require_once('data.php');

// Let's deal with our data
$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.Aar AS pAar, p.id AS pId,
    c.Sogn AS cSogn, c.Aar AS cAar, c.id AS cId
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE c.Sogn = 'Haderslev'
EOT;

if ($results = $mysqli->query($sql)): // Execute the query

    while ($row = $results->fetch_object()): // Loop through the Db results

        var_dump($row);

    endwhile;

endif;

Open in new window

Load that up in your browser and run it. It will basically 'dump' each row that your query returns so you can see exactly what's being returned fromt the Db. You might want to set a WHERE clause that only returns a few records - if you're returning several hundred your page is going to get very, very long.

The 'data.php' file in my example looks something like this:

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

$hostname = 'localhost';
$username = 'username';
$password = 'password';
$database = 'database';

$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");

Open in new window

Get that part working and then I'll show you the next part
0
Peter KromanSales ExecutiveAuthor Commented:
I believe everything is returned correctly - but it is a little hard to overlook the results.
Take a look here: http://kroweb.dk/gfdev/canvas/test.php

I am running this query
SELECT
    p.Sogn AS pSogn, p.Aar AS pAar, p.id AS pId,
    c.Sogn AS cSogn, c.Aar AS cAar, c.id AS cId
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE c.Sogn = 'Haderslev' AND c.Aar = '1921'

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
With CMD+U it gets easier to overlook the results. I'm sure it is right :)
0
Chris StanyonWebDevCommented:
Haha. Yeah - that's perfect. Just view the source of your page (Ctrl+U) and it'll make much more sense :)

All looks good - scroll right to the bottom (in source view) and you'll see an example of an orphaned record. You'll also see that all other records have one parent :   Haderslev, 1921, Id: 683

You know your data so if you're happy that's correct, then time to move on ...
0
Peter KromanSales ExecutiveAuthor Commented:
I am jumping up and down with happiness :) :) :) (my favourite wife says to me that I am crazy, and I tend to agree with her :) )
0
Chris StanyonWebDevCommented:
Now for a couple of new concepts to get your head around - classes! Because we're going to be working with Objects, it makes life a lot easier if you have a way to build your parent and child records as separate objects. We will define 3 new classes - one to represent the Parent, one to represent the Child and a utility class to define the Type (Parent / Child / Orphan). Don't worry if this doesn't make sense yet. It will.

Add a new php file called classes.php that contains the following:

<?php
abstract class RecordTypes
{
    const Master = 'master';
    const Child = 'child';
    const Orphan = 'orphan';
}

class Master
{
    public $type;
    public $id;
    public $sogn;
    public $aar;
    public $children;

    function __construct($row)
    {
        $this->type = RecordTypes::Master;
        $this->id = $row->pId;
        $this->sogn = $row->pSogn;
        $this->aar = $row->pAar;
        $this->children = array();
    }
}

class Child
{
    public $type;
    public $id;
    public $sogn;
    public $aar;

    function __construct($row)
    {
        $this->type = (is_null($row->pId)) ? RecordTypes::Orphan : RecordTypes::Child;
        $this->id = $row->cId;
        $this->sogn = $row->cSogn;
        $this->aar = $row->cAar;
    }
}

Open in new window

We will use these classes to create objects out of our database rows. These should be fairly straight forward to understand. Each of the 2 main classes has properties relating to the data. The constructor takes in a $row from our database and assignes various values from this row to the various properties on our class. Have a read through it, and see if it makes sense. You will be adding more properties to each as you go (and updating the Constructor - sometimes called a ctor!).

When we get to use these in the next step it will become a lot clearer - honest!
0
Chris StanyonWebDevCommented:
Hehe - I like happy, slightly mad programmers :)
0
Peter KromanSales ExecutiveAuthor Commented:
You are really challenging me - I might end up being a professor :)

Well - I can understand the part about creating two main classes and attaching relevant properties to them. But I don't, yet, quite understand how this will work.

I have made the file classes.php with the content from your input.
0
Peter KromanSales ExecutiveAuthor Commented:
... but am I quite wrong if I say that fairly many properties and value assignments are missing at this point in both classes - most of course in the Child class??
0
Chris StanyonWebDevCommented:
We're definitely getting into some more advanced topics. Once this is complete, hopefully you'll understand exactly why we're doing it this way, and you may even have an 'oh wow!' moment.

Part of what we're going for here is abstraction - basically keeping all the moving parts of your application separate. Makes coding and debugging a whole lot easier.

So far we have the data connection file (data.php), the models that represents our data (classes.php) and the data logic where you actually run the query. In that file, we now need to take the flat results from your query, and create a hierarchy of Parent/Child records using our new classes. I'll post up the code first, so you can take a minute or two to digest it:

<?php 
require_once('data.php');
require_once('classes.php');

// Let's deal with our data
$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.Aar AS pAar, p.id AS pId,
    c.Sogn AS cSogn, c.Aar AS cAar, c.id AS cId
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE c.Sogn = 'Haderslev'
EOT;

$data = array(); // set up the data store

if ($results = $mysqli->query($sql)): // Execute the query

    while ($row = $results->fetch_object()): // Loop through the Db results

        if (!is_null($row->pId)): // We're dealing with parent/child records

             // set some unique array keys
            $parentIndex = "master:" . $row->pId; 
            $childIndex = "child:" . $row->cId;

            if (!array_key_exists($parentIndex, $data)): // do we create a new parent or grab an existing one
                $master = $data[$parentIndex] = new Master($row);
            else:
                $master = $data[$parentIndex];
            endif;

            $master->children[$childIndex] = new Child($row); // create a new child record

        else: // we're dealing with orphaned records

            $orphanIndex = "orphan:" . $row->cId;
            $data[$orphanIndex] = new Child($row); // create a new orphan record

        endif;

    endwhile;

endif;

var_dump($data);

Open in new window

And that's it!

That now takes your flat Db records and converts them into either Master objects or Child Objects. Each Master object (a parent) has several properties:

type, id, sogn, aar, children
 
The children property contains a collection of all the related Child objects, each having the following properties:

type, id, sogn, aar

These objects are added to an array called $data which we can very easily loop through to create the HTML (the next step)

Run the code, and check the source to see whats been output :)

** I called the parent class Master, because the word Parent is reserved in PHP and has special meaning ! We could just have easily called it Header. If we were creating customer and order relationships, then I'd call the classes Customer and Order.
0
Chris StanyonWebDevCommented:
Hey Peter,

You are correct to say that most of the properties are missing at this point. Earlier on I said it would be much easier to start simple while building the logic and that's where we are. Once this is all working and you understand the moving parts, then we'll bulk it out with the proper data.

It's very easy to get bogged down with too much detail at the early stages.

Once we're good, it will simply be a case of adding properties to the class and altering your SQL
0
Chris StanyonWebDevCommented:
sorry Peter - just edited the code slightly - I'd referred to columns as parentId and childId instead of cId and pId
0
Peter KromanSales ExecutiveAuthor Commented:
This produces this error
Parse error: syntax error, unexpected 'all' (T_STRING) in /var/www/kroweb.dk/public_html/gfdev/canvas/classes.php on line 43
If I comment out line 43 in the Classes file I get dumped results like before, but this time the parent is there too :)
0
Peter KromanSales ExecutiveAuthor Commented:
.. and now I get the result sorted in arrays and objects ....
0
Chris StanyonWebDevCommented:
Hmmm - my class file only had 41 lines in it, so I'm not sure what you've got on line 43 !!

All looks perfect so far. Time to bring the magic :)

We now have a $data array that contains either Parent objects (with children) or Orphan objects (child objects without Parents). Each record has a type, so we can easily loop through this data to generate our HMTL.

Remove the var_dump($data) line from your data logic file - we don't need that anymore.

Create one more new file. This will be the UI part of our solution - the actual page that get's displayed (substitute dataLogic with whatever you've called your logic file - the one with the SQL in)

<?php require_once('dataLogic.php'); ?>

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
        <meta name="viewport" content="width=device-width; initial-scale=1.0">

        <title>Chris Stanyon // EE - 29074529</title>

        <style type="text/css">
            td, th { padding: 10px; }
            .master { background-color: red; }
            .child { background-color: green; }
            .orphan { background-color: yellow; }
        </style>    
    </head>
    <body>

       <table>
        <?php foreach ($data as $record): ?>

            <?php if ($record->type == RecordTypes::Master): // We have a parent record ?>

                <tr class="<?php echo $record->type ?>">
                    <th><?php echo $record->sogn ?></th>
                    <th><?php echo $record->aar ?></th>
                </tr>

                <?php foreach ($record->children as $child): // Let's deal with the children! ?>

                    <tr class="<?php echo $child->type ?>">
                        <td><?php echo $child->sogn ?></td>
                        <td><?php echo $child->aar ?></td>
                    </tr>

                <?php endforeach; ?>

            <?php else: // and finally the Orphaned records ?>

                <tr class="<?php echo $record->type ?>">
                    <td><?php echo $record->sogn ?></td>
                    <td><?php echo $record->aar ?></td>
                </tr>

            <?php endif; ?>

        <?php endforeach; ?>
        </table>

    </body>
</html>

Open in new window

Nice and clean :)
0
Peter KromanSales ExecutiveAuthor Commented:
I can see that your file only had 41 lines - I  don't know how this line 43 has sneaked itself in there
Select all

Open in new window


Anyway it is gone now.

Yes , nice and handsome colors :)

So - before I can start getting this worked in to my page(s) I just need to set up the collapse so that children default are collapsed under their parent. I have it set up in the "old" files, but I am not sure how to do it in this environment.
0
Chris StanyonWebDevCommented:
Yeah - the colours were just to give us an idea that we had the correct classes on the rows. We can remove them later on.

The collapsible part is now pretty straight forward. You'll need to wrap each parent and it's children in a <tbody>:

<?php if ($record->type == RecordTypes::Master): // We have a parent record ?>

    <tbody>

        <tr class="<?php echo $record->type ?>">
            <th><?php echo $record->sogn ?></th>
            <th><?php echo $record->aar ?></th>
        </tr>

        <?php foreach ($record->children as $child): // Let's deal with the children! ?>

        <tr class="<?php echo $child->type ?>">
            <td><?php echo $child->sogn ?></td>
            <td><?php echo $child->aar ?></td>
        </tr>

        <?php endforeach; ?>

	</tbody>
	
<?php else: // and finally the Orphaned records ?>

Open in new window

Use CSS to hide the child rows by default:

.child { background-color: green; display:none; }

Open in new window

And then, in the <head> of your page add in the jQuery library and your jQuery script to toggle the child rows:

<script src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
	$('#myTable').on('click', '.master', function() {
		$(this).nextAll('.child').toggle();
	});
});
</script>

Open in new window

You'll see I'm referring to a table with an ID of myTable, so you'll need to add that to your table (or choose your own ID):

<table id="myTable">

Open in new window

And that will give you your toggle functionality. Once you know this is all working as you expect, we can change out which element triggers the toggle, such as adding in a plus icon to click on. We're still building this step-by-step, in a logical way, so it makes sense to implement and test each stage before moving on to the next.
0
Peter KromanSales ExecutiveAuthor Commented:
Yep. I can make that work.

But now it is my turn to add a little complexity to this :)

I need to make this setup work on several pages, and my assumption is that if I can make it work on one page, I rather easily can make it work on the others too.

The page I am working on making it work on is this page, and the table id is "FtKTable"
http://kroweb.dk/gfdev/canvas/sognefakta_k.php 

This page is a step towards another solution to which I will get back later, and at this moment it holds a search field which I don't think is necessary afterwards. This means that it, right now, is necessary to give an input in the searchfield to make the page work, so my suggestion is that we use "Haderslev" as our common input in the searchfield during this work. It is important that the links also is working within the new setup.

I paste in the main code for the present page, and the CSS code - which in fact s adressing three tables - below.
It is in this context I need this new setup to work.

Main code:
<?php
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);


?>


<!DOCTYPE html>
<html lang="da">
<head>
    <!-- Check for title--> 
    <title>Sognefakta</title>

    <meta charset="utf-8" lang="da">





    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
      <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    <link rel="stylesheet" href="style_sognefakta.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>


 

  <script type="text/javascript">
    $(function(){
        $('#FtKTable').on('click', '.toggle', function() {
            $(this).parents('tr').nextAll('tr.child').toggle();
        });
    });
    </script>



</head>


<body>
   <!-- Page content start-->        
   <div class="w3-row" style="overflow: auto">





    <!-- Main page start 4 --> 

    <div class="w3-row" >

            <form action=""  form title=""  style="padding-top:10px; padding-bottom:10px;" method="POST" >


             <input type="search" placeholder= "Søg " style="width: 300px; height: 35px; border: 1px solid #666666;border-radius:2px;color:#666666; color: #9F5584; font-weight: ligther; font-size: 13px; padding-left:10px;" name="soger" id="soger"  />

             <!--<input type="submit"> -->

         </form>  
           <br>

        <!-- Column1 start-->         
        <div class="w3-col w3-mobile" style="width:28%; margin-top: 30px; margin-left: 10px; margin-bottom:10px; padding-bottom: 10px; text-align: left;"> 
            <p>Kirkebøger</p>


            <?php

            function cityVariations($city) {
                $substitute = array(
                    "æ" => "ae",
                    "ø" => "oe",
                    "å" => "aa",
                    "ae" =>"æ",
                    "oe" =>"ø",
                    "aa" => "å",
                    );

            $flatCity = mb_convert_case($city, MB_CASE_LOWER);

            $cities[] = $flatCity;
            $cities[] = strtr($flatCity, $substitute); 

            return $cities;
    }


            $con=mysqli_connect("mysql31.unoeuro.com", "genealogisk_dk1", "AevleBaevle194287Bum", "genealogiskforum_dk_db9") or die("Error connecting to database: ".mysql_error());

            mysqli_select_db($con,"genealogiskforum_dk_db9");
            mysqli_set_charset($con,"utf8");
            

            $soger = trim($_POST['soger']);


                // Get the city variations
            $cities = cityVariations($soger);

                // Prepare the cities for the LIKE clause by wrapping each in %...%
            array_walk($cities, function (&$city) { $city = $city.'%'; });

            ?>


            <?php

            $raw_results = mysqli_query($con, "SELECT * FROM kirkebogssogne WHERE (Sogn = '$soger' OR (Sogn LIKE '$cities[0]' OR Sogn LIKE '$cities[1]' ))");
    
            //var_dump($raw_results);

            ?>

            <?php if ($raw_results->num_rows): ?>

            <table id="KbTable" class="table">


                <tr>
                    <th width="35%"> Sogn </th> <th width="15%">Amt</th> <th width="35%">Kirkebog</th> 
                </tr>

                <?php while ($row = $raw_results->fetch_object()): ?>


                <!-- Links for the individual rows -->    
                <?php
                $link2 = sprintf("<a href='%s' target='_blank'>%s</a>", $row->URL, $row->Kirkebog); 
                ?>

                <tr>
                    <td><?php echo $row->Sogn ?></td>      
                    <td><?php echo $row->Amt ?></td>        
                    <!-- Kirkebog, link -->
                    <td><?php echo $link2 ?></td>

                </tr>


            <?php endwhile; ?>    
        </table>

    <?php else: ?>

    <p>Der er ingen resultater for denne søgning</p>

<?php endif; ?>

<?php mysqli_close($con);?>                    
</div>

<!-- Column1, end-->


<!-- Columns2, start--> 
<div class="w3-col w3-mobile" style="width:28%; margin-top: 30px; margin-left: 45px; padding-left: 00px; padding-right: 0px; padding-bottom: 10px;  text-align: left; "> 
    <p>Folketællinger </p> 

   


 <?php                    
    // Connect to the database is we have a POST
    $mysqli = mysqli_connect("mysql31.unoeuro.com", "genealogisk_dk1", "AevleBaevle194287Bum", "genealogiskforum_dk_db8") or die("Error connecting to database!");
    $mysqli->set_charset("utf8");


    $query = $mysqli->prepare("SELECT * FROM ft WHERE Collapse <> '' AND Sogn = '$soger' OR (Sogn LIKE '$cities[0]' OR Sogn LIKE '$cities[1]' ) AND Type = 'Købstad' ORDER By Aar");
//var_dump($query);

    // Do we have a query to run
    if (isset($query) && $query):
        $query->execute();
    $results = $query->get_result();
    endif;

    ?>  

    <?php if ($results->num_rows): ?>

    <table id="FtKTable" class="table">
     

     <tr>

        <th>&nbsp;</th> <th>Amt By</th> <th>Aar</th>
    </tr> 

    <!-- Loop through the results of the query -->
    <?php while ($row = $results->fetch_object()): ?>


    <!-- Links for the individual rows -->    
    <?php
   $link1 = sprintf("<a href='%s' target='_blank'>%s</a>", $row->URL, $row->AmtHerredSognGade);
    $link4 = sprintf($row->AmtHerredSognGade); 
    ?>



                <?php if ($row->Collapse == 'p'): ?>
                <!-- We have a parent row -->

                <tbody class='toggle' >
                    <tr class="expander">
                        <td><span class='toggle'></td>
                        <!--<td>&nbsp;</td> -->                     
                        <!-- link 1 -->
                        <td><?php echo (!empty($row->URL)) ? $link1 : $link4 ?></td><td>
                        <?php echo $row->Aar ?></td>  
 
                    </tr>

                <?php elseif ($row->Collapse == 'c'): ?>
                <!-- We have a child row -->

                <tr class="child">
                        <td>&nbsp;</td>
                       <!-- link 1 -->
                        <td><?php echo (!empty($row->URL)) ? $link1 : $link4 ?></td><td>
                        <?php echo $row->Aar ?></td>  
                    </tr> 

                <?php else: ?>
                <?php if($row->Collapse == '')?>
                <!-- We have a non-collapsible row -->

                <tr class="non">
                    <td>&nbsp;</td>
                    <!-- Område, link -->
                       <!-- link 1 -->
                        <td><?php echo (!empty($row->URL)) ? $link1 : $link4 ?></td><td>
                        <?php echo $row->Aar ?></td>  
         
                </tr>

        <?php endif; ?>




<?php endwhile; ?>

</table>

<?php else: ?>

    <p>Der er ingen resultater for denne søgning</p>

<?php endif; ?>

<?php mysqli_close($mysqli);?>  

</div>



<!-- Column2 end--> 



<!-- Column3 start--> 
<div class="w3-col w3-mobile" style="width:28%; margin-top: 30px; margin-left: 10px; margin-bottom:10px;  text-align: left;"> 
    <p>Skifter</p> 


    <!-- Column3 end-->  
</div>

<!-- Main page end--> 
</div>  
<!-- Page content end-->  



</body>   
</html>

Open in new window


CSS code:
          #header { margin-top: 10px; margin-bottom:10px; }
          tbody.toggle tr.child { display: none; background-color: #f2f2f2;}
          tbody.toggle tr:nth-child(even){ display: none; background-color: lightgrey;}
          tbody.toggle tr.expander { display: table-row; background-color: rgba(204, 255, 153, 0.1); }
          tbody.toggle tr.non { display: table-row; background-color: rgba(204, 255, 153, 0.1); }
          span.toggle { cursor: pointer; color: cornflowerblue; font-family: 'FontAwesome'; font-size:16px; }
          span.toggle:hover { color: blue; }
          span.toggle:after { content: '\f055' };



        .dataTables_filter { float:right; }
        .dataTables_info { float: left; }    

        #KbTable tr td:nth-child(1){width: 115px; font-size: 12px; font-weight:normal; color:black;border-bottom: 1px solid;}
        #KbTable tr td:nth-child(2){width: 30px; font-size: 12px; font-weight:normal; color:black;border-bottom: 1px solid;}
        #KbTable tr td:nth-child(3){width: 115px; font-size: 12px; font-weight: normal; color:black;border-bottom: 1px solid;}
        #KbTable tr th{font-family: Serif; font-size: 18px; background-color:#f6f6f6; text-align:left; padding-left: 20px;}

    #KbTable, td {
        border-bottom: 1px #666666;
        border-style: none;
        border-collapse: collapse;
        padding-left:20px;
       background-color: rgba(204, 255, 153, 0.1);         
    }        
                  

        #FtKTable tr td:nth-child(1){width: 5px; font-size: 12px; font-weight:normal; color:black; border-bottom: 1px solid #666666; border-collapse: collapse;}
        #FtKTable tr td:nth-child(2){width: 200px; font-size: 12px; font-weight:normal; color:black;border-bottom: 1px solid; border-collapse: collapse;}
        #FtKTable tr td:nth-child(3){width: 30px; font-size: 12px; font-weight: normal; color:black;border-bottom: 1px solid; border-collapse: collapse;}
        #FtKTable tr th{font-family: Serif; font-size: 18px; background-color:#f6f6f6; text-align:left; padding: 0px;}
        #FtKTable tr th:nth-child(2){padding-left:15px;}
        #FtKTable tr th:nth-child(3){text-align:right;}
 

      #FtKTable, td {

        border-top: 0px solid #666666;
        border-bottom: 0px solid #666666;
        border-left: 0px solid #666666;
        border-right: 0px solid #666666;
        border-collapse: collapse;
        padding-left:5px;
        padding-right:5px;
        background-color: rgba(204, 255, 153, 0.1);


      } 

        
        #FtLTable tr td:nth-child(1){width: 200px; font-size: 12px; font-weight:normal; color:black;border-bottom: 1px solid; border-collapse: collapse;}
        #FtLTable tr td:nth-child(2){width: 30px; font-size: 12px; font-weight: normal; color:black;border-bottom: 1px solid; border-collapse: collapse;}
        #FtLTable tr th{font-family: Serif; font-size: 18px; background-color:#f6f6f6; text-align:left; padding: 0px;}
        #FtLTable tr th:nth-child(1){padding-left:15px;}
        #FtLTable tr th:nth-child(2){text-align:right;}
           
           #FtLTable, td {
        border-top: 0px solid #666666;
        border-bottom: 0px solid #666666;
        border-left: 0px solid #666666;
        border-right: 0px solid #666666;
        border-collapse: collapse;
        padding-left:5px;
        padding-right:5px;
        color:black;
        background-color: rgba(204, 255, 153, 0.1);
      }

        #FtHTable tr td:nth-child(1){width: 200px; font-size: 12px; font-weight:normal; color:black;border-bottom: 1px solid; border-collapse: collapse;}
        #FtHTable tr td:nth-child(2){width: 30px; font-size: 12px; font-weight: normal; color:black;border-bottom: 1px solid; border-collapse: collapse;}
        #FtHTable tr th{font-family: Serif; font-size: 18px; background-color:#f6f6f6; text-align:left; padding: 0px;}
        #FtHTable tr th:nth-child(1){padding-left:15px;}
        #FtHTable tr th:nth-child(2){text-align:right;}

      #FtHTable, td {
        border-top: 0px solid #666666;
        border-bottom: 0px solid #666666;
        border-left: 0px solid #666666;
        border-right: 0px solid #666666;
        border-collapse: collapse;
        padding-left:5px;
        padding-right:5px;
        color:black;
        background-color: rgba(204, 255, 153, 0.1);
      }      


        #header { margin-top: 10px; margin-bottom:10px; }


    /* unvisited link */
    a:link {
        color: black;
        text-decoration: none;
    }

    /* visited link */
    a:visited {
        color: ;
    }

    /* mouse over link */
    a:hover {
        color: red;
        text-decoration: underline;
    }

    /* selected link */
    a:active {
        color: black;
        text-decoration: none;
    }  
          



 

 

  



   



     #HtTable, th {
  
    }

    #HtTable, td {
        border-bottom: 1px #666666;
        border-style: none;
        border-collapse: collapse;
        padding-top:10px;
        padding-left:20px;
        color:black;
        font-family: arial;
        font-size: 14px;
        font-weight: normal;
        text-align: left; 
        background-color: rgba(204, 255, 153, 0.2);         
    }   

  

    p {
        margin-top: 0px;
        padding-top:0px;
        margin-bottom: 0px;
        margin-right: 10px;
        margin-left: 0px;
        color: #9F5584;
        font-weight: bold;
        font-family: Serif;
        font-size: 20px;
        padding-left:5px;
        text-align: left;
        

    }

    h3 {
        margin-top: 0px;
        padding-top:0px;
        margin-bottom: 0px;
        margin-right: 10px;
        margin-left: 0px;
        color: #9F5584;
        font-weight: normal;
        font-family: Arial ;
        font-size: 18px;
        text-align: center;

    }

    h4 {
        margin-top: 0px;
        padding-top:0px;
        margin-bottom: 0px;
        margin-right: 10px;
        margin-left: 0px;
        color: #9F5584;
        font-weight: bold;
        font-family: Serif;
        font-size: 20px;
        padding-left:45px;
        text-align: left;

    }


    h5 {
        margin-top: 0px;
        padding-top:0px;
        margin-bottom: 0px;
        margin-right: 0px;
        margin-left: 0px;
        color: #9F5584;
        font-weight: normal;
        font-family: Arial ;
        font-size: 15px;
        text-align: center;

    }

    h6 {
        margin-top: 0px;
        padding-top:0px;
        margin-bottom: 0px;
        margin-right: 10px;
        margin-left: 0px;
        color: #666666;
        font-weight: normal;
        font-family: Helvetica ;
        font-size: 14px;
        text-align: center;

    }



    

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Just to let you know that I have got your suggestions working :) :) :)

http://kroweb.dk/gfdev/chris_table/canvas2.php
0
Chris StanyonWebDevCommented:
That's great Peter.

I'm not entirely sure what you need now, but it feels like you're trying to jump ahead by 3 steps. Doesn't it make sense to get this particular part working before trying to add it into your current setup. Maybe it's just me, but I find it a whole lot easier to work on the clean code before adding in all the other stuff.

If you simply want to replace the FtKTable element with the new code, then completely remove lines 168-258 and drop in the <table> code that we've been creating.

Also, right at the start of your page, add in the following:

<?php require_once('dataLogic.php'); ?>

and change the jQuery to match the code we've been working on ...

... and you should be good to go.

Obviously we haven't built any search functionality into this, or the toggle icons, or the links, or the data checks, so they'll all need adding in at some point. That's going to be trickier if you have additional stuff going on.
0
Peter KromanSales ExecutiveAuthor Commented:
Sorry - you are right - I was moving ahead to quickly.

I am sure it is best to do this step-by-step as you suggest. This will surely work better - also regarding CSS which probably should be adressed a lillte different from before.

So let's move on with the next step :)
0
Peter KromanSales ExecutiveAuthor Commented:
I believe that I have fairly good control over the CSS part now. Except the part about differing backgrouund color on even and odd lines, and of course the + icon on the parents (I have loaded the font-awsome stylesheet :) )

I have put the CSS in a separate CSS file (as suggested from a good friend earlier :) ), and this file looks like this;
    
        td, th { padding: 5px; font-weight:normal;}
 
        #FtKTable tr td:nth-child(1){width: 25px; font-size: 14px; font-weight:normal; color:black; border-bottom: 1px solid #666666; border-collapse: collapse;}
        #FtKTable tr td:nth-child(2){width: 200px; font-size: 14px; font-weight:normal; color:black;border-bottom: 1px solid; border-collapse: collapse;}
        #FtKTable tr td:nth-child(3){width: 30px; font-size: 14px; font-weight: normal; color:black;border-bottom: 1px solid; border-collapse: collapse;text-align:right;}
        .child{ background-color: lightgrey; display: none;} 
        .orphan { background-color: ;}
        .master { background-color: rgba(204, 255, 153, 0.3); font-size:14px; font-family: serif; text-align:left; border-bottom: 1px solid; border-collapse: collapse;}
        thead{background-color: #f6f6f6;font-family: serif;font-size: 18px; text-align: left;border-bottom: 1px solid #666666;}
       

   
     
     #FtKTable, td {
        border-collapse: collapse;
        background-color: rgba(204, 255, 153, 0.1);

      } 



    /* unvisited link */
    a:link {
        color: black;
        text-decoration: none;
    }

    /* visited link */
    a:visited {
        color: ;
    }

    /* mouse over link */
    a:hover {
        color: red;
        text-decoration: underline;
    }

    /* selected link */
    a:active {
        color: black;
        text-decoration: none;
    }  
          


    p {
        margin-top: 0px;
        padding-top:0px;
        margin-bottom: 0px;
        margin-right: 10px;
        margin-left: 0px;
        color: #9F5584;
        font-weight: bold;
        font-family: Serif;
        font-size: 20px;
        padding-left:5px;
        text-align: left;
        

    }

    h3 {
        margin-top: 0px;
        padding-top:0px;
        margin-bottom: 0px;
        margin-right: 10px;
        margin-left: 0px;
        color: #9F5584;
        font-weight: normal;
        font-family: Arial ;
        font-size: 18px;
        text-align: center;

    }

    h4 {
        margin-top: 0px;
        padding-top:0px;
        margin-bottom: 0px;
        margin-right: 10px;
        margin-left: 0px;
        color: #9F5584;
        font-weight: bold;
        font-family: Serif;
        font-size: 20px;
        padding-left:45px;
        text-align: left;

    }


    h5 {
        margin-top: 0px;
        padding-top:0px;
        margin-bottom: 0px;
        margin-right: 0px;
        margin-left: 0px;
        color: #9F5584;
        font-weight: normal;
        font-family: Arial ;
        font-size: 15px;
        text-align: center;

    }

    h6 {
        margin-top: 0px;
        padding-top:0px;
        margin-bottom: 0px;
        margin-right: 10px;
        margin-left: 0px;
        color: #666666;
        font-weight: normal;
        font-family: Helvetica ;
        font-size: 14px;
        text-align: center;

    }



    

Open in new window

0
Chris StanyonWebDevCommented:
Cool. Let's handle the search part next. In your HTML file, before the <table> add in a simple search form. Again it makes sense to start simple and build on it later, so a very simply search form:

<form method="post">
    <input type="text" name="sogn" placeholder="Search by Parish">
    <button type="submit">Search</button>
</form>

Open in new window

Don't worry about styling it yet. We just want a basic form with a single search term (sogn in the example above). Adjust the place holder as necessary.

Once you've got that in, head over to your data logic file. We need to change the query now to use a prepared query. This is necessary for security. While we're at it, we're going to add a couple of fields to the query, so that we can handle your links later on (URL and AmtHerredSognGade)

We're going to change the start of this file a little, so it will now start like this:

<?php
require_once('data.php');
require_once('classes.php');

$data = array(); // initialise the data store

if (empty($_POST)) { return; } // let's quit if we have no search data

// let's deal with our data
$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.Aar AS pAar, p.id AS pId,
    c.Sogn AS cSogn, c.Aar AS cAar, c.id AS cId, c.URL as cURL, c.AmtHerredSognGade as cAmtHerredSognGade
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE c.Sogn = ?
EOT;

if ($stmt = $mysqli->prepare($sql)): // prepare the query

    $sogn= $_POST['sogn']; // get the search term from the form

    $stmt->bind_param('s', $sogn); // bind the search term to the prepared query
    $stmt->execute(); // and run the query

    $results = $stmt->get_result(); // get the results from the query

    while ($row = $results->fetch_object()): // loop through the Db results
        ...

Open in new window

If you now run your page and enter Haderslev in the search field, you should see the same results as before.

One other thing I would do now (ready for the next step) is to re-name your datalogic php file. Basically, this is the logic specifically for this one table, so I would name it accordingly - for example, call it table1.logic.php and then make sure you've change it in the HTML File:

<?php require_once('table1.logic.php'); ?>

The reason we do this is because later on you will create another logic file to handle your other table (and call that table2.logic.php for example. This allows us to handle the logic (db search etc) independently of each other. You can call them something more meaningful if you like, just match the HTML include to the name of the file.

Hope that all makes sense.

In the next step we'll add in the links, the toggle icons, and some very easy styling :)
0
Peter KromanSales ExecutiveAuthor Commented:
Yep. It makes perfect sense. I have renamed the dataLogic file to data_SQL and combined it with the data file - that makes it much easier for me to understand afterwards.  I have also edited slightly in the classes and the data_SQL files to make the results a little more realistic.

The page is this: http://kroweb.dk/gfdev/chris_table/canvas2.php

I paste in code for the three files here. The search give the right result but I get this error at the top of the page  too:

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn't match number of parameters in prepared statement in /var/www/kroweb.dk/public_html/gfdev/chris_table/data_SQL.php on line 39

I also need to understand the EOT statement with the SQL query, and the $stmt variable. If you could give a few words about those two for my understanding, I would be very happy (again :) )

HTML file:
<?php require_once('data_SQL.php'); ?>

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
        <meta name="viewport" content="width=device-width; initial-scale=1.0">
      <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
         <link rel="stylesheet" href="style_FtK_table.css">      
         <script src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
        <script type="text/javascript">
        $(document).ready(function() {
         $('#FtKTable').on('click', '.master', function() {
        $(this).nextAll('.child').toggle();
    });
});
</script>

        <title>Chris Stanyon // EE - 29074529</title>

        <!--<style type="text/css">
            td, th { padding: 10px; }
            .master { background-color: red; }
            .child { background-color: green; display: none;}
            .orphan { background-color: yellow; }
        </style>  -->  
    </head>
    <body>

        <form method="post">
    <input type="text" name="sogn" placeholder="Søg pr. Sogn">
    <button type="submit">Søg</button>
        </form>

        <table id="FtKTable" class="table">
   <thead>         
     <tr>

        <th>&nbsp;</th><th>Amt By</th> <th>Aar</th>

    </tr> 
    </thead>            

        <?php foreach ($data as $record): ?>

            <?php if ($record->type == RecordTypes::Master): // We have a parent record ?>

            <tbody>

                <tr class="<?php echo $record->type ?>">
                    <td>&nbsp;</td>
                    <th><?php echo $record->amtherredsogngade ?></th>
                    <th><?php echo $record->aar ?></th>
                </tr>

                <?php foreach ($record->children as $child): // Let's deal with the children! ?>

                    <tr class="<?php echo $child->type ?>">
                        <td>&nbsp;</td>
                        <td><?php echo $child->amtherredsogngade ?></td>
                        <td><?php echo $child->aar ?></td>
                    </tr>

                <?php endforeach; ?>
            </tbody>

            <?php else: // and finally the Orphaned records ?>

                <tr class="<?php echo $record->type ?>">
                    <td>&nbsp;</td>
                    <td><?php echo $record->amtherredsogngade ?></td>
                    <td><?php echo $record->aar ?></td>
                </tr>

            <?php endif; ?>

        <?php endforeach; ?>
        </table>

    </body>
</html>

Open in new window


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

db credentials

$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");


require_once('classes.php');

$data = array(); // initialise the data store

if (empty($_POST)) { return; } // let's quit if we have no search data


// Let's deal with our data
$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.AmtHerredSognGade AS pAmtHerredSognGade, p.Aar AS pAar, p.id AS pId,
    c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE c.Sogn = 'Haderslev'
EOT;

if ($stmt = $mysqli->prepare($sql)): // prepare the query

    $sogn = $_POST['sogn']; // get the search term from the form

    $stmt->bind_param('s', $sogn); // bind the search term to the prepared query
    $stmt->execute(); // and run the query

    $results = $stmt->get_result(); // get the results from the query

    while ($row = $results->fetch_object()): // Loop through the Db results

        if (!is_null($row->pId)): // We're dealing with parent/child records

             // set some unique array keys
            $parentIndex = "master:" . $row->pId; 
            $childIndex = "child:" . $row->cId;

            if (!array_key_exists($parentIndex, $data)): // do we create a new parent or grab an existing one
                $master = $data[$parentIndex] = new Master($row);
            else:
                $master = $data[$parentIndex];
            endif;

            $master->children[$childIndex] = new Child($row); // create a new child record

        else: // we're dealing with orphaned records

            $orphanIndex = "orphan:" . $row->cId;
            $data[$orphanIndex] = new Child($row); // create a new orphan record

        endif;

    endwhile;

endif;

//var_dump($data);

Open in new window


classes file
<?php
abstract class RecordTypes
{
    const Master = 'master';
    const Child = 'child';
    const Orphan = 'orphan';
}

class Master
{
    public $type;
    public $id;
    public $amtherredsogngade;
    public $sogn;
    public $aar;
    public $children;

    function __construct($row)
    {
        $this->type = RecordTypes::Master;
        $this->id = $row->pId;
        $this->amtherredsogngade = $row->pAmtHerredSognGade;
        $this->sogn = $row->pSogn;
        $this->aar = $row->pAar;
        $this->children = array();
    }
}

class Child
{
    public $type;
    public $id;
    public $amtherredsogngade;    
    public $sogn;
    public $aar;

    function __construct($row)
    {
        $this->type = (is_null($row->pId)) ? RecordTypes::Orphan : RecordTypes::Child;
        $this->id = $row->cId;
        $this->amtherredsogngade = $row->cAmtHerredSognGade;        
        $this->sogn = $row->cSogn;
        $this->aar = $row->cAar;
    }
}

//Select all
 

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
I have fixed the error - - it was in the query I had not changed the 'Haderslev' in the WHERE clause with ?. It is done now :)
0
Chris StanyonWebDevCommented:
Hey Peter,

Good stuff. The EOT is something called a HEREDOC in PHP and it's an easy way to build a string without having to concatenate it or worry about quotes etc.

Basically, this:

$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.AmtHerredSognGade AS pAmtHerredSognGade, p.Aar AS pAar, p.id AS pId,
    c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE c.Sogn = 'Haderslev'
EOT;

Is an easier way of creating the $sql string than something like this:

$sql = "SELECT ";
$sql .= "p.Sogn AS pSogn, p.AmtHerredSognGade AS pAmtHerredSognGade, p.Aar AS pAar, p.id AS pId, ";
$sql .= "c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId ";
$sql .= "FROM ft_parents AS p RIGHT JOIN ft_children as c ";
$sql .= "ON p.Child_id = c.Parent_id WHERE c.Sogn = 'Haderslev'";

Do not add the data.php file to the data logic file. Basically, when you come to creating the logic for the other table on your page, you will end up duplicating that data - not a good idea (and it would mean 2 different connections to the same DB). It's should be included as it was originally (require_once). You'll see why later on.

Also, in order to sort your links out, you'll need to add the URL column into the SQL. We can then easily create the link directly inside the Master and Child class. Add a property to each class called $link, and then in the ctor set the link based on whether or not the URL is empty:

$this->link = empty($row->cURL) ? $row->cAmtHerredSognGade : sprintf("<a href='%s' target='_blank'>%s</a>", $row->cURL, $row->cAmtHerredSognGade);

Open in new window

This will give your class a property called link which will be the value of AmtHerredSognGade if the URL is empty, otherwise it will be the proper link. Then in your HTML, you can just do:

<?php echo $child->link; ?>
or
<?php echo $record->link; ?>

You don't have to worry about the logic for this entering the HTML as that's already been taken care of in the class.

Now we're going to get to the main page ready for adding in the different tables, so create a new HTML page. This will ultimately be the page that people see when they visit your site. This is where we set up the structure of your page, load in all the CSS/ jQuery and then we include all the individual tables.

Again, bear with me on this and you will see where it's going very shortly.

The content of this page should be the following:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">

        <title>Chris Stanyon // EE - 29074529</title>

        <link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/css/bootstrap.min.css">
        <link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
        <link rel="stylesheet" type="text/css" href="style.css">

        <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js" ></script>
    </head>
    <body>

         <div class="container">
            <div class="row">
                <div class="col">

                    <div id="search" class="pb-3">
                        <form method="post" class="form-inline">
                            <input type="text" name="sogn" class="form-control mr-2 mb-2 mb-sm-0" placeholder="Søg pr. Sogn">
                            <button type="submit" class="btn btn-primary">Search</button>
                        </form>
                    </div>

                </div>
            </div>

            <div class="row">
                <div class="col">

                    <h2>Kirkebøger</h2>

                    <p>We'll get to this later</p>

                </div>

                <div class="col">

                    <h2>Folketællinger</h2>

                    <?php include_once('table1.ui.php'); ?>

                </div>

                <div class="col">

                    <h2>Skifter</h2>

                    <p>We'll get to this later</p>

                </div>
            </div>

        </div>

    </body>
</html>

Open in new window

If you load that up now, you should see a 3 column layout with a search form at the top. You are likely to get an error in column 2 as we're trying to load in our HTML for the table, but we've haven;t quite finished that yet.

Very nearly there.
0
Peter KromanSales ExecutiveAuthor Commented:
Right :)

I have split up the files again so that the data (call it data_connection for my own understanding) .
I have added the c.URL to the SQL (only needs links in the child section)
   c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId, c.URL AS cURL

Open in new window

I have set the link automatic in the child section of classes
class Child
{
    public $type;
    public $id;
    public $amtherredsogngade;    
    public $sogn;
    public $aar;
    public $link;

    function __construct($row)
    {
        $this->type = (is_null($row->pId)) ? RecordTypes::Orphan : RecordTypes::Child;
        $this->id = $row->cId;
        $this->amtherredsogngade = $row->cAmtHerredSognGade;        
        $this->sogn = $row->cSogn;
        $this->aar = $row->cAar;
        $this->link = empty($row->cURL) ? $row->cAmtHerredSognGade : sprintf("<a href='%s' target='_blank'>%s</a>", $row->cURL, $row->cAmtHerredSognGade);
    }
}

Open in new window

I have added this line in the child section of the HTML
 <td><?php echo $child->link; ?></td>

Open in new window


But links are not working .....
0
Peter KromanSales ExecutiveAuthor Commented:
Sorry - hang on a minute - i might be preparing a Christmas present to my self - one moment more :)
0
Peter KromanSales ExecutiveAuthor Commented:
It really IS Christmas :)

Take a look here:
http://kroweb.dk/gfdev/chris_table/sognefakta_K.php
0
Peter KromanSales ExecutiveAuthor Commented:
... but some of the CSS has gone missing :)
0
Peter KromanSales ExecutiveAuthor Commented:
CSS "problem" lies here:
<link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/css/bootstrap.min.css">

When I comment out that stylesheet my CSS is working nicely, but the columns is not working anymore :)
0
Chris StanyonWebDevCommented:
Hey Peter,

Looking OK but a couple of issues - firstly, don't include your own CSS yet - I've included the bootstrap framework, so unless you know how that works, don't worry about styling at the moment. The only thing you really need in your own stylesheet is:

.child { display: none;}

Also, you've jumped ahead again, so you've now included the entire HTML page that we've been working on in the main page. You only need the table code in there along with the jQuery script and the php include line. You should wrap it inside an if block to check whether you have records or not. You seem to be loading FontAwesome and jQuery twice! You've also now used the <p> tag for a header and the header tag for a paragraph, so your HTML is now looking a little odd!

And while you're editing the Table UI, don't forget to add in the toggle icon, using FontAwesome.

Enjoy your Christmas :)
0
Peter KromanSales ExecutiveAuthor Commented:
OK.
Hope you've had a nice christmas eve. I've surely eaten too much (as usual :) )
Well - I thought I've done what you asked.
I have now done this:
1. commented out my own CSS
2. adding .child { display: none;} to the main file
3. removed the parts I believe is not needed from the table file (the <p> and <h2> is how I want it to be :) )
4 put int the toggle icon, but it is not showing the right icon??
5. wrapping the table in an if condition

But we have three more issue that we have to deal with along the way.
1. The page we are creating now adresses Cities. Cities are labeled in the "Type" column in the db-tables as "Købstad". It is only those records we want to be displayed at this page.
2. There might be some Cities where there is not a parent involved, and it seems not possible to adresss such cities at this moment.
3. We need to include the "AA = Å". aa=å etc. in our query.
0
Peter KromanSales ExecutiveAuthor Commented:
I have tried to fox the issue 1. above, but returns very strange results.

I have edited the query like this:
$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.AmtHerredSognGade AS pAmtHerredSognGade, p.Aar AS pAar, p.id AS pId, p.Type AS pType,
    c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId, c.URL AS cURL, c.Type AS cType
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE c.Sogn = ? AND c.Type = "Købstad"
EOT;

Open in new window


And I have edited the classes like this - where I use mytype because you have used type for something else:
class Master
{
    public $type;
    public $id;
    public $amtherredsogngade;
    public $sogn;
    public $aar;
    public $mytype;
    public $children;

    function __construct($row)
    {
        $this->type = RecordTypes::Master;
        $this->id = $row->pId;
        $this->amtherredsogngade = $row->pAmtHerredSognGade;
        $this->sogn = $row->pSogn;
        $this->aar = $row->pAar;
        $this->mytype = $row->pType;
        $this->children = array();
    }
}

class Child
{
    public $type;
    public $id;
    public $amtherredsogngade;    
    public $sogn;
    public $aar;
    public $mytype;
    public $link;

    function __construct($row)
    {
        $this->type = (is_null($row->pId)) ? RecordTypes::Orphan : RecordTypes::Child;
        $this->id = $row->cId;
        $this->amtherredsogngade = $row->cAmtHerredSognGade;        
        $this->sogn = $row->cSogn;
        $this->aar = $row->cAar;
        $this->mytype = $row->cType;
        $this->link = empty($row->cURL) ? $row->cAmtHerredSognGade : sprintf("<a href='%s' target='_blank'>%s</a>", $row->cURL, $row->cAmtHerredSognGade);
    }
}

Open in new window


When you search Haderslev you get the 4 parents of Haderslev and their children can be toggled correctly. So far so good. But below that is displays Haderslev Åstrupvej 1835 twice. Haderslev Åstrupvej is present in the database once and that is in the year 1940 under which parent it also lies correctly.
On the other hand there is two Type "Købstad" records present within "Haderslev" sogn in 1835 which do not have a parent. Those are both called "Haderslev Haderslev Købstad Haderslev" in the AmtHerredSognGade field.

What am I doing wrong here?
0
Chris StanyonWebDevCommented:
Morning Peter,

OK. If you do a search and then look at the Source or your page you'll see that you still have some invalid HTML around the table. That HTML file (the UI) should just contain the table code and the <script> as it's only a partial.

You seem to have added in the code for the Icon, but just as the numeric code. You haven't actually wrapped it in anything so it won't show up correctly. You're using the bootstrap and font-swesome CSS, so you can just add it in like this:

<i class="fa fa-plus-circle text-primary" aria-hidden="true"></i>

And then skip the span.toggle from your CSS.

Your whole UI page should look something like this:

<?php require_once('table1.logic.php'); ?>

<?php if (count($data)): ?>

<table id="FtKTable" class="table table-sm">

    <thead class="thead-dark">
        <tr>
            <th>&nbsp;</th>
            <th>Amt By</th>
            <th>Aar</th>		  
        </tr>
    </thead>

    <?php foreach ($data as $record): ?>

        <?php if ($record->type == RecordTypes::Master): ?>

            <tbody>

                <tr class="<?php echo $record->type ?>">
                    <th><i class="fa fa-plus-circle text-primary" aria-hidden="true"></i></th>
                    <th><?php echo $record->amtherredsogngade ?></th>
                    <th><?php echo $record->aar ?></th>
                </tr>

                <?php foreach ($record->children as $record): ?>

                <tr class="<?php echo $record->type ?>">
                    <td>&nbsp;</td>
                    <td><?php echo $child->link ?></td>
                    <td><?php echo $child->aar ?></td>
                </tr>

                <?php endforeach; ?>

            </tbody>

        <?php else: ?>

            <tr class="<?php echo $record->type ?>">
                <td>&nbsp;</td>
                <td><?php echo $record->link ?></td>
                <td><?php echo $record->aar ?></td>
            </tr>

        <?php endif; ?>

    <?php endforeach; ?>

</table>

<script type="text/javascript">
$(document).ready(function() {
    $('#FtKTable').on('click', '.master', function() {
        $(this).nextAll('.child').toggle();
    });
});
</script>

<?php else: ?>

    <p>No records to show</p>

<?php endif; ?>

Open in new window

To address just the CIties, you look like you've got your query correct. You say there's no way to address the records that have no Parent, but we do. Because we're doing a Right Join, it will select records from the child table even if they don't have a parent - they become the non-toggle orphan records that we display.

With regards to the records that are being returned. I can't really help with that. The query is correct, so if those are the records that are displaying, then those are the records being returned from the database. I would go back to your Db editor and run the query directly again to see exactly what is being returned.  

It may help, while testing, if you output the ID of the record - this will then allow you to look at the record directly in the database. For example, in the HTML that I've just posted, add in the Child ID for the orphan record:

<?php else: ?>

            <tr class="<?php echo $record->type ?>">
                <td><?php echo $record->id ?></td>
                <td><?php echo $record->link ?></td>
                <td><?php echo $record->aar ?></td>
            </tr>

        <?php endif; ?>

Open in new window

Now you can see the ID of the child record and using your DB editor go directly to that record in the child table:

SELECT * FROM ft_children WHERE id = xxx;

Get all that working and then we can look at the city variations.
0
Peter KromanSales ExecutiveAuthor Commented:
He-He - I've found a fault in your code :)

This:
<?php foreach ($record->children as $record): ?>

                <tr class="<?php echo $record->type ?>">
                    <td>&nbsp;</td>
                    <td><?php echo $child->link ?></td>
                    <td><?php echo $child->aar ?></td>
                </tr>

                <?php endforeach; ?>

Open in new window


Have to be this to work:
<?php foreach ($record->children as $child): ?>

                <tr class="<?php echo $child->type ?>">
                    <td>&nbsp;</td>
                    <td><?php echo $child->link ?></td>
                    <td><?php echo $child->aar ?></td>
                </tr>

                <?php endforeach; ?>

Open in new window


I am working on with the rest of your input and will get back a little later.
0
Peter KromanSales ExecutiveAuthor Commented:
Yep - I have done it all and I have tested with the ID's too, and everything seems to be in perfect order now :)
0
Peter KromanSales ExecutiveAuthor Commented:
Well - I have just tested a couple od instances wtih the AA, aa included.

It seems to be working allready - I am just testing a little more and will get back
0
Chris StanyonWebDevCommented:
Nice one Peter. Great to see you're understanding it enough to debug it yourself. This is absolutely key to moving forward. It's very easy on EE to just post a copy/paste solution, but as the saying goes.

give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime

I prefer to teach a man to fish :)
0
Peter KromanSales ExecutiveAuthor Commented:
I believe I am going to eat a h... of a lot of fish in the future :)

I have not learned so much since high school (almost a lifetime ago).

About the AA, aa problems - it seems to be working when searching with AA/Å and aa/å, but not with the other letters OE/Ø, oe/ø, AE/Æ, ae/æ.
0
Chris StanyonWebDevCommented:
Hehe - fill your boots :)

Regarding the letter substitution. Probably the easiest thing to do is add the cityVariations function to the classes.php file, and then call that from your logic file. Don't forget to adjust your SQL. So in the classes.php file add this at the bottom:

function cityVariations($city) {
    $substitute = array(
        "æ" => "ae",
        "ø" => "oe",
        "å" => "aa",
        "ae" =>"æ",
        "oe" =>"ø",
        "aa" => "å",
        );

    $flatCity = mb_convert_case($city, MB_CASE_LOWER);

    $cities[] = $flatCity;
    $cities[] = strtr($flatCity, $substitute); 

    return $cities;
}

Open in new window

And then in your logic file, adjust your SQL:

$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.AmtHerredSognGade AS pAmtHerredSognGade, p.Aar AS pAar, p.id AS pId, p.Type AS pType,
    c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId, c.URL AS cURL, c.Type AS cType
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE (c.Sogn LIKE ? OR c.Sogn LIKE ?) AND c.Type = "Købstad"
EOT;

Open in new window

And then adjust the parameter bindings accordingly. Instead of this:

$sogn = $_POST['sogn']; // get the search term from the form

$stmt->bind_param('s', $sogn); // bind the search term to the prepared query
$stmt->execute(); // and run the query

Open in new window


You need this:

// get the search term variations
$parishes = cityVariations($_POST['sogn']); 
array_walk($parishes, function (&$sogn) { $sogn = $sogn . '%'; });

$stmt->bind_param('ss', $parishes[0], $parishes[1]); // bind the search terms to the prepared query
$stmt->execute(); // and run the query

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
One more question:

Why are we defining these as <th> - should in not be <tr>, or is there a reaoin it is <th>?
           <?php if ($record->type == RecordTypes::Master): // We have a parent record ?>

            <tbody>

                <tr class="<?php echo $record->type ?>">
                     <th><i class="fa fa-plus-circle text-primary" aria-hidden="true"></i></th>
                    <th><?php echo $record->amtherredsogngade ?></th>
                    <th><?php echo $record->aar ?></th>
                </tr>

Open in new window

0
Chris StanyonWebDevCommented:
A <th> is a header cell, whereas a <td> is a standard table cell. The only reason I defined them as a header, was to me it made more sense that the parent details acted like a header (by default, they're bold). It's your call, and if it makes more sense for them to be <td> then change them to your liking
0
Peter KromanSales ExecutiveAuthor Commented:
I get an error:
Warning: array_walk() expects parameter 1 to be array, null given in /var/www/kroweb.dk/public_html/gfdev/chris_table/data_SQL.php on line 24

where line 24 is this:
array_walk($cities, function (&$sogn) { $sogn = $sogn . '%'; });

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
My fault. I have fixed it :)
0
Peter KromanSales ExecutiveAuthor Commented:
OK. Now we need to get the first column "Kirkebøger" working. Can I just use the code I have from the earlier page here?

And we need to add my CSS to the page, and add odd/even colors to collapsed rows.

I have activated my CSS file again and for now I had commented out the bootstrap stylesheet so you can see how I'd like it to appear (imagine the columns :) )

Look here: http://kroweb.dk/gfdev/chris_table/sognefakta_K.php
0
Peter KromanSales ExecutiveAuthor Commented:
Have tried - can't just use the same code :)
0
Chris StanyonWebDevCommented:
OK Peter,

I wouldn't advise you just use the previous code. You now have a very clean setup, with a proper separation of concerns, so it makes perfect sense to continue on that path.

Before you do anything though, load up your page and take at look at the source code. You STILL have all that extra HTML in there at the bottom. You are also still loading the FontAwesome and jQuery library twice (look at the <head> element. You need to get these issue sorted otherwise you'll end up with invalid HTML, and things like jQuery or CSS won't work as expected.

Once you've got that sorted, create 2 new files - table2.logic.php and table2.ui.php (name them what you like). Then, in your main HTML file, just include the new table2.ui.php in the column that you need it in. Your main page then remains clean:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">

        <title>Chris Stanyon // EE - 29074529</title>

        <link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/css/bootstrap.min.css">
        <link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
        <link rel="stylesheet" type="text/css" href="style.css">

        <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js" ></script>
    </head>
    <body>

         <div class="container">
            <div class="row">
                <div class="col">

                    <div id="search" class="pb-3">
                        <form method="post" class="form-inline">
                            <input type="text" name="sogn" class="form-control mr-2 mb-2 mb-sm-0" placeholder="Søg pr. Sogn">
                            <button type="submit" class="btn btn-primary">Search</button>
                        </form>
                    </div>

                </div>
            </div>

            <div class="row">
                <div class="col">

                    <h2>Kirkebøger</h2>

                    <?php include_once('table2.ui.php'); ?>

                </div>

                <div class="col">

                    <h2>Folketællinger</h2>

                    <?php include_once('table1.ui.php'); ?>

                </div>

                <div class="col">

                    <h2>Skifter</h2>

                    <p>We'll get to this later</p>

                </div>
            </div>

        </div>

    </body>
</html>

Open in new window

With regard to removing the BootStrap CSS. Ultimately, that's entirely up to you, but I would suggest you leave it in - at least for the time being. BootStrap makes prototyping you app extemely easy. It also ensures that your page remain fully responsive for when it's viewed on Mobiles and Tables etc. It sets up all the columns nicely. You can override any of the styling with your own later on, but I would recommend you leave that until you have all the functionality working correctly.

Get those issues resolved and the new pages created, and I'll write up the code for the new table 2 logic.
0
Chris StanyonWebDevCommented:
OK. Firstly, we need to create a new Class to represent the objects that we're going to be working with. Not sure what your new table represents, so I'll leave the naming up to you. Here's what it should look like and it should be added to the classes.php table (Mine's called Town).

class Town
{
    public $link;

    function __construct()
    {
         $this->link = sprintf("<a href='%s' target='_blank'>%s</a>", $this->URL, $this->Kirkebog);
    }
}

Open in new window

You'll notice that this differs from the previous 2 classes slightly. We haven't created the properties ($Sogn, $Amt etc) explicity. For this class, we're going to create those properties dynamically straight from the database. We can get away with it this time because we don't need to distinguish between parent and child records - each record from the Db can be represented by the same class. We do however, set up the $link, so that we don't have to do it in the UI. Once you've got that sorted, add the following code to your table2.logic.php file:

<?php
require_once('classes.php');
require_once('data.php');

$data = array();

if (empty($_POST)) { return; }

$sql = <<<EOT
SELECT Sogn, Amt, URL, Kirkebog
FROM kirkebogssogne
WHERE (Sogn LIKE ? OR Sogn LIKE ?')
EOT;

if ($stmt = $mysqli->prepare($sql)):

    $terms = cityVariations($_POST['sogn']); 
    array_walk($terms, function (&$term) { $term = $term . '%'; });

    $stmt->bind_param('ss', $terms[0], $terms[1]);
    $stmt->execute();

    $results = $stmt->get_result();

    while ($row = $results->fetch_object('Town')): // loop through the Db results

        $data[] = $row;

    endwhile;

endif;

Open in new window

The code for this logic should look very familiar, with one exception. On line 25, we are now calling the fetch_object() method with a parameter of 'Town'. This means that for each record pulled out of the Database, it will create a new instance of the 'Town' class we've just created. It will automatically generate the properties based on the columns we've selected in the query (Sogn, Amt, URL, Kirkebog), plus the specific class property we created - $link.

Next up is the HTML for this logic - the table2.ui.php. Again, most of this should already look familiar:

<?php require_once('table2.logic.php') ?>

<?php if (count($data)): ?>

    <table id="KbTable" class="table table-sm">

        <thead>
            <tr>
                <th>Sogn</th>
                <th>Amt</th>
                <th>Kirkebog</th>             
            </tr>
        </thead>

        <?php foreach ($data as $record): ?>

            <tr>
                <td><?php echo $record->sogn ?></td>      
                <td><?php echo $record->amt ?></td>        
                <td><?php echo $record->link ?></td>
            </tr>

        <?php endforeach; ?>

    </table>

<?php else: ?>

    <p>Der er ingen resultater for denne søgning</p>

<?php endif; ?>

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,

I have made the "cleaning" job.
I have made a little bit of renaming of files and classes to make it better for me to work on with.
I have created the class and the fils to address the "Kirkebog" column, but I get a "No result" message.
I have looked the files and the code over and over, but I simply can't see where it is going wrong.

The page we are working on is now this:
http://kroweb.dk/gfdev/chris_table/sognefakta_ByLand.php

Files for the "Kirkebog" column:
classes:
<?php
abstract class RecordTypes
{
    const Master = 'ftMaster';
    const Child = 'ftChild';
    const Orphan = 'ftOrphan';
}

class KbSogn
{
    public $link;

    function __construct()
    {
         $this->link = sprintf("<a href='%s' target='_blank'>%s</a>", $this->URL, $this->Kirkebog);
    }
}

class ftMaster
{
    public $type;
    public $id;
    public $amtherredsogngade;
    public $sogn;
    public $aar;
    public $mytype;
    public $children;

    function __construct($row)
    {
        $this->type = RecordTypes::Master;
        $this->id = $row->pId;
        $this->amtherredsogngade = $row->pAmtHerredSognGade;
        $this->sogn = $row->pSogn;
        $this->aar = $row->pAar;
        $this->mytype = $row->pType;
        $this->children = array();
    }
}

class ftChild
{
    public $type;
    public $id;
    public $amtherredsogngade;    
    public $sogn;
    public $aar;
    public $mytype;
    public $link;

    function __construct($row)
    {
        $this->type = (is_null($row->pId)) ? RecordTypes::Orphan : RecordTypes::Child;
        $this->id = $row->cId;
        $this->amtherredsogngade = $row->cAmtHerredSognGade;        
        $this->sogn = $row->cSogn;
        $this->aar = $row->cAar;
        $this->mytype = $row->cType;
        $this->link = empty($row->cURL) ? $row->cAmtHerredSognGade : sprintf("<a href='%s' target='_blank'>%s</a>", $row->cURL, $row->cAmtHerredSognGade);
    }
}

function cityVariations($city) {
    $substitute = array(
        "æ" => "ae",
        "ø" => "oe",
        "å" => "aa",
        "ae" =>"æ",
        "oe" =>"ø",
        "aa" => "å",
        );

    $flatCity = mb_convert_case($city, MB_CASE_LOWER);

    $cities[] = $flatCity;
    $cities[] = strtr($flatCity, $substitute); 

    return $cities;
}
 

Open in new window


SQL logic file:
<?php
require_once('classes.php');
require_once('kb_data_connection.php');

$data = array();

if (empty($_POST)) { return; }

$sql = <<<EOT
SELECT Sogn, Amt, URL, Kirkebog
FROM kirkebogssogne
WHERE (Sogn LIKE ? OR Sogn LIKE ?')
EOT;

if ($stmt = $mysqli->prepare($sql)):

    $terms = cityVariations($_POST['sogn']); 
    array_walk($terms, function (&$term) { $term = $term . '%'; });

    $stmt->bind_param('ss', $terms[0], $terms[1]);
    $stmt->execute();

    $results = $stmt->get_result();

    while ($row = $results->fetch_object('KbSogn')): // loop through the Db results

        $data[] = $row;

    endwhile;

endif;

Open in new window


UI file:
<?php require_once('kb_data_SQL.php') ?>

<?php if (count($data)): ?>

    <table id="KbTable" class="table table-sm">

        <thead>
            <tr>
                <th>Sogn</th>
                <th>Amt</th>
                <th>Kirkebog</th>             
            </tr>
        </thead>

        <?php foreach ($data as $record): ?>

            <tr>
                <td><?php echo $record->sogn ?></td>      
                <td><?php echo $record->amt ?></td>        
                <td><?php echo $record->link ?></td>
            </tr>

        <?php endforeach; ?>

    </table>

<?php else: ?>

    <p>Der er ingen resultater for denne søgning</p>

<?php endif; ?>

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
I am really working on this, and I believe that I have made a little progress.
I have changed the $data variable to $data1 in the table UI file ad the SQL data file, like this
<?php require_once('kb_data_SQL.php') ?>

<?php if (count($data1)): ?>

    <table id="KbTable" class="table">

        <thead>
            <tr>
                <th>Sogn</th>
                <th>Amt</th>
                ...... 

Open in new window


And
<?php
require_once('classes.php');
require_once('kb_data_connection.php');

$data1 = array();

if (empty($_PO .....

Open in new window


Now I at least get en error message:
Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn't match number of parameters in prepared statement in /var/www/kroweb.dk/public_html/gfdev/chris_table/kb_data_SQL.php on line 20

where line 20 is this one:
   $stmt->bind_param('ss', $terms[0], $terms[1]);

Open in new window


I have tried to deal with that too - but here I think I must give in :)
0
Peter KromanSales ExecutiveAuthor Commented:
Wrong - the above did not have any effect at all. The effect was because of alle my editing.
I still get "No result" - and I am getting quite nuts because I can't find out why ....
0
Peter KromanSales ExecutiveAuthor Commented:
I will just post all the files here - and hope that you can se what I can't see

Main file.
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">

        <title>Sognefakta</title>

   
        <meta name="viewport" content="width=device-width; initial-scale=1.0">  
       <link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/css/bootstrap.min.css">
        <link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
        <link rel="stylesheet" type="text/css" href="style_ByLand.css">
        <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js" ></script>
        <style>
        .ftChild {display:none;}
        </style>
    </head>
    <body>

         <div class="container">
            <div class="row">
                <div class="col">

                    <div id="search" class="pb-3">
                        <form method="post" class="form-inline">
                            <input type="text" name="sogn" class="form-control mr-2 mb-2 mb-sm-0" placeholder="Søg pr. Sogn">
                            <button type="submit" class="btn btn-primary">Search</button>
                        </form>
                    </div>

                </div>
            </div>

            <div class="row">
                <div class="col">

                    <p>Kirkebøger</p>

                    <?php include_once('kb_table.php'); ?>


                </div>

                <div class="col">

                    <p>Folketællinger</p>

                    <?php include_once('ft_table.php'); ?>

                </div>

                <div class="col">

                    <p>Skifter</p>

                    <h6>We'll get to this later</h6>

                </div>
            </div>

        </div>

    </body>
</html>

Open in new window


kb_table.php
<?php require_once('kb_data_SQL.php') ?>

<?php if (count($data)): ?>

    <table id="KbTable" class="table">

        <thead>
            <tr>
                <th>Sogn</th>
                <th>Amt</th>
                <th>Kirkebog</th>             
            </tr>
        </thead>

        <?php foreach ($data as $record): ?>

            <tr>
                <td><?php echo $record->sogn ?></td>      
                <td><?php echo $record->amt ?></td>        
                <td><?php echo $record->link ?></td>
            </tr>

        <?php endforeach; ?>

    </table>

<?php else: ?>

    <h6>Der er ingen resultater for denne søgning</h6>

<?php endif; ?>

Open in new window


kb_data_SQL.php
<?php
require_once('classes.php');
require_once('kb_data_connection.php');

$data = array();

if (empty($_POST)) { return; }

$sql = <<<EOT
SELECT Sogn, Amt, URL, Kirkebog
FROM kirkebogssogne
WHERE (Sogn LIKE ? OR Sogn LIKE ?')
EOT;

if ($stmt = $mysqli->prepare($sql)):

    $terms = cityVariations($_POST['sogn']); 
    array_walk($terms, function (&$term) { $term = $term . '%'; });

    $stmt->bind_param('ss', $terms[0], $terms[1]);
    $stmt->execute();

    $results = $stmt->get_result();

    while ($row = $results->fetch_object('KbSogn')): // loop through the Db results

        $data[] = $row;

    endwhile;

endif;

Open in new window


classes.php
<?php
abstract class RecordTypes
{
    const ftMaster = 'ftMaster';
    const ftChild = 'ftChild';
    const ftOrphan = 'ftOrphan';

}

class KbSogn
{
    public $link;

    function __construct()
    {
         $this->link = sprintf("<a href='%s' target='_blank'>%s</a>", $this->URL, $this->Kirkebog);
    }
}

class ftMaster
{
    public $type;
    public $id;
    public $amtherredsogngade;
    public $sogn;
    public $aar;
    public $mytype;
    public $children;

    function __construct($row)
    {
        $this->type = RecordTypes::ftMaster;
        $this->id = $row->pId;
        $this->amtherredsogngade = $row->pAmtHerredSognGade;
        $this->sogn = $row->pSogn;
        $this->aar = $row->pAar;
        $this->mytype = $row->pType;
        $this->children = array();
    }
}

class ftChild
{
    public $type;
    public $id;
    public $amtherredsogngade;    
    public $sogn;
    public $aar;
    public $mytype;
    public $link;

    function __construct($row)
    {
        $this->type = (is_null($row->pId)) ? RecordTypes::ftOrphan : RecordTypes::ftChild;
        $this->id = $row->cId;
        $this->amtherredsogngade = $row->cAmtHerredSognGade;        
        $this->sogn = $row->cSogn;
        $this->aar = $row->cAar;
        $this->mytype = $row->cType;
        $this->link = empty($row->cURL) ? $row->cAmtHerredSognGade : sprintf("<a href='%s' target='_blank'>%s</a>", $row->cURL, $row->cAmtHerredSognGade);
    }
}

function cityVariations($city) {
    $substitute = array(
        "æ" => "ae",
        "ø" => "oe",
        "å" => "aa",
        "ae" =>"æ",
        "oe" =>"ø",
        "aa" => "å",
        );

    $flatCity = mb_convert_case($city, MB_CASE_LOWER);

    $cities[] = $flatCity;
    $cities[] = strtr($flatCity, $substitute); 

    return $cities;
}
 

Open in new window


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

$hostname = 'mysql31.unoeuro.com';
$username = 'myUsername';
$password = 'myPasword';
$database = 'genealogiskforum_dk_db9';

$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");

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Don't worry.

I have fixed it - removing a ' from the query and editing the classes file like this:
class KbSogn
{
    public $link;
    public $sogn;
    public $amt;


    function __construct()
    {
         $this->link = sprintf("<a href='%s' target='_blank'>%s</a>", $this->URL, $this->Kirkebog);
         $this->sogn = $this->Sogn;
         $this->amt = $this->Amt;

    }
}

Open in new window

0
Chris StanyonWebDevCommented:
Hey Peter,

Looking good. Nice to see you renaming and organising your files - makes like a lot easier.

The problem you're having is because of a slight typo in my code. If you look at the SQL statement, you will see an extraneous single quote at the end. That needs removing:


$sql = <<<EOT
SELECT Sogn, Amt, URL, Kirkebog
FROM kirkebogssogne
WHERE (Sogn LIKE ? OR Sogn LIKE ?') <-- remove this single quote !!!!
EOT;

Open in new window

To do things properly, we should really add in some error checking. When you call the prepare() method, it returns a statement object if it prepared successfully, otherwise it returns false. Currently, we don't do anything if it returns false, which is why you didn't get any errors. What we should really be doing is something like this:

if ($stmt = $mysqli->prepare($sql)):
    $terms = cityVariations($_POST['sogn']); 
    ...
else:
    // handle any errors generated by the prepare()
endif;

Open in new window

... but we can look at that later on.
0
Chris StanyonWebDevCommented:
haha - beat me to it by a minute :)
0
Peter KromanSales ExecutiveAuthor Commented:
Yeah :)

So I now need to set the odd/even diff on the toggle rows.

I thought it would be something like this:
         .ftChild.toggle tr.child { display: none; background-color: #f2f2f2;}
          .ftChild.toggle tr:nth-child(even){ display: none; background-color: lightgrey;}

Open in new window


but it is not working ...
0
Chris StanyonWebDevCommented:
Hey Peter,

The CSS you need is this:

#FtKTable tbody .ftChild:nth-child(odd) { background-color: #f2f2f2; }
#FtKTable tbody .ftChild:nth-child(even) { background-color: lightgrey; }

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Yes - that works - thanks.

I don't have data for the third column yet, so that will have to wait a bit - but I am sure that I am able to add this my self after all that I have learned here :)  

Now I will create one more page because Census for the Capitol is not organized in Parishes which Churchbooks are, so I need to handle the hits for Capitol parishes separately. I will go to work on that page now, and then I will get back here, when we are ready to make the final step to get these pages active through links from another (a third) page.

Anyway - as I have said earlier - I have learned an huge bunch of good stuff here, and I can't thank you enough for that :)
0
Chris StanyonWebDevCommented:
All good Peter.

Really pleased you feel like you're learning. That's a great reward for myself.

Hopefully, from what we've gone through, you can start to see the benefits of organising your application in a more structured way. I know you end up with more files, but each file is a lot smaller and more manageable. This leads to much a cleaner, re-usable and testable codebase.

Good luck with it all

Chris
0
Peter KromanSales ExecutiveAuthor Commented:
WIth all my new knowledge, and all the good new principals I have managed to build the extra page rather quickly :)

Well - so here is where we are.

We have this page: http://kroweb.dk/gfdev/canvas/.
When clicking a County selection on the map lists with shires and parishes in the county clicked are displayed, and the parishes holds links referring to the two next pages:

http://kroweb.dk/gfdev/canvas/sognefakta_Hovedstaden.php, which is attached to all the parishes in the Capitol
http://kroweb.dk/gfdev/canvas/sognefakta_ByLand.php, which is attached to all other parishes in the Country
These two pages has - at this moment - as we know, a search field shown at the top.

What I wish to do now is this:
When clicking a parish link on the map page it should perform the following with the page it loads:
1. The search string in the searchfield on the opened page should be the parish name we are clicking on in the map
2. The search should be fired so the results are shown at he opened page
3. The search field on the opened page should be hidden (if it is not possible to hide it permantly?)

I don't know if this is possible, but that's what I hope for :)
0
Chris StanyonWebDevCommented:
Hey Peter,

You have a couple of options really. Your sognefakta_ByLand.php page is built to respond to the search form being being filled in and submitted. All this search form really does is submit a POST request to itself. You can make a POST request to that page from any other page, so maybe the easiest thing to do is to make a POST request directly from your canvas page.

One option is to add a hidden form to your canvas page:

<form id="search" style="display:none" method="post">
    <input type="text" name="sogn" id="sogn">
</form>

Open in new window


And then bind a simple jQuery event to the click of each link:

$('#herredssogne_list').on('click', '.countSogn a', function(e) {
    e.preventDefault();
    $('#sogn').val($(this).text());
    $('#search').attr('action', $(this).attr('href')).trigger('submit');
});

Open in new window

All this does is set the action of the hidden form to the URL of the link, set the sogn text box to the text from the link and then submit the hidden form to the sognefakta_ByLand.php page. That page will then behave as if you filled in the search form directly.
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,

That sounds easy :)

I have made the search field on the canvas page (and made it visible for the moment  just to see what happens).
When clicking a link this searchfield is filled - but then something goes wrong.
It opens the sognefakta_ByLand page but the page is empty, so no query seem to have been fired. At the same time the page is opening in the same window as the canvas page, and I have initially set it to open in a new window.

I have added this to the top of the canvas page
<form id="search" style:"display" method="post">
    <input type="text" name="sogn" id="sogn">
</form>

Open in new window


and this at the end of the same page
<script>
$('#herredssogne_list').on('click', '.countSogn a', function(e) {
    e.preventDefault();
    $('#sogn').val($(this).text());
    $('#search').attr('action', $(this).attr('href')).trigger('submit');
});
</script>
</body>   
</html>

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
.. forgot to mention, that I also have commented out the searchfield on the sognefakta_ByLand page so that we don't have two searchfelds named "sogn" :)
0
Chris StanyonWebDevCommented:
Hey Peter,

Looks like the links on your Canvas page all have a space in front of them, so when they get submitted to the search form, it then tries to query the database for a value that include this space, and it obviously can't find the record. In your ByLand page, you probably need to call trim on the POST['sogn'] value before doing the query (or sort out the canvas page so they don't have a space in)

$terms = cityVariations(trim($_POST['sogn']));

Also, fix your search form. You're style attribute is wrong (you have a colon instead of equals):

<form id="search" style:"display" method="post">

should be

<form id="search" style="display:none" method="post">
or
<form id="search" style="display:block" method="post">
0
Chris StanyonWebDevCommented:
You don't really need to comment out the search form on the ByLand page unless you don't need it.

Although you have 2 search fields called 'sogn' this is actually necessary because the code needs the fields to be called 'sogn'. You're never actually submitting both at the same time so it doesn't matter. By removing it from the page, you then can't use that page to do the search like we have been doing.
0
Peter KromanSales ExecutiveAuthor Commented:
Super - but a little strange anyway. There don't seem to be any spaces in the links in the database, but the trim did the trick anyway :)
How do I get it to open the page in a new window in stead at the same window as the canvas file?
0
Chris StanyonWebDevCommented:
Hey Peter,

I don't think the spaces are coming from the DB. I'm guessing they're coming from the HTML that you build in your AJAX request : ajaxSognHerredHtml.php

Take a look there and check the HTML you're generating.

Anyway, to get the form to open in a new window, just add target="_blank" to the form:

<form id="search" style="display:none" target="_blank" method="post">
    <input type="text" name="sogn" id="sogn">
</form>

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Yeah ...

It is working so beautiful now :)

Last thing in this very long thread (I think :) )

I am so proud of all my new knowlwdge, so I couldn’t help giving it a try on my ”big” census page.

I believe that I in fact am very close to succeed. It seems to run the whole process very nicely and wothout any errors. Only problem is that I get a ”No result” message for all searches.

I have only put in four queries at this moment  (have appr. 15 more to add), and I am not able to see where I am missing something in the setup.

Try just to put in ”Haderslev” in the searchfield and search for that. You can then try to add the Amt ”Haderslev”, and add the Type ”Købstad”, and add the År ”1860” – those are the four query options that I have in right now.

The page I am working on is this: http://kroweb.dk/gfdev/ft_raw3/

I have this classes file:
<?php
abstract class RecordTypes
{
    const ftMaster = 'ftMaster';
    const ftChild = 'ftChild';
    const ftOrphan = 'ftOrphan';

}

  

class ftMaster
{
    public $type;
    public $id;
    public $amtherredsogngade;
    public $sogn;
    public $aar;
    public $mytype;
    public $children;

    function __construct($row)
    {
        $this->type = RecordTypes::ftMaster;
        $this->id = $row->pId;
        $this->amtherredsogngade = $row->pAmtHerredSognGade;
        $this->sogn = $row->pSogn;
        $this->aar = $row->pAar;
        $this->mytype = $row->pType;
        $this->children = array();
    }
}

class ftChild
{
    public $type;
    public $id;
    public $amtherredsogngade;    
    public $sogn;
    public $amt;
    public $aar;
    public $mytype;
    public $kvarter;
    public $gade;
    public $matr;
    public $link;

    function __construct($row)
    {
        $this->type = (is_null($row->pId)) ? RecordTypes::ftOrphan : RecordTypes::ftChild;
        $this->id = $row->cId;
        $this->amtherredsogngade = $row->cAmtHerredSognGade;        
        $this->sogn = $row->cSogn;
        $this->amt = $row->cAmt;
        $this->aar = $row->cAar;
        $this->mytype = $row->cType;
        $this->kvarter = $row->cKvarter;
        $this->gade = $row->cGade;
        $this->matr = $row->cMatr;
        $this->link = empty($row->cURL) ? $row->cAmtHerredSognGade : sprintf("<a href='%s' target='_blank'>%s</a>", $row->cURL, $row->cAmtHerredSognGade);
    }
}

function cityVariations($city) {
    $substitute = array(
        "æ" => "ae",
        "ø" => "oe",
        "å" => "aa",
        "ae" =>"æ",
        "oe" =>"ø",
        "aa" => "å",
        );

    $flatCity = mb_convert_case($city, MB_CASE_LOWER);

    $cities[] = $flatCity;
    $cities[] = strtr($flatCity, $substitute); 

    return $cities;
}
 

Open in new window

I have this table file:
<?php require_once('ft_data_SQL.php'); ?>



<?php if (count($data)): ?>

    <table id="FtKTable" class="table">
     <thead >         
       <tr>

        <tr>
            <!-- <colgroup> </colgroup> -->
            <?php if($mytype == "Hovedstaden" && $start == "1860") :?>
            <th class="&bsp;" ></th>
            <th class="sorting" aria-controls="FtKTable">Amt By Gade</th>
            <th class="sorting" aria-controls="FtKTable">Amt</th>
            <th class="sorting" aria-controls="FtKTable">Aar</th>
            <th class="sorting" aria-controls="FtKTable">Type</th>            
            <th class="sorting" aria-controls="FtKTable">Gade (Kbh. 1860)</th>
            <th class="sorting" aria-controls="FtKTable">Matr. Nr. (Kbh 1860)</th>        


        <?php elseif($mytype == "Landdistrikt"): ?>
        <th class="&bsp;" ></th>
        <th class="sorting" aria-controls="FtKTable">Amt Herred Sogn</th>
        <th class="sorting" aria-controls="FtKTable">Amt</th>
        <th class="sorting" aria-controls="FtKTable">Aar</th>
        <th class="sorting" aria-controls="FtKTable">Type</th> 

    <?php else: ?> 

    <?php if($mytype ==""): ?>
    <th class="&bsp;" ></th>
    <th class="sorting" aria-controls="FtKTable">Landdsitr: Amt Herred sogn <br> Byer: Amt By Gade (eller andet)</th>
    <th class="sorting" aria-controls="FtKTable">Amt</th>
    <th class="sorting" aria-controls="FtKTable">Aar</th>
    <th class="sorting" aria-controls="FtKTable">Type</th>  

<?php else: ?>
   <th class="&bsp;" ></th>
   <th class="sorting" aria-controls="FtKTable">Amt By Gade (eller andet)</th>
   <th class="sorting" aria-controls="FtKTable">Amt</th>
   <th class="sorting" aria-controls="FtKTable">Aar</th>
   <th class="sorting" aria-controls="FtKTable">Type</th> 

<?php endif; ?>  
<?php endif; ?>     
</tr>



</tr> 
</thead>            

<?php foreach ($data as $record): ?>

    <?php if ($record->type == RecordTypes::ftMaster): // We have a parent record ?>

    <tbody>

        <tr class="<?php echo $record->type ?>">
           <td><i class="fa fa-plus-circle text-primary" aria-hidden="true" style="color: cornflowerblue; font-size: 16px; "></i></td>
           <td><?php echo $record->amtherredsogngade ?></td>
           <td><?php echo $record->amt ?></td>
           <td><?php echo $record->aar ?></td>
           <td><?php echo $record->mytype ?></td>
           <td><?php echo $record->gade ?></td>
           <td><?php echo $record->kvarter ?></td>
       </tr>

       <?php foreach ($record->children as $child): // Let's deal with the children! ?>

       <tr class="<?php echo $child->type ?>">
        <td>&nbsp;</td>
        <td><?php echo $child->link ?></td>
        <td><?php echo $child->amt ?></td>
        <td><?php echo $child->aar ?></td>
        <td><?php echo $child->mytype ?></td>
        <td><?php echo $child->gade ?></td>
        <td><?php echo $child->kvarter ?></td>

    </tr>

<?php endforeach; ?>
</tbody>

<?php else: // and finally the Orphaned records ?>

    <tr class="<?php echo $record->type ?>">
        <td>&nbsp;</td>
        <!-- <td><?php echo $record->id ?></td> -->
        <td><?php echo $record->link ?></td>
        <td><?php echo $record->amt ?></td>
        <td><?php echo $record->aar ?></td>
        <td><?php echo $record->mytype ?></td>
        <td><?php echo $record->gade ?></td>
        <td><?php echo $record->kvarter ?></td>
    </tr>

<?php endif; ?>

<?php endforeach; ?>
</table>

<script type="text/javascript">
$(document).ready(function() {
    $('#FtKTable').on('click', '.ftMaster', function() {
        $(this).nextAll('.ftChild').toggle();
    });
});
</script>

<?php else: ?>

    <h6>Ingen resultater for denne søgning</h6>

<?php endif; ?>           

Open in new window

I have this SQL file:
<?php 

require_once('classes.php');
require_once('ft_data_connection.php');

$data = array(); // initialise the data store


if (empty($_POST)) { return; }// let's quit if we have no search data

// Trim the input values
$start = trim($_POST['query_start']); 
$sogn = trim($_POST['sogn']);
$amt = trim($_POST['amt']);
$mytype = trim($_POST['mytype']);



        // Get the city variations
$cities = cityVariations($sogn);

        // Prepare the cities for the LIKE clause by wrapping each in %...%
array_walk($cities, function (&$city) { $city = '%'.$city.'%'; });

// Let's deal with our data
if (!empty($amt) && !empty($sogn) && empty($mytype) && empty($start)):
                // We have a 'amt - 'sogn' value
$sql = <<<EOT
SELECT
    p.* AS p*, c.* AS c*
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE Amt = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? ) ORDER BY Type ASC, Aar ASC, Amt ASC
EOT;
if ($stmt = $mysqli->prepare($sql)): // prepare the query
$stmt->bind_param("sssss",$amt, $cities[0], $cities[1], $cities[0], $cities[1]); // bind the search terms to the prepared query

elseif (!empty($amt) && !empty($start) && !empty($sogn) && empty($mytype)):
                // We have a 'amt' - 'aar' - sogn' value
$sql = <<<EOT
SELECT
    p.* AS p*, c.* AS c*
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE Amt = ? AND Aar = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? ) ORDER BY Type ASC, Aar ASC, Amt ASC
EOT;
if ($stmt = $mysqli->prepare($sql)): // prepare the query
$stmt->bind_param("ssssss", $amt, $start, $cities[0], $cities[1], $cities[0], $cities[1]); 

else:   

    if (!empty($amt) && !empty($type) && !empty($start) && !empty($sogn)):
        //We have 'amt' - 'type' - 'aar' - 'sted' value
$sql = <<<EOT
SELECT
    p.* AS p*, c.* AS c*
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE Amt = ? AND Type= ? Aar = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? ) ORDER BY Type ASC, Aar ASC, Amt ASC
EOT;
if ($stmt = $mysqli->prepare($sql)): // prepare the query
$stmt->bind_param("sssssss", $amt, $type, $start, $cities[0], $cities[1], $cities[0], $cities[1]); 


elseif (!empty($sogn) && empty($start) && empty($type) && empty($amt)):
            // We have a sogn' value
$sql = <<<EOT
SELECT
    p.* AS p*, c.* AS c*
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? ) ORDER BY Type ASC, Aar ASC, Amt ASC
EOT;
if ($stmt = $mysqli->prepare($sql)): // prepare the query
$stmt->bind_param("ssss", $cities[0], $cities[1], $cities[0], $cities[1]); 


$stmt->execute(); // run the query




    $results = $stmt->get_result(); // get the results from the query

    while ($row = $results->fetch_object()): // Loop through the Db results

        if (!is_null($row->pId)): // We're dealing with parent/child records

             // set some unique array keys
            $parentIndex = "ftMaster:" . $row->pId; 
            $childIndex = "ftChild:" . $row->cId;

            if (!array_key_exists($parentIndex, $data)): // do we create a new parent or grab an existing one
                $ftMaster = $data[$parentIndex] = new ftMaster($row);
            else:
                $ftMaster = $data[$parentIndex];
            endif;

            $ftMaster->children[$childIndex] = new ftChild($row); // create a new child record

        else: // we're dealing with orphaned records

            $orphanIndex = "ftOrphan:" . $row->cId;
            $data[$orphanIndex] = new ftChild($row); // create a new orphan record

        endif;

    endwhile;

endif;
endif;
endif;
endif;
endif;
endif;
//var_dump($data);

Open in new window

I have this main file:
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Folketællinger</title>

 

<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">

<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">

<link rel="stylesheet" type="text/css" href="style.css">



    <script src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
   

   <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.css" />

  <script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.js"></script>

  <script type="text/javascript" src="jquery.dataTables.js"></script>
 
  <script type="text/javascript">
    $(function(){
        $('#ft_items').on('click', '.toggle', function() {
            $(this).parents('tr').nextAll('tr.child').toggle();
        });
    });
    </script>


</head>

<body>
    <!-- Søge felt til folketællinger -->
    <div class="container" style="width:100%;padding-top:10px;background-color:#f6f6f6;">
        <!-- HEADER START -->
        <div class="row" id="header">


            <form method="POST" id="search" style="margin-left:30px; margin-right: 30px;"> 
                <label for="amt">Amt</label> 
                <select id="amt" name="amt" style="width: 125px; margin-right: 20px; font-weight:normal; ">
                    <option value=""></option>
                    <option value="Bornholm">Bornholm</option>
                    <option value="Frederiksborg">Frederiksborg</option>
                    <option value="Færøerne">Færøerne</option>
                    <option value="Grønland">Grønland</option>
                    <option value="Haderslev">Haderslev</option>
                    <option value="Hjørring">Hjørring</option>
                    <option value="Holbæk">Holbæk</option>
                    <option value="København">København</option>
                    <option value="Køøge">Køge</option>               
                    <option value="Maribo">Maribo</option>
                    <option value="Odense">Odense</option>
                    <option value="Præstø">Præstø</option>
                    <option value="Randers">Randers</option>
                    <option value="Ribe">Ribe</option>
                    <option value="Ringkøbing">Ringkøbing</option>
                    <option value="Roskilde">Roskilde</option>
                    <option value="Skanderborg">Skanderborg</option>
                    <option value="Sorø">Sorø</option>
                    <option value="Svendborg">Svendborg</option>
                    <option value="Sønderborg">Sønderborg</option>
                    <option value="Thisted">Thisted</option>
                    <option value="Tønder">Tønder</option> 
                    <option value="Vejle">Vejle</option>
                    <option value="Viborg">Viborg</option>
                    <option value="Aabenraa">Aabenraa</option>
                    <option value="Aalborg">Aalborg</option>
                    <option value="Aarhus">Aarhus</option>
                    <option value="Åbenrå-Sønderborg">Åbenrå-Sønderborg</option>
                </select>

                <label for="type">Type</label>
                <select id="mytype" name="mytype"style="width: 140px; margin-right: 20px; font-weight:normal;">
                    <option value=""></option>
                    <option value="Landdistrikt">Søg i Landdistrikter</option>
                    <option value="Købstad">Søg i Købstæder</option>
                    <option value="Hovedstaden">Søg i Hovedstaden</option>
                    <option value="Slesvig">Søg i Selsvig</option>
                    <option value="Særtælling">Søg i Særtællinger (alle Typer)</option>
                    <option value="Diverse">Søg i Diverse</option>
                </select>


                <label for="aar">År</label>                
                <select id="aar" name="query_start" style=" width: 100px; margin-right: 20px; font-weight:normal; ">
                    <option value=""></option>
     
                    <option value="1787"style=" color: black; ">1787</option> 
                    <option value="1801"style=" color: black; ">1801</option> 
                    <option value="1834"style=" color: black; ">1834</option>
                    <option value="1835"style=" color: black; ">1835</option>
                    <option value="1840"style=" color: black; ">1840</option>
                    <option value="1845"style=" color: black; ">1845</option>
                    <option value="1850"style=" color: black; ">1850</option>
                    <option value="1855"style=" color: black; ">1855</option>
                    <option value="1860"style=" color: black; ">1860</option>  
                    <option value="1870"style=" color: black; ">1870</option>
                    <option value="1880"style=" color: black; ">1880</option> 
                    <option value="1890"style=" color: black; ">1890</option> 
                    <option value="1901"style=" color: black; ">1901</option>
                    <option value="1906"style=" color: black; ">1906</option>
                    <option value="1911"style=" color: black; ">1911</option>
                    <option value="1916"style=" color: black; ">1916</option>
                    <option value="1921"style=" color: black; ">1921</option>
                    <option value="1925"style=" color: black; ">1925</option>
                    <option value="1930"style=" color: black; ">1930</option>
                    <option value="1940"style=" color: black; ">1940</option>                             
                    <option value="1645"style="color:red;">1645</option>
                    <option value="1664-1833"style="color:red;">1664-1833</option>
                    <option value="1702-1741"style="color:red;">1702-1741</option>
                    <option value="1709-1831"style="color:red;">1709-1831</option>
                    <option value="1717"style="color:red;">1717</option>
                    <option value="1721-1833"style="color:red;">1721-1833</option>
                    <option value="1728"style="color:red;">1728</option>
                    <option value="1731"style="color:red;">1731</option>
                    <option value="1732-1761"style="color:red;">1732-1761</option>
                    <option value="1764-1819"style="color:red;">1764-1819</option>
                    <option value="1764-1848"style="color:red;">1764-1848</option>
                    <option value="1769"style="color:red;">1769</option>
                    <option value="1772-1846"style="color:red;">1772-1846</option>
                    <option value="1773-1846"style="color:red;">1773-1846</option>
                    <option value="1787-1851"style="color:red;">1787-1851</option>
                    <option value="1790"style="color:red;">1790</option>
                    <option value="1803"style="color:red;">1803</option>
                    <option value="1805"style="color:red;">1805</option>
                    <option value="1805-1814"style="color:red;">1805-1814</option>
                    <option value="1810"style="color:red;">1810</option>
                    <option value="1811"style="color:red;">1811</option>
                    <option value="1812-1823"style="color:red;">1812-1823</option>
                    <option value="1812-1871"style="color:red;">1812-1871</option>
                    <option value="1814"style="color:red;">1814</option>
                    <option value="1814-1841"style="color:red;">1814-1841</option>
                    <option value="1815-1867"style="color:red;">1815-1867</option>
                    <option value="1818"style="color:red;">1818</option>
                    <option value="1820"style="color:red;">1820</option>
                    <option value="1822"style="color:red;">1822</option>
                    <option value="1823"style="color:red;">1823</option>
                    <option value="1824"style="color:red;">1824</option>
                    <option value="1826"style="color:red;">1826</option>
                    <option value="1831"style="color:red;">1831</option>
                    <option value="1861"style="color:red;">1861</option>                  
                    <option value="1870-1873"style="color:red;">1870-1873</option>
                    <option value="1878"style="color:red;">1878</option>                  
                    <option value="1882"style="color:red;">1882</option>
                    <option value="1882-1919"style="color:red;">1882-1919</option>
                    <option value="1886-1907"style="color:red;">1886-1907</option>
                    
                    
                    
                </select>

                <label for="sogn">Sted, By, Sogn, Gade</label>
                <input type="text" name="sogn" id="sogn" placeholder="Fritekst" style="color: #e08a94;">
                <input type="submit" value="Søg" name="Sog" id="Sog">  
                <input type="reset" value="Nulstil">
                <script>
                $(':reset', '#search').click(function(e) {
                    $('#ft_items').html('');
                    location.reload();
                });
                </script>

            </form>
        </div>  
        <!-- HEADER END -->        
    </div>   


    <div id="ft_items" </div>
  


    <script>

      // Search Button and Return key search function
      $('#search').submit(function(e) {
        e.preventDefault();
        $.ajax({
            url : 'ft_table.php',
            method: 'post',
            data : $('#search').serialize(),
            dataType: 'html'
        }).done(function(res){
            $('#ft_items').html(res);
            $('#FtKtable').dataTable({searching: true, paging: false, info: false, "dom": '<"top"<if>', order: []}); 
        });
    });


      </script>


<script>
$(function() {
  $('#amt').change(function() {
     $.post('limit.php',{amt: $(this).val()}, function(options) {
        $('#aar').html(options)
     }) 
  });
  $('#mytype').change(function() {
     $.post('limit.php',{amt: $('#amt').val(), type: $(this).val()}, function(options) {
        $('#aar').html(options)
     })
  }); 

})
</script>      

  </body>
  </html>

Open in new window

0
Chris StanyonWebDevCommented:
Hey Peter,

That's quite a lot to be going through and I think we're probably in the realms of a new question.

Before asking a new question though, I would tidy things up a bit. In your Table file, each of your rows have 7 <td> elements. You need to make sure your <thead> has 7 as well. Your if block is giving the <thead> 7 in one instance and 5 in the other 3. You should always have the correct amount, even if you have to colspan them so they add up to 7. You also have 2 <tr> elements inside each other, and you seem to giving the first column a class of &bsp; which is wrong.

One other point to note. When you're coding, try and keep the spacing and indentation consistent, so your files are easy to read at a glance. This is what your <thead> should look like:

<table id="FtKTable" class="table">
    <thead>
        <tr>

        <?php if(empty($mytype)): // we don't have a $mytype ?>

            <th>&nbsp;</th>
            <th class="sorting" aria-controls="FtKTable">Landdsitr: Amt Herred sogn <br> Byer: Amt By Gade (eller andet)</th>
            <th class="sorting" aria-controls="FtKTable">Amt</th>
            <th class="sorting" aria-controls="FtKTable">Aar</th>
            <th class="sorting" aria-controls="FtKTable">Type</th>
            <th colspan="2">&nbsp;</th>

        <?php else: // we have a $mytype so let's deal with it ?>

            <?php if ($mytype == "Hovedstaden" && $start == "1860"): ?>

                <th>&nbsp;</th>
                <th class="sorting" aria-controls="FtKTable">Amt By Gade</th>
                <th class="sorting" aria-controls="FtKTable">Amt</th>
                <th class="sorting" aria-controls="FtKTable">Aar</th>
                <th class="sorting" aria-controls="FtKTable">Type</th>
                <th class="sorting" aria-controls="FtKTable">Gade (Kbh. 1860)</th>
                <th class="sorting" aria-controls="FtKTable">Matr. Nr. (Kbh 1860)</th>

            <?php elseif ($mytype == "Landdistrikt"): ?>

                <th>&nbsp;</th>
                <th class="sorting" aria-controls="FtKTable">Amt Herred Sogn</th>
                <th class="sorting" aria-controls="FtKTable">Amt</th>
                <th class="sorting" aria-controls="FtKTable">Aar</th>
                <th class="sorting" aria-controls="FtKTable">Type</th>
                <th colspan="2">&nbsp;</th>

            <?php else: ?>

                <th>&nbsp;</th>
                <th class="sorting" aria-controls="FtKTable">Amt By Gade (eller andet)</th>
                <th class="sorting" aria-controls="FtKTable">Amt</th>
                <th class="sorting" aria-controls="FtKTable">Aar</th>
                <th class="sorting" aria-controls="FtKTable">Type</th>
                <th colspan="2">&nbsp;</th>
 
            <?php endif;

        <?php endif; ?>

        </tr>
    </thead>

Open in new window

Your main file could do with quite a bit of tidying up as well. Load it up and have a look at the source - anything in red is a problem. Try to keep everything tidy and it will make coding a lot easier:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Folketællinger</title>

    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
    <link rel="stylesheet" type="text/css" href="style.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.css" />

    <script src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.js"></script>
    <script type="text/javascript" src="jquery.dataTables.js"></script>
</head>
<body>
    <!-- Søge felt til folketællinger -->
    <div class="container" style="width:100%;padding-top:10px;background-color:#f6f6f6;">

        <!-- HEADER START -->
        <div class="row" id="header">

            <form method="POST" id="search" style="margin-left:30px; margin-right: 30px;"> 

                <label for="amt">Amt</label> 
                <select id="amt" name="amt" style="width: 125px; margin-right: 20px; font-weight:normal;">
                    <option value=""></option>
                    <option value="Bornholm">Bornholm</option>
                    <option value="Frederiksborg">Frederiksborg</option>
                    <option value="Færøerne">Færøerne</option>
                    <option value="Grønland">Grønland</option>
                    <option value="Haderslev">Haderslev</option>
                    <option value="Hjørring">Hjørring</option>
                    <option value="Holbæk">Holbæk</option>
                    <option value="København">København</option>
                    <option value="Køøge">Køge</option>               
                    <option value="Maribo">Maribo</option>
                    <option value="Odense">Odense</option>
                    <option value="Præstø">Præstø</option>
                    <option value="Randers">Randers</option>
                    <option value="Ribe">Ribe</option>
                    <option value="Ringkøbing">Ringkøbing</option>
                    <option value="Roskilde">Roskilde</option>
                    <option value="Skanderborg">Skanderborg</option>
                    <option value="Sorø">Sorø</option>
                    <option value="Svendborg">Svendborg</option>
                    <option value="Sønderborg">Sønderborg</option>
                    <option value="Thisted">Thisted</option>
                    <option value="Tønder">Tønder</option> 
                    <option value="Vejle">Vejle</option>
                    <option value="Viborg">Viborg</option>
                    <option value="Aabenraa">Aabenraa</option>
                    <option value="Aalborg">Aalborg</option>
                    <option value="Aarhus">Aarhus</option>
                    <option value="Åbenrå-Sønderborg">Åbenrå-Sønderborg</option>
                </select>

                <label for="type">Type</label>
                <select id="mytype" name="mytype" style="width: 140px; margin-right: 20px; font-weight:normal;">
                    <option value=""></option>
                    <option value="Landdistrikt">Søg i Landdistrikter</option>
                    <option value="Købstad">Søg i Købstæder</option>
                    <option value="Hovedstaden">Søg i Hovedstaden</option>
                    <option value="Slesvig">Søg i Selsvig</option>
                    <option value="Særtælling">Søg i Særtællinger (alle Typer)</option>
                    <option value="Diverse">Søg i Diverse</option>
                </select>

                <label for="aar">År</label>                
                <select id="aar" name="query_start" style=" width: 100px; margin-right: 20px; font-weight:normal; ">
                    <option value=""></option>
                    <option value="1787" style=" color: black; ">1787</option> 
                    <option value="1801" style=" color: black; ">1801</option> 
                    <option value="1834" style=" color: black; ">1834</option>
                    <option value="1835" style=" color: black; ">1835</option>
                    <option value="1840" style=" color: black; ">1840</option>
                    <option value="1845" style=" color: black; ">1845</option>
                    <option value="1850" style=" color: black; ">1850</option>
                    <option value="1855" style=" color: black; ">1855</option>
                    <option value="1860" style=" color: black; ">1860</option>  
                    <option value="1870" style=" color: black; ">1870</option>
                    <option value="1880" style=" color: black; ">1880</option> 
                    <option value="1890" style=" color: black; ">1890</option> 
                    <option value="1901" style=" color: black; ">1901</option>
                    <option value="1906" style=" color: black; ">1906</option>
                    <option value="1911" style=" color: black; ">1911</option>
                    <option value="1916" style=" color: black; ">1916</option>
                    <option value="1921" style=" color: black; ">1921</option>
                    <option value="1925" style=" color: black; ">1925</option>
                    <option value="1930" style=" color: black; ">1930</option>
                    <option value="1940" style=" color: black; ">1940</option>                             
                    <option value="1645" style="color:red;">1645</option>
                    <option value="1664-1833" style="color:red;">1664-1833</option>
                    <option value="1702-1741" style="color:red;">1702-1741</option>
                    <option value="1709-1831" style="color:red;">1709-1831</option>
                    <option value="1717" style="color:red;">1717</option>
                    <option value="1721-1833" style="color:red;">1721-1833</option>
                    <option value="1728" style="color:red;">1728</option>
                    <option value="1731" style="color:red;">1731</option>
                    <option value="1732-1761" style="color:red;">1732-1761</option>
                    <option value="1764-1819" style="color:red;">1764-1819</option>
                    <option value="1764-1848" style="color:red;">1764-1848</option>
                    <option value="1769" style="color:red;">1769</option>
                    <option value="1772-1846" style="color:red;">1772-1846</option>
                    <option value="1773-1846" style="color:red;">1773-1846</option>
                    <option value="1787-1851" style="color:red;">1787-1851</option>
                    <option value="1790" style="color:red;">1790</option>
                    <option value="1803" style="color:red;">1803</option>
                    <option value="1805" style="color:red;">1805</option>
                    <option value="1805-1814" style="color:red;">1805-1814</option>
                    <option value="1810" style="color:red;">1810</option>
                    <option value="1811" style="color:red;">1811</option>
                    <option value="1812-1823" style="color:red;">1812-1823</option>
                    <option value="1812-1871" style="color:red;">1812-1871</option>
                    <option value="1814" style="color:red;">1814</option>
                    <option value="1814-1841" style="color:red;">1814-1841</option>
                    <option value="1815-1867" style="color:red;">1815-1867</option>
                    <option value="1818" style="color:red;">1818</option>
                    <option value="1820" style="color:red;">1820</option>
                    <option value="1822" style="color:red;">1822</option>
                    <option value="1823" style="color:red;">1823</option>
                    <option value="1824" style="color:red;">1824</option>
                    <option value="1826" style="color:red;">1826</option>
                    <option value="1831" style="color:red;">1831</option>
                    <option value="1861" style="color:red;">1861</option>                  
                    <option value="1870-1873" style="color:red;">1870-1873</option>
                    <option value="1878" style="color:red;">1878</option>                  
                    <option value="1882" style="color:red;">1882</option>
                    <option value="1882-1919" style="color:red;">1882-1919</option>
                    <option value="1886-1907" style="color:red;">1886-1907</option>
                </select>

                <label for="sogn">Sted, By, Sogn, Gade</label>
                <input type="text" name="sogn" id="sogn" placeholder="Fritekst" style="color: #e08a94;">

                <input type="submit" value="Søg" name="Sog" id="Sog">  
                <input type="reset" value="Nulstil">

            </form>
        </div>
        <!-- HEADER END -->

    </div>   

    <div id="ft_items"></div>
  
    <script type="text/javascript">
    $(function(){

        // Reset the form
        $(':reset', '#search').click(function(e) {
            $('#ft_items').html('');
        });

        // Search Button
        $('#search').submit(function(e) {
            e.preventDefault();
            $.ajax({
                url : 'ft_table.php',
                method: 'post',
                data : $('#search').serialize(),
                dataType: 'html'
            }).done(function(res){
                $('#ft_items').html(res);
                $('#FtKtable').dataTable({searching: true, paging: false, info: false, "dom": '<"top"<if>', order: []}); 
            });
        });

        $('#ft_items').on('click', '.toggle', function() {
            $(this).parents('tr').nextAll('tr.child').toggle();
        });

        $.post('limit.php',{amt: $(this).val()}, function(options) {
            $('#aar').html(options)
        }); 

        $('#mytype').change(function() {
            $.post('limit.php',{amt: $('#amt').val(), type: $(this).val()}, function(options) {
                $('#aar').html(options)
            });
        }); 
    });
    </script>    

</body>
</html>

Open in new window

Get those bits sorted and then we can move on to the logic you've applied to your SQL code. That's going to need some working out.
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,

I will work on tidying this up, and then I will post a new question. Sorry - I just let my exitement take me away for a moment :)

But I have one more thing to the issue in question in this thread.

We are now using the Parish (Sogn) name as a search string that shows results from separate sources in one page. That is excellent, and precisely what I was after. But I did not consider that there in fact are several Parishes with the same Parish name  but located in different Counties (Amt). So what I would like to do is to limit the search to be within the Amt we are looking at at the canvas page.

I have tried if I could use the $amt variable that we use in the canvas page to display all the shires and parishes within the county, but I can't get it working. I'm sure this is possible, I just need a little guidance to set it up in the right way :)

Then I WILL give you some peace - sorry for my persistent spamming :)
0
Chris StanyonWebDevCommented:
Hey Peter. No worries on the 'spamming'. It's been quite a complicated application with lots of new concepts to cover.

Not entirely sure what you need. Do you need to pass 2 pieces of info to the ByLand page - the County (amt) and the Parish (sogn).

If that's the case, you would need to do a couple of things. Firstly, add a new field to the hidden form:

<form id="search" style="display:none" target="_blank" method="post">
    <input type="text" name="sogn" id="sogn">
    <input type="text" name="amt" id="amt">
</form>

Open in new window


Then make sure the amt value is set when you click on a canvas Amt. You already have the click event bound to each county, so you can just add to that:

allPaths[i].addEventListener('click',function (e){
    var area=this.id;
    $('#amt').val(area); // add this line in
    /* Here I need that clicking a selection fires a query in the DB*/       
    $.ajax({
       url: 'ajaxSognHerredHtml.php',
       ...

Open in new window

Now when the form is submitted you will have $_POST['sogn'] and $_POST['amt'], so you can use that in your SQL:

$sql = <<<EOT
SELECT Sogn, Amt, URL, Kirkebog
FROM kirkebogssogne
WHERE (Sogn LIKE ? OR Sogn LIKE ?') AND Amt == ?
EOT;

if ($stmt = $mysqli->prepare($sql)):

    $terms = cityVariations($_POST['sogn']);
    array_walk($terms, function (&$term) { $term = $term . '%'; });

    $stmt->bind_param('sss', $terms[0], $terms[1], $_POST['amt']);
    $stmt->execute();

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Now it gets weard :)

This works for church books:
$sql = <<<EOT
SELECT Sogn, Amt, URL, Kirkebog
FROM kirkebogssogne
WHERE (Sogn LIKE ? OR Sogn LIKE ?) AND Amt = ?
EOT;

if ($stmt = $mysqli->prepare($sql)):

    $terms = cityVariations(trim($_POST['sogn'])); 
    array_walk($terms, function (&$term) { $term = '%' . $term . '%'; });

    $stmt->bind_param('sss', $terms[0], $terms[1], $_POST['amt']);
    $stmt->execute();

Open in new window



This is not working for census:
$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.AmtHerredSognGade AS pAmtHerredSognGade, p.Aar AS pAar, p.id AS pId, p.Type AS pType,
    c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId, c.URL AS cURL, c.Type AS cType
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE (c.Sogn LIKE ? OR c.Sogn LIKE ?) AND Amt = ?
EOT;

if ($stmt = $mysqli->prepare($sql)): // prepare the query

// get the search term variations
$parishes = cityVariations(trim($_POST['sogn'])); 
array_walk($parishes, function (&$sogn) { $sogn = $sogn . '%'; });

$stmt->bind_param('sss', $parishes[0], $parishes[1], $_POST['amt']); // bind the search terms to the prepared query
$stmt->execute(); // and run the query

Open in new window

0
Chris StanyonWebDevCommented:
Hey Peter,

Don't forget, you have the Amt field in both tables, so you need to qualify it by specifying exactly which table you mean (c.Amt):

ON p.Child_id = c.Parent_id WHERE (c.Sogn LIKE ? OR c.Sogn LIKE ?) AND c.Amt = ?
0
Peter KromanSales ExecutiveAuthor Commented:
Yep. Understood. And it works now. Thanks :)

But it looks like one solution generates a new problem.

The search field on the sognefakta_ByLand.php page does not work now that we have added Amt the the search because it only searches for the Sogn. Is it possible to have that still working just using the Sogn as search string?
0
Chris StanyonWebDevCommented:
Yeah - it's possible :)

It'll need a bit of tweaking because depending on whether you have the Amt posted or not will change the SQL and the parameter bindings. Basically, we need to decide whether or not to include the WHERE Amt = ? in the SQL:

$where = isset($_POST['amt']) ? "AND c.Amt = ?" : "";

$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.AmtHerredSognGade AS pAmtHerredSognGade, p.Aar AS pAar, p.id AS pId, p.Type AS pType,
    c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId, c.URL AS cURL, c.Type AS cType
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE (c.Sogn LIKE ? OR c.Sogn LIKE ?)
$where
EOT;

Open in new window

Now if the Amt variable is posted, it will add in "AND c.Amt = ?" to the SQL. We then need to bind either 2 or 3 parameters to the query:

if ($stmt = $mysqli->prepare($sql)): // prepare the query

// get the search term variations
$parishes = cityVariations(trim($_POST['sogn'])); 
array_walk($parishes, function (&$sogn) { $sogn = $sogn . '%'; });

if (isset($_POST['amt'])):
    $stmt->bind_param('sss', $parishes[0], $parishes[1], $_POST['amt']); // bind the search terms to the prepared query
else:
    $stmt->bind_param('ss', $parishes[0], $parishes[1]); // bind the search terms to the prepared query
endif;

$stmt->execute(); // and run the query

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Nice - i'll be working on this, and get back to morrow.

have a beautiful and peaceful evening :)
0
Peter KromanSales ExecutiveAuthor Commented:
Yes - that works perfectly.
I have modified it a bit because I need the WHERE clause to be AmtHerredSognGade when using the link, and I need it to be Sogn when using the search field.
/ Let's deal with our data
$where = isset($_POST['amt']) ? "AND c.Amt = ?" : "";

$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.AmtHerredSognGade AS pAmtHerredSognGade, p.Aar AS pAar, p.id AS pId, p.Type AS pType, p.Amt AS pAmt,
    c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId, c.URL AS cURL, c.Type AS cType, c.Amt AS cAmt
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE (c.AmtHerredSognGade LIKE ? OR c.AmtHerredSognGade LIKE ?)
$where
EOT;

if ($stmt = $mysqli->prepare($sql)): // prepare the query

// get the search term variations
$parishes = cityVariations(trim($_POST['sogn'])); 
array_walk($parishes, function (&$sogn) { $sogn = '%' . $sogn . '%'; });

if (isset($_POST['amt'])):
    $stmt->bind_param('sss', $parishes[0], $parishes[1], $_POST['amt']); // bind the search terms to the prepared query
else:
$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.AmtHerredSognGade AS pAmtHerredSognGade, p.Aar AS pAar, p.id AS pId, p.Type AS pType, p.Amt AS pAmt,
    c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId, c.URL AS cURL, c.Type AS cType, c.Amt AS cAmt
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id WHERE (c.Sogn LIKE ? OR c.Sogn LIKE ?)
EOT;
    $stmt->bind_param('ss', $parishes[0], $parishes[1]); // bind the search terms to the prepared query
endif;

$stmt->execute(); // and run the query

Open in new window


Now I hope that we are done with this one :)

But I need to set this up as an application that I can use from other pages too. I will post a new question on that when I am ready - as well as a new question regarding the "big" census page, that needs some tidying up before we can move on.  

I will wait a little while to close this question - just in case :) :)
0
Chris StanyonWebDevCommented:
Hey Peter,

Good effort, but unfortunately you code won't quite work! If you read through the logic, your second SQL statement never gets Prepared, so when you call the last bind_param, you are still binding to the first SQL statement. The only difference between the 2 statements is the WHERE clause, so a better way to do this is like so:

// Let's deal with our data
if (isset($_POST['amt'])):
    $where = "(c.AmtHerredSognGade LIKE ? OR c.AmtHerredSognGade LIKE ?) AND c.Amt = ?";
else:
    $where = "(c.Sogn LIKE ? OR c.Sogn LIKE ?)";
endif;

$sql = <<<EOT
SELECT
    p.Sogn AS pSogn, p.AmtHerredSognGade AS pAmtHerredSognGade, p.Aar AS pAar, p.id AS pId, p.Type AS pType, p.Amt AS pAmt,
    c.Sogn AS cSogn, c.AmtHerredSognGade AS cAmtHerredSognGade, c.Aar AS cAar, c.id AS cId, c.URL AS cURL, c.Type AS cType, c.Amt AS cAmt
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id
WHERE $where
EOT;

if ($stmt = $mysqli->prepare($sql)): // prepare the query

// get the search term variations
$parishes = cityVariations(trim($_POST['sogn'])); 
array_walk($parishes, function (&$sogn) { $sogn = '%' . $sogn . '%'; });

if (isset($_POST['amt'])):
    $stmt->bind_param('sss', $parishes[0], $parishes[1], $_POST['amt']); // bind the search terms to the prepared query
else:
    $stmt->bind_param('ss', $parishes[0], $parishes[1]); // bind the search terms to the prepared query
endif;

$stmt->execute(); // and run the query

Open in new window

May be worth posting a message on here when you ask your new questions, as I'll then see them and I can take a look :)
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,

Will do. I am putting up a little and simple question in a few minutes :)
0
Peter KromanSales ExecutiveAuthor Commented:
Could we do one thing more on this.

Problem is that census for the Capitol is not organized in parishes.

So what I am thinking is, that when we on the page /sognefakta_ByLand.php is performing a search for a parish, and that parish is marked with the Type "Hovedstaden" in the database, then the second column ("Folketællinger" = Census) on the page should display this selection:
SELECT *
 FROM folketaellinger
 WHERE Område = 'København' 

Open in new window

I know that we are a little far out here, but I hope that this is possible too :)
0
Chris StanyonWebDevCommented:
Sorry Peter - not sure exactly what you need. Going to need some clarity :)
0
Peter KromanSales ExecutiveAuthor Commented:
It is a little hard to explain. Let me try ...

If you go to http://kroweb.dk/gfdev/canvas/sognefakta_ByLand.php, and type "Citadel" - which is a parish in the capitol, you get the curchbooks from the parish correctly, but the census table is empty because the census in the capitol is nit organized in parishes.

If you now go to http://kroweb.dk/gfdev/canvas/sognefakta_Hovedstaden.php, and type in "Citadel", you get the same churchbook list aS before, and you get the list of census in the capitol over the years.

What I would like is that this census list is shown on the sognefakta_ByLand page, if the sogn searched har the type "Hovedstaden".

I will just paste in the classes, SQL and table code for the  http://kroweb.dk/gfdev/canvas/sognefakta_Hovedstaden.php, and if you need more that that I am ready to copy/paste in a hurry :) :)

classes:
<?php
abstract class RecordTypes
{
    const ftMaster = 'ftMaster';
    const ftChild = 'ftChild';
    const ftOrphan = 'ftOrphan';

}

class KbSogn
{
    public $link;
    public $sogn;
    public $amt;


    function __construct()
    {
         $this->link = sprintf("<a href='%s' target='_blank'>%s</a>", $this->URL, $this->Kirkebog);
         $this->sogn = $this->Sogn;
         $this->amt = $this->Amt;

    }
}



class ftCapitol
{
    public $link;
    public $omraade;
    public $aar;

    function __construct()
    {

        $this->link = sprintf("<a href='%s' target='_blank'>%s</a>", $this->URL, $this->Område);
        $this->aar = $this->Aar;
     }
}   

class ftMaster
{
    public $type;
    public $id;
    public $amtherredsogngade;
    public $sogn;
    public $aar;
    public $mytype;
    public $children;

    function __construct($row)
    {
        $this->type = RecordTypes::ftMaster;
        $this->id = $row->pId;
        $this->amtherredsogngade = $row->pAmtHerredSognGade;
        $this->sogn = $row->pSogn;
        $this->aar = $row->pAar;
        $this->mytype = $row->pType;
        $this->children = array();
    }
}

class ftChild
{
    public $type;
    public $id;
    public $amtherredsogngade;    
    public $sogn;
    public $aar;
    public $mytype;
    public $link;

    function __construct($row)
    {
        $this->type = (is_null($row->pId)) ? RecordTypes::ftOrphan : RecordTypes::ftChild;
        $this->id = $row->cId;
        $this->amtherredsogngade = $row->cAmtHerredSognGade;        
        $this->sogn = $row->cSogn;
        $this->aar = $row->cAar;
        $this->mytype = $row->cType;
        $this->link = empty($row->cURL) ? $row->cAmtHerredSognGade : sprintf("<a href='%s' target='_blank'>%s</a>", $row->cURL, $row->cAmtHerredSognGade);
    }
}

function cityVariations($city) {
    $substitute = array(
        "æ" => "ae",
        "ø" => "oe",
        "å" => "aa",
        "ae" =>"æ",
        "oe" =>"ø",
        "aa" => "å",
        );

    $flatCity = mb_convert_case($city, MB_CASE_LOWER);

    $cities[] = $flatCity;
    $cities[] = strtr($flatCity, $substitute); 

    return $cities;
}
 

Open in new window


SQL data:
<?php 

require_once('classes.php');
require_once('ft_data_connection.php');

$data = array(); // initialise the data store

if (empty($_POST)) { return; } // let's quit if we have no search data


// Let's deal with our data
$sql = <<<EOT
SELECT *
 FROM folketaellinger
 WHERE Område = 'København' 
EOT;

if ($stmt = $mysqli->prepare($sql)):


    $stmt->execute();

    $results = $stmt->get_result();

    while ($row = $results->fetch_object('FtCapitol')): // loop through the Db results

        $data[] = $row;

    endwhile;

endif;


//var_dump($data);

Open in new window


table code:
<?php require_once('ftH_data_SQL.php'); ?>



<?php if (count($data)): ?>

        <table id="FtHTable" class="table">

   <thead >         
     <tr>

        <th>Hovedstaden</th> <th>Aar</th>
     

    </tr> 
    </thead>            

 
         <?php foreach ($data as $record): ?>

            <tr>
                <td><?php echo $record->link ?></td>            
                <td><?php echo $record->aar ?></td>
            </tr>

        <?php endforeach; ?>

    </table>

<?php else: ?>

    <h6>Der er ingen resultater for denne søgning</h6>

<?php endif; ?>

Open in new window

0
Chris StanyonWebDevCommented:
Nope - still not getting what you need. That middle column is currently based on the JOIN query and the HTML for the whole parent / child stuff we've been dealing with.

If you're saying you want a different query / table for the middle column based on some other logic, then you may be able to apply that in the HTML of the main page:

<div class="col">

    <p>Folketællinger</p>

    <?php
    if (someCondition == true):
        include_once('ft_table.php');
    else:
        include_once('some_other_table.php');
    endif;
    ?>
</div>

Open in new window

I don't know what logic you need to run to decide which file gets included. You say that is should show this other table if the Type is 'Hovedstaden', but you haven't said where you're getting that information from.

Also, in the SQL that you've posted, you seem to be selecting ALL records - there is no WHERE clause in your query, so no matter what you type into the Search box on sognefakta_Hovedstaden.php, you always get the same results in the middle column!
0
Peter KromanSales ExecutiveAuthor Commented:
Yes - that is exactly the case.
In the sognefakta_ByLand the search in the middle column is based on a join where Sogn (Parish, City) is the main key. This is because census in all parishes and Cities outside the Capitol are organized in parishes exactly as curchbooks in the first column and as well as most other archives are. So for those records we are creating a dynamic list that changes with search which is the whole point of doing this.

BUT - regarding the Capitol the census are not organized in parishes - but in streets -  which means a couple of things:
1. we can't have the same dynamics here because there is no parish or cities to be searched for
2. we need to have a static list of census in the Capitol, and not the dynamic join-searched one we are using for all other parishes
3. that is why I have made the page sognefakta_Hovedstaden.php that holds a static list of census for the Capitol kept in one line per year (and not all the streets which would have created a very. very, very long list and which I by the way don't have all needed data to support)

This static list is accordingly always shown on the sognefakta_Hovedstaden page, and It is this same static list I want to show when a search  on the sognefakta_ByLand page that returns "Hovedstaden" from the Type column in the database.

The suggestion you make has the problem that the condition I need to put in the if clause holds references to objects not yet set.
When I do like this in the sognefaktaByLand.php file:
                <?php if($row->cType == 'Hovedstaden'):?>
                    <?php include_once('ftH_table.php');?>
                <?php else: ?>
                    <?php include_once('ft_table.php'); ?>
                <?php endif; ?>  

Open in new window


I naturally get a non-object error because we have not loaded any of the logic yet.
Don't know if this is really possible, or if I am just mixing things too much here ....
0
Chris StanyonWebDevCommented:
Hey Peter,

It is possible, but obviously you'd need to run another query to find out whether the type is Hovedstaden.

One way of doing this would be to create a 'decider' page. A simple PHP script the runs the query, checks the type and then decides which page to include. May need tweaking, but something like this:

<?php 
require_once('ft_data_connection.php');

if (empty($_POST)) { return; } // let's quit if we have no search data

$sql = <<<EOT
SELECT Type
FROM ft_children 
WHERE Sogn LIKE ? OR Sogn LIKE ?
LIMIT 1
EOT;

if ($stmt = $mysqli->prepare($sql)): 

    // get the search term variations
    $parishes = cityVariations(trim($_POST['sogn'])); 
    array_walk($parishes, function (&$sogn) { $sogn = '%' . $sogn . '%'; });

    $stmt->bind_param('ss', $parishes[0], $parishes[1]);
    $stmt->execute();

    $results = $stmt->get_result();
    $row = $results->fetch_object();

    if ($row->Type == 'Hovedstaden'):
        include('ftH_table.php');
    else:
        include('ft_table.php');
    endif;

endif;

Open in new window

Then in your main file, just include the decider page:

<div class="col">

    <p>Folketællinger</p>

    <?php include_once('decider.php'); ?>

</div>

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,

I will work on that - and I have sent you a message :)
0
Peter KromanSales ExecutiveAuthor Commented:
Well - I still get a non property like this:
Notice: Trying to get property of non-object in /var/www/kroweb.dk/public_html/gfdev/canvas/decider.php on line 25
Ingen resultater for denne søgning
where line 25 is this one:
  if ($row->Type == 'Hovedstaden'):

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
When searching for Citadel and var_dump results I get this:
object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) }
0
Chris StanyonWebDevCommented:
That looks like your query is not returning any results. Try the query directly in your DB client and see what you get.

SELECT Type
FROM ft_children
WHERE Sogn LIKE %someValue% OR Sogn LIKE %someValue%
LIMIT 1
0
Peter KromanSales ExecutiveAuthor Commented:
I have changed the query - bacause we can't search for a Sogn that is not there :)
So with this decider.php file it seems to work as I would like it to.
<?php 
require_once('ft_data_connection.php');

if (empty($_POST)) { return; } // let's quit if we have no search data

$sql = <<<EOT
SELECT Type
FROM ft_children 
WHERE Sogn =""
LIMIT 1
EOT;


if ($stmt = $mysqli->prepare($sql)): 


    $stmt->execute();

    $results = $stmt->get_result();
    $row = $results->fetch_object();

    var_dump($results);

    if ($row->Type == 'Hovedstaden'):
        include('ftH_table.php');
    else:
        include('ft_table.php');
    endif;

endif;

Open in new window

0
Chris StanyonWebDevCommented:
Not sure that makes sense. You're basically always pulling out the first record from the child table where there is no SOGN, and the nchecking the type of that record.
0
Peter KromanSales ExecutiveAuthor Commented:
You are right This query is returning "Hovedstaden" which is the parameter that triggers the ftH table
Sk-rmbillede-2017-12-29-13.36.21.pngBut I still need to be able to trigger the ft if the query returns something else that "Hovedstaden".

When I run your query directly in the SQL editor I have to remove the % signs, and it is naturally returning nothing when the selection is a Capitol parish because there is no parish. On the other hand it correctly returns either "Købstad" og "Landdistrikt" when the parish searched in not a Capitol parish.
0
Chris StanyonWebDevCommented:
OK. If you're happy that the first record with no Sogn will aways return Hovedstaden, regardless of the search term, then i guess that's fine.
0
Peter KromanSales ExecutiveAuthor Commented:
I am not happy with the thing I proposed, because it means that the ftH table is always shown no matter which search I make on the sognefaktaByLand.php page.

And the thing you proposed produces an error because we are searching for a Sogn that is not here.

So what I need is something in between :) I have worked with the decider.php file, but I can't figure out how to make it do what I want.  

With this code:
<?php 
require_once('ft_data_connection.php');

if (empty($_POST)) { return; } // let's quit if we have no search data

$sql = <<<EOT
SELECT Type
FROM ft_children 
WHERE (Sogn LIKE ? OR Sogn LIKE ?)
LIMIT 1
EOT;

if ($stmt = $mysqli->prepare($sql)): 

    // get the search term variations
    $parishes = cityVariations(trim($_POST['sogn'])); 
    array_walk($parishes, function (&$sogn) { $sogn = '%' . $sogn . '%'; });

    $stmt->bind_param('ss', $parishes[0], $parishes[1]);
    $stmt->execute();

    $results = $stmt->get_result();
    $row = $results->fetch_object();

    if ($row->Type == 'Hovedstaden'):
        include('ftH_table.php');
    else:
        include('ft_table.php');
    endif;

endif;

Open in new window

the decider.php works nicely when the search is a parish outside the Capitol (a parish with a name in the database), but it produces an error when the search is for a parish in the Capitol (a parish without a name) and it is in this case I would like it to show the ftH table.

Do you think that this can be done, or is this just one of those things that is better forgotten :) :)
0
Chris StanyonWebDevCommented:
OK Peter,

I'm struggling to understand your data, partly because of the language but partly because I can't quite see how the search results are supposed to work.

When you do a search using the query you've just shown, it will either return 1 or 0 records. If it returns 1 record, you can obviously check the value of Type to see if it's Hovedstaden or not. What do you intend to do if it returns no records?
0
Peter KromanSales ExecutiveAuthor Commented:
But it will never return "Hovedstaden" because there are no Sogn in the database in all records with the Type "Hovedstaden". That is really the problem here.
0
Peter KromanSales ExecutiveAuthor Commented:
Or the other way around. All records with the Type "Hovedstaden" has no Sogn - they are empty in the Sogn column.
0
Chris StanyonWebDevCommented:
So, is this correct.

You do a search for a given Sogn. If 1 record is returned, you want to load ft_table. If no records are returned, you want to load ftH_table.php

??
0
Peter KromanSales ExecutiveAuthor Commented:
Yeahhh :)
0
Chris StanyonWebDevCommented:
Right :)

if ($stmt = $mysqli->prepare($sql)): 

    // get the search term variations
    $parishes = cityVariations(trim($_POST['sogn'])); 
    array_walk($parishes, function (&$sogn) { $sogn = '%' . $sogn . '%'; });

    $stmt->bind_param('ss', $parishes[0], $parishes[1]);
    $stmt->execute();

    $results = $stmt->get_result();

    if ($results->num_rows):
        include('ft_table.php');
    else:
        include('ftH_table.php');
    endif;

endif;

Open in new window

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 KromanSales ExecutiveAuthor Commented:
Fantastic. WIth a little roll-back on my enthusiasme on renaming, and this new script everything is working nicely.
And now I have set of pages and a script that I can use on other "front-pages" from where I might need to call these collecting-pages.

Thanks again Chris.
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
PHP

From novice to tech pro — start learning today.