How to Connect a Database with php

[carl]

The php scripting language is a method of accessing your MySQL database via the Web. Below is an Example program using PHP to interact with a MySQL database, which has been commented to explain each function:


// Connect To Database
// * mysql_connect takes the servername, user, and password
// * as arguments. mysql_selectdb takes the database name.
// * Together, they open a connection to your database.
mysql_connect($SERVER,$USER,$PASSWORD);
mysql_selectdb($DATABASE);

// Execute Query
// * mysql_query takes as its argument the query you are
// * executing on the database. It should be assigned to
// * a variable -- the variable is used by other functions
// * to retrieve the results.
$QUERY = mysql_query("SELECT * from test");

// How man rows in results?
// * mysql_num_rows takes the variable the query was
// * assigned to (referred to hereafter as the query
// * identifier) and returns the number of rows the query
// * resulted in.
$NUMROWS = mysql_num_rows($QUERY);

// Display Results
if ($NUMROWS) {
$I = 0;
while ($I < $NUMROWS) {
// Get Results
// * mysql_result returns the value of a specific field
// * in a specific row. It takes three arguments: the
// * first is the query identifier, the second is the row
// * number, and the third is the field name. In this
// * example, a while loop is used to process all
// * rows.
$FIELD1 = mysql_result($QUERY,$I,"field1");
$FIELD2 = mysql_result($QUERY,$I,"field2");
$FIELD3 = mysql_result($QUERY,$I,"field3");
echo "field1 = $FIELD1, field2 = $FIELD2, field3 = $FIELD3 \n";
$I++;
}
}

?>

PHP provides many other MySQL-related functions.

[Christina]

The server which is running PHP can connect to the server running MySQL. You can check this from the command line first. If you can't connect without PHP, you are not going to be able to connect with PHP. But If you using any other servers, you may need to ask the systems administrator for help.

[bod-jack]

Below is the code for connecting to a MSSQL Server database.

$myServer = "localhost";
$myUser = "your_name";
$myPass = "your_password";
$myDB = "examples";

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT id, name, year ";
$query .= "FROM cars ";
$query .= "WHERE name='BMW'";

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result);
echo "< h1 >" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned < /h1 >";

//display the results
while($row = mssql_fetch_array($result))
{
echo "< li >" . $row["id"] . $row["name"] . $row["year"] . "< /li >";
}
//close the connection
mssql_close($dbhandle);
?>

[Glenn]

If you want to use remote database means database hosted on other cPanel Web server then you need to replace localhost by ip of remote cpanel server. And other setting will be as given below.

$myServer = "Ip of remote server";
$myUser = "User Name of remote database ";
$myPass = "Password remote database user";
$myDB = "Remote database Name";

Also you will have to add the ip of your server at remote cPanel >> "Remote Database Access Hosts" so that remote database host will allow your host to access the database.

Glenn....

[Rodricks]

Thanks to all for providing us this helpful information

[Sean]

Thanks

I am new to PHP too. I definitely appreciate your posts in regards with the database.

[RichardM]

You can do a lot with PHP. Any time spent learning it is time well-spent.

I use PHP and MySQL to generate the pop-windows of flight instructors by state, and then sort them within the windows alphabetically by town, from the image map on this page.

Richard

[Mark Craig]

Well, also i have checked the following where we can notice what a visitor is using more at the time of browsing. We can practically check the agent string the browser sends as part of HTTP request. This is stored in a variable. This normallsy starts with $ in PHP such as follows :

< ?php
echo $_SERVER[ 'HTTP_USER_AGENT' ];
? >

It may show the following result :

Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)

Well, this interests me alot when it comest to statistics or monitoring of the websites.

[Agent_Smith]

If you make a software which relies on multiple files connecting to a database, i would place

PHP Code:

$servername = "localhost";
$dbuser = "your_name";
$dbpass = "your_password";
$dbname = "examples"; 


Along with the connect function and then use a php include in the files which require!

[sam]

Please let me know the codes for closing a connection of Database

[Christina]

Once the Database will be closed the script ends automatically. To close the connection before, use the mysql_close() function.

<?php
$con = mysql_connect(\"localhost\",\"peter\",\"abc123\");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// some code

mysql_close($con);
?>

[RichardM]

Originally Posted by Christina

Once the Database will be closed the script ends automatically. To close the connection before, use the mysql_close() function.

<?php
$con = mysql_connect(\"localhost\",\"peter\",\"abc123\");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// some code

mysql_close($con);
?>

Also:

PHP Code:

mysql_close() 


without the identifier in the (), will close the most recently open connection.

As Christina said, it usually is unnecessary to explicitly close a connection, but there are times when it is necessary.

For example, when multiple connections are needed (for example, when the same script needs to query one database to get certain information, and then query a different database to get additional information), it may be necessary to explicitly close the first connection before opening a second or subsequent connection. In this case, it's critical to use the correct identifier, for example

PHP Code:

mysql_close($first_connection); 


because mysql_close() without the identifier will close the most recent open connection.

Another time when you may want to explicitly close a connection is when the script has a lot of processing to do after the information has been retrieved, and you don't want to needlessly keep the connection open while waiting for the long script to execute.

Richard

[Agent_Smith]

Originally Posted by sam

Please let me know the codes for closing a connection of Database

PHP Code:

mysql_close($connection); 


[Sean]

mysql_close() is not necessary at times as open links are normally closed at the end of the script's execution

[carl]

Yes I agree with Samantha,

The only reason for using "mysql_close" is if your script is assuredly finished with the connection, but still has a many of processing left to do. It is almost never beneficial to open and close the same connection throughout a script.

[aliya]

Thanks all for this informative posts.This type of posting helps us to improve our knowledge.

[Rodricks]

Sometimes a webhosting provider will require you to specify the MySQL server name and port number.
For example if the MySQL server name is db.php-mysql-bodhost.com and the port number is 3306 then you you can modify the above code to :

<?php
$dbhost = 'db.php-mysql-bodhost.com:3306';
$dbuser = 'root';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');

$dbname = 'petstore';
mysql_select_db($dbname);
?>

[sharron]

Nice to see someone helping others with coding. Good Job!!!

[bod-jack]

For creating and populating the table you can Run the following SQL :-

mysql> CREATE DATABASE first_test;
Query OK, 1 row affected (0.31 sec)
mysql> USE first_test;
Database changed
mysql> CREATE TABLE people (
id int UNIQUE NOT NULL,
first_name varchar(40),
surname varchar(50),
PRIMARY KEY(id)
);
Query OK, 0 rows affected (0.24 sec)
mysql> INSERT INTO people VALUES(1,'Ann','Brache');
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO people VALUES(2,'Narcizo','Levy');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO people VALUES(3,'Tony','Crocker');
Query OK, 1 row affected (0.00 sec)

Your table should now look as follows:

mysql> SELECT * FROM people;
+----+------------+---------+
| id | first_name | surname |
+----+------------+---------+
| 1 | Ann | Brache |
| 2 | Narcizo | Levy |
| 3 | Tony | Crocker |
+----+------------+---------+
3 rows in set (0.19 sec)

[shikha]

Hello

Thanks for the post and thanks for providing those valuable information

Thanks