Posted on November 22nd, 2006
8533 views
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. I reccomend that you use or die(mysql_error()); on all mysql_query 's mysql_query Functions There are many functions.. such as:
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. Always make sure that, you have the same amount of values as the amount of fields you want to insert into. Also make sure they are in order, or it will insert the password into the username field etc 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 Host is usually localhost 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. If you need help, please post a topic in the help forum and someone will gladly assist you! Thanks for reading my tutorial! |