...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.
Re:MySQL
gizmo_mathboy on 2002-05-01T18:29:02
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.
Re:Dude
gizmo_mathboy on 2002-05-01T18:34:55
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 a deal other than I should have thought of intersecting two sets than trying an SQL query I wasn't sure would work like I need it to work.
I would be interested to look at the script if for no other reason to see if any ideas will get sparked.Try this
JeffK on 2002-05-01T20:23:20
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);
----------------------------------------------------------------
SELECT @salary:=MAX(salary.salary) FROM salary;
UPDATE salary SET salary = salary *.75 WHERE
salary = @salary;
You have to work around the IN.
I'm not really great at perl yet. But hopefully
you will be able to make use of
the SQL
Good luck
JeffKRe:Try this
gizmo_mathboy on 2002-05-02T03:24:42
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.Re:Dude
koschei on 2002-05-02T06:00:41
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. And you definitely want connection pooling.Re:Dude
JeffK on 2002-05-02T12:46:22
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?
JeffKRe:Dude
koschei on 2002-05-02T12:59:47
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 available on Windows though) and it pays attention to the structure of your document. You don't just mark a line in heading style, you make a new section, or chapter or whatever. The text of the section is part of the section, rather than just happening to follow a section heading.
It's very sweet, but only available for RISC OS (or Windows under emulation).
Same with databases: I assume people use MySQL mostly because they don't know/care about the benefits of other systems (I love transactions - can't live without them; not happy with the MySQL ones yet). And there are the people who still use flat files and non-(relational/oo) databases.
Mind you, I have MySQL installed so as to play with Slash. When I get around to it (October 2005, I think) I may even look at really getting slash working on PostgreSQL.
Anyway. that's my rant, and it may well also be OTT =)
[That's "Over The Top" =) ]Re:Dude
gizmo_mathboy on 2002-05-02T14:49:11
Great. Now my journal is associated with that weird guy aka Cliffy (I'm guessing);-)
Oh well, we all have burdens to bear.:-)
Re:temp tables
gizmo_mathboy on 2002-05-02T14:53:51
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.
