SQL queries and danish charcters

Hi,

The danish language have three special characters, Æ, Ø, Å - æ, ø, å
These characters are also written as Æ = AE, Ø=OE, Å=AA, æ=ae, ø=oe, å=aa

For example is the name of one city in Denmark be spelled like this: Aalborg and like this: Ålborg.

My question is if ii is possible to create a SQL search in a way that when searching for Ålborg it will return Ålborg as well as Aalborg.

In this searchstring it returns nothing when searching Aalborg because in the database only the spelling Ålborg is used.
      $query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ? AND AmtHerredSognGade LIKE '%".$sogn."%'");
    $query->bind_param("s", $start);

Open in new window

Peter KromanSales ExecutiveAsked:
Who is Participating?

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

x
I wear a lot of hats...

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

Peter KromanSales ExecutiveAuthor Commented:
Sorry. It is MySQL
0
Pawan KumarDatabase ExpertCommented:
Please try like this ..

Need to change collation on the fly.

SELECT * FROM ft WHERE Aar = ? AND AmtHerredSognGade LIKE '%".$sogn."%' COLLATE utf8_unicode_ci
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Kumar,

I have tried - it does not make any difference.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Pawan KumarDatabase ExpertCommented:
Did u get anything with this or not. I mean are you are getting 1 row with this ?
0
Peter KromanSales ExecutiveAuthor Commented:
No - when searching for Aalborg I get the message that nothing is found, and when searching Ålborg I get all the results in the database.
0
earth man2Commented:
I'm no expert in MySQL but what does this do ?
SELECT * FROM ft WHERE AmtHerredSognGade LIKE '%Ålborg%' COLLATE latin1_danish_ci;
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
My question is if ii is possible to create a SQL search in a way that when searching for Ålborg it will return Ålborg as well as Aalborg.
if I understand it clearly... you are trying to compare values here, in the event Å and A is essentially different.
it seems that you're using PHP, i'm thinking that you should create a replace function to replace your search string by characters and compare accordingly. like:

$sql = "SELECT * FROM ft WHERE Aar = ? AND (AmtHerredSognGade LIKE '%".$sogn."%' OR AmtHerredSognGade LIKE '%".replaceString($sogn)."%')  ";
$query = $mysqli->prepare($sql);
    $query->bind_param("s", $start);

Open in new window


then your replaceString function:

function replaceString($v) {
  $arr = array("Æ" => "AE",
              "Ø" => "OE",
              "Å" => "AA",
              "æ" => "ae",
              "ø" => "oe",
              "å" => "aa");
                         
   return ucwords(strtolower(strtr($v, $arr)));
}

Open in new window


hope this make sense
0
Pawan KumarDatabase ExpertCommented:
Please try this- this is working for me.

CREATE TABLE test123
(
   a INT
  ,b NVARCHAR(20)
);

INSERT INTO test123 VALUES ( 1 , 'Ålborg' );
INSERT INTO test123 VALUES ( 2 , 'Alborg' );
INSERT INTO test123 VALUES ( 2 , 'Pawan' );

Open in new window


SOLUTION

SELECT * FROM test123 
WHERE b LIKE '%Ålborg%' COLLATE utf8_unicode_ci

Open in new window


OUTPUT

| a |      b |
|---|--------|
| 1 | Ålborg |
| 2 | Alborg |

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Thanks to all,

@Ryan - I have tried your solution:

This query:
           // We have a 'start' and a 'sogn' value
        $query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ? AND AmtHerredSognGade LIKE '%".replaceString($sogn)."%'");
        $query->bind_param("s", $start);

Open in new window


this function:
        </script>

        <script>
        function replaceString($v) {
        $arr = array("Æ" => "AE",
              "Ø" => "OE",
              "Å" => "AA",
              "æ" => "ae",
              "ø" => "oe",
              "å" => "aa");
                         
            return ucwords(strtolower(strtr($v, $arr)));
}
            </script>

Open in new window


and I get this error:
Fatal error: Uncaught Error: Call to undefined function replaceString() in /var/www/kroweb.dk/public_html/gfdev/ft_raw2/index.php:48 Stack trace: #0 {main} thrown in /var/www/kroweb.dk/public_html/gfdev/ft_raw2/index.php on line 48
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
<script>
        function replaceString($v) {
        $arr = array("Æ" => "AE",
              "Ø" => "OE",
              "Å" => "AA",
              "æ" => "ae",
              "ø" => "oe",
              "å" => "aa");
                         
            return ucwords(strtolower(strtr($v, $arr)));
}
            </script>
It should be coded as PHP code, not Javascript code.
0
Peter KromanSales ExecutiveAuthor Commented:
Sorry - my mistake.

I have now changed the function to php, and now I get no errors. But it still does not return anything when searching for 'Aalborg' - the change is that is not returning anything now when searching for 'Ålborg' either :)
0
earth man2Commented:
Try getting the database query working first before getting the php part sorted.
0
Chris StanyonWebDevCommented:
Hey Peter,

