How to retrieve sql database using PHP?

[snoopy531]

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

 

[SrWebDeveloper]

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

 

[snoopy531]

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?

 

[SrWebDeveloper]

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?

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

 

[snoopy531]

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.

 

[SrWebDeveloper]

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.

 

[oliverdev]

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

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.