The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
|
|||||||
|
[Part 2] Learning the Basics of Coding: Database Interaction
Pandemikk
Join Date: Jul 2009
Posts: 292
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 | | +---------+-------------+------+-----+---------+-------+ 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:
PHP Code:
query_read Now say you need multiple rows? Well then you need a result set and query_read is your friend. PHP Code:
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:
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:
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:
query_write This method will allow you to save to the database. PHP Code:
PHP Code:
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:
PHP Code:
deleting records - From time-to-time, you may need to delete some old records. This can be used such as below: PHP Code:
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:
Code:
SELECT userid FROM user WHERE username = 'Pan'demikk' PHP Code:
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.
PHP Code:
PHP Code:
|
|
#12
05-05-2013, 06:23 PM
|
|||
|
|||
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 ?
|
|
#13
05-08-2013, 04:39 PM
|
|||
|
|||
|
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
|
 |
«
Previous Thread
|
Next Thread
»
|
|
All times are GMT. The time now is 04:17 AM.