This isn't an issue of charater sets - you effectively need to run several searches against your database - one for each variation of spelling. The general idea would be to create an array of the variations and then loop through that array executing your prepared statement for each. For example, give this a go with the hardcoded variations to see if you get the results you need, and then we can work on building the variation array.

$vars = array("Aalborg", "Ålborg");

$query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ? AND AmtHerredSognGade LIKE ?");
$query->bind_param("ss", $start, $city);

foreach ($vars as $city):
    $city = "%" . $city . "%";
    $query->execute();
    $result = $query->get_result();
    $data = $result->fetch_all();
    var_dump($data);
endforeach;

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Thank you earth man 2 - bur the database query works just fine. I just need to add this feature.
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,

I will work with your input, and get back later (just heading out now :) )
0
Peter KromanSales ExecutiveAuthor Commented:
Hi Chris,

Thais actually works with the hardcoding.

I have two queries where I have added this, and both works nicely.

Here is the working code:
    if (!empty($sogn) && empty($start)):
            // We have a 'sogn' value and no 'start' value
        $vars = array("Aalborg", "Ålborg");  
    $query = $mysqli->prepare("SELECT * FROM ft WHERE AmtHerredSognGade LIKE ? ");
    $query->bind_param("s", $sogn);


    foreach ($vars as $sogn):
        $sogn = "%" . $sogn . "%";
    $query->execute();
    $result = $query->get_result();
    $data = $result->fetch_all();
    //var_dump($data);
    endforeach;

    elseif (!empty($start) && !empty($sogn)):
            // We have a 'start' and a 'sogn' value
        $vars = array("Aalborg", "Ålborg");

    $query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ? AND AmtHerredSognGade LIKE ?");
    $query->bind_param("ss", $start, $sogn);

    foreach ($vars as $sogn):
        $sogn = "%" . $sogn . "%";
    $query->execute();
    $result = $query->get_result();
    $data = $result->fetch_all();
    //var_dump($data);
    endforeach;


    
    endif;

Open in new window


The problem is mainly to replace 'Å' with 'AA' and vice versus, and to replace 'å' with 'aa' and vice versus. The letters Æ,Ø, and æ,ø is not so important at this point.

So if we could build an array around this I would be very happy :)
0
Chris StanyonWebDevCommented:
Cool. Take a look at this (it's based in part on Ryan's function):

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

    $lower = mb_convert_case($city, MB_CASE_LOWER);
    $newCity = strtr($lower, $arr);

    $cities[] = $city;
    $cities[] = $newCity; 

    return array_unique($cities);
}

Open in new window

It uses the mb_convert_case instead of ucwords / strtolower as it works with the UTF characters (Å => å, etc.). It takes in a City and returns an array or variations. Use it like so:

$cities = cityVariations($sogn);

foreach ($cities as $sogn):
    $sogn = "%" . $sogn . "%";
    $query->execute();
    $result = $query->get_result();
    $data = $result->fetch_all();
    //var_dump($data);
endforeach;

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,

It works when searching 'Aalborg', but it returns "No result" when searching for 'Ålborg'

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

    );

    $lower = mb_convert_case($city, MB_CASE_LOWER);
    $newCity = strtr($lower, $arr);

    $cities[] = $city;
    $cities[] = $newCity; 

    return array_unique($cities);
}

    if (!empty($sogn) && empty($start)):
            // We have a 'sogn' value and no 'start' value

    $cities = cityVariations($sogn);
    $query = $mysqli->prepare("SELECT * FROM ft WHERE AmtHerredSognGade LIKE ? ");
    $query->bind_param("s", $sogn);


foreach ($cities as $sogn):
    $sogn = "%" . $sogn . "%";
    $query->execute();
    $result = $query->get_result();
    $data = $result->fetch_all();
    //var_dump($data);
endforeach;

    elseif (!empty($start) && !empty($sogn)):
            // We have a 'start' and a 'sogn' value

    $cities = cityVariations($sogn); 
    $query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ? AND AmtHerredSognGade LIKE ?");
    $query->bind_param("ss", $start, $sogn);
   

foreach ($cities as $sogn):
    $sogn = "%" . $sogn . "%";
    $query->execute();
    $result = $query->get_result();
    $data = $result->fetch_all();
    //var_dump($data);
endforeach;


    
    endif;

Open in new window

0
Chris StanyonWebDevCommented:
OK. Few things to try.

Make sure you're setting the character set on your Db connection straight after you've created it:

$mysqli->set_charset('utf8');

Do some data dumping so you can see what's going on:

$cities = cityVariations($sogn);
var_dump($cities);

And uncomment the var_dump($data) lines in your code - currently your code doesn't seem to output anything so I can't really see how you know what is and isn't working!
0
Peter KromanSales ExecutiveAuthor Commented:
$mysqli->set_charset('utf8'); is set correctly

I get a h... of a lot of dump when activation the var_dupm(s)

