Jump to content

TonyR

Members
  • Posts

    26
  • Joined

  • Last visited

Posts posted by TonyR

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

     

  2. 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?

  3. 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?

  4. 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.

  5. 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.

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

     

×
×
  • 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.