Optimizing an existing page

The red lines indicate invalid HTML. Take a look at the code:

<option value="1773-1846"style="color:red;">1773-1846</option>
<option value="1787-1851"style="color:red;">1787-1851</option>

You have no space between the " and style.

I would advise you use a class and style it in CSS rather than adding inline styles - and don't name your class anything 'red'. It then won't make any sense when you decide to change the colour later on :)

<option value="1773-1846" class="hilight">1773-1846</option>
<option value="1787-1851 class="hilight">1787-1851</option>

.hilite { color: red; }

Yep. That's done, and now there are no red lines in the main file sourcecode but there are red years where they should be :)

So to the SQL logic.
When I var_dump the $data variable it returns an empty array, so something is not right.

I have this code in the ft_data_SQL.php file, and I have just put in a fragment of the possible queries (4 out of a total of appr. 15). Please let me know if you need code from any other files.

<?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']); 
$s_sogn = trim($_POST['sogn']);
$s_amt = trim($_POST['amt']);
$s_mytype = trim($_POST['mytype']);



        // Get the city variations
$cities = cityVariations($s_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($s_amt) && !empty($s_sogn) && empty($s_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",$s_amt, $cities[0], $cities[1], $cities[0], $cities[1]); // bind the search terms to the prepared query

elseif (!empty($s_amt) && !empty($start) && !empty($s_sogn) && empty($s_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", $s_amt, $start, $cities[0], $cities[1], $cities[0], $cities[1]); 

else:   

    if (!empty($s_amt) && !empty($s_mytype) && !empty($start) && !empty($s_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", $s_amt, $s_mytype, $start, $cities[0], $cities[1], $cities[0], $cities[1]); 


elseif (!empty($s_sogn) && empty($start) && empty($s_mytype) && empty($s_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;
//var_dump($results);

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

Select allOpen in new window

Hmmm. Your IF statements are way off. What you effectively have is this:

$cities = cityVariations($s_sogn);
array_walk($cities, function (&$city) { $city = '%'.$city.'%'; });

if (!empty($s_amt) && !empty($s_sogn) && empty($s_mytype) && empty($start)):

    SET SQL

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

    elseif (!empty($s_amt) && !empty($start) && !empty($s_sogn) && empty($s_mytype)):

        SET SQL

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

            BIND PARAMS

        else:   

            if (!empty($s_amt) && !empty($s_mytype) && !empty($start) && !empty($s_sogn)):
                
                SET SQL

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

                    BIND PARAMS

                elseif (!empty($s_sogn) && empty($start) && empty($s_mytype) && empty($s_amt)):

                    SET SQL

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

                        BIND PARAMS

                        $stmt->execute();

                    endif;

                endif;

            endif;

        endif;

    endif;

endif;

Select allOpen in new window

Your entre logic is wrapped up in the very first IF statement, and if that's true, the second statement can't possibly be true.

You're going to have 16 variations of your query based on whether 4 variables are set or empty (4^2 = 16). In fact, looking at your code, you're only going to have variations on the WHERE clause (as well as the bindings). There is a cleaner way to do this with - BITFLAGS!

I'll post an explanation and some code in a minute or two

But I don't really understand that. This setup is in fact working in the page now: http://kroweb.dk/gfdev/ft_raw2/

I paste in a fragment of the ajaxFtHtml.php file used here, and where I have taken the if, elseif, else statements from

f (!empty($amt) && !empty($sogn) && empty($type) && empty($start)):
                // We have a 'amt - 'sogn' value

    $query = $mysqli->prepare("SELECT * FROM ftu WHERE Amt = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? )) ORDER BY Type, Aar ASC, Amt ASC");
$query->bind_param("sssss",$amt, $cities[0], $cities[1], $cities[0], $cities[1]);



elseif (!empty($amt) && !empty($start) && !empty($sogn) && empty($type)):
                // We have a 'amt' - 'aar' - sogn' value

    $query = $mysqli->prepare("SELECT * FROM ftu WHERE Amt = ? AND Aar = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? )) ORDER BY Type, Aar ASC, Amt ASC");
$query->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

        $query = $mysqli->prepare("SELECT * FROM ftu WHERE Amt = ? AND Type = ? AND Aar = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? )) ORDER BY Type, Aar ASC, Amt ASC");
    $query->bind_param("sssssss", $amt, $type, $start, $cities[0], $cities[1], $cities[0], $cities[1]);   

Select allOpen in new window

Yeah - that's different to what you have in the other page. This basically looks like so:

if (!empty($amt) && !empty($sogn) && empty($type) && empty($start)):

    SET SQL
    BIND PARAM

elseif (!empty($amt) && !empty($start) && !empty($sogn) && empty($type)):

    SET SQL
    BIND PARAM

else:   

    if (!empty($amt) && !empty($type) && !empty($start) && !empty($sogn)):

        SET SQL
        BIND PARAM

Select allOpen in new window

I'll post an alternative solution up in a few minutes.

OK Peter,

As I said, an alternative to this is to use BITFLAGS. Basically, you have a set of 4 parameters that are either set or not (on or off), so to cater for all variations of which ones are set, you will need 16 queries, ranging from none set to all 4 set and everything inbetween.

Now imaging that your 4 variables are on/off switches. Using BitFlags allows us to flick the switch for each option, so all options off would be 0000 (number 0) and all options on would be 1111 (number 15) - this is a Binary number.

Take a quick look at this to see the switches in action:

$options = 0;

$startSet = 1; // Binary 0001
$sognSet = 2; // Binary 0010
$amtSet = 4; // Binary 0100
$typeSet = 8; // Binary 1000

$options = $options | $startSet; // flick the $startSet switch
$options = $options | $amtSet; /// flick the $amt switch

What we've done there is turn on the BIT for $startSet (0001) and $amtSet (0100)

So now the 'switches' look like 0101, which is the binary representation for 5 : $start = 1 + $amt = 4. If we'd flicked the switches for $sogn and $type, we would have a value of 10 - $sogn = 2 + $type = 8

What this allows us to do is this:

// Flick the switches
if (!empty($start = trim($_POST['query_start']))) { $options = $options | $startSet; }
if (!empty($sogn = trim($_POST['sogn']))) { $options = $options | $sognSet; }
if (!empty($amt = trim($_POST['amt']))) { $options = $options | $amtSet; }
if (!empty($mytype = trim($_POST['mytype']))) { $options = $options | $typeSet; }

Now $options will have a value between 1 and 15, which will tell you exactly which switches are on:

switch($options):
    case 1: // only $start is set
        ...
        break;

    case 2: // only $sogn is set
        ...
        break;

    case 3: // $start and $sogn is set
        ...
        break;

    case 4: // only $amt is set
        ...
        break;

    ...

    case 10: // $sogn and $type is set
        ...
        break;

    case 11: // $start, $sogn and $type is set
        ...
        break;

    case 12: // $amt and $type is set
        ...
        break;

    ...

    case 15: // $start, sogn, $amt and $type is set
        ...
        break;

endswitch;

You now have all those if/else/eleseif statements gone and it's much easier to code (once you understand it!)

That's the explanation. I'll post up some code next

Hey - I think I will have to read this more that once to get the idea.

Reading .......

Just like switching the kitchen light out and the living-room light on - or having them both switched on - or off ??

I did in fact make an Excel sheet to have a little control over the possibilities when I (we) made the first solution, and which were in the code and which were not. It did not operate in binary values in the sheet, but anyway it helps me to understand better where you are heading :)

I am very exited to see the code :)

Right Peter,

Here you go. I've only set the options for the 4 queries that you had in the original post. I'll let you have a read through it before trying to explain anything. Ask if you're unsure of any of it:

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

// Init the Options
$options = 0;

// Set the values of the Switches
$startSet = 1;
$sognSet = 2;
$amtSet = 4;
$typeSet = 8;

// Flick the swithes
if (!empty($start = trim($_POST['query_start']))) { $options = $options | $startSet; }
if (!empty($sogn = trim($_POST['sogn']))) { $options = $options | $sognSet; }
if (!empty($amt = trim($_POST['amt']))) { $options = $options | $amtSet; }
if (!empty($mytype = trim($_POST['mytype']))) { $options = $options | $typeSet; }

// See if we need the CityVariations
if (!empty($sogn)):
    $cities = cityVariations($sogn);
    array_walk($cities, function (&$city) { $city = '%'.$city.'%'; });
endif;

// Set up the 'base' SQL - Make sure to include the blank line at the end!
$sql = <<<EOT
SELECT p.sogn AS pSogn, p.amt AS pAmt, 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

EOT;

// Init the variables
$where = "";
$params = array();

// Let's see which switches are turned on
switch ($options):

    case 1: // $start is set 
        break;
    
    case 2: // $sogn is set
        $where = "WHERE ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ?)) ORDER BY Type ASC, Aar ASC, Amt ASC";
        $params = array('ssss', &$cities[0], &$cities[1], &$cities[0], &$cities[1]); 
        break;

    case 3: // $start and $sogn is set
        break;

    case 4: // $amt is set
        break;

    case 5: // $start and $amt is set 
        break;

    case 6: // $sogn and $amt is set 
        $where = "WHERE Amt = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? )) ORDER BY Type ASC, Aar ASC, Amt ASC";
        $params = array('sssss', &$amt, &$cities[0], &$cities[1], &$cities[0], &$cities[1]);
        break;

    case 7: // $start, $sogn, $amt is set
        $where = "WHERE Amt = ? AND Aar = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? )) ORDER BY Type ASC, Aar ASC, Amt ASC";
        $params = array('ssssss', &$amt, &$start, &$cities[0], &$cities[1], &$cities[0], &$cities[1]);
        break;

    case 8: // $type is set
        break;

    case 15: // $start, $sogn, $amt, $type is set
        $where = "WHERE Amt = ? AND Type = ? AND Aar = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? )) ORDER BY Type ASC, Aar ASC, Amt ASC";
        $params = array('sssssss', &$amt, &$type, &$start, &$cities[0], &$cities[1], &$cities[0], &$cities[1]); 
        break;

    default: // Any other options
        break;

endswitch;

$sql = $sql . $where; // Concatenate in the WHERE clause

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

    if (count($params)): // see if we have any parameters to bind
        call_user_func_array(array($stmt, 'bind_param'), $params);
    endif;

    $stmt->execute();

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

    while ($row = $results->fetch_object()):

        // output your data

    endwhile;

endif;

Select allOpen in new window

I did notice that your SQL statement was off in your original post. You can't alias using the wildcard, so SELECT p.* AS p* is wrong!

Yeahh - I will study this very carefully.
Probably not getting back before tomorrow.
Have a nice evening :)

Well - I have tried to work the new methods gently and carefully into the code.
I still get an empty array - but I get all the params when dumping the $params variable and I get no errors ??

My SQL logic looks like this at this moment

<?php 

error_reporting(E_ALL);
ini_set('display_errors', 1);

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

// Init the Options
$options = 0;

// Set the values of the Switches
$startSet = 1;
$sognSet = 2;
$amtSet = 4;
$typeSet = 8;

// Flick the swithes
if (!empty($start = trim($_POST['query_start']))) { $options = $options | $startSet; }
if (!empty($sogn = trim($_POST['sogn']))) { $options = $options | $sognSet; }
if (!empty($amt = trim($_POST['amt']))) { $options = $options | $amtSet; }
if (!empty($mytype = trim($_POST['mytype']))) { $options = $options | $typeSet; }

// See if we need the CityVariations
if (!empty($sogn)):
    $cities = cityVariations($sogn);
    array_walk($cities, function (&$city) { $city = '%'.$city.'%'; });
endif;

// Set up the 'base' SQL - Make sure to include the blank line at the end!
$sql = <<<EOT
SELECT p.amtherredsogngade AS pAmtHerredSognGade, p.sogn AS pSogn, p.amt AS pAmt, p.aar As pAar, p.mytype AS p.Type, p.kvarter AS pKvarter, p.gade AS p.Gade. p.matr AS p.Matr, c.amtherredsogngade AS cAmtHerredSognGade, c.sogn AS cSogn, c.amt AS cAmt, c.aar As cAar, c.mytype AS c.Type, c.kvarter AS cKvarter, c.gade AS c.Gade. c.matr AS c.Matr 
FROM ft_parents AS p RIGHT JOIN ft_children as c
ON p.Child_id = c.Parent_id

EOT;

// Init the variables
$where = "";
$params = array();

// Let's see which switches are turned on
switch ($options):

    case 1: // $start is set 
        break;
    
    case 2: // $sogn is set
        $where = "WHERE ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ?)) ORDER BY Type ASC, Aar ASC, Amt ASC";
        $params = array('ssss', &$cities[0], &$cities[1], &$cities[0], &$cities[1]); 
        break;

    case 3: // $start and $sogn is set
        break;

    case 4: // $amt is set
        break;

    case 5: // $start and $amt is set 
        break;

    case 6: // $sogn and $amt is set 
        $where = "WHERE Amt = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? )) ORDER BY Type ASC, Aar ASC, Amt ASC";
        $params = array('sssss', &$amt, &$cities[0], &$cities[1], &$cities[0], &$cities[1]);
        break;

    case 7: // $start, $sogn, $amt is set
        $where = "WHERE Amt = ? AND Aar = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? )) ORDER BY Type ASC, Aar ASC, Amt ASC";
        $params = array('ssssss', &$amt, &$start, &$cities[0], &$cities[1], &$cities[0], &$cities[1]);
        break;

    case 8: // $type is set
        break;

    case 15: // $start, $sogn, $amt, $type is set
        $where = "WHERE Amt = ? AND Type = ? AND Aar = ? AND ((Sogn LIKE ? OR Sogn LIKE ?) OR (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ? )) ORDER BY Type ASC, Aar ASC, Amt ASC";
        $params = array('sssssss', &$amt, &$type, &$start, &$cities[0], &$cities[1], &$cities[0], &$cities[1]); 
        break;

    default: // Any other options
        break;

endswitch;

$sql = $sql . $where; // Concatenate in the WHERE clause

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

    if (count($params)): // see if we have any parameters to bind
        call_user_func_array(array($stmt, 'bind_param'), $params);
    endif; 


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

var_dump($data);
var_dump($params);

Select allOpen in new window

OK. One thing we've not really discussed in all of this is error handling . We've kind of ignored it, but it shold be something you build into every app.

Most of the calls we're making to the DB will return some kind of result (TRUE / FALSE / Results etc). We should be checking those results before carrying on with our code. The first point I would build checking into is the prepare() call. Basically, if you look at the manual for any PHP function, you will see the parameters for the function, as well as any possible return values. If you read what it says about the prepare call (http://php.net/manual/en/mysqli.prepare.php) under Return Values you'll see:

mysqli_prepare() returns a statement object or FALSE if an error occurred.

So we can use that for error checking. Currently we just check to see if it doesn't return false:

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

   ... // execute and retrieve

endif;

Select allOpen in new window

We can switch that around and check to see if it does return false:

if (!$stmt = $mysqli->prepare($sql)): // notice the exclamation at the start

    // We had an error when trying to prepare the query.
    die( sprintf("Error: %s", $mysqli->error) );

else:

   ... // execute and retrieve

endif;

Select allOpen in new window

Now when you run the code, if there is a problem with preparing the query, you'll see an error message and your code will stop, rather than just skipping over the whole code block like before.

That was something of an eye-opener :)

Lots of things to be fixed, in table file, in classes file, and in main file. But most of all in SQL file.
We'd forgotten to use the c. and p. in the queries :)

Well - I have now fixed as much ad I can and added the the switch cases I think I'll need to the SQL file.

But we still have a number of problems.

First of all there is a problem that the "År" selectbox is not working with the other boxes. If I choose the Type Slesvig and no year I get alle the records for the type Slesvig  which is as it should be, but if I ask for the Type Selsvig and choose a year, I get no result message (no errors).

I think it has something to do with this limit.php file that was made to limit the years to the choices of the Amt and the Type select boxes. But if I change the 'type' and $type statements in that file to mytype it is not working at all.

the php is this:

$mysqli->set_charset("utf8");
 
$amt = isset($_POST['amt']) ? $_POST['amt'] : false;
$type = isset($_POST['type']) ? $_POST['type'] : false;
$aar = isset($_POST['aar']) ? $_POST['aar'] : false;
 


// We add 1=1 so we can do the AND condition with new criteria without having
// to trim off the last one
$query = "SELECT DISTINCT Aar AS field FROM ftu WHERE 1=1";
$filter = '';
if ($type) {
  $filter .= " AND `Type` = '{$type}'";
}
if ($amt) {
  $filter .= " AND `Amt` = '{$amt}'";
}

// Repeat for any other conditions you want to add
$query .= $filter;

// ADD THE ORDER BY HERE. YOU COULD DO IT AS PART
// OF THE PREVIOUS LINE I DO IT HERE SO THAT YOU
// HAVE THE OPTION OF TURNING IT ON OR OFF
$query .= " ORDER By Aar ASC";

$result = $mysqli->query($query);
if ($result) {
  echo "<option> </option>";
  while($row = $result->fetch_object()) {
    echo <<< OPTION
<option value="{$row->field}">{$row->field}</option>
OPTION;
  }
}

Select allOpen in new window

and the js this

             <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)
                })
               }); 

            })

