[SOLVED] GET results from '$char' variable

[pescott]

I am using a search tool on a page displaying results from a MySQL db, where one can click a letter to display all entries from a certain field starting with that specific letter. See below. The data is entered in a form.

<table id="subtable">
<form name="data_search" id="search" method="get" action="">
<tr>
    	<td colspan="2" align="right">Vul een zoekterm in: <input type="text" size="27" name="textSearch" /> binnen de categorie:</td>
	<td width="20"> </td>
	<td><select name="selectSearch">
		<option value="plaatsnaam">- selecteer een optie -     </option>
		<option value="straatnaam">Straatnaam</option>
		<option value="wijk">Wijk</option>
		<option value="plaatsnaam">Plaatsnaam</option>
		<option value="type_woning">Type woning</option>
		<option value="vraagprijs">Vraagprijs</option>
		<option value="label">Energielabel</option>
    		</select>
	</td>
    </tr>
<tr>
	<td width="40%"> </td>
    	<td align="right">Hoe wilt u zoeken?</td>
	<td width="20"> </td>
    	<td><select name="matchCase">
		<option value="similar">- selecteer een optie -     </option>
		<option value="exact">Exacte zoekterm      </option>
		<option value="similar">Bevat de zoekterm</option>
		</select>
	</td>
</tr>
<tr>
    	<td colspan="2" align="right">Hoe wilt u de resultaten weergegeven hebben?</td>
	<td width="20"> </td>
    	<td><select name="sortOrder">
		<option value="asc">- selecteer een optie -     </option>
		<option value="asc">oplopend</option>
		<option value="desc">aflopend</option>
		</select>
	</td>
  </tr>
  <tr>
	<td colspan="2" align="right" height="44" valign="bottom"><input type="submit" name="submitSearch" value="Zoekopdracht ingevuld? Zoek nu!" /></td>
	<td width="20"> </td>
	<td height="44" valign="bottom"><input type="submit" name="submitViewAll" value="Alle resultaten" /></td>
</tr>
<tr>
	<td colspan="4" align="center" height="44" valign="bottom">Of klik direct op de eerste letter van de plaatsnaam om de resultaten te bekijken:</td>
</tr>
<tr>
    	<td colspan="4" align="center"><?php
                echo "| ";
                for ($i=65; $i<=90; $i++)
                     echo "<a href=?char=".chr($i+32).">".chr($i). "</a> | ";
                                        ?></td>
</tr>
<tr>
	<td colspan="4"><hr></td>
    </tr>
</form>
</table>

<?php
// how many rows do we have in the database

if (isset($_GET['submitSearch']))
{
        $textSearch = $_GET['textSearch'];
        $selectSearch = $_GET['selectSearch'];
        $matchCase = $_GET['matchCase'];
        $sortOrder = $_GET['sortOrder'];

        if ($matchCase=="exact")
		{
                $query = "SELECT * from objects
                          WHERE $selectSearch = '$textSearch'
                          ORDER BY plaatsnaam $sortOrder
                          LIMIT $offset, $rowsPerPage";
		}
        else
		{
                $query = "SELECT * from objects
                          WHERE $selectSearch like '%$textSearch%'
                          ORDER BY plaatsnaam $sortOrder
                          LIMIT $offset, $rowsPerPage";
		}
}
        elseif (isset($_GET['char']))
		{
                 $char = $_GET['char'];
                 $query = "SELECT * FROM objects WHERE plaatsnaam like '$char%' ORDER BY plaatsnaam ASC LIMIT $offset, $rowsPerPage";
		}
        else
		{
              $query = "SELECT * FROM objects ORDER BY plaatsnaam ASC LIMIT $offset, $rowsPerPage";
		}

// Retrieve all the data from the "objects" table
$result = mysql_query($query) or die(mysql_error());
$recordcount = mysql_num_rows($result);
if ($recordcount==0)
    echo "Helaas, er zijn <strong>GEEN</strong> resultaten die aan geze zoekopdracht voldoen.";
if ($recordcount==1)
    echo "Er is <strong>$recordcount</strong> resultaat:";
if ($recordcount > 1)
    echo "Er zijn <strong>$recordcount</strong> resultaten:";
    
// Define your colors for the alternating rows
$color1 = "#eeeeee";
$color2 = "#dcdcdc";
$row_count = 0;

For some reason, even though there are entries in the db that start with, say, 'J', or 'L', the query outputs this line:

Helaas, er zijn GEEN resultaten die aan geze zoekopdracht voldoen.

and subsequently, there are no results displayed.

Honestly, why does this happen? When all results are displayed from the 'show all' query, the results I am looking for are displayed amngst all others.

Help appreciated!

 

[ksupport]

Replace:

$query = "SELECT * FROM objects WHERE plaatsnaam like '$char%' ORDER BY plaatsnaam ASC LIMIT $offset, $rowsPerPage";

 

With:

$query = "SELECT * FROM objects WHERE plaatsnaam REGEXP CONVERT( _utf8 '$char(.*)'
USING latin1 )  ORDER BY plaatsnaam ASC LIMIT $offset, $rowsPerPage";

 

I used Regex in the query. change _utf8 & latini1 depends upon your db architecture.

 

 

[pescott]

Ok, that did not fix it, but it is an interesting thought...

 

Most of the fields were utf8, I changed everyting in the DB WebAdmin and now it works! Excellent work!!

[ksupport]

its ok