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:
I guess you'd have the link like : .php?sort=asc&order=email
Then when you click on it you'd check the querystring for asc or desc and then use the other one in your SQL query, and when you output the table again - you change the link so it's .php?sort=desc&order=email
Then if you click on that one it does the same as before, and the circle of life continues.
 
Last edited:
Yeah if u build to queries and use an if $sort == 'asc' then execute the asc one and if $sort = 'desc' then execute the desc one. Does that make sence?


Also where you have your query string $_GET['order'] you should add mysql_real_escape_string() to make sure your site isnt vunrable to attacks....


Checkout Rob Millers (robmiller on OcUK) PHP Security article
 
Although it's probably easier to do something like this, rather than use the input directly:

Code: 
$order = 'desc';
if ( $_GET['order'] == 'asc' )
    $order = 'asc';

That way it's descending by default, and ascending if the user tells it to be—which means you don't have any problems if an order parameter isn't passed.
 
Code: 
<?php

if (!empty($_GET['order']) || !in_array($_GET['order'], array('desc', 'asc'))
{
    $order = 'ASC';
}
else
{
    $order = mysql_real_escape_string(strtoupper($_GET['order']));
}

$query = "SELECT `whatever` FROM `table` ORDER BY $order";

?>

EDIT: go with the previous post.
 
my suggestion was to actually do...

if ($sort = 'asc') {
$sql = 'SELECT * FROM tblname ORDERBY *** ASC';
} else {
$sql = 'SELECT * FROM tblname ORDERBY *** DESC';
}

$data = mysql_query($sql);

but glad its working for ya!
 
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?
 
If that data is stored in a database, which it is. have you given each record an ID number? When querying your table you could get back all the record data (including the ID) and then when echoing the edit/delete links, just use them in that.

So your link would read something like <a href="delete.php?id=4">delete</a>.

Then use the delete.php to handle the deleting.

Im not sure how you would go about editing the content on the same page though, as you want. Soz
 
You must log in or register to reply here.
Back
Top Bottom