Page 1 of 1

PHP MySQL pagination and excel export

Posted: Wed Jun 04, 2014 5:44 am
by minghags
Hello!

Im trying to edit pagination links so that when there are more than 5-6 pages that then 5-6 pages are displayed as link <-- 3 | 3 --> and last and first page... and what i really want to get is export to excel just MySQL query that is at that moment active.

If anyone is kind enough to help please do...

So here is the code of current "product" :)

Code: Select all

<!DOCTYPE html>
<html>
<body>
<head>
<link rel="stylesheet" type="text/css" href="css/style.css">
<link rel="stylesheet" href="css/datepicker.css">
<link rel="stylesheet" href="css/bootstrap.css">

<script>
  function removeEmpties() {
        var form = document.getElementById("theform");
        var inputs = form.children;
        var remove = [];
        for(var i = 0; i < inputs.length; i++) {
            if(inputs[i].value == "") {
                remove.push(inputs[i]);
            }
        }

        if(remove.length == inputs.length - 1)
          return false;

        for(var i = 0; i < remove.length; i++) 
          form.removeChild(remove[i]);
        return true;
    }
</script>

<script src="js/jquery-1.9.1.min.js"></script>
<script src="js/bootstrap-datepicker.js"></script>
<script type="text/javascript">

            $(document).ready(function () {

                $('#fromsearchdate').datepicker({
                    format: "yyyy-mm-dd"
                });
                $('#tosearchdate').datepicker({
                    format: "yyyy-mm-dd"
                });
});
</script>

<br>
<a href="."><img src="images/header.png"></a>


<center>
<form id="theform" action="" method="GET" onsubmit="return removeEmpties()">
        <input type="text" placeholder="Agent..." name="dst">
        <input type="text" placeholder="Cakalna vrsta..."  name="queue" >
        <input type="text" placeholder="Telefonska st."  name="clid" >
        <input type="text" placeholder="Od..."  name="fromdate" id="fromsearchdate" >
        <input type="text" placeholder="Do..."  name="todate" id="tosearchdate" >
<div style="margin-top:-2px"><input type="submit" value="Iskanje"/></div>
</form>

<?php
// MySQL povezava
$con=mysqli_connect("*","*","*","*");

if (mysqli_connect_errno()) {
  echo "MySQL: ni povezave! " . mysqli_connect_error();
}


// $actual_link = 'http:/.../callrecord.php';

# parametri kateri nas zanimajo
$params = array('id', 'order', 'clid', 'fromdate', 'todate', 'dst', 'queue', 'sort', 'desc', 'page');

$used_params = array();

$data = array();

foreach ($params as $par)
{
    if (isset($_GET[$par]))
        $data[$par] = $_GET[$par];
}

if(isset($data['dst'])) {
  echo "Statistika klicev za agenta " . $data['dst'] . "";
}

if(isset($data['queue'])) {
  echo " v cakalni vrsti " . $data['queue'] . ".";
}

$desc = isset($data['desc']) ? $data['desc'] == '1' :  FALSE;
$page = isset($data['page']) ? intval($data['page']) : 1;
$sort = isset($data['sort']) ? intval($data['sort']) : 0;

function append_param($url, $param, $value)
{	
    if (($url != '') && ($url[strlen($url)-1] != '?'))
	$url .= '&';

    return $url . $param . '=' . $value;
}

function my_query_sql($mode, $data, $sort = 0, $desc = FALSE, $start_from = FALSE)
{
    global $used_params;
    global $con;
    
    $where = array();

    // sestaviš pogoje...
    
    if (isset($data['clid']))
    {
        $where[] = "clid LIKE '" . $data['clid'] . "'";
        if ($mode == 'count')
            $used_params[] = 'clid=' . $data['clid'];
    }

    if (isset($data['queue']))
    {
        $where[] = "queue = " . intval($data['queue']);
        if ($mode == 'count')
            $used_params[] = 'queue=' . $data['queue'];
    }

    if (isset($data['dst']))
    {
        $where[] = "dst = " . intval($data['dst']);
        if ($mode == 'count')
            $used_params[] = 'dst=' . $data['dst'];
    }

    if (isset($data['fromdate']) && isset($data['todate']))
    {
        $where[] = "LEFT (calldate, 10) >= '" . $data['fromdate'] . "' AND LEFT (calldate, 10) <= '" . $data['todate'] . "'";
        if ($mode == 'count')
            $used_params[] = 'fromdate=' . $data['fromdate'] . '&todate=' . $data['todate'];
    }
  

    // .....

    if ($mode == 'count')   
    {
        $sql = 'SELECT count(1) as cnt FROM cc2_cdr';    
        if (count($where) > 0)
            $sql .= ' WHERE ' . implode(' AND ', $where);
        return  mysqli_query($con, $sql);        
    }

    
    $sql = 'SELECT * FROM cc2_cdr';
    if (count($where) > 0)
        $sql .= ' WHERE ' . implode(' AND ', $where);
        
    # order - mora se ujemat z kolonami  tabeli  
    $sort_fields = array('id', 'calldate', 'clid', 'dst', 'queue', 'queuesec', 'duration', 'disposition');
    if(isset($_GET['sort']))
    {
    if (($sort >= 0) && ($sort < count($sort_fields)))
    {
        $sql .= ' ORDER BY ' . $sort_fields[$sort];
	if ($desc)
           $sql .= ' DESC';
    }
    }else
{
        $sql .= ' ORDER BY ' . $sort_fields[1];
        if ($desc)
           $sql .= ' DESC';
}
    $sql .= ' LIMIT ' . $start_from . ', 30';

echo $sql;

    return mysqli_query($con, $sql);
}

// prestejemo recorde
$record_count = 0;
$result = my_query_sql('count', $data);
if ($row = mysqli_fetch_array($result))
{
    $record_count = intval($row['cnt']);
}


