هذه هي الاكواد لعل الصورة تتضح اكثر:
=====================================
الصفحة الرئيسية
<!----------------------------START CATEGORIES--------------------->
<span class="Choose-category latest-ads">Choose a category</span>
<section class="category-section">
<?php
if(!empty($cats)){
foreach ($cats as $cat) {
?> <div> <?php
if($cat['cat_id']==1){echo '<i class="fas fa-store"></i>';}
if($cat['cat_id']==2){echo '<i class="fas fa-tools"></i>';}
if($cat['cat_id']==3){echo '<i class="fas fa-shopping-bag"></i>';}
if($cat['cat_id']==4){echo '<i class="fas fa-store"></i>';}
if($cat['cat_id']==5){echo '<i class="fas fa-user-friends"></i>';}
if($cat['cat_id']==6){echo '<i class="fas fa-question-circle"></i>';}
echo "<a href='search.php?c=".$country."&i=".$cat['cat_id']."&n=".$cat['name']."' class='cat' value=".$cat['cat_id'].">".$cat['name']."</a>";
?> </div> <?php
}
}
?>
</section>
================================
صفحة البحث
if( (isset($_GET['c']) && is_numeric($_GET['c']) && isset($_GET['i']) && is_numeric($_GET['i']) && isset($_GET['n']) && ($_GET['n']=='Activities' || $_GET['n']=='Jobs'||$_GET['n']=='Products/Services'||$_GET['n']=='Occasions'||$_GET['n']=='Wanted'||$_GET['n']=='Lost'))|| isset($_GET['page'] ) ) {
$catid =isset($_GET['i'])?$_GET['i']:''; //category id
$catName =isset($_GET['n'])?$_GET['n']:''; //category name
$count =isset($_GET['c'])?$_GET['c']:''; //country id
$countryName ='';
$country=$count<1||$count>4?1:$count;
$category=$catid<1||$catid>6?0:$catid;
//================================
// pagination
echo 'pageNum='. $pageNum= isset($_GET['page']) && is_numeric($_GET['page']) ? intval($_GET['page']) : 1;
$adsPerPage=3;
echo '<BR>startFrom='. $startFrom=($pageNum-1)* $adsPerPage;
//==============================
//country & category are false
if(($country<1 || $country>4) && ($category<1 || $category>6) ){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join sub on items.subcat_id=sub.subcat_id
join categories on categories.cat_id=items.CAT_ID
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where categories.cat_id>0 and country.country_id=1
");
$stmt->execute();
$adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join sub on items.subcat_id=sub.subcat_id
join categories on categories.cat_id=items.CAT_ID
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where categories.cat_id>0 and country.country_id=1
limit $startFrom,$adsPerPage ");
$stmt->execute();
$item= $stmt->fetchAll();
//country & category are true
}elseif(($country>=1 && $country<=4) && ($category>=1 && $category<=6) ){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join sub on items.subcat_id=sub.subcat_id
join categories on categories.cat_id=items.CAT_ID
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where categories.cat_id =? and country.country_id=?
");
$stmt->execute(array($category,$country));
$adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join sub on items.subcat_id=sub.subcat_id
join categories on categories.cat_id=items.CAT_ID
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where categories.cat_id =? and country.country_id=?
limit $startFrom,$adsPerPage ");
$stmt->execute(array($category,$country));
$item= $stmt->fetchAll();
//country is true, category is false
}elseif(($country>=1 && $country<=4) && ($category<1 || $category>6) ){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join sub on items.subcat_id=sub.subcat_id
join categories on categories.cat_id=items.CAT_ID
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where categories.cat_id>0 and country.country_id=?
");
$stmt->execute(array($country));
$adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join sub on items.subcat_id=sub.subcat_id
join categories on categories.cat_id=items.CAT_ID
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where categories.cat_id>0 and country.country_id=?
limit $startFrom,$adsPerPage ");
$stmt->execute(array($country));
$item= $stmt->fetchAll();
//country is false, category is true
}elseif(($country<1 || $country>4) && ($category>=1 && $category<=6) ){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join sub on items.subcat_id=sub.subcat_id
join categories on categories.cat_id=items.CAT_ID
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where categories.cat_id=? and country.country_id=1
");
$stmt->execute(array($category));
echo 'adsTotalNumb='. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join sub on items.subcat_id=sub.subcat_id
join categories on categories.cat_id=items.CAT_ID
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where categories.cat_id=? and country.country_id=1
limit $startFrom,$adsPerPage ");
$stmt->execute(array($category));
$item= $stmt->fetchAll();
}
//===================start pagination=========================
$jumpForward=3;
$jumpBackward=3;
if($NumberOfPages>1 ){
if(isset($_GET['page'])){
echo '<span style="margin-left:35vw">page '.$_GET['page'].'</span>';}else{
echo '<span style="margin-left:35vw">page 1</span>';
}
?>
<nav aria-label="Page navigation example" class="pagination-container">
<ul class="pagination pagination-lg">
<?php if(($pageNum-$jumpBackward)>=1 ){ //previous
?> <li class="page-item"><a class="page-link prev" href="?page=<?php echo ($pageNum-$jumpBackward)?>">Previous (-3)</a></li><?php
}else{
?> <li class="page-item disabled"><a class="page-link">Previous</a></li><?php
}
///**/
for ($page=1; $page<= $NumberOfPages; $page++) { //for loop
echo '<li class="page-item"><a class="page-link" href="search.php?page='. $page.'">'. $page.'</a></li>';
}
if(($pageNum+$jumpForward)<=$NumberOfPages ){ //next
?> <li class="page-item"> <a class="page-link next" href="?page=<?php echo ($pageNum+$jumpForward)?>" >Next (+3)</a> </li><?php
}else{
?> <li class="page-item disabled"> <a class="page-link " >Last</a> </li><?php
}
?>
</ul >
</nav>
<?php
}
}
===================================
الصفحة التي تجلب الاعلانات بناء على كود الاجاكس
//GET COMING FROM SEARCH PAGE TO RETURN ITEMS BASD ON AJAX
if ( (isset($_GET['cat']) || isset($_GET['sub']) || isset($_GET['country']) || isset($_GET['state']) || isset($_GET['city']) || isset($_GET['search']) ) || isset($_GET['page'] ) ) {
$category =$_GET['cat'];
$subcat =$_GET['sub'];
$country=$_GET['country'];
$state =$_GET['state'];
$city =$_GET['city'];
$search =$_GET['search'];
echo 'pagenum='. $pageNum= isset($_GET['page']) && is_numeric($_GET['page']) ? intval($_GET['page']) : 1;
$adsPerPage=2;
$startFrom=($pageNum-1)* $adsPerPage;
if( $category>0 && $subcat==0 && $state==0 && $city==0){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and categories.cat_id=? ");
$stmt->execute(array($country,$category));
echo 'adsTotalNumber'. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//GET ADS
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and categories.cat_id=?
limit $startFrom,$adsPerPage ");
$stmt->execute(array($country,$category));
$item= $stmt->fetchAll();
}elseif( $category==0 && $subcat==0 && $state==0 && $city==0){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? ");
$stmt->execute(array($country));
echo 'adsTotalNumber'. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//GET ADS
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? limit $startFrom,$adsPerPage ");
$stmt->execute(array($country));
$item= $stmt->fetchAll();
}elseif( $category==0 && $subcat==0 && $state>0 && $city==0){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=? ");
$stmt->execute(array($country,$state));
echo 'adsTotalNumber'. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=? ");
$stmt->execute(array($country,$state));
$item= $stmt->fetchAll();
}elseif( $category==0 && $subcat==0 && $state>0 && $city>0){
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=?
and city.city_id=? ");
$stmt->execute(array($country,$state,$city));
echo 'adsTotalNumber'. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=?
and city.city_id=? ");
$stmt->execute(array($country,$state,$city));
$item= $stmt->fetchAll();
}elseif( $subcat==0 && $state>0 && $city==0){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=?
and categories.cat_id=?");
$stmt->execute(array($country,$state,$category));
echo 'adsTotalNumber'. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=?
and categories.cat_id=? limit $startFrom,$adsPerPage");
$stmt->execute(array($country,$state,$category));
$item=$stmt->fetchAll();
}elseif( $subcat>0 && $state>0 && $city==0){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=?
and sub.subcat_id =?");
$stmt->execute(array($country,$state,$subcat));
echo 'adsTotalNumber'. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=?
and sub.subcat_id =? limit $startFrom,$adsPerPage");
$stmt->execute(array($country,$state,$subcat));
$item=$stmt->fetchAll();
}elseif( $subcat>0 && $state>0 && $city>0){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=?
and sub.subcat_id =? and city.city_id=?");
$stmt->execute(array($country,$state,$subcat,$city));
echo 'adsTotalNumber'. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=?
and sub.subcat_id =? and city.city_id=? limit $startFrom,$adsPerPage");
$stmt->execute(array($country,$state,$subcat,$city));
$item=$stmt->fetchAll();
}elseif( $subcat==0 && $state>0 && $city>0){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=?
and city.city_id=? and categories.cat_id=? ");
$stmt->execute(array($country,$state,$city,$category));
echo 'adsTotalNumber'. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and state.state_id=?
and city.city_id=? and categories.cat_id=? limit $startFrom,$adsPerPage");
$stmt->execute(array($country,$state,$city,$category));
$item=$stmt->fetchAll();
}elseif( $subcat==0 && $state==0 && $city>0){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and city.city_id=?
and categories.cat_id=? ");
$stmt->execute(array($country,$city,$category));
echo 'adsTotalNumber'. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//get ads
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and city.city_id=?
and categories.cat_id=? limit $startFrom,$adsPerPage");
$stmt->execute(array($country,$city,$category));
$item=$stmt->fetchAll();
}elseif( $subcat>0 && $state==0 && $city==0){
$stmt=$conn->prepare("
SELECT count(item_id)
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and sub.subcat_id=? ");
$stmt->execute(array($country,$subcat));
echo 'adsTotalNumber'. $adsTotalNumber=$stmt->fetchColumn();
echo '<br>NumberOfPa='. $NumberOfPages=ceil($adsTotalNumber/$adsPerPage);
//GET ADS
$stmt=$conn->prepare("
SELECT sub.*,categories.*,items.*,country.*,state.*,city.*
FROM items
join categories on categories.cat_id=items.CAT_ID
join sub on items.subcat_id=sub.subcat_id
JOIN country ON items.country_id=country.country_id
JOIN state ON items.state_id=state.state_id
JOIN city ON items.city_id=city.city_id
where country.country_id=? and sub.subcat_id=? limit $startFrom,$adsPerPage ");
$stmt->execute(array($country,$subcat));
$item=$stmt->fetchAll();
//===================start pagination=========================
$jumpForward=3;
$jumpBackward=3;
if($NumberOfPages>1 ){
if(isset($_GET['page'])){
echo '<span style="margin-left:35vw">page '.$_GET['page'].'</span>';}else{
echo '<span style="margin-left:35vw">page 1</span>';
}
?>
<nav aria-label="Page navigation example" class="pagination-container">
<ul class="pagination pagination-lg">
<?php if(($pageNum-$jumpBackward)>=1 ){ //previous
?> <li class="page-item"><a class="page-link prev" href="?page=<?php echo ($pageNum-$jumpBackward)?>">Previous (-3)</a></li><?php
}else{
?> <li class="page-item disabled"><a class="page-link">Previous</a></li><?php
}
/*$page=max(1,$pageNum-5);$page<=min($pageNum+2,$NumberOfPages);$page++*/
for ($page=1; $page<= $NumberOfPages; $page++) { //for loop
echo '<li class="page-item"><a class="page-link" href="search.php?page='. $page.'">'. $page.'</a></li>';
}
if(($pageNum+$jumpForward)<=$NumberOfPages ){ //next
?> <li class="page-item"> <a class="page-link next" href="?page=<?php echo ($pageNum+$jumpForward)?>" >Next (+3)</a> </li><?php
}else{
?> <li class="page-item disabled"> <a class="page-link " >Last</a> </li><?php
}
?>
</ul >
</nav>
<?php
}
}
عندي جدول منتجات به عدة حقول منها item_id, name, description, photo أريد أن أضيف ساعات العمل الى المنتج؛ هل أضيف الحقول الاتية الى ذلك الجدول أم الأفضل أن أنشأ جدول جديد؛ ولو أنشأت جدول جديد كيف يكون الربط الصحيح الحقول هي from_saturday, to_saturday,from_sunday,to_sunday...etc