php search query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

Would I not need a variable for the other pages, or would it work fine like that for all of them?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

Absolutely you'd need a variable. Set a fixed number to retrieve per page, but your offset is going to depend on which page you're currently viewing.
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

So, would something like

Code: Select all

<?php

$query = "SELECT * FROM downloads WHERE name LIKE :search LIMIT :start,:limit";
 $ps = $pdo->prepare($query);
 $ps->execute(array(':search' => '%' . $search . '%',
 $ps->execute(array(':start' => $start,
 $ps->execute(array(':limit' => $limit));
 $num_rows = ($ps->fetchColumn());
 
$page=1;//Default page
 $limit=5;//Records per page
 $start=0;//starts displaying records from 0
 if(isset($_GET['page']) && $_GET['page']!=''){
 $page=$_GET['page'];
 }
 $start=($page-1)*$limit;
 ?>
be ok?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

This should work

Code: Select all

<?php

$page = 1; //Default page
$limit = 5; //Records per page
if (isset($_GET['page']) && $_GET['page'] != '') {
    $page= (intval) $_GET['page'];
}

$start = ($page - 1) * $limit;

$query = "SELECT name, category, username, file FROM downloads WHERE name LIKE :search LIMIT :start, :limit";
$ps = $pdo->prepare($query);
$ps->execute(array(
    ':search' => '%' . $search . '%',
    ':start' => $start,
    ':limit' => $limit
));
$num_rows = $ps->rowCount;
You need to workout what your offset is before passing it into the query. SELECT * is always wrong; figure out which columns you need and fetch those.
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

It comes back with "unexpected '$_GET' (T_VARIABLE)"

This is the part to which it reffers to.

Code: Select all

if (isset($_GET['page']) && $_GET['page'] != '') {
     $page= (intval) $_GET['page'];
 }
What do I need to add?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

Whoops. Should be

Code: Select all

$page = intval($_GET['page']);
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

Thanks, but now here is a new problem; I have changed the document, and below is the new one.

Code: Select all

<center>
<?php
require_once('submit/db/db_connection.php');
$search = (isset($_POST['search'])) ? ($_POST['search']) : '' ;
define('PUN_ROOT', dirname(__FILE__).'/../');
include PUN_ROOT.'include/common.php';
require_once('submit/db/db_connection.php');
?>
<?php
$page = 1; //Default page
 $limit = 5; //Records per page
 $start=0;//starts displaying records from 0
 if(isset($_GET['page']) && $_GET['page']!=''){
 $page=$_GET['page'];
 }
 $start=($page-1)*$limit;
 function pagination($per_page = 10, $page = 1, $url = '', $total){ 

$adjacents = "2";
 
$page = ($page == 0 ? 1 : $page); 
$start = ($page - 1) * $per_page; 

$prev = $page - 1; 
$next = $page + 1;
 $lastpage = ceil($total/$per_page);
 $lpm1 = $lastpage - 1;
 
$pagination = "";
 if($lastpage > 1)
 { 
$pagination .= "<ul class='pagination'>";
 $pagination .= "<li class='details'>Page $page of $lastpage</li>";
 if ($lastpage < 7 + ($adjacents * 2))
 { 
for ($counter = 1; $counter <= $lastpage; $counter++)
 {
 if ($counter == $page)
 $pagination.= "<li><a class='current'>$counter</a></li>";
 else
 $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; 
}
 }
 elseif($lastpage > 5 + ($adjacents * 2))
 {
 if($page < 1 + ($adjacents * 2)) 
{
 for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
 {
 if ($counter == $page)
 $pagination.= "<li><a class='current'>$counter</a></li>";
 else
 $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; 
}
 $pagination.= "<li class='dot'>...</li>";
 $pagination.= "<li><a href='{$url}$lpm1'>$lpm1</a></li>";
 $pagination.= "<li><a href='{$url}$lastpage'>$lastpage</a></li>"; 
}
 elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
 {
 $pagination.= "<li><a href='{$url}1'>1</a></li>";
 $pagination.= "<li><a href='{$url}2'>2</a></li>";
 $pagination.= "<li class='dot'>...</li>";
 for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
 {
 if ($counter == $page)
 $pagination.= "<li><a class='current'>$counter</a></li>";
 else
 $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; 
}
 $pagination.= "<li class='dot'>..</li>";
 $pagination.= "<li><a href='{$url}$lpm1'>$lpm1</a></li>";
 $pagination.= "<li><a href='{$url}$lastpage'>$lastpage</a></li>"; 
}
 else
 {
 $pagination.= "<li><a href='{$url}1'>1</a></li>";
 $pagination.= "<li><a href='{$url}2'>2</a></li>";
 $pagination.= "<li class='dot'>..</li>";
 for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
 {
 if ($counter == $page)
 $pagination.= "<li><a class='current'>$counter</a></li>";
 else
 $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; 
}
 }
 }
 
if ($page < $counter - 1){
 $pagination.= "<li><a href='{$url}$next'>Next</a></li>";
 // $pagination.= "<li><a href='{$url}$lastpage'>Last</a></li>";
 }else{
 //$pagination.= "<li><a class='current'>Next</a></li>";
 // $pagination.= "<li><a class='current'>Last</a></li>";
 }
 $pagination.= "</ul>\n"; 
} 
return $pagination;
 } 
 if (isset($_GET['page']) && $_GET['page'] != '') {
     $page = intval($_GET['page']);
 }
 
$start = ($page - 1) * $limit;
 
$query = "SELECT name, category, username, file FROM downloads WHERE name LIKE :search LIMIT :start, :limit";
 $ps = $pdo->prepare($query);
 $ps->execute(array(
     ':search' => '%' . $search . '%',
     ':start' => $start,
     ':limit' => $limit
 ));
 $num_rows = $ps->rowCount;
//....
When I try to run the new code, it comes up with:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''0', '5'' at line 1' in C:\*directories*\search.php on line 113

Is this something I have done?

Here are the relevant lines it is talking about:

Code: Select all

//line 113
 ));
Lines nearby

Code: Select all

$query = "SELECT name, category, username, file FROM downloads WHERE name LIKE :search LIMIT :start, :limit";
 $ps = $pdo->prepare($query);
 $ps->execute(array(
     ':search' => '%' . $search . '%',
     ':start' => $start,
     ':limit' => $limit
 ));
 $num_rows = $ps->rowCount;
if ($pun_user['is_guest'])
    header("location: ../login.php");
?>
Last edited by chris98 on Tue Nov 19, 2013 11:27 am, edited 1 time in total.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

There was a bug in MySQL which has since been fixed. If you're using an older version of MySQL (ie. before the bug was fixed), add this:

Code: Select all

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

I have added that, but now even when I type the filename exact it doesn't come up.Is there something wrong in my html?


(This is on a different page)

Code: Select all

<?php
$search = (isset($_POST['search'])) ? ($_POST['search']) : '' ;
?>
<div id="form_area" class="boxed">
			<h2 class="heading">Quick Search</h2>
			<div class="content">
				<form method="post" action="search.php">
					<div>
			<label>Filename:<br /></label>
            <input type="text" class="textfield" name="search" value="" size="18" /><br /><br />
						<input class="button" type="submit" value="Search" title="Search" /><br /><br />
						&bull; <a href="ad_search.php">Advanced Search</a>
					</div>
				</form>
			</div>
		</div>
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

I am still having trouble with the stdclass thing.Could you take a look and see what is wrong?

It works as soon as I take away the variables, but of course doesn't output anything then.

These are the rows which stops thw whole script from working

Code: Select all

echo "&#8226; <span class='text_bold'>File</span>: " .$row['name']."<br /><br />";
		   echo "&#8226; <span class='text_bold'>Category</span>: " .$row['category']."<br /><br />";
           echo "&#8226; <span class='text_bold'>Author</span>: " .$row['username']."<br /><br />";
           echo "&#8226; <span class='text_bold'>Size</span>:  " .$row['size']." MB<br /><br /> "; 
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

chris98 wrote:I am still having trouble with the stdclass thing.
What does that mean?
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

Every time I try to run the code, I get this error:
( ! ) Fatal error: Cannot use object of type stdClass as array in C:\wamp\www\stronghold2nation\downloads\search.php on line 203
Once I remove the php from the code above, it allows it.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

The error message is pretty specific; you're fetching an object (PDO::FETCH_OBJ) and trying to use it as an array ($row['name']). You can't do that. Either fetch an array or use object notation.
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

How could I fetch as an array?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

Post Reply