Retrieving Query Results -- Not connecting to database

[BrentonHale]

I'm not able to retrieve data from the database in my browser (localhost).  There is no error, warnings, notices or anything. 

 

---

 

 

Here is my code for view_users.php

 

[<?php # Script 7.4 - view_users.php
// This script retrieves all the records from the users table.

$page_title = 'View the Current Users';
include ('./header.html');

// Page header.
echo '<h1 id="mainhead">Registered Users</h1>';

require_once ('mysql_connect.php'); // Connect to the db.

$link = mysql_connect('localhost', 'username', 'password');
if ($link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);


// make sitename the current db
$db_selected = mysql_select_db("sitename") or die(mysql_error());
$data = mysql_query("SELECT * FROM users")
or die(mysql_error()); 


// Make the query.
$query = "SELECT CONCAT(last_name, ',', first_name) AS name, DATE_FORMAT (registration_date, '%M %d, %Y') AS dr FROM users ORDER BY registration_date ASC";
$result = @mysql_query ($query); // Run the query.

if ($result) { // If it ran OK, display the records.

// Table header.
echo '<table align="center" cellspacing="0" cellpadding="5">
<tr><td align="left"><b>Name</b></td> <td align="left"><b>Date Registered</b></td></tr>
';

// Fetch and print all the records.
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
echo '<tr><td align="left">' . $row['name'] . '</td><td align="left">' . $row['dr'] . '</td></tr>
';
}

echo '</table>';

mysql_free_result ($result); // Free up the resources.

} else { // If it did not run OK.
echo '<p class="error"> The current users could not be retrieved.  We apologize.</p>'; // Public message.
echo '<p>' . mysql_error() . ' <br/> <br/>Query: ' . $query . '</p>'; // Debugging message.
}

mysql_close(); // Close the database connection.

include ('./footer.html'); // Include the HTML footer.
?>/code]

[Psycho]

The error checking on the database connection is wrong. Should be a "!" in the IF condition. See if that helps.

 

$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

 

[BrentonHale]

The error checking on the database connection is wrong. Should be a "!" in the IF condition. See if that helps.

 

$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

 

When I use a "!" there, it crashes my apache http server version 2.2.11  .  It shuts down and closes.  Here is the message I get in the browser also.

 

The connection was reset

 

 

The connection to the server was reset while the page was loading.

 

 

    *  The site could be temporarily unavailable or too busy. Try again in a few

          moments.

 

    *  If you are unable to load any pages, check your computer's network

          connection.

 

    *  If your computer or network is protected by a firewall or proxy, make sure

          that Firefox is permitted to access the Web.

 

---

 

PHP version 5.3.0

 

 

[scvinodkumar]

try like this

 $link = mysql_connect('localhost', 'username', 'password') or die(mysql_error()); 

[BrentonHale]

try like this

 $link = mysql_connect('localhost', 'username', 'password') or die(mysql_error()); 

 

The same thing is still happening. Did I place the code in the correct place?

 

// Page header.
echo '<h1 id="mainhead">Registered Users</h1>';

require_once ('mysql_connect.php'); // Connect to the db.

$link = mysql_connect('localhost', 'username', 'password') or die(mysql_error()); 


// make sitename the current db
$db_selected = mysql_select_db("sitename") or die(mysql_error());
$data = mysql_query("SELECT * FROM users")
or die(mysql_error()); 

 

---

 

 

[scvinodkumar]

what the file contains, mysql_connect.php

 

 

[Psycho]

I just noticed that you do a mysql_close() right after connecting to the database - so none of the queries would actually run anyway. Although I suspect you're not even getting a connection due to the server failing.

 

Here is a complete rewrite (may be some minor syntax typos):

 

<?php

# Script 7.4 - view_users.php
// This script retrieves all the records from the users table.


// Connect to the db
require_once ('mysql_connect.php'); 
$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
    die('Could not connect to db server: ' . mysql_error());
}

// make sitename the current db
mysql_select_db("sitename") or die(mysql_error());

$query = "SELECT * FROM users";
$result = mysql_query($query) or die(mysql_error()); 

// Make the query.
$query = "SELECT CONCAT(last_name, ',', first_name) AS name, DATE_FORMAT (registration_date, '%M %d, %Y') AS dr
          FROM users
          ORDER BY registration_date ASC";
$result = @mysql_query ($query); // Run the query.

