MySQL Tables

MySQL Tables To create a database you will need to think of the following:

What is the table going to be called
The fields it will have

For example if you will make a click counter it would be :

CREATE TABLE clickcounter (
id int(10) NOT NULL auto_increment,
url text,
clicks int(11) default NULL,
lastclickerip text,
time text,
PRIMARY KEY (id)
) TYPE=MyISAM;

Now the only thing you might not know about that is whats up with the clicks field, here's the explaination:

When you are going to select the clicks from the table you would use something like this:

SELECT * FROM clicks ORDER by clicks DESC

Now in order to add that table to your mysql database you will either need to paste that code into your phpmyadmin query window OR use the following php code.

$result = mysql_query("CREATE TABLE clickcounter (
id int(10) NOT NULL auto_increment,
url text,
clicks int(11) default NULL,
lastclickerip text,
time text,
PRIMARY KEY (id)
) TYPE=MyISAM");

if ($result) {
echo 'The Table has been install successfully';
} else {
echo 'And Error Has Occured While Trying To Create the table'; }

Inserting Into Mysql

You would have needed to make your table as shown above and also be conntected to mysql on the page your using to insert data.

The code to insert mysql looks like this.

$query = "INSERT INTO table (field1, field2)
VALUES ('value1','value2')";
mysql_query($query);

If you wanted to know if the query worked you could use something like this:

$query = "INSERT INTO table
(field1, field2)
VALUES ('value1','value2')";
$result = mysql_query($query);
if ($result) {
echo 'The Query Worked';
}
else {
echo 'The Query Failed';
}

If you have more fields in the table you can edit the query to suit the table, you should now know how the code works and you'll see that for every field you just add a , and the fieldname.

Now if you were wanted to know the id of the row you just inserted there is a variable you can use.

Simple use something like this

$newid = mysql_insert_id();

If you had a forum or something you could just redirect the person to ?id=forumthread&thread=$newid or whatever you would like to do.

Updating Inserted Rows

Ok so if you wanted to update the data you would need this code:

$query = "UPDATE 'table' SET 'field1' = 'value1', 'field2' = 'value2' WHERE 'id'
= '$id'";
mysql_query($query);

Now for that you also could have found out if the query worked by using this:

$query = "UPDATE
'table' SET 'field1' = 'value1', 'field2' = 'value2' WHERE 'id' = '$id'";
$result = mysql_query($query);
if ($result) {
echo 'The Query Worked';
}
else {
echo 'The Query Failed';
}

Now see that we used where id=$id, you will need to set whitch row to select there by using something like ?id=1 in the url then the row with the id 1 would have been edited

There isnt much to this so it shoulnt be to hard

Deleting Rows From Mysql

Ok like before this is a very easy command and once you get the hang of it it will become very easy.

To delete a row:

$query = "DELETE FROM table WHERE id='$id'";
$result = mysql_query($query);

if ($result) {
echo 'The Row was deleted';
} else {
echo 'The Row was Not Deleted';
}

Counting Rows

Ok if you wanted to count how many members you have or something with a stats script you would need something like this:

$total = mysql_result(mysql_query("SELECT COUNT(id) FROM users WHERE activated='1'"),0);

Note you can remove the WHERE activated='1' bit if you want to count EVERYthing on a table for example:

$total = mysql_result(mysql_query("SELECT COUNT(id) FROM unique_hits"),0);

Now you can also use this for some error checking when selecting data like this:

$total = mysql_result(mysql_query("SELECT COUNT(id) FROM comments"),0);
if ($total > 0) {
echo 'There are comments, do your other commands here';
}
else {
echo 'All the comments were deleted';
}

Selecting Data

Ok if you wanted to select stuff like your members you would use something like this:

$result = mysql_query("SELECT * FROM users WHERE accepted='1' order by username");
while($r = mysql_fetch_array($result))
{
echo "$r[username]";
}

This will select all of your members:

To select different fields just use this:

echo "$r[field]";

Using WHERE, LIMIT and order By Ok,

When you wanted to selected your data your query would look something like this:

SELECT * FROM table

Now to spice it up and limit the number of results to say 50 use this

SELECT * FROM table LIMIT 50

Or if you wanted to order it DESCENDING by id you would use this

SELECT * FROM table order by id DESC

To switch between DESCENDING and ASCENDING you need to use DESC or ASC

To select only certain value you need to use WHERE like this

SELECT * FROM table WHERE field='value'

Now the query would only select the rows where the fields value is "value"

You can also do something like this:

SELECT * FROM table WHERE field='value' OR
field='value2'

This will select the rows where the values are value or value2

MySQL Tables
Create, insert, update, delete, count and select rows in a database.
Acquiring PHP MySQL
PHP and MySQL are already packaged in Linux and Mac OS X OSs. However, most PHP developer are actually using Windows when developing PHP applications. This is also true to myself. I have Mandrake Linux 10 but Ill just use it for testing purposes of my finished scripts...
Alternating Rows: Mysql
The first thing we need to do is connect to the database. I always put my db connection in a universal file, that way I can just change the variables once if I need to; but for the sake of the tutorial, im putting it in this file.
PHP Navigation and Connecting to a MySQL Database
PHP Navigation and Connecting to a MySQL Database
Alternating Row Colors
Theres two different ways this can be done. The first is by getting results from a mysql query, the second is going through the values of an array. Ill go over both. First the full files (ill break it up into two files, one using mysql, and one using an array)
Beginner Tables
Tables are an essential piece for creating a professional layout. HavenFX uses many tables to organize and display data, even though the tables probably are not visible. Here you will learn the basic tags used in creating tables.
The Background-Table Combo
Ever wondered how some websites seem to have multiple layers of designs? Although some use frames and other design techniques to accomplish this look, Im going to show you a simple way to create the look without the headaches of complicated design techniques. Youll be glad to hear...
Top 3 Free Web Hosting Services
Nowadays, there are many free webhosting services available on the Internet. Sometimes it can be difficult to choose which one is best for you. First of all, you need to bear in mind free wehosting usually have some limitations such as not allowed email accounts, no mysql databases, no perl etc.
Why Your Site Should be Developed with CSS and Semantic Markup
One thing that I have learned in over a decade developing web sites is that the Net is continually changing, and to keep up you need to change with it.
How To Top The Medal Tables With Your Website
How To Top The Medal Tables With Your Website The Olympic motto is Citius, Altius, Fortius meaning Swifter, Higher, Stronger so lets talk about how you can improve your website in each of these ways...