|
Retrieving data from a table (Single row)
Before we can retrive data from a database table we need a table with data, I will use a jokes table which will contain some jokes for working with the example (I thought why not make it interesting and fun).
 |
If you dont know how to create a database or work with MySQL database you can refer this article for more info. |
|
Jokes Table with sample data
# Table structure for table `jokes`
CREATE TABLE jokes (
joke_no smallint(6) NOT NULL default '0',
joke text NOT NULL,
PRIMARY KEY (joke_no)
) TYPE=MyISAM;
# Data for table `jokes`
INSERT INTO jokes VALUES (1, 'Q:) What is the astronaut\'s favorite place on the computer? \r\n\r\nA:) The space bar. \r\n');
INSERT INTO jokes VALUES (2, 'Salesman: This computer will cut your workload by 50%. \r\n\r\nCustomer: That\'s great. I\'ll take two of them! \r\n');
INSERT INTO jokes VALUES (3, 'Q:) What do you get when you cross a computer with an alligator? \r\nA:) A megabite. \r\n');
The above is the dump of table jokes with 3 jokes in it.
Performing SQL Query
In order to retrive data from the table we will have to execute a SQL Query for that we use the mysql_query function the syntax of which is
mysql_query(, );
For example if we were to write a query to retrive joke no 1 from our jokes table we would write the code as
$sql_result = mysql_query("SELECT joke FROM jokes WHERE joke_no=1", $db);
Remember: We had created our database connection and stored it in $db variable, thus I have replaced the connection id with $db, I have also stored the value of mysql_query (resource id) in a variable $sql_result
The above query did not give us the actual data but a resource id, to get the actual data we use the function mysql_fetch_row in case of a single row and mysql_fetch_array in case of multiple rows.
So to retrive the data I use the following code (we have just one row)
$rs = mysql_fetch_row($sql_result);
echo $rs[0];
The above code displays the joke, mysql_fetch_row outputs the data (joke) in an array we have stored that in a variable $rs and in the next line we use the echo construct to display the value in our first array $rs[0] as joke is the first element in our array.
To summarize, here's the complete code of a PHP Web page that will connect to our database, fetch the joke (joke number 1) from the jokes tables in the database
<?php
$db = mysql_connect("localhost","root","pass");
mysql_select_db("mybuddy",$db);
//replace the above values with your actual database values
$sql_result = mysql_query("SELECT joke FROM jokes WHERE joke_no=1", $db);
$rs = mysql_fetch_row($sql_result);
echo $rs[0];
?>
Next: Retrieving multiple rows for data from a table
Home | Privacy Policy | Contact Us | Terms of Service
(c) 2002 - 2019 www.PHPbuddy.com Unauthorized reproduction/replication of any part of this site is prohibited.
|
|