Take a look for your self here: http://kroweb.dk/gfdev/ft_raw2/index_test.php

When setting only the var_dump($cities); and searching for 'Ålborg' I get this message, which seems to tell me that it converts upper case letters to lower case letters ??

array(2) { [0]=> string(7) "Ålborg" [1]=> string(7) "aalborg" }
0
Chris StanyonWebDevCommented:
OK. Well the code on the page you've linked to is clearly NOT the code you've posted above. The code I'd posted was intended to get things moving along - it was never intended to be just dropped into another script in the hope that it would work.

Nowhere in your code above are you actually outputting anything. You are however setting and re-setting the $data variable in a loop so it will only ever contain the data from the last iteration of the loop. The idea in my code was to var_dump it just to visualize the info being returned from the Db.

Because you haven't shown the code you're actually using, I can only guess as to what you need. Basically, you should add the data from the Db to an array, not just reset it!

$records = array();
foreach ($cities as $sogn):
    $sogn = "%" . $sogn . "%";
    $query->execute();
    $result = $query->get_result();
    while ($record = $query->fetch_object()):
        $records[] = $record;
    endwhile;
endforeach;

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Sorry. You are right - I have out commented the var_dump($data) lines again. That is reset now. Sorry. Everything else is as you have posted in your code.

I post the total working code here.

<?php
// Turn on error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Do we need the database
if (!empty($_POST)):

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

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

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

    );

    $lower = mb_convert_case($city, MB_CASE_LOWER);
    $newCity = strtr($lower, $arr);

    $cities[] = $city;
    $cities[] = $newCity; 

    return array_unique($cities);
}


if (!empty($amt) && empty($start)):
       //We have an 'amt' value and no 'start' value
        $query = $mysqli->prepare("SELECT * FROM ft  WHERE Amt = ? ");
        $query->bind_param("s", $amt);   

else:

    if (!empty($amt) && !empty($start)):
       //We have an 'amt' value and a 'start' value 
        $query = $mysqli->prepare("SELECT * FROM ft  WHERE Amt = ? AND Aar = ?");
        $query->bind_param("ss", $amt, $start);                



   elseif (!empty($start) && empty($sogn)):
      // We have a 'start' value and no 'sogn' value
    $query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ?");
    $query->bind_param("s", $start);


else:

    if (!empty($sogn) && empty($start)):
            // We have a 'sogn' value and no 'start' value

    $cities = cityVariations($sogn);
var_dump($cities);
    $query = $mysqli->prepare("SELECT * FROM ft WHERE AmtHerredSognGade LIKE ? ");
    $query->bind_param("s", $sogn);


foreach ($cities as $sogn):
    $sogn = "%" . $sogn . "%";
    $query->execute();
    $result = $query->get_result();
    $data = $result->fetch_all();
    var_dump($data);
endforeach;

    elseif (!empty($start) && !empty($sogn)):
            // We have a 'start' and a 'sogn' value

    $cities = cityVariations($sogn); 
var_dump($cities);
    $query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ? AND AmtHerredSognGade LIKE ?");
    $query->bind_param("ss", $start, $sogn);
   

foreach ($cities as $sogn):
    $sogn = "%" . $sogn . "%";
    $query->execute();
    $result = $query->get_result();
    $data = $result->fetch_all();
    var_dump($data);
