w3mentor
Learning web technologies simplified!
 
Home / PHP/MySQL Tutorials / PHP MYSQL / Archive by category 'Php Mysql Examples'

Php Mysql Examples

Connect to MySql and select database using PHP

The following code is an example of how to connect to a mysql server and select a database to query. We should always free up resources you don’t need anymore i.e make sure that mysql_close() executes at the end. This makes your code more efficient and reduces the possibility of errors.

<?php
$DB_HOST = "localhost";
$DB_NAME = "db";
$DB_USER = "user";
$DB_PASSWORD = "pass";
$con = mysql_connect($DB_HOST, $DB_USER, $DB_PASSWORD);
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}
mysql_select_db($DB_NAME , $con);
mysql_close($con);
?>

Access Sqlite Database

$dbconn = sqlite_open('phpdb');    if ($dbconn) {
            sqlite_query($dbconn, "CREATE TABLE animal(Name VARCHAR(255), MaxAge INT);");
            sqlite_query($dbconn, "INSERT INTO animal VALUES ('A', 15)");
            $result = sqlite_query($dbconn, "SELECT Name FROM animal");
            var_dump(sqlite_fetch_array($result, SQLITE_ASSOC));
    } else {
            print "Connection to database failed!\n";
    }

Adding A Row To A Table

<html>
<head>
<title>Adding a Row to a Database</title>
</head>
<body>
<div>
<?php
$user = "root";
$pass = "";
$db = "mydatabase";
$link = @mysql_connect( "localhost", $user, $pass );
if ( ! $link ) {
  die( "Couldn't connect to MySQL: ".mysql_error() );
}
print "<h2>Successfully connected to server</h2>\n\n";
@mysql_select_db( $db ) or die ( "Couldn't open $db: ".mysql_error() );
print "Successfully selected database \"$db\"<br />\n";$query = "INSERT INTO domains( domain, sex, mail )values( 'w3mentor.com', 'F', 'a@w3mentor.com' )";
print "running query: <br />\n$query<br />\n";
mysql_query( $query, $link ) or die ( "INSERT error: ".mysql_error() );
mysql_close( $link );
?>
</div>
</body>
</html>

Add Record To My_Database/My_Table

<html>
<body>
<?php
    $self =  $_SERVER['PHP_SELF'];
    $id =    $_POST['id'];
    $fname = $_POST['fname'];
    $lname = $_POST['lname'];
?><form action="<?php echo( $self ); ?>" method="post">
ID: <input type="text" name="id" size="3">
First Name: <input type="text" name="fname" size="8">
Last Name: <input type="text" name="lname" size="8"><br />
<input type="submit" value="Submit">
</form><?phpif( $id and $fname and $lname){
 $conn=@mysql_connect( "localhost", "userName", "password" ) or die( "Err:Conn" ); $rs = @mysql_select_db( "my_database", $conn) or die( "Err:Db" ); $sql = "insert into my_table ( id, first_name, last_name ) values ( $id, \"$fname\", \"$lname\" )"; $rs = mysql_query( $sql, $conn ); if( $rs ){
   echo( "Record added:$id $fname $lname" );
 }
}
 
?></body></html>

Advanced Functions

$dbconn = sqlite_open('phpdb');    if ($dbconn) {
            script!
            sqlite_query($dbconn, "INSERT INTO animal VALUES('a', 14)");
            sqlite_query($dbconn, "INSERT INTO animal VALUES('b', 16)");
            sqlite_query($dbconn, "INSERT INTO animal VALUES('c', 13)");
            var_dump(sqlite_array_query($dbconn, "SELECT * FROM animal", SQLITE_ASSOC));
    } else {
            print "Connection to database failed!\n";
    }

A Function To Open A Connection To Mysql

<?php
  function opendatabase ($host,$user,$pass) {
    try {
      if ($db = mysql_connect ($host,$user,$pass)){
        return $db;
      } else {
        throw new exception ("Sorry, could not connect to mysql.");
      }
    } catch (exception $e) {
      echo $e->getmessage ();
    }
  }
 
  function closedatabase ($db){
    mysql_close ($db);
  }
 
  $db = opendatabase ("localhost","root","");
 
  try {
    if (!mysql_select_db ("mydatabase",$db)){
      throw new exception ("Sorry, database could not be opened.");
    }      $myquery = "INSERT INTO mytable (id,title,myvalue) VALUES (0,'Blue',20)";
 
    if (mysql_query ($myquery, $db)){
      echo "We were successful.";
    } else {
      throw new exception (mysql_error());
    }
  } catch (exception $e) {
    echo $e->getmessage();
  }
 
  closedatabase ($db);
 
