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 »

Is there no easier way to do 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 »

No easier way that replacing PDO::FETCH_OBJ with PDO::FETCH_ASSOC? No, I'd say that's about as simple as things get.
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 »

What would it be that I actually needed to change?

Would it be the select statement, or the php from below 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 »

Neither. When you run fetch, you tell it how you want your data returned. You're currently requesting an object (PDO::FETCH_OBJ) and want an associative array instead (PDO::FETCH_ASSOC). Literally find/replace those two terms.
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 »

Ah, thanks.I was looking for something far more complex than that.
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 get this in the pagination?

Code: Select all

echo pagination($limit,$page,'search.php?page=',$num_rows'&start_q=4&query=',$search'');
Current:

Code: Select all

echo pagination($limit,$page,'search.php?page=',$num_rows);
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

Something like this, perhaps?

Code: Select all

    echo pagination($limit, $page, 'search.php?start_q=4&query=' . $search . '&page=', $num_rows);
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 »

Thank you very much, the actual pagination works now, however though it works, it requires you to actually add the page number in the top to work, and it doesn't actually display the pagination feature on the page anymore.

I know it can't be the css, because I haven't changed that, I know it can't be the html, because I haven't changed any, so the only thing it could be is the PHP.I am still definitely echoing the pagination onto the page, and I think I am still using PDO.

I just don't know why it isn't echoing it.

(Sorry about the long wait in my reply, my router broke, and I had to get another one.)

Code: Select all

<center>
<?php
require_once('submit/db/db_connection.php');
$search = (isset($_GET['search'])) ? ($_GET['search']) : '' ;
define('PUN_ROOT', dirname(__FILE__).'/../');
include PUN_ROOT.'include/common.php';
require_once('submit/db/db_connection.php');
if ($pun_user['is_guest'])
    header("location: ../login.php");
?>
<?php 
$page=1;//Default page
 $limit=1;//Records per page
 $start=0;//starts displaying records from 0
 if (isset($_GET['page']) && $_GET['page'] != '') {
     $page = $_GET['page'];
 }
 
 $start=($page-1)*$limit;
 ?>
<?php 
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;
 } 

?>
 <?php
$query = "SELECT name, category, username, file, id, size FROM downloads WHERE name LIKE :search LIMIT :start, :limit";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
 $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");
