Posted by Tom on 07/01/06 00:34

I'm trying to adapt a function I'd written to insert arrays in MySQL to
the ADOdb_lite class. The best way to do this would probably be to
extend the ADOdb_lite class, but there doesn't seem to be a nice way to
do that. So what I've done is created two wrapper functions:

1. wrapper_ado_cx($db_name='_default', $db_user='_default',
$db_pass='_default', $db_server='_default', $db_type='mysql')
2. wrapper_ado_insert_array($ARRAY, $table, $check_cols=1,
$db_name='_default')

The first creates the ADO object and manages the database connection
using the ADOdb_lite class. The second inserts an array into a table by
preparing a statement that matches the array keys to the table columns.

I'd like to know if I'm going about this is the most efficient way or
if anyone has any suggestions for making this more efficient. The goal
is to package the connection and insert code into the functions as
neatly as possible and have each function always refer to the same ADO
object. I do this by making the ADO object a global. Is there a better
way to do this?

The functions are below. If the formatting gets mangled, please see
this site for clean formatting:

http://project1020.pbwiki.com/adodb_lite%20wrappers#Code

Thanks,
Tom


// wrapper_ado_cx
/*____________________________________________________________________________*/
function wrapper_ado_cx($db_name='_default', $db_user='_default',
$db_pass='_default', $db_server='_default', $db_type='mysql')
{
// *** DATA

# Global
global $O_WRAP_ADO;


// *** MANIPULATE

// Sanity Checks

# DB Name
if ( $db_name == '_default' )
{
if ( defined('DBA_NAME') )
{
$db_name = DBA_NAME;
}
else
{
trigger_error('invalid db name - DBA_NAME not set',
E_USER_WARNING);
return 0;
}
}

# DB User
if ( $db_user == '_default' )
{
if ( defined('DBA_USER') )
{
$db_user = DBA_USER;
}
else
{
trigger_error('invalid db user - DBA_USER not set',
E_USER_WARNING);
return 0;
}
}

# DB Pass
if ( $db_pass == '_default' )
{
if ( defined('DBA_PASS') )
{
$db_pass = DBA_PASS;
}
else
{
trigger_error('invalid db name - DBA_PASS not set',
E_USER_WARNING);
return 0;
}
}

# DB Server
if ( $db_server == '_default' )
{
if ( defined('DBA_SERVER') )
{
$db_server = DBA_SERVER;
}
else
{
trigger_error('invalid db name - DBA_SERVER not set',
E_USER_WARNING);
return 0;
}
}

# New ADO Object
if ( !is_object($O_WRAP_ADO['DB']) )
{
# Create New Object
$O_WRAP_ADO['DB'] = ADONewConnection($db_type);
$O_WRAP_ADO['DB']->Connect($db_server, $db_user, $db_pass,
$db_name);
$O_WRAP_ADO['DB']->db_name = $db_name;
}

# Retrieve Existing Object
else
{
# New Connection
if ( !$O_WRAP_ADO['DB']->IsConnected() )
{
$O_WRAP_ADO['DB']->Connect($db_server, $db_user, $db_pass,
$db_name);
$O_WRAP_ADO['DB']->db_name = $db_name;
}

# Existing Connection (check db name)
elseif ( $O_WRAP_ADO['DB']->db_name <> $db_name )
{
$O_WRAP_ADO['DB']->SelectDB($db_name);
$O_WRAP_ADO['DB']->db_name = $db_name;
}
}


// *** RETURN

return $O_WRAP_ADO['DB'];

} # end Fx
/*____________________________________________________________________________*/


// wrapper_ado_insert_array
/*____________________________________________________________________________*/
function wrapper_ado_insert_array($ARRAY, $table, $check_cols=1,
$db_name='_default')
{
// *** DATA

# Globals
global $O_WRAP_ADO;

# SQL
$_SQL = array();
$_SQL['check_cols'] = '';
$_SQL['insert_array'] = '';

# flags
$_FLAG['check_cols'] = $check_cols;
$_FLAG['is_col'] = 0;

# internal
$_link = 0;
$_insert_line = '';
$_values_line = '';
$_TABLE_COL = array();
$_COLUMN = array();
$_DATA_BIND = array();
$_ADO_r = 0;

# return
$insert_id = 0;


// *** MANIPULATE

// Check DB Connection
if ( !is_object($O_WRAP_ADO['DB']) ||
!$O_WRAP_ADO['DB']->IsConnected() )
{
if ( !wrapper_ado_cx($db_name) )
{
trigger_error('could not establish db connection',
E_USER_WARNING);
return 0;
}
}

// Check DB

if ( $db_name <> '_default' && $O_WRAP_ADO['DB']->db_name <>
$db_name )
{
$O_WRAP_ADO['DB']->SelectDB($db_name);
$O_WRAP_ADO['DB']->db_name = $db_name;
}

// Set Fetch Mode

$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;

// Check Table Columns
if ( $check_cols )
{
# SQL Query
$_SQL['check_cols'] = "SHOW COLUMNS FROM $table";

# Run Query
if ( !$_ADO_r = $O_WRAP_ADO['DB']->Execute($_SQL['check_cols']) )

{
trigger_error("Unable to check columns for table [$table]:
{$_SQL['check_cols']}", E_USER_WARNING);
return 0;
}

# Sanity Check
if ( !$_ADO_r->RecordCount() )
{
trigger_error("No columns found for table [$table]",
E_USER_WARNING);
return 0;
}

# Collect Table Columns
else
{
while ( !$_ADO_r->EOF )
{
$_TABLE_COL = $_ADO_r->fields;
$_COLUMN[] = $_TABLE_COL['Field'];
$_ADO_r->MoveNext();
}
}
}

// Construct Query String

# cycle array
foreach ( $ARRAY as $_key => $_value )
{
# check to see ARRAY key is a table column [$_COLUMN]
if ( !$check_cols || in_array($_key, $_COLUMN ) )
{
# extend insert, values line
$_insert_line .= "`$_key`, ";
$_values_line .= "?, ";

# add value to data binding array
$_DATA_BIND[] = $_value;
}
else
{
trigger_error("key [$_key] is not column in table [$table] ->
will not be inserted; please correct code", E_USER_NOTICE);
}
}

# assemble query

# trim insert superfluous comma from query strings
$_insert_line = substr($_insert_line, 0, strrpos($_insert_line,
','));
$_values_line = substr($_values_line, 0, strrpos($_values_line,
','));

# SQL query
$_SQL['insert_array'] = <<<SQL
INSERT INTO `$table` ( $_insert_line )
VALUES ( $_values_line )
SQL;

# debug
if ( OWRAP_DEBUG ) trigger_notice($_SQL['insert_array']);


// Run Query

if ( !$_ADO_r = $O_WRAP_ADO['DB']->Execute($_SQL['insert_array'],
$_DATA_BIND) )
{
trigger_error($O_WRAP_ADO['DB']->ErrorMsg(), E_USER_WARNING);
return 0;
}
else
{
$insert_id = $O_WRAP_ADO['DB']->Insert_ID();
}


// Close DB

$O_WRAP_ADO['DB']->Close();


// *** RETURN

return $insert_id;

} # end Fx
/*____________________________________________________________________________*/

• Next in forum: String comparison problems
• Prev in forum: Re: MySQL prepare statement performance bottom-neck
• Thread view: Wrapper for ADOdb_lite -- best way to do it?

[Reply to this message]