php search query
Moderator: General Moderators
- chris98
- Forum Contributor
- Posts: 103
- Joined: Tue Jun 11, 2013 10:47 am
- Location: England, United Kingdom
Re: php search query
Is there no easier way to do it?
Re: php search query
No easier way that replacing PDO::FETCH_OBJ with PDO::FETCH_ASSOC? No, I'd say that's about as simple as things get.
- chris98
- Forum Contributor
- Posts: 103
- Joined: Tue Jun 11, 2013 10:47 am
- Location: England, United Kingdom
Re: php search query
What would it be that I actually needed to change?
Would it be the select statement, or the php from below it?
Would it be the select statement, or the php from below it?
Re: php search query
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.
- chris98
- Forum Contributor
- Posts: 103
- Joined: Tue Jun 11, 2013 10:47 am
- Location: England, United Kingdom
Re: php search query
Ah, thanks.I was looking for something far more complex than that.
- chris98
- Forum Contributor
- Posts: 103
- Joined: Tue Jun 11, 2013 10:47 am
- Location: England, United Kingdom
Re: php search query
How could I get this in the pagination?
Current:
Code: Select all
echo pagination($limit,$page,'search.php?page=',$num_rows'&start_q=4&query=',$search'');
Code: Select all
echo pagination($limit,$page,'search.php?page=',$num_rows);Re: php search query
Something like this, perhaps?
Code: Select all
echo pagination($limit, $page, 'search.php?start_q=4&query=' . $search . '&page=', $num_rows);- chris98
- Forum Contributor
- Posts: 103
- Joined: Tue Jun 11, 2013 10:47 am
- Location: England, United Kingdom
Re: php search query
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.)
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.
Re: php search query
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.
There never is because you're always limiting your query to a single row and passing that in as the total number of rows.
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.
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) {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 1It 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
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.
- chris98
- Forum Contributor
- Posts: 103
- Joined: Tue Jun 11, 2013 10:47 am
- Location: England, United Kingdom
Re: php search query
I've now changed to
And I have also changed the pagination function to outside the (in the code above)
But to include the SELECT COUNT(), would I have to have a seperate sql statement?
Code: Select all
if ($lastpage > 1) {Code: Select all
if ($lastpage > 0) {Code: Select all
<?php } }?>But to include the SELECT COUNT(), would I have to have a seperate sql statement?
Re: php search query
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.
- chris98
- Forum Contributor
- Posts: 103
- Joined: Tue Jun 11, 2013 10:47 am
- Location: England, United Kingdom
Re: php search query
I have used the COUNT(), and now it is working.
Thank you VERY much!
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 />
- chris98
- Forum Contributor
- Posts: 103
- Joined: Tue Jun 11, 2013 10:47 am
- Location: England, United Kingdom
Re: php search query
How could I create an advanced search for this?
So far, in my advanced_search.php, I have this:
And I have tried this with the PHP, but it doesn't appear to work. (Search.php)
How could I allow for people missing (or adding) multiple fields?
Would that be something like this, or is there a simpler way?
All of the variables are passed to the search.php, as I have checked the URL.
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']; ?>">
• <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>• <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>• <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>• <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>• <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>
• <b>Filename:</b><br /><input class="formbox" type="text" name="file" size="20"><br><br>
• <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>
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
}
?>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.etcRe: php search query
The problem here is that you're including all fields in your query even if they have no values, so you end up with
which will almost certainly return 0.
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 = ''