Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

gizmo_mathboy (782)

gizmo_mathboy
  reversethis-{moc ... } {yobhtamomzig}

aerospace engineer by education, all-around walking cesspool of knowledge by nature, and professional grade slacker by choice. Sysadmin at a major midwestern university.
Tuesday April 30, 2002
11:04 PM

Form-based search of a database...

[ #4566 ]

...isn't as easy a task as I thought it would be. Especially if you're doing it across tables.

My initial attempt really didn't work so well. Looking through the Cheetah I was thinking subqueries might be the way. Unfortunately MySQL has no such thing just yet, at least in the stable version, 3.x, I'm using.

So I settled on using two searches (if needed). If two searches were needed then I took the intersection of the two sets, otherwise I just took the results from whatever search was performed.

I also had to put in a filter so that the results found were appropiate to the person viewing them. This is for a ticketing type of system so only customers should only be to see results pertinent to them or their customers.

Well, that was the biggest bit of coding today, basically rewriting code I wrote yesterday. You gotta love how the better idea comes to your after a user finds defects in it and you figure out to fix it.

Well, tomorrow is sort of a thinking day. I need to read some manuals and do some planning for a couple of other projects. Woo. Hoo. Beats being unemployed I suppose.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Why MySQL? I had to evaluate it for some project I did a few years ago and found it severely lacking at the time (it has improved now that it has transactions [kindof]).
    --
      ---ict / Spoon
    • I'm more familiar with MySQL. I have been planning to give PostGreSql a try when I have the time.

      I'll probably need to move to PostGres or something else if the traffice really picks up, but MySQL does what I need to do and what it can't do I'm finding solutions.
  • way to use the free software. Oracle's nice, but the cost is so prohibitive for the little guy. I might have some search scripts laying around if you wanna look at it.

    JeffK

    What was that other guys problem? You run into those jerks everywhere.
    • I'm running Apache and RedHat 7.2,too.

      I'm currently looking at Tomcat to see if a java servlet can run on it. I might have to use win2k for that since the company that created the servlet (and assoicated apps) is windows only. I would like to stay on Linux/Apache but I may have to move to win2k/Apache. I hope I won't have to go win2k/IIS.

      I think the biggest problem with the search script is that I'm potentially querying 2 tables. With no subqueries I had to make 2 queries and then merge them. Not that big
      • Here are different examples of how to do a psudeo-subquery with MySQL.

        # max_salary table to get the names of the
        # employees with the highest salary.
        #
        SELECT employee_name
        FROM employee, salary, max_salary
        WHERE employee.employee_id = salary.employee_id

        AND salary = max_salary
        ---------------------------------------------------------------

        SELECT employee_name
        FROM employee, salary
        WHERE employee.employee_id = salary.employee_id
        AND salary = (SELECT MAX(salary.salary) FROM
        salary);

        ------
        • Cool. Perl runs strong in me but SQL is weak. Maybe I can trade Perl knowledge for SQL. ;-)

          Know of any good SQL books? I suppose I should poke around the usenet archives, but I'm lazy and like to get recommendations before I spend money on an area I'm not so knowledgeable.
    • Bit OTT surely? No need to call me a jerk. Hmm. Or were you referring to that idiot who posted previous comments to other journal entries?

      I was merely asking after his reasons in using MySQL. It was only due to the mention of having trouble trying to do something that MySQL doesn't support and, as you noted, have to kludge around.

      fwiw, as gizmo noted: Postgres is also nice, and as free as MySQL. Hmm. And has solid transaction support =)

      I tried Oracle - ate my machine. Helps to have more than 128Mb RAM. A
      --
        ---ict / Spoon
      • I'm really sorry if you thought I meant you.
        I was talking about the other guy you mentionned.
        I agree about Postgres. Everyone has there own
        reasons for everytihng they use. I mean
        do people really like to use Word or do
        they have to?
        Again I'm sorry for the confusion. What does
        OTT mean?

        JeffK
        • It's cool. I was halfway through writing a much too nasty reply and suddenly thought "hang on! this is gizmo's journal. it's got that weird guy." Not to worry.

          I've always assumed that people use Word because they either have to or that they don't know any better - or that anything better is too much effort. I mean, I praise LaTeX and TeX to the heavens. Wouldn't live without them. But there's one hell of a learning curve. Now TechWriter, on the other hand, that's a wonderful program. GUI based (not quite
          --
            ---ict / Spoon
          • Great. Now my journal is associated with that weird guy aka Cliffy (I'm guessing) ;-)

            Oh well, we all have burdens to bear. :-)

  • Have you tried creating a temporary table that has the columns you are searching for it it, that you update every day/hour/etc. You can get pretty good mileage out of such a technique.
    • Didn't really think about it. I'll keep it mind for future growth though. I'm guessing my double search kludge will work for some time (and I could try the SQL queries that JeffK posted).

      I really need to sit down and read some db books. I have "Database Design for Mere Mortals" which seems rather good so far as it is an intro for design. I not so sure what else I'll need to know other than learning via the school of hard knocks.