if i insert more than one its taken only one value.database field city_id type is int

$city=intval($_GET['city']);
$query="SELECT `city_id`, `city` FROM `cities` WHERE `state_id`='$city'";
$result=mysql_query($query);

 while($row=mysql_fetch_array($result)) { ?>
<li>
<input type="checkbox"  name="place[]" value="<?=$row['city_id']?>" /><span><?=$row['city']?></span>
</li>
<? } ?>



$ids = array();
                foreach($_POST['place'] as $val)
                {
                $ids[] = (int) $val;
                }
                echo $ids = implode(',', $ids);
                $sql3 = mysql_query("INSERT INTO `city_place`(`state_id`,`city_id`,`spaid`) VALUES ('$cityid'," . implode('),(', $ids) . ",$lid)");

Hello,

note that you are already overwriting the $ids variable on line 18:

echo $ids = implode(',', $ids);

So, when you try to perform the same action on line 19, you should get a warning like implode(): Invalid arguments passed... because it expects an array, and instead it gets a string: resulting with an empty "string". The other problem is that the second implode is not surrounded by quotes, so the result will look like:

INSERT INTO `city_place` (`state_id`, `city_id`, `spaid`) VALUES('123', 1),(2),(3),(4, 900)

which can be wrong:

  • if you're trying to store multiple values in one single insert query, then you must replicate all the columns into each group of values, so:

    VALUES('123', 1, 900), ('123', 2, 900), ('123', 3, 900)

You must modify your script this way:

$values = array();
$sql = "INSERT INTO `city_place` (`state_id`, `city_id`, `spaid`) VALUES";

foreach($ids as $value)
{
    $values[] = "($city_id, $value, $lid)";
}

$sql .= implode(', ', $values);
mysql_query($sql) or die(mysql_error());

To get:

select * from city_place;
+----------+---------+-------+
| state_id | city_id | spaid |
+----------+---------+-------+
|      123 | 1       |   900 |
|      123 | 2       |   900 |
|      123 | 3       |   900 |
|      123 | 4       |   900 |
+----------+---------+-------+
4 rows in set (0.01 sec)
  • to insert the list in one single column, instead, you have to add quotes:

    sql3 = "INSERT INTO city_place(state_id,city_id,spaid) VALUES ('$cityid', '" . implode(',', $ids) . "', $lid)";

To get:

select * from city_place;
+----------+---------+-------+
| state_id | city_id | spaid |
+----------+---------+-------+
|      123 | 1,2,3,4 |   900 |
+----------+---------+-------+
1 row in set (0.00 sec)

But in this last case city_id cannot be an integer column type.

thanks it works perfectly.
and one more question after insert i have to split the city_id.and in edit page to display all city values with checked and non checked values in checkbox. if i use this coding am getting all values checked.

 <?php 
 $query = "select s.*,cp.state_id,cp.city_id from spa s LEFT JOIN city_place cp ON cp.spaid=s.id where s.id=$id";
$result = mysql_query($query); ?>
<form action='editsql.php' method='post' name='myform' enctype='multipart/form-data'>

<?php $row = mysql_fetch_array($result);
          $catid = $row['state_id'];

 $sql4 = mysql_query("SELECT `state_id`, `state` FROM `states` "); ?>
                <select name="city" id="city" onChange="getPlace(this.value)" >
               <option value="0" selected="selected">...SELECT CITY...</option> 
            <?   while($s = mysql_fetch_array($sql4))
                {
                    $selected = "";
                    $categoryId = $s['state_id'];
                    $categoryName = $s['state'];

                    if($catid == $categoryId)
                    {
                    $selected = "selected";
                    }?>
                <option value="<?php echo $s['state_id']; ?>" <?php echo $selected; ?> ><?php echo $s['state']; ?></option><?php } ?>
                </select>

after the dropdown box value i have to display city values based on state_id
here is my 4 tables
city_place,cities,states,spa

