Pixel2Life Forum > Understand MYSQL Tutorial :)

Full Version: Understand MYSQL Tutorial :)

Pixel2Life Forum > Member Tutorials and Requests > Forum Tutorial Archives > Miscellaneous Tutorials

. Adam .

Sep 15 2006, 02:05 PM

Hey There,
In this tutorial I am going to try and teach you how to use mysql properly.

First of all, you need to know the basics.

To insert into a mysql database, you use a code similiar to this

CODE

<?

$query = mysql_query("INSERT INTO `users` (`username`, `password`, `email`) VALUES
('$username','$password','$email')");

?>

You can probably see what you need to change etc, But incase you dont. I am going
to explain anyway.

INSERT INTO users - change users to your table name
(username, password, email) - change to the fields in the table, you want to insert into
seperated with a comma and a space
('$username','$password','$email') - Sets what to insert into each field.

Next i will show you how to select from a database

CODE

<?

$select = mysql_query("SELECT * FROM `users`");

?>

Now there is a few things you can do after that.
Here is two of them.

CODE

<?

$select = mysql_query("SELECT * FROM `users`");
$select = mysql_fetch_array($select);

?>

This will select the information from the table you set in the mysql query.

CODE

<?

$select = mysql_query("SELECT * FROM `users`");
$select = mysql_num_rows($select);

?>

The above will count how many rows in the table you set in the mysql query.

Next i am going to show you how you can select certain information from the table.

CODE

<?

$select = mysql_query("SELECT * FROM `users` WHERE `username` = '$username'");
$select = mysql_num_rows($select);

?>

That will select from the table users, how many rows have the username $username.

You obviously have to set the variable $username.

Now im going to show you how to use a while.

CODE

<?

$select = mysql_query("SELECT * FROM `users`");
while($r = mysql_fetch_array($select))
{
echo("$r[username]<br>");
}

?>

That would echo out, All the users in your database, one user on each line.

You can also set persific information in the while query aswell

Like i showed you earlier, You can select where username=adam127 etc.

Now we get a bit more advanced

Next, To update a row in a table, you will need a code similiar to the one below.

CODE

<?

$query = mysql_query("UPDATE `users` SET `username` = '$username' , `password` = '$password
WHERE `id` = '$id' ");

?>

You have to set the variables, but thats not what this tutorial is about

It explains most of this, further up in the tutorial

Now I will show you how to delete from a database.

You will have a code similiar to this one

CODE

<?

$query = mysql_query("DELETE FROM `users` WHERE `username` = '$username'");

?>

same as before, you should know what to change.

Okay, You know how to:
- Insert To Database
- Select From Database
- Update Database
- Delete From Database

Now im going to show you 1 more thing.

Im going to show you how to update all rows in the specified table.
You would use something like this

CODE

<?
$select = mysql_query("SELECT * FROM `users`");
$get = mysql_num_rows($select);

$update = mysql_query("UPDATE `users` SET `level` = '0' LIMIT $get");

?>

That would set every user in the database, level to 0

Now theres one more thing im going to show you, and it comes in very useful sometimes.

or die(mysql_error());

Now you may already know this, but this it for...

if you have an error in your mysql query, it will die ( stop ) the script, and echo the error.

This is how you add it.. below is an example:

CODE

<?
$select = mysql_query("SELECT * FROM `users`") or die(mysql_error());
?>

I hope this tutorial Has helped you understand mysql if you didnt already know it.

Please post C & C Below

- Adam

Dh.

Sep 15 2006, 02:14 PM

Thats a very nice tut - Cheers Adam

Matthew.

Sep 15 2006, 02:40 PM

I nearly cried before i saw the last part about error reporting. Anyway you have a typo here:

CODE

$select = mysql_query("SELECT * FROM `users`") or die(mysql_error));

Should be

CODE

$select = mysql_query("SELECT * FROM `users`") or die(mysql_error());

Just missed a parenthesis. I would have also gone into using field names instead of the asteria to select only certain fields for speed but meh. Not bad for basics but for a mysql tutorial i would have focuses more on syntax than the functions side.

. Adam .

Sep 15 2006, 02:45 PM

Okay,

I edited it, fixing my typo

And I wrote this tutorial a while ago. Ive edited it a bit, and added more since i first wrote it though

- Adam

Trix06

Sep 16 2006, 06:59 AM

Fantastic Adam, we need to get that part of P2L filled. I think you just started it

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.