How to: Connect to my MySQL Database server using command line and php

by on September 5, 2006 · 13 comments· LAST UPDATED May 31, 2007

in , ,

Q. How do I connect to my MySQL database server using command line (over ssh) or using PHP?

A. You can connect to MySQL database server using mysql command line client or using programming language such as PHP or perl.

Task: Use command line client - mysql

Genral syntax is as follows:
mysql -u DBUSER -h DBSERVERNAME -p

So at a shell prompt you type all one single line to connect to database server install on localhost for vivek user:
$ mysql -u vivek -h localhost -p

Supply the password when prompted for password. Make sure you replace vivek and localhost name with your database username and hostname.

Task: Use PHP to connect to MySQL

Type the following PHP code:

<?php
   $link = mysql_connect("localhost", "USERNAME", "PASSWORD");
   mysql_select_db("DATABASE");
   $query = "SELECT * FROM TABLE";
   $result = mysql_query($query);
   while ($line = mysql_fetch_array($result))
   {
      foreach ($line as $value)
       {
         print "$value\n";
      }
   }
    mysql_close($link);
?>

Make sure you replace USERNAME and PASSWORD with your database user name and password. Also, replace TABLE and DATABASE with the valid table and database names from your database.

Upload PHP file and type url http://yourdomain.com/myscript.php

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 13 comments… read them below or add one }

1 sr April 6, 2009 at 12:30 pm

hi i am trying to connect to my sql with this code

but i get an erorr of exceeding the time or long period more than 60 sec

would u help me

Reply

2 Bim September 17, 2010 at 11:01 am

Hi sr,
with the age of this post i guess you would have solved this by now but for others:

looking at the code in the example ‘select * from table’ and a loop printing out each value on the screen theres a good chance that the script is taking too long to execute (default is 60 seconds) for others wanting to test connections i’d suggest putting on a limit eg.
SELECT * FROM table LIMIT 50;
Then that will limit mysql to only pull the first 50 rows

Reply

3 grimdestripador@hotmail.com May 24, 2011 at 4:18 pm

Begin Quote: but i get an erorr of exceeding the time or long period more than 60 sec. End Quote.
You need to edit php.ini

Reply

4 Michael April 15, 2009 at 6:34 pm

Other than displaying the value twice, this worked great for me! Thanks

Reply

5 dhirendra October 5, 2009 at 7:51 pm

i didn’t get the solution of my problem. actually i’ve created database on my own system and i want to connect it to my web host server so that every body who visits my site can access data from that database. is it possible? what could i have to do for that?

Reply

6 Dawson April 27, 2011 at 5:39 pm

Thanks! Worked perfectly.

Reply

7 portgaz July 27, 2011 at 2:22 am

Hi thanks for your code it really works. But i got an error when i try to use my own ip.
The error says, “Host ‘comp.local’ is not allowed to connect to this MySQL server”. Any ideas?

Thanks in advance

Reply

8 portgaz July 27, 2011 at 2:52 am

Hi I already figured it out. I got the error because the host is set to localhost and not my local ip..

Cheers

Reply

9 John Ortiz December 2, 2011 at 2:29 pm

Thank you for this.

Reply

10 Matt Moore January 31, 2012 at 2:46 pm

Thank you soo much for this absolute life saver! i’m currently converting our old site to PHP (after using HTML with CSS for so long) it got abit time consuming trying to flick through books which conveniently miss the PRINT function *palms own face*. I can’t believe after hours of chewing through my code trying to decipher the simple way to produce the results it was something as small as that. 10 thumbs up!

Reply

11 Matt Moore January 31, 2012 at 4:42 pm

In response to feedback ’3′ of this thread (and for anyone reading this who gets the same result). The only way i could remove the duplicate results after fetching an array query was to take the PRINT out of the code and put it into an ECHO command by adding an extra variable.(i used $range for this example but you can use anything)

e.g-

<?php
$link = mysql_connect("localhost", "USERNAME", "HOST");
mysql_select_db("DATABASE");
$query = "SELECT * FROM TABLE";
$result = mysql_query($query);
$range = "";
while ($line=mysql_fetch_array($result))
{
foreach ($line as $value)
{

//$range .= $value;
}

$range .= "$value”;
mysql_close($link);

}

?>

Then later in the code to produce the results from the query i put the ECHO in a element:

not really related to much to the topic and i know there might be other easier ways but i thought it might help people who are new to PHP like me :)

Reply

12 Matt Moore January 31, 2012 at 4:45 pm

edit to previous post :

$link = mysql_connect(“localhost”, “USERNAME”, “PASSWORD”);

sorry

Reply

13 rathnam July 24, 2012 at 8:42 am

how to update and insert and edit the data

Reply

Leave a Comment

Tagged as:

Previous Faq:

Next Faq: