How to retrieve sql database using PHP?

Discussion in 'Web Design and Development' started by snoopy531, Aug 16, 2013.

  1. snoopy531 macrumors newbie

    Sep 26, 2011

    I would please like to know how I can retreive sql database table using php? I tried an exemple from a book and an exemple code from a website ( In both cases, I sucessfully connected to mysql server but haven't succeded connecting to the database to retreive information from a table. I spend 3 days on it but I didn't find the solution. My guess is that it has something to do with the user, host and password I enter. I think that I don't use a username and a password so I enter "localhost" for the host, "root" for the user and "" for the password. I tried the command "SELECT * FROM mysql.user;" to get that information and I saw many users, hosts and passwords and I don't know which on is the right one nor which one I am currently using. A few of those passwords were probably created by me a month ago and now, i don't even remember how.

    Additional information:
    Before connecting to mysql I went to the bash application and entered the following parameters:
    alias mysql=/usr/local/mysql/bin/mysql
    mysql --user=root mysql
    So from the following, I would like to know if I should enter
    $username = "user";
    $password = "passe";
    $hostname = "localhost";
    in my case ? I also tried it but it still didn't solve the situation.

    In resumé, in the following code from (, I need to know why I can't retrieve information(I always get Could not select examples message) and If I correctelly entered the user, password and host?

    $username = "root";
    $password = "";
    $hostname = "localhost";

    //connection to the database
    $dbhandle = mysql_connect($hostname, $username, $password)
    or die("Unable to connect to MySQL");
    echo "Connected to MySQL<br>";

    //select a database to work with
    $selected = mysql_select_db("examples",$dbhandle)
    or die("Could not select examples");

    //execute the SQL query and return records
    $result = mysql_query("SELECT id, model,year FROM cars");

    //fetch tha data from the database
    while ($row = mysql_fetch_array($result)) {
    echo "ID:".$row{'id'}." Name:".$row{'model'}."Year: ". //display the results
    //close the connection

    To create 'examples' database on your MySQL server I run the following script:

    CREATE DATABASE `examples`;
    USE `examples`;
    CREATE TABLE `cars` (
    `id` int UNIQUE NOT NULL,
    `name` varchar(40),
    `year` varchar(50),
    INSERT INTO cars VALUES(1,'Mercedes','2000');
    INSERT INTO cars VALUES(2,'BMW','2004');
    INSERT INTO cars VALUES(3,'Audi','2001');

    Thank you very much !!
  2. SrWebDeveloper macrumors 68000


    Dec 7, 2007
    Alexandria, VA, USA
    Next time please use the PHP code tags when posting PHP code.

    Change this portion of your code as follows:

    //execute the SQL query and return records
    $result mysql_query("SELECT id, name,year FROM cars");

    //fetch tha data from the database and display results
    while ($row mysql_fetch_array($result)) {
    "ID:".$row['id']." Name:".$row['name']."Year: "$row['year']."<br />";
    //close the connection
    You defined a table column as "name" in your schema but used "model" in your code. Also, PHP associative arrays use [] and not {}. Once you correct all that and make a successful query and result, look into adding error trapping for the query such as demonstrated in this link:


  3. snoopy531 thread starter macrumors newbie

    Sep 26, 2011
    Thx, I retried it with your advice but I still have the same problem . The problem is before that istruction, it's when I try to conect to the database:
    $selected mysql_select_db("examples",$dbhandle)  
      or die(
    "Could not select examples"); 
    It doesn't connect and never recognize database examples. I always get Could not select examples. Why?
  4. SrWebDeveloper macrumors 68000


    Dec 7, 2007
    Alexandria, VA, USA
    Change that code to:

    $selected mysql_select_db('examples'$dbhandle);
    if (!
    $selected) {
        die (
    'Can\'t use examples : ' mysql_error());
    That will tell you the error involved - always add error trapping and error message reporting to your code, each step from connection to result. ;-)

    Since you didn't get a MySQL server connection error it means you did use the right server name, username and hostname (noting root login without a password is very insecure and never recommended). Then, once connected, it failed on DB selection. I notice in your bash setup a different DB than what's in the schema posted at the bottom. Either the schema is wrong or a permissions issue of some sort, most likely. To find out, login to MySQL directly as root and then type in "show databases;" to see what's really there.

    Note: In a production site you should not use root, rather setup another username with proper privileges just to that one database. For examples of how to create users and assign databases and grants for secure access, please see this page:

  5. snoopy531, Aug 18, 2013
    Last edited: Aug 18, 2013

    snoopy531 thread starter macrumors newbie

    Sep 26, 2011
    Thx, I added . mysql_error() and the error I get is that it doesn't recognize the database exemples. But, when I type show database in mysql, database exemples is there. I am trapped again because I can't retrieve the table information and I don't know what I did wrong?

    I created a new user and password with
    CREATE USER 'user'@'localhost' IDENTIFIED BY 'passe';
    GRANT [ALL PRIVILEGES] ON [exemples].[cars] TO ‘[user]’@'localhost’;

    I entered in the bash and changed the first bash parameters to :
    bash-3.2$ myuser='user'
    bash-3.2$ mypass='passe'
    bash-3.2$ mydb='examples'
    bash-3.2$ myq="Quers"
    bash-3.2$ myserver='localhost'
    then I connected to mysql

    and In the php code, I changed
    $username = "root";
    $password = "";
    $hostname = "localhost";
    $username = "user";
    $password = "passe";
    $hostname = "localhost";
    and now when I do that, I can't connect to the server. I am trapped again because I can't retreive the table information and I don't know what I did wrong? I still need help please.
  6. SrWebDeveloper macrumors 68000


    Dec 7, 2007
    Alexandria, VA, USA
    1) Bash stuff is not required for PHP and MySQL. But you keep spelling it "examples" in bash code and "exemples" in PHP. All that matters is the actual DB created in MySQL, use that for PHP DB name, whatever it is.

    2) Um, how to put this... for grants you didn't realize the documentation stuff wrapped in [ and ] were added by the author of that blog and meant to be replaced, i.e. don't use [exemple] use exemple instead. I suggest creating another user named "test" with a new password and properly setup grants, flush, adjust PHP to match all the settings.
  7. oliverdev Suspended


    Apr 19, 2018

    For this, you only have to connect your database with your PHP file, which you can do it simply using MySQL or you can also use PDO or MySQLi. For MySQL method, once your database is in place, create a db_connection.php file and establish the connection from it. Next, from your index.php file, test the connection. That's the simplest method for connecting MySQL database with PHP. PDO and MySQLi are little longer but they do the same.

Share This Page

6 August 16, 2013