PhP Connection to MySQL database

Before you can get content out of your MySQL database, you must know how to establish a connection to MySQL from inside a PHP script. To perform basic queries from within MySQL is very easy. This article will show you how to get up and running.

Let's get started. The first thing to do is connect to the database.The function to connect to MySQL is called mysql_connect. This function returns a resource which is a pointer to the database connection. It's also called a database handle, and we'll use it in later functions. Don't forget to replace your connection details.

";
?>

All going well, you should see "Connected to MySQL" when you run this script. If you can't connect to the server, make sure your password, username and hostname are correct.

Once you've connected, you're going to want to select a database to work with. Let's assume the database is called 'examples'. To start working in this database, you'll need the mysql_select_db() function:



Now that you're connected, let's try and run some queries. The function used to perform queries is named - mysql_query(). The function returns a resource that contains the results of the query, called the result set. To examine the result we're going to use the mysql_fetch_array function, which returns the results row by row. In the case of a query that doesn't return results, the resource that the function returns is simply a value true or false.

A convenient way to access all the rows is with a while loop. Let's add the code to our script:

";
}
?>

Finally, we close the connection. Although this isn't strictly speaking necessary, PHP will automatically close the connection when the script ends, you should get into the habit of closing what you open.



Here is a code in full:

";

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

To create 'examples' database on your MySQL server you should 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');


Squarespace | Create beautiful websites.Advertise Here
Need help with ASP, PHP, Javascript?
Let our experts help you.


ASPRunner.NET - code generator for ASP.NET
Web hosting on steroids (PHP,ASP,MySQL,unlimited emails)



Tags: PHP CONNECTION DATABASE MYSQL FUNCTIONS RETRIEVE PRINT SQL QUERY

Add To: Add to dzone dzone | Digg this digg | Add to del.icio.us del.icio.us | Stumble it stumbleupon

* Comments (25)


HTML tags and other related characters will be stripped out of comments.
E-Mail address will not be displayed and will only be used for E-Mail notifications.
Your Name *
E-Mail Address
Comments *

i had done the above instruction yet not working, are there any internal settings to be done in php.ini or in apache server httpdconf?? please reply immediately its urgent.

# Posted by jayesh | 4 Dec 2006 02:38:14
i am using windowxp, username means, what?? name on which os is installed??
# Posted by jayesh | 4 Dec 2006 02:43:23
Jayesh, have a look at the article

How to install and cofigure PHP 5 on Windows box
http://www.webcheatsheet.com/php/install_and_configure.php

username applies to MySQL
for more details about usernames and passwords in MySQL have a look at the following link:
http://dev.mysql.com/doc/refman/5.0/en/user-names.html


# Posted by Andrew | 4 Dec 2006 05:49:25
Do i need to have mysql installed in the computer? I have dream wever software.

# Posted by koushik gattu | 2 Jan 2007 11:21:08
Works great! I am catching on so keep up the good work!


# Posted by willyboy | 2 Jan 2007 20:48:15
Hi, thank you for this tutorial. It was bit confusing when you used two different things. In while loop you used people, and displaying rows you used cars, but other than that i got the idea. Thank you.


# Posted by dzemal | 17 Jan 2007 12:31:43
how would you pull data from two different tables? for example; one table holds names and another holds info such as dob, address and so forth? so, in the end it would look like: name - address, dob,...


# Posted by dzemal | 31 Jan 2007 13:56:51
Dzemal, you should use the following SQL query:

SELECT table1.name, table2.address
FROM table1, table2
WHERE table1.id = table2.id

This SQL statement would return all rows from table1 and table2 where there is a matching id value in both table1 and table2.

php code:
...
//$result = mysql_query("SELECT table1.name, table2.address FROM table1, table2 WHERE table1.id = table2.id");

//while ($row = mysql_fetch_array($result)) {
echo $row{'name'}." - ".$row{'address'}."
";
}
...



# Posted by Andrew J. Cooper | 31 Jan 2007 23:46:47
So basically you are matching id's as relationship. Thank you Andrew, you make it to easy :).



# Posted by dzemal | 1 Feb 2007 05:50:39
Andrew, i have one more question if i may. In this example that you gave me:
$result = mysql_query("SELECT table1.name, table2.address FROM table1, table2 WHERE table1.id = table2.id");