?>

An Example Of Using The Old Mysql Extension

<?php    mysql_connect("localhost", "username", "password");
    mysql_select_db("mydatabase");    $result = mysql_query("SELECT * FROM mytable");    while($row = mysql_fetch_array($result)) {        foreach($row as $key=>$value) {            echo "$key = $value<br/>\n";        }
    }    mysql_free_result($result);
    mysql_close();
?>

Building Queries On The Fly

<?php
  function opendatabase ($host,$user,$pass) {
    try {
      if ($db = mysql_connect ($host,$user,$pass)){
        return $db;
      } else {
        throw new exception ("Sorry, could not connect to mysql.");
      }
    } catch (exception $e) {
      echo $e->getmessage ();
    }
  }
 
  function selectdb ($whichdb, $db){
    try {
      if (!mysql_select_db ($whichdb,$db)){
        throw new exception ("Sorry, database could not be opened.");
      }
    } catch (exception $e) {
      echo $e->getmessage();
    }
  }
  function closedatabase ($db){
    mysql_close ($db);
  }
  $db = opendatabase ("localhost","root","");
  selectdb ("mydatabase",$db);
  $_POST['user'] = "myname";
  $_POST['pass'] = "mypassword";
 
  function validatelogin ($user,$pass){
    mysql_real_escape_string ($user);
    mysql_real_escape_string ($pass);
    $thequery = "SELECT * FROM userlogin WHERE username='$user' AND password='$pass'";
    if ($aquery = mysql_query ($thequery)){
      if (mysql_num_rows ($aquery) > 0){
        return true;
      } else {
        return false;
      }
    } else {
      echo mysql_error();
    }
  }
 
  if (validatelogin ($_POST['user'],$_POST['pass'])){
    echo "You have successfully logged in.";
  } else {
    echo "Sorry, you have an incorrect username and/or password.";
  }
 
  closedatabase ($db);
 
?>

Calculating Password Length With Dbm

<?php
$data_file = '/tmp/users.db';
$total_length = 0;
if (! ($dbh = dba_open($data_file,'r','gdbm'))) {
    die("Can't open database $data_file");
}$k = dba_firstkey($dbh);
while ($k) {
    $total_length += strlen(dba_fetch($k,$dbh));
    $k = dba_nextkey($dbh);
}print "Total length of all passwords is $total_length characters.";dba_close($dbh);
?>

Close Database Connections

 
    <?php
   @mysql_connect("mysql153.secureserver.net","w3mentor","password") or die("Could not connect to MySQL server!");   @mysql_select_db("w3mentor") or die("Could not select database!");   echo "You're connected to a MySQL database!";   mysql_close();?>

Connecting To A Mysql Database

resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]] ) <?php
try {
    $mysqlhost = "localhost";
    $mysqluser = "root";
    $mysqlpass = "";
    if ($db = mysql_connect ($mysqlhost,$mysqluser,$mysqlpass)){
        echo "Successfully connected to the database.";
        mysql_close ($db);
 
    } else {
        throw new exception ("Sorry, could not connect to mysql.");
    }
} catch (exception $e) {
echo $e->getmessage ();
}
?>

Connecting User

<?php$domain = "localhost";
$user = "userName";
$password = "password";$conn = mysql_connect( $domain, $user, $password );if($conn)
{
 $msg = "Congratulations $user, You connected to MySQL";
} ?><html> <head>
  <title>Connecting user</title>
 </head> <body>
  <h3>
   <?php echo( $msg ); ?>
  </h3>
 </body></html>

Connect To Mysql

 
    <?
$connection = @mysql_connect("mysql153.secureserver.net", "w3mentor", "password") or die(mysql_error());$dbs = @mysql_list_dbs($connection)or die(mysql_error());
$db_list ="<ul>";
$i =0;while ($i < mysql_num_rows($dbs)){
     $db_names[$i] = mysql_tablename($dbs,$i);
     $db_list .= "<li>$db_names[$i]";
     $i++;
}
$db_list .="</ul>";
?><html>
<head>
<title>MySQL Databases</title>
</head>
<body>
<p><strong>Databases on localhost</strong>:</p>
<? echo "$db_list"; ?>
</body>
</html>

Connect To Mysql Database

 
    <?php
