tyrone 1988 tyrone 1988 - 24 days ago 9
MySQL Question

PHP - Search results not paginating

I'm trying to get search results to paginate if there are greater than 10 items found in the database. For some reason, even though the code recognises there are more than 10 items and creates links for subsequent pages, all search results are listed on the first page only. Anyone able to help please? Code is below:

for($i = 0; $i < $terms_count; $i++)
{
$search_terms_array[$i] = trim($search_terms_array[$i]);
${"query".$i} = $this->mysqli_link->query("SELECT prod_id, prod_tags FROM table WHERE prod_tags LIKE '%" . $search_terms_array[$i] . "%'");
if(${"query".$i}->num_rows < 1)
{
$zerocount++;
}
else
{
$rows = array();
while($row = ${"query".$i}->fetch_array())
{
$rows[] = $row;
}
foreach($rows as $row)
{
$search_id_results[] = $row['prod_id'];
}
}
}
if($zerocount == $terms_count)
{
echo $this->err_handle->fetch_error_text("search_terms_0_results");
return;
}
else
{
$search_results = array_values(array_unique($search_id_results));
$search_results_count = count($search_results);
$search_page_count = ceil($search_results_count / 10);
$search_page_first_result = ($search_page - 1) * 10;
echo '<p>Go to page: ';
for($i = 1; $i <= $search_page_count; $i++)
{
if($i == $search_page)
{
echo "&nbsp;<strong>" . $i . "</strong>";
}
else
{
echo '&nbsp;<a href="index.php?section=products&amp;action=search&amp;page=' . $i . '">' . $i . '</a>';
}
}
echo '</p><p>&nbsp;</p><p>&nbsp;</p>';
for($i = 0; $i < $search_results_count; $i++)
{
$query = $this->mysqli_link->query("SELECT * FROM table WHERE prod_id='" . $search_results[$i] . "' LIMIT " . $search_page_first_result . ", 10");
while($row = $query->fetch_array())
{
echo "<h4><a href=\"index.php?section=products&amp;subsection=" . $row['prod_category'] . "&amp;prodid=" . $row['prod_id'] . "\">" . $row['prod_name'] . "</h4></a><p><img src=\"includes/images/product_images/" . $row['prod_category'] . "/" . $row['prod_pic_filename'] . "\" alt=\"\" width=\"150\" height=\"200\" /></p><p>Price: £" . $row['prod_price'] . "</p><p>" . $row['prod_code'] . "</p><input type=\"number\" name=\"prod_qty\" maxlength=\"2\" /><input type=\"submit\" name=\"add_to_basket\" value=\"Add To Basket\" /></form></p><p>&nbsp;</p><p>&nbsp;</p>";
}
}
echo '<p>Go to page: ';
for($i = 1; $i <= $search_page_count; $i++)
{
if($i == $search_page)
{
echo "&nbsp;<strong>" . $i . "</strong>";
}
else
{
echo '&nbsp;<a href="index.php?section=products&amp;action=search&amp;page=' . $i . '">' . $i . '</a>';
}
}
echo '</p><p>&nbsp;</p><p>&nbsp;</p>';
}

Answer

Ok so I found a solution to this problem and the full function is now as follows:

public function product_search($search_terms, $search_page, $search_flag_check)
{
    if($search_flag_check == "invalid")
    {
        echo $this->err_handle->fetch_error_text("invalid_ns_term");
        return;
    }
    if($search_terms == "")
    {
        echo $this->err_handle->fetch_error_text("no_search_string");
        return;
    }
    $search_terms = htmlspecialchars($search_terms);
    $search_terms = $this->mysqli_link->real_escape_string(filter_var($search_terms, FILTER_SANITIZE_FULL_SPECIAL_CHARS, FILTER_FLAG_NO_ENCODE_QUOTES));
    $search_terms_array = explode(" ", $search_terms);
    $terms_count = count($search_terms_array);
    $zerocount = 0;
    $search_id_results = array();
    for($i = 0; $i < $terms_count; $i++)
    {
        $search_terms_array[$i] = trim($search_terms_array[$i]);
        ${"query".$i} = $this->mysqli_link->query("SELECT prod_id, prod_tags FROM table WHERE prod_tags LIKE '%" . $search_terms_array[$i] . "%'");
        if(${"query".$i}->num_rows < 1)
        {
            $zerocount++;
        }
        else
        {
            $rows = array();
            while($row = ${"query".$i}->fetch_array())
            {
                $rows[] = $row;
            }
            foreach($rows as $row)
            {
                $search_id_results[] = $row['prod_id'];
            }
        }
    }
    if($zerocount == $terms_count)
    {
        echo $this->err_handle->fetch_error_text("search_terms_0_results");
        return;
    }
    else
    {
        $search_results = array_values(array_unique($search_id_results));
        $search_results_count = count($search_results);
        $search_page_count = ceil($search_results_count / 10);
        $search_page_first_result = ($search_page - 1) * 10;
        $search_page_results_limit = 10;
        if($search_page_first_result < 1)
        {
            $search_page_first_result = 1;
        }
        echo '<p>Go to page: ';
        for($i = 1; $i <= $search_page_count; $i++)
        {
            if($i == $search_page)
            {
                echo "&nbsp;<strong>" . $i . "</strong>";
            }
            else
            {
                echo '&nbsp;<a href="index.php?section=products&amp;action=search&amp;page=' . $i . '">' . $i . '</a>';
            }
        }
        echo '</p><p>&nbsp;</p><p>&nbsp;</p>';
        $search_page_upper_limit = $search_page_first_result + 9;
        if(array_key_exists($search_page_upper_limit, $search_results))
        {
            $search_results_limit = $search_page_first_result + $search_page_results_limit;
        }
        else
        {
            end($search_results);
            $search_results_limit = key($search_results);
            reset($search_results);
        }
        for($i = $search_page_first_result; $i <= $search_results_limit; $i++)
        {
            $query2 = $this->mysqli_link->query("SELECT * FROM table WHERE prod_id='" . $search_results[$i] . "'");
            $row = $query2->fetch_array();

            echo "<h4><a href=\"index.php?section=products&amp;subsection=" . $row['prod_category'] . "&amp;prodid=" . $row['prod_id'] . "\">" . $row['prod_name'] . "</h4></a><p><img src=\"includes/images/product_images/" . $row['prod_category'] . "/" . $row['prod_pic_filename'] . "\" alt=\"\" width=\"150\" height=\"200\" /></p><p>Price: £" . $row['prod_price'] . "</p><p>" . $row['prod_code'] . "</p><input type=\"number\" name=\"prod_qty\" maxlength=\"2\" /><input type=\"submit\" name=\"add_to_basket\" value=\"Add To Basket\" /></form></p><p>&nbsp;</p><p>&nbsp;</p>";

        }
        echo '<p>Go to page: ';
        for($i = 1; $i <= $search_page_count; $i++)
        {
            if($i == $search_page)
            {
                echo "&nbsp;<strong>" . $i . "</strong>";
            }
            else
            {
                echo '&nbsp;<a href="index.php?section=products&amp;action=search&amp;page=' . $i . '">' . $i . '</a>';
            }
        }
        echo '</p><p>&nbsp;</p><p>&nbsp;</p>';
    }
}

It took a bit of thinking and I was about to give up, but then I thought about using the array keys to calculate the limits for each search page and after a bit of googling on relevant PHP array functions it all fell into place quite well. I understand the code may not be very tidy right now and there may be ways to optimize/improve it, however for the time being it does the job.