[solved] Display database entries in HTML table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

[solved] Display database entries in HTML table?

Post by Sindarin »

So here I am trying to make my own news system for a project.
I made a php file that makes a table 'news' in the database. Then I stored in the cells of the table some values:

Code: Select all

<?php
 
//Get the variables from the form and connect to database
 
$db_connection = mysql_connect("$_POST[db_host]","$_POST[db_username]","$_POST[db_password]");
 
//Check if connection to the database was successful
 
if (!$db_connection)
  {
  die("<font color='red'>Error</font>: Could not connect to database: " . mysql_error());
  }
 echo "<font color='blue'>Success</font>: Connection established.<br>";
  
//Select database to connect to,
 
$db_database=mysql_select_db("$_POST[db_database]", $db_connection);
 
// Check if could connect to particular database,
 
if (!$db_database)
  {
  die("<font color='red'>Error</font>: Could not connect to the particular database: " . mysql_error());
  }
 echo "<font color='blue'>Success</font>: Connection established to database $_POST[db_database].<br>";
 
// Create table in my database
$db_table = "CREATE TABLE news
(
news_id varchar(1024),
news_title varchar(1024),
news_content varchar(1024),
news_tags varchar(1024)
)";
 
// Check if table exists,
 
if (!mysql_query($db_table,$db_connection))
  {
  die("<font color='red'>Error</font>: " . mysql_error());
  }
echo "<font color='blue'>Success</font>: Table was created.<br>";
 
//Insert the variable values
 
$db_insert="INSERT INTO news (news_id, news_title, news_content, news_tags)
VALUES
('1','my title','my content','my tags')";
 
if (!mysql_query($db_insert,$db_connection))
  {
  die("<font color='red'>Error</font>: " . mysql_error());
  }
echo "<font color='blue'>Success</font>: Default article was added successfully.";
 
 
//Various  tests & Close connection
 
$db_insert="INSERT INTO news (news_id, news_title, news_content, news_tags)
VALUES
('2','my title2','my content3','my tags4')";
 
if (!mysql_query($db_insert,$db_connection))
  {
  die("<font color='red'>Error</font>: " . mysql_error());
  }
echo "<font color='blue'>Success</font>: Second article was added successfully.";
 
//select news and display them..... but how do I display them in a table form????
$db_select = 'SELECT * FROM `news` ORDER BY `news`.`news_id` ASC LIMIT 0, 30 ';
 
if (!mysql_query($db_select,$db_connection))
  {
  die("<font color='red'>Error</font>: " . mysql_error());
  }
echo "<font color='blue'>Success</font>: I am going to list your entries now: <br/>$db_select<br/>";
 
mysql_close($db_connection);
?>
In the last part you'll notice I tried to select 30 news entries and display them, but they wont show up (The sql query code shows up).
How do I return those entries in the form of an HTML table?
In phpMyAdmin the table shows correctly.

And also, can someone explain me the difference between varchar, int etc.?
And what's the best to use? I use varchar all the time.
Last edited by Sindarin on Sat Aug 30, 2008 4:05 am, edited 2 times in total.
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: Display database entries in HTML table?

Post by lukewilkins »

As for what field types to use in your database, look here: http://help.scibit.com/Mascon/masconMyS ... Types.html
Using varchar all of the time is probably not smart. As well as having unneeded weight in your database, you can also use different field types to ensure clean values.

For displaying your rows in a table:

Code: Select all

$query = "SELECT * FROM `news` ORDER BY `news`.`news_id` ASC LIMIT 0, 30";
$result = mysql_query($query,$db_connection);
 
echo "<table>\n<tr>";
for($i=0; $i < mysql_num_fields($result); $i++) {
     echo "<th>" . mysql_field_name($result, $i) . "</th>";
}
$table .= "</tr>\n";
while($row = mysql_fetch_row($result)) {
     echo "<tr>\n";
     foreach($row as $value) {
          echo "<td>" . $value . "</td>";
     }
     echo "</tr>\n";
}
echo "</table>\n";
I don't see anything in your code now that is trying to display your rows. Hope that helps.
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Display database entries in HTML table?

Post by Sindarin »

Ah I see now, A question,
while($row = mysql_fetch_row($result))
wouldn't mysql_fetch_array do quite the same?
As for what field types to use in your database, look here: http://help.scibit.com/Mascon/masconMyS ... Types.html
Thanks for the link. But it confused me a little. I use varchar, but it says that it accepts up to 255 characters.
What I should use for text and numbers for over 255 characters?

Also I changed the table creation code to,
// Create table in my database
$db_table = "CREATE TABLE news
(
news_id int(10) auto_increment,
news_title varchar(1024),
news_content varchar(1024),
news_tags varchar(1024)
)";
seeing as I need the news_id column to auto increment, I changed the code but sql throws an error saying:
Incorrect table definition; There can be only one auto column and it must be defined as a key.
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: Display database entries in HTML table?

Post by lukewilkins »

Hey Sindarin,

Yes, mysql_fetch_array() will work just like mysql_fetch_row() except it will depend on the $result_type parameter to determine what type of array to return (associative array, a numeric array, or both) ... see http://us3.php.net/manual/en/function.m ... -array.php

If I remember correctly, mysql_fetch_row() is marginally faster than mysql_fetch_array().

As for your field types, MySQL is throwing the errors on table creation probably because VARCHAR(1024) is invalid since the max VARCHAR is 255. For storing large chunks of data, look in to the BLOB field types. Note however that I try to lean away from BLOB fields as it adds a significant weight to your database. I understand that sometimes it is inevitable... You can google MySQL BLOB fields to learn more about this.

Hope that helps!
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Display database entries in HTML table?

Post by Sindarin »

As for your field types, MySQL is throwing the errors on table creation probably because VARCHAR(1024) is invalid since the max VARCHAR is 255. For storing large chunks of data, look in to the BLOB field types. Note however that I try to lean away from BLOB fields as it adds a significant weight to your database. I understand that sometimes it is inevitable... You can google MySQL BLOB fields to learn more about this.
I set VARCHAR to 999999 and I get no errors(?). :? I need to store large amounts of text in it and I don't really want to set a limit.

The problem was that I needed to set news_id as primary key before I set auto_increment on it.

Whew.. I learned so much sql today. My news system currently has add,edit,delete,sorting and search! :D
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: Display database entries in HTML table?

Post by lukewilkins »

Sindarin, glad it is working for you now. I'm unsure why it would even let you do a VARCHAR(999999).

Good luck to you.
Luke
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Display database entries in HTML table?

Post by Sindarin »

Thanks, I looked more into that link about data types and I think TEXT or MEDIUMTEXT would be best to use. (don't know how I missed those)
It's wise not to try and exploit MySql. I don't want any surprises when the site goes live. :D
Post Reply