Location: PHPKode > scripts > Jack's MySQL class > jacks-mysql-class/example3.php
<?php

// requre mySQL class
require_once('sql.class.php');

// set database values 
$_DBHOST = 'localhost';
$_DBUSER = 'root';
$_DBNAME = 'test';
$_DBPASS = '';

// 'start' the class

// 1st way: with out values 
// it will use $_DBHOST, $_DBUSER, $_DBNAME, $_DBPASS as defaults
$s = new sql();

// 1. Set default Method

$s->setFetchMethod('object'); // will set default fetch method ('array', 'assoc', 'row', 'object')

// 2. query1()

/* query1 method is replacement for
	$s->query($qry, $index);
	$row = $s->getrow($index);
	$s->free_result($index);
 */
$row  = $s->query1("SELECT * TABLE table_name WHERE id='1' ");	// select first recod and then clear = result set

// 3. free_result() and nextindex

// free_result() clear query result infos, its automatically called on query1(), and getrows()
$s->free_result($index);

// next index gives you the next avalable index level for the query
$s->query('SELECT id FROM table_name');

while($row = $s->getrow())
{
	$index = $s->nextindex();	// it will be 1
	
	$s->query("SELECT * FROM table_name2 WHERE parent_id='{$row->id}'", $index);
	
	while($row2 = $s->getrow($index))
	{
		print '<pre>';
		print_r($row2);
		print '</pre>';
	}
	
	// @note:	when $row2 = $s->getrow($index) finish reading rows it will call free_result() and free index number,
	//			so when you call $s->nextindex() it will be again 1
}

// 4. escape methods

$var1 = $s->escape("fssd'''';'''''fsdfs");	// will cleer it
$var2 = $s->escape_full("__ AND do '");		// will escape sql symbols, good when use string for search queries
$var3 = $s->quote('fsdsdfsd');				// will return "fsdsdfsd"

// 5. utility methods

// where()
$where_cond1 = $s->where();										// ""
$where_cond2 = $s->where(1);									// WHERE id='1'
$where_cond3 = $s->where("field='equal'");						// WHERE field='equal'
$where_cond4 = $s->where(array('id=2', 'name="test"'));			// WHERE id=1 AND name="test"
$where_cond5 = $s->where(array('id' => 2, 'name' => 'test'));	// WHERE id="1" AND name="test"

// @note: I use where() on insert, update, delete, select, so conditons can be given in that format

// update on update() function
// I remove avalues argument and improved values(2nd argument) so now it not have to be hash table, it could be also and string, and normal array
$s->update('table_name', array('name="test"', 'date=now()'));	//	UPDATE table_name SET name="test", date=now()
$s->update('table_name', 'name="test", date=now()');			//	UPDATE table_name SET name="test", date=now()

// select() function
$s->select('table_name', 'field1, field2, field3', 'id=2', 2);	// SELECT field1, field2, field3 FROM table_name WHERE id=2 LIMIT 2

// @note: to use selected result use this:
while ($row = $s->getrow())
{
	print '<pre>';
	print_r($row2);
	print '</pre>';
}

// you could select the first result by:
$row = $s->select('table_name', 'field1, field2, field3', 'id=2', 1);	//	if last argument $limit is 1 it will returt firt result

// if you need order condition, you will just have to hack the function, with something link this
$s->select('table1', '*', $s->where('name="test" AND updated < now()') . ' ORDER BY name');	// SELECT * FROM table1 WHERE name="test" AND updated < now() ORDER BY name

?>
Return current item: Jack's MySQL class