?>
<style type="text/css">
 ul.pagination { font-family: "Arial", "Helvetica", sans-serif;
 font-size: 13px;
 height: 100%;
 list-style-type: none;
 margin: 20px 0;
 overflow: hidden;
 padding: 0; }
 ul.pagination li.details { background-color: white;
 border-color: #C8D5E0;
 border-image: none;
 border-style: solid;
 border-width: 1px 1px 2px;
 color: #1E598E;
 font-weight: bold;
 padding: 8px 10px;
 text-decoration: none; }
 ul.pagination li.dot { padding: 3px 0; }
 ul.pagination li { float: left;
 list-style-type: none;
 margin: 0 3px 0 0; }
 ul.pagination li:first-child { margin-left: 0; }
 ul.pagination li a { color: black;
 display: block;
 padding: 7px 10px;
 text-decoration: none; }
 ul.pagination li a img { border: medium none; }
 ul.pagination li a.current { background-color: white;
 border-radius: 0 0 0 0;
 color: #333333; }
 ul.pagination li a.current:hover { background-color: white; }
 ul.pagination li a:hover { background-color: #C8D5E0; }
 ul.pagination li a { background-color: #F6F6F6;
 border-color: #C8D5E0;
 border-image: none;
 border-style: solid;
 border-width: 1px 1px 2px;
 color: #1E598E;
 display: block;
 font-weight: bold;
 padding: 8px 10px;
 text-decoration: none; }
 </style>  
//Show html....
Last edited by chris98 on Tue Nov 19, 2013 11:29 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 »

This code is a mess! I had to spend 10 minutes just formatting it so I could read it and make sense of it. Indent your code! If not for yourself, at least for whomever is going to be stuck taking it over.

Your pagination function is working fine. The reason you're not seeing anything displayed is that it will only return results if there is more than one page to display.

Code: Select all

if ($lastpage > 1) {
There never is because you're always limiting your query to a single row and passing that in as the total number of rows.

Code: Select all

$limit  = 1; //Records per page
... (farther down)
$query = "SELECT name, category, username, file, id, size FROM downloads WHERE name LIKE :search LIMIT :start, :limit";
$ps->execute(array(
	':search' => '%' . $search . '%',
	':start' => $start,
	':limit' => $limit    // Still 1
));
$num_rows = $ps->rowCount();
... (much farther down)
echo pagination($limit, $page, 'search.php?start_q=4&query=' . $search . '&page=', $num_rows);    // STILL 1
See what's going on here?

It seems there's some confusion between SELECT COUNT() and PDOStatement::rowCount(). The former will count the number of rows in the database matching your query. The latter displays the number of rows returned by your last query. If you set LIMIT X in your query, rowCount() will always equal X. In this case, 1.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

While we're at it, you're calling your pagination function while you're iterating over your search results. If you were displaying 10 results per page, your paginator would be displayed 10 times.
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've now changed

Code: Select all

if ($lastpage > 1) {
to

Code: Select all

if ($lastpage > 0) {
And I have also changed the pagination function to outside the

Code: Select all

<?php } }?>
(in the code above)

But to include the SELECT COUNT(), would I have to have a seperate sql statement?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

I would. SELECT COUNT(id) to get the total number of results, then run a second query to get the results to display on the current 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 »

I have used the COUNT(), and now it is working.

Code: Select all

Total Files: <b><?php 
$sql_1 = "SELECT COUNT(*) AS id FROM downloads WHERE name LIKE :search";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps_1 = $pdo->prepare($sql_1);
$ps_1->execute(array( 
':search'=>'%' . $_GET['search'] . '%'));
foreach ($ps_1 as $row)
{ ?>
			<?php echo $row['id']; 
			?>
			  <?php $rows = $row['id']; } ?></b>
			  <br />
Thank you VERY much!
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 create an advanced search for this?

So far, in my advanced_search.php, I have this:

Code: Select all

<?php
//After submitted, post the info to search.php via the URL
if (isset($_POST['submitBtn_search'])) {
$file = (isset($_POST['file'])) ? ($_POST['file']) : '' ;
$author = (isset($_POST['author'])) ? ($_POST['author']) : '' ;
$difficulty = (isset($_POST['difficulty'])) ? ($_POST['difficulty']) : '' ;
$balance = (isset($_POST['balance'])) ? ($_POST['balance']) : '' ;
$category = (isset($_POST['category'])) ? ($_POST['category']) : '' ;
$missions = (isset($_POST['missions'])) ? ($_POST['missions']) : '' ;
$estates = (isset($_POST['estates'])) ? ($_POST['estates']) : '' ;
header("Location:  search.php?file=".$file."&author=".$author."&difficulty=".$difficulty."&estates=".$estates."&missions=".$missions."&balance=".$balance."&category=".$category."");
}
?>
        <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
				&bull; <b>Category:</b><br>
        <select name="category">
		<option value="">Any Category</option>
        <option value="kingmaker">Kingmaker Scenarios</option>
        <option value="Custom War">Custom War Scenarios</option>
		<option value="peace custom">Peace Custom Scenarios</option>
		<option value="Free Build">Free Build Scenarios</option>
		<option value="Miscellaneous">Miscellaneous Files</option>
        </select><br><br>
		<p>&bull; <b>Balance:</b></p>
        <select name="balance">
		<option value="">Any</option>
        <option value="Balanced">Balanced</option>
        <option value="Custom War">Unbalanced</option>
		<option value="N/A">N/A (Miscellaneous Files only)</option>
        </select><br><br>
		<p>&bull; <b>Difficulty:</b></p>
        <select name="difficulty">
		<option value="">Any Difficulty</option>
        <option value="Very Easy">Very Easy</option>
		<option value="Easy">Easy</option>
		<option value="Normal">Normal</option>
		<option value="Hard">Hard</option>
		<option value="Very Hard">Very Hard</option>
		<option value="Insane">Insane</option>
        </select><br><br>
		<p>&bull; <b>Missions:</b></p>
		<select name="missions"><option value="">Any Number of Missions</option><option value="1">1</option>
   <option value="2">2</option>
   <option value="2">2</option>
   <option value="3">3</option>
   <option value="4">4</option>
   <option value="5">5</option>
   <option value="6">6</option>
   <option value="7">7</option>
   <option value="8">8</option>
   <option value="9">9</option>
   <option value="10">10</option>
   <option value="11">11</option>
   <option value="12">12</option>
   <option value=">12">>12 (Greater than 12)</option>
   <option value="N/A">N/A (Miscellaneous only)</option>
 </select><br><br>
 <p>&bull; <b>No of Estates:</b></p><select name="estates"><option value="">Any Number of Estates</option><option value="1">1</option>
   <option value="2">2</option>
   <option value="2">2</option>
   <option value="3">3</option>
   <option value="4">4</option>
   <option value="5">5</option>
   <option value="6">6</option>
   <option value="7">7</option>
   <option value="8">8</option>
   <option value="9">9</option>
   <option value="10">10</option>
   <option value="11">11</option>
   <option value="12">12</option>
   <option value="13">13</option>
   <option value="14">14</option>
   <option value="15">15</option>
   <option value="16">16</option>
   <option value="17">17</option>
   <option value="18">18</option>
   <option value="N/A">N/A (miscellaneous only)</option>
 </select><br><br>
                &bull; <b>Filename:</b><br /><input class="formbox" type="text" name="file" size="20"><br><br>
				&bull; <b>Author:</b><br /><input class="formbox" type="text" name="author"> <br><br>

        
        <table width="80%" cellspacing="2" cellpadding="0">
        <tr>            
        </table>
        <input class="button" name="submitBtn_search" type="submit" value="Search" title="Search"><br><br>
        </form>
And I have tried this with the PHP, but it doesn't appear to work. (Search.php)

Code: Select all

<?php
$file = $_GET['file'];
$author = $_GET['author'];
$difficulty = $_GET['difficulty'];
$estates = $_GET['estates'];
$missions = $_GET['missions'];
$balance = $_GET['balance'];
$category = $_GET['category'];

//count for pagination
$sql_1 = "SELECT COUNT(*) AS id FROM downloads WHERE name LIKE :search AND username LIKE :user AND difficulty = :difficulty AND estates = :estates AND missions = :missions AND balanced = :balance AND category = :category";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps_1 = $pdo->prepare($sql_1);
$ps_1->execute(array( 
':search'=>'%' . $_GET['file'] . '%',
':user'=>'%' . $_GET['author'] . '%',
':difficulty'=>$_GET['difficulty'],
':estates'=>$_GET['estates'],
':missions'=>$_GET['missions'],
':balance'=>$_GET['balance'],
':category'=>$_GET['category']));
foreach ($ps_1 as $row)
{ ?>
			<?php echo $row['id']; 
if ($row['id'] < 1)
		{
		echo '<div id="general_message">
         <p>Your search query has generated 0 results.<br /><br />Not what you were looking for? Try again <a href="ad_search.php">here</a>!</p>
       </div>'; 
	   }
	   else
	   {
	   echo '</td>
				  <table class="upload" cellspacing="0" cellpadding="0" border="0" width="100%">
	<colgroup><col width="5%"><col width="35%"><col width="20%"><col width="15%"><col width="25%"></colgroup>
	<tr>
		<th class="files">Download</th>
		<th>Filename</th>
		<th>Author</th>
		<th>Added</th>
		<th>Difficulty</th>
	</tr>';
	   }			} 

//select statement
$sql_2 = "SELECT id,name,text,size,file,username,date,difficulty FROM downloads WHERE name LIKE :search AND username LIKE :user AND difficulty = :difficulty AND estates = :estates AND missions = :missions AND balanced = :balance AND category = :category order by id DESC LIMIT :start, :limit";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql_2);
$ps->execute(array( 
':search'=>'%' . $_GET['file'] . '%',
':user'=>'%' . $_GET['author'] . '%',
':difficulty'=>$_GET['difficulty'],
':estates'=>$_GET['estates'],
':missions'=>$_GET['missions'],
':balance'=>$_GET['balance'],
':category'=>$_GET['category'],
':start'=>$start,
':limit'=>$limit));
while ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
//echo results here
}
?>
How could I allow for people missing (or adding) multiple fields?

Would that be something like this, or is there a simpler way?

Code: Select all

if (empty($file))
{
//Search without the filename
}

if (empty($author))
{
//Search without a user
}

etc.etc
All of the variables are passed to the search.php, as I have checked the URL.
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 problem here is that you're including all fields in your query even if they have no values, so you end up with

Code: Select all

SELECT COUNT(*) AS id FROM downloads WHERE name LIKE 'foo' AND username LIKE 'bar' AND difficulty = 'Easy' AND estates = '' AND missions = '' AND balanced = '' AND category = ''
which will almost certainly return 0.
Post Reply