php - Search results not displaying

Question

charliehustle88 0 Newbie Poster

11 Years Ago

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!

mysql php

3 Contributors
8 Replies
416 Views
17 Hours Discussion Span
Latest Post 11 Years Ago Latest Post by broj1

Dani AI

Generated 9 Months Ago

A short troubleshooting summary and checklist that builds on and without repeating their code: the two immediate bugs called out in the thread (uninitialized $HTML and the missing fetch into $records) are the common causes, but if those are fixed and pagination still indicates matching rows while nothing prints, run the deeper checks below.

Enable PDO exceptions so SQL errors (syntax, reserved words, etc.) don’t fail silently; wrap prepare/execute in a try/catch and log the exception text. That will immediately reveal problems such as using a reserved word for a table name or a malformed query. Example pattern for debugging (not for production output) is shown below — set the attribute and catch PDOException to see the real error. (php.net)

# turn on exceptions on the connection once at startup
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
  $stmt = $dbh->prepare($sql);
  /* bind and execute */
  $stmt->execute();
} catch (PDOException $e) {
  error_log($e->getMessage()); // inspect this while debugging
}

A less-obvious cause: repeating the same named placeholder twice (e.g. :search in both predicates) can behave inconsistently across drivers/PHP versions. Use two distinct placeholders (e.g. :s1 and :s2) and bind both, or enable emulated prepares; binding once for two identical names can silently misbehave. Changing the placeholders often fixes mysteriously empty results. (stackoverflow.com)

Finally, don’t rely on rowCount() to get the total for pagination — use SELECT COUNT(*) (or fetch and count the result set when appropriate) and ensure you actually fetch rows into a variable before iterating. Verify the fetched row structure (use var_dump($row) or print_r($records[0])) — column key case (state vs State) or a missing fetch will also cause nothing to be printed. (php.net)

Quick checklist: enable PDO exceptions, inspect the exception message, confirm the query works in the DB client with the same parameters, use unique placeholders or emulate prepares, fetch rows into an array or loop with fetch(), and ensure your HTML variable is initialized and echoed after the loop.

All 8 Replies

jstfsklh211 79 Light Poster

11 Years Ago

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 356 Humble servant

11 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

broj1 356 Humble servant Featured Poster · Answer 1 · 2015-04-13T13:37:34+00:00

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 0 Newbie Poster · Answer 2 · 2015-04-13T13:54:54+00:00

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 356 Humble servant Featured Poster · Answer 3 · 2015-04-13T15:17:17+00:00

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/>';
}

broj1 356 Humble servant Featured Poster · Answer 4 · 2015-04-13T17:27:07+00:00

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

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 79 Light Poster · Answer 5 · 2015-04-13T23:08:07+00:00

@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 356 Humble servant Featured Poster · Answer 6 · 2015-04-14T06:14:29+00:00

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

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.

php - Search results not displaying

Dani AI

Recommended Answers Collapse Answers

All 8 Replies

Recommended Answers