Re: php search query
Posted: Sat Sep 07, 2013 7:00 am
Is there no easier way to do it?
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
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);Code: Select all
echo pagination($limit, $page, 'search.php?start_q=4&query=' . $search . '&page=', $num_rows);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....
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 1Code: Select all
if ($lastpage > 1) {Code: Select all
if ($lastpage > 0) {Code: Select all
<?php } }?>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 />
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
}
?>Code: Select all
if (empty($file))
{
//Search without the filename
}
if (empty($author))
{
//Search without a user
}
etc.etcCode: 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 = ''