Pages Navigation Menu
 

How to Access a MySQL Database Using PHP

Beautiful WordPress Themes

You should not use this code on a production website.

Warning: This tutorial uses old techniques. It is insecure and will leave your server vulnerable to SQL Injection attacks.This tutorials also uses mysql_ functions that are no longer support. For updated tutorials look for a PDO or MySQLi tutorial.This post will be delete or revised in the future.

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.
How to Access MySQL Databases using PHP

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.
How to Access MySQL Databases using PHP

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)
” – this is where you need to type in the password for your database user
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).

How to Access MySQL Databases using PHP

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.

How to Access MySQL Databases using PHP

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).
How to Access MySQL Databases using PHP

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.
How to Access MySQL Databases using PHP

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.

69 Comments

  1. 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.

  2. 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).

    • i need multiple list not password and usernname more than fields…

  3. This is the error message I got. Any idea what I did wrong? Page is http://www.athomeforhire.com/resumes.php

  4. 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

  5. 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

  6. 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.

  7. I forgot. It should be around line 14, but it could be before it.

  8. 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

  9. Very good article but you could also use Dreamweaver to create the connection string for you.

  10. 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?

  11. 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.

  12. 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!

  13. 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.

  14. nice tutorial .. dude…

  15. 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

  16. 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

  17. @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 :) .

  18. 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.

  19. 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!

  20. 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.

  21. Wonderful article, thanx for putting this up, really appreciate it, keep up the good work…

  22. 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……

  23. 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.

  24. 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.

  25. My CPanel / MySQL doesn’t show me the ‘connection string’, is there somewhere else I can find this string?

  26. I am using MySQL client version: 4.1.22 where can I find the string?

  27. it’s in the tutorial. Change your projectc_testuse to your username and change password to your password

  28. 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.

  29. 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'].””;}

    ?>

  30. 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?

  31. 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.

  32. 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..

  33. 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

  34. 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

  35. Hey, thanks for a great tutorial, John… very easy to follow, and works great.

    One suggestion, though… it’d be helpful if, rather than posting snapshots of your code, you posted something which we could copy and then paste in our codes. Or maybe add a section at the end where we can download your entire code.

    But great work… thanks again :).

  36. Hello every one,

    I am having problems with Mysql and PHP,

    I have installed WAMP and cann’t access the mysql database even after granting all the privileges.

    Help needed plz…

  37. Agree with the above poster regarding copy and paste codes. It coukd make it easier for a goof like me.

  38. thanks a lot.
    explanation was really helpful

  39. thank the heavens for teamtutorials.com!

  40. Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in…

    Make sure you have

    mysql_select_db($DB) or die( “Unable to select database”);

    don’t forget that bit of code in the make connection section. If you forget it you will keep getting the error code. Don’t forget to replace $DB with your variable or database name

  41. Hi,

    It was really helpful to me. Thank you very much for giving a clear guidance to me.

    Best Regards

  42. Is there a tutorial for displaying the results with more advanced html and css?

    • Once you have the data in a table you can apply any CSS you want. One of the easiest way to style tables would be using something like TableCloth. I wrote a TableCloth Tutorial awhile ago. Hope that helps.

  43. Hi,
    I need to get one particluars data from the cell of my db

    $table = “SELECT cost FROM Pricelist WHERE roomId = 1″;
    $all = mysql_query($table);
    echo “$all”;

    Plis help

  44. hi! im creating a tutorial website like this one, the page im working on: title of the tutorial will be a link when they click it it will be on new page plus the whole tutorial and all of this are from mysql db, the problem is i can display the link title and the whole content in a new page..the display would be the tutorial title and the content, thanks again and nice tutorial! hope you could answer my problem. :)

  45. Hello John, You are doing a great job. This tutorial was really helpful to me. Please keep up the good work.
    Thank you.

  46. Username Display

    <?php

    // check to see that we are submitting the correct form
    if($_GET['do'] == "submit")
    {

    $con = mysql_connect ("127.0.0.1", "root", "password");
    mysql_selectdb ( "fraser", $con );
    $query = ("SELECT username, password FROM fraser");
    while($row=mysql_fetch_array($result));

    }
    echo "User :{$row['username']}”;

    ?>

    why wont this display information from my db?

    • correction:

      $result=mysql_query($query);

      “Above line missing in your coding”

  47. Love it, works a treat.

  48. This is not workign for me do you have the source code?

  49. mission accomplished and beyond… Fantastic!

    I want to learn how to make a php template now!

  50. ok It would be nice if you could show where the course continuous,i didnt find a link right away.

  51. I am still having problem still. I have utilized the example you gave above. I have amended the password to mine but got this error after executing:
    Parse error: syntax error, unexpected T_STRING, expecting ‘,’ or ‘;’ in /home/ijsshrco/public_html/assignments.php on line 22
    I wish i could upload my program as an attachment but i’ll post it all the same:

    Oteh’s Assignments

  52. i did a few more editing but got this error:
    Parse error: syntax error, unexpected $end in /home/ijsshrco/public_html/assignments.php on line 35

    My php code is as follows:

    Oteh’s Assignments

    <?php
    //make connection
    mysql_connect ("localhost","ijsshco_assignments","master123") or die ('I cannot connect to be database because: ' . mysql_error());
    mysql_select_db ("ijsshco_assignments");
    //build query
    $query = mysql_query("select * FROM contacts");
    //display results
    while ($row = mysql_fetch_array($query)){
    echo //tells php to print the results to screen
    "ID: “//html for label. Goes to new line and prints label
    .$row['ID']. //selects our first row data ID and prints it
    ” Name: “//more html just tells the web browser to go the next line. Line we print the label out.
    .$row['name']. //prints our name data
    ” School: ” //more html another break tag and label
    .$row['school']. //grabs school data from Database
    “Course Title: ” //
    .$row['course_title']. //prints Course Title data
    “Course Code: ” //
    .$row['course_code']. //prints Course Code Data
    “Registration Number: ” //
    .$row['reg_no']. //prints Course Code Data
    “Email: ” //
    .$row['email']. //prints Course Code Data
    “Upload: ” //
    .$row['upload']. //prints Course Code Data
    “;} //final break and we close the loop.

    ?>

  53. My latest error after getting thru that state:

    Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘ijsshco_assignme’@’localhost’ (using password: YES) in /home/ijsshrco/public_html/assignments.php on line 8
    I cannot connect to be database because: Access denied for user ‘ijsshco_assignme’@’localhost’ (using password: YES)

    • You’re user can’t connect to the database. You need to make sure the user has permissions and you are using the correct password. Also be sure that you have the user enabled for local_host.

  54. I have just a blank page.
    In phpMyAdmin on request – “SELECT * FROM TestTable” I have “#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘TestTable’ at line 1″

  55. thank u very much i was looking for this from 2 day and very hard worked for it.
    Thanksss…

  56. Once I run the test, I do see the table column field names on the return search page such as:

    ID:
    First Name:
    Last Name:
    Phone:

    but I don’t get any of the actual data from the database.

    I assume the coding of .$row is responsible to get this data onto the search result page.

  57. hir sir! can i ask for a help ?
    ..can u help me, I’m looking for a PHP script, on how to display images from the directory/database i’m using MYSQL database..

    hope u can help me…

    wheng..

  58. pls i need to know ow to put d echoed values into a textbox in a form..

    <input type = "text" size = "20" value= "”>

    didnt work

  59. this is by far the simplest and the best php+mysql “basic tutorial” I have seen. you should seriously considering writing a book.

  60. hello !  php is not at all working in my browser its simply showing blank page please help me with reply

  61. it’s now 2012 but your tutorial is still amazing and easy to understand !!! this is great

  62. php wil work when u will install xampp or easyphp or somekind of software
    I am using xampp
    first u need to do is go to c: drive and xampp then htdocs and there u have to save all of ur files with .php extentions
    Then run any browser like chrome or mozilla
    then type 127.0.0.1/filename.php
    or localhost/filename.php

  63. This is fantastic. This is the first straightforward set of PHP code I have found that really gave me a real result when building php pages in in ew4. Thank you so much.

  64. thanks very helpfull

Trackbacks/Pingbacks

  1. Team Tutorials » Inserting Data Into a MySQL Database using PHP - [...] tutorial is a continuation on the “How to Access a MySQL Database Using PHP” tutorial that showed you how …
  2. Team Tutorials » Creating a Form that will Search a MySQL Database - [...] how to add a simple search box to query a MySQL database. This tutorial is a continuation of the …
  3. Convert a MySQL date field using PHP Functions | Team Tutorials - [...] How to Access a MySQL Database Using PHP [...]
  4. Editing MySQL Data Using PHP | Team Tutorials - [...] you haven’t taken a look at the past tuorials you may want to: How to Access a MySQL Database …
  5. Database connection string? - x10Hosting Community - [...] connection string? I am following a tutorial on how to access a MySQL database; http://teamtutorials.com/web-develop...base-using-php …
  6. Php for me - [...] http://teamtutorials.com/web-development-tutorials/how-to-access-a-mysql-database-using-php [...]
  7. Creating a Form that will Search a MySQL Database | Hitlanka - [...] how to add a simple search box to query a MySQL database. This tutorial is a continuation of the …
  8. Authentication with PHP/MySQL - […] Database Construction Access Database with PHP Creating Data for Database in […]

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>