//while ($row = mysql_fetch_array($result)) {
echo $row{'name'}." - ".$row{'address'}."
";
}

how would i SORT BY table.id DESC

What i am trying to do is figure out how to bring latest entree from my voting pull to show rather then my first. i tried placing it in the SELECT statement but it didn't work!?


# Posted by dzemal | 1 Feb 2007 07:35:34
Dzemal, you should use Order By statement.
have a look at the tutorial:
http://www.webcheatsheet.com/sql/interactive_sql_tutorial/sql_orderby.php


# Posted by Andrew J. Cooper | 2 Feb 2007 00:10:05
Thank you, I have been trying to learn how to use php to access my database and your tutorial made it simple. Keep up the good work.


# Posted by baspot | 10 Feb 2007 11:59:34
This was so very helpfull... This is the stuff I have spent the entire day trying to figure out by reading on other PHP and SQL tut. Thank you so much.


# Posted by r00m23 | 27 Mar 2007 16:49:20
Thanx 4 ur tutorial. But i m still having a problem with it. When i run this file.php in my web browser, it is showing nothing. I mean a blank web page. There is problem in connection n thats why echo command is not running. Plz tell whats d problem. I need it urgent


# Posted by Sourabh Bansal | 6 Apr 2007 21:54:35
It is working really great, i was able to connect successfully.

Thanks a lot



# Posted by Tawaiha | 15 Apr 2007 20:09:21
Really Great...............!

Well descripted. Easy to undestand concepts behind.

Good work. Keep it up ..............!


# Posted by Laks | 19 Jun 2007 22:40:40
I have a upload php script that i had made by a programmer and i moved it to another domain and now it doesnt work.Cant find the person i bought it from. It worked perfect until i moved it. Can some one please help me make a mysql database schema upload table for my script, Or show me how. I just need a table in mysql and i dont know how to make it i have these files Can any one help please? The files i have are folder: uploads(Video,Pictures ) file:up_config.php,upload.php,db_config.php,up_func.php,error_log

Please help
Rat



# Posted by ratt831 | 6 Aug 2007 21:22:15
i am using PHP5 in my localhost & also my host server using php5. when i upload my script in host server the following error occurs.

//" ERRNO: 2 TEXT: mysql_fetch_array(): supplied argument is not a valid MySQL result resource LOCATION: /home/aminul02/public_html/control/log.ph "


My CODE is :
//while ($row = mysql_fetch_array($result))
{
echo $row[0];
}

Note: it works in my local PC.

thanks
Aminul

# Posted by Aminul Islam | 12 Sep 2007 23:04:43
I am trying to connect MYSQL Database using PHP but failed. My code is as below:



When I try run on browser, following error displays:

Fatal error: Call to undefined function mysql_connect() in C:Inetpubwwwrootdbtest.php on line 8

Please give possible solutions.

Thankx

Shahzad
# Posted by Shahzad | 2 Nov 2007 03:04:51
the code above is wrong.
in the sql script, you need to change 'name' to 'model'

besides that i got it to work
# Posted by Matt | 21 Nov 2007 18:04:30
Please send me more example in connecting into a database using php. I really need it for my thesis.
# Posted by Gritzen Carcasona | 27 Feb 2008 07:32:43
Have to do this for my College course, and couldn't find any good tutorials on it, but I used this and it worked straight away. Would advice this tutorial :)
# Posted by Aimee | 19 Apr 2008 14:48:03
When you connect to a database with for example:
$dbhandle = mysql_connect($hostname, $username, $password)
the password is a string that anybody can see. Is there a way to hide or encrypt the password that is not there everybody to see?
# Posted by koos | 12 May 2008 08:36:56
Thanks. Works great. Keep it up
# Posted by paulraj | 8 Jun 2008 05:28:48
I get the following error :

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:xampphtdocsconnect.php on line 19

line 19 is the line with the while loop below, can anyone help?

Thanks.

while ($row = mysql_fetch_array($result)) {
echo "ID:".$row{'id'}. "AgentID:".$row{'AgentID'} . "FirstName:".$row{'FirstName'} . "
";
}

PWWB

WAR AGAINST Terrorism Urdu Languae Presentation By Dr. Abu Waqas

Part 1

Next Part

Youtube Abu Waqas