Improvement on selecting db results

First, add  "Amt" to the fields in your SELECT query:

$sql = <<<EOT SELECT Type, Amt
...

Then after you execute the query, use fetch_assoc() on the result to get the next row:
$row = $results->fetch_assoc();
if($row['Amt'] == 'København')
{
   include('ftH_table.php');
}

Hey Peter,

You'll need to add Amt to the selected columns:

$sql = <<<EOT
SELECT Type, Amt
FROM ft_children

Select allOpen in new window

The following should load up the ftH table if there are no results from the database, or there is a result and the Amt column = København

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

$result = $stmt->fetch_array();

if ($result != null && $result['Amt'] != "København"):
    include('ft_table.php');
else:
    include('ftH_table.php');
endif;

Select allOpen in new window

Haven't tested it so give it a go.

Right. I put in your suggestions, and then I get this error:

Parse error: syntax error, unexpected end of file, expecting elseif (T_ELSEIF) or else (T_ELSE) or endif (T_ENDIF) in /var/www/kroweb.dk/public_html/gfdev/collecting_page2/decider.php on line 30

So I add an extra endif;
And then I get this error:

Catchable fatal error: Object of class mysqli_stmt could not be converted to string in /var/www/kroweb.dk/public_html/gfdev/collecting_page2/decider.php on line 19

Peter,

You're going to have to post your code. Line 30 and line 19 have no context if we can't see your code. You haven't said which code you implemented so can't offer any suggetsions

Sorry - here is the code:

<?php 
require_once('ft_data_connection.php');

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

$sql = <<<EOT
SELECT Type, Amt
FROM ft_children 
WHERE (Sogn LIKE ? OR Sogn LIKE ? AND Amt LIKE ?)
LIMIT 1
EOT;

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

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

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

$result = $stmt->fetch_array();

if ($result != null && $result['$amt'] != "København"):
    include('ft_table.php');
else:
    include('ftH_table.php');
endif;
endif;

Select allOpen in new window

OK. Line 19 has a double $$ sign:

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

Line 24 isn't the same as I posted. You're looking at 'Amt' and not '$amt':

if ($result != null && $result['Amt'] != "København"):


Your bind_param seems to be using a variable called $amt but I can't see anywhere where you're setting this!!

Thanks Chris,

I know I have tried a few things - that's why there is a little confusion in the code. I have changed $amt to Amt, and I have removed the doubble $$.

I get this error. I know this requires more code, but I have to leave for now. Will be back tomorrow with code :)

Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::fetch_array() in /var/www/kroweb.dk/public_html/gfdev/collecting_page2/decider.php:22 Stack trace: #0 /var/www/kroweb.dk/public_html/gfdev/arkivalier/canvas3/sognefakta_ByLand2.php(147): include_once() #1 {main} thrown in /var/www/kroweb.dk/public_html/gfdev/collecting_page2/decider.php on line 22

Sorry Peter,

My bad. Should be:

$stmt->execute();
$results = $stmt->get_result();
$result = $results->fetch_array();

Select allOpen in new window

Something is still wrong. I get this error with this code:

Fatal error: Uncaught Error: Call to a member function fetch_array() on boolean in /var/www/kroweb.dk/public_html/gfdev/collecting_page2/decider.php:21 Stack trace: #0 /var/www/kroweb.dk/public_html/gfdev/arkivalier/canvas3/sognefakta_ByLand2.php(147): include_once() #1 {main} thrown in /var/www/kroweb.dk/public_html/gfdev/collecting_page2/decider.php on line 21

<?php 
require_once('ft_data_connection.php');

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

$sql = <<<EOT
SELECT Type, Amt
FROM ft_children 
WHERE (Sogn LIKE ? OR Sogn LIKE ? AND Amt LIKE ?)
LIMIT 1
EOT;

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

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

$stmt->execute();
$results = $stmt->get_result();
$result = $results->fetch_array();


if ($result != null && $result['Amt'] != "København"):
    include('ft_table.php');
else:
    include('ftH_table.php');
endif;
endif;

Select allOpen in new window

If I do like this I get no errors, but I also get a "No result" message and no results are displayed.

<?php 
require_once('ft_data_connection.php');

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

$sql = <<<EOT
SELECT Type, Amt
FROM ft_children 
WHERE (Sogn LIKE ? OR Sogn LIKE ? AND Amt LIKE ?)
LIMIT 1
EOT;

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

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

$stmt->execute();
$result = $results->fetch_array();
$results = $stmt->get_result();



if ($result != null && $result['Amt'] != "København"):
    include('ft_table.php');
else:
    include('ftH_table.php');
endif;
endif;

Select allOpen in new window

