MySQL and PDO

This will display all of the links and descriptions

//change these
$user = 'root';
$pass = '';
try
{
//localhost, test database
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
//table is called test
foreach($dbh->query('SELECT * from test') as $row)
{
print "{$row['name']} : {$row['desc']}
";
}
$dbh = null;
}
catch (PDOException $e)
{ print "Error!: " . $e->getMessage() . "
";
die();
}
?>

Count the number of rows in a MySQL database

Often there may be a requirement to count the number of entries in a MySQL table.


//connect to server with username and password
$connection = mysql_connect ("localhost","username", "password") or die ("Cannot make the connection");
//connect to database
$db = mysql_select_db ("test",$connection) or die ("Cannot connect to database");
//our SQL query
$sql_query = "SELECT * FROM test";
//store the SQL query in the result variable
$result = mysql_query($sql_query);
$rows = mysql_num_rows($result);
//output total
echo $rows;
?>

Random entry from MySQL table

This example displays one random link from the example database mentioned below

//connect to server with username and password
$connection = mysql_connect ("localhost","username", "password") or die ("Cannot make the connection");
//connect to database
$db = mysql_select_db ("test",$connection) or die ("Cannot connect to database");
//our SQL query
$sql_query = "SELECT * FROM test ORDER BY RAND() LIMIT 1";
//store the SQL query in the result variable
$result = mysql_query($sql_query);
if(mysql_num_rows($result))
{
//output as long as there are still available fields
while($row = mysql_fetch_row($result))
{
echo ("$row[3]");
echo (": $row[4]
");
}
}
//if no fields exist
else
{
echo "no values in the database";
}
?>

Display X entries from MySQL table

This example will display only 3 entries from the sample MySQl database

//connect to server with username and password, this is the default settings
//when MySQL is installed on Windows XP(Not recommended)
$connection = mysql_connect ("localhost","root", "") or die ("Cannot make the connection");
//connect to database
$db = mysql_select_db ("test",$connection) or die ("Cannot connect to database");
//our SQL query
$sql_query = "SELECT * FROM test LIMIT 3";
//store the SQL query in the result variable
$result = mysql_query($sql_query);
if(mysql_num_rows($result))
{
//output as long as there are still available fields
while($row = mysql_fetch_row($result))
{
echo ("$row[3]");
echo (": $row[4]
");
}
}
//if no fields exist
else
{
echo "no values in the database";
}
?>

Display all entries in a MySQL table

This shows all the available rows in a MySQL table

//when MySQL is installed on a Windows XP rig (Not recommended)
$connection = mysql_connect ("localhost","root", "") or die ("Cannot make the connection");
//connect to the test database
$db = mysql_select_db ("test",$connection) or die ("Cannot connect to database");
//our SQL query
$sql_query = "SELECT * FROM test";
//store the SQL query in the result variable
$result = mysql_query($sql_query);
if(mysql_num_rows($result))
{ //output as long as there are still available fields
while($row = mysql_fetch_row($result))
{
echo ("$row[3]");
echo (": $row[4]
");
}
}
//if no fields exist
else
{
echo "no values in the database";
}
?>

create a table in a MySQL database

This shows how to create a table in a MySQL database

//connect to your MySQL server using your servername , username
//and password
$conn = mysql_connect("localhost" , "username" , "password");
//if you cannot connect display an error message and exit
if ($conn == false)
{
echo mysql_errno().": ".mysql_error()."
";
exit;
}
//create a table called contact , this will contain 2 fields .
//fullname to store a name and email to store an email address
$query = "create table contact" .
"(fullname varchar(255), email varchar(255))";
//store this in $result variable
$result = mysql_db_query ("mydb", $query);
//if successful display this message
if ($result)
echo "Table 'mydb' was successfully created!";
//if unsuuccessful display the error message
else
echo mysql_errno().": ".mysql_error()."
";
mysql_close ();
?>

list all your MySql databases using PHP

A bit of a beginner example which shows how to list all of your MySQL databases

$server = "localhost";
$username = "root";
$password = "";
$con = mysql_connect("$server","$username","$password")or die ("Couldn't connect to MySQL Server.");
$listdb = mysql_list_dbs($con);
while ($row = mysql_fetch_object($listdb))
{
echo $row->Database . "
";
}
?>

MySQL Dump for PHP examples

This is a sample MySQL data that we will use in several of our examples



CREATE TABLE `test` (
`id` int(4) NOT NULL auto_increment,
`cat` varchar(10) NOT NULL default '',
`url` varchar(50) NOT NULL default '',
`name` varchar(50) NOT NULL default '',
`desc` varchar(100) NOT NULL default '',
KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=10;

#
# Dumping data for table `test`
#

INSERT INTO `test` VALUES (1, 'search', 'http://www.google.com', 'google', 'The best search engine and directory');
INSERT INTO `test` VALUES (2, 'search', 'http://yahoo.com', 'yahoo', 'The mighty yahoo');
INSERT INTO `test` VALUES (3, 'programming', 'http://www.programmershelp.co.uk', 'programmershelp', 'Various programming resources');
INSERT INTO `test` VALUES (4, 'programming', 'http://www.programmershelp.net', 'Programmers Help', 'Programmers Help');
INSERT INTO `test` VALUES (5, 'programming', 'http://www.beginnersphp.co.uk', 'Beginners PHP', 'Beginners PHP site');
INSERT INTO `test` VALUES (6, 'misc', 'http://www.maxidirectory.com', 'Maxi directory', 'maxi directory');
INSERT INTO `test` VALUES (7, 'misc', 'http://www.holed.net', 'Holed golf', 'Holed golf');
INSERT INTO `test` VALUES (8, 'misc', 'http://www.worldipod.com', 'DWorld Ipod', 'All about the IPod and Iphone');
INSERT INTO `test` VALUES (9, 'misc', 'http://www.acaiplace.info', 'Acai Place', 'Acai information');