[RESOLVED] Displaying search results to edit

[mrw2020]

I would like to access a db table and display records that can then be edited or deleted on additional pages and I'm using the following code for this. I'm using WAMP server version2 as a testing server on a windows Vista for business machine (service pack 2). When I test it in my browser locally (firefox & IE) I keep getting the 'Apache Server timed out' message I have tried various tweeks and adjustments to the code and would appreciate any guidance as to what I'm doing wrong. I've tested it with NuSphere where it does work and doesn't display any mistakes, so I'm left completely puzzled.

Any help would be greatly appreciated
😕

<?php # View retailers.php
// This is where you can view retailers in the database.

// Include the configuration file for error management and such.
include ('includes/config.inc.php');

// Set the page title and include the HTML header.
$page_title = 'View/Edit Retailers';
include ('includes/header.html');
// If no first_name variable exists, redirect the user.
if (!isset($_SESSION['first_name'])) {

// Start defining the URL.
$url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);
// Check for a trailing slash.
if ((substr($url, -1) == '/') OR (substr($url, -1) == '\\') ) {
    $url = substr ($url, 0, -1); // Chop off the slash.
}
// Add the page.
$url .= '/index.php';

ob_end_clean(); // Delete the buffer.
header("Location: $url");
exit(); // Quit the script.

} else { // Welcome the user by name if they are logged in.
echo '<h2>Welcome'; 
if (isset($_SESSION['first_name'])) {
    echo " {$_SESSION['first_name']}";
    }
}

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

// Make the query.
$query = "SELECT ret_name, ret_add1, ret_add2, ret_town, ret_county, ret_pcode, ret_phone, ret_email, ret_web, DATE_FORMAT(ret_dateEntered, '%d %M, %Y') AS de, ret_id FROM retailers ORDER BY ret_name ASC";
$result = mysql_query ($query); // Run the query

$num_rows = mysql_num_rows($result); // Count the number of records.

echo "<p class='maincopy'>We currently supply <b>$num_rows</b> retailers across the UK</p>\n";

// Table header
echo '<table align="left" cellpadding="0" cellspacing="5">
        <tr>
            <td align="left"><strong>Edit</strong></td>
            <td align="left"><strong>Delete</strong></td>
            <td align="left"><strong>Shop Name</strong></td>
            <td align="left"><strong>Shop Address & Details</strong></td>
        </tr>
        ';

// Fetch and print all records.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo '<tr>
            <td><td align="left"><a href="editRetailer.php?id=' . $row['ret_id'] . '">Edit</a></td>
            <td><td align="left"><a href="deleteRetailer.php?id=' . $row['ret_id'] . '">Delete</a></td>
            <td align="left">' . $row['ret_name'] . ' <br />
                             ' . $row['ret_add1'] . ' <br />
                             ' . $row['ret_add2'] . ' <br />
                             ' . $row['ret_town'] . ' <br />
                             ' . $row['ret_county'] . ' <br />
                             ' . $row['ret_pcode'] . ' <br />
                             ' . $row['ret_phone'] . ' <br />
                             ' . $row['ret_email'] . ' <br />
                             ' . $row['ret_web'] . ' <br />
                             ' . $row['de'] . ' <br /></td>
        </tr>
        ';
    }

echo '</table>';

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

mysql_close(); // Close the database connection

include ('includes/footer.html');

?>

[Emma]

I'm no expert with Apache but I presume, it's timing out because something's taking too long. Try checking what the maximum execution time is set to, (look in phpinfo) and how long the query takes to execute in the database. Failing that, comment out sections until you don't get a time out to narrow down the cause.

[mrw2020]

Thank you Emma for your response, I have been through most of what you suggest - I suppose it just gets to the stage where you can't see the code correctly and it all goes into one blur!. Another point is that I wasn't very clear with my original post but the actual message says that the connection to the Apache server has been lost/terminated (not quite sure what that means) the only thing I can think of is that somehow the connection script isn't working correctly although it works for the other pages and I've been through it several times - any thoughts anyone?. Any help would be much appreciated.

[mrw2020]

Just an update to this thread, I've re-written the code and cleaned it up to try and get it to work, unfortunately it still doesn't - the error message I keep getting is as follows:

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

and here is the cleaned up code:

If anybody could steer me in the right direction or give me an idea of what I'm doing wrong I would really appreciate it. Many thanks in advance.😕

<?php # viewRetailers.php

// This script retrieves all the records from the retailers table.
// This script links to the edit and delete pages.

$page_title = 'View Current Retailers';
include ('includes/header.html');

// Page header.
echo '<h1>Shops that sell our products</h1>';

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

// Make the query.
$query = "SELECT ret_name, ret_add1, ret_add2, ret_town, ret_county, ret_pcode, ret_phone, ret_email, ret_web, DATE_FORMAT(ret_dateEntered,'%d %M, %Y') AS de FROM retailers ORDER BY ret_name ASC";

