Displaying entire table data in html table problem

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

Displaying entire table data in html table problem

Post by Sindarin »

The first script works ok.

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'><b>Error</b></font>: Could not connect to database: " . mysql_error());
  }
 echo "<font color='navy'><b>Connection Established</b>.<br></font>";
  
//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'><b>Error</b></font>: Could not connect to the particular database: " . mysql_error());
  }
 echo "<font color='blue'><b>Success</b></font>: Connection established to database <b>$_POST[db_database]</b>.<br>";
 
 //get the table name
$db_table_name=$_POST[db_table];
 
// Create table in my database
$db_table = "CREATE TABLE $db_table_name
(
db_host varchar(128),
db_username varchar(128),
db_password varchar(512)
)";
 
// Check if table exists,
 
if (!mysql_query($db_table,$db_connection))
  {
  die("<font color='red'><b>Error</b></font>: " . mysql_error());
  }
echo "<font color='blue'><b>Success</b></font>: Table <b>$db_table_name</b> was created.<br>";
 
//Convert password to md5 string
 
$db_password = md5($_POST[db_password]);
echo "<font color='blue'><b>Success</b></font>: Converted password to <b>md5</b>.<br>";
 
//Insert the variable values
 
$db_insert="INSERT INTO $db_table_name (db_host, db_username, db_password)
VALUES
('$_POST[db_host]','$_POST[db_username]','$db_password')";
 
if (!mysql_query($db_insert,$db_connection))
  {
  die("<font color='red'><b>Error</b></font>: " . mysql_error());
  }
echo "<font color='blue'><b>Success</b></font>: <b>Variables</b> were configured successfully.<br>";
 
//Close connection
 
mysql_close($db_connection);
echo "<font color='navy'><b>Connection Closed</b></font><br>";
?>
This one above successfully creates a new table in the database in where it places database username,host and password in 3 cells.

and now I want to read all the rows and display all the data in an html table,

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'><b>Error</b></font>: Could not connect to database: " . mysql_error());
  }
 echo "<font color='navy'><b>Connection Established</b>.<br></font>";
  
//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'><b>Error</b></font>: Could not connect to the particular database: " . mysql_error());
  }
 echo "<font color='blue'><b>Success</b></font>: Connection established to database <b>$_POST[db_database]</b>.<br>";
 
 //get the table name
$db_table_name=$_POST[db_table];
 
// Show tables from database
$db_table_read = "SELECT * FROM $db_table_name";
 
if (!mysql_query($db_table_read,$db_connection))
  {
  die("<font color='red'><b>Error</b></font>: " . mysql_error());
  }
 echo "<font color='blue'><b>Success</b></font>: Fetching database contents...<br><br>";  
while ($db_table_contents = mysql_fetch_array($db_table_read)) {
    echo $db_table_contents;
    }
 
//Close connection
 
mysql_close($db_connection);
echo "<br><font color='navy'><b>Connection Closed</b></font><br>";
?>
This one upon execution it gives me:
Connection Established.
Success: Connection established to database my_test.
Success: Fetching database contents...


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/myuser/public_html/tests/sql/show.php on line 38

Connection Closed
Any help on this?
Last edited by Sindarin on Thu Mar 06, 2008 8:16 am, edited 1 time in total.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Displaying entire table data in html table problem

Post by EverLearning »

mysql_fetch_array() expects resource type as the first argument(returned by call to mysql_query), and you're passing SQL statement string.

In order for your code to work you need this:

Code: Select all

if (!mysql_query($db_table_read,$db_connection))
changed to

Code: Select all

if (!($result = mysql_query($db_table_read,$db_connection)))
and

Code: Select all

mysql_fetch_array($db_table_read)
changed to

Code: Select all

mysql_fetch_array($result)
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Displaying entire table data in html table problem

Post by Sindarin »

It now outputs:
Connection Established.
Success: Connection established to database my_test.
Success: Fetching database contents...

Array
Connection Closed
yet no data from the table cells...

EDIT

I just changed my query code to:

Code: Select all

// Show tables from database
$db_table_read = "SELECT * FROM $db_table_name";
 
if (!($result = mysql_query($db_table_read,$db_connection)))
  {
  die("<font color='red'><b>Error</b></font>: " . mysql_error());
  }
 echo "<font color='blue'><b>Success</b></font>: Fetching database contents...<br>";  
while ($db_table_contents = mysql_fetch_array($result)) {
    echo "$db_table_contents[db_host]<br>";
        echo "$db_table_contents[db_username]<br>";
                echo "$db_table_contents[db_password]";
    }
it successfully returns:
Connection Established.
Success: Connection established to database my_test.
Success: Fetching database contents...
localhost
myuser
42119331e4c41de6889355a7ce45789f
Connection Closed
But I have to ask, so far I have one entry in each cell. If I have more, in what way would that output it?
Would it be like:
Connection Established.
Success: Connection established to database my_test.
Success: Fetching database contents...
localhost
myuser1
42119331e4c41de6889355a7ce45789f
host2
myuser2
28119331e4c41dsa889355a7cef5789f
host3
myuser3
62119381e4c41bsa889357a7cef5789f
Connection Closed
depending on what sort I specify?
Post Reply