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 KromanSenior Proposal SpecialistAsked:
Who is Participating?
 
Chris StanyonConnect With a Mentor Commented:
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
 
Peter KromanSenior Proposal SpecialistAuthor 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Peter KromanSenior Proposal SpecialistAuthor Commented:
Thanks Kumar,

I have tried - it does not make any difference.
0
 
Pawan KumarDatabase ExpertCommented:
Did u get anything with this or not. I mean are you are getting 1 row with this ?
0
 
Peter KromanSenior Proposal SpecialistAuthor 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 ChongCommented:
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 KromanSenior Proposal SpecialistAuthor 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 ChongCommented:
<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 KromanSenior Proposal SpecialistAuthor 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 StanyonCommented:
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 KromanSenior Proposal SpecialistAuthor Commented:
Thank you earth man 2 - bur the database query works just fine. I just need to add this feature.
0
 
Peter KromanSenior Proposal SpecialistAuthor Commented:
Thanks Chris,

I will work with your input, and get back later (just heading out now :) )
0
 
Peter KromanSenior Proposal SpecialistAuthor 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 StanyonCommented:
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 KromanSenior Proposal SpecialistAuthor 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 StanyonCommented:
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 KromanSenior Proposal SpecialistAuthor 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 StanyonCommented:
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 KromanSenior Proposal SpecialistAuthor 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 StanyonCommented:
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
 
PortletPaulfreelancerCommented:
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 ChongCommented:
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 KromanSenior Proposal SpecialistAuthor 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 ChongCommented:
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 KromanSenior Proposal SpecialistAuthor 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 ChongCommented:
so is something like that worked for you?

SELECT * FROM ft WHERE Aar = ? AND (AmtHerredSognGade LIKE '%Ã¥lborg%' OR AmtHerredSognGade LIKE '%aalborg%')
0
 
Chris StanyonCommented:
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 KromanSenior Proposal SpecialistAuthor Commented:
Thanks Ryan,

It still does not work.
0
 
Peter KromanSenior Proposal SpecialistAuthor 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 KromanSenior Proposal SpecialistAuthor 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 StanyonCommented:
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 ChongCommented:
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 KromanSenior Proposal SpecialistAuthor Commented:
Thanks Chris, for a perfect, and perfectly understandable, solution and explanation. That is brilliant. Thanks a lot, Chris.
0
 
Peter KromanSenior Proposal SpecialistAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.