echo $result = mysql_query("SELECT DISTINCT c.`city`,c.`city_id` from cities c");
while($p=mysql_fetch_array($result)) 
{
    $state_id = $p['state_id'];
    $city_id = $p['Place'];
    $place = $_GET['place'];
    $city = $p['city'];

    //$cityex = $row['city_id'][];
    $ids = array($city_id);
    echo $latlngStrs = place.split(",",$city_id);
    $i=0;
if($latlngStrs[$i]!= '')
{

$latlngStrs[$i]=mysql_query("select city_id from city_place where city_id=$latlngStrs[$i]");
$i++;
}



?>
<li>
<? $checked = (is_null($latlngStrs)) ? '' : " checked"; ?>
<input type="checkbox"  name="place[$latlngStrs]" value="<?=$latlngStrs?>" onfocus="myfunction()" <?php echo $checked; ?>/><span><?=$p['city']?></span>

</li><?php } ?>

how to split the values and assign to checkbox checked values

Ok, but what is the condition to sign the checkboxes? A match between $_GET['place'] and city_id?

no i checked whether it works or not from previous page am getting the city ids through url.
if cities.city_id==city_place.city_id means ticked checkbox

Ok, there are few problems in the above code. The first problem is given by this line:

$ids = array($city_id);

Even if $city_id is a comma separated list, for MySQL it is always a string, so it is for the MySQL API in PHP, and the output generated by the above line is:

Array
(
    [0] => 1,2,3
)

When instead you may want something like this:

Array
(
    [0] => 1
    [1] => 2
    [2] => 3
)

The other problem is at line 11:

echo $latlngStrs = place.split(",",$city_id);

Assuming that place is $place, this will generate:

1,2,3Array

considering this output, the query at line 16:

$latlngStrs[$i]=mysql_query("select city_id from city_place where city_id=$latlngStrs[$i]");

Will try to compare it to the city_place.city_id, so:

select city_id from city_place where city_id = '1,2,3Array';

It will return an empty result set. Then, since split() (which is deprecated) can return an array or a boolean FALSE, but never NULL, the condition at line 24:

$checked = (is_null($latlngStrs)) ? '' : " checked";

will always return FALSE, so you will always get checked.

Now, the script can be solved with a single query which takes the rows from city_place with the same spa.id of the first query and uses FIND_IN_SET to compare cities.city_id against city_place.city_id and returns a checked column, basically:

select c.city, c.city_id, (CASE WHEN FIND_IN_SET(c.city_id, cp.city_id) THEN 1 ELSE 0 END) as checked from cities as c, city_place as cp where cp.spaid = 420;

+------+---------+---------+
| city | city_id | checked |
+------+---------+---------+
| AAA  |      18 |       1 |
| BBB  |      20 |       1 |
| CCC  |      34 |       1 |
| DDD  |      35 |       0 |
+------+---------+---------+
4 rows in set (0.00 sec)

Where 1 stands for true, 0 for false. Your script now becomes:

    <?php

    $place      = $_GET['place'];
    $state_id   = $s['state_id'];
    $result     = mysql_query("select c.city, c.city_id, (CASE WHEN FIND_IN_SET(c.city_id, cp.city_id) THEN 1 ELSE 0 END) as checked from cities as c, city_place as cp where cp.spaid = {$id}");

    echo "<ul>";

    while($p=mysql_fetch_array($result)) 
    {
        $city_id    = $p['city_id'];
        $city       = $p['city'];
        $checked    = $p['checked'] == 0 ? '' : " checked";

        echo "
        <li>
            <label>
            <input type='checkbox' name='place[{$city_id}]' value='{$city_id}' onfocus='myfunction()' {$checked} />
                {$p['city']}
            </label>
        </li>
        ";

    }

    echo "</ul>";

    ?>

What I'm still not sure about is the spa.City column, which seems to be the state_id column of the other tables. If this is correct then your previous:

    $state_id   = $p['state_id'];