Select allOpen in new window

Hey Peter,

That file and script have nothing to do with the one we're working on. When I choose Slesvig  and a year, the form is POSTed as expected with the following POST array:

amt => ''
mytype => 'Slesvig'
query_start => '1835'
sogn => ''

From that, you can see that the data is POSTed to ft_table.php as expected, so that will give an $options value of 9 (start = 1 + type = 8). Take a look at your switch statement for case 9 and check for errors there. Also, just as a sanity check, make sure you do hactually have records in your DB that match the query.

Post up your code for that page if you want me to look over it

Yeahhh - it's so good that you are pointing me in the right directions :)

I had a mock-up in the case numbering, so I made a little sheet with some checking calculations to be sure what is right and wrong.
So this part is working nicely now.

Next problem is that links are not displayed.

I can't see where this is going wrong.

I am calling the link here in the ft_table.php file

      <?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; ?>

Select allOpen in new window

     
I am defining the link here in the classes.php file

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);
    }
}

Select allOpen in new window

I am getting there. I have solved the URL problem - missed to define the c.URL int the 'base' SQL

Nice - whichever fields are needed in your classes must be included in the SQL data query :)

Yep :)

Last thing I can't get working is the Data Tables.
I believe that I have it set up in the ft_table.php file here:

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

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