I just saw that the parameter binding was missing in the code. I have added it but it does not make any difference.

Code looks like this now:

<?php 
require_once('ft_data_connection.php');

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

$sql = <<<EOT
SELECT Type, Amt
FROM ft_children 
WHERE (Sogn LIKE ? OR Sogn LIKE ? AND Amt LIKE ?)
LIMIT 1
EOT;

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

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

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


	$result = $results->fetch_array();
	$results = $stmt->get_result();



if ($result != null && $result['Amt'] != 'København'):
    include('ft_table.php');
else:
    include('ftH_table.php');
endif;
endif;

Select allOpen in new window

Yes - I think I have got it working with this code:

<?php 
require_once('ft_data_connection.php');

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

$sql = <<<EOT
SELECT Type, Amt
FROM ft_children 
WHERE (Sogn LIKE ? OR Sogn LIKE ? AND Amt LIKE ?)
LIMIT 1
EOT;

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

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

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

	$results = $stmt->get_result();
	$result = $results->fetch_array();




if ($result != null && $result['Amt'] != 'København'):
    include('ft_table.php');
else:
    include('ftH_table.php');
endif;
endif;

Select allOpen in new window

No - it is not working correctly. I have been working a little more with the code, but I can't get it working right.

What I need in a little more detail is that he table 'ftH_table' is included if 'Amt' == 'København' and 'Type' == 'Hovedstaden' and else the table 'ft_table' should be included.

With the code below I get a result, but it never shows 'ftH_table', so the result must come from the 'ft_table'.
When I var_dump $result it returns NULL even if it displays a result which I assume means that the if block handling $result does not work correctly ???

<?php 
require_once('ft_data_connection.php');

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

$sql = <<<EOT
SELECT Type, Amt
FROM ft_children 
WHERE (Sogn LIKE ? OR Sogn LIKE ? AND Amt LIKE ?)
LIMIT 1
EOT;

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

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

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

	$results = $stmt->get_result();
	$result = $results->fetch_array();




if ($result['Amt'] !='København' && $result['Type'] !='Hovedstaden'):
    include('ft_table.php');
else:
    include('ftH_table.php');
endif;
endif;

var_dump($result);

Select allOpen in new window

Hey Peter,

Not sure what you mean by $result = NULL even if it displays a result!

Few things I would point out in your code.

In your query you have Amt LIKE ? and in the Parameter Binding, you are trying to bind a variable called $amt. Nowhere in your code are you setting this, so where is it getting that value from?

In the if block to check the result, ftH will only be shown if you actually have a record and the values are exactly København and Hovedstaden. The fact that you say $result is null, means you don't have a record, so the if block will fail and probably cause an error.

Make sure error reporting is turned on and include a NULL check in your if statement:

if ($result != null && $result['Amt'] !='København' && $result['Type'] !='Hovedstaden'):

Yes. I have included error reporting and a NULL check.

Well this is a flow, starting with an index file, in this case this: https://kroweb.dk/gfdev/arkivalier/canvas3/
The index file holds a hidden form which includes to fields where the Sogn and the Amt is loaded into when clicking a Sogn on the index page.

The same click opens a file named sognefakta_ByLand2.php which is the collection page that presents information from several sources.

The collecting page we are working on now is this: https://kroweb.dk/gfdev/arkivalier/canvas3/sognefakta_ByLand2.php

One of the sources we present data from on the collecting page is Census (Folketællinger), which is the issue we are working with now.

In order to display Folketællinger on the collecting page we created af file named decider.php which is the actual file we are working on now, and which is called from the /sognefakta_ByLand2.php page.

the decider.php file is (should be) calling one of two table files depending on the result. let's say we need the ftH_table.php now. This table file calls a ftH_data_SQL.php file which again calls a file called classes.php which holds all the classes and their variables, including $amt.

I hope this makes sense - and just let me know if you need some of the code for any of those files mentioned above.

I'm assuming things still aren't working.

It sounds very much like your query isn't returning a record - i.e. no records exist in the database that match.

For the time being completely remove the if block and var_dump some info:

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

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

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

    $results = $stmt->get_result();
    $result = $results->fetch_array();

    var_dump($parishes);
    var_dump($amt);
    var_dump($results);
    var_dump($results->num_rows);
    var_dump($result);

endif;

Select allOpen in new window

Try that and see what the output is. Post it up here if you want us to look at it.

Yes.

I have entered you suggestion now.

I am on this page: https://kroweb.dk/gfdev/arkivalier/canvas3/sognefakta_ByLand2.php