// Run the query.
$result = mysql_query ($query);

$num = mysql_num_rows($result);

if ($num > 0) { // If everything is okay.

 echo "<p>There are currently <strong>$num</strong> retailers that sell our products.</p>\n";

 // Table header
echo '<table align="left" cellpadding="0" cellspacing="5">
    <tr>
        <td align="left"><strong>Edit</strong></td>
        <td align="left"><strong>Delete</strong></td>
        <td align="left"><strong>Shop Name</strong></td>
        <td align="left"><strong>Shop Address & Details</strong></td>
    </tr>
    ';

// Fetch and print all records.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<tr>
        <td align=left><a href="editRetailer.php?id=' . $row['ret_id'] . '">Edit</a></td>
        <td align="left"><a href="deleteRetailer.php?id=' . $row['ret_id'] . '">Delete</a></td>
        <td align="left">' . $row['ret_name'] . '</td>
        <td align="left">
                         ' . $row['ret_add1'] . ' <br />
                         ' . $row['ret_add2'] . ' <br />
                         ' . $row['ret_town'] . ' <br />
                         ' . $row['ret_county'] . ' <br />
                         ' . $row['ret_pcode'] . ' <br />
                         ' . $row['ret_phone'] . ' <br />
                         ' . $row['ret_email'] . ' <br />
                         ' . $row['ret_web'] . ' <br />
                         ' . $row['de'] . ' <br /></td>
    </tr>
    ';
}

echo '</table>';

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

} else { // If it didn't run ok.
	echo '<p> There are currently no registered retailers.</p>';
}

// Close the connection.
mysql_close();

// Include the footer.
include ('includes/footer.html');

?>

[zooropa]

I've just run your code against a "retailers" table and it worked ok provided:

//remember to get ret_id with your query
$query = "SELECT ret_id, ret_name, ret_add1, ret_add2 FROM retailers ";

// you need 2 parameters in $result but you will probably need to change $dbc to
// whatever you've called it inside your mysql_connect.php connection file
$result = mysql_query ($dbc,$query);

//Provide $dbc inside mysql_close() - again you will probably have to change $dbc
// to whatever you have inside mysql_connect.php
mysqli_close($dbc);

[mrw2020]

Many thanks for your response, I've been working on this most of today so feel a little washed out with it. I'll give your suggestions a go tomorrow. I did notice the 'ret_id' was misssing in the 'SELECT' statement so altered that. Hopefully I'll be refreshed and enthusiastic tomorrow and all will be well with the world!. I'll let you know how it goes.

I really do appreciate your response and suggestions, warm regards.

Maddie

[mrw2020]

Using your suggestion of closing the $dbc connection I substitued the following code

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

mysql_close(); // Close the database connection.

for this:

 mysql_close($dbc);
  unset($dbc);

and it now works a treat, for some reason it really didn't like the mysql_close statement or the mysql_free_result. I fumbled through these possiblilities without really knowing what I'm doing so if anyone could explain why the code works please let me know - is the 'unset' statement more stable or reliable?

If anyone would like to use or adapt the code here is the complete page:

 # - viewRetailers.php

// This script retrieves all the records from the retailers table.

// Include the config file for error management and such.
include ('includes/config.inc.php');

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

// If no first_name variable exists, redirect the user.
if (!isset($_SESSION['first_name'])) {

// Start defining the URL.
$url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);

// Check for a trailing slash.
if ((substr($url, -1) == '/') OR (substr($url, -1) == '\\') ) {
	$url = substr ($url, 0, -1); // Chop off the slash.
}

// Add the page.
$url .= '/index.php';
ob_end_clean(); // Delete the buffer.
header("Location: $url");
exit(); // Quit the script.

} else { // Welcome the user by name if they are logged in.

echo '<strong>Welcome</strong>'; 

if (isset($_SESSION['first_name'])) {
	echo " {$_SESSION['first_name']}";

}

}

// Page header.
echo '<p><h2>What on Earth Retailers</h2></p>';

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

$result = mysql_query("SELECT * FROM retailers");
$num_rows = mysql_num_rows($result);

echo "There are currently <strong>$num_rows</strong> retailers that sell our products<br />";

// Number of records to show per page:
$display = 3;

// Determine how many pages there are. 
if (isset($_GET['np'])) { // Already been determined.

$num_pages = $_GET['np'];

} else { // Need to determine.

// Count the number of records
$query = "SELECT COUNT(*) FROM retailers ORDER BY ret_name ASC";
$result = mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
$num_records = $row[0];

// Calculate the number of pages.
if ($num_records > $display) { // More than 1 page.
	$num_pages = ceil ($num_records/$display);
} else {
	$num_pages = 1;
}

}

