MySQL/PHp Filter Results List Error

Question

I have a legacy PHP script which creates a list of resources from information stored in a MySQL database. Users can search the list or filter by the first letter in the title (this is stored as a column in the database). You can see it in action here: http://lib.skidmore.edu/library/index.php/researchdatabases). The script works fine except for one resource, FT.com, which appears incorrectly when users filter by letter. Regardless of the letter selected, its entry will be either at the top or the bottom. Note that in the unfiltered view FT.com is in proper alphabetical order. My first thought was to look at the database entry, but everything looks fine.

My hypothesis is a variable is not being set correctly. The way the script works is the top half of it contains a web form. The PHP below then picks up the input and assigns it to the variable $searchletter.

A combination of while loops and mysqli queries then retrieves and displays the results. Interestingly when the $searchletter = !empty line is commented out, the entire list disappears for the unfiltered view except for the FT.com entry (see this test script for an example: http://lib.skidmore.edu/library/search_dbs2.php). Otherwise I can see anything in neither the script nor the database which might be causing the observed behavior. Is my suspicion correct?

Here is the code. I've included everything except the connection information so you can see how it all works.

$search=(isset($_GET['search']) ? $_GET['search'] : null);
$search = !empty($_GET['search']) ? $_GET['search'] : 'default';
$search= addslashes($search); 
$searchletter=(isset($_GET['searchletter']) ? $_GET['searchletter'] : null);
$searchletter = !empty($_GET['searchletter']) ? $_GET['searchletter'] : 'default';

var_dump ($_GET['searchletter']);

$con=mysqli_connect(DB_HOST,WEBMISC_USER,WEBMISC_PASS,DB_NAME);
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
if ($search == "default" && $searchletter == "default"){
$result = mysqli_query($con,"SELECT title,summary,url,coverage,format FROM dbs");

//This while loop creates the inital A to Z list.
while($row = mysqli_fetch_array($result))
  {

$url=$row['url'];
$title=$row['title'];
$summary=$row['summary'];
$coverage=$row['coverage'];
$format=$row['format'];

echo <<<HTML
        <p><h6><a href="$url">$title</a></h6>
        <br />$summary</p>
HTML;
 } 
}
else {
$result = mysqli_query($con,"SELECT title,summary,url,coverage,format,fletter FROM dbs where title like '%$search%' or summary like '%$search%' or fletter = TRIM('$searchletter')");

//This block creates the filtered and searched version of the list.
while($row = mysqli_fetch_array($result))
  {
$url=$row['url'];
$title=$row['title'];
$summary=$row['summary'];
$coverage=$row['coverage'];
$format=$row['format'];

echo <<<HTML
        <p><h6><a href="$url">$title</a></h6>
        <br />$summary</p>
HTML;
  }   
mysqli_close($con);

the "fine" may be confusing. See if you're able to run trim function on the result before sorting it. it will remove whitespace characters from the name. Got me too many times when client was copy-pasting things from excel file ;) also, this can be cleaned up in mysql with TRIM, see: stackoverflow.com/questions/1504962/… for an example — Jan Myszkier
– Jan Myszkier, Commented Jul 25, 2018 at 18:44
I tried using TRIM (fletter) in the SELECT statement and fletter = TRIM('$searchletter'), but it didn't help. fletter is the database column where the first letter of the title is stored. — Mike P
– Mike P, Commented Jul 25, 2018 at 19:20

Jan Myszkier · Accepted Answer · 2018-07-26 07:03:51Z

0

the first serious problem with this script is that it seems to be prone to MySQL Injection, the most serious problem of them all. (but I may be wrong). Please consider switching this code to PDO and its prepared statements and bindParam method.

the second is that, in the FORM you either support search OR letter (but not both) BUT you use both in mysql query. you should split the result fetching from

$result = mysqli_query($con,"SELECT 
title,summary,url,coverage,format,fletter 
FROM dbs 
where title like
'%$search%' or summary like '%$search%' or fletter = '$searchletter'");

into if/else statement:

if(!empty($search)){
    $result = mysqli_query($con,"SELECT 
    title,summary,url,coverage,format,fletter 
    FROM dbs 
    where title like
    '%$search%' or summary like '%$search%'");
} elseif(!empty($searchletter)){
    $result = mysqli_query($con,"SELECT 
    title,summary,url,coverage,format,fletter 
    FROM dbs 
    where fletter = '$searchletter'");
}

this will not fire BOTH cases on the search and should return more reliable result based on your selection.

EDIT: after you added more code, it's clear that every "unset by user" field has value of "default". which means: whatever letter you chose, the "seachphrase" will be set to "default" and "default" appears to be a part of FT.com summary field (you can see this word in search results). Again: splitting the query into two cases will solve this, so "default" word is never used in the search query.

edited Jul 26, 2018 at 7:03

answered Jul 25, 2018 at 20:05

Jan Myszkier

2,7541 gold badge18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mike P Over a year ago

There is more to the script than what I posted. Search is handled by a different variable, SQL query, and while loop. There is also a separate form for the search, which sits above the search letter drop down menu. Since that bit is working fine, I didn't post it as it didn't seem relevant to the question. This script is part of a legacy system we will eventually be getting rid of.

Jan Myszkier Over a year ago

still, please split the searchletter vs search mysql query and if that doesn't help please post how you determine $search variable value so we can have a better picture.

Jan Myszkier Over a year ago

and I'm asking for search becasue if search is determined the same way as searchletter , then it will always get a value of "default" which, exists in the "summary" of the FT.com article ;)

Mike P Over a year ago

I included everything except the connection information into my original post.

Mike P Over a year ago

Thanks for your help. That worked. I also added a third else block to display the initial, unfiltered view.

Collectives™ on Stack Overflow

MySQL/PHp Filter Results List Error

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related