Select allOpen in new window

and in the js here:

             $('#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: []}); 
                });
            });

Select allOpen in new window

... and the loaded stylesheet and scripts here in the main file

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

Select allOpen in new window

Hey Peter,

The jquery.dataTables.js script is not loading as it's returning a 404.

What can I do about that?

I have tried to copy the loading lines form a page where dataTables is working, but it is still not working ...

Currently, this line:

<script type="text/javascript" src="jquery.dataTables.js"></script>

Is trying to load the script from the root of your website. The file doesn't exist so you get a File Not Found error (404). Either change the location above to point to where the file is, move the file into your root so it can find it, or use a CDN so you don't have to download it yourself.

If you want to use the CDN, then you need this line instead:

<script type="text/javascript" src="//cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>

Done - no change

OK. Had another look, and it seems that there's typo in your code. Your table has an ID of FtKTable (upper case T) but your DataTable script is looking for FtKtable (lower case T).

Also still looks like you've got your "dom" property wrong!!

You've got a <script> error at the start of your page:

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

Select allOpen in new window

Double opening script tags.

One other issue which you look like you've fixed - you can't use colspan in a DataTable

We are rolling :)

But about the dom property I need a little guidance - have forgotten what was wrong last time :)

Happy New Year :)

I believe that I have catched the greater idea of BITFLAG switches. Very nice tehcnique :)

