Location: PHPKode > scripts > Jack's MySQL class > jacks-mysql-class/example.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();

// 2nd way: set host, user, pass, name
$s = new sql($_DBHOST, $_DBUSER, $_DBPASS, $_DBNAME);

// make query
// note: '1' is the index of the query, if we make more than one query, 0 is default value
$s->query('SELECT id1 FROM tbl1 ', 1);

// get results
while ($row = $s->getrow(1))
{
	// make second query with the result from the first one
	$s->query("SELECT id2 FROM tbl2 WHERE id1='{$row->id1}' ");
	
	// get result
	$res = $s->getrow();
	
	// get 5th row from the result
	$res = $s->getrow(0, 5);
	
	// get result in array
	$res = $s->getrow(0, false, 'array');

	// get the number of affected rows
	$aff = $s->getnumaff();
	
	// get get the number of selected fields
	$flds = $s->getnumflds();
	
	// get the number of selected rows 
	$rows =$s->getnumrows();
	
	// free result
	$s->free_result();
}

// make insert query

// table to insert
$table = 'some_table';

// table values to insert
$values = array(
				'db_field_1_name'	=> 'db_field_1_value',
				'db_field_2_name'	=> 'db_field_2_value',
				'db_field_3_name'	=> 'db_field_3_value',
				'db_field_4_name'	=> 'db_field_4_value',
				'db_field_5_name'	=> 'db_field_5_value'
				);
				
// aditional table value (don't add sleshes to the query)
$avalues = array(
				'date'	=> 'date()',
				'sum'	=> 'db_field_1_name + db_field_2_name'
				);
				
// insert table
$s->insert($table, $values, $avalues);

// get last added primary key
$s->getlastid();

// table to update
$table = 'some_table';

// update conditionals
$cond = "id='10' ";

// table values to update
$values = array(
				'db_field_1_name'	=> 'db_field_1_value',
				'db_field_2_name'	=> 'db_field_2_value',
				'db_field_3_name'	=> 'db_field_3_value',
				'db_field_4_name'	=> 'db_field_4_value',
				'db_field_5_name'	=> 'db_field_5_value'
				);
				
// aditional table value (use sql codes)
$avalues = array(
				'lastupd'	=> 'date()',
				'sum'		=> 'db_field_1_name + db_field_2_name'
				);

// update table
$s->update($table, $values, $cond, $avalues);

// delete table record(s)

// 1st way delete record(s) from one table
$s->delete('table_name', "id='something' ");	// delete record with id equal to 'something'
$s->delete('table_name');						// delete all records

// 2nd way delete record(s) from more than one tables
$tables = array('table_name1', 'table_name2', 'table_name3'); // set tables to array

$s->delete($tables, "id='5' ");			// delete records from all $tables with id equal to '5'
$s->delete($tables);					// delete all records from all tables

// set record function

// values to set
$values = array(
				'db_field_1_name'	=> 'db_field_1_value',
				'db_field_2_name'	=> 'db_field_2_value',
				'db_field_3_name'	=> 'db_field_3_value',
				'db_field_4_name'	=> 'db_field_4_value',
				'db_field_5_name'	=> 'db_field_5_value'
				);

// now this will update record with db_id='10' or will add new record if there isn't record with db_id = 100
$s->set('sometable', $values, 'db_id', 10); 

// we can give multiple conditions too
$cond = array(
			'db_id'		=> 10,
			'db_cid'	=> 10,
			'db_fid'	=> 12
			);

$s->set('sometable', $values, $cond);

// we can use set function for delete to
$s->set('sometable', 0, 'db_id', 10); 	// will delete record(s) with db_id = 10
$s->set('sometable', 0, $cond);			// will delete record(s) with given conditions

// close connection
$s->disconnect();

?>
Return current item: Jack's MySQL class