Minimum characters in search setting, and pagination.

strago · May 3, 2013

I'm trying to only let searches work if they have three or more characters.

if ($search2 == NULL){

spits out an error if you search with no characters. If I replace it with

if (strlen($search2) < 3){

it spits out an error if there are no characters, but still goes through if you do a search with just one or two characters.

For breaking the results up in to pages when a search has over 20 results...

$query = "SELECT COUNT(*) {$keywords}";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$total_records = $row[0];
$total_pages = ceil($total_records / 20);
for ($i=1; $i<=$total_pages; $i++) {
            echo "<a href='search.php?search=$value&page=".$i."'>".$i."</a>";
};

only generates one link, to page one that's the exact same results. Change 1 in the URL to say 2 and you then get page two.

<?php
require 'config.php';

mysql_connect ($dbhost, $dbusername, $dbuserpass);
mysql_select_db($dbname) or die('Cannot select database');

if(isset($_GET['search']))
{
$search = $_GET['search'];
}
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $search);
$keywords = explode(" ", $search);
$keywords = array_diff($keywords, array(""));
if ($search == NULL or $search == '%'){
} else {
for ($i=0; $i<count($keywords); $i++) {

/* construct and run our query */
if (isset($_GET["page"]))
{
$page  = $_GET["page"];
}
else
{
$page=1;
};
$start_from = ($page-1) * 20;

$search2 = "SELECT * FROM clickbank " . "WHERE Title LIKE '%".$keywords[$i]."%'". " ORDER BY Title ASC LIMIT $start_from, 20";
}
//Store the results in a variable or die if query fails
$result = mysql_query($search2);
}
if ($search2 == NULL or $search2 == '%'){
} else {
//Count the rows retrived
$count = mysql_num_rows($result);
}
echo "<html><head><title>Your Title Here</title></head>";
echo "<body onLoad=\"self.focus();document.searchform.search.focus()\"><center><br /><form name=\"searchform\" method=\"GET\" action=\"search.php\"> <input type=\"text\" name=\"search\" size=\"20\" TABINDEX=\"1\" /> <input type=\"submit\" value=\"Search\" /></form>";
if ($search2 == NULL) {
} else {
echo "You searched for <b><FONT COLOR=\"blue\">";
foreach($keywords as $value) {
   print "$value ";
}
echo "</font></b>";
}
echo "<p> </p><br /></center>";
//if ($search2 == NULL){
if (strlen($search2) < 3){
echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";
}
 elseif ($search2 == '%'){
echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";
//If no results are returned print it
} elseif ($count <= 0){
echo "<center><b><FONT COLOR=\"red\">Your query returned no results from the database.</font></b><br /></center>";
//ELSE print the data in a table
} else {
//Table header
echo "<TABLE border=1><tr><td><b>Link</b></td><td><b>Gravity</b></td><td><b>InitialEarningsPerSale</b></td><td><b>AverageEarningsPerSale</b></td><td><b>ActivateDate</b></td></TR><TR>";

while($row = mysql_fetch_array($result))
{
echo "<tr><td>".$row['Link']."</td><td>".$row['Gravity']."</td><td>".$row['InitialEarningsPerSale']."</td><td>".$row['AverageEarningsPerSale']."</td><td>".$row['ActivateDate']."</td></tr>";
$row_count++;
}
}
echo "</table>";

if ($search2 == NULL or $search2 == '%') {
} else {
mysql_free_result($result);
}
$query = "SELECT COUNT(*) {$keywords}";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$total_records = $row[0];
$total_pages = ceil($total_records / 20);
for ($i=1; $i<=$total_pages; $i++) {
            echo "<a href='search.php?search=$value&page=".$i."'>".$i."</a>";
};
require "footer.php";
?>

requinix · May 3, 2013

Do you know what the $search2 variable even is? Read the code and see what value it actually has.

strago · May 3, 2013

$search2 = "SELECT * FROM database " . "WHERE Title LIKE '%".$keywords[$i]."%'". " ORDER BY Title ASC LIMIT $start_from, 20";

requinix · May 3, 2013

That's right: it's a SQL query. Your ==NULL and =="%" and strlen() checks on it don't make any sense.

strago · May 3, 2013

if (strlen($search) < 3){

did it.

Any one know how to get this to show the correct number of pagnation links??

$query = "SELECT COUNT(*) {$keywords}";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$total_records = $row[0];
$total_pages = ceil($total_records / 20);
for ($i=1; $i<=$total_pages; $i++) {
echo "<a href='search.php?search=$value&page=".$i."'>".$i."</a>";

If I have

$query = "SELECT COUNT(*) FROM databasename";

the links show up as if it think's it's trying to show the whole database.

$query = "SELECT COUNT(*) {$keywords}";

only generates one link, to page one that's the exact same results. Change 1 in the URL to say 2 and you then get page two.

Edited May 3, 2013 by strago

requinix · May 3, 2013

First things first: have you tried searching with more than one keyword?

strago · May 3, 2013

It does the same thing.

I'm gussing {$keywords} should be something else, but what ever I put there, doesn't generate the correct number of pages.

Edited May 3, 2013 by strago

requinix · May 3, 2013

So there's currently two problems:

1. You're searching on the last keyword. Because you put it in $search2 and overwrite that for every keyword. Only the last one will stick.

2. To get the count do

SELECT COUNT(*) FROM table WHERE whatever conditions you have

The table name and the conditions are the exact same as in the regular query; the only differences are the COUNT(*) and not using the ORDER BY (doesn't matter) and LIMIT (you want to count everything).

strago · May 4, 2013

$query = "SELECT COUNT(*) FROM table WHERE Title LIKE '%".$keywords[$i]."%'". " ORDER BY Title ASC LIMIT $start_from, 10";
$query = "SELECT COUNT(*) FROM table WHERE Title LIKE '%".$keywords[$i]."%'";

both generate page numbers like it's counting the complete table!!

Is there any way to fix #1 so if the search has multiple keywords, it just has to have one of the keywords, any keyword in the search and it shows up? I even tried the original search script before my changes, and it still did it!

Edited May 4, 2013 by strago

QuickOldCar · May 4, 2013

I just wrote this function up for you so can do more than one word.

I know there are simpler ways to do this, but I did it this way.

single or more search terms

single or multiple fields

can set the minimum character count for each word, any that are less never get into the actual mysql query

option to use AND/OR to get different results

Is a demo there with it so can try it out and see the results it gets

To use this, check that the function does not return an empty value, and be sure to filter/escape anything going into the mysql query

<?php
//build query searches from multiple words

function querySearch($search, $field, $search_term_length = NULL, $and_or = NULL)
{
    //check if search is not empty
    if ($search == '') {
        return '';
        exit();
    }
    
    if ($field == '') {
        return '';
        exit();
    }
    
    if (is_array($field)) {
        $field = implode(",", $field);
    }
    //only do the search if word has more than 3 characters unless set different function
    if (is_null($search_term_length)) {
        $search_term_length = "3";
    }
    
    if (is_null($and_or)) {
        $and_or = "OR";
    }
    
    //create an array to eliminate errors checking array
    $search_exploded = array();
    
    //create a new array exploding the spaces of multiple search terms
    $search_exploded = explode(" ", $search);
    
    //if is multiple search terms
    if (count($search_exploded > 0)) {
        //loop through array
        foreach ($search_exploded as $search_term) {
            //only with certain amount of characters
            if (strlen($search_term) >= $search_term_length) {
                //create new array and lower all the words
                $search_terms[] = strtolower($search_term);
            }
        }
        
    } else {
        //if is a single search term
        $search_terms = strtolower($search);
    }
    
    //build the search query
    $construct_query = array();
    if ($search_terms != '' && !is_array($search_terms)) {
        $construct_query = "WHERE (" . $field . ") LIKE '%" . $search_terms . "%'";
    } elseif (is_array($search_terms) && count($search_terms) > 0) {
        $construct_query[] = "WHERE (" . $field . ") LIKE '%" . $search_terms['0'] . "%'";
        unset($search_terms['0']);
        
        foreach ($search_terms as $additional_term) {
            $construct_query[] = " $and_or (" . $field . ") LIKE '%" . $additional_term . "%'";
        }
        
    }
    
    $query_term = '';
    
    if (count($construct_query) > 0) {
        $query_term = implode('', $construct_query);
    }
    
    return $query_term;
} //end querySearch function


//usage
$search = trim($_GET['search']);
if (isset($_GET['search']) && $search != '') {
    //run function to insert into query
    echo querySearch($search, "title", 2, "AND"); //search term,database field,minimum character length,and/or for different results
    echo "<br />";
    echo querySearch($search, "cars", 3, "OR");
    echo "<br />";
    echo querySearch($search, array("cats","dogs"), 3, "OR"); //using more than one field with an array
} else {
    echo "Insert a search term";
}
?>

<form action="" method="get">
<input type="text" name="search" value=""/>
<input type="submit" value="Search"/>
</form>

Edited May 4, 2013 by QuickOldCar

mac_gyver · May 4, 2013

you need to build the sql WHERE whatever conditions you have in a php variable so that you can use that WHERE condition in both queries. right now $keywords is an array, it's not a complete and valid sql conditional statement.

if you want to find row(s) with ANY of the keywords in the title, you need to form a query using OR logic. if you want to find row(s) with ALL the keywords in the title, you need to form a query using AND logic.

for example, if you enter two keywords in the search and want to match either keyword, your queries would need to look like -

SELECT COUNT(*) FROM your_table WHERE title LIKE '%keyword1%' OR title LIKE '%keyword2%'

SELECT * FROM your_table WHERE title LIKE '%keyword1%' OR title LIKE '%keyword2%' ORDER BY title ASC LIMIT $start_from, $rows_per_page

correctly building the WHERE condition (in bold) is what your immediate task should be.

mac_gyver · May 4, 2013

slightly off topic, but you need to limit the $page value so that it is between 1 and $total_pages (inclusive) so that someone cannot produce query errors by feeding in a zero or a negative number or feeding in a value greater than the number of pages and wasting time running a query that won't return any rows.

the code to produce $total_pages needs to come before the code producing $page and then you need to add a little logic to limit the value in $page.

strago · May 5, 2013

This got the page numbers correct, along with an error being generated when some one tries to enter a page number higher than the number of actual pages...

$search = $_GET['search'];
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $search);
$keywords = explode(" ", $search);
$keywords = array_diff($keywords, array(""));
for ($i=0; $i<count($keywords); $i++)
$query = "SELECT COUNT(*) FROM xxxx WHERE Title LIKE '%".$keywords[$i]."%'". " ORDER BY Title ASC";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$total_records = $row[0];
$total_pages = ceil($total_records / 20);

$page = $_GET["page"];
if ($page > $total_pages)
{
//Spits out the $count <= 0 error.
echo "";
}

for ($i=1; $i<=$total_pages; $i++) {
echo "<a href='search.php?search=$value&page=".$i."'>".$i."</a> ";

QuickOldCar, how do I get your code working in the main script?

It spits out for example

SELECT * FROM xxxxx WHERE Title LIKE '%google%' AND Title LIKE '%facebook%'
SELECT * FROM xxxxx WHERE Title LIKE '%google%' OR Title LIKE '%facebook%'
SELECT * FROM xxxxx WHERE Title LIKE '%google%' OR Title LIKE '%facebook%'

In the main search script, where it shows the keywords, it spits out

You searched for facebook google facebook

at search.php?search=google+facebook

so I'm guessing I might be close. I'm guessing the construct_query area might need a litle changing.

<?php
require 'config.php';

mysql_connect ($dbhost, $dbusername, $dbuserpass);
mysql_select_db($dbname) or die('Cannot select database');

if(isset($_GET['search']))
{
$search = $_GET['search'];
}
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $search);
$keywords = array();
$keywords = explode(" ", $search);
$keywords = array_diff($keywords, array(""));


    if (is_null($search_term_length)) {
        $search_term_length = "3";
    }
    if (is_null($and_or)) {
        $and_or = "OR";
    }

    //if is multiple search terms
    if (count($keywords > 0)) {
        //loop through array
        foreach ($keywords as $search_term) {
            //only with certain amount of characters
            if (strlen($search_term) >= $search_term_length) {
                //create new array and lower all the words
                $keywords[] = strtolower($search_term);
            }
        }
            }
else {
        //if is a single search term
        $keywords = strtolower($search);
    }


if ($search == NULL or $search == '%')
{
}
else
{
for ($i=0; $i<count($keywords); $i++)
{

/* construct and run our query */
if (isset($_GET["page"]))
{
$page  = $_GET["page"];
}
else
{
$page=1;
};
$start_from = ($page-1) * 20;

    $construct_query = array();
    if ($keywords != '' && !is_array($keywords)) {
        $construct_query = "SELECT * FROM xxxxx " . "WHERE Title LIKE '%" . $keywords . "%'";
    } elseif (is_array($keywords) && count($keywords) > 0) {
        $construct_query[] = "SELECT * FROM xxxxx " . "WHERE Title LIKE '%" . $keywords['0'] . "%'";
        unset($keywords['0']);
                foreach ($keywords as $additional_term) {
            $construct_query[] = " $and_or Title LIKE '%" . $additional_term . "%'";
        }
            }

    $query_term = '';
    
    if (count($construct_query) > 0) {
        $query_term = implode('', $construct_query);
    }


//$search2 = "SELECT * FROM xxxxx " . "WHERE Title LIKE '%".$keywords[$i]."%'". " ORDER BY Title ASC LIMIT $start_from, 20";
}
//Store the results in a variable or die if query fails
$result = mysql_query($search2);
}
if ($search == NULL or $search == '%'){
} else {
//Count the rows retrived
$count = mysql_num_rows($result);
}
echo "<html><head><title>Your Title Here</title></head>";
echo "<body onLoad=\"self.focus();document.searchform.search.focus()\"><center><br /><form name=\"searchform\" method=\"GET\" action=\"search.php\"> <input type=\"text\" name=\"search\" size=\"20\" TABINDEX=\"1\" /> <input type=\"submit\" value=\"Search\" /></form>";
if ($search == NULL) {
} else {
echo "You searched for <b><FONT COLOR=\"blue\">";
foreach($keywords as $value) {
   print "$value ";
}
echo "</font></b>";
}
echo "<p> </p><br /></center>";
if (strlen($search) < 3){
echo "<center><b><FONT COLOR=\"red\">Search term must be three characters or longer.</font></b><br /></center>";
}
//If no results are returned print it
elseif ($count <= 0){
echo "<center><b><FONT COLOR=\"red\">Your query returned no results from the database.</font></b><br /></center>";
//ELSE print the data in a table
} else {
//Table header
echo "<TABLE border=1><tr><td><b>Link</b></td><td><b>Gravity</b></td><td><b>InitialEarningsPerSale</b></td><td><b>AverageEarningsPerSale</b></td><td><b>ActivateDate</b></td></TR><TR>";

while($row = mysql_fetch_array($result))
{
echo "<tr><td>".$row['Link']."</td><td>".$row['Gravity']."</td><td>".$row['InitialEarningsPerSale']."</td><td>".$row['AverageEarningsPerSale']."</td><td>".$row['ActivateDate']."</td></tr>";
$row_count++;

}
}
echo "</table>";

$search = $_GET['search'];
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $search);
$keywords = explode(" ", $search);
$keywords = array_diff($keywords, array(""));
for ($i=0; $i<count($keywords); $i++)
$query = "SELECT COUNT(*) FROM xxxxx WHERE Title LIKE '%".$keywords[$i]."%'". " ORDER BY Title ASC";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$total_records = $row[0];
$total_pages = ceil($total_records / 20);

$page  = $_GET["page"];
if ($page > $total_pages)
{
echo "";
}

for ($i=1; $i<=$total_pages; $i++) {
            echo "<a href='search.php?search=$value&page=".$i."'>".$i."</a> ";
};
//require "footer.php";
?>

mac_gyver · May 5, 2013

you were explicitly told that you need to build a correctly formatted WHERE condition in a variable so that you can use it in both queries (you could actually use a portion of quickoldcar's code to do that.) both queries must match the same rows for this to work. until you can produce the correct queries from your search input value, you won't be able to get your code to work.

programming involves breaking down a task into the steps needed to complete that task. just work on producing the correct WHERE condition in a variable and you must do this in your code before you make or run either one of the queries.

Edited May 5, 2013 by mac_gyver

mac_gyver · May 5, 2013

your code is all over the place with copy/pasted logic that isn't doing anything toward your goal. i cleaned it up for you and arranged it in a logical/necessary order. forming your multi keyword search is still up to you, but you do it ONCE in the logic where indicated -

<?php
require 'config.php';

mysql_connect ($dbhost, $dbusername, $dbuserpass);
mysql_select_db($dbname) or die('Cannot select database');

// process any search term
$search = ''; // default to an empty string
if(isset($_GET['search'])){
    $search = trim($_GET['search']);
    $search = preg_replace('/\s+/', ' ', $search); // replace multiple white-space with one space
}

// produce the where condition here...

// ... your code that you need to figure out for multiple keywords goes here (this is the only place you need to have any keyword logic)


// fake a where_condition for testing - i.e. works when one keyword is entered
if($search != ''){
    $where_condition = "title like '%$search%'";
} else {
    $where_condition = '1=1';
}

// all the following code is basic pagination logic and it doesn't need any logic added to it to search for multiple keywords
$table_name = 'your_table';
$rows_per_page = 20;

// get and calculate the total number of pages
$query = "SELECT COUNT(*) FROM $table_name WHERE $where_condition";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$total_records = $row[0];
$total_pages = ceil($total_records / $rows_per_page);

// get and limit the requested page number
if (isset($_GET["page"])){
    $page  = (int)$_GET["page"];
} else {
    $page=1; // not set, default to 1
}
if($page < 1){
    $page = 1;
}
if($total_pages > 0 && $page > $total_pages){
    $page = $total_pages;
}

// produce and run the main query
$start_from = ($page-1) * $rows_per_page;
$query = "SELECT * FROM $table_name WHERE $where_condition ORDER BY Title ASC LIMIT $start_from, $rows_per_page";
$result = mysql_query($query);
$count = mysql_num_rows($result);
if($count <= 0){
    // no matching rows
    echo "<center><b><FONT COLOR=\"red\">Your query returned no results from the database.</font></b><br /></center>";
} else {
    // one or more matching row
    echo "<TABLE border=1><tr><td><b>Link</b></td><td><b>Gravity</b></td><td><b>InitialEarningsPerSale</b></td><td><b>AverageEarningsPerSale</b></td><td><b>ActivateDate</b></td></TR><TR>";
    while($row = mysql_fetch_array($result)){
        echo "<tr><td>".$row['Link']."</td><td>".$row['Gravity']."</td><td>".$row['InitialEarningsPerSale']."</td><td>".$row['AverageEarningsPerSale']."</td><td>".$row['ActivateDate']."</td></tr>";
    }
    echo "</table>";
}

// produce the pagination links
if($total_pages > 1){ // no point if there's only one page
    for ($i=1; $i<=$total_pages; $i++) {
        echo "<a href='?search=$search&page=".$i."'>".$i."</a> ";
    }
}
?>

strago · May 6, 2013

you could actually use a portion of quickoldcar's code to do that.

That's exactly what I'm trying to do. You're looking at a php n00bie!! Am I even geting close??

<?php
require 'config.php';

mysql_connect ($dbhost, $dbusername, $dbuserpass);
mysql_select_db($dbname) or die('Cannot select database');

// process any search term
$search = ''; // default to an empty string
if(isset($_GET['search'])){
    $search = trim($_GET['search']);
    $search = preg_replace('/\s+/', ' ', $search); // replace multiple white-space with one space
}

// produce the where condition here...

// ... your code that you need to figure out for multiple keywords goes here (this is the only place you need to have any keyword logic)

    //create an array to eliminate errors checking array
    $search_exploded = array();
    
    //create a new array exploding the spaces of multiple search terms
    $search_exploded = explode(" ", $search);

    //if is multiple search terms
    if (count($search_exploded > 0)) {
        //loop through array
        foreach ($search_exploded as $search_term) {
            //only with certain amount of characters
            if (strlen($search_term) >= $search_term_length) {
                //create new array and lower all the words
                $search_terms[] = strtolower($search_term);
            }
        }
            }
else {
        //if is a single search term
        $search_terms = strtolower($search);
    }
$start_from = ($page-1) * $rows_per_page;

// fake a where_condition for testing - i.e. works when one keyword is entered
if($search != '')
    $construct_query = array();
    if ($search != '' && !is_array($search)) {
{
    $where_condition = "Title LIKE '%" . $search . "%'". "";

}
if (is_array($search) && count($search) > 0)
{
     $where_condition[] = "Title LIKE '%" . $search['0'] . "%'";
}

// get and calculate the total number of pages
$query = "SELECT COUNT(*) FROM $table_name WHERE $where_condition";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$total_records = $row[0];
$total_pages = ceil($total_records / $rows_per_page);

// get and limit the requested page number
if (isset($_GET["page"])){
    $page  = (int)$_GET["page"];
} else {
    $page=1; // not set, default to 1
}
if($page < 1){
    $page = 1;
}
if($total_pages > 0 && $page > $total_pages){
    $page = $total_pages;
}

// produce and run the main query

$start_from = ($page-1) * $rows_per_page;
$query = "SELECT * FROM $table_name WHERE $where_condition ORDER BY Title ASC LIMIT $start_from, $rows_per_page";
$result = mysql_query($query);
$count = mysql_num_rows($result);
if($count <= 0){
    // no matching rows
    echo "<center><b><FONT COLOR=\"red\">Your query returned no results from the database.</font></b><br /></center>";
} else {
    // one or more matching row
    echo "<TABLE border=1><tr><td><b>Link</b></td><td><b>Gravity</b></td><td><b>InitialEarningsPerSale</b></td><td><b>AverageEarningsPerSale</b></td><td><b>ActivateDate</b></td></TR><TR>";
    while($row = mysql_fetch_array($result)){
        echo "<tr><td>".$row['Link']."</td><td>".$row['Gravity']."</td><td>".$row['InitialEarningsPerSale']."</td><td>".$row['AverageEarningsPerSale']."</td><td>".$row['ActivateDate']."</td></tr>";
    }
    echo "</table>";
}

// produce the pagination links
if($total_pages > 1){ // no point if there's only one page
    for ($i=1; $i<=$total_pages; $i++) {
        echo "<a href='?search=$search&page=".$i."'>".$i."</a> ";
    }
}
}
?>

Sign In

Minimum characters in search setting, and pagination.

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

Link to comment

Share on other sites

Join the conversation

Important Information