php - Search results not displaying

[charliehustle88]

Hello,

The particular code I have in place is based off of search, that will then grab items from my data base and render the results in a table formatted display. pagination included.

}
$var1 = $_POST['var1'];
$query = "SELECT * FROM table WHERE state LIKE :search OR city LIKE :search";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':search', '%' . $var1 . '%', PDO::PARAM_INT);
$stmt->execute();
$count = $stmt->rowCount();
$adjacents = 2;
$records_per_page = 5;
$page = (int) (isset($_POST['page_id']) ? $_POST['page_id'] : 1);
$page = ($page == 0 ? 1 : $page);
$start = ($page-1) * $records_per_page;
$next = $page + 1;
$prev = $page - 1;
$last_page = ceil($count/$records_per_page);
$second_last = $last_page - 1;
 $pagination = "";
 if($last_page > 1){
        $pagination .= "<div class='pagination'>";
        if($page > 1)
            $pagination.= "<a href='javascript:void(0);' onClick='change_page(1);'>&laquo; First</a>";
        else
            $pagination.= "<span class='disabled'>&laquo; First</span>";
 if ($page > 1)
            $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($prev).");'>&laquo; Previous&nbsp;&nbsp;</a>";
        else
            $pagination.= "<span class='disabled'>&laquo; Previous&nbsp;&nbsp;</span>";   
        if ($last_page < 7 + ($adjacents * 2))
        {   
            for ($counter = 1; $counter <= $last_page; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class='current'>$counter</span>";
                else
                    $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($counter).");'>$counter</a>";     
            }
        }
        elseif($last_page > 5 + ($adjacents * 2))
        {
            if($page < 1 + ($adjacents * 2))
            {
                for($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                {
                    if($counter == $page)
                        $pagination.= "<span class='current'>$counter</span>";
                    else
                        $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($counter).");'>$counter</a>";     
                }
                $pagination.= "...";
                $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($second_last).");'> $second_last</a>";
                $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($last_page).");'>$last_page</a>";   
           }
           elseif($last_page - ($adjacents * 2) > $page && $page > ($adjacents * 2))
           {
               $pagination.= "<a href='javascript:void(0);' onClick='change_page(1);'>1</a>";
               $pagination.= "<a href='javascript:void(0);' onClick='change_page(2);'>2</a>";
               $pagination.= "...";
               for($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
               {
                   if($counter == $page)
                       $pagination.= "<span class='current'>$counter</span>";
                   else
                       $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($counter).");'>$counter</a>";     
               }
               $pagination.= "..";
               $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($second_last).");'>$second_last</a>";
               $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($last_page).");'>$last_page</a>";   
           }
           else
           {
               $pagination.= "<a href='javascript:void(0);' onClick='change_page(1);'>1</a>";
               $pagination.= "<a href='javascript:void(0);' onClick='change_page(2);'>2</a>";
               $pagination.= "..";
               for($counter = $last_page - (2 + ($adjacents * 2)); $counter <= $last_page; $counter++)
               {
                   if($counter == $page)
                        $pagination.= "<span class='current'>$counter</span>";
                   else
                        $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($counter).");'>$counter</a>";     
               }
           }
        }
        if($page < $counter - 1)
            $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($next).");'>Next &raquo;</a>";
        else
            $pagination.= "<span class='disabled'>Next &raquo;</span>";
 if($page < $last_page)
            $pagination.= "<a href='javascript:void(0);' onClick='change_page(".($last_page).");'>Last &raquo;</a>";
        else
            $pagination.= "<span class='disabled'>Last &raquo;</span>";
        $pagination.= "</div>";       
    }
$query = "SELECT * FROM table WHERE state LIKE :search OR city LIKE :search LIMIT $start, $records_per_page";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':search', '%' . $var1 . '%', PDO::PARAM_INT);
$stmt->execute();
$count = $stmt->rowCount();
if($count > 0)
{
    foreach($records as $row) {
 $HTML.='<div>';
        $HTML.= $row['State'];
        $HTML.='</div><br/>';
 }
}
else
{
    $HTML='No Data Found';
}
echo $HTML;
echo $pagination;
?>
</body>
</html>

The issue is within the rendering itself. After the search is performed, the results are simply not displaying. However, the search is functionally performing what it needs to do because the pagination itself is corresponding. Is there an error within my code that is preventing the data from displaying? If so, what can i do to correct this? Thank you!

[broj1]

Where do you initialize the $HTML variable so you can concatenate to it?

// initialize $HTML
$HTML = '';
foreach($records as $row) {
    $HTML.='<div>';
    $HTML.= $row['State'];
    $HTML.='</div><br/>';
}

Also the PDO::PARAM_INT value in the binding part should be PDO::PARAM_STR, since you are actually binding a string type.

$query = "SELECT * FROM table WHERE state LIKE :search OR city LIKE :search LIMIT $start, $records_per_page";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':search', '%' . $var1 . '%', PDO::PARAM_STR);

[charliehustle88]

just realized the $HTML variable was never defined, thank you. Just made the modification to the binding string. Unfortunately, I'm still coming up empty. no data is being displayed still

[broj1]

Is the generated HTML valid i.e without serious errors. Have you inspected the code (right click in the browser -> View page source). It maybe a dumb guess but the first that comes to my mind.

Edit: Just noticed that $records is not defined. Correct the code to something like:

$HTML = '';
foreach(($records = $sth->fetch(PDO::FETCH_ASSOC)) as $row) {
    $HTML.='<div>';
    $HTML.= $row['State'];
    $HTML.='</div><br/>';
}

[jstfsklh211]

according to the docs rowcount is not always accurate and you shouldn't use it to check your results

go with something more like this

$records = $sth->fetchAll(PDO::FETH_ASSOC);
if($records && count($records) {
foreach ($records as $row) {
    //html here
}
} else { //no data }

[broj1]

Also there is a small error in my and consequentely (probably) also in jstfsklh211's post. It is $stmt not $sth. Cause: copying from php.net example :-)

[broj1]

this would fail with no idex set $row['state']

@jstfsklh211
But there is an index 'state', see the query (was that a reason for downvote?). Anyway, the aim was to point out that the row ($record) was never read.

[jstfsklh211]

@broj
the way you set it up $row would have been equal to the individual fields not the records
$records in your case would have been a individual row because you used fetch instead of fetchAll

[broj1]

There is another error in the SQL statement that is actually preventing the script to execute:

$query = "SELECT * FROM table WHERE state LIKE :search OR city LIKE :search";

If you want to use table as the table name in mysql you have to enclose it in backticks since table is also a mysql reserved word. It is actualy best to avoid using reserved words for table and column names. So the query should be:

$query = "SELECT * FROM `table` WHERE state LIKE :search OR city LIKE :search";

the way you set it up $row would have been equal to the individual fields not the records
$records in your case would have been a individual row because you used fetch instead of fetchAll

@jstfsklh211
That is simply not true. Both fetch() and fetchAll() return a row, but with fetch() you have to use the PDO::FETCH_ASSOC constant which I did. Please see:

http://php.net/manual/en/pdostatement.fetch.php
http://php.net/manual/en/pdostatement.fetchall.php

I have tested the case before I posted.