kelatebaru

In this post we are going to learn about pagination using jQuery,MySQL and PHP.This is very simple tutorial.It has four files which are as below. (1) config.php file     - For the database connection details. (2) index.php file     - Main file that display the result to the user. (3) data.php file     - File having code to fetch the data from the table. (4) pagination.js file     - Javascript file for acting as a data controller Download Full Source Create Database table CREATE TABLE IF NOT EXISTS `users`(  `id` int(10) NOT NULL AUTO_INCREMENT,  `FirstName` varchar(200) NOT NULL,  `Middlename` varchar(200) NOT NULL,  `LastName` varchar(200) NOT NULL,  PRIMARY KEY (`id`) ) config.php file Change the values of hostname,username,password and database name <?php $mysql_hostname = "localhost";  $mysql_user = "root";  $mysql_password = "";  $mysql_database = "test";  $con = mysql_connect($mysql_hostname, $...

If you are running a large web application then you must have lots of data(unwanted data also).For example, spam emails,unused records of the users.All these spamy and junk data will cause problem of database overload and backups.Its performance will also affected.So what could be the solution to overcome this problem.The solution is topic of this post called “MySQL Event Scheduler”. From the release of MySQL 5.1.6 comes the inclusion of a scheduler. Perfect name "The scheduler" that schedules tasks within the DB. When you will need MySQL Events? (1) To delete junk records automatically on regular interval of time. E.g. delete every week. (2) Used to Call a stored procedure at specific time. (3) Automatically perform any scheduled SQL operation. Three basic steps are required to schedule a task. • Event Name • Event Interval • SQL Statement Create a Table – Cart CREATE TABLE cart( cartID INT AUTO_INCREMENT , userID INT, product_id INT, created TIMESTAMP DEFAULT CURRENT_TIMEST...

While surfing internet you may have seen many websites which displays the number of online users. In this post we are going to learn how to create the similar script using PHP and MySQL.But before reading  this  post further,if you haven’t read my previous post then please have a look at “ Visitors Counter Script ”.After reading  that post your basic concepts regarding this will gets more clear. This script is very simple and has only two steps which are as below. Step: 1 Create table : “online_users” CREATE TABLE `online_users` ( `session` char(100) NOT NULL default '', `time` int(11) NOT NULL default '0' ) TYPE=MyISAM; Step:2 Create file : online_users.php <?php session_start(); $session=session_id(); $time=time(); $time_check=$time-300; //We Have Set Time 5 Minutes $host="localhost"; // your Host name $username="root"; // your Mysql username $password=""; // your Mysql password $db_name="test"; // your Databasename $tbl_na...

If you are a developer then definitely you might have faced this. Many times you  need to import data from a CSV (comma separated value) file and insert it into your MySQL database. Say for Example consider a case when you have many records in a CSV file and you need to import them into your MySQL database then you can’t  insert each n every single record manually as it will take too much time. This case arises mostly when you want to import existing data in your website. In this tutorial I am going to  explain you how easily you can do that. SQL query to create csvdata table: CREATE TABLE IF NOT EXISTS `csvtbl`(  `ID` int(10) NOT NULL AUTO_INCREMENT,  `name` varchar(50) NOT NULL,  `city` varchar(50) NOT NULL,  PRIMARY KEY (`ID`) ) csvimport.php File Download File <?php  //database connection details $connect = mysql_connect('localhost','root','123456'); if (!$connect) {  die('Could not connect to MySQL: ' . mysql_error());  } //your ...

If you have noticed that many websites display their total numbers of visitors.In this tutorial I am going to explain you how to create a simple visitor counter using php. For this, 1) Create one table called “visitor_counter” in your database. 2) Create file named “counter.php”. Step1: Creating “visitor_counter” Table CREATE TABLE `visitor_counter` ( `counts` int(10) NOT NULL default '0' ) Step2: Creating “counter.php” File <?php  // Database Details $host="localhost"; $username="root"; $password=""; $db_name="test"; $tbl_name="visitor_counter"; // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect to server "); mysql_select_db("$db_name")or die("cannot select DB");  $sql="SELECT * FROM $tbl_name"; $result=mysql_query($sql); $rows=mysql_fetch_array($result); $counter=$rows['counts']; // se...

Hi all, Few days back I was working on a project having large database.Before applying new modifications in the project, backup of the current databse was necessary.when I was taking backup  it gave me error because it was too big, because it exceeded the timeout. For the solution of this error i googled and found one amazing solution the name of which is BigDump . What is BigDump? When you are about to replicate or move a database then importing large SQL dumps  can be difficult for those with only web access to the MySQL server. phpMyAdmin has certain shortcomings when importing files of more than a modest size. In such cases BigDump can be a helpful utility. How to use BigDump? 1.    Download the BigDump from here : BigDump 2.    Extract the bigdump.zip file and open the bigdump.php file in a text editor. 3.    Configure: Server, database name, username, password, and the file name. <?php // Database configuration  $db_se...