endforeach;


    
    endif;
    endif;

    endif;

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

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

        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.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">
        $(function(){
            $('#myTable').on('click', '.toggle', function() {
                $(this).parents('tr').nextAll('tr.child').toggle();
            });
        });
        </script>


        <style type="text/css">

        #header { margin-top: 10px; margin-bottom:10px; }
        tbody.toggle tr.child { display: none; background-color: #f6f6f6;}
        tbody.toggle tr:nth-child(even){ display: none; background-color: #d5d5d5;}
        tbody.toggle tr.expander { display: table-row; background-color: #f6f6f6; }
        tbody.toggle tr.non { display: table-row; background-color: white; }
        span.toggle { cursor: pointer; color: cornflowerblue; font-family: 'FontAwesome'; font-size: 16px;}
        span.toggle:hover { color: cornflowerblue; }
        span.toggle:after { content: '\f055' };
        </style>
        <style>

        table, th {

            border-bottom: 1px  #666666;
            border-top: 0px;
            border-right: 0px;
            border-left: 0px;
            border-style: solid;
            border-collapse: collapse;
            padding-top:10px;
            padding-left:20px;
            color:black;
            font-family: Serif; 
            font-size: 20px;
            font-weight: bold;
            text-align: left;




        }

        table, td {

            border: 0px  #f6f6f6;
            border-style: none;
            border-collapse: collapse;
            padding-top: 0px;
            border-bottom: 1px solid #666666;
            padding-left: 0px;
            color:black;
            font-family: arial;
            font-size: 12px;
            font-weight: normal;
            text-align: padding-left;




        }



        a:link {
            color: black;
            text-decoration: underline;
            font-weight: bold;
        }
        a:visited {
            color: #e08a94;
            text-decoration: underline;

        }
        a:hover {
            color: cornflowerblue;
            text-decoration: underline;
            font-weight:normal;
        }
        a:active {
            color: black;
            text-decoration: underline;
        }

        .table > tbody + tbody { border-top: none; }
        .table > tbody > tr > td { border-top: none; }


        </style>



    </head>

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

        <!-- HEADER START -->
        <div class="row" id="header">
            <form method="POST" style="margin-left:30px; margin-right: 30px;"> 


                Amt 
                <select id="amt" name="amt" style="margin-right: 20px; font-weight:bold;">
                    <option value=""></option>
                    <option value="Bornholm">Bornholm</option>
                    <option value="Frederiksborg">Frederiksborg</option>
                    <option value="Haderslev">Haderslev</option>
                    <option value="Hjørring">Hjørring</option>
                    <option value="Holbæk">Holbæk</option>
                    <option value="København">København</option>
                    <option value="Maribo">Maribo</option>
                    <option value="Odense">Odense</option>
                    <option value="Præstø">Præstø</option>
                    <option value="Randers">Randers</option>
                    <option value="Ribe">Ribe</option>
                    <option value="Ringkøbing">Ringkøbing</option>
                    <option value="Roskilde">Roskilde</option>
                    <option value="Skanderborg">Skanderborg</option>
                    <option value="Sorø">Sorø</option>
                    <option value="Svendborg">Svendborg</option>
                    <option value="Sønderborg">Sønderborg</option>
                    <option value="Thisted">Thisted</option>
                    <option value="Tønder">Tønder</option>                       
                    <option value="Vejle">Vejle</option>                       
                    <option value="Viborg">Viborg</option>
                    <option value="Aabenraa">Aabenraa</option>
                    <option value="Aalborg">Aalborg</option>
                    <option value="Aarhus">Aarhus</option>

                </select>


                År                
                <select id="aar" name="query_start" style="margin-right: 20px; padding-left: 30px; ">
                    <option value=""></option>
                    <option value="1787">1787</option>
                    <option value="1801">1801</option>
                    <option value="1803">1803</option>
                    <option value="1834">1834</option>
                    <option value="1835">1835</option>                        
                    <option value="1840">1840</option>
                    <option value="1845">1845</option>
                    <option value="1850">1850</option>
                    <option value="1855">1855</option>
                    <option value="1860">1860</option>
                    <option value="1860">1860</option>                        
                    <option value="1870">1870</option>
                    <option value="1860">1878</option>                        
                    <option value="1880">1880</option>
                    <option value="1890">1890</option>
                    <option value="1901">1901</option>
                    <option value="1906">1906</option>
                    <option value="1911">1911</option>
                    <option value="1916">1916</option>
                    <option value="1921">1921</option>
                    <option value="1925">1925</option>
                    <option value="1930">1930</option>                       
                    <option value="1940">1940</option>                       
                </select>



                Sted, By, Sogn, Gade
                <input type="text" title="Søger du et specifikt Sogn, så skriv Amtet med i søgestrengen f. eks 'Bornholm Rønne'" placeholder="Fritekst" style="margin-left:5px; height:25px;width:275px;border: 1px solid #666666;border-radius:2px;color:#666666;" name="sogn">




                <input type="submit" title="Klik her, eller tast Enter, for at aktivere søgningen." style="margin-left:5px;padding-right:10px;height:25px;font-family:arial; font-size: 12px; text-align:left; background-color:#cccccc; border: 2px solid #grey;border-radius:2px; color:black; background-color:#cccccc; box-shadow: 0 8px 16px 0 rgba(0,0,0,0.2), 0 6px 20px 0 rgba(0,0,0,0.19);" value="Søg">

                <input type="submit" style="margin-left:5px;height:25px;font-family:arial; font-size: 12px; text-align:left; background-color:#cccccc; border: 2px solid grey;border-radius:2px; color:black; background-color:#cccccc; box-shadow: 0 8px 16px 0 rgba(0,0,0,0.2), 0 6px 20px 0 rgba(0,0,0,0.19);;" value="Nulstil">




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

        <!-- DATA START -->
    </div>    


    <div class="flex-container" style="width:site-width; background-color:white;">
        <div class="flex-item" style="margin-top:10px; float:left; margin-left:20px;width: site-width; overflow:auto;">

            <div class="row" style="margin-left:10px;margin-right:10px;">

                <!-- Have we ran a query -->
                <?php if (isset($results)): ?>

                <!-- Do we have any results -->
                <?php if ($results->num_rows): ?>

                <table id="myTable" class="table">
                    <colgroup>

                </colgroup>


                <tr>
                    <th>&nbsp;</th> <th>Sted Sogn Gade</th> <th>Amt</th> <th>Aar</th> <th>Gade (1860)</th> <th>Matr. nr. (1860)</th>  
                </tr>


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


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

                <?php if ($row->Collapse == 'p'): ?>

                <!-- We have a parent row -->
                <tbody class='toggle' >

                    <tr class="expander">
                        <td><span class='toggle'></td>
                        <?php if (!empty($row->URL)):?>
                        <td><?php echo $link1 ?></td> 
                    <?php else:?> 
                    <td> <?php echo $link4?> </td> 
                <?php endif; ?> 
                <td><?php echo $row->Amt ?></td>             
                <td><?php echo $row->Aar ?></td>
                <td><?php echo $row->Gade ?></td>            
                <td><?php echo $row->Kvarter ?></td>            

            </tr>

        <?php elseif ($row->Collapse == 'c'): ?>

        <!-- We have a child row -->
        <tr class="child">
            <td>&nbsp;</td>
            <?php if (!empty($row->URL)):?>
            <td><?php echo $link1 ?></td> 
        <?php else:?> 
        <td> <?php echo $link4?> </td> 
    <?php endif; ?> 
    <td><?php echo $row->Amt ?></td>             
    <td><?php echo $row->Aar ?></td>
    <td><?php echo $row->Gade ?></td>            
    <td><?php echo $row->Kvarter ?></td>           


</tr>

<?php else: ?>

    <!-- We have a non-collapsible row -->
    <tr class="non">
        <td>&nbsp;</td>
        <!-- Område, link -->
        <td><?php echo $link1 ?></td>
        <td><?php echo $row->Amt ?></td>      
        <td><?php echo $row->Aar ?></td>
    </tr>

<?php endif; ?>

<?php endwhile; ?>

</table>

<?php else: ?>

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

<?php endif; ?>

<?php endif; ?>

</div>
<!-- DATA END -->

</div> 
</div>

</body>
</html>

Open in new window

0
Chris StanyonWebDevCommented:
OK. That's going to need some serious unraveling!

The problem you have is that after you've done everything with the cityVariations, you then go on to execute the query again (line 103) but because you're not doing that in a loop of the city variations, only the query for the last city is executed.

There are issues with your logic.

I'll need to have a look later on and come back to you.
0
PortletPaulEE Topic AdvisorCommented:
Must you hit the dbms many times?

Can't you form the variatons into a set of OR conditions to be executed once as the where clause?
2
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
will something like this worked for you?

$sogn = "Ålborg";
$searchStr1 = replaceString($sogn);
$searchStr2 = replaceString($searchStr1, false);

$sql = "SELECT * FROM ft WHERE Aar = ? AND (AmtHerredSognGade LIKE '%".$searchStr1."%' OR AmtHerredSognGade LIKE '%".$searchStr2."%')  ";

echo $sogn." > ".$searchStr1." > ".$searchStr2."<br><br>";

echo $sql;

function replaceString($v, $reverse = null) {
  $arr = array("Æ" => "AE",
              "Ø" => "OE",
              "Å" => "AA",
              "æ" => "ae",
              "ø" => "oe",
              "å" => "aa");
   $isDefault = is_null($reverse);
   if ((!$direction) && ($reverse)) {
      $isDefault = false;
   }
   if ($isDefault) {
      $arr2 = array_flip($arr);
      return strtr(mb_convert_case($v, MB_CASE_LOWER), $arr2);
   } else {
      return strtr(mb_convert_case($v, MB_CASE_LOWER), $arr);
   }
}

Open in new window

0
Pawan KumarDatabase ExpertCommented:
The only problem i see in this type of replace approach is that if a new characters comes then that will not work , we again have to add that to our code. So making it generic would be very difficult. An approach from the DB itself should be used.
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chrism Kumar and Ryan,

I had no idea that this was such a big issue. I thought that a thing like this could be solved rather easily.

It is a rather big database I am creating here. At this point I have appr. 20.000 lines and when finished the database will hold about 65.000 lines, so I don't need to make any more queries than necessary :)

I agree with Kumar about that this ought to be solved within the database itself, but if that is not possible I hope that we can solve it in another way that works nicely and do not slow things down.

$Ryan - I have tested your suggestion, and I can't get that working - sorry.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ooops, found a mistake in my function, try this instead:

<?php

ini_set('error_reporting', E_ALL);
ini_set('display_errors', 'On');  //On or Off

/* Test 1 */
echo "<p>Test 1</p>";

$sogn = "Ålborg";
$searchStr1 = replaceString($sogn);
$searchStr2 = replaceString($searchStr1, false);

echo $sogn." > ".$searchStr1." > ".$searchStr2."<br><br>";

$sql = "SELECT * FROM ft WHERE Aar = ? AND (AmtHerredSognGade LIKE '%".$searchStr1."%' OR AmtHerredSognGade LIKE '%".$searchStr2."%')  ";

echo $sql."<br><br>";


/* Test 2 */
echo "<p>Test 2</p>";
$sogn = "Aalborg";
$searchStr1 = replaceString($sogn);
$searchStr2 = replaceString($searchStr1, false);

echo $sogn." > ".$searchStr1." > ".$searchStr2."<br><br>";

$sql = "SELECT * FROM ft WHERE Aar = ? AND (AmtHerredSognGade LIKE '%".$searchStr1."%' OR AmtHerredSognGade LIKE '%".$searchStr2."%')  ";

echo $sql."<br><br>";


function replaceString($v, $reverse = null) {
  $arr = array("Æ" => "AE",
              "Ø" => "OE",
              "Å" => "AA",
              "æ" => "ae",
              "ø" => "oe",
              "å" => "aa");
   $isDefault = is_null($reverse);
   if ((!$isDefault) && ($reverse)) {
      $isDefault = false;
   }
   if ($isDefault) {
      $arr2 = array_flip($arr);
      return strtr(mb_convert_case($v, MB_CASE_LOWER), $arr2);
   } else {
      return strtr(mb_convert_case($v, MB_CASE_LOWER), $arr);
   }
}

?>

Open in new window

1
Peter KromanSales ExecutiveAuthor Commented:
Thanks Ryan,

This is the output of your suggestion:

Test 1
Ã…lborg > Ã¥lborg > aalborg

SELECT * FROM ft WHERE Aar = ? AND (AmtHerredSognGade LIKE '%Ã¥lborg%' OR AmtHerredSognGade LIKE '%aalborg%')

Test 2
Aalborg > Ã¥lborg > aalborg

SELECT * FROM ft WHERE Aar = ? AND (AmtHerredSognGade LIKE '%Ã¥lborg%' OR AmtHerredSognGade LIKE '%aalborg%')
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
so is something like that worked for you?

SELECT * FROM ft WHERE Aar = ? AND (AmtHerredSognGade LIKE '%Ã¥lborg%' OR AmtHerredSognGade LIKE '%aalborg%')
0
Chris StanyonWebDevCommented:
Hey Peter,

Had a chance to properly look through your code and I've made a few tweaks which I think will work (haven't tested it!!). I've adapted the cityVariations method slightly. It will always now return an array with 2 items, all in lowercase. This won't matter as you're running it against a LIKE clause. I know it's beed suggested this is done at the DB level, but I really don't think that's possible.

I've also added in a little call to array_walk which takes the city variations array and wraps each element in the % sign, ready for the query.

I've removed the need to loop through the variations and built it up as a single query with an OR statement. Also tweaked a couple of other bits as well as stripping out the inline CSS and dropping it to an external file - helps to keep things neat. Also tightened up the indenting as I think this helps wil the readability of the code. Have a good read through the following to see if you fully understand what's going on and come back with any questions.

<?php
// Turn on error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

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

    $flatCity = mb_convert_case($city, MB_CASE_LOWER);

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

    return $cities;
}

// Do we need the database
if (!empty($_POST)):

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

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

    if (!empty($amt) && empty($start)):
        //We have an 'amt' value and no 'start' value

        $query = $mysqli->prepare("SELECT * FROM ft WHERE Amt = ? ");
        $query->bind_param("s", $amt);   

    else:

        if (!empty($amt) && !empty($start)):
            //We have an 'amt' value and a 'start' value 

            $query = $mysqli->prepare("SELECT * FROM ft WHERE Amt = ? AND Aar = ?");
            $query->bind_param("ss", $amt, $start);                

        elseif (!empty($start) && empty($sogn)):
            // We have a 'start' value and no 'sogn' value

            $query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ?");
            $query->bind_param("s", $start);

        else:

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

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

            if (!empty($sogn) && empty($start)):
                // We have a 'sogn' value and no 'start' value

                $query = $mysqli->prepare("SELECT * FROM ft WHERE AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ?");
                $query->bind_param("ss", $cities[0], $cities[1]);

            elseif (!empty($start) && !empty($sogn)):
                // We have a 'start' and a 'sogn' value

                $query = $mysqli->prepare("SELECT * FROM ft WHERE Aar = ? AND (AmtHerredSognGade LIKE ? OR AmtHerredSognGade LIKE ?)");
                $query->bind_param("sss", $start, $cities[0], $cities[1]);

            endif;

        endif;

    endif;

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

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

    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    <link rel="stylesheet" type="text/css" href="style.css">

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

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

<body>
    <!-- Søge felt til folketællinger -->
    <div class="container" style="width:100%;padding-top:10px;background-color:#f6f6f6;">
        <!-- HEADER START -->
        <div class="row" id="header">
            <form method="POST" style="margin-left:30px; margin-right: 30px;"> 
                <label for="amt">Amt</label> 
                <select id="amt" name="amt" style="margin-right: 20px; font-weight:bold;">
                    <option value=""></option>
                    <option value="Bornholm">Bornholm</option>
                    <option value="Frederiksborg">Frederiksborg</option>
                    <option value="Haderslev">Haderslev</option>
                    <option value="Hjørring">Hjørring</option>
                    <option value="Holbæk">Holbæk</option>
                    <option value="København">København</option>
                    <option value="Maribo">Maribo</option>
                    <option value="Odense">Odense</option>
                    <option value="Præstø">Præstø</option>
                    <option value="Randers">Randers</option>
                    <option value="Ribe">Ribe</option>
                    <option value="Ringkøbing">Ringkøbing</option>
                    <option value="Roskilde">Roskilde</option>
                    <option value="Skanderborg">Skanderborg</option>
                    <option value="Sorø">Sorø</option>
                    <option value="Svendborg">Svendborg</option>
                    <option value="Sønderborg">Sønderborg</option>
                    <option value="Thisted">Thisted</option>
                    <option value="Tønder">Tønder</option>                       
                    <option value="Vejle">Vejle</option>                       
                    <option value="Viborg">Viborg</option>
                    <option value="Aabenraa">Aabenraa</option>
                    <option value="Aalborg">Aalborg</option>
                    <option value="Aarhus">Aarhus</option>
                </select>

                <label for="aar">År</label>                
                <select id="aar" name="query_start" style="margin-right: 20px; padding-left: 30px; ">
                    <option value=""></option>
                    <option value="1787">1787</option>
                    <option value="1801">1801</option>
                    <option value="1803">1803</option>
                    <option value="1834">1834</option>
                    <option value="1835">1835</option>                        
                    <option value="1840">1840</option>
                    <option value="1845">1845</option>
                    <option value="1850">1850</option>
                    <option value="1855">1855</option>
                    <option value="1860">1860</option>
                    <option value="1860">1860</option>                        
                    <option value="1870">1870</option>
                    <option value="1860">1878</option>                        
                    <option value="1880">1880</option>
                    <option value="1890">1890</option>
                    <option value="1901">1901</option>
                    <option value="1906">1906</option>
                    <option value="1911">1911</option>
                    <option value="1916">1916</option>
                    <option value="1921">1921</option>
                    <option value="1925">1925</option>
                    <option value="1930">1930</option>                       
                    <option value="1940">1940</option>          
                </select>

                <label for="sogn">Sted, By, Sogn, Gade</label>
                <input type="text" name="sogn" id="sogn">
                <input type="submit" value="Søg">
                <input type="submit" value="Nulstil">
            </form>
        </div>  
        <!-- HEADER END -->        
    </div>

    <div class="flex-container" style="width:site-width; background-color:white;">
        <div class="flex-item" style="margin-top:10px; float:left; margin-left:20px;width: site-width; overflow:auto;">
            <div class="row" style="margin-left:10px;margin-right:10px;">

                <!-- Have we ran a query and got some results -->
                <?php if (isset($results) && $results->num_rows): ?>

                    <table id="myTable" class="table">
                        <colgroup>
                        </colgroup>

                        <tr>
                            <th>&nbsp;</th> <th>Sted Sogn Gade</th> <th>Amt</th> <th>Aar</th> <th>Gade (1860)</th> <th>Matr. nr. (1860)</th>  
                        </tr>

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

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

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

                                <tbody class='toggle' >
                                    <tr class="expander">
                                        <td><span class='toggle'></td>
                                        <td><?php echo (!empty($row->URL)) ? $link1 : link4 ?></td>
                                        <td><?php echo $row->Amt ?></td>             
                                        <td><?php echo $row->Aar ?></td>
                                        <td><?php echo $row->Gade ?></td>            
                                        <td><?php echo $row->Kvarter ?></td>            
                                    </tr>

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

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

                            <?php else: ?>
                                <!-- We have a non-collapsible row -->
        
                                <tr class="non">
                                    <td>&nbsp;</td>
                                    <!-- Område, link -->
                                    <td><?php echo $link1 ?></td>
                                    <td><?php echo $row->Amt ?></td>      
                                    <td><?php echo $row->Aar ?></td>
                                    <td colspan="2">&nbsp;</td>
                                </tr>

                            <?php endif; ?>

                        <?php endwhile; ?>

                    </table>

                <?php else: ?>

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

                <?php endif; ?>

            </div>
        </div> 
    </div>

</body>
</html>

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Ryan,

It still does not work.
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris,

I will look into it a little later. I have some working problems right now :)
0
earth man2Commented:
Don't you want to use SOUNDEX hash anyways ?
0
Peter KromanSales ExecutiveAuthor Commented:
Hi Chris,

