Home
Web Programming
PHP
MySQL Tables
Level: Beginner Software:
PHP Editors
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
More Web Programming: Most Popular Materials | Fresh Materials
| More PHP Tutorials at LearnPHP.org