Can be written before the WHILE loop and chosen from the previous query:

    $state_id   = $s['state_id'];

Also because the old query does not return the column state_id and that would generate a notice from PHP. But I don't understand if you want to restrict the query of the cities table, to list only those cities in a particular state (state_id 11 for example).

Anyway, hope it helps!

Thanks cereal it works thank you so much i was stuck in that.here it showing all city values but i need to show state related.
And one more question i have a dropdownbox value is states if i select any state that related cities will show.but in edit page i got that,one selected state from privious page and related cities. if i select diff state i want show that related cities without checked.

<tr><td><div align="left">City : </div></td><td>


                <?php $sql4 = mysql_query("SELECT `state_id`, `state` FROM `states` "); ?>
                <select name="city" id="city" onChange="getPlace(this.value)" >
               <option value="0" selected="selected">...SELECT CITY...</option> 
            <?   while($s = mysql_fetch_array($sql4))
                {
                    $selected = "";
                    $categoryId = $s['state_id'];
                    $categoryName = $s['state'];

                    if($catid == $categoryId)
                    {
                    $selected = "selected";
                    }?>
                <option value="<?php echo $s['state_id']; ?>" <?php echo $selected; ?> ><?php echo $s['state']; ?></option><?php } ?>
                </select>
                <?php //} ?>
                </td></tr>

                <tr>
                  <td colspan="2"><div align="left" id="citydiv"><ul>

<?php // c.`state_id`,cp.city_id,cp.state_id,s.id,s.Place FROM spa s LEFT JOIN `cities` c ON s.Place=c.city_id  LEFT JOIN city_place cp ON cp.city_id=c.city_id WHERE s.id='$id'
$place      = $_GET['place'];
    $state_id   = $s['state_id'];
    $result     = mysql_query("select c.city, c.city_id, (CASE WHEN FIND_IN_SET(c.city_id, cp.city_id) THEN 1 ELSE 0 END) as checked from cities as c, city_place as cp where cp.spaid = {$id}");

    while($p=mysql_fetch_array($result)) 
    {
        $city_id    = $p['city_id'];
        $city       = $p['city'];
        $checked    = $p['checked'] == 0 ? '' : " checked";
        echo "
        <li>
            <label>
            <input type='checkbox' name='place[{$city_id}]' value='{$city_id}' onfocus='myfunction()' {$checked} />
                {$p['city']}
            </label>
        </li>
        ";
    }


?>
</ul></div></td>
                </tr>

script

function getPlace(stateId) {        

        var strURL="findplace1.php?city="+stateId;
        var req = getXMLHTTP();

        if (req) {

            req.onreadystatechange = function() {
                if (req.readyState == 4) {
                    // only if "OK"
                    if (req.status == 200) {                        
                        document.getElementById('citydiv').innerHTML=req.responseText;                      
                    } else {
                        alert("There was a problem while using XMLHTTP:\n" + req.statusText);
                    }
                }               
            }           
            req.open("GET", strURL, true);
            req.send(null);
        }       
    }

findplace1.php

<? $city=intval($_GET['city']);
include("db.php");
$query="SELECT `city_id`, `city` FROM `cities` WHERE `state_id`='$city'";
$result=mysql_query($query);

?>
<table width="100%" cellpadding="0" cellspacing="0" border="0" align="left">
<tr>
<td width="197" valign="top"><div align="left">Place:</div></td><td>
<ul class="subcat_tick">
<? while($row=mysql_fetch_array($result)) { ?>
<li>
<input type="checkbox"  name="place[]" value="<?=$row['city_id']?>" /><span><?=$row['city']?></span>
</li>
<? } ?>
</ul>
</td>
</tr></table>

how to combine with these two

here it showing all city values but i need to show state related.

To solve this just change the query at line 28:

$state_id   = $s['state_id'];
$result     = mysql_query("select c.city, c.city_id, (CASE WHEN FIND_IN_SET(c.city_id, cp.city_id) THEN 1 ELSE 0 END) as checked from cities as c, city_place as cp where cp.spaid = {$id}");

With:

$state_id   = $catid; // IMPORTANT
$result     = mysql_query("select c.city, c.city_id, (CASE WHEN FIND_IN_SET(c.city_id, cp.city_id) THEN 1 ELSE 0 END) as checked from cities as c, city_place as cp where cp.spaid = {$id} and c.state_id = {$state_id}");

In practice we are adding the condition c.state_id = {$state_id} to the WHERE clause. This should restrict the result set. Note the change to the $state_id variable, because otherwise you get an empty string and the query does not work fine.

And one more question i have a dropdownbox ...

Always in relation with $id? In this case you can move/copy the same script to the findplace1.php file, add an hidden input field to your form to save the id and add it to the AJAX request. For example, in the JS instead of:

function getPlace(stateId) {        
    var strURL="findplace1.php?city="+stateId;

Write:

function getPlace(stateId) {
    var spaid = document.getElementById('spaid').value;
    var strURL="findplace1.php?city="+stateId+"&id="+spaid;

To the form add:

<input type="hidden" name="spaid" id="spaid" value="<?php echo $id; ?> />

The findplace1.php can be rewritten:

<?php

include("db.php");
$city   = intval($_GET['city']);
$id     = intval($_GET['id']);

$result = mysql_query("select c.city, c.city_id, (CASE WHEN FIND_IN_SET(c.city_id, cp.city_id) THEN 1 ELSE 0 END) as checked from cities as c, city_place as cp where cp.spaid = {$id} and c.state_id = {$city}") or die(mysql_error());

echo "
    <div align=\"left\" id=\"citydiv\"><ul>";

    while($p=mysql_fetch_array($result)) 
    {
        $city_id    = $p['city_id'];
        $city       = $p['city'];
        $checked    = $p['checked'] == 0 ? '' : " checked";

        echo "
        <li>
            <label>
            <input type='checkbox' name='place[{$city_id}]' value='{$city_id}' onfocus='myfunction()' {$checked} />
                {$p['city']}
            </label>
        </li>
        ";

    }

    echo "</ul></div>";

?>

And it should work fine.

its working fine what i expect thank you so much.

i need a pagination for rel="country1" and rel="country2" in same page. here i added pagination code but that is not working properly.it works only one pagination code for one page.you have any idea about that.

<div class="leftcontainer">
<ul id="countrytabs" class="shadetabs">
<li selected=""><a class="selected" href="index.php?city=$city&search" rel="country1" title="Search Categories">Search Categories</a></li>
<li><a class="" href="index.php?city=$city&article&page=1" rel="country2" title="Article Categories">Article Categories</a></li>
<li><a class="" href="#" rel="country3" title="Best Deals">Best Deals</a></li>
<li><a class="" href="#" rel="country4" title="Why Us?" style="margin-right:0px;">Why Us?</a></li>
</ul>
<div style="padding:0 5px 3px;">         
            <!-- Contents Tab1 Begins here -->
<div style="display: none;" id="country1" class="other_tabs" valign="top">
             <?php
    /*
        Place code to connect to your DB here.
    */
    include('db.php');  // include your code to connect to DB.
    $tbl_name="catagorykeywords";       //your table name
    // How many adjacent pages should be shown on each side?
    $adjacents = 3;
    /* 
       First get total number of rows in data table. 
       If you have a WHERE clause in your query, make sure you mirror it here.
    */
    $query = "select count(DISTINCT MainCatagory,id,name,path,size) as num from catagorykeywords ";
    $total_pages = mysql_fetch_array(mysql_query($query));
    $total_pages = $total_pages[num];
    /* Setup vars for query. */
    $targetpage = "index.php?city=$city&search";    //your file name  (the name of this file)
    $limit = 10;                                //how many items to show per page
    $page = $_GET['page'];
    if($page) 
        $start = ($page - 1) * $limit;          //first item to display on this page
    else
        $start = 0;                             //if no page var is given, set start to 0
    /* Get data. */
    $sql = mysql_query("select DISTINCT MainCatagory,id,name,path,size from catagorykeywords  ORDER BY id  DESC LIMIT $start,$limit");
    //$result = mysql_query($sql);
    /* Setup page vars for display. */
    if ($page == 0) $page = 1;                  //if no page var is given, default to 1.
    $prev = $page - 1;                          //previous page is page - 1
    $next = $page + 1;                          //next page is page + 1
    $lastpage = ceil($total_pages/$limit);      //lastpage is = total pages / items per page, rounded up.
    $lpm1 = $lastpage - 1;                      //last page minus 1
    /* 
        Now we apply our rules and draw the pagination object. 
        We're actually saving the code to a variable in case we want to draw it more than once.
    */
    $pagination = "";
    if($lastpage > 1)
    {   
        $pagination .= "<div class=\"pagination\">";
        //previous button
        if ($page > 1) 
            $pagination.= "<a href=\"$targetpage&page=$prev\"> previous</a>";
        else
            $pagination.= "<span class=\"disabled\"> previous</span>";    
        //pages 
        if ($lastpage < 7 + ($adjacents * 2))    //not enough pages to bother breaking it up
        {   
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class=\"current\">$counter</span>";
                else
                    $pagination.= "<a href=\"$targetpage&page=$counter\">$counter</a>";                   
            }
        }
        elseif($lastpage > 5 + ($adjacents * 2))    //enough pages to hide some
        {
            //close to beginning; only hide later pages
            if($page < 1 + ($adjacents * 2))     
            {
                for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage&page=$counter\">$counter</a>";                   
                }
                $pagination.= "...";
                $pagination.= "<a href=\"$targetpage&page=$lpm1\">$lpm1</a>";
                $pagination.= "<a href=\"$targetpage&page=$lastpage\">$lastpage</a>";     
            }
            //in middle; hide some front and some back
            elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
            {
                $pagination.= "<a href=\"$targetpage&page=1\">1</a>";
                $pagination.= "<a href=\"$targetpage&page=2\">2</a>";
                $pagination.= "...";
                for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage&page=$counter\">$counter</a>";                   
                }
                $pagination.= "...";
                $pagination.= "<a href=\"$targetpage&page=$lpm1\">$lpm1</a>";
                $pagination.= "<a href=\"$targetpage&page=$lastpage\">$lastpage</a>";     
            }
            //close to end; only hide early pages
            else
            {
                $pagination.= "<a href=\"$targetpage&page=1\">1</a>";
                $pagination.= "<a href=\"$targetpage&page=2\">2</a>";
                $pagination.= "...";
                for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage&page=$counter\">$counter</a>";                   
                }
            }
        }
        //next button
        if ($page < $counter - 1) 
            $pagination.= "<a href=\"$targetpage&page=$next\">next </a>";
        else
            $pagination.= "<span class=\"disabled\">next </span>";
        $pagination.= "</div>\n";        
    }
