Jump to content

Problem with form output


TonyR

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

 

Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
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?

Link to comment
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.

 

Link to comment
Share on other sites

  • 4 weeks later...

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.";
	}

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.