Pagination with Jquery, PHP , Ajax and MySQL.

Friends, In my current employment i need to develop huge database driven web application. Which normally deals with millions  of records. I use indexing and cache techniques. Along with that i use jquery to make it handy for end use.

   

Database
MySQL messages table contains two columns msg_id and message

CREATE TABLE messages
(
msg_id INT PRIMARY KEY AUTO_INCREMENT,
message VARCHAR(150)
);

JavaScript Code
This script works like a data controller.

<script type="text/javascript" src="http://ajax.googleapis.com/
ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function()
{function loading_show()
{
$('#loading').html("<img src='images/loading.gif'/>").fadeIn('fast');
}function loading_hide()
{
$('#loading').fadeOut();
}

function loadData(page)
{
loading_show();
$.ajax
({
type: "POST",
url: "load_data.php",
data: "page="+page,
success: function(msg)
{
$("#container").ajaxComplete(function(event, request, settings)
{
loading_hide();
$("#container").html(msg);
});
}
});
}
loadData(1); // For first time page load default results
$('#container .pagination li.active').live('click',function(){
var page = $(this).attr('p');
loadData(page);
});
});

</script>

load_data.php
Contains PHP coding. Displaying data from the messages table.

<?php
if($_POST['page'])
{
$page = $_POST['page'];
$cur_page = $page;
$page -= 1;
$per_page = 15; // Per page records
$previous_btn = true;
$next_btn = true;
$first_btn = true;
$last_btn = true;
$start = $page * $per_page;
include"db.php";
$query_pag_data = "SELECT msg_id,
message from messages LIMIT $start, $per_page";
$result_pag_data = mysql_query($query_pag_data) 
or die('MySql Error' . mysql_error());
$msg = "";
while ($row = mysql_fetch_array($result_pag_data))
{
$htmlmsg=htmlentities($row['message']); //HTML entries filter
$msg .= "<li><b>" . $row['msg_id'] . "</b> " . $htmlmsg . "</li>";
}
$msg = "<div class='data'><ul>" . $msg . "</ul></div>"; // Content for Data
/* -----Total count--- */
$query_pag_num = "SELECT COUNT(*) AS count FROM messages"; // Total records
$result_pag_num = mysql_query($query_pag_num);
$row = mysql_fetch_array($result_pag_num);
$count = $row['count'];
$no_of_paginations = ceil($count / $per_page);
/* -----Calculating the starting and endign values for the loop----- *///Some Code. Available in download script }
?>

configuring php to connect with oracle database using XAMPP server manager

[caption id="" align="alignright" width="190" caption="Image via CrunchBase"]S[/caption]

Today i was searching for "how to configure php to connect with oracle ".  I tried different search words and searched the whole internet. Finally i was successful.

I use the package XAMM for windows to run PHP and MySql.

Step-A

Check whether you have oracle client already installed

1. In your XAMPP Start Page, go to phpinfo, look for string oci8. If string found it indicate that connection to oracle is available and simply execute Step-3.           Otherwise to activate connection do the following steps:


2. Open the currently used php.ini file by looking at the phpinfo, from the XAMPP folder.


3. Find string ;extension=php_oci8.dll. Remove the semicolon (;) from the begining of the string to activate the oracle extension.


4. Save the php.ini file.


5. Download the “Instant Client Package – Basic” for Windows from the OTN Instant Client page. Unzip it to "C:\xampp\php\ext" folder


6. Edit the PATH environment setting and add C:\xampp\php\ext before any other Oracle directories. For example, on Windows XP, follow Start -> Control Panel -> System -> Advanced -> Environment Variables and edit PATH in the System variables list.


7. Set desired Oracle globalization language environment variables such as NLS_LANG. If nothing is set, a default local environment will be assumed. See An Overview on Globalizing Oracle PHP Applications for more details.


8. Unset Oracle variables such as ORACLE_HOME and ORACLE_SID, which are unnecessary with Instant Client (if they are set previously).


9. Restart XAMPP (or Start if its not already started).


10. To make sure that connection to oracle database has successfully activated, go to phpinfo. Find string: oci8. If found, then XAMPP can now communicate with Oracle Database.

Step-B

Now connect to your desired database and execute your query

<?php

define('DB_SERVER', 'yourservername');
define('DB_USERNAME', 'yourUserName');
define('DB_PASSWORD', 'yourPassword');
define('DB_DATABASE', 'yourDatabaseName');
$conn= oci_connect(DB_USERNAME, DB_PASSWORD, DB_SERVER)
or die(oci_error());
echo "success...";
//$database = oci_select_db(DB_DATABASE) or die(oci_error());
$stid = oci_parse($conn, 'SELECT * FROM employees'); oci_execute($stid); echo "<table border='1'>\n"; while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {     echo "<tr>\n";     foreach ($row as $item) {         echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";     }     echo "</tr>\n"; } echo "</table>\n";
?>

For further help you can always refer the PHP manual.

A simple PHP database connection script to start-up

<?php
$host = "localhost"; //database location
$user = "s"; //database username
$pass = "s"; //database password
$db_name = "sw"; //database name

//database connection
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db_name);

$sqlstr = mysql_query("SELECT * FROM a");
if (mysql_numrows($sqlstr) != 0) {
while ($row = mysql_fetch_array($sqlstr)) {
?>
<p><?= $row['name'] ?></p>
<p><?= $row['userid'] ?></p>
<p><?= $row['id'] ?></p>
<?php
}
}
?>

SQL Server 2005 login error

SQL Server 2005: While login Error 18452 (not associated with a trusted sql server connection)

I found the following simple solution

Go to Start > Programs > Microsoft SQL Server 2005 > 
SQL Server Management Studio
Right-click the Server name, 
select Properties > Security
Under Server Authentication, 
select SQL Server and Windows Authentication Mode
The server must be stopped and re-started before this will take effect..."

It has solved my problem, so I hope it could be helpful for you too.