MY SQL-CREATING TABLE,SELECT DATA FROM A TABLE


'mysql_query ("create table example')

The first part of the mysql_query told MySQL that we wanted to create a new table. The two capitalized words are reserved MySQL keywords.
The word "example" is the name of our table, as it came directly after "CREATE TABLE". It is a good idea to use descriptive names when creating a table, such as: employee_information, contacts, or customer_orders. Clear names will ensure that you will know what the table is about when revisiting it a year after you make it.


'id int not null auto_increment'
Here we create a column "id" that will automatically increment each time a new entry is added to the table. This will result in the first row in the table having an id = 1, the second row id = 2, the third row id = 3, and so on.
The column "id" is not something that we need to worry about after we create this table, as it is all automatically calculated within MySQL.
Reserved MySQL Keywords: 
Here are a few quick definitions of the reserved words used in this line of code:
  • INT - This stands for integer or whole number. 'id' has been defined to be an integer.
  • NOT NULL - These are actually two keywords, but they combine together to say that this column cannot be null. An entry is NOT NULL only if it has some value, while something with no value is NULL.
  • AUTO_INCREMENT - Each time a new entry is added the value will be incremented by 1.
'primary key (id)'
PRIMARY KEY is used as a unique identifier for the rows. Here we have made "id" the PRIMARY KEY for this table. This means that no two ids can be the same, or else we will run into trouble. This is why we made "id" an auto-incrementing counter in the previous line of code.
'name varchar(30),'
Here we make a new column with the name "name"! VARCHAR stands for "variable character". "Character" means that you can put in any kind of typed information in this column (letters, numbers, symbols, etc). It's "variable" because it can adjust its size to store as little as 0 characters and up to a specified maximum number of characters.
We will most likely only be using this name column to store characters (A-Z, a-z). The number inside the parentheses sets the maximum number of characters. In this case, the max is 30.
'age int,'
Our third and final column is age, which stores an integer. Notice that there are no parentheses following "INT". MySQL already knows what to do with an integer. The possible integer values that can be stored in an "INT" are -2,147,483,648 to 2,147,483,647, which is more than enough to store someone's age!
'or die(mysql_error());'
This will print out an error if there is a problem in the table creation process.
mysql insert
When data is put into a MySQL table it is referred to as inserting data. When inserting data it is important to remember the exact names and types of the table's columns. If you try to place a 500 word essay into a column that only accepts integers of size three, you will end up with a nasty error!

inserting data into your table

Now that you have created your table, let's put some data into that puppy! Here is the PHP/MySQL code for inserting data into the "example" table we created in the previous lesson.

PHP & MySQL Code:

// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
 
