Jump to content


Photo
- - - - -

mySQL Basics


  • Please log in to reply
No replies to this topic

#1 DanWilliamson

DanWilliamson

    P2L Jedi

  • Members
  • PipPipPip
  • 651 posts

Posted 05 June 2006 - 09:04 PM

In this tutorial I will tell you how to use the basics of mySQL Databases and how to use them with PHP.

What will you learn:

- Connect to the mySQL Database
- Select the mySQL Database
- Query the mySQL Database
- Display the mySQL results

I am guessing you know what the basics are and what a mySQL Database is and how to set the DB up and add tables in PHPmyadmin. If you don't know what the mySQL tables are then here is a quick and brief explanation. Tables are like fields which store DATA in then which you can output, say you have 2 tables with different DATA it will be displayed like this:

Table_1
This is the first table with some random DATA lets say it holds a cool paragraph

Table_2
This is another table which is just the same but named table 2.

So you need to name the fields for example when I create a form I use mySQL and the fields would simply be.

Name:
Url:
E-mail:
Message:

So there is 4 fields and you can add extra ones for IP etc and security implementations, but we won't go into forms on this we are going to learn mySQL not PHP.

mysql_connect

Ok so we need to connect to the Database which we haven't set up yet or perhaps you have if you know how to use PHPmyadmin, anyway the basic connection would have to begin with opening up the PHP tags and defining a variable with the mysql_connect tag.

<?php


// Define mysql_connect inside a variable


$mysqlconnection = mysql_connect( "localhost" , "mysql_username" , "mysql_password" );


// We have no defined the connection


?>

So now we have localhost, mysql_username and mysql_password the localhost will almost always stay the same but you will need to change it to the username and password you have defined in cPanel. I hope you understand this.

mysql_select_db

So now we have connected to the mySQL Database we will need to select it otherwise we won't really be connecting to anything will we so we use mysql_select_db for this and use the table name which you have created. I am going to use the basic table_1 which we used as an example before so we would use this code:

<?php


// Define mysql_connect inside a variable


$mysqlconnection = mysql_connect( "localhost" , "mysql_username" , "mysql_password" );


// We have now defined the connection
// We must now select the Database


mysql_select_db( "table_1" , $mysqlconnection );


// We have now selected table_1 which we haven't set up or we have.


?>

Also what we have done as well is used the variable mysqlconnection, we can also close the mySQL connection. We will do that using our mysql_close and then mysqlconnection.

<?php


// Define mysql_connect inside a variable


$mysqlconnection = mysql_connect( "localhost" , "mysql_username" , "mysql_password" );


// We have now defined the connection
// We must now select the Database


mysql_select_db( "table_1" , $mysqlconnection );


// We have now selected table_1 which we haven't set up or we have.


mysql_close( $mysqlconnection );


// We have now closed the mySQL connection again using the connection $mysqlconnection


?>

I have heavily commented still so it's easily explained and hopefully you are still understanding because we are about to learn how to create really basic tables in mysql using a variable and the custom wrote CREATE TABLE, now this part may get confusing for the newcomers but it is a hell of an advantage when coming to install mySQL when your dealing with newcomers and people that you have built scripts with who have no idea how to handle the Database or use PHPmyadmin, you can set up more advanced tables than mine but I am just going to cover the basics which will hopefully let you escalate on it.

<?php


// Define mysql_connect inside a variable


$mysqlconnection = mysql_connect( "localhost" , "mysql_username" , "mysql_password" );


// We have now defined the connection
// We must now select the Database


mysql_select_db( "table_1" , $mysqlconnection );


// We have now selected table_1 which we haven't set up or we have.
// We now need to create the tables
// Define the variable sql


$sql = CREATE TABLE `table_1` (
	  `Name` VARCHAR( 1 ) NOT NULL ,
	   PRIMARY KEY ( `Name` )
	   ) ENGINE = MYISAM;


// That should work 



mysql_close( $mysqlconnection );


// We have now closed the mySQL connection again using the connection $mysqlconnection


?>

The $sql should work but I just made it up quickly and my mySQL manual creation isn't too good, now we could do something like query the Database in which.

Query

A query is used mainly to insert data into an already existing Database but you can use delete etc here is something really basic where we will create the tables we have already defines in the variable $sql so here is what we add to our code and it's getting quite long now but most of it is comments at the moment, I have tried to explain this the best I can so bear with me.

<?php


// Define mysql_connect inside a variable


$mysqlconnection = mysql_connect( "localhost" , "mysql_username" , "mysql_password" );


// We have now defined the connection
// We must now select the Database


mysql_select_db( "table_1" , $mysqlconnection );


// We have now selected table_1 which we haven't set up or we have.
// We now need to create the tables
// Define the variable sql


$sql = CREATE TABLE `table_1` (
	  `Name` VARCHAR( 1 ) NOT NULL ,
	   PRIMARY KEY ( `Name` )
	   ) ENGINE = MYISAM;


// That should work 
// We need a Query now
// We use mysql_query


mysql_query( $sql );


// We have created table_1
// We don't need to do anything more


mysql_close( $mysqlconnection );


// We have now closed the mySQL connection again using the connection $mysqlconnection


?>

Now how about another basic query as we have the table set up so now we can use the INSERT function to query the mySQL Database and insert some fields and information into the Database.

<?php


// Define mysql_connect inside a variable


$mysqlconnection = mysql_connect( "localhost" , "mysql_username" , "mysql_password" );


// We have now defined the connection
// We must now select the Database


mysql_select_db( "table_1" , $mysqlconnection );


// We now use INSERT and insert DATA into our table
// Define the variable sql


$sql = 'INSERT INTO `table_1` (`ID`, `Name`, `Age`) VALUES (NULL, \'Dan\', \'14\');'; 


// We need a Query now
// We use mysql_query


mysql_query( $sql );


// We have inserted into table_1
// We don't need to do anything more


mysql_close( $mysqlconnection );


// We have now closed the mySQL connection again using the connection $mysqlconnection


?>

I think this will do with this tutorial, I am fairly new to mySQL and have only been working with it on and off for around 4 months so if you spot any errors please contact me




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users