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.

  • http://www.egames-evo.com Robert

    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.

  • http://www.teamtutorials.com John Ward

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

    • pratap

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

  • http://www.athomeforhire.com Tahrah Hunt

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

  • http://www.athomeforhire.com Tahrah Hunt

    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

  • danny spektor

    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

  • http://www.teamtutorials.com John Ward

    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.

  • http://www.teamtutorials.com John Ward

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

  • steve

    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

  • http://www.node-net.com mark hearne

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

  • Pingback: Team Tutorials » Inserting Data Into a MySQL Database using PHP

  • julie

    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?

  • http://www.teamtutorials.com John Ward

    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.

  • julie

    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!

  • http://www.teamtutorials.com John Ward

    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.

  • http://www.f3mp3.com www.f3mp3.com

    nice tutorial .. dude…

  • hafiz

    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

  • Mark

    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

  • http://www.teamtutorials.com John Ward

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

  • mike schnider

    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.

  • Pingback: Team Tutorials » Creating a Form that will Search a MySQL Database

  • http://pearlcar.com Will

    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!

  • http://www.teamtutorials.com John Ward

    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.

  • Charles

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

  • NISHANT

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

  • http://www.teamtutorials.com John Ward

    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.

  • Will

    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.

  • http://www.umglobalhr.com Elena

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

  • http://www.umglobalhr.com Elena

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

  • http://www.teamtutorials.com John Ward

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

  • http://webair.designedbymkg.com/test.php Kamal

    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.

  • http://webair.designedbymkg.com/test.php Kamal

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

    ?>

  • Tuba

    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?

  • Tuba

    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.

  • http://www.premierewebdesign.net William

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

  • http://99orkut.com 99Orkut.com

    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

  • Pingback: Convert a MySQL date field using PHP Functions | Team Tutorials

  • Pingback: Editing MySQL Data Using PHP | Team Tutorials

  • Michael

    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

  • Tunçay Şanlı

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

  • Ari

    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…

  • http://www.pspgoguide.com/ PSP Go

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

  • http://techtricks.co.in sreenidhi

    thanks a lot.
    explanation was really helpful

  • http://www.pika.me Clay

    thank the heavens for teamtutorials.com!

  • Pingback: Database connection string? - x10Hosting Community

  • Pingback: Php for me

  • Mherb204

    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

  • bunty

    Hi,

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

    Best Regards

  • Tom

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

    • http://www.johnathanward.com John Ward

      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.

  • Johnny

    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

  • http://teachsanjay.x10.mx/ sanjay

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

  • http://hilltoplynk.net talk2shine

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

  • http://google.com noob

    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?

    • http://ssssssss Pawan

      correction:

      $result=mysql_query($query);

      “Above line missing in your coding”

  • http: Anthoy Jackson

    Love it, works a treat.

  • mike

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

  • http://softubspas.co.uk Adam

    mission accomplished and beyond… Fantastic!

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

  • http://1surf.info john

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

  • http://www.ijsshr.com Marshal

    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

  • http://www.ijsshr.com Marshal
  • http://www.ijsshr.com Marshal

    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.

    ?>

  • http://www.ijsshr.com Marshal

    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)

    • http://www.johnathanward.com John Ward

      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.

  • illia

    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″

  • http://glamourboy4000.freeweb.pk Hasnain

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

  • http://none David

    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.

  • http://none wheng

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

  • Pingback: Creating a Form that will Search a MySQL Database | Hitlanka

  • Princeyeni

    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

  • Geocrafter Team

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

  • Prakashsb1990

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

  • http://telexperience.co aj

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

  • mohit

    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

  • CJ215

    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.

  • Jay Ar

    thanks very helpfull

  • Pingback: Authentication with PHP/MySQL

  • http://majkelfight.edublogs.org/ flight simulator x addons

    I was recommended this website by my cousin. I am not sure whether this post is written by him as nobody else know such
    detailed about my difficulty. You’re amazing!
    Thanks!

    Look at my webpage – flight simulator x addons