Go Back   vb.org Archive > vBulletin Article Depository > Read An Article > Programming Articles
Reload this Page [Part 2] Learning the Basics of Coding: Database Interaction
FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply
Page 1 of 2 1 2
 
Thread Tools
[Part 2] Learning the Basics of Coding: Database Interaction
Pandemikk
Join Date: Jul 2009
Posts: 292

 

Show Printable Version Email this Page Subscription
Pandemikk Pandemikk is offline 03-30-2012, 10:00 PM
[Part 1] Learning the Basics of Coding

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.

Code:
+---------+-------------+------+-----+---------+-------+
| 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/...ng-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.

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.

PHP Code:
$me = $db->query_first("SELECT userid FROM " . TABLE_PREFIX . "user WHERE username = 'Pandemikk'");

// outputs 344400
echo $me['userid']; 
PHP Code:
$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.

PHP Code:
$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.

PHP Code:
$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.

PHP Code:
$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.

PHP Code:
$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.

PHP Code:
$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.

PHP Code:
$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.

PHP Code:
$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.

PHP Code:
$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:
PHP Code:
$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.

PHP Code:
$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:

Code:
SELECT userid FROM user WHERE username = 'Pan'demikk'
The query's like wtf is "demikk"? Note: I have no table_prefix.

PHP Code:
$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.
PHP Code:
$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:

PHP Code:
$me['userid'] = 344400;
 
 $result_q = $db->query_read("SELECT userid FROM " . TABLE_PREFIX . "user WHERE userid = " . $me['userid'] . " "); 
Reply With Quote
  #2  
Old 04-10-2012, 07:29 PM
v123shine v123shine is offline
 
Join Date: Sep 2008
Posts: 242
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
very difficult for me :'(
Reply With Quote
  #3  
Old 04-26-2012, 01:41 AM
Dax IX Dax IX is offline
 
Join Date: Jul 2005
Posts: 153
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Two things...first, there's a typo in your INSERT IGNORE INTO explanation. I think you meant:

Quote:
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.
And second, when you say query_write, you're still typing query_read in your examples. Is this correct?

And I'm assuming this article is valid for both vB3 and vB4?

I'm still learning PHP, much less vB programming, but this helps a lot! Thanks!
Reply With Quote
  #4  
Old 04-26-2012, 06:47 PM
Pandemikk Pandemikk is offline
 
Join Date: Jul 2009
Posts: 292
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Quote:
Originally Posted by Chani View Post
Two things...first, there's a typo in your INSERT IGNORE INTO explanation. I think you meant:

And second, when you say query_write, you're still typing query_read in your examples. Is this correct?

And I'm assuming this article is valid for both vB3 and vB4?

I'm still learning PHP, much less vB programming, but this helps a lot! Thanks!
Yep. Both were typos. I've since fixed then.

This article applies for both vB3 and vB4 since there hasn't been any (that I've noticed) changes between them when dealing with the database.

No problem! The first part of my articles deals with basic PHP, you should take a look at it if you're trying to learn the basics of PHP.
Reply With Quote
  #5  
Old 04-26-2012, 06:56 PM
Dax IX Dax IX is offline
 
Join Date: Jul 2005
Posts: 153
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Yup, I've had a look at your other article, too.

I can't wait for Part 3!

I'd like to see a little more about reading and writing more than one field in a database record.

I know the scope of this article doesn't necessarily cover MySQL, but whatever information you can divulge would be helpful.

Unless of course that's what Part 3 will be.
Reply With Quote
  #6  
Old 05-01-2012, 05:44 PM
abdobasha2004's Avatar
abdobasha2004 abdobasha2004 is offline
 
Join Date: Aug 2008
Posts: 541
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
this is some priceless effort ....
Reply With Quote
  #7  
Old 05-04-2012, 06:48 AM
Pandemikk Pandemikk is offline
 
Join Date: Jul 2009
Posts: 292
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Quote:
Originally Posted by Chani View Post
Yup, I've had a look at your other article, too.

I can't wait for Part 3!

I'd like to see a little more about reading and writing more than one field in a database record.

I know the scope of this article doesn't necessarily cover MySQL, but whatever information you can divulge would be helpful.

Unless of course that's what Part 3 will be.
Writing more than one column has an example in this article. Updating and reading columns are very similar to updating and reading a column. Just always be sure to separate with a comma.

Maybe one day I'll be able to write up a MySQL general article, but I've been extremely busy lately. Part III is due, I have about half of it drafted.
Reply With Quote
  #8  
Old 05-15-2012, 11:19 AM
tradedemon tradedemon is offline
 
Join Date: Oct 2006
Location: Bedfordshire, UK
Posts: 3
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Awesome work ,, looking forward to part 3 also like how this one is more VB specific.
Reply With Quote
  #9  
Old 06-11-2012, 06:30 PM
boooooo boooooo is offline
 
Join Date: Apr 2008
Posts: 6
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
need part 3
Reply With Quote
  #10  
Old 11-06-2012, 07:30 PM
Dorgham's Avatar
Dorgham Dorgham is offline
 
Join Date: May 2012
Location: Egypt
Posts: 69
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
I followed the first part of the basics of Coding: Database and really benefited him greatly benefit the and we are waiting for the third part
Reply With Quote
Reply
Page 1 of 2 1 2

« Previous Thread | Next Thread »
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT. The time now is 09:27 AM.

Contact Us - Archive - Top

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.05012 seconds
  • Memory Usage 2,347KB
  • Queries Executed 23 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (2)bbcode_code
  • (15)bbcode_php
  • (3)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)modsystem_article
  • (1)navbar
  • (4)navbar_link
  • (120)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (10)post_thanks_box
  • (4)post_thanks_box_bit
  • (10)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (1)post_thanks_postbit
  • (10)post_thanks_postbit_info
  • (9)postbit
  • (10)postbit_onlinestatus
  • (10)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 
Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • fetch_musername
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • post_thanks_function_fetch_thanks_bit_start
  • post_thanks_function_show_thanks_date_start
  • post_thanks_function_show_thanks_date_end
  • post_thanks_function_fetch_thanks_bit_end
  • post_thanks_function_fetch_post_thanks_template_start
  • post_thanks_function_fetch_post_thanks_template_end
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete