PHP | MySQL ( Creating Table )
What is a table?
In relational databases, and flat file databases, a table is a set of data elements using a model of vertical columns and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows.
Creating a MySQL Table Using MySQLi and PDO
We have already learned about creating databases in MySQL from PHP in this article. The steps to create table are similar to creating databases. The difference is instead of creating a new database we will connect to existing database and create a table in that database. To connect to an existing database we can pass an extra variable “database name” while connecting to MySQL.
The CREATE TABLE statement is used to create a table in MySQL.
In this article, a table named “employees”, with four columns: “id”, “firstname”, “lastname” and “email” will be created.
The data types that will be used are :
- VARCHAR:Holds a variable length string that can contain letters, numbers, and special characters. The maximum size is specified in parenthesis.
- INT :he INTEGER data type accepts numeric values with an implied scale of zero. It stores any integer value between -2147483648 to 2147483647.
The attributes that are used along with data types in this article are:
- NOT NULL: Each row must contain a value for that column, null values are not allowed.
- PRIMARY KEY: Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number.
Creating tables in three different versions are described below:
- Creating table using MySQLi Object-oriented Procedure
Syntax :
// checking connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) < die("Connection failed: " . $conn->connect_error); > // sql code to create table $sql = "CREATE TABLE employees( id INT(2) PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50) )"; if ($conn->query($sql) === TRUE) < echo "Table employees created successfully"; >else < echo "Error creating table: " . $conn->error; > $conn->close(); ?> 
Output :
Creating table using MySQLi Procedural procedure
Syntax :
// Checking connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) < die("Connection failed: " . mysqli_connect_error()); >// sql code to create table $sql = "CREATE TABLE employees ( id INT(2) PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50) )"; if (mysqli_query($conn, $sql)) < echo "Table employees created successfully"; >else < echo "Error creating table: " . mysqli_error($conn); >mysqli_close($conn); ?>
Output :
Creating table using PDO procedure
Syntax :
// setting the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // sql code to create table $sql = "CREATE TABLE employees ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50) )"; // using exec() because no results are returned $conn->exec($sql); echo "Table employees created successfully"; > catch(PDOException $e) < echo $sql . "
" . $e->getMessage(); > $conn = null; ?>
Output :
display data from SQL database into php/ html table [closed]
Want to improve this question? Update the question so it focuses on one problem only by editing this post.
I have a database on MySQL and I want to display one of my SQL tables on a HTML or PHP table. I have searched online and cannot implement this feature. Could someone please help me with the coding?
database = 'hrmwaitrose' username = 'root' host = 'localhost' What you need is more than StackOverflow can supply. I suggest getting a book on introductory PHP, or else Google for something like «PHP SQL tutorial».
4 Answers 4
PHP provides functions for connecting to a MySQL database.
$connection = mysql_connect('localhost', 'root', ''); //The Blank string is the password mysql_select_db('hrmwaitrose'); $query = "SELECT * FROM employee"; //You don't need a ; like you do in SQL $result = mysql_query($query); echo ""; // start a table tag in the HTML while($row = mysql_fetch_array($result))< //Creates a loop to loop through results echo "" . htmlspecialchars($row['name']) . " " . htmlspecialchars($row['age']) . "
"; //Close the table in HTML mysql_close(); //Make sure to close out the database connection In the while loop (which runs every time we encounter a result row), we echo which creates a new table row. I also add a to contain the fields.
This is a very basic template. You see the other answers using mysqli_connect instead of mysql_connect. mysqli stands for mysql improved. It offers a better range of features. You notice it is also a little bit more complex. It depends on what you need.
Please note that «mysql_fetch_array» is now deprecated since PHP 5.5.0, and it was removed in PHP 7.0.0. So please take a look in «mysqli_fetch_array()» instead.
Create HTML table from sql table
I have a table in a SQL database with the following fields: ID, Name, Email, University, Languages, and Experience. I want to create an html table that fetches data from SQL and outputs the last 10 results? How would I do that? I’m sorry if this is a very simple question, I have very little knowledge in PHP and SQL. Here’s the code I have right now that just displays the name and not in a table:
mysql_select_db("apploymentdevs"); $results = mysql_query("SELECT * FROM demo"); while($row = mysql_fetch_array($results)) < echo $row['Name'] . ""; ?> 6 Answers 6
Here is something that should help you to create the table and get more knowledge of php and mysql.
Also you should move your connection logic and query to the beginning of your process, thus avoiding errors while loading the page and showing a more accurate error than just the mysql_error.
Edit: If your ids are incrementing, then you could add the ORDER BY clause,
change: SELECT * FROM demo LIMIT 10 to: SELECT * FROM demo LIMIT 10 ORDER BY id
Id Name ?>
I’m not one to constantly bring up why you should avoid mysql_* commands, but when you’re using them in answers to those who are just learning the language, it is potentially counter-productive.
I got very annoyed pasting together the same code over and over again to build HTML tables from SQL queries.
So, here we go with a function that takes mysqli results and pastes them together to an plain simple HTML table that has column names according to those in the database:
function sql_to_html_table($sqlresult, $delim="\n") < // starting table $htmltable = "" . $delim ; $counter = 0 ; // putting in lines while( $row = $sqlresult->fetch_assoc() )< if ( $counter===0 ) < // table header $htmltable .= "" . $delim; foreach ($row as $key => $value ) < $htmltable .= "" . $key . " " . $delim ; > $htmltable .= " " . $delim ; $counter = 22; > // table body $htmltable .= "" . $delim ; foreach ($row as $key => $value ) < $htmltable .= "" . $value . " " . $delim ; > $htmltable .= " " . $delim ; > // closing table $htmltable .= "
" . $delim ; // return return( $htmltable ) ; > Example Usage:
$DB = new mysqli("host", "username", "password", "database"); $sqlresult = $DB->query( "SELECT * FROM testtable LIMIT 1 ;" ) ; echo sql_to_html_table( $sqlresult, $delim="\n" ) ; Im confused where you put this in your html file. Is the top part in a php section and the bottom also within a php?
Yes, of cause, exactly as you are saying. This is only the php-code that you still have to put between . For best practice the first code block would go into a separate file and made available via include ‘file/path/my_fiel.php’ .
Calling table( $result ); on your query results will generate a html based table regardless of the size of the sql array you feed it. I hope this helps 🙂
fetch_array( MYSQLI_ASSOC ); echo ''; tableHead( $result ); tableBody( $result ); echo '
'; > function tableHead( $result ) < echo ''; foreach ( $result as $x ) < echo ''; foreach ( $x as $k => $y ) < echo '' . ucfirst( $k ) . ' '; > echo ' '; break; > echo ''; > function tableBody( $result ) < echo ''; foreach ( $result as $x ) < echo ''; foreach ( $x as $y ) < echo '' . $y . ' '; > echo ' '; > echo ''; > You might be interested in fetching with mysql_fetch_assoc() (so that you get the data in an associative array : keys=>value). In the keys are your column names ; so, for each line, you can loop through each column (with array_keys() ) and print its value.
$results = mysql_query("SELECT * FROM demo"); while($row = mysql_fetch_assoc($results)) < foreach (array_keys($row) as $column) < echo $row[$key] . ""; > > (After that, you can cache array_keys($row) in a variable which is only set once, because its value won’t change while you run through the results.)
Even though it’s been a while I’m going to put my 2 cents in: use functions (even better — classes). Creating tables from mysql results is a very common task.
return $args; > /** * Connect to db * * @param string $db_host * @param string $db */ function db_connect($db_host = "localhost", $db = "apploymentdevs") < $connect = mysql_connect($db_host,"root", "root"); if (!$connect) < die(mysql_error()); >mysql_select_db($db); > /** * Create a table from a result set * * @param array $results * @return string */ function createTable(array $results = array()) < if (empty($results)) < return 'Empty Result Set
'; > // dynamically create the header information from the keys // of the result array from mysql $table = ''; $keys = array_keys(reset($results)); $table.=''; foreach ($keys as $key) < $table.=''.$key.' '; > $table.=' '; // populate the main table body $table.=''; foreach ($results as $result) < $table.=''; foreach ($result as $val) < $table.=''.$val.' '; > $table.=' '; > $table.='
'; return $table; >