Subscribe to Tutorial Feed

Flash and PHP Bible

The Flash and PHP Bible has been released! The book can be found on Amazon or wherever fine books are sold in your area.

The Flash and PHP Bible has a forum for quick support.

Scriptplayground » tutorials » php » Fetch MySQL Row

Fetch MySQL Row

Fetch (retrieve) a specific MySQL row and display it in a simple table.

View an Example of this article before you get started.

This week we are going to work with a common use of PHP and MySQL, fetching results from MySQL and displaying them

We will first take a look at the code and then explain it in depth

$sql_link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('db_name', $sql_link);

$book_id = $_GET['bid'];
$query = "SELECT b_name, b_author, b_cat, b_price from lib where id='" . $book_id . "'";

$result = mysql_query($query);

print "<table>";
while($row = mysql_fetch_array($result)) {
    print "<tr><td>" . $row['b_name'] . "</td><td>" 
		. $row['b_author'] . "</td><td>" 
		. $row['b_cat'] . "</td><td>" 
		. $row['b_price'] . "</td></tr>\n";
}
print "</table>";

mysql_close($sql_link);

That is the code, now on to the explanation phase.

$sql_link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('db_name', $sql_link);

The first line is used to connect with a MySQL database and the second simply connects to a database. You would input your specific server connection info, which can be obtained by your admin/host.

$book_id = $_GET['bid'];
$query = "SELECT b_name, b_author, b_cat, b_price from lib where id='" . $book_id . "'";

$_GET is used to pull in the book id

$result = mysql_query($query);

Actually execute the sql and grab are resultset

print "<table>"; // opening of the table

print "output" an opening to the table

while($row = mysql_fetch_array($result)) {

Loop through the results and print them

print "<tr><td>" . $row['b_name'] . "</td><td>" 
	. $row['b_author'] . "</td><td>" 
	. $row['b_cat'] . "</td><td>" 
	. $row['b_price'] . "</td></tr>\n";
}

Display the data in a little "non-formatted" table.</table>

print "</table>";

Close the table

mysql_close($sql_link);

Finally close the mysql connection

That's it, if you have any questions please leave a comment.

Follow Scriptplayground on Twitter (@scriptplay)

Comments: Fetch MySQL Row

 Joe  Sun Aug 6, 2006 10:03 am  
Your closing the table? why "mysql_close($db_conn);"

It won't do anything as, db_conn variable does not exist, if you ran that variable as your connect it would.

Just a little advice to edit, correct me if I'm wrong.
 Matthew  Sun Aug 6, 2006 10:22 am  
That was an oversight on the explanation portion, thanks for pointing it out.
 salm  Tue Nov 28, 2006 7:12 pm  
Lets suppose james thomas wrote more than one book.What i want to show all the books written by him.
What cod will be required to display this information from mysql database on a page?

Please help as i need this urgently.
 Matthew  Tue Nov 28, 2006 7:38 pm  
Hope this is a quick enough response. :)

$query = "SELECT b_name, b_author, b_cat, b_price from lib where b_author='James Thomas'";

The rest of the code would be the same.
 ferensick  Wed Mar 7, 2007 5:38 am  
I use mySQL 5.1.14 with php 5.2.0 on my site.
I have a timestamp in my database that stores comments from my site. All I want to do is change the format of the time that is displaying.

'ALTER TABLE `comments` CHANGE `EST_time` `EST_time` TIMESTAMP(10) ON UPDATE CURRENT_TIMESTAMP NULL';

I run this code successfully, but the format does not change. instead of TIMESTAMP(10) it still shows 12...
Is this a known bug or is there another way to accomplish this?

Thanks in advance.
 mkeefe  Wed Mar 7, 2007 5:55 am  
The safest way to alter a table is to first backup the data. Then you create a new "temp table", assign the new attributes and move the data in. After moving the data make the old table a "temp table" and only delete it after you know the new table is functioning properly.
 ferensick  Wed Mar 7, 2007 6:44 am  
Thank you for the advice.
Any insight on my primary question?

I have been reading on the web about bugs with the mySQL timestamp... I will look more into it and poste the info if relevant.

Cheers.
 Paul Marsden  Tue Nov 13, 2007 11:20 am  
Question. what is the best way to deal with null values? They give me weird effects when displayed in a web page. I have tried printf and echo.
 mkeefe  Wed Nov 14, 2007 7:15 am  
There are a couple of options:

1. Set the values to not null in the database will ensure the values are never null

2. Second option is to run a if statement check to see if the variable is in fact null and if so, set it to a valid value.

if(is_null($value))
{
$value = "";
}
 remi  Mon Mar 2, 2009 7:47 am  
hello when i use WHERE in my MySql query with mysql_fetch_array or mysql_fetch_row i always get error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:uksquad.co.ukwwwroottestuserindex.php on line 13

whats wrong with it? please help
thx in advance
Remi
 mkeefe  Wed Mar 4, 2009 5:00 am  
@remi - That sounds like you may not be creating a proper mysql_query() before you run the mysql_fetch_array().
 Craig  Sat May 9, 2009 9:21 am  
I have an absolutely similar code on my table result set, however I want to display the results (I have around 60-100 results) and on every 10th row, I would like to display a "specific" result, I don't suppose you know the code for that? I can't figure out the where statement part of that.. - wonder if you could help?
 mkeefe  Mon May 11, 2009 6:05 pm  
@Craig - Depending on how the data in your SQL database is structured you may want to create two selects and place the proper data on every 10th line.
 Eric Thomson  Mon Mar 22, 2010 12:57 pm  
I don't understand where the 'bid' variable is coming from. And is 'b_name' etc the field name or is the field name 'name' and you're adding the b_? If so, why? Just trying to figure out why it won't work (Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/byebyew1/public_html/locationstest.php on line 14)

line 14 is the while($row... line
 Abhyuday  Tue Aug 10, 2010 12:57 am  
Sir, How to fetch data from Mysql DB and display on screen, accept modification on that data and then updating the DB accordingly.
 emoghene  Thu Nov 3, 2011 5:48 pm  
i have tried these code, but it keeps printing out just one row and thats the last on the loop? how do is solve that?
 gghgh  Mon Dec 12, 2011 7:24 pm  
How to use paging with serial number in php
Add a comment
The ability to add new comments has been disabled in this archived version.

Loading
Main | Tutorials | Articles