// Insert a row of information into the table "example"
mysql_query("INSERT INTO example 
(name, age) VALUES('Timmy Mellowman', '23' ) ") 
or die(mysql_error());  
 
mysql_query("INSERT INTO example 
(name, age) VALUES('Sandy Smith', '21' ) ") 
or die(mysql_error());  
 
mysql_query("INSERT INTO example 
(name, age) VALUES('Bobby Wallace', '15' ) ") 
or die(mysql_error());  
 
echo "Data Inserted!";
 
?>
This code is much simpler to understand than the create table code, as will be most of the MySQL queries you will learn in the rest of this tutorial. Once again, we will cover the code line by line.
'mysql_query("insert into example'
Again we are using the mysql_query function. "INSERT INTO" means that data is going to be put into a table. The name of the table we specified to insert data into was "example".
'(name, age) values('timmy mellowman', '23' ) ")'
"(name, age)" are the two columns we want to add data into. "VALUES" means that what follows is the data to be put into the columns that we just specified. Here we enter the name Timmy Mellowman for "name", and 23 for "age".
Be sure to note the location and number of apostrophes and parentheses in the PHP code, as this is where a lot of beginner PHP/MySQL programmers run into problems.
mysql query
So far we have seen a couple different uses of PHP's mysql_query function and we'll be seeing more of it as nearly all MySQL in PHP is done through the MySQL Query function. We have already created a new table and inserted data into that table. In this lesson we will cover the most common MySQL Query that is used to retrieve information from a database.

retrieving data with php & mysql

Usually most of the work done with MySQL involves pulling down data from a MySQL database. In MySQL, data is retrieved with the "SELECT" keyword. Think of SELECT as working the same way as it does on your computer. If you wanted to copy some information in a document, you would first select the desired information, then copy and paste.

using mysql select & from

Before attempting this lesson, be sure that you have created a table that contains some data, preferably the same data that we had in the MySQL Insert lesson. In this example, we will output the first entry of our MySQL "examples" table to the web browser.

PHP & MySQL Code:

// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
 
// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM example")
or die(mysql_error());  
 
// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry 
 
echo "Name: ".$row['name'];
echo " Age: ".$row['age'];
 
?>
This is an example of how to use MySQL's SELECT statement in PHP. Although the MySQL code is simple, printing out the information with PHP is somewhat more involved.
Below is a step-by-step walkthrough of the code.
'$result = mysql_query("select * from example")'
When you perform a SELECT query on the database it will return a MySQL Resource that holds everything from your MySQL table, "example". We want to use this Resource in our PHP code, so we need to store it in a variable, $result.
'select * from example'
Yes, this is a partial repeat of the same line of code, but we wanted to explain this MySQL statement in greater detail again!
In English, this line of code reads "Select every entry from the table example". The asterisk is the wild card in MySQL which just tells MySQL to include every single column for that table.
'$row = mysql_fetch_array( $result );'
$result is now a MySQL Resource containing data from your MySQL table, "example". Data is being stored in $result, but it is not yet visible to visitors of your website. When we pass $result into the mysql_fetch_array function -- mysql_fetch_array($result) -- an associative array (name, age) is returned.
In our MySQL table "example," there are only two fields that we care about: name and age. These names are the keys to extracting the data from our associative array. To get the name we use $row['name'] and to get the age we use $row['age'].
PHP is case sensitive when you reference MySQL column names, so be sure to use capitalization in your PHP code that matches the MySQL column names!
mysql fetch array
MySQL doesn't have a Fetch Array function. mysql_fetch_array is actually a PHP function that allows you to access data stored in the result returned from a successful mysql_query. If you have been jumping around our MySQL Tutorial then you would have already seen this function popping up all over the place.

mysql_fetch_array: why use it?

Do you know what is returned when you used the mysql_query function to query a MySQL database? It isn't something you can directly manipulate, that is for sure. Here is a sample SELECT query of a table we created in the MySQL Create Table lesson.

PHP and MySQL Code:

$result = mysql_query("SELECT * FROM example");
?>
The value that mysql_query returns and stores into $result is a special type of data, it is a MySQL Resource. Additional PHP functions are required to extract the data from this Resource.
a row of data
The mysql_fetch_array function takes a MySQL query resource as an argument ($result) and returns the first row of data returned by the mysql_query. Our table example basically looks like the table below.
The first row of data in this table is "Timmy Mellowman" and "23". When we fetch an array from our MySQL Resource $result it should have Timmy's name and age in it.

getting a row of data using mysql_fetch_array

mysql_fetch_array returns the first row in a MySQL Resource in the form of anassociative array. The columns of the MySQL Result can be accessed by using the column names of the table. In our table example these are: name and age. Here is the code to print out the first MySQL Result row.

PHP and MySQL Code:

// Make a MySQL Connection
$query = "SELECT * FROM example"; 
                     
$result = mysql_query($query) or die(mysql_error());
 
 
$row = mysql_fetch_array($result) or die(mysql_error());
echo $row['name']. " - ". $row['age'];
?>
This is just what we expected would happen! Now, the cool thing aboutmysql_fetch_array is that you can use it again on the same MySQL Resource to return the second, third, fourth and so on rows. You can keep doing this until the MySQL Resource has reached the end (which would be three times in our example).
Sounds like an awfully repetitive task. It would be nice if we could get all our results from a MySQL Resource in an easy to do script.

fetch array while loop

As we have said, the mysql_fetch_array function returns an associative array, but it also returns FALSE if there are no more rows to return! Using a PHP While Loop we can use this information to our advantage.
If we place the statement "$row = mysql_fetch_array()" as our while loop's conditional statement we will accomplish two things:
1.      We will get a new row of MySQL information that we can print out each time the while loop checks its conditional statement.
2.      When there are no more rows the function will return FALSE causing the while loop to stop!
Now that we know what we need to do and how to go about doing it, the code pretty much writes itself, so let's move on to the next lesson. Just kidding! Here is the code that will print out all the rows of our MySQL Resource.

PHP and MySQL Code:

// Make a MySQL Connection
$query = "SELECT * FROM example"; 
                     
$result = mysql_query($query) or die(mysql_error());
 
 
while($row = mysql_fetch_array($result)){
                    echo $row['name']. " - ". $row['age'];
                    echo "
";
}
?>
mysql select
You have seen two types of MySQL queries thus far: the query which we used to create a table and the query we used to insert data into our newly created table. The query in this lesson is SELECT, which is used to get information from the database, so that its data can be used in our PHP script.

retrieving information from mysql

Finally, we get to use the data in our MySQL database to create a dynamic PHP page. In this example we will select everything in our table "example" and put it into a nicely formatted HTML table. Remember, if you don't understand the HTML or PHP code, be sure to check out the HTML and/or PHP Tutorial(s).

PHP & MySQL Code:

// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
 
// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM example") 
or die(mysql_error());  
 
echo "";


echo "
 Name Age 
";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
                    // Print out the contents of each row into a table
                    echo "
"; 

                    echo $row['name'];
                    echo "
"; 
                    echo $row['age'];
                    echo "
"; 
} 
 
echo "
";
?>
'$result = mysql_query...'
When you select items from a database using mysql_query, the data is returned as a MySQL result. Since we want to use this data in our table we need to store it in a variable. $result now holds the result from our mysql_query.
'select * from example'
In English, this line of code reads "Select everything from the table example". The asterisk is the wild card in MySQL which just tells MySQL to retrieve every single field from the table.
'while($row = mysql_fetch_array( $result )'
The mysql_fetch_array function gets the next-in-line associative array from a MySQL result. By putting it in a while loop it will continue to fetch the next array until there is no next array to fetch. This function can be called as many times as you want, but it will return FALSE when the last associative array has already been returned.
By placing this function within the conditional statement of the while loop, we can kill two birds with one stones.
1.      We can retrieve the next associative array from our MySQL Resource, $result, so that we can print out the name and age of that person.
2.      We can tell the while loop to stop printingn out information when the MySQL Resource has returned the last array, as False is returned when it reaches the end and this will cause the while loop to halt.
In our MySQL table "example" there are only two fields that we care about: name and age. These fields are the keys to extracting the data from our associative array. To get the name we use $row['name'] and to get the age we use $row['age'].
mysql where
In a previous lesson we did a SELECT query to get all the data from our "example" table. If we wanted to select only certain entries of our table, then we would use the keyword WHERE.

being selective with your mysql selection

There are three entries in our "example" table: Tim, Sandy, and Bobby. To select Sandy only we could either specify Sandy's age (21) or we could use her name (Sandy Smith). In the future there may be other people

Comments

Post a Comment

Thank you for your Comment