Table sorting via PHP

PHP
1

I can successfully sort 1 HTML table that pulls data from 1 mySQL table.

What I am trying to do is on one page, have three HTML tables, each one pulling data from the same MySQL table Each HTML table displays certain content depending on a field marked yes or no.

Table 1:
Books

Table 2:
Games

Table 3:
Movies

I want to be able to sort Table 1 one by its different fields, but it doesn’t work and I suspect that it’s because I have the other two tables listed with the same case IDs.

<th style="width:250px"><strong><a href="other-items.php?order=oi_name">Name</a></strong></th>
                <th style="width:250px"><strong><a href="other-items.php?order=oi_description">Description</a></strong></th>

 while ($row = mysql_fetch_array($result)) {
      echo '
        <tr>
            <td style="width:250px">' . $row['oi_name'] . '</td>
            <td style="width:250px">' . $row['oi_description'] . '</td>

…

        $order = '';
    switch($_GET['order'])
    {
    case 'order':
        $order = 'oi_id';
    break;
    
    case 'oi_name':
        $order = 'oi_name';
    break;

This is the whole code for one table:

<?php
    // Request the text electronic items
    $result = @mysql_query("SELECT oi_name,oi_description,oi_manufacturer,oi_model,oi_serial,oi_purchasedate,oi_manufactureddate,oi_amount,oi_price,oi_retailer,oi_cameraitem,oi_upload1,oi_upload2,oi_upload3 FROM other_items WHERE oi_electronicitem LIKE ('yes') ORDER BY oi_name ASC");

    if (!$result) {
        exit('<p>Error performing query: ' . mysql_error() . '</p>');
    }
    //creating the table w/ headers
    echo '
    <table>
        <thead>
            <tr>
                <th style="width:250px"><strong><a href="other-items.php?order=oi_name">Name</a></strong></th>
                <th style="width:250px"><strong><a href="other-items.php?order=oi_description">Description</a></strong></th>
                <th style="width:150px"><strong><a href="other-items.php?order=oi_manufacturer">Manufacturer</a></strong></th>
                <th style="width:250px"><strong><a href="other-items.php?order=oi_model">Model</a></strong></th>
                <th style="width:120px"><strong><a href="other-items.php?order=oi_serial">Serial</a></strong></th>
                <th style="width:60px"><strong><a href="other-items.php?order=oi_amount">Amount</a></strong></th>
                <th style="width:84px"><strong><a href="other-items.php?order=oi_price">Price</strong></th>
                <th style="width:110px"><strong><a href="other-items.php?order=oi_purchasedate">Purchased</strong></th>
                <th style="width:90px"><strong><a href="other-items.php?order=oi_manufactureddate">Manufactured</strong></th>
                <th style="width:90px"><strong><a href="other-items.php?order=oi_retailer">Retailer</strong></th>
            </tr>
        </thead>';    
    // Display each item
      while ($row = mysql_fetch_array($result)) {
      echo '
        <tr>
            <td style="width:250px">' . $row['oi_name'] . '</td>
            <td style="width:250px">' . $row['oi_description'] . '</td>
            <td style="width:150px">' . $row['oi_manufacturer'] . '</td>
            <td style="width:100px">' . $row['oi_model'] . '</td>
            <td style="width:120px">' . $row['oi_serial'] . '</td>
            <td style="width:30px">' . $row['oi_amount'] . '</td>
            <td style="width:84px">$' . $row['oi_price'] .'</td>
            <td style="width:110px">' . $row['oi_purchasedate'] . '</td>
            <td style="width:90px">' . $row['oi_manufactureddate'] .'</td>
            <td style="width:90px">' . $row['oi_retailer'] .'</td>
        </tr>
      ';
      }        
      echo '</table>';
      
        $order = '';
    switch($_GET['order'])
    {
    case 'order':
        $order = 'oi_id';
    break;
    
    case 'oi_name':
        $order = 'oi_name';
    break;

    case 'oi_description':
        $order = 'oi_description';
    break;

    case 'oi_manufacturer':
        $order = 'oi_manufacturer';
    break;

    case 'oi_model':
        $order = 'oi_model';
    break;
    
    case 'oi_serial':
        $order = 'oi_serial';
    break;

    case 'oi_amount':
        $order = 'oi_amount';
    break;

    case 'oi_price':
        $order = 'oi_price';
    break;
    
    case 'oi_purchasedate':
        $order = 'oi_purchasedate';
    break;

    case 'oi_manufactureddate':
        $order = 'oi_manufactureddate';
    break;    
    
    case 'oi_retailer':
        $order = 'oi_retailer';
    break;    

    default:
        $order = 'order';
    break;
    }
    ;
    ?>
2

What I am trying to do is on one page, have three HTML tables, each one pulling data from the same MySQL table Each HTML table displays certain content depending on a field marked yes or no.

Table 1:
Books

Table 2:
Games

Table 3:
Movies

I want to be able to sort Table 1 one by its different fields, but it doesn’t work and I suspect that it’s because I have the other two tables listed with the same case IDs.

So one single db table gives 3 different html tables? (just trying to differentiate your use of the word tables)

So presumably you have 3 different sql statements going on?

" … where type= ‘book’";

" … where type= ‘video’";

" … where type= ‘game’";

Then you want to control an ORDER BY statement for each of these tables in the URL?

And presumably, you want to maintain state between page refreshes…

So I click on books and order them by name, then click on video and order them by manufacturer, but do I still get to see books ordered by name?

What if you have 100 books and 500 videos, are you going to introduce pagination too?

If yes, then I think it is doable, but not advisable – there must be a better way of representing the data.

How big is the dataset, is this just a practice program?

3

It’s mostly for myself, it’s on a private server and am trying to display essentially a fake catalog of items. There won’t be a pagination as there won’t be a need for it. I’d say there’s about 300 items.

4

In which case a typical url might look like:

other-items.php?order=oi_name&table=video

which results in a query which is the equivalent of:

“select (stuff) from mytable where type=”. $_GET[‘table’] . " ORDER BY " . $_GET[‘oi_name’] . " LIMIT 10";

In which case what remains for you to do is to protect your database against an [google]SQL injection attack[/google]

5

They are all in one table in MySQL. I’m assuming that your code won’t work?

6

If its all in MySQL, can you use PEAR Structures_DataGrid?

7

OK, you have one database table called other_items.

In that table you have 3 different item types: books videos and games.

This code you posted;


// Request the text electronic items
$result = @mysql_query("SELECT oi_name,oi_description,
oi_manufacturer,oi_model,oi_serial,oi_purchasedate,
oi_manufactureddate,oi_amount,oi_price,oi_retailer,
oi_cameraitem,oi_upload1,oi_upload2,oi_upload3
FROM other_items
WHERE oi_electronicitem LIKE ('yes') ORDER BY oi_name ASC");

Suggests you also have “electronicitem”.

So I am now confused because it looks as if your table structure is wrong or incomplete.

This part:


WHERE oi_electronicitem LIKE ('yes')

Suggests you should restructure your data so that you can do the equivalent of:


WHERE item_type = 'electronics'

OR


WHERE item_type = 'video'

Then you can have 3 basic generic “filters” which generate your 3 html tables.


$videos_sql = "SELECT (stuff) from other_items WHERE item_type = 'video'";
$books_sql = "SELECT (stuff) from other_items WHERE item_type = 'book';
$games_sql = "SELECT (stuff) from other_items WHERE item_type = 'game';

So, if nothing comes from your html page, it runs the 3 generic queries.

Now, if somone wants to reorder say, books by price you can create the link:

other-items.php?books=id;

So then your PHP script detects that this is the equivalent of:


$videos_sql = "SELECT (stuff) from other_items WHERE item_type = 'video'";
$books_sql = "SELECT (stuff) from other_items WHERE item_type = 'book' ORDER BY price DESC;
$games_sql = "SELECT (stuff) from other_items WHERE item_type = 'game';

Does that match with what you want to do? Else post the structure of your table (use the sql statement “DESCRIBE other_items” directly into you db)

8

I think what you’re trying to do it’s pretty difficult and it’s can only be done with bad coding.bad coding means bad maintenance.
I have recently build a javascript class that recieves an object with options (tablewdth,columns,url etc) and creates an html table that is fuly sortable and searchable. you can also use jTable to do that. go here and check it out.