//
$hostname="mysql153.secureserver.net";
$username="w3mentor";
$password="password";
$dbname="w3mentor";
$usertable="Employee";
$yourfield = "FirstName";mysql_connect($hostname,$username, $password) OR DIE ("Unable to connect to database! Please try again later.");
mysql_select_db($dbname);$query = "SELECT * FROM $usertable";
$result = mysql_query($query);
if($result) {
    while($row = mysql_fetch_array($result)){
        $name = $row["$yourfield"];
        echo "Name: ".$name;
    }
}
?>

Copying Resultsets Into An Array Manually

<?php
     $rows = array();     while($row = sqlite_fetch_array($result, $res_type, $decode)) {
        $rows[] = $row;
     }
?>

Create Guestbook Table

<html>
 
 <head>
  <title>Create guestbook table</title>
 </head>
 
 <body> <?php  $conn = @mysql_connect("localhost", "userName", "password") or die("Could not connect to database");
 
  $rs = @mysql_select_db("my_database", $conn) or die("Could not select database");
 
  $query = "id int(4) auto_increment, name varchar(50),";
  $query.= "email varchar(50), comments text, ";
  $query.= "time timestamp(14), primary key(id)";
  $sql = "create table guestbook($query)";
 
  $rs = @mysql_query ($sql);  if( $rs )
  {
    $msg = "<h3>Created guestbook table</h3>";
  }
  else
  {
    $msg = "<h3>Could not create guestbook table";
    $msg.= "<br />Does it already exist?</h3>";
  }
  echo($msg);
 ?> </body></html>

Create New Table In Mysql

 
    <?php
$linkID = mysql_connect('mysql153.secureserver.net', 'java2s', 'password');
mysql_create_db('new_db', $linkID);
mysql_select_db('new_db');$query = "CREATE TABLE new_table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, new_col VARCHAR(25))";
$result = mysql_query($query);
$axe = mysql_drop_db('new_db');
?>

Create Table And Install Data

 
     <?
  define("DBName","test");
  define("HostName","localhost");
  define("UserName","root");
  define("Password","");
 
  if(!mysql_connect(HostName,UserName,Password)){
    echo "Cannot get connected to the database ".DBName."!<br />";
    echo mysql_error();
    exit;
  }
  mysql_select_db(DBName);  // Creating the t table. If it already exists,
  // "@" overrides the error message
  @mysql_query("create table t(id int, a double)");  // Inserting 10 records into the table
  for($i=0; $i<10; $i++){
    $id=time();
    mysql_query("insert into t(id, a) values($id, '$i')");
  }
 
  // Outputting all records
  $r=mysql_query("select * from t");
 
  for($i=0; $i<mysql_num_rows($r); $i++) {
    $f=mysql_fetch_array($r);
    echo "$f[id] -> $f[a]<br />\n";
  }
?>

Creating A Database In Mysql

 
    <?php
  $host="mysql153.secureserver.net";
  $uname="w3mentor";
  $pass="password";
 
  $connection= mysql_connect ($host, $uname, $pass);
  if (!$connection) {
    die ("A connection to the server could not be established!");
  }  $result=mysql_create_db ("mydatabase");  if (!$result) {
    die ("database could not be created!");
  }
  echo "Database megabase was created successfully.";
?>

Creating An Sqlite Database

<?php
$db = new PDO('sqlite:/usr/local/zodiac');$db->beginTransaction();
$q = $db->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'zodiac'");if ($q->fetch() === false) {    $db->exec(<<<_SQL_
CREATE TABLE zodiac (
  id INT UNSIGNED NOT NULL,
  sign CHAR(11),
  symbol CHAR(13),
  planet CHAR(7),
  element CHAR(5),
  start_month TINYINT,
  start_day TINYINT,
  end_month TINYINT,
  end_day TINYINT,
  PRIMARY KEY(id)
)
_SQL_
);    $sql=<<<_SQL_
INSERT INTO zodiac VALUES (1,'Aries','Ram','Mars','fire',3,21,4,19);
INSERT INTO zodiac VALUES (2,'Taurus','Bull','Venus','earth',4,20,5,20);
INSERT INTO zodiac VALUES (3,'Gemini','Twins','Mercury','air',5,21,6,21);
_SQL_;    foreach (explode("\n",trim($sql)) as $q) {
        $db->exec(trim($q));
    }
    $db->commit();
} else {
    $db->rollback();
}
?>

Latest Tutorials & Code Snippets

Reference Guides