Design Question

[HenryCan]

I'm looking for some design ideas on how to handle a situation on a website I am building.

 

On one of my pages, I want to display a table of data. The volume of data will be something in the neighbourhood of 400 rows and it will grow at the rate of one row per month. I expect to put the data in a MySQL table.

 

My users are going to want to look at the data in different sequences. The data is a list of meeting topics which our book discussion club has had over the past 30-odd years. Some users will want to see the data in chronological order from earliest topic to latest. Some will want to see it in order by the author name. Some will want to see it in order by the name of the person who presented the discussion. I'd like to accomodate all of them as cleanly and efficiently as I can.

 

The data is on a server owned by a hosting service. They have PHP and MySQL but they don't have Ajax, JQuery or other tools and it looks like they won't let me install such tools. Setting up my own server is not an option at this point so I need to solve this problem with just PHP and MySQL.

 

Ideally, I'd like to display the data initially in a default order (chronological order by meeting date) but let the user get the data re-sorted in any of the four sequences I've described by clicking on something. I could do this easily enough in Java but don't know the best way to accomplish it in PHP. Perhaps having a clickable column name with an up arrow and a down arrow to control sort sequence - like Thunderbird. Or maybe I need to use a form and make the user select the sequence with a set of radio buttons.

 

I can't be the first guy who has needed to do this so I thought I'd ask the experts for the best way forward. If anyone can point me to an actual example that is close to what I want to do, that would be an added bonus :-)

 

 

[premiso]

You do know that your Server / Host has really no control over AJAX / jQuery stuff, as that is all (well AJAX makes call to the server but it just calls your PHP file) client side code.

 

So yea, you can use it no matter what your delusional mind created up, I suggest looking into it. If you do not believe me, find an example jQuery setup with the html and setup the html file on your server and run it. You shall see it works just fine.

[Maq]

First of all, you can upload JQuery yourself and use it, I've never heard of a hosting service restricting this.  Having said that, AJAX is simply a technique you can employ in your code.

 

The 2 major components of this design is the interface and constructing the query.  Depending on your criteria, you can use a combination of radio buttons, check boxes, etc.

 

The same concept applies in Java as PHP, after the user submits.  Construct the query based on what parameters & conditions the user selects.  This is something we can only help you with when you post code.

[xyph]

jQuery and 'AJAX' are both JavaScript technologies. Your server doesn't need to support them, just your clients.

 

PHP, MySQL and jQuery all solve different problems.

 

With MySQL, you can simply sort by another column.

 

SELECT * FROM discussions ORDER BY author

 

http://www.techotopia.com/index.php/Sorting_Data_Retrieved_from_a_MySQL_Database

 

Whether you do this through radio buttons or clicking a column header is all a matter of user preference. Both will have to be programmed in using similar methods.

[Psycho]

Maybe I missed something, but I would think a standard pagination script with the ability to reorder when the user clicks a column header is all that is needed. I didn't see anything in the requirements about having to filter the data.

 

But, I would advise first implementing a solution with just PHP/MySQL. Then once that is working you can add some AJAX into it if you really want to.

[HenryCan]

Premiso, xyph and Maq:

 

I was apparently mistaken about Ajax and XQuery - I had assumed they were server-side and needed to be installed on the server to be usable - but I assure you that I'm not delusional ;-)

 

Psycho:

 

Although I don't mind learning new things, I don't know if I want to learn a possibly complex new thing like Ajax or XQuery just to be able to do a routine job. As you say, I'm just showing some simple data that I want the user to be able to page and sort. If I can do that without having to learn something like Ajax or XQuery, I'm inclined to do it. After all, I'm already new to PHP so I'm inclined to keep it within MySQL and PHP if I can. (For what's its worth, I'm very familiar with SQL having taught it professionally for several years so I'm not worried about that part of the job.)

 

Can anyone suggest examples or existing code that already does what I want it to do? It would save me time to at least start with that, though I'd likely tweak it a bit to get exactly what I want.

 

 

[PFMaBiSmAd]

There are a number of existing database display/edit scripts with pagination/column ordering - http://www.phpmyedit.org/

[Psycho]

There's also a tutorial right here on PHP Freaks: http://www.phpfreaks.com/tutorial/basic-pagination