Welcome To My Tutorial

After reading this, you should hopefully know how to use Mysql properly with PHP.

Firstly, Some basics of php that you need to know so you understand the rest of the tutorial.

[code=TEXT]
<?php

?>
[/code]

<?php - Starts PHP

?> - Ends PHP

Now lets move on to the Mysql part.



Mysql Query

This part of the tutorial is based on the PHP function Mysql Query .

Here is an example, of mysql_query

[code=TEXT]
<?php

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

?> [/code]

$select = - Definining the $select variable. This can be anything, aslong as it starts with $

mysql_query - Start the mysql_query function

SELECT - This is what we want to do. In this case, it is only an example so I have used SELECT. You can use other things such as INSERT or DELETE or UPDATE

* - means select ALL fields in that table. You can define certain fields to select like this:


[code=TEXT]
<?php

$select = mysql_query("SELECT `username`,`password` FROM `users`") or die(mysql_error());

?> [/code]

That would select username and password from users.

or die(mysql_error());

Now, are you wondering what that means? Simple. If the query has an error, it will die the script and echo the Mysql error. Great for debugging.


mysql_query Functions

There are many functions.. such as:

  
• SELECT
  
• INSERT INTO
  
• DELETE FROM
  
• UPDATE

Now I will show you how to use each of those.



SELECT

This is for selecting from the database. If you was using this, you would use a code something like this:

[code=TEXT]
<?php

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

?> [/code]

That would:

Select all fields from the table users. if there was an error, it would die the script and echo the mysql error.

Now all that code above, I have explained on previous pages.




INSERT

You would use this, if you wanted to insert into a database. You would use it something like this:

[code=TEXT]

<?

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

?>
 [/code]

Again, most of that code has been explained before.. some bits havnt...

(`username`, `password`, `email`) - The fields you want to insert into

VALUES - Tell Mysql, we are now definining the values to insert.

('$username','$password','$email') - The values to insert.





DELETE

Now, you would use this for deleting a record from a database. Example code:


[code=TEXT]<?

$query = mysql_query("DELETE FROM `users` WHERE `username` = 'Adam'") or die(mysql_error());

?>[/code]

DELETE FROM `users` - Define what table to delete from

WHERE `username` = 'Adam' - Where the username field = Adam

So that would delete all records from users where the username is Adam.




UPDATE

[code=TEXT]<?

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

?>[/code]

Pretty self explanatory really.

UPDATE `users` - The table to update

SET - Start to define the new values

`username` = 'John' - That would set username as John

You define the fields you want to update, like the line above. Seperate each one with a comma.

So thats that. Thats the basics of mysql_query covered.



Now lets get more advanced!

Ordering your results

You can specify the order that the results are pulled from the database.

Lets say.. If you wanted to Order by ID in ascending order...

[code=TEXT]
<?php

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

?> [/code]

We just add, ORDER BY `id` ASC to our mysql query.

If you wanted to order descending... You can guess what you do, just incase you cant..

[code=TEXT]
<?php

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

?> [/code]

Just changing ASC to DESC

ASC = Ascending
DESC = Descending




Limiting your results

Do you want to limit your results, i.e Pull 5 records from the database, not all of them? Mysql can do that!

You just add:

[code=TEXT]LIMIT 5[/code]

Changing 5 to the number of results you want to get.

Now lets add that into our query...

[code=TEXT]
<?php

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

?> [/code]

That would pull the first 5 results it finds.

We can get a bit more advanced now, and specify the order and limit the results..

[code=TEXT]
<?php

$select = mysql_query("SELECT * FROM `users` ORDER BY `id`DESC  LIMIT 5") or die(mysql_error());

?> [/code]

So try and work out what that would do, in your head....


...

..

Answer: It would pull the 5 records that have the highest ID number. So if it was a users table, it would probably pull the 5 latest members. Can come in very useful!


Shall we get even more advanced? yh! lets do it...

[code=TEXT]
<?php

$select = mysql_query("SELECT * FROM `users` WHERE `active_account` = 'yes' ORDER BY `id`DESC  LIMIT 5") or die(mysql_error());

?> [/code]

Now that would get the first 5 records, ordered by ID desc where the field active_account = yes.

Hopefully you understand that!



Now we have covered mysql_query, lets move on...

mysql_fetch_array

yes, this is refering to the sql function mysql_fetch_array

Now this is useful for fetching data from a database. Example:

[code=TEXT]
<?php

$select = mysql_query("SELECT * FROM `users` WHERE `active_account` = 'yes' ORDER BY `id`DESC  LIMIT 5") or die(mysql_error());

$user = mysql_fetch_array($select);

?> [/code]

This would fetch all the data, matching your query. To echo 5 results, you would need to use a while loop. This is where the PHP comes in...

[code=TEXT]
<?php

$select = mysql_query("SELECT * FROM `users` WHERE `active_account` = 'yes' ORDER BY `id`DESC  LIMIT 5") or die(mysql_error());

while($user = mysql_fetch_array($select))
{
echo $user['username'] . '<br />';
}

?> [/code]

Now that would echo the username, for every record limit 3. If there was only 2 records, it would only show 2.

Thats very simple PHP.

<br /> Means new line

Thats very easy to understand.


mysql_num_rows

This refers to the function mysq_num_rows

This is for counting the amount of rows matching your query.

Usage:

[code=TEXT]
<?php

$select = mysql_query("SELECT * FROM `users` WHERE `active_account` = 'yes' ORDER BY `id`DESC") or die(mysql_error());

$count = mysql_num_rows($select);

?> [/code]

Thats also very easy to understand. Thats the SQL function we created earlier with a new line. The new line being the mysql_num_rows




mysql_connect

Now im going to tell you about mysql_connect

This is for connecting to a database. Much needed on a mysql powered website! lol

Heres an example:

[code=TEXT]

<?php

$connect = mysql_connect('localhost','database_username','database_password') or die(mysql_error());

?>

[/code]

The connect function needs 3 variables in this order:

Host, database username, database password


Then we use the or die(mysql_error()); as I explained before what that means :D

That would then connect to the database, if not echo the error.

Now we need to actually select the database.


This is were mysql_select_db comes in useful

You need to connect using mysql_connect then you select using mysql_select_db

[code=TEXT]

mysql_select_db('database_name') or die(mysql_error());

[/code]

Simple as that!

Full connect & select code:

[code=TEXT]

<?php

$connect = mysql_connect('localhost','database_username','database_password') or die(mysql_error());

mysql_select_db('database_name') or die(mysql_error());

?>

[/code]

Nice huh.

Have explained all that before.




Summary

Using mysql with PHP is easy isnt it?

Hopefully you have learnt something whilst reading this tutorial.


Thanks for reading my tutorial!