Register Members List Search Today's Posts Mark Forums Read

Reply
 
Article Options
[Part 2] Learning the Basics of Coding: Database Interaction
Pandemikk
Join Date: Jul 2009
Posts: 291

by Pandemikk Pandemikk is offline 31 Mar 2012

[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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

% 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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Whenever specifying multiple columns to be updated you must use a comma before each column = value. Not doing so result in an error message.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

What did I do wrong here? I caused a database error because the query will execute as:


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

The query's like wtf is "demikk"? Note: I have no table_prefix.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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.

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

That's a perfectly acceptable query and, in my personal opinion, is better than:


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


Last edited by Pandemikk : 20 Jun 2012 at 06:10.
Views: 4205
Reply With Quote
Comments
  #2  
Old 10 Apr 2012, 20:29
v123shine v123shine is offline
 
Join Date: Sep 2008
very difficult for me :'(
__________________
Games
Reply With Quote
  #3  
Old 26 Apr 2012, 02:41
Dax IX Dax IX is offline
 
Join Date: Jul 2005
Real name: Emily
Two things...first, there's a typo in your INSERT IGNORE INTO explanation. I think you meant:

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!
__________________
Just a placeholder.
Reply With Quote
  #4  
Old 26 Apr 2012, 19:47
Pandemikk Pandemikk is offline
 
Join Date: Jul 2009
Real name: Jon
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 26 Apr 2012, 19:56
Dax IX Dax IX is offline
 
Join Date: Jul 2005
Real name: Emily
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.
__________________
Just a placeholder.
Reply With Quote
  #6  
Old 01 May 2012, 18:44
abdobasha2004's Avatar
abdobasha2004 abdobasha2004 is offline
 
Join Date: Aug 2008
this is some priceless effort ....
__________________
Egypt News website, latest Egyptian news updated instantly.
Reply With Quote
  #7  
Old 04 May 2012, 07:48
Pandemikk Pandemikk is offline
 
Join Date: Jul 2009
Real name: Jon
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 15 May 2012, 12:19
tradedemon's Avatar
tradedemon tradedemon is offline
 
Join Date: Nov 2006
Real name: Zee
Awesome work ,, looking forward to part 3 also like how this one is more VB specific.
__________________
Illustration & Web Design

NEW!! - FREE Vbulletin Skins
Reply With Quote
  #9  
Old 11 Jun 2012, 19:30
boooooo boooooo is offline
 
Join Date: Apr 2008
need part 3
Reply With Quote
  #10  
Old 06 Nov 2012, 19:30
Dorgham's Avatar
Dorgham Dorgham is offline
 
Join Date: May 2012
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
__________________
AdSense revenue sharing 100% - [ Hanan ]
News, egypt : أخبار مصر - Photo, picture : صور
Reply With Quote
  #11  
Old 05 May 2013, 19:23
TheSupportForum TheSupportForum is offline
 
Join Date: Jan 2007
once this is set in the php file, how would i output the $results into a table in as template

i am yet to convert standard php to vbulletin for my sql query, so

what i want to do it put this into a table of 2 rows 3 columns into a template for output

1) what is the correct procedure to write the mysqli_query in the php file
2) how to output $results into a template
3) do i need to register $variables for each $row['fieldname'];
- if so, how ?
__________________
http://www.multihunters.co.uk - all your coding needs
Reply With Quote
  #12  
Old 08 May 2013, 17:39
TheSupportForum TheSupportForum is offline
 
Join Date: Jan 2007
how do you query this to show multiple tables lets say

$row['country'] has 20 different countries

how do i output different 'countries' into separate tables without having to repeat the same query
__________________
http://www.multihunters.co.uk - all your coding needs
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Article Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
Forum Jump


New To Site? Need Help?

All times are GMT. The time now is 22:59.

Layout Options | Width: Wide Color: