Pandemikk
03-30-2012, 10:00 PM
[Part 1] Learning the Basics of Coding (https://vborg.vbsupport.ru/showthread.php?t=280880)
The second part of my four part article will focus on database interactions: Using PHP to interact with your database in various manners. This is a fundamental part of any modification, software or program as the database stores information that would otherwise be lost when a script has finished executing.
The Basics
I will use some terms here you may be unfamiliar with, so hopefully this depiction will help you visualize the relationship.
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+Table: Pet (http://dev.mysql.com/doc/refman/5.0/en/creating-tables.html)
For full information on column types please see the above link.
Database Interacting
For the sake of where this is being posted, we are going to be using PHP as our server-side language and MySQL as our database. We will also be assuming we are within a vB environment. The database abstraction class between vB3 and vB4 is very similar, so no assumptions of vB versions will be made.
Important Variables & Constants:
Please refer to part one if you do not know what a constant is.
$vbulletin is a variable. It refers to the vbulletin object.
$db is a variable. It refers to the database object.
Objects are instances of a class. Classes contain their own constants, variables (properties) and functions (methods).
$vbulletin->db for all intents and purposes is the same as $db. However, depending on where you are using the code, $db (and even $vbulletin) may not be available to you. Why? Variable scope. If you ever get a using $db when not in context error, that means $db is not an object. You'll get whenever operating in a class that does not have $db in its scope. You can fix this by adding global $db; or passing $db as an argument when calling a method. Be sure that $db is an object before passing it as an argument.
For this guide, we will be assuming $db is in context. I will be using $db instead of $vbulletin-> as I prefer it because it is much shorter and, I believe, there is less overhead from doing so. It should be noted that when $vbulletin is in context but $db is not you should use $vbulletin->db instead of bringing $db into context.
TABLE_PREFIX this is a very important constant. Never, ever, ever forget to put this in front of a table when querying the database. Why? Because for databases that use a prefix you will get a "table not found" database error. Never allow database errors.
query_first
Selects the first result within a query.
A common misconception, one I had until recently as well, was that query_first adds a LIMIT ONE. It does not. This means, unless only one row is returned in the result set, you will be wasting resources. More on why this is inefficient in [Part 3] Learning the Basics of Coding: Best Practices (https://vborg.vbsupport.ru/showthread.php?p=2340978#post2340978).
So what's the point of using query_first? Because it saves you time. It fetches the first row and returns it to you as an array instead of a result. It also frees the result from memory.
$me = $db->query_first("SELECT userid FROM " . TABLE_PREFIX . "user WHERE username = 'Pandemikk'");
// outputs 344400
echo $me['userid'];$me = $db->query_first("SELECT userid FROM " . TABLE_PREFIX . "user WHERE username = 'Pan%' ");% is a wildcard selector. Selects every username that begins with: Pan
query_read
Now say you need multiple rows? Well then you need a result set and query_read is your friend.
$result_q = $db->query_read("SELECT username FROM " . TABLE_PREFIX . "user WHERE userid IN (0, 344400)");Nobody has a userid of 0, because userid is an auto-incrementing field. This means, each time a new record is added to the table the userid field goes up by one. This is why userid, threadid, forumid, etc,. never have values less than 1.
num_rows
Returns the amount of rows in the result resource. This method ONLY works on mysql results. Returns false on failure and, as you should know, a control structure (such as if) does not execute if it evaluates to false.
You should use this method before fetch_array because using fetch_array on an invalid mysql result set (no rows returned for example) will emit: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result. Avoid warnings.
$result_q = $db->query_read("SELECT username FROM " . TABLE_PREFIX . "user WHERE userid = 0");
if (!$db->num_rows($result_q))
{
echo 'Fail';
exit;
}Remember, no 0 values in auto-increment fields.
fetch_array
Returns an array from a result resource. In other words, it takes a result set and returns a row from it. So to grab all the rows you will need to use a loop.
$result_q = $db->query_read("SELECT username FROM " . TABLE_PREFIX . "user WHERE userid IN (0, 344400)");
if ($db->num_rows($result_q))
{
while ($result_r = $db->fetch_array($result_q))
{
echo $result_r['username'];
}
}
$db->free_result($result_q);This is the best way to fetch a mysql result.
free_result
This method will destroy the result resource thus freeing up memory. You should always free_result a mysql result when you are finished with it.
$result_q = $db->query_read("SELECT username FROM " . TABLE_PREFIX . "user WHERE userid IN (0, 344400) ");
$db->free_result($result_q);
if ($db->num_rows($result_q))
{
echo true;
}Your browser will never receive output because $result_q is now NULL. NULL evaluates to false.
query_write
This method will allow you to save to the database.
$result_q = $db->query_write("UPDATE " . TABLE_PREFIX . "user SET username = 'King', email = 'iamking@king.com' WHERE username = 'Pandemikk' ");Whenever specifying multiple columns to be updated you must use a comma before each column = value. Not doing so result in an error message.
$result_q = $db->query_write("INSERT INTO " . TABLE_PREFIX . "user (username) VALUES ('Pandemikk') ");Will insert another of me into the user table. I'm not including any of the other user columns because this is an example.
The difference between update and insert is fairly obvious. Update will update an existing record while insert will create a new record.
There are also less common variants of the above suit for special purposes.
$result_q = $db->query_write("INSERT IGNORE INTO " . TABLE_PREFIX . "user (username) VALUES ('Pandemikk') ");Assuming username is a primary key or unique key, this query will insert a new record if the username Pandemikk does not exist or do nothing (no database error) if it does.
$result_q = $db->query_write("REPLACE INTO " . TABLE_PREFIX . "user (username) VALUES ('Pandemikk') ");Assuming username is a primary key or unique key, this query will DELETE the old row and INSERT the new one if a record for the username Pandemikk exists.
deleting records - From time-to-time, you may need to delete some old records. This can be used such as below:
$result_q = $db->query_write("DELETE FROM " . TABLE_PREFIX . "user WHERE username = 'Pandemikk' ");Deleted myself from the user table.
escape_string
Always use this method when using strings in your query. An exception is if you know for sure that the string is not user-input and does not have any single quotes in it. What's the big deal you're asking? The big deal is doing so leaves you open to SQL injections. SQL injections basically say: "Here's access to the database, do whatever you want with it." See [Part 4] for more information on security.
$username = "Pan'demikk";
$result_q = $db->query_read("SELECT userid FROM " . TABLE_PREFIX . "user WHERE username = '" . $username . "' ");What did I do wrong here? I caused a database error because the query will execute as:
SELECT userid FROM user WHERE username = 'Pan'demikk'The query's like wtf is "demikk"? Note: I have no table_prefix.
$username = "Pan'demikk";
$result_q = $db->query_read("SELECT userid FROM " . TABLE_PREFIX . "user WHERE username = '" . $db->escape_string($username) . "' ");Much better. Although there's no user with the name "Pan'demikk" there's no exploit in the query, either.
What escape_string does it add a backslash in front of single quotes, backslashes, and other special characters, which effectively "escapes" the input.
When the query is executed, backslashes are stripped once from the parser and again when the pattern match is made, so don't worry about escape_string returning wrong data.
Final Notes
Couldn't really find a place to put these so I'll stuff them here.
All results from a query are returned as strings. Even if the column is type int it will return as a string.
You should never wrap integers around single quotes.
You should always wrap strings around single quotes.
Use = instead of LIKE when searching for strings without a wildcard.
By default, MySQL is not case-sensitive.
If you want to search for '\' you will need to specify: '\\\\' unless you are searching at the end of the string then simply '\\' will suffice.
vB coding standards dictates you wrap queries in double-quotes. This means you do not need to exit a string when using a variable within a query.$me['userid'] = 344400;
$result_q = $db->query_read("SELECT userid FROM " . TABLE_PREFIX . "user WHERE userid = " . $me['userid']);That's a perfectly acceptable query and, in my personal opinion, is better than:
$me['userid'] = 344400;
$result_q = $db->query_read("SELECT userid FROM " . TABLE_PREFIX . "user WHERE userid = " . $me['userid'] . " ");
The second part of my four part article will focus on database interactions: Using PHP to interact with your database in various manners. This is a fundamental part of any modification, software or program as the database stores information that would otherwise be lost when a script has finished executing.
The Basics
I will use some terms here you may be unfamiliar with, so hopefully this depiction will help you visualize the relationship.
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+Table: Pet (http://dev.mysql.com/doc/refman/5.0/en/creating-tables.html)
For full information on column types please see the above link.
Database Interacting
For the sake of where this is being posted, we are going to be using PHP as our server-side language and MySQL as our database. We will also be assuming we are within a vB environment. The database abstraction class between vB3 and vB4 is very similar, so no assumptions of vB versions will be made.
Important Variables & Constants:
Please refer to part one if you do not know what a constant is.
$vbulletin is a variable. It refers to the vbulletin object.
$db is a variable. It refers to the database object.
Objects are instances of a class. Classes contain their own constants, variables (properties) and functions (methods).
$vbulletin->db for all intents and purposes is the same as $db. However, depending on where you are using the code, $db (and even $vbulletin) may not be available to you. Why? Variable scope. If you ever get a using $db when not in context error, that means $db is not an object. You'll get whenever operating in a class that does not have $db in its scope. You can fix this by adding global $db; or passing $db as an argument when calling a method. Be sure that $db is an object before passing it as an argument.
For this guide, we will be assuming $db is in context. I will be using $db instead of $vbulletin-> as I prefer it because it is much shorter and, I believe, there is less overhead from doing so. It should be noted that when $vbulletin is in context but $db is not you should use $vbulletin->db instead of bringing $db into context.
TABLE_PREFIX this is a very important constant. Never, ever, ever forget to put this in front of a table when querying the database. Why? Because for databases that use a prefix you will get a "table not found" database error. Never allow database errors.
query_first
Selects the first result within a query.
A common misconception, one I had until recently as well, was that query_first adds a LIMIT ONE. It does not. This means, unless only one row is returned in the result set, you will be wasting resources. More on why this is inefficient in [Part 3] Learning the Basics of Coding: Best Practices (https://vborg.vbsupport.ru/showthread.php?p=2340978#post2340978).
So what's the point of using query_first? Because it saves you time. It fetches the first row and returns it to you as an array instead of a result. It also frees the result from memory.
$me = $db->query_first("SELECT userid FROM " . TABLE_PREFIX . "user WHERE username = 'Pandemikk'");
// outputs 344400
echo $me['userid'];$me = $db->query_first("SELECT userid FROM " . TABLE_PREFIX . "user WHERE username = 'Pan%' ");% is a wildcard selector. Selects every username that begins with: Pan
query_read
Now say you need multiple rows? Well then you need a result set and query_read is your friend.
$result_q = $db->query_read("SELECT username FROM " . TABLE_PREFIX . "user WHERE userid IN (0, 344400)");Nobody has a userid of 0, because userid is an auto-incrementing field. This means, each time a new record is added to the table the userid field goes up by one. This is why userid, threadid, forumid, etc,. never have values less than 1.
num_rows
Returns the amount of rows in the result resource. This method ONLY works on mysql results. Returns false on failure and, as you should know, a control structure (such as if) does not execute if it evaluates to false.
You should use this method before fetch_array because using fetch_array on an invalid mysql result set (no rows returned for example) will emit: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result. Avoid warnings.
$result_q = $db->query_read("SELECT username FROM " . TABLE_PREFIX . "user WHERE userid = 0");
if (!$db->num_rows($result_q))
{
echo 'Fail';
exit;
}Remember, no 0 values in auto-increment fields.
fetch_array
Returns an array from a result resource. In other words, it takes a result set and returns a row from it. So to grab all the rows you will need to use a loop.
$result_q = $db->query_read("SELECT username FROM " . TABLE_PREFIX . "user WHERE userid IN (0, 344400)");
if ($db->num_rows($result_q))
{
while ($result_r = $db->fetch_array($result_q))
{
echo $result_r['username'];
}
}
$db->free_result($result_q);This is the best way to fetch a mysql result.
free_result
This method will destroy the result resource thus freeing up memory. You should always free_result a mysql result when you are finished with it.
$result_q = $db->query_read("SELECT username FROM " . TABLE_PREFIX . "user WHERE userid IN (0, 344400) ");
$db->free_result($result_q);
if ($db->num_rows($result_q))
{
echo true;
}Your browser will never receive output because $result_q is now NULL. NULL evaluates to false.
query_write
This method will allow you to save to the database.
$result_q = $db->query_write("UPDATE " . TABLE_PREFIX . "user SET username = 'King', email = 'iamking@king.com' WHERE username = 'Pandemikk' ");Whenever specifying multiple columns to be updated you must use a comma before each column = value. Not doing so result in an error message.
$result_q = $db->query_write("INSERT INTO " . TABLE_PREFIX . "user (username) VALUES ('Pandemikk') ");Will insert another of me into the user table. I'm not including any of the other user columns because this is an example.
The difference between update and insert is fairly obvious. Update will update an existing record while insert will create a new record.
There are also less common variants of the above suit for special purposes.
$result_q = $db->query_write("INSERT IGNORE INTO " . TABLE_PREFIX . "user (username) VALUES ('Pandemikk') ");Assuming username is a primary key or unique key, this query will insert a new record if the username Pandemikk does not exist or do nothing (no database error) if it does.
$result_q = $db->query_write("REPLACE INTO " . TABLE_PREFIX . "user (username) VALUES ('Pandemikk') ");Assuming username is a primary key or unique key, this query will DELETE the old row and INSERT the new one if a record for the username Pandemikk exists.
deleting records - From time-to-time, you may need to delete some old records. This can be used such as below:
$result_q = $db->query_write("DELETE FROM " . TABLE_PREFIX . "user WHERE username = 'Pandemikk' ");Deleted myself from the user table.
escape_string
Always use this method when using strings in your query. An exception is if you know for sure that the string is not user-input and does not have any single quotes in it. What's the big deal you're asking? The big deal is doing so leaves you open to SQL injections. SQL injections basically say: "Here's access to the database, do whatever you want with it." See [Part 4] for more information on security.
$username = "Pan'demikk";
$result_q = $db->query_read("SELECT userid FROM " . TABLE_PREFIX . "user WHERE username = '" . $username . "' ");What did I do wrong here? I caused a database error because the query will execute as:
SELECT userid FROM user WHERE username = 'Pan'demikk'The query's like wtf is "demikk"? Note: I have no table_prefix.
$username = "Pan'demikk";
$result_q = $db->query_read("SELECT userid FROM " . TABLE_PREFIX . "user WHERE username = '" . $db->escape_string($username) . "' ");Much better. Although there's no user with the name "Pan'demikk" there's no exploit in the query, either.
What escape_string does it add a backslash in front of single quotes, backslashes, and other special characters, which effectively "escapes" the input.
When the query is executed, backslashes are stripped once from the parser and again when the pattern match is made, so don't worry about escape_string returning wrong data.
Final Notes
Couldn't really find a place to put these so I'll stuff them here.
All results from a query are returned as strings. Even if the column is type int it will return as a string.
You should never wrap integers around single quotes.
You should always wrap strings around single quotes.
Use = instead of LIKE when searching for strings without a wildcard.
By default, MySQL is not case-sensitive.
If you want to search for '\' you will need to specify: '\\\\' unless you are searching at the end of the string then simply '\\' will suffice.
vB coding standards dictates you wrap queries in double-quotes. This means you do not need to exit a string when using a variable within a query.$me['userid'] = 344400;
$result_q = $db->query_read("SELECT userid FROM " . TABLE_PREFIX . "user WHERE userid = " . $me['userid']);That's a perfectly acceptable query and, in my personal opinion, is better than:
$me['userid'] = 344400;
$result_q = $db->query_read("SELECT userid FROM " . TABLE_PREFIX . "user WHERE userid = " . $me['userid'] . " ");