?>                
                    <?php
                        /*include("db.php");
                        $sql = mysql_query("select DISTINCT Maincatagory from spa where City='$city'");
                           while($v = mysql_fetch_array($sql))
                            {
                              $maincatagory=$v['Maincatagory'];
                              echo'<div class="subcatagoriesleft"><h2>'.$v['Maincatagory'].'</h2>';
                                 $sql1 = mysql_query("select DISTINCT Catagory from spa where Maincatagory='$maincatagory'");
                                  echo'<ul>';
                                      while($v1= mysql_fetch_array($sql1))
                                      {
                                      echo'<li><a href="searchbycat.php?catagory='.$v1['Catagory'].'">'.$v1['Catagory'].',</a></li>';
                                      }
                                  echo'</ul>';
                              echo'</div>';
                             // echo'<hr/>';
                             }*/
                             include("db.php");
                           while($v = mysql_fetch_array($sql))
                            {
                              $maincatagory=$v['MainCatagory'];
                              //$subcategory = $v['Subcatagory'];
                              $catid = $v['id'];
                              $name = $v['name'];
                              $path = $v['path'];
                              $size = $v['size']; ///backend/upimg/caticon/'.$v['name'].'
                              date_default_timezone_set('UTC');
                                $today = date("Y-m-d");
                                $dt = $today.'_'.time();
                                $enc = urlencode($maincatagory);
                              if($name)
                              echo '<div class="subcatagoriesleft"><img src="/backend/'.$v['path'].'" width="32px" height="32px" ><h2><a href="searchbycat.php?catagory='.$enc.'&maincateg='.$enc.'&maincatid='.$v['id'].'&city=$city">'.$maincatagory.'</a></h2>';
                              else
                              echo '<div class="subcatagoriesleft"><img src="/backend/images/default.png" width="32px" height="32px" ><h2><a href="searchbycat.php?catagory='.$enc.'&maincateg='.$enc.'&maincatid='.$v['id'].'&city=$city">'.$v['MainCatagory'].'</a></h2>';
                                 $sql1 = mysql_query("select DISTINCT sub.Subcatagory,sub.id,sub.MainCatid,ck.MainCatagory from subcatagorykeywords sub JOIN catagorykeywords ck ON ck.id=sub.MainCatid where sub.MainCatid='$catid' ORDER BY sub.id DESC LIMIT 0,10");
                                  echo '<div class="subcat_tick"><ul>';
                                      while($v1= mysql_fetch_array($sql1))
                                      {
                                          $subcategory = $v1['Subcatagory'];
                                          $position=20; 
                                          $Subcatagory11 = substr($subcategory, 0, $position);
                                          $maincat = $v1['MainCatagory'];
                                          $encr = urlencode($maincat);
                                      echo '<li><a href="searchby_subcat.php?catagory='.$v1['Subcatagory'].'&subcatagory='.$v1['Subcatagory'].'&subcatid='.$v1['id'].'&maincatid='.$v1['MainCatid'].'&maincateg='.$encr.'&city=$city">'.$Subcatagory11.'</a></li>';
                                      }
                                  echo '</ul></div>';
                                   $sql12 = mysql_query("select DISTINCT Subcatagory,id,MainCatid from subcatagorykeywords where MainCatid='$catid' ORDER BY id DESC");
                                   $count1 = mysql_num_rows($sql12);
                                  if($count1>10)
                                  {
                              echo '<div class="toggle">
                              <div class="toggle_content">';
$sql9 = mysql_query("select DISTINCT sub.Subcatagory,sub.id,sub.MainCatid,ck.MainCatagory from subcatagorykeywords sub JOIN catagorykeywords ck ON ck.id=sub.MainCatid where sub.MainCatid='$catid' ORDER BY sub.id DESC LIMIT 10,30");
                                  echo '<div class="subcat_tick"><ul>';
                                      while($v9= mysql_fetch_array($sql9))
                                      {
                                          $subcategory = $v9['Subcatagory'];
                                          $position=20; 
                                          $Subcatagory11 = substr($subcategory, 0, $position);
                                          $maincatt = $v9['MainCatagory'];
                                          $encrr = urlencode($maincatt);
                                      echo '<li><a href="searchby_subcat.php?catagory='.$v9['Subcatagory'].'&subcatagory='.$v9['Subcatagory'].'&subcatid='.$v9['id'].'&maincatid='.$v9['MainCatid'].'&maincateg='.$encrr.'&city=$city">'.$Subcatagory11.'</a></li>';
                                      }
                                  echo '</ul></div>';
echo '</div><h4 class="toggle_title"><span class="toggle_indicator"></span>Read more</h4></div>';
                                }
                                echo '</div>';
                            } 
                         ?>
                      <div class="home_tab_page">
                        <?=$pagination;?>
                      </div>                
                <link rel="stylesheet" id="shortcodes-css-css" href="r-script/scoll/shortcodes.css" type="text/css" media="all">
