[RESOLVED] another post about search + pagination

[johanafm]

# This returns all selected rows into an associative array
$search_result = $stmt->fetchAll(PDO::FETCH_ASSOC);


///// define $search_result here??? /////
$rows = array(
	$search_results
);

Well, if $search_result may end up not being defined, then obviously neither will $row. There are only 3 ways to go:
1. Do everything you need to with the var where it is is defined
2. Define it as something resonable in the other if block (or before both blocks), such as empty array, null, false
3. Check if it's set when you need to use it at a later point (if isset(...))

And just to be certain, I hope you realize that if and else are always mutually exclusive

if ($something)
{
	$var = true;
}
else
{
	# $var not defined
}

/* We don't have to know the value of $something to know several things about the above code:
- Exactly one of the above blocks was sexecuted
- We cannot know if $var has been defined (without knowing the value of $something)

Truthfully, those two statements aren't "several things", they are one since they are actually equivalent.
And because of that, the two following statements are also equivalent
*/
# once again checking if $something is true
if ($something)
{
	# and then user $var
	echo $var;
}

# checking if $var is set
if (isset($var))
{
	echo $var;
}

But if you want to echo $var in case it's been defined like we do in the last block, for reasons of code readability, do not use if ($something) ONLY to ceck if $var is set. Instead, use if isset($var) - This tells YOU exactly what is going on.

With the code structure I proposed in my code, I'd probably proceed the first if check (that is, at the very top) with $search_result = array(); Now you know it's defined, everywhere. Once you get to the file dealing with output, it'll somewhere contain

foreach ($search_result as $row)

and that works perfectly fine with an empty array. This can either mean no search was done, no results found or perhaps a database error. Then the feedback and error variables are used for additional feedback: 0 rows found, "An error occurred" etc.

[darkangel2001lv]

dalecosp;11019465 wrote:

Google is your friend?

Here's the result with about 6 minutes of work and a fairly decent, but basic, text editor:

thanks, I did search on google and just didn't understand it completely. looking at your verision and mine i can see what i did wrong with indentation. Thanks now that i see the right way to do it i will work on my messy codes lol. so much easier to follow tho

[darkangel2001lv]

Hey guys, sorry be reading up on a few things, got most of the issues fixed I think. Only is error that is coming up is this

Error displayed is this: added comments so you know that message is from two different outputs

Connected to database <--- Connection Good, This message is from Connection file script
There was a database error, (!$stmt->execute($params)) did not execute. Please contact the Webmaster of this site via our contact page if this problem persists

^-------- From results.php page. page We are working on

