Insane Visions - Basics of MySQL Tutorial :: Premium PHP Scripts - AdaptCMS, AdaptBB
Basics of MySQL at Nov 04, 07 - 3:03 pm
         Views: 8,349
Type: MySQL
Experience Level: Beginner
MySQL is a very powerful language. Coupled with PHP you can do just about anything you want. This tutorial here will cover "INSERT", "SELECT", "DELETE", "UPDATE" and "RAND".
MySQL RAND()
Probably the least-used aspect of MySQL, rand basically stands for "Random". This is fantastic for rotating signatures, avatars, affiliate programs and more. Here's an example use of RAND:
<?php
$ sql = mysql_query("SELECT * FROM signatures
ORDER BY RAND() LIMIT 1");
?>
In the above example I am using rand to pull a random signature out of the table. In the below example I will add a fetch to put it in action:
<?php
$ sql2 = mysql_fetch_row(mysql_query("SELECT imageurl FROM
signatures ORDER BY RAND() LIMIT 1"));
echo "<img src='".$ sql2[0]."'>";
?>
With the above code it will display the data. "imageurl" is the name of the field, from the table "signatures". If you want to fetch more than one field simply seperate with a comma. (ex. id,imageurl,siteurl) Just in case you want to try it out yourself, use the following MySQL Database Info:
id (primary, index, auto_increment)
imageurl (text, not null) - this is where the url of the image would go
siteurl (text, not null) - this is optional, only if you want the image to link to something
MySQL INSERT
This is one of the most important MySQL functions. This allows you to add data into a table. Now since I started with the whole "Rotating Signature" thing, I might as well continue with it for the rest of the tutorial. First lets make a form:
<?php
echo "<form method='post' action='signatures.php'>
<input type='text' name='imageurl'><br>
<input type='text' name='siteurl'><br><br>
<input type='submit' value='Submit'></form>";
?>
Just a basic form consisting of fields for the "URL of the image" and (optional) the url to whatever page you set. So now with that out of the way it's time for the form to add the data:
<?php
mysql_query("INSERT INTO signatures VALUES (null,
'".strip_tags(addslashes($ _POST[imageurl]))."',
'".strip_tags(addslashes($ _POST[siteurl]))."')");
?>
Now let's break that down. First of all, the "null" is for the id field. Basically if you set it to nothing it will add 1 to the current id (say you have 5 signatures, the current would be 6 with the 1 added) so we just put in null, which is the same as "nothing". Next we have the posts tag for image url and site url, note that both are surrounded with a single quote around them and also the 3 fields are enclosed by paranthesis. If you forget one or both of these, plan on seeing a nasty MySQL error.
MySQL DELETE
This along with UPDATE and INSERT are almost always musts with scripts, especially one's that are like control panels that allow you to totally manage everything. Here is an example of delete, which is pretty easy and simple.
<?php
mysql_query("DELETE FROM signatures WHERE id = '1'");
?>
First of all you don't have to delete from the id. If you want to delete by the imageurl or siteurl, either will work. But remember when doing this, if you delete data it's gone forever. It's not like throwing trash away only to come back later and get it out if you want to, this isn't backed up anywhere. (unless you backup the SQL beforehand)
MySQL UPDATE
Probably the closest to the DELETE function is UPDATE. Here is an example below:
<?php
mysql_query("UPDATE signatures SET
imageurl = 'http://www.yourmom.com/image.jpg'
WHERE id = '1'");
?>
Now this is more confusing than delete, so let me explain. The WHERE part applies the same as delete where you don't have to (in this case update) by id. However after UPDATE "tablename" (in this case signatures) you then have SET and after that you can update multiple fields in one query like the below:
<?php
mysql_query("UPDATE signatures SET imageurl = 'teehee.jpg',
url = '".strip_tags(addslashes($ _POST[siteurl]))."'
WHERE id = '1'");
?>
Simply put the field name = 'data' and then if there is more than one fields that your updating, put a comma afterwards.
Conclusion
Well, that just about wraps up my basic tutorial on MySQL. MySQL combined with PHP is a very powerful tool. PHP on it's own is great, but MySQL expands the possibilities of PHP scripts and projects to near no limits. In the downloadable file below, you will find all this code inside it so you can try it out for yourself.
Download: 
Rating:  
| 
