Can you help with a website development problem? (php, sql)
Asked by
skorned (
97
)
June 24th, 2009
I’m developing a website as a part of my method to learn web development. It basically has a huge directory of all past exam papers for IGCSE stored on the server. On the website, there’ll be an options config on the left, where a user specifies filters, such as the paper number, year, and subject. These arguements are then passed, probably to a php script, to make the relevant files appear in a table on the right, with links to download them as a pdf, or view them in an online pdf viewer.
Now I have the whole bunch of files, but am wondering how to do the filtering? Should I put the file details into a MySQL database with links to the files, and then use SQL queries on the database? Or should I directly issue php filters on the files or something? I am in doubt, and would help some clarification.
Also, If i decide to go the MySQL way, is there some automated way to import the information into the table?
Basically each file is code-named like 0625_s09_02.pdf means the paper is of syllabus code 0625 (specifies the subject), from Summer 09, Paper 2…. There has gotta be some way to parse these names into a sql table right?
Observing members:
0
Composing members:
0
3 Answers
You could do it either way, whichever is more comfortable for you.
I would go the MySQL route because for me it helps keep the code cleaner, and doesn’t require lots of filesystem access by the php script (but takes a bit more planning).
As for importing the data into the database, well, there’s a lot of ways to do it. First, I would get a list of all the files (windows dir, unix ls commands would work), and modify into the necessary columns with your favorite editor. At work I have to use Excel and Notepad for this type of stuff, at home, I’d rather use Vi & various GNU Utils (basically opposite ends of the spectrum).
Then, you could either write a simple importer in php (make sure only you have access, of course), or, if you have access to a mySQL GUI (I like phpMyAdmin) use that, or use the mysqlimport command to import the file (that will depend on what you have access to on the server).
The other option, using php to parse the directories, your best bet would probably to read up on PHP’s filesystem functions: http://us3.php.net/function.file
hth
I agree with mcs; not much to add really..
I would definitely go with the database option; it gives you more flexibility and power at the end of the day.
The Excel route: copy & paste the file list into one column in Excel (might need to use one of the “Paste Special” options to stop the list all going into one cell). You might be able to use excel functions in further columns to pull some of the other filter information from the file names. If you do this, make it like a database table, ie with SQL-friendly column headings, no blank cells, etc.
You can then import directly from your Excel file into SQL if you have the right software. But if not you can certainly save from Excel to CSV which can be imported into any database system.
hey thanks a lot…really wasn’t expecting such clear replies for such a long and convoluted question….I’ve decided to go the MySQL way after all…
and @mcs , thanks a ton for the excel idea….dunno why i didn’t think of that earlier!! i just messed around with the formulae a bit right now, and with the right combination of MIDs, IFs and ORs, life is much simpler than using a spider to index them or creating my own php script to go through the file names…thank god for CIE’s brilliant naming convention…
now the next thing i need to find is free means to have a full text index of the pdf files…any ideas? maybe I’ll post this as another question…
Answer this question 
This question is in the General Section. Responses must be helpful and on-topic.
Composing members:
0
