We help IT Professionals succeed at work.

Retrieving SQL Data via HTML

414 Views
Last Modified: 2012-05-29
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>
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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?

Author

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

Author

Commented:
if it does not support it, is there another solution?
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Move to one that does.  Also, have you verified your access info to the database?
Olaf DoschkeSoftware Developer
CERTIFIED EXPERT

Commented:
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.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.