When searching for a parish (Bellinge) which is outside of 'Amt' == 'København' and 'Type' == 'Hovedstaden' i get this output:
array(2) { [0]=> &string(10) "%bellinge%" [1]=> &string(10) "%bellinge%" } NULL object(mysqli_result)#3 (5) { ["current_field"]=> int(0) ["field_count"]=> int(2) ["lengths"]=> array(2) { [0]=> int(12) [1]=> int(6) } ["num_rows"]=> int(1) ["type"]=> int(0) } int(1) array(4) { [0]=> string(12) "Landdistrikt" ["Type"]=> string(12) "Landdistrikt" [1]=> string(6) "Odense" ["Amt"]=> string(6) "Odense" }

When searching for a parish (Citadel) which is not outside the above I get this output:
array(2) { [0]=> &string(9) "%citadel%" [1]=> &string(9) "%citadel%" } NULL object(mysqli_result)#3 (5) { ["current_field"]=> int(0) ["field_count"]=> int(2) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) } int(0) NULL

BUT - all the parishes in the database that has the Type 'Hovedstaden' and the Amt 'København' has no Sogn (parish) in the database, and they can not get a parish because the Census in København was not performed by parish but by street. So perhaps there is a problem lying here too? All other entries in the database has a Sogn entry included.

OK Peter. I don't really understand your data in the same way that you do.

The 1st result shows that you have a record and the values are NOT København and Hovedstaden, so it would try and load the ft_table.php.

The 2nd results shows that there no matching records in the database ($result == null), so it would try and load ftH_table.php.

If there is no Sogn in the Database, then your DB won't return any records ($result will equal NULL), so it can't check the values - the if block will fail and drop into the else block.

What exactly is the problem with that logic?

The problem is that the ftH_table is not shown at any time .... and I really don't understand why that is

OK. Show me the code as you have it for the full if / else block

Yep. You get the code for the entire file here:

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


require_once('ft_data_connection.php');

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

$sql = <<<EOT
SELECT Type, Amt
FROM ft_children 
WHERE (Sogn LIKE ? OR Sogn LIKE ? AND Amt LIKE ?)
LIMIT 1
EOT;

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

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

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

	$results = $stmt->get_result();
	$result = $results->fetch_array();




if ($result != null && $result['Amt'] !='København' && $result['Type'] !='Hovedstaden'):
    include('ft_table.php');
else:
    include('ftH_table.php');
endif;
endif;

//var_dump($result);

Select allOpen in new window

Something else must be going on here. That if block will fire the else part if $result is null. When you search for Citadel, $result is null, so it should fire the else block.

Let's strip that block back to basics and see what's going on. We can then build it back up. Replace it with the following:

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

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

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

    $results = $stmt->get_result();
    $result = $results->fetch_array();

    var_dump($result);

    if ($result != null):
        echo "We have a record";
    else:
        echo "We don't have a record";
    endif;

endif;

Select allOpen in new window

Do a search for Citadel and post up the response.

Response: NULL We don't have a record

And if i try searching for Bellinge I get this response:
array(4) { [0]=> string(12) "Landdistrikt" ["Type"]=> string(12) "Landdistrikt" [1]=> string(6) "Odense" ["Amt"]=> string(6) "Odense" } We have a record

OK. Good stuff.

Now replace the echos with the includes:

if ($result != null):
        include('ft_table.php');
    else:
        include('ftH_table.php');
    endif;

Select allOpen in new window

Try the same searches and you should see each of the pages load. Confirm this and then we'll work on the full if statement.

Yes. Have done.
Citadel gives this response: NULL
Der er ingen resultater for denne søgning

Bellinge gives this response:
array(4) { [0]=> string(12) "Landdistrikt" ["Type"]=> string(12) "Landdistrikt" [1]=> string(6) "Odense" ["Amt"]=> string(6) "Odense" }

       Land: Amt Herred Sogn
        By: By Gade                                Aar
        Odense Odense Bellinge       1787
        Odense Odense Bellinge       1801
        Odense Odense Bellinge       1834
        Odense Odense Bellinge       1840
        Odense Odense Bellinge       1845
        Odense Odense Bellinge       1850
        Odense Odense Bellinge       1855
        Odense Odense Bellinge       1860
        Odense Odense Bellinge       1870
        Odense Odense Bellinge       1880
        Odense Odense Bellinge       1890
        Odense Odense Bellinge       1901
        Odense Odense Bellinge       1906
        Odense Odense Bellinge       1911
        Odense Odense Bellinge       1916
        Odense Odense Bellinge       1921
        Odense Odense Bellinge       1925
        Odense Odense Bellinge       1930
        Odense Brændekilde-Bellinge Bellinge       1940
        Odense Brændekilde-Bellinge Bellinge       1940
        Odense Brændekilde-Bellinge Brændekilde       1940