I believe that I understand what is going on here, except from a missing $ sign :) :)
<td><?php echo (!empty($row->URL)) ? $link1 : link4 ?></td>

Just one question - the $cities[0], $cities[1] - is it a correct assumption that these represent the array of possible letters in the search. e.g. AA and Å  - and that they work together with the function cityVariations and the variable flatCity??  
And one more question - you are also using a variable $city, but I don't quite understand where that variable is set??
Just  to improve a little on my learning curve :) :)

One little thing is not working right. When hitting the 'Nulstil' (Reset) button, it shows the message corresponding to  "No results" - this also goes when I reload the page. How do I fix that?

Take a look here: http://kroweb.dk/gfdev/ft_raw2/index_test_2.php

But - I can not get it working right when putting the CSS in a separate file. It does not show the collapsible rows, and it does generally not show the page right.

What I am doing?

I take the whole style section:
       <style type="text/css">

        #header { margin-top: 10px; margin-bottom:10px; }
        tbody.toggle tr.child { display: none; background-color: #f6f6f6;}
        tbody.toggle tr:nth-child(even){ display: none; background-color: #d5d5d5;}
        tbody.toggle tr.expander { display: table-row; background-color: #f6f6f6; }
        tbody.toggle tr.non { display: table-row; background-color: white; }
        span.toggle { cursor: pointer; color: cornflowerblue; font-family: 'FontAwesome'; font-size: 16px;}
        span.toggle:hover { color: cornflowerblue; }
        span.toggle:after { content: '\f055' };
        </style>
        <style>

        table, th {

            border-bottom: 1px  #666666;
            border-top: 0px;
            border-right: 0px;
            border-left: 0px;
            border-style: solid;
            border-collapse: collapse;
            padding-top:10px;
            padding-left:20px;
            color:black;
            font-family: Serif; 
            font-size: 20px;
            font-weight: bold;
            text-align: left;




        }

        table, td {

            border: 0px  #f6f6f6;
            border-style: none;
            border-collapse: collapse;
            padding-top: 0px;
            border-bottom: 1px solid #666666;
            padding-left: 0px;
            color:black;
            font-family: arial;
            font-size: 12px;
            font-weight: normal;
            text-align: padding-left;




        }



        a:link {
            color: black;
            text-decoration: underline;
            font-weight: bold;
        }
        a:visited {
            color: #e08a94;
            text-decoration: underline;

        }
        a:hover {
            color: cornflowerblue;
            text-decoration: underline;
            font-weight:normal;
        }
        a:active {
            color: black;
            text-decoration: underline;
        }

        .table > tbody + tbody { border-top: none; }
        .table > tbody > tr > td { border-top: none; }


        </style>

Open in new window


and dump it int a file I call style.css. Then i refer to it in the head like this:
<link rel="stylesheet" type="text/css" href="style.css">

Open in new window


This is not working, but when I take the exact same lines and paste them into the head of the main file, it works nicely. Why is that?
0
Chris StanyonWebDevCommented:
Hey Peter.

Haha. Yeah - missed off the $ sign. That code is a ternary operator and is shorthand for an if /else statement.

Regarding $cities[0] and $cities[1]. When a value (city / sogn) is passed into the cityVariations($city) function, that function returns an array containing 2 elements (the [0] and [1]). The first element [0] is the city that was passed in (such as Ålborg) and the second element [1] contains the city with the special characters substituted (Aalborg). So, if we do this:

$cities = cityVariations("Ålborg");

we end up with an array called $cities:

$cities[0] = "Ålborg"
$cities[1] = "Aalborg"

We then walk that array, which basically means: for each element in an array, run a function:

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

This walks the $cities array, and passes each value into an anonymous function. The value is passed in as a parameter called $city. The function then changes this value to wrap it in % makes which is needed for the LIKE query. Effectively, that changes the $cities array into this:

$cities[0] = "%Ålborg%"
$cities[1] = "%Aalborg%"

So the values in the array are now suitable for being bound to the parameters in your query for the LIKE ? part of it.

Regarding the Nutsil button, there is no code that handles this and I'm not sure what you want to happen whens it's clicked. Currently you have 2 buttons on your form ()Sog / Nutsil) and they both doe exactly the same this - submit the form!

The code you've dropped into style.css still has all the <style> tags in. Remove all of those and just keep the CSS
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
Chris StanyonWebDevCommented:
As for the "No Results" part - sorry ... when I redid some of your code I joined two If statements into 1.

Split line 189 in my code back to 2 separate if statements:

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

And around line 257, you'll need 2 closing endifs instead of the one:

    <?php endif; ?>
<?php endif; ?>
0
NerdsOfTechTechnology ScientistCommented:
since the replace list goes left to right shouldn't the list be rearranged to this:
$arr = array(
        "ae" =>"æ",
        "oe" =>"ø",
        "aa" => "å",
        "æ" => "ae",
        "ø" => "oe",
        "å" => "aa"
);

Open in new window


instead of this array:

$arr = array(
        "æ" => "ae",
        "ø" => "oe",
        "å" => "aa",
        "ae" =>"æ",
        "oe" =>"ø",
        "aa" => "å"
);

Open in new window


Also, what about capital letters?
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
It still does not work.
I guess it need to be applied properly into your codes, what I have provided is a sample which generate the converted strings.
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks Chris, for a perfect, and perfectly understandable, solution and explanation. That is brilliant. Thanks a lot, Chris.
0
Peter KromanSales ExecutiveAuthor Commented:
Thanks a lot to everyone who have contributed to this thread. This shows that working together just "works" :)

Thanks a lot to all.
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
Query Syntax

From novice to tech pro — start learning today.