Category: Mysql

Create simple pagination using PHP and MySQLi

In your website page when we want to show data list then maximum time we need pagination for better understand. We can do that using php and mysqli. For this case just follow this instruction. First we need to create a table so first create database name “pagination” then run this sql in your phpmyadmin.


CREATE TABLE `user` (
  `userid` int(11) NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `username` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `user` (`userid`, `firstname`, `lastname`, `username`) VALUES
(1, 'First1', 'Last1', 'test1'),
(2, 'First2', 'Last2', 'test2'),
(3, 'First3', 'Last3', 'test3'),
(4, 'First4', 'Last4', 'test4'),
(5, 'First5', 'Last5', 'test5'),
(6, 'First6', 'Last6', 'test6'),
(7, 'First7', 'Last7', 'test7'),
(8, 'First8', 'Last8', 'test8'),
(9, 'First9', 'Last9', 'test9'),
(10, 'First10', 'Last10', 'test10'),
(11, 'First11', 'Last11', 'test11'),
(12, 'First12', 'Last12', 'test12'),
(13, 'First13', 'Last13', 'test13'),
(14, 'First14', 'Last14', 'test14');


ALTER TABLE `user`
  ADD PRIMARY KEY (`userid`);


ALTER TABLE `user`
  MODIFY `userid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;


I just add some demo data for list . Then create a database connection file name “db.php“. Add this code in db.php

<?php

$conn = mysqli_connect("localhost","root","","pagination");
if (!$conn) {
 die("Connection failed: " . mysqli_connect_error());
}
 
?>

then create main page index.php which page show content

<?php include('pagination.php'); ?>
<!DOCTYPE html>
<html>
<head>
	<link rel="stylesheet" href="http://techparkbd.com/subdomain/source_file/bootstrap.min.css" />
	<script src="http://techparkbd.com/subdomain/source_file/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
	<div style="height: 20px;"></div>
	<div class="row">
	<div class="col-lg-2">
	</div>
	<div class="col-lg-8">
	<table width="80%" class="table table-striped table-bordered table-hover">
		<thead>
			<th>UserID</th>
			<th>Firstname</th>
			<th>Lastname</th>
			<th>Username</th>
		</thead>
		<tbody>
		<?php
			while($crow = mysqli_fetch_array($nquery)){
			?>
				<tr>
					<td><?php echo $crow['userid']; ?></td>
					<td><?php echo $crow['firstname']; ?></td>
					<td><?php echo $crow['lastname']; ?></td>
					<td><?php echo $crow['username']; ?></td>
				</tr>
			<?php
			}		
		?>
		</tbody>
	</table>
	<div id="pagination_controls"><?php echo $paginationCtrls; ?></div>
	</div>
	<div class="col-lg-2">
	</div>
	</div>
</div>
</body>
</html>

then this is pagination page which manage pagination list .

<?php

	include("db.php");
	
	$query=mysqli_query($conn,"select count(userid) from `user`");
	$row = mysqli_fetch_row($query);

	$rows = $row[0];
	
	$page_rows = 5; // change how many row show every page

	$last = ceil($rows/$page_rows);

	if($last < 1){
		$last = 1;
	}

	$pagenum = 1;

	if(isset($_GET['pn'])){
		$pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);
	}

	if ($pagenum < 1) { 
		$pagenum = 1; 
	} 
	else if ($pagenum > $last) { 
		$pagenum = $last; 
	}

	$limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;
	
	$nquery=mysqli_query($conn,"select * from `user` $limit");

	$paginationCtrls = '';

	if($last != 1){
		
	if ($pagenum > 1) {
        $previous = $pagenum - 1;
		$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'" class="btn btn-default">Pre</a> &nbsp; &nbsp; ';
		
		for($i = $pagenum-4; $i < $pagenum; $i++){
			if($i > 0){
		        $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'" class="btn btn-default">'.$i.'</a> &nbsp; ';
			}
	    }
    }
	
	$paginationCtrls .= ''.$pagenum.' &nbsp; ';
	
	for($i = $pagenum+1; $i <= $last; $i++){
		$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'" class="btn btn-default">'.$i.'</a> &nbsp; ';
		if($i >= $pagenum+4){
			break;
		}
	}

    if ($pagenum != $last) {
        $next = $pagenum + 1;
        $paginationCtrls .= ' &nbsp; &nbsp; <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'" class="btn btn-default">Next</a> ';
    }
	}

?>

then browse your index.php page then you will show list using pagination. This is simple demo content

If you face any problem then please inform me. I will try to help you.

Credit : https://www.sourcecodester.com/

Comment

Dynamic Drag and Drop using PHP & mysql and jQuery

This is very common feature that items list need to drag and drop and save it. Drag’n drop generally looks hard-to-apply but it is definitely not by using JavaScript frameworks. In this article i will show how the drag’n drop & saving the new positions to the database Will be working”. This is very simple process you can apply it. This is simple code which you can check

First create a simple database Suppose “drag” . Here is the code for create a table then run this code in mysql

CREATE TABLE `records` (
 `recordID` int(11) NOT NULL auto_increment,
 `recordText` varchar(255) default NULL,
 `recordListingID` int(11) default NULL,
 PRIMARY KEY (`recordID`)
 );
 
INSERT INTO `records` VALUES ('1', 'Once dropped, an Ajax query is activated', '3');
 INSERT INTO `records` VALUES ('2', 'Dragging changes the opacity of the item', '2');
 INSERT INTO `records` VALUES ('3', 'Returned array can be found at the right', '1');
 INSERT INTO `records` VALUES ('4', 'It is very very easy', '4');

The most important column in the database is recordListingID which shows us the order of the records.

Then create a php page which ready mysql connect .

<?php
/*
* Author : Ali Aboussebaba
* Email : bewebdeveloper@gmail.com
* Website : http://www.bewebdeveloper.com
* Subject : Dynamic Drag and Drop with jQuery and PHP
*/

// PDO connect *********
function connect() {
	$host = 'localhost';
	$db_name = 'drag';
	$db_user = 'root';
	$db_password = '';
    return new PDO('mysql:host='.$host.';dbname='.$db_name, $db_user, $db_password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
}
?>

Then create index.php which is the main page to view list

<!DOCTYPE>
<html>
<?php
include('config.php');
$pdo = connect();
$sql = 'SELECT * FROM records ORDER BY recordListingID ASC';
$query = $pdo->prepare($sql);
$query->execute();
$list = $query->fetchAll();

?>

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>jQuery Dynamic Drag'n Drop</title>
    <script type="text/javascript" src="http://techparkbd.com/subdomain/source_file/jquery-1.10.2.js"></script>
    <script type="text/javascript" src="http://techparkbd.com/subdomain/source_file/jquery-ui-1.10.4.custom.min.js"></script>

    <style>
        body {
            font-family: Arial, Helvetica, sans-serif;
            font-size: 16px;
            margin-top: 10px;
        }

        ul {
            margin: 0;
        }

        #contentWrap {
            width: 700px;
            margin: 0 auto;
            height: auto;
            overflow: hidden;
        }

        #contentTop {
            width: 600px;
            padding: 10px;
            margin-left: 30px;
        }

        #contentLeft {
            float: left;
            width: 400px;
        }

        #contentLeft li {
            list-style: none;
            margin: 0 0 4px 0;
            padding: 10px;
            background-color:#00CCCC;
            border: #CCCCCC solid 1px;
            color:#fff;
        }

        #contentRight {
            float: right;
            width: 260px;
            padding:10px;
            background-color:#336600;
            color:#FFFFFF;
        }

    </style>
    <script type="text/javascript">
        $(document).ready(function(){

            $(function() {
                $("#contentLeft ul").sortable({ opacity: 0.6, cursor: 'move', update: function() {
                    var order = $(this).sortable("serialize");
                    $.ajax({
                        url: 'http://localhost/updatelist.php?action=updateRecordsListings',
                        type: 'POST',
                        data: order,
                        success: function (result) {
                            $("#contentRight").html(result);
                        }
                    });
                }
                });
            });

        });
    </script>

</head>
<body>

<div id="contentWrap">
    <div id="contentLeft">
        <ul>
            <?php
            foreach ($list as $rs) {
                ?>
                <li id="recordsArray_<?=$rs['recordID']?>"><?=$rs['recordText']?></li>
                <?php
            }
            ?>
        </ul>
    </div>

    <div id="contentRight">
        <p>New List will be displayed here.</p>
        <p>&nbsp; </p>
    </div>

</div>

</body>
</html>


After posting the array of “new order of the items” to updatelist.php, we must run a query to update our database that will reflect the last positions of every item:

<?php
include('config.php');
$pdo = connect();

$action = $_GET['action'];
$updateRecordsArray = $_POST['recordsArray'];

if ($action == "updateRecordsListings"){

    $listingCounter = 1;
    foreach ($updateRecordsArray as $recordIDValue) {

        $query = "UPDATE records SET recordListingID = " . $listingCounter . " WHERE recordID = " . $recordIDValue;
        $query = $pdo->prepare($query);
        $query->execute();
        $listingCounter = $listingCounter + 1;
    }

    echo '<pre>';
    print_r($updateRecordsArray);
    echo '</pre>';
    echo 'If you refresh the page, you will see that records will stay just as you modified.';
}
?>

all done so run index.php and drag list , it will automatically save drag list and after refresh it will show new list

This is example output


this is output

Comment

How to get MySQL column names?

Sometime we need to MYSQL table column name for many requirement, In this case we get columns name using simple MYSQL query. This is query and in this query i select column_name,column_type and table_name for more details . I use order by column_type so i can see it easily. You can change which you want

SELECT `COLUMN_NAME`,COLUMN_TYPE,TABLE_NAME 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' order by DATA_TYPE;

If you want to check only double type filed then you can do it easily

SELECT `COLUMN_NAME`,COLUMN_TYPE,TABLE_NAME,DATA_TYPE 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' AND DATA_TYPE like '%bigint%'  order by DATA_TYPE;

if you want to check which field allow null type etc then you can use this

SELECT `COLUMN_NAME`,COLUMN_TYPE,TABLE_NAME,IS_NULLABLE,DATA_TYPE 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' and DATA_TYPE like '%bigint%' and IS_NULLABLE ='NO' order by COLUMN_TYPE;

Change your field name as your table field, if you face any problem then please inform me.

Comment

How to import large files in windows using MySQL’s source command?

There many way you can do that . i will give some options here to import large file using command.

Option 1. You can do this using single cmd. In this cmd D is my xampp or wampp install folder so i use this where mysql.exe install and second option database name and last is sql file so replace it as your then run this. This is for xampp .

 
D:\xampp\mysql\bin\mysql.exe -u root -p databse_name < D:\yoursqlfile.sql

This is for wampp

 
D:\wamp64\bin\mysql\mysql5.7.14\bin\mysql.exe -u root -p databse_name< D:\yoursqlfile.sql

just change your folder and mysql version etc

Option 2. Suppose your current path is which is showing command prompt

 
C:\Users\shafiq;

then change directory using cd.. then goto your mysql directory where your xampp installed. Then cd.. for change directory. then go to bin folder.

 
C:\xampp\mysql\bin;
C:\xampp\mysql\bin\mysql -u {username} -p {database password}.

then please enter when you see enter password in command prompt.
choose database using

 
mysql->use test (where database name test)

then put in source sql in bin folder.

then last command will be

 
mysql-> source test.sql (where test.sql is file name which need to import)

then press enter

This is full command

 
C:\Users\shafiq;
C:\xampp\mysql\bin
C:\xampp\mysql\bin\mysql -u {username} -p {database password}
mysql-> use test
mysql->source test.sql
Comment