Basics of MySQL - Insane Visions

Basics of MySQL

A new tutorial is up covering and explaining the very basics of MySQL. The article was written by myself a few years ago and after modifying/updating it, here it is. Enjoy!

Basics of MySQL 

https://www.insanevisions.com/tutorials/mysql_basics.phps

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 name=’imageurl’ type=’text’ /><br />
<input name=’siteurl’ type=’text’ /><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.

James

Hi there, I'm James, editor in chief at InsaneVisions.com and finance journalist. Feel free to message me on Twitter and Facebook. Make sure to subscribe to our mailing visit for the latest finance and tech news.
James

Latest posts by James (see all)