Improvement on selecting db results

Hi,

In this code a selection between two tables is made depending on results or no results in the actual db search.
$sql = <<<EOT
SELECT Type
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();
 

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

Open in new window



What I would like to improve here is that the table ftH_table.php should be chosen only if 'Amt' in the db is 'København'.
That should be all :)

The target page is this: https://kroweb.dk/gfdev/arkivalier/canvas3/sognefakta_ByLand2.php
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.

gr8gonzoConsultantCommented:
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');
}
0
Chris StanyonWebDevCommented:
Hey Peter,

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

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

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

Open in new window

Haven't tested it so give it a go.
0
Peter KromanSales ExecutiveAuthor Commented:
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
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Chris StanyonWebDevCommented:
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
0
Peter KromanSales ExecutiveAuthor Commented:
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;

Open in new window

0
Chris StanyonWebDevCommented:
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!!
0
Peter KromanSales ExecutiveAuthor Commented:
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
0
Chris StanyonWebDevCommented:
Sorry Peter,

My bad. Should be:

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

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
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;

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
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;

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
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;

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
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;

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
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);

Open in new window

0
Chris StanyonWebDevCommented:
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'):
0
Peter KromanSales ExecutiveAuthor Commented:
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.
0
Chris StanyonWebDevCommented:
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;

Open in new window

Try that and see what the output is. Post it up here if you want us to look at it.
0
Peter KromanSales ExecutiveAuthor Commented:
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.
0
Chris StanyonWebDevCommented:
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?
0
Peter KromanSales ExecutiveAuthor Commented:
The problem is that the ftH_table is not shown at any time .... and I really don't understand why that is
0
Chris StanyonWebDevCommented:
OK. Show me the code as you have it for the full if / else block
0
Peter KromanSales ExecutiveAuthor Commented:
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);

Open in new window

0
Chris StanyonWebDevCommented:
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;

Open in new window

Do a search for Citadel and post up the response.
0
Peter KromanSales ExecutiveAuthor Commented:
Response: NULL We don't have a record
0
Peter KromanSales ExecutiveAuthor Commented:
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
0
Chris StanyonWebDevCommented:
OK. Good stuff.

Now replace the echos with the includes:

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

Open 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.
0
Peter KromanSales ExecutiveAuthor Commented:
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
0
Chris StanyonWebDevCommented:
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.
0
Peter KromanSales ExecutiveAuthor Commented:
Yes. I get the exact same results :)
0
Chris StanyonWebDevCommented:
OK. Perfect. So it looks like everything is working as it should be.
0
Peter KromanSales ExecutiveAuthor Commented:
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"
0
Chris StanyonWebDevCommented:
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!
0
Peter KromanSales ExecutiveAuthor Commented:
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; ?>           

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

Open in new window

0
Chris StanyonWebDevCommented:
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;

Open 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.
0
Peter KromanSales ExecutiveAuthor Commented:
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;[

Open 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?
0
Chris StanyonWebDevCommented:
OK. Starting to understand a little more. It sounds very much like this is a problem caused by a deficient database design. Your database is not properly normalised so you have records that may or my not contain information.  

I guess you could technically do another query inside the else block:

if ($results->num_rows):

    include('ft_table.php');

else:

    // we don't have any records from the normal query, so let's try another.
    // do a different query here and then check the Amt and Type
    ...
    if (...):
        include('ft_table.php');
    else:
        include('ftH_table.php');
    endif;

endif;

Open in new window

Don't know if that's feasible or not and I don't know what query you'd need so you'll need to figure that out.
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:
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.
0
Peter KromanSales ExecutiveAuthor Commented:
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.
0
Peter KromanSales ExecutiveAuthor Commented:
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' == '']

Open 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?
0
Chris StanyonWebDevCommented:
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 :)
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
SQL

From novice to tech pro — start learning today.