Step 3 - Retrieving data from the database
________________________________________
PHP MySQL tutorial
Now it's time to make something more interesting work. For example let's try to retrieve data from our table. To do this we first need to open a connection and select the relevant database as before. The next step is to send an SQL query - in this case a Select statement - to the database. Now try to get all records from MySQL. The SQL statement for this is:
SELECT * FROM users;
We need to send this command to the server and store the response. We can do this by using the mysql_query() function in this way:
Code:
1. mysql_connect("localhost", "username", "password") or die(mysql_error());
2. mysql_select_db("test") or die(mysql_error());
3.
4. $result = mysql_query("SELECT * FROM users");
5.
6. echo $result;
7.
PHP F1
Ok, this is nice but how to display data from the $result variable. If you try to print it with echo then you will get an output something like this:
Resource id #3
This is not what we want. To display the selected data correctly you need to do a bit more. There are more functions in PHP which you can use to retrieve data from a MySQL database result set. These are the followings:
• mysql_fetch_assoc() - Fetch a result row as an associative array
• mysql_fetch_row() - Fetch a result row as an enumerated array
• mysql_fetch_array() - Fetch a result row as an associative array, a numeric array, or both
All of them converts one record of the result to an array and later you can use this array as you want. To get the array you need to call one of the above mentioned function. In this case I use the associative version.
Code:
1. $row = mysql_fetch_assoc($result);
2.
3. echo "ID: ".$row['id'].", Name:".$row['name']
4. .", City:".$row['city'].", Age:".$row['age']."
";
PHP F1
However the function returns only with one record, but we should have 5 records in the result set. To list all of the records we need to create a loop. The fetch functions returns with an array if there is a record in the result set and returns false if no more records are available. So creating a while loop is very simple as here:
Code:
1. $result = mysql_query("SELECT * FROM users");
2.
3. while($row = mysql_fetch_assoc($result)){
4. echo "ID: ".$row['id'].", Name:".$row['name']
5. .", City:".$row['city'].", Age:".$row['age']."
";
6. }
7.
PHP F1
As you can see it is not a complicated task. In the next section we will insert data into the database.
________________________________________
PHP MySQL tutorial
Now it's time to make something more interesting work. For example let's try to retrieve data from our table. To do this we first need to open a connection and select the relevant database as before. The next step is to send an SQL query - in this case a Select statement - to the database. Now try to get all records from MySQL. The SQL statement for this is:
SELECT * FROM users;
We need to send this command to the server and store the response. We can do this by using the mysql_query() function in this way:
Code:
1. mysql_connect("localhost", "username", "password") or die(mysql_error());
2. mysql_select_db("test") or die(mysql_error());
3.
4. $result = mysql_query("SELECT * FROM users");
5.
6. echo $result;
7.
PHP F1
Ok, this is nice but how to display data from the $result variable. If you try to print it with echo then you will get an output something like this:
Resource id #3
This is not what we want. To display the selected data correctly you need to do a bit more. There are more functions in PHP which you can use to retrieve data from a MySQL database result set. These are the followings:
• mysql_fetch_assoc() - Fetch a result row as an associative array
• mysql_fetch_row() - Fetch a result row as an enumerated array
• mysql_fetch_array() - Fetch a result row as an associative array, a numeric array, or both
All of them converts one record of the result to an array and later you can use this array as you want. To get the array you need to call one of the above mentioned function. In this case I use the associative version.
Code:
1. $row = mysql_fetch_assoc($result);
2.
3. echo "ID: ".$row['id'].", Name:".$row['name']
4. .", City:".$row['city'].", Age:".$row['age']."
";
PHP F1
However the function returns only with one record, but we should have 5 records in the result set. To list all of the records we need to create a loop. The fetch functions returns with an array if there is a record in the result set and returns false if no more records are available. So creating a while loop is very simple as here:
Code:
1. $result = mysql_query("SELECT * FROM users");
2.
3. while($row = mysql_fetch_assoc($result)){
4. echo "ID: ".$row['id'].", Name:".$row['name']
5. .", City:".$row['city'].", Age:".$row['age']."
";
6. }
7.
PHP F1
As you can see it is not a complicated task. In the next section we will insert data into the database.
Explore more on PHP form creation and handling :
Post a Comment