Forum Moderators: coopster
Scenario: Page is a listing of university fraternity alumni. Records include name, address, phone, e-mail, etc.
Dilemma: You know how on auction sites you can re-sort the auctions by clicking "highest bid" or "lowest bid" or "ending first"? <b>HOW would I do that?!?</B>
I have a couple of these alumni that want to manage the main database (I can make them export it to Excel if needed), but my scripting needs to make this SORT thing happen.
Is PHP the way to go for the bulk of what I gotta do? Or is ASP better? Or am I totally on the wrong track?
I have, however, recently converted a DB from Access to MySQL and am slowly building the interface in PHP. Depending on how robust, secure and feature-rich you want it, it ranges from fairly easy to well, the limits of the technology I suppose, but I don't go there.
You have a few options. Basically, they are in order
- from easiest to hardest
- from least flexible to most
1. If you have a database that doesn't absolutely have to be online in real time (for example, it's okay to update addresses once per month), then don't put the database online at all. Simply use Access to generate four reports, each one sorted by the criterion or criteria that you want. Save the reports in Access so they can be run at the puch of a button. Then someone with Access and an FTP client can update the pages once per month in about 10 minutes (once you have the Access reports tweaked).
2. Find a host that allows you to run Access databases (in other words, a Microsoft server rather than *nix) and just put the Access database up on the web. The Access help files will give you some ideas and Microsoft has pretty good documentation on their site.
3a. Use ASP and MS SQL Server to build it. I don't know anything about this.
3b. Convert the database to MySQL (this is relatively easy) and use PHP to build it. If you go this route, I can help with the conversion. Jatar_K recently did a great tutorial on extracting data from a database using PHP and MySQL. Folks can point you there and elsewhere.
For what you're talking about, I would go with option 1.
I've already pre-thought how to keep them from messing up the whole site, I'll just limit them to only having permissions for ONE folder, and the database will be the only thing in there.
You know how on eBay, if you search out a certain widget, it'll usually give you the auctions listed chronologically by what's ending first? BUT you can re-arrange the search after you get your page listing, to show the HIGHEST priced ones 1st? Or the Buy-it-Now auctions only?
I want the end-user to be able to go to this page of fraternity brothers.... and it will automatically pull them in from the db alphabetically. BUT I want the headers to be links that are live, so if they want to "re-sort" them by only those who have an e-mail, OR re-sort them by city, etc., they can.
That's the big dilemma. I can always take their Access database and convert it. Or teach them to convert it to what I need for web.
I don't know if ASP or PHP can do this type of trick?
Dilemma: You know how on auction sites you can re-sort the auctions by clicking "highest bid" or "lowest bid" or "ending first"? <b>HOW would I do that?!?</B>
Use ORDER BY (http://www.mysql.com/doc/en/ORDER_BY_optimisation.html)
WBF
First, don't confuse the data retrieval and manipulation with the presentation. MySQL is a database, as is MS Access. Data is stored here and can be retrieved (read) from tables (or files as some folks call them). When data is retrieved it can be done so in a fashion that allows you to all kinds of nifty things, including sorting it as it pulls it from the table. You still need to display it, and that's where the programming comes in -- ASP versus PHP versus Perl...
So, to answer your column-sorting question, yes, you can do that and you can do that with either PHP or ASP, or any number of server-side programming languages -- but, as ergophobe mentioned, the first thing you are going to have to decide is how and where you intend to store the data, and, more importantly, how the data will be used, searched, displayed, accessed, etc.
I'm just trying to figure out the best way to do this, so it works from the get-go, and will be easy to manage down the line.
I was into a scripting job once, and after 6 hours, it still wouldn't work, it made me nuts. I like my code to be neat, easy to read, and very workable. I like very easily navigated things.
Can Access be exported to MySQL and then parsed? I've never done that. They will make the DB whatever I need it to be.
Woah! Better slow down a second here!
Indeed...
Welcome to WebmasterWorld, never_enuf!
Forgetting my manners. I didn't notice that you were that new! Welcome.
Anyway coopster's right - we're getting way too far ahead here.
I'm not sure why they chose Access vs. Excel, or whatnot.
Well, Access is a database management system (nobody laugh!) and does that really well in a desktop environment. Excel does some things great, but it isn't a dbms.
Can Access be exported to MySQL and then parsed?
There's a great conversion script. It is an Access VBA script, so you will need Access and the data. It will create a nice little SQL file that you can transfer easily. It's a little finicky, though, so if you go that route, we can get back to that. Know for the present that the conversion to MySQL will be really easy.
I need to be able to update it on the fly,
Are you sure? The phone company updates the public version of their database (known as the phone book) once per year. That seems to work for a population that is not highly mobile.
Let's assume that you do want something to work in PHP/MySQL with mimimum "admin" intervention. I'm not saying that's a good idea, just that it's possible. In roughly this order, you will need to
1. install apache, php and mysql servers on your own machine for building and testing.
2. get some sample data (perhaps the actual data).
3. write a script that will allow anyone (registered users?) to view records and admins to update records.
From what you say, it seems to me that what you would want is a login system where everyone registers and you assign different levels of privileges, such as regular users can view all records, search the DB and update their own records, and "admins" who can muck with other records as well.
I think the first thing I would do if I were you, would be to Google for
php mysql address book
I bet there are several free scripts out there that do more or less what you want. Then you just have to get the data into them.
However, you want to "sort by column". To me, that says that you are simply outputting the whole list and then sorting. That further suggests that you don't have 30K records or anything, but perhaps a few hundred or a thousand. In that case, I would stick with what I said - it doesn't seem worth the effort to build a whole system for that unless you simply want the experience of doing something with PHP and MySQL.
Tom
YAAB X - Yet Another Address Book X
Seems to be a more powerful version of YAAB by the same author. NOte that both are available on his website, including a demo of the X version, whatever that means.
[db.elnino-security.de...]
There are also two Sourceforge projects
PHP-PDB Address Book
[php-pdb.sourceforge.net...]
Yet Another PHP Address Book
[yapab.sourceforge.net...]
I don't know whether any of these will meet your needs, but they are all free with downloadable source code, so you should be able to save a lot of time by finding one you like and then modifiying it to fit your needs.
I have this feeling that I would get better results with some other search terms instead of address book ("alumni directory" of something like that?). Anyway, there's lot os stuff out there.
Tom