// Determine where in the database to start returning results.
if (isset($_GET['s'])) {
	$start = $_GET['s'];
} else {
	$start = 0;}

// Make the links to other pages, if necessary.
if ($num_pages > 1) {

echo '<br /><p>';
// Determine what page the script is on.	
$current_page = ($start/$display) + 1;

// If it's not the first page, make a Previous button.
if ($current_page != 1) {
	echo '<a href="viewRetailers.php?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';
}

// Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
	if ($i != $current_page) {
		echo '<a href="viewRetailers.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> ';
	} else {
		echo $i . ' ';
	}
}

// If it's not the last page, make a Next button.
if ($current_page != $num_pages) {
	echo '<a href="viewRetailers.php?s=' . ($start + $display) . '&np=' . $num_pages . '" class="navtext">Next</a>';
}

echo '</p>';

} // End of links section.

// Make the query:
$q = "SELECT ret_id,ret_name,ret_add1,ret_add2,ret_town,ret_county,ret_pcode,ret_phone,ret_email,ret_web FROM retailers ORDER BY ret_name ASC LIMIT $start, $display";        

$result = mysql_query ($q); // Run the query.Define the $result variable and display or send error message

// Table header
echo '<table align="left" cellpadding="5" cellpadding="5" class="maincopy" border="1">
    <tr>
        <td align="left"><strong>Edit</strong></td>
        <td align="left"><strong>Delete</strong></td>
        <td align="left"><strong>Shop Name</strong></td>
        <td align="left"><strong>Shop Address & Details</strong></td>
    </tr>
    ';

// Fetch and print all records.
while ($row = mysql_fetch_array($result)) {
echo '<tr valign="top">
        <td align=left><a href="editRetailer.php?id=' . $row['ret_id'] . '">Edit</a></td>
        <td align="left"><a href="deleteRetailer.php?id=' . $row['ret_id'] . '">Delete</a></td>
        <td align="left"><h2>' . $row['ret_name'] . '</h2></td>
        <td align="left">
                         ' . $row['ret_add1'] . ' <br />
                         ' . $row['ret_add2'] . ' <br />
                         ' . $row['ret_town'] . ' <br />
                         ' . $row['ret_county'] . ' <br />
                         ' . $row['ret_pcode'] . ' <br />
                         ' . $row['ret_phone'] . ' <br />
                         ' . $row['ret_email'] . ' <br />
                         ' . $row['ret_web'] . ' <br />
						 </td>
    </tr>
    ';
}

echo '</table>';


  mysql_close($dbc);
  unset($dbc);

include ('includes/footer.html'); // Include the HTML footer.

I adapted this code from various sources but mainly from Larry Ulmans books.

[mrw2020]

Thanks Zooropa for steering me in the right direction!

[zooropa]

No problem. Just out of curiosity you might just want to delete

mysql_close($dbc);
unset($dbc);

as i think the database connection is closed automatically anyway so these two lines of code may be unnecessary

[zooropa]

Just another thought - could you post the contents of mysql_connect.php ?

[mrw2020]

No problem Zooropa, here is the code without the username and password information, let me know what you think - I'll give your suggestion a try and let you know how it works. Thank you again

/**************************************************************************
* 
* 
****************************************************************************/

// This file contains the database access information.
// This file also establishes a connection to MySQL and selects the database.
// This file also defines the 'escape_data()' function.

// Set the database access information as constants...  

  DEFINE ('DB_USER', '');
  DEFINE ('DB_PASSWORD', '');
  DEFINE ('DB_HOST', '');
  DEFINE ('DB_NAME', '');

// Make the connection...
$dbc = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if ($dbc) { // Make the connection.

if (!mysql_select_db (DB_NAME)) { // If it can't select the database.

// Handle the error.
trigger_error("Could not select the database!\n<br />MySQL Error: " .mysql_error());

// Print a message to the user, include the footer, and kill the script.
include ('/woe/content management/includes/footer.html');
exit();

} // End of mysql_select_db IF.

} else { // If it couldn't connect to MySQL.

// Print a message to the user, include the footer, and kill the script.
trigger_error("Could not connect to MySQL!\n<br />MySQL Error: " .mysql_error());
include ('/woe/content management/includes/footer.html');
exit();
}

// Create a function to escape the data...
function escape_data($data) {

// Address Magic Quotes...
if (ini_get('magic_quotes_gpc')) {
    $data = stripslashes($data);
}

   // Check for mysql_real_escape_string() support.
   if (function_exists('mysql_real_escape_string')) {
	   global $dbc; // Need the connection
	   $data = mysql_real_escape_string(trim($data), $dbc);
   } else {
	   $data = mysql_escape_string(trim($data));

   }

   // Return data
   return $data;

}// End function   

[zooropa]

That looks OK - I just wanted to be sure that is said $dbc inside mysql_connect.php

Let us know how you get on with deleting the two lines