But just to confirm that i've got it. i need you to confirm that you in fact have something wrong in your post here:

$options = 0;

$startSet = 1; // Binary 0001
$sognSet = 2; // Binary 0010
$amtSet = 4; // Binary 0100
$typeSet = 8; // Binary 1000

$options = $options | $startSet; // flick the $startSet switch
$options = $options | $amtSet; /// flick the $amt switch

What we've done there is turn on the BIT for $startSet (0001) and $amtSet (0100)

So now the 'switches' look like 0101, which is the binary representation for 5 : $start = 1 + $amt = 4. If we'd flicked the switches for $sogn and $type, we would have a value of 10 - $sogn = 2 + $type = 8

where you say that 0101 is $start + $amt - shouldn't that in fact be $sogn + $type that refers to no. 5 in this setup?

And then I also need to understand if the $...Set values we attatch are values we can choose freely - or if there is a sepcific reason that you choose exactly the values 1, 2, 4, 8.

One more question :)

The years that should be marked in red in the "År" selectbox list we supplied with a class. I call that class

class="sepcyear"

Select allOpen in new window

My question is if it possible to refer to that class in a php if/else statement.

What I need is something like this:

  if($start == one of the values within the specyear class) :
       ... perform something ...
    else:    
        ... perform something else
    endif;

