Gossamer Forum
Home : General : Databases and SQL :

Help with printing to file...

Quote Reply
Help with printing to file...
I am very new to perl and mysql, but I have inherited an online voting program at my college that was written by a student last year. I can telnet into the database and view the tables I need to find out election results, but I think that there must be a way I can print these tables to a file and either convert it to an Exel or Access table (or at least have the information in a .txt file). Is there anything I can add to my

select * from votes;

command to get it to print to a file?



A second question I have is regarding the query of those tables. We have a list of candidates, votes, and logins that we keep. How could I query for people who voted for person A for office A, AND person B for office B. (as opposed to everyone who voted for every office, or people who only voted for a specific office or for a specific person).



Thanks in advance.
Quote Reply
Re: [Pupator] Help with printing to file... In reply to
Easiest thing to do may be to get something like mysqlman, perlmyadmin, phpmyadmin, all of which are cgis that make it very easy to handle your mysql dataases.

From there, there are ways of getting the data dumped quite easily into a file that can be loaded into Excel or Access. (CSV, TAB-delimieted, etc)

The second question is tricker, you may need to do a table join and a sneaky sum trick.

Last edited by:

Aki: Sep 12, 2002, 4:18 PM
Quote Reply
Re: [Pupator] Help with printing to file... In reply to
As AKI alluded to, do you have FK constraints between those tables???

Example:

PersonID
VotesID
OfficeID

If you have FK relations between your tables, then you could join them by those FK and then get the count that you are looking for.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Stealth] Help with printing to file... In reply to
I'm thinking for someone new to mysql it's going to be a nasty learning curve to get that query. Frown Offhand, assuming it's using basic relations, the query is going to look something like this. I didn't test this so I'm sure it won't work right away.

Code:
SELECT V.votername,
SUM( C.name = 'wanted_nameA' = O.name = 'wanted_nameA' ) AND
SUM( C.name = 'wanted_nameB' = O.name = 'wanted_nameB' ) as match

FROM
Candidates as C, Votes as V, Offices as O

WHERE
C.name = O.candidate_name_fk
and C.name = V.candidate_name_fk

GROUP BY V.votername

HAVING match

ORDER BY V.votername

Otherwise, it may just be best to write a program and get used to the DBI.

Last edited by:

Aki: Sep 12, 2002, 4:43 PM
Quote Reply
I agree with the learning curve comment... In reply to
Not only am I new to all this, but I'm not even running linux, I'm simply using telnet to get to the files. Also, is there anyway to, when I query the DB (such as select * from votes;) to be able to see everything in the table? After everyone has voted, there are over 3000 entries and when it srolls down, I can only see the last 200 or so. I don't suppose theres a way to "pipe to more" or something like that? Also, I'm still slightly confused on the best way to get it to a text file. Is there not a command that will just allow me to output the query to a file?
Quote Reply
Re: [Pupator] I agree with the learning curve comment... In reply to
Hi,

Try:

SELECT * INTO OUTFILE '/tmp/results.txt' FROM votes;

and it will put them into a tab delimited file you can import into excel. See:

http://www.mysql.com/doc/en/SELECT.html

for more info.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Thanks, but now... In reply to
since I'm not actually on the machine running the server, how can I get that file that is now saved in the /tmp directory to my hard drive or a floppy or something where I can get it...



ahh - it's going to be a long night.
Quote Reply
Re: [Pupator] Thanks, but now... In reply to
File Transfer Protocol (FTP) or Secure Shell (SSH) FTP, if you have FTP deamon running on the server.
========================================
Buh Bye!

Cheers,
Me