Start Free Trial

asked on

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

ASKER

Sorry. It is MySQL

Please try like this ..

Need to change collation on the fly.

SELECT * FROM ft WHERE Aar = ? AND AmtHerredSognGade LIKE '%".$sogn."%' COLLATE utf8_unicode_ci

ASKER

Thanks Kumar,

I have tried - it does not make any difference.

Did u get anything with this or not. I mean are you are getting 1 row with this ?

ASKER

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.

I'm no expert in MySQL but what does this do ?
SELECT * FROM ft WHERE AmtHerredSognGade LIKE '%Ålborg%' COLLATE latin1_danish_ci;

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

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

ASKER

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

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

ASKER

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

Try getting the database query working first before getting the php part sorted.

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

ASKER

Thank you earth man 2 - bur the database query works just fine. I just need to add this feature.

ASKER

Thanks Chris,

I will work with your input, and get back later (just heading out now :) )

ASKER

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

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

ASKER

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

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!

ASKER

$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" }

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

ASKER

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

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.

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?

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

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.

ASKER

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.

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

ASKER

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%')

so is something like that worked for you?

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

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

ASKER

Thanks Ryan,

It still does not work.

ASKER

Thanks Chris,

I will look into it a little later. I have some working problems right now :)

Don't you want to use SOUNDEX hash anyways ?

ASKER

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?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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?

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.

ASKER

Thanks Chris, for a perfect, and perfectly understandable, solution and explanation. That is brilliant. Thanks a lot, Chris.

ASKER

Thanks a lot to everyone who have contributed to this thread. This shows that working together just "works" :)

Thanks a lot to all.