Select allOpen in new window

Now I am spamming again :)

Sorry - and a very Happy New Year to you.

Well - what I am after here might not be possible, but i'll ask anyway.

When we are generating our table now, the children will always be shown "beneath" their parent, because we have set the children rows to display:none. That is also how it basically should be.

But if a search returns only a few children (meaning. not all children attatched to the given parent are returned), would then be possible to just display the children returned in the table?

The reason for this question is that the children represents for 99,9% streets in the cities, and when a search is made for street, it would be more user friendly to get the street searched for, in stead of it's parent.

Hey Peter,

Happy New Year to you.

The reason we select those specific values is because they are decimal representations of the binary switches. which we need for BitFlag operations. Decimal 1 is Binary 1, Decimal 2 is Binary 10, Decimal 4 is Binary 100, and Decimal 8 is Binary 1000, which gives you your 4 switches

0001 = 1
0010 = 2
0100 = 4
1000 = 8

So now the 'switches' look like 0101, which is the binary representation for 5 : $start = 1 + $amt = 4. If we'd flicked the switches for $sogn and $type, we would have a value of 10 - $sogn = 2 + $type = 8

This is correct. When we turn on the start flag (0001 = 1) and the amt flag (0100 = 4), those 2 BitWised together give you (0101), which is the binary number for 5. When we turn on sogn (0010 = 2) and type (1000 = 8) those 2 BitWised together give you 1010 which is the binary number for 10.

