Easy, Simple Paging Using PHP
Easy & Simple Paging using PHP. Here is the result: (DB has 8records, Rows per page is set to 5, and we're on page 2)
Paging usually takes a while but here we will do it quickly and cleanly.
Your page will be named page.php. And when we want to switch pages we will make it page.php?pg=2.
Im sure you've seen this before. Anyways lets dive in.
I use shorthand to clean stuff up, but basically shorthand is
$returnvalue=(condition ? "true" : "false");
Example:
$is_loggedin=(isset($_SESSION["loggedin"]) ? 1 : 0);
After this statement, if the session variable is set, $is_loggedin will equal 1, else it will equal 0.
At the top of your PHP page type the following:
<?php /*paging*/ Line 1: $pg=(isset($_GET["pg"]) && is_numeric($_GET["pg"]) && $_GET["pg"]>0 ? $_GET["pg"] : 1); Line 2: $rowsperpage=10; Line 3: $offset=($pg>1 ? ($pg-1)*$rowsperpage : 0 ); Line 4: $limitstr=" LIMIT $offset, $rowsperpage"; ?>
Explanation:
First of all Limit is : LIMIT $startrowid, $number of entries to return
So LIMIT 55, 5 means start at row 55 and return 5 rows (55, 56, 57, 58, 59)
Line 1: If in the query string the pg is set e.g page.php?pg=4, and it is numeric, and greater than 0 we will assign the value of $_GET["pg"] to our $pg variable. If it is not set, or it is not numeric, or zero or less, we set the $pg variable to 1.
Line 2: We set our rows per page
Line 3: If the page is greater than 1, we set the offset to ($pg-1)*rowsperpage. If the page is 1 or less, we set offset to 0.
Line 4: We create the limit string for the sql statement. This will output something like LIMIT x, y.
(rowsperpage set to 10)
In this case page.php?pg=1
$pg = 1;
$offset = 0;
$rowsperpage=10;
$limitstr="LIMIT 0, 10"; //this string will pull 10 rows starting from row 0 (the first row) (rows 0-9)
In this case page.php?pg=3
$pg=3;
$offset= (3-1) * 10; //offset calculates to 20
$limitstr="LIMIT 20, 10"; //this string will pull 10 rows starting from and including row 20 (rows 20-29)
Ok. Now that we got that out of the way.
We will use a simple query, then we will attach our new limit string (looking at case 2 above)
$sql="SELECT * FROM MEMBERS $limitstr"; //sql = "SELECT * FROM MEMBERS LIMIT 20,10";
$res=mysql_query($sql);
while($row=mysql_fetch_array($res)){
//processing if you need to
}
We also need to know the total count for this table
$sql="SELECT COUNT(memberid) FROM MEMBERS";
$res=mysql_query($sql);
$row=mysql_fetch_array($res);
$total = $row[0];
Now we know the total count.
So we need to calculate the number of pages. That is done by this formula:
($total/$rowsperpage);
Now in the case of 26 members and 5 rows per page. Using this formula 26/5 = 5.2pages, so we need to round up!
$maxpage=ceil($total/$rowsperpage); //max page is now 6.
Ok, we got everything set up 
Just need to print out our paging!
Wherever you want your paging setup add this:
<?php
print '<div class="paging"><ul>';
for($i=1; $i<=$maxpage; $i++){
if($pg==$i)
print '<li><span>'.$i.'</span></li>';
else
print '<li><a href="?pg='.$i.'">'.$i.'</a></li>';
}
print "</ul></div>";
?>
Then we can do some nice styling (Mess around with margins to setup spacing)
CSS
div.paging {text-align: center; margin-top: 5px;}
div.paging span{font-weight: bold;}
div.paging ul{overflow: hidden;}
div.paging ul li{width: 10px; float: left; margin: 0px 10px 5px 0px;}
This will page across until it hits the edge of the div, then it will move to the next line.
ENJOY and comment !
USB Backup Utility with Batches
After my USB got FRIED behind the damn photocopier in the library a couple years ago, I had to re-do my whole Java Assignment AGAIN. I decided to make a little backup utility that would make it a little easier to back up my usb. It will only work if the pen drive is a fixed letter, but its always E: on my computer so thats fine by me. It consists on 2 files placed on the usb, a backup.bat that does the backing up and an autorun.inf which puts the Backup option into the autorun screen. When i plug my USB pen drive, the autorun gives me the option to backup, making an exact copy of my usb at c:/usb
Open notepad and paste this text in.
@echo off
echo USB BACKUP UTILITY V1.0
echo CREATED BY SCOTT
echo ----------------------------------------------------------
echo Starting Backup:
cd \
c:
if exist c:\usb goto exists
mkdir "USB"
goto copy
:exists
rmdir "USB" /s /q
mkdir "USB"
:copy
echo Date>C:\usb\backup.txt
date /t>>C:\usb\backup.txt
echo.>>C:\usb\backup.txt
echo Time>>C:\usb\backup.txt
time /t>>C:\usb\backup.txt
echo ------------------------------------------->>C:\usb\backup.txt
cd \
xcopy E:\* "C:\USB" /y /e /r /v>>C:\usb\backup.txt
echo Backup Complete!
echo.
echo Backup of entire USB at C:/USB
echo Log file placed at C:/USB/backup.txt
echo ----------------------------------------------------------
Pause
In the line xcopy E:\* "C:\USB" /y /e /r /v>>C:\usb\backup.txt, replace E: in this line with your actual drive letter.
Save this file as backup.bat making sure that "All Files" is selected from the files drop down box.
Next repeat the same steps, saving this text as autorun.inf
[autorun]
action=Backup
open=backup.bat
label=MyUsbName
includeRuntimeComponents=True
Save both these files on the root of your USB.
The next time you plug your usb in, you will see the autorun option "backup".
By clicking this and hitting ok, a full usb backup will be performed and put in C:/USB
Installing and Configuring MySql/Connecting with C#
Recently, I was asked to do some C# development with MySQL as the backend.
Having C# with SQL Server 2005 development under my belt setting up a connection to a MySQL Server was pretty easy, however, I will walk you through the setup.
Two packages you will need are the MySQL Essentials Installer(this installs the server)
and the MySQL Connecter/.NET Installer(this installs the bridge between MySQL and .NET)
The third package, the GUI tools, is optional, but it is a lot easier to work with. (Think of it as a Management Studio Express, for those of you who have used SQL 2005)
MySQL Essentials Package: http://dev.mysql.com/downloads/mysql/5.1.html#downloads
MySqlConnector/NET: http://www.mysql.com/products/connector/net/
MySQL Gui Tools: http://dev.mysql.com/downloads/gui-tools/5.0.html
Run the Essentials Package first, this package actually contains the server instance.
Follow the prompts, they are pretty much straight forward.
When you receive the prompt about your root password, pick a password.
Next install the MySQL Connector.
Follow the prompts, they are pretty much straight forward as well, and should install pretty quickly.
From here, you can use the command line client located in Start>Program Files>MySql Version>MySql Command Line Client, however, I choose to use the GUI tools instead.
If you have not already, launch the GUI tools installer.
Once this is done, go to Start>Program Files>MySql Version#>MySQL query browser
Click the ... button next to Stored Connection.
Click New Connection.
Fill out the properties
Connection: Name the connection
Username: root
Password: password used during setup
Hostname: localhost
Port: default is 3306
Schema: specify the default database, you can leave this blank.
Click apply and close.
In the connection screen, from the dropdown, pick the connection we just created.
Type your password.
If given a warning about a default schema, type test or default in the Default Schema field, this will create
a database named test or default. You can always delete this dummy database afterwards.
To create tables right click your database in the Schemata and click Create New Table.
Most of the GUI is intuitive and should be picked up quite easy.
Connecting to MySQL using C#
Start a C# Application project (I'm using a Console App in my example)
Right click your project and click Add Reference.
Navigate to the Connector DLL.
By default this dll is located at C:/Program Files/MySql/MySql Connector .NET Version#/Binaries/.NET 2.0/MySql.Data.dll
Once this is added add the line:
using MySql.Data.MySqlClient;
using System.Data; //for the ConnectionState enum
to the top of your code.
Add this code to your main/load method:
//sets up the connection string
string connString = "Server = localhost; Database = databaseName; Uid = root; Pwd = pass;";
//creates a new MySqlConnection object
MySqlConnection conn = new MySqlConnection(connString);
try{
//try to open the connection, catch any exceptions
conn.Open();
//if the connection succeeds, print a msg
Console.WriteLine("Connection Succeeded");
}catch(Exception ex){
//print that the connection failed, and the associated Message
Console.WriteLine("Connection Failed: "+ex.Message);
}finally{
//if the connection is currently open, close it
if(conn.State==ConnectionState.Open)
conn.Close();
}
Troubleshooting:
- If you are having trouble connecting, check your username and password.
- Double check the connection string.
- If you are sure the above is correct, make sure the MySql server instance is running. You can do this by
- going to Start>Run. Type services.msc and hit Ok. Scroll down the list to MySql, it should say started. If it does not, right click and hit Start.
- Try to connect with the MySql GUI tools.
Just about everything you can do with SQL Server you can do with MySql using the familiarly named classes:
MySqlConnection, MySqlCommand, MySqlDataReader
Hope this gets you started on using MySql and C#.
Any questions or comments, feel free to ask.