These classes are based on a much improved set of classes and functions that were originally built on CodeAcademy and that we have found extremely useful. They can serve as a basis for not only UserSpice or for your project as a whole.

Usage

To instantiate (use) the database object

$db = DB::getInstance();

Please note: You only need to do this if you are not pulling in the UserSpice headers/init.  If you are, we do that for you.  You will also need to use this in any function you create that uses the database.

To insert into the database, you make an array of your fields then run the insert query, like this…
$fields=array('user_id'=>$user_id, 'profile_pic'=>'dan.jpg'); //column_name=>entry
$db->insert('table_name',$fields);
To delete from the database it is tablename, selector, and value so something like…
$db->delete('users',array('id','=',4));

Updating is similar. Table name, id and the same fields array

PLEASE NOTE: This method assumes that the first column in your table is called id.  It can’t be userID or forumID or it won’t work and will drive you crazy.
$db->update('camps',5,$fields);

Find All
$userQ = $db->findAll('camps');
$camps = $campQ->results(); //will give you a multidimensional array
Or you can just find the first
$first_camp = $campQ->first();

Find By Id
Relational SQL databases live and die by ids.  It’s really common to need to search a database table by id.  We make that easy.  Let’s say you want to find a user based on the id in your variable.
$id = 1;
$player = $db->findById($id,'users');

Get (A Quick Select * Feature)

If you want all results from the users table where the deactivated field is set to 0)…
$users = $db->get('users',['deactivated','=',0]);

If you only want a SINGLE result (the first one), that’s where the false comes in…
$user = $db->get('users',['id','=',11],false);

Frustrated with the classes? Just run a basic or bound query, like this…
$director_id = 1;
$query = $db->query("SELECT * FROM camps WHERE director_id = ?", array($director_id));
$x = $query->results();

Don’t want to deal with objects? No problem.  Just pass the word true into your results query.

$x = $query->results(true);
BAM, no more OOP results!

Binding. You should do it.  That’s all those question marks in the queries.  It’s super simple.
If you are sitting there scratching your head that you didn’t get the results that you expected from the database,
it’s probably because you put a variable in the query itself. Save yourself the headache and do it right.

$thing1 = "thisthing";
$thing2 = "thatthing";
$query = $db->query("SELECT * FROM users WHERE name = ? AND job = ?",[$thing1,$thing2]);

These are the original videos describing how the db class was created several years ago.

These classes are based on a much improved set of classes and functions that were originally built on CodeAcademy and that we have found extremely useful. They can serve as a basis for not only UserSpice or for your project as a whole.

Usage

To instantiate (use) the database object

$db = DB::getInstance();

Please note: You only need to do this if you are not pulling in the UserSpice headers/init.  If you are, we do that for you.  You will also need to use this in any function you create that uses the database.

To insert into the database, you make an array of your fields then run the insert query, like this…
$fields=array('user_id'=>$user_id, 'profile_pic'=>'dan.jpg'); //column_name=>entry
$db->insert('table_name',$fields);
To delete from the database it is tablename, selector, and value so something like…
$db->delete('users',array('id','=',4));

Updating is similar. Table name, id and the same fields array

PLEASE NOTE: This method assumes that the first column in your table is called id.  It can’t be userID or forumID or it won’t work and will drive you crazy.
$db->update('camps',5,$fields);

Find All
$userQ = $db->findAll('camps');
$camps = $campQ->results(); //will give you a multidimensional array
Or you can just find the first
$first_camp = $campQ->first();

Find By Id
Relational SQL databases live and die by ids.  It’s really common to need to search a database table by id.  We make that easy.  Let’s say you want to find a user based on the id in your variable.
$id = 1;
$player = $db->findById($id,'users');

Get (A Quick Select * Feature)

If you want all results from the users table where the deactivated field is set to 0)…
$users = $db->get('users',['deactivated','=',0]);

If you only want a SINGLE result (the first one), that’s where the false comes in…
$user = $db->get('users',['id','=',11],false);

Frustrated with the classes? Just run a basic or bound query, like this…
$director_id = 1;
$query = $db->query("SELECT * FROM camps WHERE director_id = ?", array($director_id));
$x = $query->results();

Don’t want to deal with objects? No problem.  Just pass the word true into your results query.

$x = $query->results(true);
BAM, no more OOP results!

Binding. You should do it.  That’s all those question marks in the queries.  It’s super simple.
If you are sitting there scratching your head that you didn’t get the results that you expected from the database,
it’s probably because you put a variable in the query itself. Save yourself the headache and do it right.

$thing1 = "thisthing";
$thing2 = "thatthing";
$query = $db->query("SELECT * FROM users WHERE name = ? AND job = ?",[$thing1,$thing2]);