Category: My SQL

Pagination in PHP, MYSQL serverside API

Pagination is a useful concept when there is a requirement of retreiving large chunks of data from server. Pagination reduces the load on server and also is user friendly as the end user sees less data in a go.

So here I am using PHP to retreive rows from MYSQL using pagination.

#retreive start and limit values
if(isset($_POST[“start”])) {
$start = $_POST[“start”]-1;
} else {
$start = 0;
}

##number of rows to be retreived from starting point, can be 10,20,30…
if(isset($_POST[“limit”])) {
$limit = $_POST[“limit”] ;
} else {
$limit = 10 ; //get 10 as default from starting point
}

#set header
header(‘Content-Type: application/json’);

#connection to database
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

#form query
$sql = “Select * from mytable LIMIT $start, $limit “; ##this query retreives rows from mytable starting from start point and upto $limit rows

#execute query
$retval = mysqli_query( $conn, $sql );

#fetch rows if query executed succesfully else return error
if(! $retval ) {
$data = array(“status”=>”failure”,”message”=>”Log fetch error!!”);
echo json_encode( $data );
mysqli_close($conn);
die(‘Could not fetch logs: ‘ . mysqli_error());
}
else {
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC)) {
$data[] = $row;
}

##send response to client
$data = array(‘status’=>’success’,’message’=>’Log fetched.’,’records’=>$data);

#close connection
mysqli_close($conn);
echo json_encode( $data );
}

This script will fetch rows from database based on start and limit variables. If these variables are not passed during client request it sets to default of 0 to 10 rows.

Happy Coding.

Saving unicode or utf8 data using PHP-MYSQL

Saving data in MYSQL is almost common in every website. When it comes to unicode date there is a bit of overhead that needs to be taken care of. I am listing those settings step by step.

1. Set table’s collation to “utf8_general_ci”

 ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

2. Set the column’s collation to “utf8_general_ci”

 ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

3. In PHP use the below code while the data is being inserted into the table.

 mysqli_query($conn,"SET names 'utf8'");

Most Effective Way To Reset Your Lost Password Or Forgotten Password Of MySQL.

You have to reset the password! steps for mac osx(tested and working) and Ubuntu

Stop MySQL

$ sudo /usr/local/mysql/support-files/mysql.server stop

Start mysql in safe mode:

$ sudo mysqld_safe –skip-grant-tables

(above line is one whole command)

This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:

$ mysql -u root
Now:
mysql> UPDATE mysql.user SET Password=PASSWORD(‘password’) WHERE User=‘root’;

$ sudo /usr/local/mysql/support-files/mysql.server start


your new password is ‘password’.