if (!$result)
{
    //Query did not run successfully.
    $output  = "<p class=\"error\">The current users could not be retrieved. We apologize.</p>"; // Public message.
    $output .= "<p>" . mysql_error() . "<br /><br />Query:{$query}</p>"; // Debugging message.
}
else
{
    //Query ran OK, create the output of records.

    // Table header.
    $output  = "<table align=\"center\" cellspacing=\"0\" cellpadding=\"5\">\n";
    $output .= "<tr><td align=\"left\"><b>Name</b></td><td align=\"left\"><b>Date Registered</b></td></tr>\n";
    // Fetch and print all the records.
    while ($row = mysql_fetch_array ($result, MYSQL_ASSOC))
    {
        $output .= "<tr><td align=\"left\">{$row['name']}</td><td align=\"left\">{$row['dr']}</td></tr>\n';
    }
    //Close output table
    $output .= "</table>\n";

    // Free up the resources.
    mysql_free_result ($result);
}

// Close the database connection.
mysql_close(); 


//Create the page output
$page_title = 'View the Current Users';
include ('./header.html');
// Page header.
echo "<h1 id=\"mainhead\">Registered Users</h1>";
// Page output
echo $output;
// Page footer
include ('./footer.html'); // Include the HTML footer.

?>

 

However, the problem probably does lie in the 'mysql_connect.php' file, which is why scvinodkumar stated we need to see that code.

[BrentonHale]

I just noticed that you do a mysql_close() right after connecting to the database - so none of the queries would actually run anyway. Although I suspect you're not even getting a connection due to the server failing.

 

Here is a complete rewrite (may be some minor syntax typos):

 

<?php

# Script 7.4 - view_users.php
// This script retrieves all the records from the users table.


// Connect to the db
require_once ('mysql_connect.php'); 
$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
    die('Could not connect to db server: ' . mysql_error());
}

// make sitename the current db
mysql_select_db("sitename") or die(mysql_error());

$query = "SELECT * FROM users";
$result = mysql_query($query) or die(mysql_error()); 

// Make the query.
$query = "SELECT CONCAT(last_name, ',', first_name) AS name, DATE_FORMAT (registration_date, '%M %d, %Y') AS dr
          FROM users
          ORDER BY registration_date ASC";
$result = @mysql_query ($query); // Run the query.

if (!$result)
{
    //Query did not run successfully.
    $output  = "<p class=\"error\">The current users could not be retrieved. We apologize.</p>"; // Public message.
    $output .= "<p>" . mysql_error() . "<br /><br />Query:{$query}</p>"; // Debugging message.
}
else
{
    //Query ran OK, create the output of records.

    // Table header.
    $output  = "<table align=\"center\" cellspacing=\"0\" cellpadding=\"5\">\n";
    $output .= "<tr><td align=\"left\"><b>Name</b></td><td align=\"left\"><b>Date Registered</b></td></tr>\n";
    // Fetch and print all the records.
    while ($row = mysql_fetch_array ($result, MYSQL_ASSOC))
    {
        $output .= "<tr><td align=\"left\">{$row['name']}</td><td align=\"left\">{$row['dr']}</td></tr>\n';
    }
    //Close output table
    $output .= "</table>\n";

    // Free up the resources.
    mysql_free_result ($result);
}

// Close the database connection.
mysql_close(); 


//Create the page output
$page_title = 'View the Current Users';
include ('./header.html');
// Page header.
echo "<h1 id=\"mainhead\">Registered Users</h1>";
// Page output
echo $output;
// Page footer
include ('./footer.html'); // Include the HTML footer.

?>

 

However, the problem probably does lie in the 'mysql_connect.php' file, which is why scvinodkumar stated we need to see that code.

 

 

Thank you for all the replies! Here is the code for the 'mysql_connect.php file:

 

<?php # Script 7.2 - mysql_connect.php

// This file contains the database access information.
// This file also establishes a connection to MySQL and selects the database.

// Set the database access information as constants.
DEFINE ('DB_USER', 'username');
DEFINE ('DB_PASSWORD', 'password');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'sitename');

// Make the connection.
$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error() );

// Select the database.
@mysql_select_db (DB_NAME) OR DIE ('Could not select the database: ' . mysql_error() );


?>

[BrentonHale]

I've completely re-written the view_users.php  page.  I think the problem lies within that page.  However, I'm having some formatting issues.  It appears that the database is now connecting but the results arn't really readable.

 

Can some help me with this and give me a code sample.

 

<?php
$username = "username";
$password = "password";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";


//select a database to work with
$selected = mysql_select_db("sitename",$dbhandle)
  or die("Could not select examples");

//execute the SQL query and return records
$result = mysql_query("SELECT user_id, first_name, last_name, registration_date FROM users");

//fetch tha data from the database
while ($row = mysql_fetch_array($result)) {
   echo "user_id:".$row{'user_id'}." first_name:".$row{'first_name'}."last_name: ". $row{'last_name'}."registration_date:".
   $row{'registration_date'}."<br>";  //display the results
}
//close the connection
mysql_close($dbhandle);


?>

[Psycho]

Add an or die() to the mysql_query() line to see what errors may be occuring. Are you getting any errors currently?