When we switch the $options, value 5 means that start and amt are set, value 10 means sogn and type are set.

This is how binary numbers count up:

1 = 2
10 = 2
11 = 3
100 = 4
101 = 5
110 = 6
111 = 7
1000 = 8
1001 = 9
1010 = 10
etc.

Hope that make sense :)

You're not going to be able to use the class from the select in the PHP as that class value never get's POSTed to the script.

As for displaying only the children, technically you could probably achieve this by doing a count on the children. If it's below a certain amount, then you could choose to not show the parent, but you'd need to play around with the CSS classes, as you wouldn't want to give them the child class as this would hide them and you wouldn't have the parent class to toggle them back on.

1. Yeahhh - then the next value would be 16 because it represents binary 10000?

2. If I can not use the class from select how can I make a routine that chooses those values in a PHP if/else function?

3. I will try working with the COUNT. And as I understand you, I need to create a new CSS class to handle these results?

I need a little guidance on the count thing :)

I have made this code below  - the bolded as a sort of a "woekbench" to get the count in place. Try to go to the page (http://kroweb.dk/gfdev/ft_raw3/) and choose Hovedstaden as the Type and put in "Vestergade" in the searchfield then you can see what I am doing. I am not quite sure that I have put this in the right place in the code, and hope you can guide me through the last part of it.

<?php echo "Number of Children: " . count($record->children); ?>
<?php if(count($record->children) < '4') : ?>
       <?php echo "Here I need to tell the code that I don't want parents displayed" ?>
  <?php endif; ?>

?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: 18px; cursor: pointer"></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>
           
       </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>

    </tr>

<?php endforeach; ?>


<?php echo "Number of Children: " . count($record->children); ?>
<?php if(count($record->children) < '4') : ?>
       <?php echo "Here I need to tell the code that I don't want parents displayed" ?>
  <?php endif; ?> 

<?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>
 
    </tr>

<?php endif; ?>

<?php endforeach; ?>

Select allOpen in new window

Point 1 : Absolutely correct :)

