Select data From mysql

When you need data from your mysql database to show in your web page, you need to select database, table and what's row you want to pull its data first.

Syntax

// Select all columns from all rows.
"SELECT * FROM table_name";

or
// Select some column from all rows.
"SELECT column_name1, column_name2 FROM table_name";

or
// Select all coulumns from one row.

"SELECT * FROM table_name WHERE column_name=' value in column '";

Overview

In this tutorial, we'll create only 1 file.
1. select.php

Steps
1. Create table "test_mysql" in database "test".
2. Create file select.php.
3. test it!

If you don't want looping rows in mysql, replace

while($rows=mysql_fetch_array($result)){
........
<?php
}
mysql_close();
?>


replace with this
$rows=mysql_fetch_array($result);
.........
<?php
mysql_close();
?>


STEP1: Create table "test_mysql"

In this step, you have to create a table and insert data for testing our code.


CREATE TABLE `test_mysql` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(65) NOT NULL default '',
`lastname` varchar(65) NOT NULL default '',
`email` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

--
-- Dumping data for table `test_mysql`
--

INSERT INTO `test_mysql` VALUES (1, 'Billly', 'Blueton', 'bb5@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (2, 'Jame', 'Campbell', 'jame@somewhere.com');
INSERT INTO `test_mysql` VALUES (3, 'Mark', 'Jackson', 'mark@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (4, 'Linda', 'Travor', 'lin65@phpeasystep.com');
INSERT INTO `test_mysql` VALUES (5, 'Joey', 'Ford', 'fordloi@somewhere.com');
INSERT INTO `test_mysql` VALUES (6, 'Sidney', 'Gibson', 'gibson@phpeasystep.com');

STEP2: Create file - Select.php

<?php

$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="test_mysql"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Retrieve data from database
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>

 

<table width="400" border="1" cellspacing="0" cellpadding="3">

 

<?php

// Start looping rows in mysql database.
while($rows=mysql_fetch_array($result)){
?>


<tr>
<td width="10%"><? echo $rows['id']; ?></td>
<td width="30%"><? echo $rows['name']; ?></td>
<td width="30%"><? echo $rows['lastname']; ?></td>
<td width="30%"><? echo $rows['email']; ?></td>
</tr>

<?php
// close while loop
}

</table>

?>


<?php
// close MySQL connection
mysql_close();
?>

 

STEP3: Run the Code


Similar Post
Connecting to MySQL database

Connect to MySQL database

This tutorial will show you how to connect to mysql database. It's easy to write a script to connect to world's most popular opensource database.
Order MySQL results

Order MySQL results

In this tutorial, you'll learn how to sort MySQL result. You can sort MySQL result by ascending, descending and random MySQL result.