php/mysql question

Ripper

Ripper

Ripper

Soldato
Joined
25 Jan 2003
Posts
11,542
Location
Newark, Notts
this has me stumped.

I've got this page http://saywhatagain.co.uk/customers.php

What its currently doing is listing a table of results twice, and in the first table listing them in ascending order, and in the second listing them by descending, by whichever column heading is selected.

What i want it to do is have ONE table of results, with two headings for each column (ascending/descending), and then whichever you select it sorts the results etc.

Here's the code I have at the moment (just the two blocks of php for each table):

Code: 
<?php
 
 $default_sort = 'CustomerID'; // default sort order is by the first column - customerID
 $allowed_order = array ('CustomerID', 'Name','Address','Postcode','Telephone','Email'); //array of all column headings

if (!isset ($_GET['order']) || 
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}

 $query = "SELECT * FROM customers ORDER BY $order"; // query which selects all data from the table and orders it by the selected column
$result = mysql_query($query);

// gets the first row and starts the table	
$row = mysql_fetch_assoc ($result);
echo "<table cellpadding='5' cellspacing='5'>\n";
echo "<tr>\n";
foreach ($row as $heading=>$column) {

    //if the heading is in our allowed_order array, its hyperlinked so that the table can be ordered by this column 
    echo "<td><b>";
    if (in_array ($heading, $allowed_order)) {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
		
    } else {
        echo $heading;
    }                
    echo "</b></td>\n";
}
echo "</tr>\n";

//display the data
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<tr>\n";
    foreach ($row as $column) {
        echo "<td>$column</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";


?>
</table>
<table cellpadding="5" cellspacing="5">
 <tr><td>
<b>Descending Order</b></td></tr>
 <tr><td>
 
<?php
 
 $default_sort1 = 'CustomerID'; // default sort order is by the first column - customerID
 $allowed_order1 = array ('CustomerID', 'Name','Address','Postcode','Telephone','Email'); //array of all column headings

if (!isset ($_GET['order']) || 
    !in_array ($_GET['order'], $allowed_order1)) {
    $order1 = $default_sort1;
} else {
    $order1 = $_GET['order'];
}

 $query1 = "SELECT * FROM customers ORDER BY $order1 DESC"; // query which selects all data from the table and orders it by the selected column
$result1 = mysql_query($query1);

// gets the first row and starts the table	
$row = mysql_fetch_assoc ($result1);
echo "<table cellpadding='5' cellspacing='5'>\n";
echo "<tr>\n";
foreach ($row as $heading=>$column) {

    //if the heading is in our allowed_order array, its hyperlinked so that the table can be ordered by this column 
    echo "<td><b>";
    if (in_array ($heading, $allowed_order1)) {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
		
    } else {
        echo $heading;
    }                
    echo "</b></td>\n";
}
echo "</tr>\n";

//display the data
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result1)) {
    echo "<tr>\n";
    foreach ($row as $column) {
        echo "<td>$column</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";


?>

Anyone got any ideas?
 
i'll try and draw out what I mean:

column1 ------- column2 ----- column3 ------ column4
asc/desc ------ asc/desc ----- asc/desc ----- asc/desc
data ---------- data -------- data -------- data
data ---------- data -------- data -------- data
data ---------- data -------- data -------- data
data ---------- data -------- data -------- data
data ---------- data -------- data -------- data
data ---------- data -------- data -------- data

asc and desc would both be links which when clicked would sort the table by the chosen column in either ascending or descending order.

As i said, at the moment the only way ive figured out how to do this is two seperate tables, but the above is how i'd prefer it.
 
Last edited:
got another question. Now i have the page i created earlier:

http://www.saywhatagain.co.uk/php/results.php

Now what i want to do now is turn the text on the last column (edit/delete), into links which will, when clicked, either turn the selected row into textboxes to edit the data in the row, or delete the row after confirmation. Not quite sure how to do this as I cant figure out how to link the edit/delete to the selected ID.

Code for the php so far is below:

Code: 
<?php
 
 $default_sort = 'CustomerID'; // default sort order is by the first column - customerID
 $allowed_order = array ('CustomerID', 'Name','Address','Postcode','Telephone','Email','DESC','sortby'); //array of all column headings

if (!isset ($_GET['order']) || 
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}
if ($_GET['sortby']) {

$query = "SELECT * FROM customers ORDER BY $order " . $_GET['sortby']."";

}
else
{
$query = "SELECT * FROM customers ORDER BY $order"; // query which selects all data from the table and orders it by the selected column
}
$result = mysql_query($query);

// gets the first row and starts the table	
$row = mysql_fetch_assoc ($result);
echo "<table cellpadding='5' cellspacing='5'>\n";
echo "<tr><td><b>CustomerID</b></td><td><b>Name</b></td><td><b>Address</b></td><td><b>Postcode</b></td><td><b>Telephone</b></td><td><b>Email</b></td></tr>";
echo "<tr>\n";
foreach ($row as $heading=>$column) {

    //if the heading is in our allowed_order array, its hyperlinked so that the table can be ordered by this column 
    echo "<td>";
    if (in_array ($heading, $allowed_order)) {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">Asc</a> / 
		<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&sortby=DESC\">Desc</a>";
		
		
    } else {
        echo $heading;
    }                
    echo "</td>\n";
}
echo "</tr>\n";

//display the data
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<tr>\n";
    foreach ($row as $column) {
        echo "<td>$column</td>\n";
    }
    echo "<td>edit/delete</td></tr>\n";
}
echo "</table>\n";


?>

Anyone got any ideas of how i can go about this?
 
You must log in or register to reply here.
Back
Top Bottom