php-mysql install on redhat
I am attempting to use Redhat's update to get the latest version of php-mysql. Howeever, each time I try it prompts: Unresolvable chain of dependencies: MySQL-bench 4.0.21-0 requires MySQL-client Running rpm -qa | grep MySQL gives me: MySQL-client-4.0.21-0 ... and a host of other things. mysqld is also running. Should I scrap this plan, and just install from source via AB Mysql? Or is there another way? I'm wondering if my versions are incompatible with one another. Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: where is my my.cnf files??
What you need to do is rename one of the .cnf files - such as my-small.cnf - to my.cnf and copy it into your /etc/ directory (on Linux, Windows directory in Windows). - Eve -Original Message- From: Hiu Yen Onn [mailto:[EMAIL PROTECTED] Sent: Thursday, December 09, 2004 3:28 PM To: [EMAIL PROTECTED] Subject: where is my my.cnf files?? hi, i compiled mysql-4.1.7 from source. actually, i want to configure a mysql cluster. from the documentation, i need to add some flag into a file called my.cnf. i searched through the files. it consisted of my-small.cnf, my-medium.cnf, my-huge.cnf. but, i cant see the file my.cnf file. where does it located pls enlighten me..thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best learning path to DBA?
I am an web designer / php programmer / unofficial network administrator with a well-rounded technical background - but, as they say, a jack of all trades (expert in nothing). If I were to pursue a DBA path, what would be a good way of going about it? School, books, etc... - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trying to dump from GUI client
I am trying MySQL Query Browser, but I get this error... SELECT * INTO OUTFILE 'c:\temp\candidate.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' FROM candidate; ERROR 1045: Access denied for user: 'wowdba'@'%' (Using password: YES) ...even though I am logged in via the client. -Original Message- From: Adam [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 7:04 PM To: [EMAIL PROTECTED]; MySQL General Subject: Re: Trying to dump from GUI client Eve, From the command line you can use `mysql`, command line tool that ships with MySQL. You would want to use the SELECT ... INTO OUTFILE 'file.txt' FROM ...; See the MySQL manual for more information on this: - http://dev.mysql.com/doc/mysql/en/SELECT.html You can use another MySQL client. Such as Toad for MySQL or MySQL query browser - see URLs below. Toad for MySQL - http://www.toadsoft.com/toadmysql/toad_mysql.htm MySQL Query Browser: - http://dev.mysql.com/downloads/query-browser/index.html Both of these tools will allow you to export a record set as a comma delimited file. Good luck! Regards, Adam Eve Atley I'm not sure how best to proceed in dumping data from 1 database and getting a copy of the export, in order to transfer it to another server. I usually use phpmyadmin to do an export, which nicely creates a .zip file of everything. I managed to get it connected with Mysql Control Center, but am not sure how to dump from this. So I figure I can: A. use a command line (in which case, what commnands should I use to dump and export to a file), Or B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config file for this), Or C. learn how to dump from MySql CC (how? I saw no way of handling this from MySQL CC), Or D. use another GUI client (which one?). The server in question is mysql.loosefoot.com. Oddly, it was connecting fine until my company decided to move to a new server, and suddenly, it throws an error that Connection to database failed: Unknown MySQL Server Host 'mysql.loosefoot.com' (0). I've changed *nothing* in my connect script, and as mentioned, I can connect to the database via other means. What would you suggest as the least painful solution? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trying to dump from GUI client
Alright, using TOAD, I managed to get my data into CSV. When using PHPMYADMIN to load into another database, it tells me it can't read the file! -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 4:00 PM To: Adam; [EMAIL PROTECTED]; MySQL General Subject: RE: Trying to dump from GUI client If I remember correctly, with select into outfile the outfile has to be on the server. -Original Message- From: Adam [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 4:04 PM To: [EMAIL PROTECTED]; MySQL General Subject: Re: Trying to dump from GUI client Eve, From the command line you can use `mysql`, command line tool that ships with MySQL. You would want to use the SELECT ... INTO OUTFILE 'file.txt' FROM ...; See the MySQL manual for more information on this: - http://dev.mysql.com/doc/mysql/en/SELECT.html You can use another MySQL client. Such as Toad for MySQL or MySQL query browser - see URLs below. Toad for MySQL - http://www.toadsoft.com/toadmysql/toad_mysql.htm MySQL Query Browser: - http://dev.mysql.com/downloads/query-browser/index.html Both of these tools will allow you to export a record set as a comma delimited file. Good luck! Regards, Adam Eve Atley I'm not sure how best to proceed in dumping data from 1 database and getting a copy of the export, in order to transfer it to another server. I usually use phpmyadmin to do an export, which nicely creates a .zip file of everything. I managed to get it connected with Mysql Control Center, but am not sure how to dump from this. So I figure I can: A. use a command line (in which case, what commnands should I use to dump and export to a file), Or B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config file for this), Or C. learn how to dump from MySql CC (how? I saw no way of handling this from MySQL CC), Or D. use another GUI client (which one?). The server in question is mysql.loosefoot.com. Oddly, it was connecting fine until my company decided to move to a new server, and suddenly, it throws an error that Connection to database failed: Unknown MySQL Server Host 'mysql.loosefoot.com' (0). I've changed *nothing* in my connect script, and as mentioned, I can connect to the database via other means. What would you suggest as the least painful solution? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trying to dump from GUI client
I'm not sure how best to proceed in dumping data from 1 database and getting a copy of the export, in order to transfer it to another server. I usually use phpmyadmin to do an export, which nicely creates a .zip file of everything. I managed to get it connected with Mysql Control Center, but am not sure how to dump from this. So I figure I can: A. use a command line (in which case, what commnands should I use to dump and export to a file), Or B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config file for this), Or C. learn how to dump from MySql CC (how? I saw no way of handling this from MySQL CC), Or D. use another GUI client (which one?). The server in question is mysql.loosefoot.com. Oddly, it was connecting fine until my company decided to move to a new server, and suddenly, it throws an error that Connection to database failed: Unknown MySQL Server Host 'mysql.loosefoot.com' (0). I've changed *nothing* in my connect script, and as mentioned, I can connect to the database via other means. What would you suggest as the least painful solution? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.20 for full-text searching with match against
We are switching web servers, and they have installed Mysql 4.0.20. One of our apps uses full-text boolean text searching with MATCH AGAINST. I wanted to ensure this version of MySQL supports it, or if I need a newer version of MySQL. Can someone verify? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
I can't tell you how *glad* I am to get this running! Big hugs to you,
Shawn!
It's running beautifully. My only question is, after I run the queries,
I notice it won't let me create the temporary table again (saying
'tmpCandidates' already exists). Do I just need to then log out of my
client (MySQL Control Center) and back in to get rid of that temp table?
As I'll need to change what it searches for (ie baan, peoplesoft, etc.).
Or is there a query I can put in at the end of the queries to destroy
the temporary table once through with it?
Final query setup posted below.
Thanks,
Eve
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value)
SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID;
INSERT IGNORE INTO wow.candidate_erp (Candidate_ID, Vendor_ID,
Last_Name, First_Name, Middle_Initial, Condition_Type, Employer,
Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments,
Working, Available, Start_Date, Location, HoldOnPeriod, Relocation,
Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
I think this will be my last question on the matter.
I was reading previous messages to the list regarding the boolean search
function, and its problems when searching with 3-letter search terms.
I'll need to search for sap as an ERP term. Is there an efficient way
to avoid getting something like 'sappy' when what I really want is just
'sap', nothing preceeding and nothing following?
Thanks,
Eve
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, October 25, 2004 12:54 PM
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: RE: Determining if query will work
I am so happy to get you working. Hopefully we helped some other along
the way ,too :-)
It's a good idea when working with data that you should always clean up
after yourself, regardless of what language you are using. Not only does
it free up resources faster it helps to make sure that you don't end up
with a contention issue like this.
I take the blame for this one! I gave you the CREATE TEMPORARY...
without the corresponding DROP TEMPORARY TABLE.
http://dev.mysql.com/doc/mysql/en/DROP_TABLE.html
Just put:
DROP TEMPORARY TABLE tmpCandidates
at the end of each pass and you will get rid of the temp table. No more
error message. Temp tables are connection specific so you don't have to
worry about more than one user/process sharing the same temp table,
unless they share the same database connection (connection pooling is
one example)
Come back to the list if you need any more help.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eve Atley [EMAIL PROTECTED] wrote on 10/25/2004 12:48:43 PM:
I can't tell you how *glad* I am to get this running! Big hugs to you,
Shawn!
It's running beautifully. My only question is, after I run the
queries,
I notice it won't let me create the temporary table again (saying
'tmpCandidates' already exists). Do I just need to then log out of my
client (MySQL Control Center) and back in to get rid of that temp
table?
As I'll need to change what it searches for (ie baan, peoplesoft,
etc.).
Or is there a query I can put in at the end of the queries to destroy
the temporary table once through with it?
Final query setup posted below.
Thanks,
Eve
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value)
SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID;
INSERT IGNORE INTO wow.candidate_erp (Candidate_ID, Vendor_ID,
Last_Name, First_Name, Middle_Initial, Condition_Type, Employer,
Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments,
Working, Available, Start_Date, Location, HoldOnPeriod, Relocation,
Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
RE: Determining if query will work
Sorry for the delay in this. Still having problems with this one.
The second query is going to produce one row for every resume. Based on
your results, I assume you have 1000 resumes that have candidates
associated with them.
Let's say I have 256 resulting rows for table 'resume', Section_ID 1,
where the word 'peoplesoft' was found. What I'm really trying to do is
get the 256 that come up in the first query, and then work with only
that 256. The way the table 'resume' is set up is with the following
fields:
Candidate_ID
Section_ID
Section_Value
Section_ID 1 is where all basic text in a resume is stored; there are a
total of 6 sections, each section holding something different. I must
search first on Section 1 for references to 'peoplesoft'. Ultimately,
each candidate will have 6 total sections in resume - in this case, 256
x 6 = 1536. Therefore, I would need the second query to return 1536,
returning results for all candidates matching for Section 1, then using
the Candidate_ID to find all resulting rows in table 'resume'.
I also notice that ALL of the fields in your select statement come from
the candidate table. If that's truly the case, why do you need to JOIN
to
the resume table?
See above. :) Resume material is boolean-searched, then I have to copy
that Candidate_ID from *both* tables 'candidate' and 'resume' into
'candidate_erp' and 'resume_erp'.
Thanks,
Eve
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 20, 2004 3:50 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Determining if query will work
SELECT *
FROM wow.resume r
INNER JOIN wow.candidate c
ON c.Candidate_ID = r.Candidate_ID
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, r.Section_ID,
r.Section_Value
FROM wow.resume r
INNER JOIN wow.candidate c
ON r.Candidate_ID = c.Candidate_ID;
INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working,
Available, Start_Date, Location, HoldOnPeriod, Relocation,
Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country) SELECT SQL_CALC_FOUND_ROWS c.candidate_id,
c.Vendor_ID, c.Last_Name, c.First_Name, c.Middle_Initial,
c.Condition_Type, c.Employer, c.Country_ID, c.Visa_Status, c.Dt_Visa,
c.MMDD_Birth, c.SSN, c.CSG_Comments, c.Working, c.Available,
c.Start_Date, c.Location, c.HoldOnPeriod, c.Relocation,
c.Tech_Ranking, c.Comm_Ranking, c.Availability, c.Cert_Comments,
c.Dt_Submitted, c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country FROM wow.resume r INNER JOIN wow.candidate c
ON r.Candidate_ID = c.Candidate_ID;
I changed the boolean search to what I was matching against (this time
'peoplesoft') with a resulting 256 rows.
I ran the first two queries, which resulted in part 1 returning 256
rows, part 2 affecting 1000 rows, and nothing entering into the
database candidate_erp. The third resulted in ERROR 1136: Column count
doesn't match value count at row 1.
The second query is going to produce one row for every resume. Based on
your results, I assume you have 1000 resumes that have candidates
associated with them.
In the third query you left out a field (c.def_rate, perhaps?). Here are
your fields lined up vertically
INSERT fieldsSELECT fields
Candidate_ID,c.candidate_id,
Vendor_ID, c.Vendor_ID,
Last_Name, c.Last_Name,
First_Name, c.First_Name,
Middle_Initial, c.Middle_Initial,
Condition_Type, c.Condition_Type,
Employer, c.Employer,
Country_ID, c.Country_ID,
Visa_Status, c.Visa_Status,
Dt_Visa,c.Dt_Visa,
MMDD_Birth, c.MMDD_Birth,
SSN,c.SSN,
CSG_Comments, c.CSG_Comments,
Working,c.Working,
Available, c.Available,
Start_Date, c.Start_Date,
Location, c.Location,
HoldOnPeriod, c.HoldOnPeriod,
Relocation, c.Relocation,
Tech_Ranking, c.Tech_Ranking,
Comm_Ranking,c.Comm_Ranking,
Availability, c.Availability,
Cert_Comments, c.Cert_Comments,
Dt_Submitted, c.Dt_Submitted,
Def_Rate,
Def_Rate_Unit, c.Def_Rate_Unit,
Other_Country, c.Other_Country,
Currency_id,c.Currency_id,
Interview_Availability, c.Interview_Availability, Interview_Contact,
c.Interview_Contact, US_Experience, c.US_Experience, Location_Country
c.Location_Country
I also notice that ALL of the fields in your select statement come from
the candidate table. If that's truly the case, why do you need to JOIN
to
the resume table?
I
RE: Determining if query will work
Incidentally, we are using a newer 4 version of MySQL now, if there's
another way apart from a temp table to do this?
Let me try with the temp table meanwhile. Thanks!
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, October 22, 2004 1:27 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Determining if query will work
I would simplify this search by breaking the query into two steps, just
as
you described. First locate all of the resumes where section 1 contains
the bit of text you are looking for then use those results to get the
full
resume (all 6 sections)
There are 3 ways to do this. One is a single-query method but it will
require a rather complex set of JOINS and I don't think the response
from
it will be good enough (it should be quite slow, based on my
experience),
one uses a subquery which your current version of MySQL may not support
(last I read, you were on 3.23.58), and the third which I show below
uses
a TEMP TABLE:
CREATE TEMPORARY TABLE tmpCandidates
SELECT DISTINCT Candidate_ID
FROM resume
WHERE Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country
FROM tmpCandidates tc
INNER JOIN wow.candidate c
ON tc.Candidate_ID = c.Candidate_ID;
If you had 200 resumes from 150 candidates where section 1 matched
peoplesoft and each resume had 6 sections, this will give you 150
candidate returns (you didn't get all 200 resume hits because I
eliminated
the duplicate candidate matches with the DISTINCT keyword). If you want
to
get each set of candidate information along with their resume
information,
you will end up with all columns of the candidate information being
duplicated 6 times, once for each resume section. That query would look
like:
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country, r2.section_id,
r2.section_value
FROM tmpCandidates tc
INNER JOIN resume r
on r.candidate_ID = tc.candidate_id
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
This will return the full 1200 rows (200 resume matches x 6 sections per
resume) because we re-introduced (by joining to the Resume table) the
multiple resumes for each candidates.
Once you have tuned your select queries to return what you want, it's
trivially simple to add the INSERT (...) clause to the beginning of it
to
get the results to go into a table instead of to your client.
Keep me informed on your progress, please. Thank you for being so
patient.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eve Atley [EMAIL PROTECTED] wrote on 10/22/2004 12:43:26 PM:
Sorry for the delay in this. Still having problems with this one.
The second query is going to produce one row for every resume. Based
on
your results, I assume you have 1000 resumes that have candidates
associated with them.
Let's say I have 256 resulting rows for table 'resume', Section_ID 1,
where the word 'peoplesoft' was found. What I'm really trying to do is
get the 256 that come up in the first query, and then work with only
that 256. The way the table 'resume' is set up is with the following
fields:
Candidate_ID
Section_ID
Section_Value
Section_ID 1 is where all basic text in a resume is stored; there are
a total of 6 sections, each section holding something different. I
must search first on Section 1 for references to 'peoplesoft'.
Ultimately, each candidate will have 6 total sections in resume - in
this case, 256 x 6 = 1536. Therefore, I would need the second query
to return 1536, returning results for all candidates matching for
Section 1, then using the Candidate_ID to find all resulting rows in
table 'resume'.
I also notice that ALL of the fields in your select statement come
from
the candidate table. If that's truly the case, why do you need to
JOIN
to
the resume table?
See above. :) Resume material is boolean-searched, then I have to copy
that Candidate_ID from *both* tables 'candidate' and 'resume
RE: Determining if query will work
I think we're on to something. I've got one table importing data
correctly, resume to resume_erp. However, 'candidate' to 'candidate_erp'
appears goofy. I get the following error:
[mysql.loosefoot.com] ERROR 1062: Duplicate entry '1185' for key 1
With the following queries, query 3 being the problematic one:
# Connection: mysql.loosefoot.com
# Host: mysql.loosefoot.com
# Saved: 2004-10-22 14:51:15
#
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE);
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available,
Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking,
Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate,
Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability,
Interview_Contact, US_Experience, Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
But I think we're almost there:)
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
What is the definition of resume_erp (what is the result of: SHOW CREATE TABLE resume_erp) ? What that error is telling you is that you are Glad you added more info, or I would have been asking you, 'what do you mean by definition?' :) CREATE TABLE `resume_erp` ( `Candidate_ID` int(10) NOT NULL default '0', `Section_ID` int(10) default NULL, `Section_Value` longtext ) TYPE=MyISAM DEFAULT CHARSET=latin1 And if you need candidate_erp...I had figured candidate_erp was the problem?... CREATE TABLE `candidate_erp` ( `Candidate_ID` int(11) NOT NULL auto_increment, `Vendor_ID` longtext, `Last_Name` longtext, `First_Name` longtext, `Middle_Initial` longtext, `Condition_Type` longtext, `Employer` longtext, `Country_ID` longtext, `Visa_Status` longtext, `Dt_Visa` longtext, `MMDD_Birth` longtext, `SSN` longtext, `CSG_Comments` longtext, `Working` longtext, `Available` longtext, `Start_Date` longtext, `Location` longtext, `HoldOnPeriod` longtext, `Relocation` longtext, `Tech_Ranking` longtext, `Comm_Ranking` longtext, `Availability` longtext, `Cert_Comments` longtext, `Dt_Submitted` longtext, `Def_Rate` longtext, `Def_Rate_Unit` longtext, `Other_Country` longtext, `Currency_id` longtext, `Interview_Availability` longtext, `Interview_Contact` longtext, `US_Experience` longtext, `Location_Country` longtext, PRIMARY KEY (`Candidate_ID`) ) TYPE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, r.Section_ID, r.Section_Value FROM wow.tmpCandidates tc INNER JOIN wow.resume r on r.Candidate_ID = tc.Candidate_ID INNER JOIN wow.candidate c ON c.Candidate_ID = tc.Candidate_ID; The only reason you need to JOIN the table candidate to this query is to make sure that you don't add any records to resume_erp unless they have a corresponding record in candidate. If you are sure that all resumes still have their corresponding candidate records then you can drop the second INNER JOIN and just use the candidate_ID column from either resume or tmpCandidates (your choice) in your SELECT clause. Hmm, ok, thanks for this. Tells me I can safely drop te second INNER JOIN then. INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID FROM wow.tmpCandidates tc INNER JOIN wow.resume r on r.Candidate_ID = tc.Candidate_ID; - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Determining if query will work
I am attempting to copy data from 1 table into another, based on certain
criteria. I have set up the following queries, but am unsure if they
will function properly:
#This pulls back all data for matching candidates with keyword from
RESUME and CANDIDATE
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
= '1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
#This must copy all corresponding fields from RESUME into RESUME_ERP
*for each candidate ID previously found*
INSERT INTO wow.resume_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
#This must copy all corresponding fields from CANDIDATE into
CANDIDATE_ERP *for each candidate ID previously found*
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
SSN, CSG_Comments, Working, Available, Start_Date, Location,
HoldOnPeriod, Relocation, Tech_Ranking, Comm_Ranking, Availability,
Cert_Comments, Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country,
Currency_id, Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Am I on the right track?
Please read further for the kind of data returned, if necessary.
Thanks,
Eve
--
Here's the kind of data returned...
| Candidate_ID | Section_ID | Section_Value
| Candidate_ID | Vendor_ID | Last_Name | First_Name | Middle_Initial
| Condition_Type | Employer| Country_ID | Visa_Status | Dt_Visa
| MMDD_Birth | SSN| CSG_Comments
| Working | Available | Start_Date | Location| HoldOnPeriod |
Relocation | Tech_Ranking | Comm_Ranking | Availability |
Cert_Comments
| Dt_Submitted | Def_Rate | Def_Rate_Unit | Other_Country |
Currency_id | Interview_Availability | Interview_Contact|
US_Experience | Location_Country |
| 1185 | 1 |Information Technology
Strategic Systems Planning Development / Project Management / E-
Commerce / Technical Leadership/ Architectural Guidance/ Systems Design
Implementation / Organizational Structure
_
Management career encompasses a strong functional, technical, financial
and business acumen, including executive leadership in application
solutions, implementation, IT operations and teamwork. Experienced in
global business and information technology, ERP systems (MAPICS, BPCS,
JD Edwards, BAAN, ASK, Data 3. Having considerable experience and
expertise in the areas of distribution/logistics, materials management,
manufacturing, systems design and implementation, LAN/WANs, contract
negotiations and legal obligations, in addition to strategic business
planning.
SELECTED CAREER ACCOMPLISHMENTS
ó Designed and implemented a corporate-wide inventory and order
entry system that improved customer service and saved $70 million
dollars of inventory investment.
ó Automated internal operations through workflow/imaging systems
with an investment of $85,000 that saved $750,000 in annual engineering,
sales and accounting costs.
ó Instituted a technology leasing program that freed-up $8 million
in annual operating capital allowing for significant improvements in
infrastructure.
ó Implemented customer-focused manufacturing planning and
scheduling system that enabled sales growth of 70% in a world wide heavy
equipment machinery manufacturing company.
ó Consolidated contracts cutting cost for voice and data
networking services with annual savings of $200,000.
ó Re-implemented a failed ERP (BPCS) project with major gains in
on-time shipments, lower supply chain cost and improved management
control with an estimated value of almost $8 million dollars to the
corporation.
ó Created and marketed a successful educational game that taught
the basic principles of teamwork.
Systems experience includes: Enterprise Resource Planning (BPCS,
MAPICS), BPCS - Supply Chain Management, Human Resource Information
Systems (HRIS), CAD/CAM, Accounting and Financial reporting systems,
MRPII Class A certification, Configuration Management, Production
Scheduling, Forecasting, Inventory Management, Distribution (including
DRP), Purchasing, Warehousing, Imaging and Work Flow, and state of the
art manufacturing technologies, Process Flow, CIM, TQM. Sales Force
Automation and CRM.
| 1185 | 462 | Bishop | Jack|
| E | | 1 | N/A |
| | 123456 |
| 0 | 1 | 7/21/2003 | Lanchster PA| 24 hrs | No
| 5| 5| Immediate|
| 6/3/2003 | 90 | Hourly |
Determining if query will work
I am attempting to copy data from 1 table into another, based on certain
criteria. I have set up the following queries, but am unsure if they
will function properly:
#This pulls back all data for matching candidates with keyword from
RESUME and CANDIDATE
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
= '1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
#This must copy all corresponding fields from RESUME into RESUME_ERP
*for each candidate ID previously found*
INSERT INTO wow.resume_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
#This must copy all corresponding fields from CANDIDATE into
CANDIDATE_ERP *for each candidate ID previously found*
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
SSN, CSG_Comments, Working, Available, Start_Date, Location,
HoldOnPeriod, Relocation, Tech_Ranking, Comm_Ranking, Availability,
Cert_Comments, Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country,
Currency_id, Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Am I on the right track?
Please read further for the kind of data returned, if necessary.
Thanks,
Eve
--
Here's the kind of data returned...
| Candidate_ID | Section_ID | Section_Value
| Candidate_ID | Vendor_ID | Last_Name | First_Name | Middle_Initial
| Condition_Type | Employer| Country_ID | Visa_Status | Dt_Visa
| MMDD_Birth | SSN| CSG_Comments
| Working | Available | Start_Date | Location| HoldOnPeriod |
Relocation | Tech_Ranking | Comm_Ranking | Availability |
Cert_Comments
| Dt_Submitted | Def_Rate | Def_Rate_Unit | Other_Country |
Currency_id | Interview_Availability | Interview_Contact|
US_Experience | Location_Country |
| 1185 | 1 |Information Technology
Strategic Systems Planning Development / Project Management / E-
Commerce / Technical Leadership/ Architectural Guidance/ Systems Design
Implementation / Organizational Structure
_
Management career encompasses a strong functional, technical, financial
and business acumen, including executive leadership in application
solutions, implementation, IT operations and teamwork. Experienced in
global business and information technology, ERP systems (MAPICS, BPCS,
JD Edwards, BAAN, ASK, Data 3. Having considerable experience and
expertise in the areas of distribution/logistics, materials management,
manufacturing, systems design and implementation, LAN/WANs, contract
negotiations and legal obligations, in addition to strategic business
planning.
SELECTED CAREER ACCOMPLISHMENTS
ó Designed and implemented a corporate-wide inventory and order
entry system that improved customer service and saved $70 million
dollars of inventory investment.
ó Automated internal operations through workflow/imaging systems
with an investment of $85,000 that saved $750,000 in annual engineering,
sales and accounting costs.
ó Instituted a technology leasing program that freed-up $8 million
in annual operating capital allowing for significant improvements in
infrastructure.
ó Implemented customer-focused manufacturing planning and
scheduling system that enabled sales growth of 70% in a world wide heavy
equipment machinery manufacturing company.
ó Consolidated contracts cutting cost for voice and data
networking services with annual savings of $200,000.
ó Re-implemented a failed ERP (BPCS) project with major gains in
on-time shipments, lower supply chain cost and improved management
control with an estimated value of almost $8 million dollars to the
corporation.
ó Created and marketed a successful educational game that taught
the basic principles of teamwork.
Systems experience includes: Enterprise Resource Planning (BPCS,
MAPICS), BPCS - Supply Chain Management, Human Resource Information
Systems (HRIS), CAD/CAM, Accounting and Financial reporting systems,
MRPII Class A certification, Configuration Management, Production
Scheduling, Forecasting, Inventory Management, Distribution (including
DRP), Purchasing, Warehousing, Imaging and Work Flow, and state of the
art manufacturing technologies, Process Flow, CIM, TQM. Sales Force
Automation and CRM.
| 1185 | 462 | Bishop | Jack|
| E | | 1 | N/A |
| | 123456 |
| 0 | 1 | 7/21/2003 | Lanchster PA| 24 hrs | No
| 5| 5| Immediate|
| 6/3/2003 | 90 | Hourly |
RE: Determining if query will work
Based on Shawn's tips, I revised the query to read thusly:
SELECT *
FROM wow.resume r
INNER JOIN wow.candidate c
ON c.Candidate_ID = r.Candidate_ID
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, r.Section_ID, r.Section_Value
FROM wow.resume r
INNER JOIN wow.candidate c
ON r.Candidate_ID = c.Candidate_ID;
INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name,
First_Name, Middle_Initial, Condition_Type, Employer, Country_ID,
Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, Available,
Start_Date, Location, HoldOnPeriod, Relocation, Tech_Ranking,
Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, Def_Rate,
Def_Rate_Unit, Other_Country, Currency_id, Interview_Availability,
Interview_Contact, US_Experience, Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country FROM wow.resume
r
INNER JOIN wow.candidate c
ON r.Candidate_ID = c.Candidate_ID;
I changed the boolean search to what I was matching against (this time
'peoplesoft') with a resulting 256 rows.
I ran the first two queries, which resulted in part 1 returning 256
rows, part 2 affecting 1000 rows, and nothing entering into the database
candidate_erp. The third resulted in ERROR 1136: Column count doesn't
match value count at row 1.
Yes, you were close. May I suggest a little reading?
I'll give those links a look, though often the reading in there seems a
bit thick!
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do I need to add all states to an IN?
I have a resume form that picks consultants by state (such as an html guru
in California, or an SAP expert in Alabama). But what if I want to pick from
ANY state?
When I put in criteria, the SQL comes back like so...
SELECT SQL_CALC_FOUND_ROWS * FROM resume INNER JOIN candidate WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('+html' IN
BOOLEAN MODE) AND candidate.Candidate_ID = resume.Candidate_ID AND
candidate.Location IN ('CA', 'California') LIMIT 0 , 10
(note the candidate.Location IN('CA','California') ).
Now, when I want to pull back ANY state, I need to pull it back from
anywhere. If I leave the candidate.Location part off in my code, will it
automatically search anywhere regardless of state location? Or do I need to
go through my states database and put it into an array so that the IN
portion is filled in with all states?
If so... what's the proper structure to list all states in this statement?
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Insert Statement?
My query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Is it possible to do a multiple insert statement like so?
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, etc.)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Or do I have to break out the INSERT statements seperately?
Thanks!
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple Insert Statement?
Then I need help getting on the right track here. What I really want to do
is something like the following:
INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT
* FROM wow.resume r WHERE r.Candidate_ID = '13103';
INSERT INTO wow.candidate_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience, Location_Country)
SELECT *
FROM wow.candidate c
WHERE c.Candidate_ID = '13103';
Yet pulled from the resultset in this query:
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID =
'1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
Perhaps the above isn't set up correctly, as when I attempt these queries:
INSERT INTO wow.candidate_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.candidate;
INSERT INTO wow.resume_erp
(Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience, Location_Country)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume;
...it returns an error of 1136: Column count doesn't match value count at
row 1. The query INSERT INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) SELECT * FROM wow.resume r WHERE r.Candidate_ID = '13103';
is impractical when my results are over 400.
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Copying rows based on query result to another table?
Does anyone know if I can construct my insert/select statement this way
instead:
INSERT INTO wow.resume_erp
( field1, field2, etc)
INSERT INTO wow.candidate_erp
( field1, field2, etc)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value)
AGAINST ('+BAAN' IN BOOLEAN MODE)
AND c.Candidate_ID = r.Candidate_ID
-Original Message-
From: Peter Lovatt [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 14, 2004 1:29 PM
To: [EMAIL PROTECTED]; Mysql
Subject: RE: Copying rows based on query result to another table?
try
INSERT INTO other_table
( field1, field2, etc)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value)
AGAINST ('+BAAN' IN BOOLEAN MODE)
AND c.Candidate_ID = r.Candidate_ID
Peter
-Original Message-
From: Eve Atley [mailto:[EMAIL PROTECTED]
Sent: 14 September 2004 18:18
To: Mysql
Subject: Copying rows based on query result to another table?
I want to copy rows of data from a query result to another table. How do I
accomplish this? Here's my example for further explanation...
I have 2 tables, candidate and resume. I have 2 other (currently empty)
tables, exact duplicates, candidate_erp and resume_erp. In resume_erp will
be duplicates from resume that contain 'ERP' (Enterprise Resource
Planning)
keywords, ie. BAAN, SAP, Peoplesoft, and more. In candidate_erp
will be the
associated 'candidate' rows. When I construct my SQL query and
run it, I can
successfully view the resulting query which contain the keyword BAAN. How
can I then copy those results into candidate_erp?
A query example...
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value)
AGAINST ('+BAAN' IN BOOLEAN MODE)
AND c.Candidate_ID = r.Candidate_ID
I notice that MySQL CC and MySQL Query Browser both have the ability to
export to a file (ie. CSV), but it doesn't appear they will import neatly
into the tables candidate_erp and resume_erp.
Thanks in advance,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql and PHP
You will likely want to read this: http://us4.php.net/mysql In PHP 5, MySQL is no longer enabled by default, nor is the MySQL library bundled with PHP. Read this FAQ for details on why. Because of this, Windows users will need to enable php_mysql.dll inside of php.ini and either copy libmysql.dll into the Windows system directory or make it available to the PATH. For compiling, simply use --with-mysql=[DIR] where [DIR] points to your MySQL installation directory. - Eve -Original Message- From: andy thomas [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 15, 2004 11:03 AM To: nestor(earth) Cc: [EMAIL PROTECTED] Subject: Re: Mysql and PHP On Wed, 15 Sep 2004, nestor(earth) wrote: People, This more of a php mysql question. I have installed PHP ( 5.01) with Apache(1.31) and it runs. I have install Mysql (the latest as of last night) and it runs. My problem is that PHP does not see Mysql. Now I have done this installation 4 or 5 times but th elast time was over a year ago. Did you build both php and apache with MySQL support? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql and PHP
This is also very useful: Getting PHP 5 to work with MySQL http://www.phpbuilder.com/board/showthread.php?s=threadid=10246585 - Eve -Original Message- From: Greg Donald [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 15, 2004 11:10 AM To: nestor(earth) Cc: [EMAIL PROTECTED] Subject: Re: Mysql and PHP On Wed, 15 Sep 2004 08:04:08 -0700, nestor(earth) [EMAIL PROTECTED] wrote: This more of a php mysql question. I have installed PHP ( 5.01) with Apache(1.31) and it runs. I have install Mysql (the latest as of last night) and it runs. My problem is that PHP does not see Mysql. Now I have done this installation 4 or 5 times but th elast time was over a year ago. Any ideas? Perhaps this will help: http://www.wampserver.com/en/faq.php#q5 -- Greg Donald http://gdconsultants.com/ http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copying rows based on query result to another table?
I want to copy rows of data from a query result to another table. How do I
accomplish this? Here's my example for further explanation...
I have 2 tables, candidate and resume. I have 2 other (currently empty)
tables, exact duplicates, candidate_erp and resume_erp. In resume_erp will
be duplicates from resume that contain 'ERP' (Enterprise Resource Planning)
keywords, ie. BAAN, SAP, Peoplesoft, and more. In candidate_erp will be the
associated 'candidate' rows. When I construct my SQL query and run it, I can
successfully view the resulting query which contain the keyword BAAN. How
can I then copy those results into candidate_erp?
A query example...
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value)
AGAINST ('+BAAN' IN BOOLEAN MODE)
AND c.Candidate_ID = r.Candidate_ID
I notice that MySQL CC and MySQL Query Browser both have the ability to
export to a file (ie. CSV), but it doesn't appear they will import neatly
into the tables candidate_erp and resume_erp.
Thanks in advance,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
another insert/select for multiple tables... (or, determining previous auto-increment ID)
The question:
Is there anything in MySQL that will allow me to determine, accurately, the
last auto-incremented field from a particular database, so I can then insert
based upon this into another table? What if 2 users input at the same time?
Please see the 'long explanation' for further details.
Long explanation:
I've been reading over the previous thread for this. I am faced with a
similar situation and would appreciate some syntax assistance. I am only
partially finished.
I have 2 tables to insert into:
candidate
resume
When a new entry is entered via an INSERT statement I have set up, an
auto-increment integer is assigned (Candidate_ID) in the 'candidate' table.
This insert is structured in PHP:
$query = INSERT INTO candidate (Last_Name, First_Name, Middle_Initial,
Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN,
CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience, Location_Country)
values ('$lastname', '$firstname', '$middle', '', '$Employer', '', '',
'$visa_dt', '$MMDD_Birth', '$ssn', '$csg_comments', '$working',
'$Available', '', '$location', '', '$relocation', '$Tech_Ranking',
'$Comm_Ranking', '', '$cert_comments', '', '$Def_Rate', '$Def_Rate_Unit',
'', '', '$interview_availability', '$interview_contact', '$US_Experience',
'');
$result = mysql_query($query) or die(Error: . mysql_error());
--
Now, having entered that data, I need to insert different fields from my
form into 'resume'; syntax is probably the same, but where I'm stuck is how
to determine what auto-incremented number was assigned to the entry in
candidate, so I can then insert columns based on this.
resume is structured as so:
Field TypeNullDefault
Candidate_IDint(10) Yes NULL
Section_ID int(10) Yes NULL
Section_Value longtextYes NULL
Scenario is as follows:
New entry: James Brown, is assigned 10700 for Candidate_ID and is inserted
into table 'candidate'.
Following this insert, insert is required for 6 rows of data into table
'resume':
Candidate_ID must be 10700 / Section_ID = 1 / Section_Value = some form data
from textbox
Candidate_ID must be 10700 / Section_ID = 2 / Section_Value = some form data
from a different textbox
etc. until Section_ID 6.
How would I go about setting this up for resume, now that it's set up for
candidate?
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best method to export Excel data into MySQL?
What would be the best method (using free tools, or Office suite) to export an Excel spreadsheet into a format suitable for import into MySQL? I am on a PC platform. Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is overhead and what causes it?
As a newbie to MySQL, can folks explain to me what is 'overhead', and what causes it? (I sometimes get a very small overhead on 1 table and feel the urge to optimize it.) Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Assistance with SQL syntax: pulling duplicates back
I think this is an easy question...I've set up a SQL statement like so:
SELECT resume.Section_Value, candidate.Location FROM resume, candidate WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location LIKE '%CA%' OR 'California'
--
And where 'html' should come up in 1 entry, I get duplicates when printing
out the field to the screen:
--
html unix network php Over 10 years of HTML experience. 2 years networking
administration.
html unix network php Over 10 years of HTML experience. 2 years networking
administration.
--
I can't decide if this is my code, or the SQL syntax. Would it be possible,
based on this statement, to have pulled back duplicates from the same
record?
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to construct query for 1M records DB?
Hello all, I am stuck on the best way to proceed in order to make a SQL query efficient and quick searching through a very large (near million and growing) database. I am working with MySQL 3.23.58 and using PHP to construct a dynamic query, based on HTML form input. Please let me know if you need more information, or would like to see the code which constructs the SQL statement. My form fields: Skills Last Name First Name City State Tables: candidate resume Candidate fields: Candidate_ID Last_Name First_Name Location ..snip.. Resume fields: Candidate_ID Section_ID Section_Value --- I did not create this database, and changing it is really not an option. --- Within PHP, I construct a dynamic query, using only those fields that the user has submitted in the form. Example, where SKILLS and STATE have been filled in: SELECT resume.Section_Value, candidate.Location FROM candidates, resumes WHERE resume.Section_Value CONTAINS 'html' OR 'unix' AND resume.Section_ID = '1' AND candidate.Location LIKE '%CA%' OR '%California%' (note: this is necessary due to the way the original table was created. Location information is in one text field, with some entries entered as 'CA' and some as 'California') Or where Location only is selected: SELECT candidate.Location FROM candidate WHERE camdodate.Location LIKE '%CA%' OR '%California%' I have thought of approaching this 2 ways. 1. Search first for candidates meeting every other criteria, ie. State, or Last Name. Then drill down on any results for matching skills. 2. Construct the full query based on all matches based on fields entered, and then matching results between tables with Candidate_ID for display. Of course, what if there IS no other criteria to drill down upon? Right now, the form requires that a state always be present. However, I will need to build in the ability to search for anyone with HTML and Unix skills in any state. So if 'skills' are the only fields submitted, the dynamic query would look like: SELECT resume.Section_Value FROM resume WHERE resume.Section_Value CONTAINS 'html' OR 'unix' AND resume.Section_ID = '1' (resume.Section_ID is the section for skills) It would entail searching through millions of records. Is there any way to make this faster and/or more efficient, based on the way I'm handling it? How long would this search take for so many records? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text for keyword(s) search?
If I want to search for a word (or multiple words) in a field on one table, searching through at least a thousand records (and growing daily), should I be using full text or something else to make this search faster? I'm using MySQL 3.23.58. Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sql function for timestamp
While in PHP you can try something like this... $date_added = $row['date_added']; $date = date(M d, Y, strtotime($date_added)); echo $date; H and i do work in PHP as well, I believe, if you want to add the hour and seconds. Check out the 'date' function in PHP for info. - Eve -Original Message- From: Chinchilla Zúñiga, Guillermo [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 14, 2004 11:54 AM To: J S; [EMAIL PROTECTED] Subject: RE: sql function for timestamp In MySQL you can do that with: DATE_FORMAT DATE_FORMAT (column_name,'%Y-%m-%d %H:%i') -Mensaje original- De: J S [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 14 de Julio de 2004 08:26 a.m. Para: [EMAIL PROTECTED] Asunto: sql function for timestamp Hi, What SQL function do I need to convert timestamp 200406011403 into 2004-06-01 14:03 ? Thanks, JS. _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reset lost password on Server Logistics Mac OS X install
I'm attempting to follow instructions at the MySQL AB site: http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html ...in order to reset a lost root password. I am using the Complete MySQL package from Server Logistics (www.serverlogistics.com) for Mac OS X Panther. I've read the installation manual from Server Logistics; I've read the instructions at MySQL AB; but each comes up with mysql: command not found, mysqld_safe: command not found, etc. Where can I find these tools in a default install of the Server Logistics Complete MySQL? Has anyone using the same configuration and OS done a successful reset of a root password and is willing to share their steps? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Principles of Data-Base Management
Not specifically a MySQL question, but my boss has a lending library, and I found a book here titled Principles of Data-Base Management by James Martin, publish date of 1976. I thought I might peruse this to get a background on DB Mangement, but the publishing date concerns me. Has anyone read this book, and if so, is it worth reading? Is it safe to read in order to get a general background of concepts, or has database management changed enough to make the book obsolete? I'm concerned that the most recent programming lanaguage discussed here is COBOL, FORTRAN and assembly languages. :) Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need correct 'order by' syntax where field does not contain NULL
Hi. I had a MySQL DB set up and recently added a field 'order' to allow for exceptions in a web site menu heirarchy. Fields should be ordered by 'order' field first where it does not contain 'NULL', and then by field 'title'. I had this previously: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY title ...and now, when I put in: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY order, title ...my menu shows nothing. How can I write my statement to allow for ORDER by order where 'order' does not contain 'NULL', and then title? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Randomly selecting from table
Is it possible to randomly select from all entries in a table, but have it be 2 different ones each time? If so, what documentation should I be looking at? I am using PHP and MySQL together, if this helps. Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving file into database
Is there an advantage to storing the PDFs directly into the database? I'm also curious how large this would make a database. Is there any space saved through this method, or would they still be the same size as the original PDF? - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More on indexes?
Hi, I've been following the list to get a better grip on MySQL, and have been hearing a lot about indexing. I'm beginning to think I should have indexed some of my tables in the past. Can anyone point me in the right direction as to the purpose of indexes, why they are used, the benefits, and when one should index? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transferring comma-delimited list imto mysql table
I have a large email database that has been translated into a CSV, simply like this: [EMAIL PROTECTED];[EMAIL PROTECTED]; (etc.) I need to get this into SOME format. I have tried Access, which said it was too large - so I am trying my mysql. I created a table with fields ID, and email. I just want to put each email into the email field. I get this error: LOAD DATA LOCAL INFILE 'C:\\PHP\\uploadtemp\\php7.tmp' REPLACE INTO TABLE `email` FIELDS TERMINATED BY ';' MySQL said: #1148 - The used command is not allowed with this MySQL version Can anyone help me? Excel choked, Access choked. My intent is to pull all these from MySQL and into Outlook. If MySQL isn't the app to use, perhaps another solution can be recommended. Thanks so much, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sequrity question or am I paranoid?
Speaking of MySQL database security, what are some key things to keep in mind, other than changing the root password? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Require greater than 255 in varchar?
I have a large chunk of text I've attempted to put into a varchar field, and it chopped off a chunk of it. If I need to enter text that is greater than the default 255, what choice should I use instead? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: So, how do you REALLY install MySQL in Mac OS X (Panther)?!
I successfully used the packages from Server Logistics for this task, installing PHP, Apache and MySQL first on my 10.2.8 Jaguar, then on my 10.3.2 Panther G4. http://www.serverlogistics.com/mysql.php - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: So, how do you REALLY install MySQL in Mac OS X (Panther)?!
This one actually installs a preference pane that allow you to manipulate settings, start/stop server, and change root password. It's quite nice. - Eve -Original Message- From: sulewski [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 7:25 AM To: Eve Atley Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: So, how do you REALLY install MySQL in Mac OS X (Panther)?! I think this is the package I used. The one that I used actually wrote and init script so if you reboot mysql will start automagically. I think this is the one. Joe On Thursday, January 22, 2004, at 08:51 AM, Eve Atley wrote: I successfully used the packages from Server Logistics for this task, installing PHP, Apache and MySQL first on my 10.2.8 Jaguar, then on my 10.3.2 Panther G4. http://www.serverlogistics.com/mysql.php - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Removing default MySQL install
I've been trying to install a later version of MySQL (4.0.16). The Redhat default install is 3.x, and using rpm -e fails to remove the default packages due to 'dependencies'. How can I safely and successfully uninstall the default mysql packages (server and client) so I can start fresh? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installing MySQL on Linux, HELP!
I have had some help on my mysql install woes, and here's the latest. rpm -qa | grep MySQL - this just hung and never returned anything (see below) # service mysqld start or # service mysql start - these just returned a new command prompt, so couldn't tell whether or not they worked. I checked my processes and never saw mysqld. I put in 'whereis mysql', and this returned: /usr/bin/mysql /user/lib/mysql /user/share/mysql /user/share/man/man1/mysql.1.gz I was going to output a new list of installed packages to ensure that mysql was in there, but rpm -qa | ./installed_packages doesn't seem to work at the moment...I can't restart the server right now, so perhaps I could kill and restart some process? However, this is from yesterday's output: postgresql-libs-7.3.4-3.rhl9 postgresql-7.3.4-3.rhl9 mysql-3.23.58-1.9 mysql-server-3.23.58-1.9 postgresql-server-7.3.4-3.rhl9 Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installing MySQL on Linux, HELP!
After some trouble with rpm, I managed to create a file of installed packages. Redhat 9 says I have installed: MySQL-client-4.0.1-2 perl-DBD-MySQL-2.1021-3 mysql-3.23.58-1.9 mysql-server-3.23.58-1.9 And I am not having any luck with these...starting them, installing them, compiling them. I have read manuals, taken advice, but nothing comprehensive to get things to work. I would prefer to install a version of at least 4 for a server. Any help is appreciated! Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing MySQL on Linux, HELP!
Well, RedHat 9 says mysql is *already* installed. But I can't start it. And when I attempt to configure it using mysql_install_db, it says to run make install on it first. When I try to ./configure, it says it can't be found. (i know gcc exists) When trying make make install, it says nothing to be done. Now I think I'm just goofing things up. Ran a 4.0.1 rpm, and it said it installed; but still nothing. Then ran another one, and now it says dependencies are missing. I am following instructions from http://www.brtnet.org/linux/lamp.htm. - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using BETWEEN or = =
I am attempting to construct a select statement in which I can find values between two fields: start, and end. I have tried using BETWEEN and comparing with = and =, but neither meet success. Can someone please set me straight? This is meant to be in a PHP page, but I'm assuming the syntax is similar if not the same. I understand there is also a min/max, but I'm not sure it would work in this instance. $sql = SELECT * FROM federal-married WHERE start = .$_POST['salary']. AND end = .$_POST['salary']; Table: federal-married (finds federal tax rate based on marital status) start = min field, ie. 804 end = max field, ie. 2801 $_POST['salary'] = salary of individual posted from a form -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing a cross-reference table: tips?
I am in in the processing of laying out a database for a consulting firm. My basic structure is like so: placement_candidate (candidates listing) primary key = CandidateID placement_primarytech (skills listing) primary key = PrimaryTechID I have created an intended cross-reference table, placement_candidatetech, which blends in these two tables, with a format like so: CandidateID PositionsID PrimaryTechID Notes 1 0 4 1 0 7 1 0 9 1 0 13 2 0 1 2 0 4 I showed this to my boss who said, I don't think we need that, we're going to have thousands of users, that table will be huge. Granted, 1 user may have 50 skills. Currently, as you see, only 2 users are in the database (candidate 1 with 4 skills and candidate 2 with 2 skills). Is there a more efficent way of handling this? Something else I should be doing? I would have thought this would be a faster way of searching. Is there another way of setting up this cross-reference table? I am no database guru. Eve Atley
