Freelancer's Playground! New knowledges every now and then

Mimic Oracle’s LOV Feature for Web Application Part 2: Add Database Support

On my previous tutorial, you have seen how to create a Lookup Grid. That's just basic, without database support.
In this tutorial, I'll enhance the script with database and search support. What you'll need first is obviously a database server. If you don't have the tutorial file, please download it from my previous tutorial. Next think to do is, create a file which contain database information. Let's call it db_conn.php.

<?php
$conn = mysql_connect('localhost', 'db_username', 'db_password');
mysql_select_db('database_name');
?>

With database information obtained, now we'll move to lov.php file. Open it and include db_conn.php.

include('db_conn.php');

Next, delete all <row></row> xml data. Add this code right after header("Content-Type: text/xml");.

$x = isset($_GET['query']) ? $_GET['query'] : '';
$addSQL = "";
if(!empty($x)){
	$addSQL = "WHERE name LIKE '%".mysql_escape_string($x)."%'";
}
$sql = "SELECT * FROM friends $addSQL";
$rs = mysql_query($sql, $conn);

On place where <row></row> was, add this code:

<?php
	$select = 'selected="1"';
	while(false !== ($r = mysql_fetch_array($rs))){
		echo '
	<row id="'.$r['id'].'" '.$select.'>
      <cell>'.$r['id'].'</cell>
      <cell>'.$r['name'].'</cell>
      <cell>'.$r['city'].'</cell>
      <cell>'.$r['phone'].'</cell>
      <cell>'.$r['email'].'</cell>
    </row>
		';
		$select = "";
	}
?>

Now the XML generator part is ready, now we'll head to HTML part.
Please add this code right before <div id="gridbox" height="100%"></div>

<div id="searchbox">
	Search&nbsp;<input type="text" id="searchText" value="">&nbsp;
	<input type="button" onclick="doLimit(document.getElementById('searchText').value)" value="Search">
</div>

And this javascript function inside HTML HEAD tag.

<script language="javascript" type="text/javascript">
	var mygrid = null;
	function doLimit(txt){
		mygrid.clearAll();
		mygrid.loadXML("lov.php?reqType=getXML&query="+txt);
	}
</script>

That's it, save your lov.php file and run it to test. You can also try in online here
and download the example file here. Any question you might have, you can write in here in comment form below.
I'll try to answer when I have time.