if (!$stmt->execute($params)) {
		$errors[] = 'There was a database error, (!$stmt->execute($params)) did not execute. Please contact the Webmaster of this site via our contact page if this problem persists';

I tested connection by running connection file nfacts.php by itself and seems to connect based on message displayed "Connected To Database".

[/code]

Here is the results.php page that is having the problem Full reworked code

#
#Script Ver 13 12/3/12
#
#
# I like collecting errors throughout the page to provide feedback
# Same with other feedback, although I keep them separate
$errors = array();
$feedback = array();
$search_result = array();

/* First off, I'd try to get as many checks as possible into one single
 * For example, instead of checking both "no input" and "less than 3 characters" and informing
 * user of each one separately, just check string length and inform user: has to be at least 3 chars
 *
 * Also note that $_GET['SearchTerms'] will always be set if the form was posted properly,
 * even if the field was left empty. Thus, in the original case of !isset($_GET['SearchTerm'])
 * it would have been better with !empty($_GET['SearchTerm'])
 */

# To make sure the array element is set, using the ternary operator ?:
# Works like this
# (is this value true) ? (then return this value) : (else return this value)
$_GET['SearchTerms'] = !isset($_GET['SearchTerms']) ? '' : trim($_GET['SearchTerms']);

# Now that we've trimmed the search, check for string length using mb_strlen (you are using utf-8 aren't you?)
if (mb_strlen($_GET['SearchTerms']) < 2) {
	$errors[] = "<p>Search terms must be longer than 2 characters. Please try your search again. ";


} else {
	$s = $_GET['SearchTerms'];

// Checking SearchTerm for disallowed search terms
# Easier way to check for these values in a more condensed form
$word_check = array(
	'bread',
	'white',
	'wheat',
	'rye'
);
if (in_array($s, $word_check)) {
	$feedback[] = "<p>Your search term is too broad. Please enter a more specific search term. ";
}

$limit = 10; // rows to return


// Build SQL Query


# Connecting to the DB using PDO 
include('includes/connections/nfacts.php'); 

// Display message if successfully connect, otherwise retains and outputs the potential error
try {
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  echo 'Connected to database';
}
catch(PDOException $e) {
  echo $e->getMessage();
}

$count_stmt = $conn->prepare('SELECT COUNT(*) AS rowcount FROM products WHERE keywords like :search');
# The query fetching the product links will also need "LIMIT offset, count"
$stmt = $conn->prepare('SELECT link, keywords FROM products WHERE keywords like :search ORDER BY keywords LIMIT :offset, :count');

# search here will replace :search in the query
$count_params = array(
	'search' => $s
);
# I put in fixed values for offset and count, leaving for you to deal with the pagination :)
$params = array(
	'search' => $s,
	'offset' => 10,
	'count' => 20
);



# Then you use matching name, other_name (with or without :) as array keys to pass the data



if (!$stmt->execute($params)) {
	$errors[] = 'There was a database error, (!$stmt->execute($params)) did not execute. Please contact the Webmaster of this site via our contact page if this problem persists';


} else {
	# This returns all selected rows into an associative array
	$search_result = $stmt->fetchAll(PDO::FETCH_ASSOC);

	$count_stmt->execute($params);
	# Whereas fetchAll fetches all rows at once, fetch fetches one row at the time
	# and here we only have one row anyway
	$row = $count_stmt->fetch(PDO::FETCH_ASSOC);
	$rowcount = $row['rowcount']; 

	# Add search result number independently if that was 0 or any other number
	$feedback[] = sprintf('Your search found %d results', $numrows);

		echo 'Database query passed however isset($search-result) was empty. Please look at that section of code';


	if ($numrows == 0) {
		$feedback[] = sprintf('Sorry, your search: "%s" returned zero results. Please try again. If you need help please <a href="%s">click here.</a></p>', $s, BASE . HELPDIR . 'search-help.php');




	}
}
}

# But before you get to actual content, let's give the user errors and feedback, if any
# Which ought to start with errors
foreach ($errors as $err) {
	echo $err . "<br />";
}
foreach ($feedback as $f) {
	echo $f . "<br />";
}


// now you can display the results returned


foreach($search_result as $row) {
      echo $row['link']. '<br />';

}


#  followed by pagination 
# echo "here..." ;

I know issue is here but I am missing it

include('includes/connections/nfacts.php'); 

$count_stmt = $conn->prepare('SELECT COUNT(*) AS rowcount FROM products WHERE keywords like :search');
# The query fetching the product links will also need "LIMIT offset, count"
$stmt = $conn->prepare('SELECT link, keywords FROM products WHERE keywords like :search ORDER BY keywords LIMIT :offset, :count');

# search here will replace :search in the query
$count_params = array(
	'search' => $s
);
# I put in fixed values for offset and count, leaving for you to deal with the pagination :)
$params = array(
	'search' => $s,
	'offset' => 10,
	'count' => 20
);

And Connection File

// Connection data (server_address, database, username, password)
$hostdb = 'mysite.com';
$namedb = 'mydatabase';
$userdb = 'myusername';
$passdb = 'mypassword';

// Display message if successfully connect, otherwise retains and outputs the potential error
try {
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  echo 'Connected to database';
}
catch(PDOException $e) {
  echo $e->getMessage();
}

[Weedpacket]

For debugging you can use the PDOStatement's errorInfo() method to get a description of what the DBMS didn't like.

       $errors[] = 'There was a database error, (!$stmt->execute($params)) did not execute. Please contact the Webmaster of this site via our contact page if this problem persists';
// only for debugging...
      $errorInfo = $stmt->errorInfo();
      $errors[] = $errorInfo[2];
        

[darkangel2001lv]

Weedpacket;11019663 wrote:

For debugging you can use the PDOStatement's errorInfo() method to get a description of what the DBMS didn't like.

       $errors[] = 'There was a database error, (!$stmt->execute($params)) did not execute. Please contact the Webmaster of this site via our contact page if this problem persists';
// only for debugging...
      $errorInfo = $stmt->errorInfo();
      $errors[] = $errorInfo[2];
        

Thanks, Weedpacket

this is the results that were reported

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10', '20'' at line 1

my MySQL version is 5.0 best hosting company offers - sucks I know but don't have a choice.
tried using phpmyadmin to troubleshoot query but it will not accept it

syntax error is here

$count_stmt = $conn->prepare('SELECT COUNT(*) AS rowcount FROM products WHERE keywords like :search');
    # The query fetching the product links will also need "LIMIT offset, count"
    $stmt = $conn->prepare('SELECT link, keywords FROM products WHERE keywords like :search ORDER BY keywords LIMIT :offset, :count');

# search here will replace :search in the query
$count_params = array(
	'search' => $s
);
# I put in fixed values for offset and count
$params = array(
	'search' => $s,
	'offset' => 10,
	'count' => 20);

[bradgrafelman]

The PDOStatement::execute() method (man: [man]pdostatement.execute[/man]) has a default behavior for the input parameters argument that will prevent you from simply passing in your parameters:

PHP Manual wrote:

All values are treated as PDO:😛ARAM_STR.

The last two arguments, offset and count, are not strings - they are integers.

As such, you'll have to replace this:

    # I put in fixed values for offset and count 
    $params = array( 
        'search' => $s, 
        'offset' => 10, 
        'count' => 20); 

with three calls to PDOStatement::bindparam() (man: [man]pdostatement.bindparam[/man]) to set each parameter equal to the appropriate value using the appropriate type (which, for the last two, will be PDO:😛ARAM_INT).

[darkangel2001lv]

bradgrafelman;11019669 wrote:

The PDOStatement::execute() method (man: [man]pdostatement.execute[/man]) has a default behavior for the input parameters argument that will prevent you from simply passing in your parameters:

The last two arguments, offset and count, are not strings - they are integers.

As such, you'll have to replace this:

    # I put in fixed values for offset and count 
    $params = array( 
        'search' => $s, 
        'offset' => 10, 
        'count' => 20); 

with three calls to PDOStatement::bindparam() (man: [man]pdostatement.bindparam[/man]) to set each parameter equal to the appropriate value using the appropriate type (which, for the last two, will be PDO:😛ARAM_INT).

ok I don't really understand, but its starting point to know what should be looking for. If possible can you explain this a little more Thanks bradgrafelman.

[darkangel2001lv]

bradgrafelman;11019669 wrote:

The PDOStatement::execute() method (man: [man]pdostatement.execute[/man]) has a default behavior for the input parameters argument that will prevent you from simply passing in your parameters:

The last two arguments, offset and count, are not strings - they are integers.

As such, you'll have to replace this:

    # I put in fixed values for offset and count 
    $params = array( 
        'search' => $s, 
        'offset' => 10, 
        'count' => 20); 

with three calls to PDOStatement::bindparam() (man: [man]pdostatement.bindparam[/man]) to set each parameter equal to the appropriate value using the appropriate type (which, for the last two, will be PDO:😛ARAM_INT).

ok I don't really understand, but its starting point to know what should be looking for. If possible can you explain this a little more Thanks bradgrafelman.

[darkangel2001lv]

Ok work in progress I know code is not finished, got stuck
this is what i have so far

	# Connecting to the DB using PDO 
include('includes/connections/nfacts.php'); 

$offset = 10;
$count = 20 ;

$count_stmt = $conn->prepare('SELECT COUNT(*) AS rowcount FROM products WHERE keywords like :search');
# The query fetching the product links will also need "LIMIT offset, count"
$stmt = $conn->prepare('SELECT link, keywords FROM products WHERE keywords like :search ORDER BY keywords LIMIT :offset, :count');

$stmt->bindValue(':search', $s, PDO::PARAM_STR);
$stmt->bindValue(':offset', (int) $offset, PDO::PARAM_INT);
$stmt->bindValue(':count', (int) $count, PDO::PARAM_INT);  


$count_params = array(
	'search' => $s
);



if (!$stmt->execute($params); {
	$errors[] = 'There was a database error, (!$stmt->execute($params)) did not execute. Please contact the Webmaster of this site via our contact page if this problem persists';
	// only for debugging...
  $errorInfo = $stmt->errorInfo();
  $errors[] = $errorInfo[2];  


} else {
	# This returns all selected rows into an associative array
	$search_result = $stmt->fetchAll(PDO::FETCH_ASSOC);

this part is left from previous version don't know how to correct it to work with the changes that were made.

$count_params = array(
		'search' => $s
	);



if (!$stmt->execute($params); {
	$errors[] = 'There was a database error, (!$stmt->execute($params)) did not execute. Please contact the Webmaster of this site via our contact page if this problem persists';
	// only for debugging...
  $errorInfo = $stmt->errorInfo();
  $errors[] = $errorInfo[2];  


} else {
	# This returns all selected rows into an associative array
	$search_result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Are the changes right or did I completely not get what bradgrafelman was saying ?

[darkangel2001lv]

bump

[johanafm]

Yes, that's what he meant. Although, after binding, you don't need to send params to execute(), i.e.
$db>execute();