MySQL SELECT Statement with PHP

MySQL SELECT statement selects records from MySQL database. We can execute SELECT statement in PHP and get desired results.

Let’s create a demo table for the example in MySQL database, Copy following MySQL code in your PhpMyAdmin. It will create a table name demotable with 4 columns: id, name, email and phone number, and inserts some records for the demo.

[cc lang=”mysql”]
CREATE TABLE IF NOT EXISTS `demotable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`iName` varchar(60) NOT NULL,
`iEmail` varchar(60) NOT NULL,
`iPhoneNumber` varchar(60) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `demotable` (`iName`, `iEmail`, `iPhoneNumber`) VALUES
(‘Katarzyna Halec’, ‘[email protected]’, ‘3984504809’),
(‘Minney Min’, ‘[email protected]’, ‘5768689686’),
(‘Mac Colar’, ‘[email protected]’, ‘7987569870’),
(‘Jonathan Weber’, ‘[email protected]’, ‘6768080345’);
[/cc]

Select All Records

PHP code below connects to MySQL database and executes SELECT statement, which will fetch all records from the database. Save this file in your testing server and execute it by typing its address on browser. You can read comments provided in each line to understand properly.

[cc lang=”php”]
‘. mysql_error());
}

//If everything looks good, Select our Database
mysql_select_db($MySQLDatabase, $MySQLConnection);

//MySQL Statement
//* (Star) Selects all columns from MYSQL
$query = mysql_query(“SELECT * FROM demotable”);

//While loop will loop through records in the recordset and returns each row in $myrow variable.
while($myrow = mysql_fetch_array($query))
{
echo ‘Name : ‘. $myrow[‘iName’].’
‘;
echo ‘Email : ‘. $myrow[‘iEmail’].’
‘;
echo ‘Phone Number : ‘. $myrow[‘iPhoneNumber’].’

‘;
}
//Close MySQL connection
mysql_close($MySQLConnection);
?>
[/cc]

Result :
Name : Katarzyna Halec
Email : [email protected]
Phone Number : 3984504809

Name : Minney Min
Email : [email protected]
Phone Number : 5768689686

Name : Mac Colar
Email : [email protected]
Phone Number : 7987569870

Name : Jonathan Weber
Email : [email protected]
Phone Number : 6768080345

Select Particular Records with WHERE

In-case we need to retrieve only particular results from the record, we can use WHERE with MySQL statement. I have only modified MySQL statement in above PHP code limit the results. Below code will now return records with matching Phone number.

[cc lang=”php”]
‘. mysql_error());
}

//If everything looks good, Select our Database
mysql_select_db($MySQLDatabase, $MySQLConnection);

//MySQL Statement modified
$query = mysql_query(“SELECT * FROM demotable WHERE iPhoneNumber = ‘5768689686’”);

while($row = mysql_fetch_array($query))
{
echo ‘Name : ‘. $row[‘iName’].’
‘;
echo ‘Email : ‘. $row[‘iEmail’].’
‘;
echo ‘Phone Number : ‘. $row[‘iPhoneNumber’].’

‘;
}
//Close MySQL connection
mysql_close($MySQLConnection);
?>
[/cc]

Result :
Name : Minney Min
Email : [email protected]
Phone Number : 5768689686