How to Access a MySQL Database Using PHP
In this tutorial I will show you how to display data from a MySQL database on a web page using PHP. This is part two in our MySQL database tutorial. If you haven’t completed the first part of this tutorial you need to do that first. Part 1 is located here: Create a MySQL Database with cPanel and PhpAdmin. When you finish that tutorial, make sure you populate the database with a few entries.
I will be using Macromedia Dreamweaver (Macromedia is now owned by Adobe) to write the code for this tutorial. You can use any plain text editor (such as notepad). This tutorial will be very basic. I hope to build on it later.
First we will need to establish a connection to the database. A nice thing about using cPanel is that it will give you the connection string. Log into your cPanel Administration panel and go to your MySQL databases.
Notice the Connection Strings. We will be using the PHP connection string. Copy everything but the $dbh= from the PHP connection string and paste it into your PHP file. As you can see below, I have already added the basic HTML to the page. Simply paste the code between the open and close PHP tags.
To explain the string a little bit:
Mysql_connect – is a built in function to connect to a database
“localost” – tells the server to connect to the local database
“projectc_testuse” – this is my username for the database (be sure to replace it with yours)
“
Or die – will return an error if we cannt connect to the database.
Mysql_select_db (”projectc_Test”); – select the database for use
Now that we have a connection to the database, we will need to build our query. We will be selecting everything from the TestTable we created in the previous tutorial. An asterisks (*) is a wildcard character that means all. So our query will look like this (note: I added comments. They will be in orange).
What did we do? We created a variable called query. Then we set the variable to be a MySQL query that will select everything from our TestTable.
Now we need to execute the query and display the results.
Ok. We are creating an array called $row. Using the mysql_fetch_array function we will execute our query and store it to our array so that we can write the data to the page. Echo will write whatever follows to the page.
I will break it down the echo string a little bit here is what we have (check the comments on each line).
That is it. Save this file with a .php extension. Upload the file to your web server and execute it in your browser. The results should look something like this.
That should give you a basic idea of how to execute a query and display the results. Maybe I will do a tutorial that shows you how to style those results using HTML and CSS. Until then, you guys and girls can play around with this code and see what you come up with. Feel free to make suggestions for future tutorials in the comments.
Popularity: 15% [?]
on August 12th, 2007 at 1:39 am
Hi I read the first tutorial for the creation and this one. Only theres one small detail missing for me. I use CPanel11 which dosent seem to include the ‘connection string’ , is there somewhere else I can find this string. The password I inserted for the user is not valid as the normal characters I supplied in the user creation.
on August 12th, 2007 at 6:55 am
Just modify the connection string example I have given. The username and password are the ones that you set up in part one. The connection string will work regardless of the cPanel version (it is a MySQL function).
on September 6th, 2007 at 11:59 pm
This is the error message I got. Any idea what I did wrong? Page is http://www.athomeforhire.com/resumes.php
on September 7th, 2007 at 12:00 am
I forgot to copy/paste the error message:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/athomef/public_html/resumes.php on line 20
on September 7th, 2007 at 4:30 am
Hi, um, im having this error, can somebody please help?
http://dspek.ath.cx/test.php
Parse error: syntax error, unexpected T_VARIABLE in D:\wamp\www\test.php on line 14
on September 8th, 2007 at 4:20 pm
The error codes in PHP are pretty simple to figure out (at least to know where to look). It is telling you that you have a syntax error on line 14 of your code. An unexpected variable is just that, the server didn’t expect a variable where you have one in your code. What this usually means is you have a typo or you forgot or added some punctuation. Check the code one more time for errors.
on September 8th, 2007 at 4:20 pm
I forgot. It should be around line 14, but it could be before it.
on September 8th, 2007 at 5:22 pm
I am trying to publish excel file tables in php so that they can be edited and saved on line. I have converted excel to mysql dump file and then imported it thru phpmyadmin and after this I am lost. How do I save the imported mysql file to php file and set up ADMIN authority to edit databaseand anyuser to read only authority.
My server is linux and supports php and mysql. I could also have jsp if I upgrade and I read about java pages also that can be converted from excel and edited.
Regards
Steve
on October 5th, 2007 at 3:19 pm
Very good article but you could also use Dreamweaver to create the connection string for you.
on February 4th, 2008 at 12:00 am
[...] tutorial is a continuation on the “How to Access a MySQL Database Using PHP” tutorial that showed you how to setup a database using phpMyAdmin and how to read data from the database [...]
on March 6th, 2008 at 8:13 am
Question about the connection string – isn’t it unsafe nowadays to put a username and password for the database right in the code where anyone can view the source? Is there a way to hide it?
on March 6th, 2008 at 9:09 am
PHP is a server side language. Nobody can view the source because by the time it gets to your browser the code has already been processed server. It isn’t like Javascript (which is processed by the client) where you could right click the page and hit view source and see all of the code.
on March 6th, 2008 at 9:52 am
Ahh – thanks for your quick reply. This tutorial was exactly what I needed – if I had one wish though, it would be to have an email confirmation sent after the user filled out the form…. but I”m working on that myself with the php books and the rest of the internet guiding me…..again, thanks so much, your tutorials were very easy to follow and didn’t make me learn more than I need to know about php!
on March 6th, 2008 at 10:15 am
emails are pretty easy with PHP. You would hav to write the logic of course, but this is what I use on one of my sites:
$todayis = date(”l, F j, Y, g:i a”) ;
$attn = $attn ;
$subject = “CSSWV Contact:”.$attn;
$notes = stripcslashes($notes);
$message = ”
$todayis [EST]
Attention: $attn
Additional Info : IP = $ip
Browser Info: $httpagent
From: $visitor ($visitormail)\n
Message: $notes \n
Referral : $httpref \n
Heard About Us: $heardabout\n
Other: $other\n
“;
$from = “From: $visitormail\r\n”;
mail(”youremailhere, secondemailaddressifneeded”, $subject, $message, $from);
Most of the variables are sent from the previous form. We also keep track of any referring sites by capturing it in a cookie. You would probably just need the last last. Basically you would check if the registration was successful and then send the email. I am actually working on user registration for another site.
I have learned been working a lot with PHP lately launching a new site, but I have been too lazy to write any tutorials, lol. I go back to midnight shift at work this week, so I should be able to crank out a few tutorials.
on March 19th, 2008 at 3:34 am
nice tutorial .. dude…
on May 3rd, 2008 at 8:04 am
nice job you have done for new comers in programing.I need help about retrieving a single and selective record from database, actually I’m working for a site in which I post articles I wish all data will display from data base. Is it Possible? How can i do this? what happened when user wish to view previous articles or data. Please guide me
on June 11th, 2008 at 5:10 pm
This (and your previous tutorial) is possibly the best tutorial I’ve ever followed. I am a teacher and value the importance of detail and thoroughness when explaining and documenting a task.
I have been trying to get to grips with PHP for a long time (longer than a year, i tried asp and didn’t like it that much), you have restored my confidence in php and I will continue to look out for any tutorials you create.
Keep them coming
Mark
on June 11th, 2008 at 6:25 pm
@hafiz
To receive a select row it depends on what you are looking for. You would just have to change the query to find what your are looking for. For example, if you wanted to display and article title “PHP for noobs” assuming it was store in a table called article you would: select * from article where title = ‘PHP for noobs’. Now it sounds like youa re tyring to code a content management system (aka CMS). This is obviously going to be more difficult. The way I have done it in the past is to create one php file that has an input variable. You would send the variable for which article you want to display using the link in the previous page. I think clean up the urls using .htaccess. It is a little bit complicated if you have never done it before. There are plenty of free CMS options out there if you wanted to implement them. We use Wordpress on this site but you have other options like: Drupal, Geeklog, Joomla, Mambo Open Source, PHP-Nuke, phpWCMS, phpWebSite, Post-Nuke, Siteframe, TYPO3, Xoops. Of course for some projects it is easier to build your own than to manipulate some of these. Good Luck.
@Mark
.
Thanks for the comment. Mike and I are not PHP experts by any means, but we will try to pass on what little knowledge we have. I hope to create some more PHP/MySQL coding tutorials in the future. I will try to cover the basics and include some more advanced things. It’s hard to find time to write these sometimes
on July 10th, 2008 at 9:11 pm
Thank you very much for the tutorials, it works great. Now I would love to take the next step and be able to search the database and display ID FName LName and PHON by inputting in a search field a name or last name or phone number (just like searching through a phone book).
Thank you for your help.
on July 16th, 2008 at 3:30 am
[...] how to add a simple search box to query a MySQL database. This tutorial is a continuation of the “How to Access a MySQL Database Using PHP” tutorial. Since writing that original tutorial I have created a local WAMP test environment. I have [...]
on August 3rd, 2008 at 4:00 pm
Thank you so much, John. REALLY well done. I’ve been successful on three of your tutorials so far. You mentioned, “Maybe I will do a tutorial that shows you how to style those results using HTML and CSS.” Is this on TeamTutorials anywhere? I’m also trying to figure out how to return image results, but don’t know if this is possible. Thanks again!
on August 3rd, 2008 at 10:52 pm
Will,
No I have not made that tutorial on styling the results yet. Also, to store images in the database is a little more difficult. Some people prefer to store the images in a folder and store the URL in the database. If you want to store it in the database you will probably store it in a BLOB format.
on August 7th, 2008 at 11:19 am
Wonderful article, thanx for putting this up, really appreciate it, keep up the good work…
on August 20th, 2008 at 5:33 am
i go through the first part of this tutorial but could’nt find cPannel.
ijust select phpMyadmin from the menu of the wamp icon and create
the database .But hou to access cPannel……
on August 20th, 2008 at 6:25 am
If you install the wamp server locally you will not have Cpanel. Cpanel is a control panel offer by a large selection of web hosts.
On a local wamp install you can create the databases using phpmyadmin.
on August 31st, 2008 at 5:34 pm
Thanks, John . . . . picking up the project again. I’m trying create site that is similar to an auto classifieds site. (I’m testing a business hypothesis, but need to build this website to do so.) A programmer told me that I need to learn PHP to build a classifieds site, but integrating PHP results with images and formatting PHP results is a little, um, daunting. I’ll see if I can figure out how to store image URLs in the database–I really appreciate hints like that.
Also, thanks for the PHP website templates tutorial. Learned a lot from it.
on October 5th, 2008 at 6:42 pm
My CPanel / MySQL doesn’t show me the ‘connection string’, is there somewhere else I can find this string?
on October 5th, 2008 at 6:43 pm
I am using MySQL client version: 4.1.22 where can I find the string?
on October 5th, 2008 at 10:26 pm
it’s in the tutorial. Change your projectc_testuse to your username and change password to your password
on November 15th, 2008 at 9:51 am
Hi,
Thanks for the tutorial, well written and easy to understand. However, like Tahrah Hunt, im also having the same error message being displayed:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/gbitegr/public_html/webair/test.php on line 17
I’ve checked several times and I can’ find any typos or spelling mistakes.. how can fix it?
Thanks again for the tutorial.
Kamal.
on November 15th, 2008 at 9:58 am
heres my code (I’ve replaced the username and password with *******) <?php
// make connection
mysql_connect (”localhost”, “*******_*******”,”*******”) or die (’I cannot connect to the database because: ‘ . mysql_error());
mysql_select_db (”*******_****”);
// build query
$query = mysql_query(”SELECT * FROM TestTable”);
// display query
while ($row = mysql_fetch_array($query)) {
echo ” ID: ” .$row['ID']. ” First Name: “.$row['FName'].” Last Name: “.$row['LName']. ” Phone: “.$row['PHON'].”";}
?>
on January 8th, 2009 at 5:29 pm
I have a tiny question with this. Let’s say if you have users adding these datas to your database, is there any way to see who sent what?
on January 8th, 2009 at 5:48 pm
What I mean by that is, that use this for getting the user’s info, then making it get the info they just added through a user panel.
on January 30th, 2009 at 1:25 am
John,
This is an excellent tutorial! My only wish is that I had someone that could teach so well when I was beginning PHP . I followed through your tutorial and was amazed at how well you explained everything. The tutorial is really done in a way so that anyone wanting to learn PHP can grasp what you are showing them. Well done..
on January 30th, 2009 at 3:11 am
It shows
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/orkut99/public_html/Test.php on line 14
What can I do…? Plz Help me
on February 20th, 2009 at 2:52 am
[...] How to Access a MySQL Database Using PHP [...]
on March 13th, 2009 at 1:01 pm
[...] you haven’t taken a look at the past tuorials you may want to: How to Access a MySQL Database Using PHP Inserting Data Into a MySQL Database using [...]
on March 19th, 2009 at 5:16 pm
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\PP\insert_staff.php on line 16
Could not insert data: Unknown column ‘E_mail’ in ‘field list’
Would you please assist me ith this.
Thank you