Point 2: You would need to have some way of passing the data back to the PHP script. The easiest way to do this is to probably assign a delimited value to each option, and then split on the delimiter in the PHP script. Your <options> could look something like this:

<option value="0|1945">1945</option>
<option value="1|2018" class="sepcyear">1945</option>

And then in PHP you would explode the value by splitting on the pipe (|):

$values = explode("|", $_POST['query_start']);

This will give an array with 2 values:

$values[0] == 0;
$values[1] == 1945;

or

$values[0] == 1;
$values[1] == 2018;


So you can now check it like so:

if ($values[0]): // the first part of the value was set to 1 (true)
    // do something with $values[1]
else:  // the first part of the value was set to 0 (false)
    // do something else with $values[1]
endif;

Obviously if you go this route, anywhere you previously used $_POST['query_start'] you would need to split it and use the second part of the split array.

Point 3. Rather than thinking 'Don't show the Parent', think the other way around - 'when do I want to show the parent'. Also, as I said, you currently set the style for the child to display:none. You will need a way to override that, so I would just assign a second class to the child rows, such as showChild. Have a look through this:

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

<tbody>

    <?php $showChildren = true; ?>

    <?php if(count($record->children) > '4'): ?>

        <?php $showChildren = false; ?>

        <tr class="<?php echo $record->type ?>">
           <td><i class="fa fa-plus-circle text-primary" aria-hidden="true" style="color: cornflowerblue; font-size: 18px; cursor: pointer"></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>
       </tr>

    <?php endif; ?>

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

       <tr class="<?php echo $child->type ?> <?php echo $showChildren ? "showChild" : "" ?>">
            <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>
        </tr>

    <?php endforeach; ?>

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

Select allOpen in new window

We only show the parent if we have more than 4 children. We also initialise a variable called $showChildren to true. If we are showing the parent then we set it to false (because we don't want to show the children). When we output the child row, we check that variable and if it's set to true, we add a second class to the row called showChild. Your row would then look like:

<tr class="ftChild showChild">...

And we can override the default ftChild styling by adding the following CSS:

#FtKTable tbody .ftChild.showChild { display: table-row; }

Thanks Chris,

 A lot of stuff to work on. I am going to work :)
WIll be back.

Hi CHris,

I think I will skip the idea with the "År" select box. It is too complicated, and it is not that important in the "great picture" :)

About the parent/child thing, it is working nicely. But I just need to check if I understand the things that you do correctly.
It is especially this part
<?php echo $showChildren ? "showChild" : "" ?>
of this line
<tr class="<?php echo $child->type ?> <?php echo $showChildren ? "showChild" : "" ?>">

Am I all wrong if I say that this is something in the direction of an if statement, something like this:
 <?php echo $showChildren ?  - means something like - check if the variable $showChildren i set to true
"showChild" : "" - means something like - if not initialize the class "showChild" (for use in CSS) and give it the value ""

could perhaps be rolled out something like this ?

<?php if($showChildren = false) : ?>
<?php class="showChild" value""> ?>

Select allOpen in new window

OK - Thanks,

Then I did get it a little wrong.
But now I did become a little wiser - again :)

Thanks again, Chris for a perfect guidance through - for me - rather difficult areas.

Everything is working perfectly, and now I am in process of normalising my data - and that is going to take quite some time, so I think I will be absent from this page for a while :)