Lessons learned: shm/apc are 32-60 times faster than memcached or mysql shm/apc are 2 times faster than php file cache with apc php file cache with apc is 15-24 times faster than memcached or mysql mysql is 2 times faster than memcached when storing more than 400 bytes memcached is 2 times faster than mysql when storing less than 400 bytes php file cache with apc is 2-3 times faster than normal file cache php file cache without apc is 8 times slower than normal file cache Tests were made with PHP 5.3.10, MySQL 5.5.29, memcached 1.4.13, 64bit, 3.4GHz (QEMU): shm 0.031 0.020 0.021 0.021 0.026 0.028 0.032 0.042 0.084 0.155 0.290 0.629 0.110 Total: 1.489, Avg: 0.115 apc 0.025 0.025 0.025 0.026 0.031 0.036 0.043 0.060 0.106 0.171 0.328 0.756 0.097 Total: 1.728, Avg: 0.133 memcache 3.116 3.014 3.005 3.072 3.077 3.910 3.929 4.067 4.308 10.371 15.323 25.013 3.281 Total: 85.488, Avg: 6.576 memcache socket 1.736 1.756 1.981 1.780 1.809 1.907 1.941 1.983 2.225 9.368 14.071 24.897 1.979 Total: ...

Queries are gaining more and more complexity, data is getting bigger and bigger. Most optimizations in database technology are done in the database server. This is an approach to optimize queries on the client side. With this ORM, queries ... don't select more data than needed contain less joins when data is expected to be consistent can be written manually in pure SQL are not written in a new query language We need a good API, so ... it should be easy to learn method names must be short and intuitive the goal is to map datasets and relations to objects the API should offer method chaining special features like auto-increments should be included the code should be small, no getters and setters the database schema is created before writing PHP code relationships should be defined in the database, not in the code we get low latencies combined with low memory usage To make things easier, we make some restrictions: only UTF-8 only MySQL/MariaDB (mysqli) only PHP 5.4.0+ only buffer...

Lessons learned: SQLite performs inserts 8-14 times faster then InnoDB / MyISAM SQLite performs updates 4-8 times faster then InnoDB and as fast as MyISAM SQLite performs selects 2 times faster than InnoDB and 3 times slower than MyISAM SQLite requires 2.6 times less disk space than InnoDB and 1.7 times more than MyISAM Allowing null values or using synchronous=NORMAL makes inserts 5-10 percent faster in SQLite Using SQLite instead of MySQL can be a great alternative on certain architectures. Especially if you can partition the data into several SQLite databases (e.g. one database per user) and limit parallel transactions on one database. Replication, backup and restore can be done easily over the file system. The results: (MySQL 5.6.5 default config without binlog, SQLite 3.7.7, PHP 5.4.5, 2 x 1.4 GHz, disk 5400rpm) insert [s] sum [s] update [s] size [MB] JSON 1.84 1.30 2.92 2.96 CSV 1.97 2.25 3.7 2.57 SQLite (memory) 2.74 0.12 0.52 0.00 SQLite (memory, not null) 3.00 0...

Lessons learned: Prepared statements are 13 percent faster than normal statements with escaping Prepared statements are 8 percent faster than normal statements without escaping To get improvements, you need at least 10000 inserts for 1 statement Using insert...set is 0.5-1 percent faster than insert...values Here is the code: $db = new mysqli('127.0.0.1', 'root', '', 'test'); $db->query('create table if not exists prep (i1 int, i2 int, s1 varchar(255)) engine=myisam'); $db->query('truncate table prep'); $start = microtime(true); $stmt = $db->prepare('insert into prep (i1,i2,s1) values (?,?,?)'); $i=0; $j=0; $s=null; $stmt->bind_param('iis', $i, $j, $s); for ($i=0; $i<100000; $i++) { $j = $i*2; $s = 'hello world'.$i; $stmt->execute(); } echo 'prep values '.number_format(microtime(true)-$start, 2)."\n"; assert($db->query('select count(*) from prep')->fetch_ro...

Lessons learned: MySQLi is 3-4 times slower than MySQL when fetching less then 500 datasets MySQLi is 2-4 times faster than MySQL when fetching more than 500 datasets PDO is 2-5 times slower than MySQL/MySQLi Unbuffered queries are 15-40 percent faster than buffered queries in MySQLi Unbuffered queries are 10-25 percent faster than buffered queries in MySQL for less than 10000 datasets Unbuffered queries are 3-7 percent slower than buffered queries in MySQL for more than 10000 datasets Unbuffered queries are 0-5 percent faster than buffered queries in PDO Non thread safe versions of PHP on win32 are 50 percent faster than thread safe versions Here is the test script: $table = 'test1.test2'; benchmark($table, 100); benchmark($table, 500); benchmark($table, 1000); benchmark($table, 5000); benchmark($table, 10000); benchmark($table, 50000); benchmark($table, 100000); function benchmark($table, $size) { mysql_connect('127.0.0.1', 'root', ''); mysql_qu...