$total_pages = ($record_count / 30);
if (($total_pages * 30) < $record_count)
    $total_pages++;


$actual_link .= '?' . implode('&', $used_params);

#  ....&page=2&sort=1&dest=1
#  ....&page=2&sort=3


$columns = array();
$columns[] = array('name' => 'ID', 'align' => 'center');
$columns[] = array('name' => 'Datum', 'align' => 'center');
$columns[] = array('name' => 'Stevilka', 'align' => 'center');
$columns[] = array('name' => 'Agent', 'align' => 'center');
$columns[] = array('name' => 'Cakalna', 'align' => 'center');
$columns[] = array('name' => 'Cas v cakalni', 'align' => 'center');
$columns[] = array('name' => 'Dolzina klica', 'align' => 'center');
$columns[] = array('name' => 'Odgovorjen', 'align' => 'center');

echo "<table border='1'>";
echo "<tr>";
$i = 0;
foreach ($columns as $col)
{
    $url = $actual_link;
    $url = append_param($url, 'sort', $i);

    if (($sort == $i) && (!$desc))
        $url = append_param($url,'desc', '1');

    $url = append_param($url, 'page', $page);
        
    # if $col['center']
        
    echo "<td><center><b><a href='$url'>" . $col['name'] . "</a></b></center></td>";

    $i++;    
}
echo "</tr>";

$start_from = ($page - 1) * 30; // preveri formulo
$result = my_query_sql('data', $data, $sort, $desc, $start_from);

while($row = mysqli_fetch_array($result)) {
  echo "<tr class='rows'>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . date("d. m. Y h:m:s", strtotime($row['calldate'])) . "</td>";
  echo "<td>" . $row['clid'] . "</td>";
  echo "<td>" . $row['dst'] . "</td>";
  echo "<td>" . $row['queue'] . "</td>";
  echo "<td>" . gmdate("H:i:s", $row['queuesec']) . "</td>";
  echo "<td>" . gmdate("H:i:s", $row['duration']) . "</td>";
  echo "<td>" . $row['disposition'] . "</td>"; 
  if ($row['disposition'] == '1') {
        echo "<td><b><font color='#00CC00'>DA</font></b></td>";
    }else
  {
    echo"<td><b><font color='#FF0000'>NE</font></b></td>";
  }
  // echo "" . $callbyday . "";
}


echo "</table>"; 

echo "<br />"; 

for ($i=1; $i<=$total_pages; $i++) { 

    $url = $actual_link;
    $url = append_param($url, 'page', $i); 
    $url = append_param($url, 'sort', $sort);
    if ($desc)
	$url = append_param($url, 'desc', '1');

    if ($i != $page)
        echo "<a href='" . $url . "'>".$i."</a> "; 
    else
        echo '<b>' . $i . '</b> ';
}; 

echo "<br /><br /><br />";

mysqli_close($con);
?>
</center>
</body>
</html>

Re: PHP MySQL pagination and excel export

Posted: Wed Jun 04, 2014 7:09 am
by Celauran
minghags wrote:what i really want to get is export to excel just MySQL query that is at that moment active.
The query or the query results? Does it need to be Excel, or can it be CSV (much easier)?

Re: PHP MySQL pagination and excel export

Posted: Wed Jun 04, 2014 7:15 am
by minghags
query results and its not really that important if its Excel or CSV.

Thank you

Re: PHP MySQL pagination and excel export

Posted: Wed Jun 04, 2014 7:28 am
by Celauran
In that case, while you iterate over the DB results, append them to a string which you'll then write to a file.

Code: Select all

$file_output = '';
while ($row = $result->fetch()) {
	$file_output .= "{$row->foo},{$row->bar},{$row->baz}\n";
}

$fh = fopen('/path/to/file', 'w');
if ($fh !== false) {
	fwrite($fh, $file_output);
	fclose($fh);
}
(Note this is pseudo code and shouldn't be copied literally.)

Re: PHP MySQL pagination and excel export

Posted: Thu Jun 05, 2014 1:16 pm
by minghags
Thanks but I cant figure it out :/

Can anyone help me with this paging, i want to limit list page of pages if its more than 5 pages like 1, 2, 3 ,4 ,5 > >>, > next page >> last page and for example << < 6, 7, 8, 9, 10 > >>.

Here is my code that im using:

Code: Select all

    for ($i=1; $i<=$total_pages; $i++) {
        
        $url = $actual_link;
        $url = append_param($url, 'page', $i);
        $url = append_param($url, 'sort', $sort);
        if ($desc)
            $url = append_param($url, 'desc', '1');
        
        if ($i != $page)
            echo "<a href='" . $url . "'>".$i."</a> ";
        else
            echo '<b>' . $i . '</b> ';
    };

Re: PHP MySQL pagination and excel export

Posted: Thu Jun 05, 2014 2:52 pm
by Celauran
What can't you figure out? What have you tried?

Re: PHP MySQL pagination and excel export

Posted: Thu Jun 05, 2014 2:53 pm
by Celauran
Logically, you want something like this, right?

Code: Select all

if ($total_pages <= 5) {
	just display everything
} else {
	if ($i > 3) {
		print << <
	}

	if ($i <= 3) {
		print first five
	} else if ($total_pages - $i <= 3) {
		print last five
	} else {
		print $i - 2 through $i + 2
	}

	if ($total_pages - $i <= 3) {
		print > >>
	}
}

Re: PHP MySQL pagination and excel export

Posted: Thu Jun 05, 2014 3:30 pm
by minghags
Celauran wrote:What can't you figure out? What have you tried?
I thought about excel export, but now I figured out with much much much of sweat :P About pagination I will try and report. Thanks.