Avatar of Hollie18502
Hollie18502
 asked on

Retrieving SQL Data via HTML

I am trying to create a table by retrieving data from sql table via html page.  this is what i am using... could you please tell me what's wrong?

      <html>
      <head>
      <title>Retrieve data from database </title>
      </head>
      <body>

      <?php
      // Connect to database server
      mysql_connect("livp3qtm01", "userid", "password") or die (mysql_error ());

      // Select database
      mysql_select_db("dept225") or die(mysql_error());

      // SQL query
      $strSQL = "SELECT director FROM auditplanitems";

      // Execute the query (the recordset $rs contains the result)
      $rs = mysql_query($strSQL);
      
      // Loop the recordset $rs
      // Each row will be made into an array ($row) using mysql_fetch_array
      while($row = mysql_fetch_array($rs)) {

         // Write the value of the column FirstName (which is now in the array $row)
        echo $row['director'] . "<br />";

        }

      // Close the database connection
      mysql_close();
      ?>
      </body>
      </html>
HTMLMicrosoft SQL ServerPHP

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Randy Downs

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dave Baldwin

There is nothing wrong with the code you have posted.  I made a copy and put my own info in the without any other changes and it works perfectly.  What errors are you seeing?  Does your server support PHP?  Are you running it on a web server that supports PHP?  Does the file name have a 'php' extension so the web server will know to run it thru the PHP interpreter?
Hollie18502

ASKER
when i run it, it comes up with a blank page.  i will check to see if the server supports php.
Hollie18502

ASKER
if it does not support it, is there another solution?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dave Baldwin

Move to one that does.  Also, have you verified your access info to the database?
Olaf Doschke

If you don't even know, if the server supports php, start small, how about this:

<html><body>
<p><?php echo 'test'; ?></p>
</body></html>

Open in new window


Next thing you could test is:
<?php phpinfo(); ?>

Open in new window


Bye, Olaf.
Ray Paseur

The "blank page" syndrome usually means that your error reporting levels are too low.  You definitely want to see all of the error messages, warnings and notices.  Here is a teaching example that shows some of the basics in PHP and MySQL, including how to test for success and visualize any error conditions.  

You might also want to get this excellent little book and work through a few of the chapters.  It will not make you a pro, but it is easy to read and has great examples.
http://www.sitepoint.com/books/phpmysql5/

<?php // RAY_mysql_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $err <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!mysql_select_db($db_name, $db_connection))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $err <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE ALL DATA FIELDS BEFORE USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/> $sql <br/>";
    die($err);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
$fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/> $sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $fmt ROWS OF DATA ";
    echo "<br/> $sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    var_dump($row);
}




// ANOTHER WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($err);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/> $sql <br/>";
    die($err);
}

// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.