UNDERSTANDING EFICTION SQL STATEMENTS

SQL statements are basically a simple language that controls your database. In most eFiction mods, we usually only worry about three statements.

• SELECT -- just looks up the information, usually to display it at some point.
• INSERT -- adds new information, such as when someone makes a new account.
• UPDATE -- updates information that already exists, such as when they update their profile.

To look something up manually you would start with the command "SELECT."

Next, you have to designate what fields you want. You can specify multiple fields by placing a comma between each item.

Finally, use the "FROM" command to specify what table you want data from, ending with a semi-colon to indicate that the statement is complete.

Example:

Say we want to look up everything in the authors table. We could write all the fields out by hand, but that's too much like work. So… we cheat. ^_^ An asterisk ( * ) tells the database we want the whole thing.

But wait, we're doing this through php rather than by hand. So, we have to create a query in php.

$result stores the data from the SQL statement that asked for all the information.

mysql_query indicates to the script that we're going to use a SQL statement to do something.

We then enclose the SQL statement inside curved brackets and double quotation marks. We then tell the script that that particular section of code is complete with a semi-colon ;.

That's about the easiest MySQL gets. ^_^ However, we're not looking up information in a query; we want to write it into the database. It's the same principle, just slightly more complicated.

If this were just normal SQL that we were doing by hand we could insert a new record by writing: (notice the order: uid, penname, realname, e-mail . . .)

And we'd continue the comma-separated list until we'd filled out the record for my account, and then close it with another ) and then a semicolon ( ; ) just like we did above to look data up.

However, since eFiction is a dynamic script, we have to make certain the values in the script are also dynamic and will add in the data from the form, no matter who is filling it out and when.

Scroll down to line 375 or about, until you see a line that begins:

This is your first SQL statement and is set to add new a new record to the authors table when they register.

In order to prevent an error, you must add your field name in the order it appears in within the database or else you might get an error. (This doesn't always happen, but just when you think it's safe putting it out of order an error will come back and bite you in the butt.) Even worse, the wrong data could be written to the wrong field or even be given to the wrong user.

Read through the words that are inside the ( and ) and compare them to the author's table. Notice those are the same as the names of those fields? And they're more or less in the same order they appear in the database.

Next up is the statements that will take the information from the form fields, take out any extras that the database doesn't need or shouldn't have added to it, and add then add the data to the table. Once again, look closely and you'll see the same names as above, more or less in the same order.

Let's dissect one:

First of all, you have a single quotation mark, then a double, and a period. strip_tags simply removes any HTML tags you don't want the field to contain. Next up is the actions inside the (. First is $_POST, which tells to take the data from the form field marked AOL when they submit the form. Next up is the field name inside two brackets ( [' and '] ). Then a comma, and the $allowed_tags statement, which looks up the tags you allow in your eFiction settings. Then another ), a period, a double quotation mark, single quotation mark, and a comma to set it off from the next item.

To add your own, first remember what field you placed your new field after. Inside the first bunch of statements inside the ( and ), add your's in the appropriate place.

Next, find the corresponding tag and make your new one. Remember that copy and paste is your friend. Just rename the copied code to your new field.

That example was for text/character fields like websites, instant messenger links, biographies, notes, etc. What if you need to add just numbers, like the 0 and 1 yes/no question? Then it becomes even simpler.

Then you just have to enclose it inside two single quotes, then add a $ sign, and your field name. Close it with another single quote and a comma to separate it from the next item. (Remember, we already inserted part of the control at the top of the edit bio function.)

Example:

And you're done with the INSERT statement. If you did it right, your new field will now be included anytime someone registers to your site.

Now, you have to move on to the UPDATE statements.
Like SELECT and INSERT, UPDATE is simple too.

What this just did was search for the person whose UID is 1 and then change (called "set", just like you'd set your clock or wrist watch) their realname to "Sally."

In user.php, there are two of these. One if the password has been changed and one if it hasn't.

The first one begins at approximately line 394:

Examine the statement once again and you'll notice that it's the field names again, except in a different format. Look at the way I updated my realname above. Again, since this is a dynamic environment, we have to pull the data from the form.

Here's one separate from the rest:

See the same format as the manual version? But since we're pulling it from the form, we have to add some stuff. First we have to remove any HTML tags, then we have to add slashes ( \ ) if necessary to protect any possible quotation marks, apostrophes, or accent marks the field may contain. Then we pull it from the form field marked "realname" when they submit the form.

Easy enough?

Just like the INSERT statement, determine the proper place to put your new field, copy, paste, and rename. Again, numerical values can be simplified, since there's nothing but numbers going in anyway.

Format is just like this:

Example:

The next update statement is almost exact; it just begins a little differently (about line 410):

Use the same format as you used for the first update statement.