TwitterFacebookGoogle

web development tutorials

Monthly Archives: April 2010

list tables in mysql database with php

Posted on by Posted in PHP/mysql

How to display mysql tables in given database using php

we know that it common practice to list database tables in mysql related backup php scripts.we learn here how to display the tables in given mysql database. for that we need to select a database and create some test tables with below queries .

CREATE TABLE  `testtable` (
  `test_id` int(10) unsigned NOT NULL auto_increment,
  `test_name` varchar(45) NOT NULL,
  `test_created` datetime NOT NULL,
  PRIMARY KEY  (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
CREATE TABLE  `testtable1` (
  `test_id1` int(10) unsigned NOT NULL auto_increment,
  `test_name1` varchar(45) NOT NULL,
  `test_created1` datetime NOT NULL,
  PRIMARY KEY  (`test_id1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
CREATE TABLE  `testtable2` (
  `test_id2` int(10) unsigned NOT NULL auto_increment,
  `test_name2` varchar(45) NOT NULL,
  `test_created2` datetime NOT NULL,
  PRIMARY KEY  (`test_id2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 
CREATE TABLE  `testtable3` (
  `test_id3` int(10) unsigned NOT NULL auto_increment,
  `test_name3` varchar(45) NOT NULL,
  `test_created3` datetime NOT NULL,
  PRIMARY KEY  (`test_id3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Code Example

//replace it with your host normally it could be localhost
$hostname='localhost';
//mysql user name
$username='root';
//mysql user password
$password='';
//connect to the mysql server
$ss = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
//select a database on the mysql server
//please change as you like the database name
mysql_select_db('database');
 
//SHOW COLUMNS FROM TABLENAME
$query=mysql_query('SHOW TABLES ') or die(mysql_error());
 
//iterate trough the query result and fetch each field
while($field=mysql_fetch_row($query)){
$fields[]=$field[0];//collect each field into a array
};
 
print_r($fields);//test the fields array

Output

Array
(
    [0] => testtable
    [1] => testtable1
    [2] => testtable2
    [3] => testtable3
)

print mysql table fields

Posted on by Posted in PHP/mysql

How to display mysql table fields using PHP

It is common to find the mysql table field names in the given table while creating dynamic models for our web applications.To continue this tutorial we have to create a test table. Below mysql query is used to create this test table.

CREATE TABLE  `testtable` (
  `test_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `test_name` VARCHAR(45) NOT NULL,
  `test_created` DATETIME NOT NULL,
  PRIMARY KEY (`test_id`)
)
ENGINE = InnoDB;

SHOW COLUMNS FROM TABLENAME query is used for list the mysql fields.

below code lists the mysql table fields.

<?php
//replace it with your host normally it could be localhost
$hostname='localhost';
//mysql user name
$username='username';
//mysql user password
$password='password';
//connect to the mysql server
$ss = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
//select a database on the mysql server
//please change as you like the database name
mysql_select_db('mydatabase');
 
//SHOW COLUMNS FROM TABLENAME
$query=mysql_query('SHOW COLUMNS FROM testtable ') or die(mysql_error());
 
//iterate trough the query result and fetch each field
while($field=mysql_fetch_object($query)){
$fields[]=$field;//collect each field into a array
};
 
print_r($fields);//test the fields array
 
foreach($fields as $key=>$field){
echo $field->Field.'</br>'; // print each field name
}
 
?>