<script type="text/javascript" src="r-script/scoll/jquery_007.js"></script>
<script type="text/javascript">
/* <![CDATA[ */
var _wpcf7 = {"loaderUrl":"http:\/\/www.redcamel.in\/wp-content\/plugins\/contact-form-7\/images\/ajax-loader.gif","sending":"Sending ..."};
/* ]]> */
</script>
<script type="text/javascript" src="r-script/scoll/scripts.js"></script>
<script type="text/javascript" src="r-script/scoll/shortcodes.js"></script>
<script type="text/javascript">
function wp_attempt_focus() {
setTimeout( function() {
try {
d = document.getElementById('user_login');
d.value = '';
d.focus();
} catch(e){}
}, 200 );
}
wp_attempt_focus();
if(typeof wpOnload=='function')wpOnload()
</script>
                </div>
             <!-- Contents Tab1 Ends here -->
            <!-- Contents Tab2 Begins here -->   
                <div style="display: block;" id="country2" class="other_tabs" >
                  <?php include("db.php");
                  $tbl_name="article";      //your table name
    // How many adjacent pages should be shown on each side?
    $adjacents = 3;
    /* 
       First get total number of rows in data table. 
       If you have a WHERE clause in your query, make sure you mirror it here.
    */
    $query = "SELECT count(DISTINCT ac.ArticleCategory,ac.id,a.ArticleTitle) as num FROM articlecategory ac JOIN article a ON a.Catagoryid=ac.id WHERE Publish=1";
    $total_pages = mysql_fetch_array(mysql_query($query));
    $total_pages = $total_pages[num];
    /* Setup vars for query. */
    $targetpage = "index.php?city=$city&article";   //your file name  (the name of this file)
    $limit = 5;                                 //how many items to show per page
    $page = $_GET['page'];
    if($page) 
        $start = ($page - 1) * $limit;          //first item to display on this page
    else
        $start = 0;                             //if no page var is given, set start to 0
    /* Get data. */
                $sql3 = mysql_query("select DISTINCT ac.ArticleCategory,ac.id,a.ArticleTitle from article a JOIN articlecategory ac ON a.Catagoryid=ac.id  LIMIT $start,$limit");
                $result = mysql_query($sql3);
    /* Setup page vars for display. */
    if ($page == 0) $page = 1;                  //if no page var is given, default to 1.
    $prev = $page - 1;                          //previous page is page - 1
    $next = $page + 1;                          //next page is page + 1
    $lastpage = ceil($total_pages/$limit);      //lastpage is = total pages / items per page, rounded up.
    $lpm1 = $lastpage - 1;                      //last page minus 1
    /* 
        Now we apply our rules and draw the pagination object. 
        We're actually saving the code to a variable in case we want to draw it more than once.
    */
    $pagination = "";
    if($lastpage > 1)
    {   
        $pagination .= "<div class=\"pagination\">";
        //previous button
        if ($page > 1) 
            $pagination.= "<a href=\"$targetpage&page=$prev\"> Previous</a>";
        else
            $pagination.= "<span class=\"disabled\"> Previous</span>";    
        //pages 
        if ($lastpage < 7 + ($adjacents * 2))    //not enough pages to bother breaking it up
        {   
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class=\"current\">$counter</span>";
                else
                    $pagination.= "<a href=\"$targetpage&page=$counter\">$counter</a>";                   
            }
        }
        elseif($lastpage > 5 + ($adjacents * 2))    //enough pages to hide some
        {
            //close to beginning; only hide later pages
            if($page < 1 + ($adjacents * 2))     
            {
                for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage&page=$counter\">$counter</a>";                   
                }
                $pagination.= "...";
                $pagination.= "<a href=\"$targetpage&page=$lpm1\">$lpm1</a>";
                $pagination.= "<a href=\"$targetpage&page=$lastpage\">$lastpage</a>";     
            }
            //in middle; hide some front and some back
            elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
            {
                $pagination.= "<a href=\"$targetpage&page=1\">1</a>";
                $pagination.= "<a href=\"$targetpage&page=2\">2</a>";
                $pagination.= "...";
                for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage&page=$counter\">$counter</a>";                   
                }
                $pagination.= "...";
                $pagination.= "<a href=\"$targetpage&page=$lpm1\">$lpm1</a>";
                $pagination.= "<a href=\"$targetpage&page=$lastpage\">$lastpage</a>";     
            }
            //close to end; only hide early pages
            else
            {
                $pagination.= "<a href=\"$targetpage&page=1\">1</a>";
                $pagination.= "<a href=\"$targetpage&page=2\">2</a>";
                $pagination.= "...";
                for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage&page=$counter\">$counter</a>";                   
                }
            }
        }
        //next button
        if ($page < $counter - 1) 
            $pagination.= "<a href=\"$targetpage&page=$next\">Next </a>";
        else
            $pagination.= "<span class=\"disabled\">Next </span>";
        $pagination.= "</div>\n";        
    }
                       while($v3 = mysql_fetch_array($sql3))
                        {
                            $take = $v3['ArticleTitle'];
                          $catagory = $v3['ArticleCategory'];
                          $articlecatid = $v3['id'];
                          if(!$take = '')
                          echo '<a href="articlesbycat.php?catagory='.$v3['ArticleCategory'].'&id='.$v3['id'].'" style="text-decoration:none;"><div class="tabdisp_articles"><h2>'.$v3['ArticleCategory'].'</h2></a>';
    $count = $_GET['counting'];
    $articleid = $_GET['id'];
    $sql5 = mysql_query("UPDATE article SET Count='$count'+1 WHERE Id='$articleid'");
                            $sql4 = mysql_query("SELECT * FROM article a JOIN articlecategory ac ON ac.id=a.Catagoryid WHERE a.Publish=1 AND a.Catagoryid='$articlecatid' ORDER BY a.Id DESC LIMIT 2");
    //$result = mysql_query($sql4);
                             //WHERE Catagoryid='$articlecatid' AND Publish=1 ORDER BY Id DESC
                              echo '<ul>';
                                  while($v4= mysql_fetch_array($sql4))
                                  {
                                  echo '<li><a href="articles.php?id='.$v4['Id'].'&catagory='.$v4['ArticleCategory'].'&catagoryid='.$v4['Catagoryid'].'&articletitle='.$v4['ArticleTitle'].'&counting='.$v4['Count'].'" onclick="counting()">'.$v4['ArticleTitle'].',</a></li>';
                                  }
                              echo '</ul>';
                              echo '<div class="readmore"><a href="articlesbycat.php?catagory='.$v3['ArticleCategory'].'&id='.$v3['id'].'">Read More</a></div>';
                          echo '</div>';
                         } ?>
                    <!--<div class="article" >
                        <h2>Most Recently Added Articles</h2>
                        <?php
                            include("db.php");
                            $sql2 = mysql_query("select * from article where Publish=1 order by Id desc LIMIT 10 ");
                           echo '<ul>';  
                           while($v2 = mysql_fetch_array($sql2))
                            {
                            echo '<li><a name="id" id="id" href="articles.php?id='.$v2['Id'].'">'.$v2['ArticleTitle'].'</a></li>';
                            }
                            echo '</ul>';
                         ?>
                    </div>-->
                    <div class="home_tab_page">
                      <?=$pagination?>   
                </div>                
                </div>
           <!-- Contents Tab2 Ends here -->
  </div>
  </div>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.