OK. So we know the if statement is working correctly up to this point. Now we just build on the statement:

if ($result != null && $result['Amt'] != 'København' && $result['Type'] != 'Hovedstaden'):

And you should get exactly the same results as before.

Yes. I get the exact same results :)

OK. Perfect. So it looks like everything is working as it should be.

No. It is exactly not functioning as it should :)

Citadel is a parish within Amt København and Type Hovedstaden, and then it should display ftH_table.php.
It displays "No result"

I'm confused - when you do a search for Citadel, your Database doesn't return any records. Because there are no records, $result == null, so the if statement returns false and loads up the ftH_table.php file from the else block.

Don't know where you're getting the "No Result" text from but if you try and use the $results in your ftH table, bear in mind, that your query didn't return any results, so there are no records to show!

If your search returns NO RECORDS, how can you possibly check the Type and Amt!

The message comes form the ftH_table.php file.

But what I am after is that when the search is done for a Amt = København and Type = Hovedstaden then it should display the table ftH_table.php file, which is a static table for this use only, and it should not display the "No result" message (this message in danish : "Der er ingen resultater for denne søgning". In all other cases it should display the table in ft_table.php if there is a result of the search.

I give you the code of the two table files here:

ft_table.php:

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



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

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

        <th>&nbsp;</th><th>Land: Amt Herred Sogn <br> By: By Gade</th> <th>Aar</th>
     

    </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->aar ?></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->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->link ?></td>
                     <td><?php echo $record->aar ?></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 //var_dump($data) ; ?>

 <?php else: ?>

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

<?php endif; ?>           

Select allOpen in new window

ftH_table.php:

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

Select allOpen in new window

I'm really struggling to understand your requirements here Peter.

Let's try and break this down with an example:

You do a Search for Citadel.
NO RECORDS ARE RETURNED
You then want to check if the Type = Hovedstaden and Amt = København
HOW?? There are NO RECORDS TO CHECK AGAINST. Nothing has been returned from your database, so it's impossible to check the values of a non-existent record.

if ($result != null && $result['Amt'] != 'København' && $result['Type'] != 'Hovedstaden'):
    // we have a record that doesn't contain København and Hovedstaden
else:
   // either we don't have a record (as in Citadel)
   // OR we have a record that contains København and Hovedstaden
endif;

Select allOpen in new window

If you then load up the ftH_Table and you're seeing "No Result", then it's because whatever is going on inside of ftH_data_SQL.php is not returning any records - count($data) is returning 0.

Yes, I can see that.

The problem is, as I explained earlier, that there are no parish posts in the entires where Amt = København and Type = Hovedstaden. So when I search a parish name it will of course return an empty string.

 I think I will have to go back to the starting point which worked in most cases:

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

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

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

    $results = $stmt->get_result();
 

    if ($results->num_rows):
        include('ft_table.php');
    else:
        include('ftH_table.php');
    endif;
   //var_dump($results);
endif;[

Select allOpen in new window

The problem with this is, that it displays ftH_table.php on any search that returns an empty string, and what I would like is that this table was displayed only when the search was done for a parish where Amt = København and Type = Hovedataden. Otherwise it shoud respond with the "No results" message.

But that might not be possible to do?

I'll be working on this, and get back later.

But I'm sure that my database-design is as good as it can be :)  The database is normalized to the possible degree considering the basic data-information that have had to build it upon.

Hi Chris,

I have found the solution.

This if/else block is working as I would like it to work :)

    if ($result['Amt'] == 'København' && $result['Sogn' == ""]):
    include('ftH_table.php');
    else:
    include('ft_table.php');
endif;

Thanks for your help and sparring on this.

I will be back with another issue adressing the same page. It is the third column on that page where I am working on the basic data and the database parts right now.

Thanks again.

Anyway - one more little question:

I am not sure that this statement in the if line always does what I expect it to do:

$result['Sogn' == '']

Select allOpen in new window

What I would like it to do is to eavluate results where the 'Sogn' returned is empty.
It does it in some cases but in some cases it seemes that it is not doing it.

So my question is if my syntax is correct in this particular statement?

Hey Peter,

Your syntax is slightly off. We access an item in an array by using the key, so to access the value you would need  $result['Sogn']. To then check the value you would do this:

$result['Sogn'] == ''

or you could use

empty( $result['Sogn'] )

The way you had it basically evaluates 'Sogn' == '' which obviously returns false, so effectively it would then end up being the equivalent of $result[0] which may or may not evaluate to true depending on whether the first column of your results contained any data.

Glad you got there in the end - I really struggled to understand your requirements this time around :)