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

PHP MYSQL

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);
?>

PHP Script to backup tables from mysql database

This script allows you to backup Mysql database and its tables. It is configured by default to Easyphp locally.

<?php
$Database_name = 'somedb';
$table = 'sometable';
 
     Define ('H', 'localhost'); define ('N', 'root') define ('P','password'); define ('T', $database_name);
     Mysql_connect (H, N, P) or die (mysql_error ()); mysql_select_db (T) or die (mysql_error ());
 
     function Escape($v){return mysql_real_escape_string($v);}
 
     Define('FILE', 'C:/Program Files/EasyPHP 2.0b1/www/'. $table .'/'. date ('d').'-'. date (' m').'-' . date ('Y').'.php');
     Define('FP', fopen(FILE,'w'));
 
     $aa = mysql_list_tables($database_name); while ($a = mysql_fetch_row($aa))
     {
 
			 $bb = mysql_query ('SELECT * FROM'. $a[0].''); $tt = mysql_num_rows($bb); $nb = mysql_num_fields($bb);
			 If($tt>0) 
			 {
				 $p = 'INSERT INTO'. $a[0]. " VALUES ';
 
				 $t = 0;
				 while ($b = mysql_fetch_array($bb)) 
				 {
					 $ p .='(';
					 For ($i=0;$i<$nb, $i++) 
 
					 {
							if ($i==0) 
							{
								$p .= "'$b[$i]'";
							}
							else
							{
								$p .=' , ''. Escape($b[$ i]). ''';
							}
					 }
					 $t++;
 
					 if ($t<$tt) 
					 {
						 $ p .='),'."  n ";
					 }
					 else
					 {
						$ p .=');'."  n";
					 }
				 } 
				 $p .= "nnn";
				 fwrite(FP, $p);
			 }
     }
 
     fclose(FP);
?>

PHP function that will help you fill a list from any table in your database

function listmytablerows($table, $name, $field, $where, $textID)
{
/ / Connect to the database and query execution
connect ();
$Sql = "select * from". $table. "". $where. "ORDER BY". $field;
$Req = mysql_query($sql);
$Res = mysql_num_rows($req);
?>
<Select name = "<?php echo $name; ?>" id="<?php echo $textID; ?>">
<option value=""> ____</ option>
<? Php
/ / We do a loop that will read the information for each record
while ($data = mysql_fetch_array($res))
{
    / / We display the information from the current record
?>
      <Option value = "<?php echo $data['id']; ?>">
      <?php echo $data[$field]; ?>
</ Option>
     <? Php
    }
?>
</ Select>
 
<? Php
}
?>

Simple tutorial on php mysql

Hello, here is a little tutorial on managing databases using PHP

To connect to your database we can use the function mysql_connect () that accepts three arguments:
* The host name
* User Name
* Password
This function returns the MySQL connection id on success or FALSE on failure

<?php
$connection = mysql_connect ("localhost", "root", "password");
if (! $connection)
die ("can not connect");
?>

To close the connection, we use the mysql_close function () This function takes as argument the variable returned by mysql_connect

<?php
mysql_close ($ connection);
?>

Select the database using mysql_select_db() function. This function takes as argument the name of the database

<?php
$MyDatabase = "database";
mysql_select_db ($MyDatabase)
?>

To perform operation on the database use the mysql_query() function. This function takes as argument a string containing the SQL query.

$query = "SELECT * FROM Article", / / request
mysql_query ($query, $ connection); / / execute the query
?>

Connecting to a MySQL database using PHP

<?php
$db_hostname = 'localhost';
$db_database = 'dbname';
$db_username = 'username';
$db_password = 'password';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
?>

Retrieve and display data using MySQLi Object Interface with PHP

The following is an example on how to retrieve and display data using MySQLi Object Interface with PHP.

$mydb = new mysqli('localhost', 'username', 'password', 'databasename');
$sql = "SELECT * FROM users ORDER BY lname, fname";
$result = $mydb->query($sql);
while( $row = $result->fetch_assoc() ){
echo $row['fname'] . " " . $row['lname'] ;
echo " made these comments: " . substr($row['comments'],0,150) ;
echo "<br/>";
}
$mydb->close ();

Using MySQLi Object Interface with PHP to insert data

The following is an example of using MySQLi Object Interface with PHP to insert data into a database table.

$mydb = new mysqli('localhost', 'username', 'password', 'databasename');
$sql = "INSERT INTO users (fname, lname, comments)
VALUES ('$_POST[fname]', '$_POST[lname]', '$_POST[comments]')";
if ($mydb->query($sql) == TRUE) {
echo "user entry saved successfully.";
} else {
echo "INSERT attempt failed" ;
}
$mydb->close();

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;
    }
}
?>

Latest Tutorials & Code Snippets

Reference Guides