Jump to content
TonyR

Problem with form output

Recommended Posts

Hi

I am having a problem with my form output. No matter what I search I get "No matching records". Even if I am searching words that I know should return a result. Any help would be much appreciated

 

<?php                                                 

define("PERPAGE", 15);                                                // number of records on each page

/**************************************************************************************
* function to output page selection buttons
*                     
* @param int $total   total records
* @param int $page    current page number
* @return string      selection buttons html
*/
function page_selector($total, $page)
{
    if ($total==0) {
        return '';
    }
    $kPages = ceil($total/PERPAGE);
    $filler = '&nbsp;&middot;&nbsp;&middot;&nbsp;&middot;&nbsp;';
    $lim1 = max(1, $page-2);
    $lim2 = min($kPages, $page+3);
    $p = $page==1 ? 1 : $page - 1;
    $n = $page== $kPages ? $kPages : $page + 1;;
    $out = "$kPages page" . ($kPages==1 ? '' : 's') . " &emsp;";
    if ($kPages==1) {
        return $out;
    }
    $out .= ($page > 1) ? "<div class='pagipage' data-pn='$p'>Prev</div>&ensp;" : '';
    if ($page > 4) {
        $out .= "<div class='pagipage' data-pn='1'>1</div> $filler";
    }
    elseif ($page==4) {
        $out .= "<div class='pagipage' data-pn='1'>1</div>";
    } 
    for ($i=$lim1; $i<=$lim2; $i++) {
        if ($page==$i)
            $out .= "<div class='pagicurrent'>$i</div>";
        else
            $out .= "<div class='pagipage' data-pn='$i'>$i</div>";
    }
    if ($page < $kPages-3) {
        $out .= "$filler <div class='pagipage' data-pn='$kPages'>$kPages</div>";
    }
    $out .= $page < $kPages ? "&ensp;<div class='pagipage' data-pn='$n'>Next</div>" : '';
    return $out;
}

/***********************************************
**  SEARCH FOR MATCHING TVS
************************************************/
$showResults = 0;
$search = '';
if (isset($_GET['tag'])) {
    $showResults = 1;
    $search = $_GET['tag'];
    $srch = array_unique(explode(' ', trim($_GET['tag'])));
    foreach ($srch as $t) {
        $repl[] = "<span class='hilite'>$t</span>";
        $placeholders[] = '?';
        $params[] = $t;
    }
    $params[] = count($srch);
    //
    // FINDTOTAL RECORDS IN SEARCH RESULTS                                      
    //
    $res = $db->prepare("SELECT COUNT(*) as tot
                        FROM  television
                        WHERE MATCH(title,description,keywords) AGAINST(? IN BOOLEAN MODE)
                        ");
    $res->execute($params);
    $total = $res->fetchColumn();

    $page = $_GET['page'] ?? 1;
    
    
    $params[] = ($page-1)*PERPAGE;    // append parameters offset
    $params[] = PERPAGE;              // and number of records for limit clause
    //
    // GET A PAGEFUL OF RECORDS
    //
   $sql = "SELECT 
                   id
                 , title
                 , description
                 , keywords
            FROM television
            WHERE MATCH(title,description,keywords) AGAINST(? IN BOOLEAN MODE)
            ORDER BY TITLE
            LIMIT ?,? ";
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    
    if ($stmt->rowCount()==0) {
        $results = "<h3>No matching records</h3>";
    } else {
        $results = "<tr><th>Product Id</th><th>Title</th><th>Description</th><th>Tags</th><th>Edit</th></tr>\n";
        foreach ($stmt as $rec) {
            $alltags = str_replace($srch, $repl, $rec['keywords']);
            $results .= "<tr><td>{$rec['id']}</td><td>{$rec['title']}</td><td>{$rec['description']}</td><td>$alltags</td>
            <td><a href='?action=edit&id={$rec['id']}'><img src='edit-icon.png' alt='edit'></a></td>
            </tr>\n";
        }
    }
}
?>
<div id='title'>Television Search</div>
<form id='form1'>
    <fieldset>
    <legend>Search for tags</legend>
        <input type='text' name='tag' size='50' value='<?=$search?>' placeholder='Search for...' >
        <input type="hidden" name="page" id="page" value="1">
        <input type="hidden" name="action" id="page" value="search">
        <input type="submit" name="btnSub" value="Search">
    </fieldset>
    <div id='formfoot'></div>
</form>
<?php if ($showResults) { ?>
         <div class="paginate_panel">
             <?=page_selector($total, $page)?>
         </div>
         <table border='1'>
             <?=$results?>
         </table>
<?php } ?>

 

Share this post


Link to post
Share on other sites

Put your PDO into exception throwing mode and try again.

Share this post


Link to post
Share on other sites

Thank you requinix for the reply. Excuse my ignorance but do you mean this:

<?php

    $host = 'localhost';
    $username = 'root';
    $password = '';
    $database = 'tonyruttle';

    $dsn = "mysql:dbname=$database; host=$host; charset=utf8";

    $db = new pdo($dsn, $username, $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);

If you do then I'm still getting the same results.

Share this post


Link to post
Share on other sites

Ah, okay, found the bug.

Check your $params array more closely. What each item inside will be. Compare with the query that tries to use it.

Share this post


Link to post
Share on other sites

My php knowledge is very limited (it is improving) but unfortunately this bug is too difficult for me.

I wish I had seen your post this morning before I went to college. Then I could've asked my teacher for some help. I will tell him tomorrow morning that the issue is with the $params and hopefully he will help me. If not, I'm sure I will be back here.

Thank you requinix.

Share this post


Link to post
Share on other sites

The $params are filling the placeholders when the query gets executed. They need to match with what the query is trying to do.

Just before you execute the query, dump out the contents of $params and the SQL query itself. Compare the two to make sure $params has the values the query needs it to have.

Share this post


Link to post
Share on other sites

Unfortunately my teacher would not fix the code for me so I am back. After doing some reading online I read that I cannot use placeholders for table names, column names or limit values. So LIMIT ?,? is not correct as well as the $param issue that you have mentioned. Is that correct?

Share this post


Link to post
Share on other sites

You can't use them for column and table names, they got that  correct, but I have never had a problem when using LIMIT ?,?

Share this post


Link to post
Share on other sites
1 hour ago, TonyR said:

Unfortunately my teacher would not fix the code for me so I am back. After doing some reading online I read that I cannot use placeholders for table names, column names or limit values. So LIMIT ?,? is not correct as well as the $param issue that you have mentioned. Is that correct?

wherever you read that, it (should have) included the information - 'when using emulated prepared queries and supplying the values via an array to the ->execute() method call'. since you are not using emulated prepared queries, you can use place-holders for the limit values.

 

Share this post


Link to post
Share on other sites

Thank you Barand and mac_gyver for your replies. I guess I’ll go back to looking at the $params and not any other issues with the code.

Share this post


Link to post
Share on other sites

One thing at a time.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.