Query Helper

The Query Helper file contains functions that assist in returning data using MySQL queries. It also includes some bitvector mathematics (setting boolean toggles by bit on an integer). Use the mysql_*() functions only on pages that are not HerbIgniter-based (you can require this helper as a library in raw PHP), and you can also use the equivalent functions inside HerbIgniter (namely: find, find_like, find_all, get_related, etc)

Loading this Helper

This helper is loaded using the following code:

$this->load->helper('query');

The following functions are available:

flag( bit, flag )

Returns true if bit is set on flag. Bit can be compound (a series of bits such as 1, 2, 4, 8, 32 but not 16, checked against the flag variable). A bitvector is a power of two (1,2,4,8,16,32,64,...)

Example:

if ( !flag( SOME_CONSTANT_BIT, $flags ) ) return true; else return false;

on( bit, flag )

Returns true if bit is set on flag. Bit can be compound (a series of bits such as 1, 2, 4, 8, 32 but not 16, checked against the flag variable).

if ( !on( SOME_CONSTANT_BIT, $flags ) ) return true;

else return false;

off( bit, flag )

Returns true if bit is NOT set on flag. Bit can be compound (a series of bits such as 1, 2, 4, 8, 32 but not 16, checked against the flag variable).

if ( off( SOME_CONSTANT_BIT, $flags ) ) return true;

else return false;

bittoggle( bit, flag )

Toggles the status of a bit on a flag vector.

