Problem with form output

TonyR · February 27, 2019

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 } ?>

requinix · February 27, 2019

Put your PDO into exception throwing mode and try again.

TonyR · February 27, 2019

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.

requinix · February 27, 2019

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.

TonyR · February 28, 2019

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.

requinix · February 28, 2019

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.

TonyR · March 2, 2019

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?

Barand · March 2, 2019

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

mac_gyver · March 2, 2019

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.

TonyR · March 3, 2019

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.

requinix · March 3, 2019

One thing at a time.

TonyR · March 28, 2019

Apologies to start this up again but I had some personal problems at home which meant I had to stop my coursework. My college had made arrangements for me to restart this after the Easter holidays but I felt I wanted to get back into this.

I really have racked my brains trying to figure out the issue with the $params but to no avail. Any more help please?

Psycho · March 28, 2019

First, I would strongly suggest that you first use more appropriately named variables. Don't create a generic variable and then try and reuse it. What does "$params" represent? Parameters obviously, but parameters for "what"? In this case $params is an array that keeps getting additional values appended to it - and those values are logically different "types" of values. Also, a prepared query will expect an array with the same number of parameters as placeholder in the query.

Second, output your variables to the page to "see" what they contain. $params does not contain what you think it does when you are using it.

TonyR · March 29, 2019

Thank you Psycho for your reply. What I am trying to do is join two pieces of code together to make a search form. Both Code A and Code B work but what I would like to do is to add Code B to Code A. I have tried to remove parts of Code A and insert the code from Code B which is where I am at in the original post above. No matter what I search I get "No matching records".

CODE A

<?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 PRODUCTS 
************************************************/
$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 (
                                SELECT id
                                FROM (
                                     SELECT v.id
                                          , v.title
                                          , GROUP_CONCAT(tag SEPARATOR ' ') as alltags
                                          FROM product v
                                                JOIN
                                               product_tag t ON v.id = t.product_id
                                          GROUP BY v.id    
                                     ) v
                                        JOIN
                                     product_tag t ON v.id = t.product_id
                                WHERE tag IN (" . join(",", $placeholders) . ")
                                GROUP BY id
                                HAVING COUNT(t.tag) = ?
                             ) found;
                        ");
    $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
                 , alltags
            FROM (
                 SELECT v.id
                      , v.title
                      , GROUP_CONCAT(tag SEPARATOR ' ') as alltags
                      FROM product v
                            JOIN
                           product_tag t ON v.id = t.product_id
                      GROUP BY v.id    
                 ) v
                    JOIN
                 product_tag t ON v.id = t.product_id
            WHERE tag IN (" . join(",", $placeholders) . ")
            GROUP BY id
            HAVING COUNT(t.tag) = ?
            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>Tags</th><th>Edit</th></tr>\n";
        foreach ($stmt as $rec) {
            $alltags = str_replace($srch, $repl, $rec['alltags']);
            $results .= "<tr><td>{$rec['id']}</td><td>{$rec['title']}</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'>Product 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 } ?>

CODE B

	$txt = $_GET['search'] ?? '';

	$whereclause = '';
	$search_values = [];

	if (isset($_GET['search'])) {
		$where = ['active <> 0'];
		$search_values = [];

        if ($_GET['search'] != '') {
		$words = array_filter(explode(' ', $_GET['search']));
		$badft = array_filter($words, function($v) { return strlen($v) <= 3;});
		$goodft = array_filter($words, function($v) { return strlen($v) > 3;});
		array_walk($goodft, function(&$v, $k) {						// add the +'s to the search tags
			$v = '+'.$v;
	});

	if ($goodft) {
		$srch = join(' ', $goodft);
		$where[] = "MATCH (title, description, keywords) AGAINST (? IN BOOLEAN MODE)";
		$search_values[] = $srch;
	}

	foreach ($badft as $w) {
		$w = trim($w, ', ');
		$where[] = "((title LIKE ?) OR (description LIKE ?) OR (keywords LIKE ?))";
		array_push($search_values, "%$w%", "%$w%", "%$w%");
	}
	}

	if ($where) {
		$whereclause = "WHERE " . join(' AND ', $where);
	}
	}

	$count = $db->prepare("SELECT COUNT(*)
		FROM television
		$whereclause
			");

	$count->execute( $search_values );
	$total_recs = $count->fetchColumn();
	$page = isset($_GET['page']) ? $_GET['page'] : 1;

	if (intval($page) == 0) $page = 1;
	$offset = ($page - 1) * PERPAGE;
	$limit = PERPAGE;
	array_push($search_values, $offset, $limit);		// add offset and limit to the search parameters
    
	$stmt = $db->prepare("SELECT image
				, title
				, description
				, url
				, id
			FROM television
			$whereclause
			ORDER BY upload DESC
			LIMIT ?,?    
				");

	$stmt->execute( $search_values );
	$search_results = '';
	if ($txt) {
		foreach ($stmt as $row) {
			$search_results .= '<li>
						<a href="' . 'product.php?id=' . $row['id'] . '"><img src="' . $row['image'] . '" class="thumbnail" alt="' . $row['title'] . '"></a>
						<div>
							<h1><a href="' . 'product.php?id=' . $row['id'] . '">' . $row['title'] . '</a></h1>
							<p>' . $row['description'] . '</p>
						</div>
					</li>' . PHP_EOL;
	}

	$search_msg = count($rows) > 0 ? "$total_recs products found" : "Please check your spelling or try a more generic search term";

	}

	else {
		$total_recs = 0;
		$search_msg = "Please enter your search text.";
	}

Psycho · March 29, 2019

So, now you are going to post the REAL code? How nice of wasting people's time. You still haven't done what has been suggested multiple times - output your variables so you can SEE what they are. Instead of putting your query string directly in the prepare() statement, create it as a variable. Also, the full query that you are now showing us could be the problem. Even if there is matching data an error in the JOIN criteria could prevent any results.

Replace this for the relevant section in your code and look at the results to see if the query and param values are what you think they are.

    //
    // FINDTOTAL RECORDS IN SEARCH RESULTS                                      
    //
    $placeholderStr = join(",", $placeholders);
    $query = "SELECT COUNT(*) as tot
                        FROM (
                                SELECT id
                                FROM (
                                     SELECT v.id
                                          , v.title
                                          , GROUP_CONCAT(tag SEPARATOR ' ') as alltags
                                          FROM product v
                                                JOIN
                                               product_tag t ON v.id = t.product_id
                                          GROUP BY v.id    
                                     ) v
                                        JOIN
                                     product_tag t ON v.id = t.product_id
                                WHERE tag IN ({$placeholderStr})
                                GROUP BY id
                                HAVING COUNT(t.tag) = ?
                             ) found;";
    echo "<b>Query:</b><pre>{$query}</pre><br>\n";
    echo "<b>Params:</b><pre>".print_r($params, true)."</pre><br>\n";
    $res = $db->prepare($query);
    $res->execute($params);

Sign In

Problem with form output

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information