MySQL Select Query is used to select the records from MySQL database tables in php
how to use MySQL Select Query is used to select the records from MySQL database tables using php.
PHP Select Data From MySQL(MySQL Select Query)
PHP Select Data From MySQL database name :: onlinecode_db table name :: user_table +----+------------+-----------+-----------------------------+ | id | first_name | last_name | user_email | // column name +-----------+------------+-----------+----------------------+ | 1 | Peter | Kong | peterKong@mail.com | | 2 | John | smith | johnsmith@mail.com | | 3 | Shuvo | Kent | Shuvokent@mail.com | | 4 | John | Habib | johnHabib@mail.com | | 5 | Anthony | Potter | Anthonypott@mail.com | +-----------+------------+-----------+----------------------+
$database_host = 'localhost'; // database host name
// if using port then add port $database_host = 'localhost:3036';
$database_user = 'database_user'; // database user name
$database_pass = 'database_password'; // database user password
$database_name = 'onlinecode_db'; // database name
// connect with database
$database_conn = mysql_connect($database_host, $database_user, $database_pass);
// check database connection
if(! $database_conn )
{
// error in database connection
die('Could not connect to database : ' . mysql_error());
}
$sql_select_query = 'SELECT id,first_name,last_name,user_email FROM user_table';
mysql_select_db($database_name);
$sql_result = mysql_query( $sql_select_query, $database_conn );
if(! $sql_result )
{
// error in sql query or Fetch data
die('Could not get data: ' . mysql_error());
}
while($result = mysql_fetch_array($sql_result, MYSQL_ASSOC))
{
echo "User ID :{$result['id']}".
"First Name : {$result['first_name']}".
"Last Name : {$result['last_name']}".
"Email Address : {$result['user_email']}";
}
echo "Get data successfully";
// close connection with Mysql database
mysql_close($database_conn);
PHP Select Data From MySQLi Object-Oriented(MySQL Select Query)
$database_host = 'localhost'; // database host name
// if using port then add port $database_host = 'localhost:3036';
$database_user = 'database_user'; // database user name
$database_pass = 'database_password'; // database user password
$database_name = 'onlinecode_db'; // database name
// connect with database
$database_conn = new mysqli($database_host, $database_user, $database_pass, $database_name);
// check database connection
if ($database_conn->connect_error)
{
// error in database connection
die("Could not connect to database : " . $database_conn->connect_error);
}
$sql_select_query = 'SELECT id,first_name,last_name,user_email FROM user_table';
$sql_result = $database_conn->query($sql_select_query);
if ($sql_result->num_rows > 0) {
// output data of each row
while($result = $sql_result->fetch_assoc()) {
echo "User ID :{$result['id']}".
"First Name : {$result['first_name']}".
"Last Name : {$result['last_name']}".
"Email Address : {$result['user_email']}";
}
} else {
echo "No data found";
}
// close connection with MySQLi database
$database_conn->close();
PHP Select Data From MySQLi Procedural(MySQL Select Query)
$database_host = 'localhost'; // database host name
// if using port then add port $database_host = 'localhost:3036';
$database_user = 'database_user'; // database user name
$database_pass = 'database_password'; // database user password
$database_name = 'onlinecode_db'; // database name
// connect with database
$database_conn = mysqli_connect($database_host, $database_user, $database_pass, $database_name);
// check database connection
if (!$database_conn)
{
// error in database connection
die("Could not connect to database : " . mysqli_connect_error());
}
$sql_select_query = 'SELECT id,first_name,last_name,user_email FROM user_table';
$sql_result = mysqli_query($database_conn, $sql_select_query);
if (mysqli_num_rows($sql_result) > 0)
{
// output data of each row
while($result = mysqli_fetch_assoc($sql_result)) {
echo "User ID :{$result['id']}".
"First Name : {$result['first_name']}".
"Last Name : {$result['last_name']}".
"Email Address : {$result['user_email']}";
}
}
else
{
echo "No data found";
}
// close connection with MySQLi Procedural
mysqli_close($database_conn);
PHP Select Data From PDO with Prepared Statements(MySQL Select Query)
$database_host = 'localhost'; // database host name
$database_user = 'database_user'; // database user name
$database_pass = 'database_password'; // database user password
$database_name = 'onlinecode_db'; // database name
try
{
$database_conn = new PDO("mysql:host=$database_host;dbname=$database_name", $database_user, $database_pass);
// if using port then use port id in PDO
//$database_conn = new PDO('mysql:host=$database_host;port=5432;dbname=$database_name', $database_user, $database_pass);
// exception for PDO connection error
$database_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql_select_query = 'SELECT id,first_name,last_name,user_email FROM user_table';
$database_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $database_conn->prepare($sql_select_query);
$stmt->execute(); // execute stmt
// set the resulting(stmt result) array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $key=>$values)
{
echo $key." = ".$values;
}
}
catch(PDOException $exception)
{
// error in database connection
echo "Could not connect to database : " . $exception->getMessage(); // exception
}
// close connection with PDO
$database_conn = null;