Page 3 of 4

Re: php search query

Posted: Sat Sep 07, 2013 7:00 am
by chris98
Is there no easier way to do it?

Re: php search query

Posted: Sat Sep 07, 2013 7:04 am
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.

Re: php search query

Posted: Sat Sep 07, 2013 7:28 am
by chris98
What would it be that I actually needed to change?

Would it be the select statement, or the php from below it?

Re: php search query

Posted: Sat Sep 07, 2013 7:45 am
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.

Re: php search query

Posted: Sat Sep 07, 2013 7:59 am
by chris98
Ah, thanks.I was looking for something far more complex than that.

Re: php search query

Posted: Sat Sep 07, 2013 8:11 am
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);

Re: php search query

Posted: Sat Sep 07, 2013 8:29 am
by Celauran
Something like this, perhaps?

Code: Select all

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

Re: php search query

Posted: Thu Sep 12, 2013 12:11 pm
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....

Re: php search query

Posted: Fri Sep 13, 2013 10:59 pm
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.

Re: php search query

Posted: Fri Sep 13, 2013 11:06 pm
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.

Re: php search query

Posted: Sat Sep 14, 2013 2:55 am
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?

Re: php search query

Posted: Sat Sep 14, 2013 6:51 am
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.

Re: php search query

Posted: Sat Sep 14, 2013 11:55 am
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!

Re: php search query

Posted: Wed Sep 25, 2013 10:29 am
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.

Re: php search query

Posted: Wed Sep 25, 2013 11:17 am
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.