Snippet

Connecting to a MySQL database in PHP and executing SQL queries


PHP code snippets demonstrating the usage of some common PHP-MySQL functions. There are sample codes showing primitive use of functions to establish connection to a MySQL server and handle SQL queries like SELECT, UPDATE, etc.


PHP Snippet
mysqlconnect.php

<?php

$db_host = "127.0.0.1"; $db_name = "databaseName"; $db_username = "userName"; $db_password = "password";

if (!$db_connect = mysql_connect($db_host, $db_username, $db_password))

die('Unable to connect to MySQL.');

if (!$db_select = mysql_select_db($db_name, $db_connect))

die('Unable to connect to the database.');

?>

This file will handle initiating connections to MySQL. This means that to connect with the MySQL server from a PHP file, we would just need to include the file mysqlconnect.php as shown below in examples.


Example 1


Suppose there is a table named users. It has atleast two columns, id and name. We want a PHP file that accepts id as a GET argument and returns the name corresponding to that id.

getname.php

<?php

include ('mysqlconnect.php');

$id = $_GET['id'];

$result = mysql_query("SELECT * FROM students WHERE id = '$id';");

$user = mysql_fetch_array($result); //Fetch first row from the $result array

print $user['name']; // Print value of "name" column in the fetched row

?>


Example 2


Let there be a table named users containing columns named username and password. This is a very simple authentication login system that checks if the username and password entered by a user are correct.

logincheck.php

<?php

include ('mysqlconnect.php');

$username = $_GET['username'];

$password = $_GET['password'];

$result = mysql_query("SELECT * FROM users WHERE username LIKE '$username' AND password LIKE '$password';");

$count = mysql_num_rows($result); // Count number of rows returned in the query

if($count==0)

echo "Wrong Credentials";

else

echo "Login Successful";

?>


Example 3


This example demonstrates another primitive authentication system feature. It is also the first example where the PHP script causes a modification in database. First, the username and password are verified. If correct, the password is replaced with a new password provided as a POST argument.

changePassword.php

<?php

include ('mysqlconnect.php');

$uname=$_POST['uname'];

$pass = $_POST['pass']; $passnew = $_POST['newpass'];

$result_names = mysql_query("SELECT * FROM users WHERE username LIKE '$uname' AND password LIKE '$pass';");

$count=mysql_num_rows($result_names);

if($count==0)

echo "Incorrect Credentials";

else

{

mysql_query("UPDATE users SET password = '$passnew' WHERE username LIKE '$uname' ;");

echo "Password Update Successul";

}

?>

Rohan Raja

Recently graduated, majoring in Mathematics and Computing from IIT Kharagpur, Rohan is a technology enthusiast and passionate programmer. Likes to apply Mathematics and Artificial Intelligence to devise creative solutions to common problems.