bittoggle( SOME_CONSTANT_BIT, $

adt( strlist )

Adds the tick (`) to a string that looks like "this, that, the, other, thing" becomes "`this`,`that`,`the`,`other`,`thing`"

$result_str = adt( "this, that, the, other, thing");

adq( strlist )

Adds apostrophes (') to a string that looks like "this, that, the, other, thing" becomes "'this','that'..."

$result_str = adq( $strlist );

sq( str )

Slash quotes: fixes \" and \' to be " and ' (the sourceforge bug)

$result_str = sq( $str );

msq( str )

Make slash quotes: fixes " and ' to be \" and \' (to include in javascript)

$result_str = msq( $str );

qs( str )

Make slash quotes: fixes " to be \" (to include in javascript)
Fixes \n to be \\n

$result_str = qs( $str );

err( die, query="" )

Displays the die() message evalulating mysql_query() (or any other error message); setting the global $err_file tells you the originating file that the MySQL is complaining in.

$res = mysql_query($query,$target_db) or err(mysql_error(),$query);

mysql_get_last_id( table )

Acquires the last_insert_id of a MySQL table. Uses the global $target_db, which must be set to an active MySQL database connection.

$last_id = mysql_get_last_id( "users" );

mysql_insert( table, field, value )

Does a simple, single-value insert into a table and returns the resulting mysql resource. Uses the global $target_db, which must be set to an active MySQL database connection.

mysql_insert( "users", "id", 7 );

mysql_set( table, id, field, value )

Updates a single value in a table row. Assumes your table has a primary key named id - Uses the global $target_db, which must be set to an active MySQL database connection.

mysql_set( "users", $user_id, "username", "Zephyr667" );

mysql_now( table, id, field )

Inserts the current timestamp into a DATETIME or TIMESTAMP field in a MySQL database. Uses the global $target_db, which must be set to an active MySQL database connection.

mysql_now( "users", $user_id, "modified" );

mysql_activate( table, id, bitvector, bit )

Activates (or leaves unchanged if already set) an arbitrary bit on a field that acts like a bitvector. Uses the global $target_db, which must be set to an active MySQL database connection.

define( SOME_FLAG_THAT_MEANS_USER_IS_BANNED, 1 );



mysql_activate( "users", $user_id, "access_flags", SOME_FLAG_THAT_MEANS_USER_IS_BANNED );

mysql_deactivate( table, id, bitvector, bit )

Deactivates (or leaves unchanged if already unset) an arbitrary bit on a field that acts like a bitvector. Uses the global $target_db, which must be set to an active MySQL database connection.

define( SOME_FLAG_THAT_MEANS_USER_IS_BANNED, 1 );



mysql_deactivate( "users", $user_id, "access_flags", SOME_FLAG_THAT_MEANS_USER_IS_BANNED );

mysql_toggle( table, id, bitvector, bit )

Toggles (or leaves unchanged if already unset) an arbitrary bit on a field that acts like a bitvector. Uses the global $target_db, which must be set to an active MySQL database connection.

define( SOME_FLAG_THAT_MEANS_USER_IS_BANNED, 1 );



mysql_toggle( "users", $user_id, "access_flags", SOME_FLAG_THAT_MEANS_USER_IS_BANNED );

mysql_flag_value( table, id, field )

Returns the value of a field in a table, used mainly for requesting a single value without getting the entire resource. Uses the global $target_db, which must be set to an active MySQL database connection.

$flags = mysql_flag_value( "users", $user_id, "access_flags" );

mysql_has( table, id, field, bit )

This function tests for the presence of a bit on a flag field. Uses the global $target_db, which must be set to an active MySQL database connection.

if ( mysql_has( "users", $user_id, "access_flags', SOME_FLAG_THAT_MEANS_USER_IS_BANNED ) ) {...} else {...}

mysql_add_field( table, field, sql_type )

Adds a field to a MYSQL table. Uses the global $target_db, which must be set to an active MySQL database connection.

mysql_add_field( "users", "brief_bio", "VARCHAR(255)" );

mysql_find( table, field, value, other="" )

Finds the first row returned from a table with a particular field and value. Returns a single array that contains 1 table entry and is indexed by row['field']. Uses the global $target_db, which must be set to an active MySQL database connection.

$row=mysql_find( "users", "username", "jrichards" );



// Use the optional fourth parameter to filter more specifically.

$row=mysql_find( "users", "username", "jrichards", " AND WHERE modified=NOW() ORDER BY `username` LIMIT 1" );

mysql_find_like( table, field, value, filter="" )

Finds the all rows returned from a table with particular fields and values. Returns an array, similar to $this->db->query() - Uses the global $target_db, which must be set to an active MySQL database connection.

// Returns all rows in table `cars` where field `brand` is 'Ford'

$rows=mysql_find_like( "cars", "brand", "Ford"  );



// Returns only rows in table `cars` matching brand `Ford` released after the year 1997


$rows=mysql_find_like( "cars", "brand", "Ford", " AND `released`>1997 ORDER BY `released` ASC LIMIT 100" );

mysql_find_month( table, field, month, year, filter="" )

Finds all items in a table by a date field which fall within a single month in a single year. Returns an array, similar to $this->db->query() - Uses the global $target_db, which must be set to an active MySQL database connection.

$september09 = mysql_find_month( "logins", "time", 9, 2009 );

mysql_find_all( table, filter="" )

Returns every entry in a table. You can also use this as a basic query generator. Returns an array, similar to $this->db->query() - Uses the global $target_db, which must be set to an active MySQL database connection.

$table = mysql_find_all( "users" );



// Specify subsets...

$table = mysql_find_all( "users", " WHERE `username` LIKE '%john%'" );




// Other queries...


// Specify subsets...

$table = mysql_find_all( "users", " WHERE `username` LIKE '%john%' AND `age`>17 LIMIT 5" );

mysql_get_related( table, id, val )

Just like "find_like" but cannot be expanded.

// Returns all rows in table `cars` where field `brand` is 'Ford'

$rows=mysql_get_related( "cars", "brand", "Ford"  );

mysql_find_sorted( table, order_by, limit, asc_desc="DESC" )

Returns everything in a table ordered by a field within a particular limit that is greater than 0, ASCending or DESCending. Uses the global $target_db, which must be set to an active MySQL database connection.

$sorted = mysql_find_sorted( "users", "username", 10, "ASC" );

mysql_to_array($res)

Converts a raw mysql resource (returned by mysql_query() ) to an array.

$array = mysql_to_array( mysql_query ( $db_conn, $somequery ) ) ;

find_like( table, id, value, filter="" )

Uses HerbIgniter's $this->db->query() to execute a SELECT query.

// Returns all rows in table `cars` where field `brand` is 'Ford'

$rows=find_like( "cars", "brand", "Ford"  );



// Returns only rows in table `cars` matching brand `Ford` released after the year 1997 

$rows=find_like( "cars", "brand", "Ford", " AND `released`>1997 ORDER BY `released` ASC LIMIT 100" );

find_similar( table, id, value, filter="" )

Identical to find_like (above)

// Returns all rows in table `cars` where field `brand` is 'Ford'

$rows=find_similar( "cars", "brand", "Ford"  );



// Returns only rows in table `cars` matching brand `Ford` released after the year 1997 

$rows=find_similar( "cars", "brand", "Ford", " AND `released`>1997 ORDER BY `released` ASC LIMIT 100" );

find_all( table, filter="" )

Returns every entry in a table. You can also use this as a basic query generator. Returns an array, similar to $this->db->query()

$table = find_all( "users" );



// Specify subsets...

$table = find_all( "users", " WHERE `username` LIKE '%john%'" ); 



// Other queries... 

// Specify subsets...

$table = find_all( "users", " WHERE `username` LIKE '%john%' AND `age`>17 LIMIT 5" );

find_month( table, field, month, year, filter="" )

Finds all items in a table by a date field which fall within a single month in a single year. Returns an array, similar to $this->db->query()

$september09 = mysql_find_month( "logins", "time", 9, 2009 );

insert( table, field, value )

Does a simple, single-value insert into a table and returns the last_insert_id

insert( "users", "id", 7 );

new_id( table, primary_key="id" )

Attempts to calculate the next_insert_id

$next_id = new_id( "users" );

multiinsert( $table, [field, value ... ])

Discretized insert statement that executes a database query, returning the last_insert_id

$user_id = multiinsert( "users", "username", "MMoore", "first_name", "Michael", "last_name", "Moore" );

multiupdate( table, id, [field, value ... ])

Discretized insert statement that executes a database query

multiupdate( "users", $user_id, "username", "MikeyMoore", "first_name", "Mikey" );

set( table, id, field, value )

Sets a table by primary key id, a field and value.

set( "users", $user_id, "username", "Mikey420" );

now( table, id, field )

Updates a timestamp field in a table row to the current time.

now( "users", $user_id, "logged_off" );

activate( table, id, field, bit )

Activates a flag on bit in a table by id

activate( "users", $user_id, "flags", FLAG_USER_IS_LOGGED_OUT );

deactivate( table, id, field, bit )

Bitvector deactivation (opposite of activate)

deactivate( "users", $user_id, "flags", FLAG_USER_IS_LOGGED_IN );

toggle( table, id, field, bit )

Bitvector toggling; toggles bit in field on table row with id

toggle( "users", $user_id, "flags", FLAG_ONLINE_STATUS );

flag_value( table, id, field )

You can also thing of this as "get()", the opposite of "set()", but its also very much like the mysql_flag_value() function. It returns a flag or value from a field in a row.

$flags = flag_value( "users", $user_id, "flags" );

has( table, id, field, bit )

Checks to see if a table at id with field has bit.