How to retrieve sql database using PHP?

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

  1. snoopy531 macrumors newbie

    Joined:
    Sep 26, 2011
    #1
    Hi,

    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 (http://webcheatsheet.com/php/connect_mysql_database.php?print=Y). 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:
    myuser='user'
    mypass='passe'
    mydb='namr'
    myq="Quers"
    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 (http://webcheatsheet.com/php/connect_mysql_database.php?print=Y), 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?

    <?php
    $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
    $row{'year'}."<br>";
    }
    //close the connection
    mysql_close($dbhandle);
    ?>


    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),
    PRIMARY KEY(id)
    );
    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

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #2
    Next time please use the PHP code tags when posting PHP code.

    Change this portion of your code as follows:

    PHP:
    //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)) {
    echo 
    "ID:".$row['id']." Name:".$row['name']."Year: "$row['year']."<br />";
    }
    //close the connection
    mysql_close($dbhandle);
    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: http://php.net/manual/en/function.mysql-error.php

    Cheers

    :cool:
     
  3. snoopy531 thread starter macrumors newbie

    Joined:
    Sep 26, 2011
    #3
    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:
    PHP:
    $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

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #4
    Change that code to:

    PHP:
    $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: https://www.digitalocean.com/commun...ate-a-new-user-and-grant-permissions-in-mysql

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

    snoopy531 thread starter macrumors newbie

    Joined:
    Sep 26, 2011
    #5
    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?

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

    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";
    by
    $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

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #6
    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.
     

Share This Page