Repeat records on results
I sort of know why this is happening. Just not sure how to correct it. My query: SELECT Reg.firstname,Reg.lastname, Profiles.ProfileName FROM Profiles INNER JOIN Reg ON (Profiles.LID = Reg.RegID) INNER JOIN PSkicerts ON Profiles.ProfileID = PSkicerts.ProfileID) where Profiles.Status != 2 The table PSkicerts has multiple records for each ProfileID. When I run a results set I get back a row of the same firstname, lastname and profilename for each record that exists in PSkicerts under the same ProfileID. Now if I add a DISTINCT, that seems to fix it, but this is really part of a bigger query that has more tables similar to PSkicerts. When I add those into the mix, DISTINCT seems to no longer help. Hope this makes sense. Perhaps someone knows how I can fix it. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database values to variables
I need to convert database values into php variables. Let me explain: i.e. select firstbase, secondbase, thirdbase, home from allstars where firstbase = 122; Now I want to use all those field names as variables with the same record information they would have if you just ran the above statement. i.e $fb = $firstbase $sb = $secondbase $tb = $thirdbase I just can't figure out how to do this. I've already driven the people in php-db mad. j/k What I need is to pull variables out for an email. Having the damdest time doing so. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database values to variables
--- Jay Blanchard [EMAIL PROTECTED] wrote: My suggestion is that you work some basic PHP/MySQL tutorials and use the PHP general list ([EMAIL PROTECTED]) for questions like this. Make sure you have RTFM, STFA, and STFW before posting, those guys can be merciless. Jay, thank you and I more then appreciate the code. I will learn something from this. Now what is STFA and STFW ? I won an acronym contest 2 years ago. I'm going downhill. PHP list, pt...I can handle it. Aren't you on the list ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Results question
I have a select, from, where, query set up. There are a number of inner joins in it as well. Now what I noticed is if there are some null fields in the records, nothing will get returned. If I remove those particular joins (where the NULLS are), the record is returned. Does this sounds like a join issue ? The tables that are joined are interpretive tables. Meaning I store a 1 for Alabama in the main table. The States table I join has the 1 and then the associate label. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where overload: Is there such a thing
I'm creating a search form, the query is dynamic. Meaning user can select 1 or all options. All options translates to about 40 where statements, including 1 -3 full text searches. Is this too many where statements ? Do I need to watch out for anything. Looking for any advice on this issue. Thank you Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question: Limit search on string
I'm storing telephone number (US) in 10 digit varchars. If I want to do a search on just the area code, is there a way to limit it to just the first 3 digits of the string ? Thank you Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question: Limit search on string
--- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm storing telephone number (US) in 10 digit varchars. If I want to do a search on just the area code, is there a way to limit it to just the first 3 digits of the string ? Thank you Stuart I'm trying something like this but still getting back the whole string: select Telephone from SignUp where Left (SUBSTRING(Telephone,1,3), 3) LIKE '4%' Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question: Limit search on string
--- sol beach [EMAIL PROTECTED] wrote: Of course you do. You are asking for all of Telephone to be returned. Duh! I'm trying something like this but still getting back the whole string: select Telephone from SignUp where Left (SUBSTRING(Telephone,1,3), 3) LIKE '4%' Great response sol ! Why did you send it offline ? Wouldn't it be better to do some sit ups then take your frustrations out on me ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question: Limit search on string
--- Roger Baklund [EMAIL PROTECTED] wrote: It's a bit unclear what you are trying to do. Are you trying to find all numbers within a 3 digit area code, i.e. numbers starting with some 3 digits? Or are you trying to find numbers with any of the three first digits equal to 4? For the first case, area code 444: SELECT Telephone FROM SignUp WHERE Telephone LIKE '444%'; ... and for the second case, finding the digit 4: SELECT Telephone FROM SignUp WHERE LEFT(Telephone,3) LIKE '%4%'; Thank you Roger. It's the 2nd case. Yes, using LEFT worked but as you saw , I was also trying to do a substring so the return would only have the first 3 digits (the area code). Perhaps that needs to be a seperate line of code. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]Question: Limit search on string
--- Stuart Felenstein [EMAIL PROTECTED] wrote: --- Roger Baklund [EMAIL PROTECTED] wrote: Yes, I had not included in my original post I wanted just the area code returned. It works - select SUBSTRING(Telephone, 1 ,3) from SignUp where Left (Telephone, 3) LIKE '4%' ; Thank you Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird query behaviour
or maybe it's me :) Anyway here is my table ++--+ | RecordID | School | | PID,AI,INT | Varchar| ++--+ | 108 | Columbia | +|--+ | 108 | Princeton | +|--+ | 108 | Stamford | +|--+ | 109 | USC| +|--+ | 109 | NYU| +|--+ | 109 | Columbia | +|--+ If I do this: SELECT School,RecordID FROM Profiles_Schools WHERE School = Columbia I get back RecordID's 108 and 109 But if in the where statment I add: where School = Columbia and School = Stamford Nothing is returned Am I doing something wrong. btw I have comp indexes on both columns. Thank you , Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird query behaviour
--- [EMAIL PROTECTED] wrote:
There is nothing weird about that behavior. You
asked for all of the rows
where the School column has both of two different
values at the same time.
I thought joins were difficult to comprehend ;)
Try an OR instead or use the IN() operator.
WHERE School='Columbia' OR School='Stamford'
WHERE School IN ('Columbia', 'Stamford')
See the difference?
Yes, now I do.
Shawn Green
Thank you
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird query behaviour
--- Roger Baklund [EMAIL PROTECTED] wrote: The WHERE clause describes EACH of the rows you get in the result. No one row can have a value in the School column equal to Columbia AND Stamford at the same time. You should use OR instead of AND. Thank you Roger. That is one of the best (maybe the best) description of the where clause. I have a far better understanding now. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bizarre table type switch
--- Heikki Tuuri [EMAIL PROTECTED] wrote: Stuart, you probably have skip-innodb in my.cnf. Best regards, Heikki Tuuri Heikki - Nope , doesn't seem so. My.cnf is below. Also, I'm guessing that if it was set to skip-innodb, I wouldn't not have had the ability to change them back. It's very puzzling. I'm not expecting to find an answer. The logs were checked and nothing found to inidicate anything. I'm on shared host. Supposedly my ISP did this for me. They are pretty reliable. But we are talking about 20+ tables. [mysqld] safe-show-database max_connections = 750 key_buffer = 136M myisam_sort_buffer_size = 72M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 1024 thread_cache_size = 128 wait_timeout = 5500 connect_timeout = 10 max_allowed_packet = 32M max_connect_errors = 10 query_cache_limit = 2M query_cache_size = 72M query_cache_type = 1 tmp_table_size = 72M read_rnd_buffer_size = 524288 bulk_insert_buffer_size = 72M [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 32M [myisamchk] key_buffer = 72M sort_buffer = 72M read_buffer = 24M write_buffer = 24M Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bizarre table type switch
I'm not sure what happened but when I ran some test yesterday on a transaction it failed. Being puzzled I started digging around. I have come to find out that all the tables involved were now set to MyISAM. Obviously transactions wouldn't work. The odd thing that I'd like to figure out is how did they get switched. I know this is a vague and gray question , and if there is a way to trace it I'd like to know. Is there anything that would cause this type of occurance ? Anything I can do to find out why it happened? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bizarre table type switch
--- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm not sure what happened but when I ran some test yesterday on a transaction it failed. Being puzzled I started digging around. I have come to find out that all the tables involved were now set to MyISAM. Obviously transactions wouldn't work. The odd thing that I'd like to figure out is how did they get switched. I know this is a vague and gray question , and if there is a way to trace it I'd like to know. Is there anything that would cause this type of occurance ? Anything I can do to find out why it happened? I forgot to mention this is 4.0.22-standard. I have requested my ISP check my.cnf to see if Innodb has been skipped for support. I am able to switch them back though. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATEDIFF() question
Does DATEDIFF only work with actual dates input i.e
('2004-12-05'
Or couldn't I do :
Select DATEDIFF (StartDate, EndDate) AS DaysBtwn from
mytable?
(StateDate,EndDate are date columns from db)
I'm generating a syntax error on this above.
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]DATEDIFF() question
--- Paul DuBois [EMAIL PROTECTED] wrote: There's a space beteeen the function name and the following parenthesis. Yes, that and not remembering I'm still on 4.0.22 :) Yeesh...sorry Paul. Switched to TO_DAYS ..no diff I guess Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert statement problem
I'm trying to build an insert query that will add a value into a field , after the first insert query adds the record. Relevant table information: +-+-++--+ | RecordID| InitOn | LenChoice | EndDate | + [PrimID,AutoInc]| [Date] | [int]| [Date] | +-+-++--+ So in the first insert the RecordID, InitOn (Using select NOW()), and LenChoice would be inserted. Looking something like this: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 | | +-+---++--+ Now I try to use (and I've tried an update statement as well: Insert MyTable (EndDate) Values(DATE_ADD(InitOn, INTERVAL LenChoice DAY)) Which I would hope to result in: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 |12/02/2004| +-+---++--+ However what is returned is an error message Column EndDate cannot be NULL. Anyway idea what I'm doing wrong ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]Insert statement problem
--- [EMAIL PROTECTED] wrote: I think you need an UPDATE statement UPDATE MyTable set EndDate=DATE_ADD(InitOn, INTERVAL LenChoice DAY) WHERE RecordID = value ; Insert *always* creates new records if successful and cannot be used to modify them. Update *always* updates recirds in position and cannot be used to insert them Replace is a hybrid whcih can do either if you set your indexes right. I think what you want is an Update, not an Insert. Alec Thank you Alex. It works. ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Messed up on auto inc
While what I'm working on is only a test database, I'd like to see what I can preserve. I used a data dumping program to create records in my database. However I'm not sure how I messed up , but here is the problem. I have 1016 records in a particular table. The first column is a Primary ID Int set to auto-inc. Now if I dump out all he records I can see 1 through 1016. None others , sorted it follows through numerically. However it seems that the records are showing up with the Primary ID going up to 9000. If I do a simple select * from table where primaryID = 8585 , a record is returned. Yet just looking at all the records, nothing exceeds the 1016. Strange phenomena I guess? Not sure if it's fixable but wanted to throw it out to the list and see at least if I can understand it better. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question: Marking records
I'm not even sure what this would be called, but maybe someone (or more then one) can give me some pointers and where to learn how this is down: Someone does a search on my system: 1- I need a way to mark (somewhere) that the record came up in a search. i.e. 700 records were returned in a search, each one needs to a) be marked that it came up in a search b) that number needs to be incremented 2- Step further, out of those 700 records, user chooses to view details on 30 of them - those records now need to be marked as viewed, again , incrementing everytime they are viewed is needed. Sorry, if this is another lame question. I am not looking for the code, just some ideas how these things get implemented. Using 4.0.22 Thank you. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question: Marking records
--- Jay Blanchard [EMAIL PROTECTED] wrote: [snip] 1- I need a way to mark (somewhere) that the record came up in a search. i.e. 700 records were returned in a search, each one needs to a) be marked that it came up in a search b) that number needs to be incremented 2- Step further, out of those 700 records, user chooses to view details on 30 of them - those records now need to be marked as viewed, again , incrementing everytime they are viewed is needed. [/snip] Additional tables to hold status information is in order. You would query those tables for their current counts and update as required. I'm thinking this through. In other words, when a record is returned in a search , and insert statement makes an entry into another table? This will involve scripting as well as sql statements ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question: Marking records
--- Brent Baisley [EMAIL PROTECTED] wrote: It depends on whether you are tracking info for individual users Individual users (lucky me!) Now, if you want to keep separate logs for each user, you need to create a log table. The log table would have the following fields: relatedRecordID, userID, searchCount, viewCount I just ran a search on Log Tables. I'm coming to the conclusion they are just tables , myisam or innodb. No different , except log is how they are used ? Is this a correct assumption ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limit error
I'm trying to paginate my record results. Running
into a sql syntax error that is boggling my limited
brain cells.
The print out of my statement as it is parsed:
This is before the error occurs, the first 15 records
return fine:
WHERE VendorJobs.Industry IN ('3') AND
VendorJobs.JobTitle LIKE '%%' AND
VendorJobs.LocationCity LIKE '%%' LIMIT 0, 15
I can't get a print, well maybe I can, but this is the
error that is coming back when I hit next:
Check the manual that corresponds to your MySQL server
version for the right syntax to use near 'LIMIT 15,
15' at line 6
I'll hold off on my statement for now if the error
isn't apparent.
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:[SOLVED] Limit error
Note to self, check variables for typos before posting to list! --- Stuart Felenstein [EMAIL PROTECTED] wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on date calculation +
While I'm figuring this needs to be addressed via my scripting language wanted to ask here. Customers will buy a block of time to use my service. Time meaning number of days. 90, 45, 60, etc. Here are the fields relevant to this question: PostStart [Date] LenChoice [int] DaysLeft [int] When they register , the current date is input automagically into PostStart. LenChoice is chosen by the user and is the length of days they want this block. DaysLeft is where the calculation would be done to hold the difference between the current date, date posted and how many days were paid for. This is where I'm not entirely sure what to do. I'm probably inhaling gasoline or something but how would I get the field to the numbers of DaysLeft ? i.e. DaysLeft[today]= 3, DaysLeft[tomorrow]=2..etc Thank you , Stuart p.s. I'm on 4.0.22 , so no stored procedures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on date calculation +
--- Bernard Clement [EMAIL PROTECTED] wrote: You will get your answers by reading carefully the Date Time Functions in the MySQL Reference Manual. This will tell me how to automatically update the column in question ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
I have a query statement set up for record returns based on various where statements. The select statement consists of a number of joins. One of those joins includes a field that is marked no null. Recently I did a mass insertion into the table. Into this particular no null field were place 0's (zeroes). Now the queries are not running correctly. I'm wondering if it's possible that these 0's could be effecting the query ? Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:[SOLVED] Query question
--- Brent Baisley [EMAIL PROTECTED] wrote: Quite possibly since 0 could also mean false depending on your comparison operator. For instance, using a generic if statement, these two would both evaluate to false: if(0) if(null) You should be very specific when checking for NULL. WHERE field IS NOT NULL or WHERE field IS NULL Also, you may want to look into the NULL safe comparison operator: http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html Thank you Brent. I discovered it was the 0's. Thank you for the information. I'll be doing my reading. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Conditonal where
--- Adams, Pat 006
[EMAIL PROTECTED] wrote:
You could add a where condition that's always true
to the main part of
the SQL statement so that you can just tack on more
clauses
conditionally.
$sql .= SELECT PostStart, JobTitle, Industry,
LocationState, VendorID
. FROM VendorJobs
. WHERE 1 = 1 ;
if ($s_Ind) {
$sql .= AND VendorJobs.Industry IN ($s_Ind) ;
}
if ($s_State) {
$sql .= AND VendorJobs.LocationState IN
($s_State);
}
See I knew I wasn't crazy as Rhino may have suggested
:). I had a vague idea that 1=1 would have worked but
sadly I did not test it. Anyway, I went with just a
blank array and as values are set , the where clause
grows dynamically.
Thanks for your help.
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Conditonal where
Question - I'm creating a dynamic query (using PHP)
but my question I think is more related to mysql
syntax.
Right now I have these statements:
?php
$sql .= SELECT PostStart, JobTitle, Industry,
LocationState, VendorID FROM VendorJobs;
if ($s_Ind){
$sql .= WHERE VendorJobs.Industry IN ($s_Ind);
}
if ($s_State){
$sql .= AND VendorJobs.LocationState IN ($s_State);
}
What I think I need is some kind of default WHERE in
the first statement. Both Ind and State are
conditional based on whether the user input anything.
Right now they would be forced to at least choose the
Ind. So instead of the $s_Ind have a WHERE it
should be an AND .
??Any thoughts / ideas.
Thank you
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditonal where
--- Rhino [EMAIL PROTECTED] wrote: No offense, Stuart, but I really can't make out what you are asking; the question is bordering on incoherent. Well it's probably true that I'm losing it :) Chris though understood even through my inane babbling. Except while the statements are correct, and no sql errors, the results aren't coming back. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditonal where
--- Chris [EMAIL PROTECTED] wrote:
What I tend to do in this situation is have a WHERE
array:
$sql .= SELECT PostStart, JobTitle, Industry,
LocationState, VendorID FROM VendorJobs;
$aWHERE = array();
if(isset($s_Ind)) $aWHERE[] =
VendorJobs.LocationState IN ($s_Ind)
if(isset($s_State)) $aWHERE[] =
VendorJobs.LocationState IN ($s_State)
if(!empty($aWHERE)) $sql .= 'WHERE '.implode(' AND
',$aWHERE);
Chris - thank you , it's working great now. Even
though someone had suggested this on php-general it
took me till now to get it to work!
Appreciate the help!
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL syntax error
I've had this going over on the php-general list. Thought I would throw it out here . Running PHP 4.0.22 Keep getting this error - SELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobsSELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobsWHERE VendorJobs.Industry = '2','3','4','5'Query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '.Industry = '2','3','4','5'' at line 2 The first is the printout of my statement followed by the mysql_error . Here is my code. This is driving me nuts. Sorry $sql = SELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobs; echo $sql; //if ($Ind) $sql .= WHERE VendorJobs.Industry = $s_Ind; As you can see above s_ind is an array , comma delimited. To me this all looks fine. to the parser, well ;) Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error
--- Jim Winstead [EMAIL PROTECTED] wrote: You can't compare a column with a comma-delimited list of numbers like that... What should the seperator be then ? Thank you Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:[SOLVED] SQL syntax error
--- Jim Winstead [EMAIL PROTECTED] wrote: My point was that you can't compare a column with an array of numbers using the '=' operator. You have to use the IN operator, as in the line of code I posted: Thank you Jim , it's working now! Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help! Question on backup
My database got hosed last night due to some version upgrade. Anyway an older copy was restored on the server. I have a more recent backup on my local machine. However, when I have done the data transfers I have found that the table names were convereted to all lower case. I'm assuming when I put them back on the linux machine that is going to be a problem since all my scripts call to the name with the lower and upper letters . Would that be correct ? And why do you think it converts everything to lowercase. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help! Question on backup
I apologize for the missing information. First the server was on 4.0.20 now on 4.0.22. Apache 1.3.x not sure of the linux The local machine is windows xp with mysql v. 4.0.20a I did the data transfer running Navicat, so that may not be so clear to anyone, but the only options that were enabled: 1-Create tables 2-Indclude all table create options 3-Create records They were transfered directly to the server. The tables are all fine ,outside of when they came to the windows machine all lowercase on the table names. I can rename them on the windows machine using upper case. I guess I'm still trying to figure out how the database got hosed anyway (still awaiting answer from web host). One possible guess is that most of my tables are Innodb. Not sure why , but perhaps its related. Stuart --- mysq -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query statement
I'm having a difficult time getting a query to work correctly. I'm not sure, if this is proprietary to Dreamweaver or not (that is what I do my application development in) Anyway I have created a search page that is dynamic (meaning up to the user to choose the criteria) It all works fine provided I am only querying the main table. Since that table is composed of some reference integers, joins are the only way to make it effective. ButI've tried formating as the following: SELECT `StaIndTypes`.`CareerCategories`, `USStates`.`States`, `staTaxTerm`.`TaxTerm`,... FROM VendorJobs`, `USStates, `staTaxTerm` INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry` = `StaIndTypes`.`CareerIDs`) LEFT OUTER JOIN `USStates` ON (`VendorJobs`.`LocationState` = `USStates`.`StateID`) LEFT OUTER JOIN `staTaxTerm` ON (`VendorJobs`.`TaxTerm` = `staTaxTerm`.`TaxTermID`) I've also tried: moving the joins into the where statement where vendorjob.industry = staindtypes.careerids and VendorJobs.LocationState = USStates.StateID Neither works inside the page - though the both work as just a straight query to the database. I'm not sure, myabe the code is broken somewhere else, but if I just put that one table in there select * from vendorjobs (it adds this): SELECT * FROM VendorJobs $MM_whereConst $whereClause order by PostStart desc I am not sure what the $MM_whereConst or %whereClause is , or what it should contain. So , after many hours of trying various things I thought I'd throw it out here and see if anyone understands. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help: Script stopped working
I had a transaction script that was working fine. Now I think it maybe due to a duplicate key error. At least that is the error being printed out. Here is the table Profiles_Skicerts[innodb]: P_ID - PK, Normal Index (int) Skicert - PK, Normal Index (varchar) N_Year - int, allow null L_Used - int, allow null Here is the printout of insert statement from echo: INSERT INTO Profiles_Skicerts (P_ID, SkiCert, N_Year, Lused) VALUES (14, 'one', 2,4) INSERT INTO Profiles_Skicerts (P_ID, SkiCert, N_Year, L_used) VALUES (14, 'two', 1,4) INSERT INTO Profiles_Skicerts (P_ID, SkiCert, N_Year, _used) VALUES (14, 'three', 7,6) INSERT INTO Profiles_Skicerts (P_ID, SkilCert, N_Year, L_used) VALUES (14, 'four', 5,4) INSERT INTO Profiles_Skicerts (P_ID, SkilCert, N_Year, L_used) VALUES (14, 'five', 7,5)1062 : Duplicate entry '14-five' for key 1 THe P_ID is non auto inc and is being passed over via mysql_insert_id() I don't understand where my mistake could be. Thank you . Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Online Store
Hope this is not a bad suggestion. I'd suggest also looking at something like x-cart-gold. I't s a php / mysql product and very robust for online ecommerce. If you want some ideas about what an online store would need and should contain , you might get some ideas. I'm not promoting the product. getting my flame retardent wear out;; Stuart --- [EMAIL PROTECTED] wrote: I heartily agree with Jay. Use paper and pencil to decide what information you want to maintain about each of the objects (users, customers, administrators, credits, debits, products, catalogs, descriptions, carts, cart contents, etc.) in your system. When your paper model supports the business model you want to have, now you are in a position where you can BEGIN the process of database design. In this case I would think that what you need to keep in your database will be heavily driven by the needs of the website, the billing department, the stock managers, and the sales managers. Basically, you need to make sure that all of the other business processes involved with this site have identified every piece of information they will need from you in order to do their jobs properly. Until they have, you will be working in the dark. Make sure your data consumers (all of those other people) sign off on what they give you as being complete (I practically guarantee that it won't be the first time around. But it's a start!). That way you can keep your project creep to a minimum and you will most likely avoid a major eleventh-hour rewrite because some other manager comes up to you and says, oh, by the way, where's the XXX data for this purchase? and you didn't have it in your model. Pencil and paper are your best friends. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jay Blanchard [EMAIL PROTECTED] wrote on 10/21/2004 01:50:18 PM: [snip] I am writing an online store for my company using MySQL, and PHP. I was wondering if anyone could suggest the table structure to include. [/snip] This is way too open ended for a sane answer. There are database structures for as many folks as have designed online stores. Have you done a flowchart, UML, or any other plan? This is the first place you start looking when trying to decide these things. If you haven't, stop now...get out a pencil and paper, and draw it up. -- 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: Transactions dilemma
Egor, Thank you , I wasn't sure anyone would ever respond to this post :) What I wound up doing is , from the application level, running an if / else. The if checks to see if each $query has succeeded. If any of them failed, I do a rollback. If they all have succeeded, I then do a committ. Now I'm not totally sure how a rollback would effect the one Myisam query. I mean it wouldn't , since rollback is alien to myisam, so I probably need to put a line in there to (whatever a rollback is in myisam language) as well. Stuart --- Egor Egorov [EMAIL PROTECTED] wrote: Stuart Felenstein [EMAIL PROTECTED] wrote: I have a slight dilemma. I am using transactions to insert data into multiple tables. All but one table is Innodb. That one is Myisam and it's left as such because its one text column, so I want the benefits of full text search. Still I need this transaction to somehow include this entry. Two thoughts : 1- I created a temp innodb table and then after transaction move the data over to the myisam. 2-Figure out what the text search options are in innodb and maybe if there is a way to improve on them. Any suggestions ? Consider LOCK TABLES: http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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]
Re: commit or rollback?
I'm relatively new to all of this but just about
finished setting up a transaction myself.
I'm doing something like this:
this is in php:( i also have functions set up for
begin, rollback and committ.
You should also set autocommitt to 0 .
Hope this helps!
Stuart
function run_query($sql)
{
$result = mysql_query($query);
if(!$result)
{
return false;
}else{
return true;
}
}
then:
begin();
$query = INSERT INTO firsttable.//first query
$res1 = run_query($query);
$query = INSERT INTO secondtable...//second query
$res2 = run_query($query);
$query = INSERT INTO thirdtable//third query
$res3 = run_query($query);
if($res1 $res2 $res3) // If all results are true
{
commit();
echo your insertions were successful;
}else{
echo mysql_errno($link). :
.mysql_error($link).\n;
rollback();
exit;
}
Hi all,
I'm working with tables stored by the InnoDB engine
and would like to
be able to commit only if there are no errors
generated by a group of
statements like this.
/* -*- sql -*- */
SET AUTOCOMMIT=0;
use db1;
begin work;
sql statement 1;
sql statement 2;
.
.
.
sql statement n;
At this point I'd like to say, in sql,
if no errors then
commit;
else
rollback
end
From what I read in the manual I can do one or the
other (commit or
rollback) but there didn't seem to be a way of
conditionally doing one
or the other of them.
Thanks in advance,
--
_\\|//_
( O-O )
---o00--(_)--00o--
Colm G. Connolly| Tel :
+353-1-716-2851
Department of Computer Science | Fax :
+353-1-269-7262
University College Dublin (UCD) | Web :
http://darwin.ucd.ie/
Belfield, Dublin 4 | MSN :
[EMAIL PROTECTED]
Éire / Republic of Ireland |
--
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]
Transactions - working but unsure about steps
My statements are all working but I'm not sure if
things are set up correctly. I say this because at
one point the first $query failed, yet the rest of
inserts wre committed. Now I believe I need to set
autocommit to 0 , yet the query failed due to a syntax
error. Hence the rule about 0 records effected
wouldn't be the case here.
Here is what I have:
//Create these functions -
function begin()
{
mysql_query(BEGIN);
}
function commit()
{
mysql_query(COMMIT);
}
function rollback()
{
mysql_query(ROLLBACK);
}
connection statement with error checking...
begin(); // transaction begins
$query = INSERT INTO firsttable.//first query
$result = mysql_query($query); // process first query
$query = INSERT INTO secondtable...//second query
$result = mysql_query($query); // process second query
$query = INSERT INTO thirdtable//third query
$result = mysql_query($query); // process third query
then:
if(!$result)
{
echo mysql_errno($link) . : . mysql_error($link).
\n;
rollback(); // transaction rolls back
exit;
}
else
{
commit(); // transaction is committed
echo your insertion was successful;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Transactions - working but unsure about steps
Thank you Osvaldo, That is what I was thinking.
Sometimes when you try to learn from example or manual
it's a bit unclear because they generally show a
simple transaction (1 insert into 1 table)
Stuart
--- Osvaldo Sommer [EMAIL PROTECTED] wrote:
You have a problem, what if the first insert give a
error, then you
don't know. You have to check each statement for
error and if no error
if found in all the statements then issue a commit
if not a rollback
Osvaldo Sommer
-Original Message-
From: Stuart Felenstein [mailto:[EMAIL PROTECTED]
Sent: Saturday, October 16, 2004 7:19 AM
To: [EMAIL PROTECTED]
Subject: Transactions - working but unsure about
steps
My statements are all working but I'm not sure if
things are set up correctly. I say this because at
one point the first $query failed, yet the rest of
inserts wre committed. Now I believe I need to set
autocommit to 0 , yet the query failed due to a
syntax
error. Hence the rule about 0 records effected
wouldn't be the case here.
Here is what I have:
//Create these functions -
function begin()
{
mysql_query(BEGIN);
}
function commit()
{
mysql_query(COMMIT);
}
function rollback()
{
mysql_query(ROLLBACK);
}
connection statement with error checking...
begin(); // transaction begins
$query = INSERT INTO firsttable.//first query
$result = mysql_query($query); // process first
query
$query = INSERT INTO secondtable...//second query
$result = mysql_query($query); // process second
query
$query = INSERT INTO thirdtable//third query
$result = mysql_query($query); // process third
query
then:
if(!$result)
{
echo mysql_errno($link) . : . mysql_error($link).
\n;
rollback(); // transaction rolls back
exit;
}
else
{
commit(); // transaction is committed
echo your insertion was successful;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release
Date: 10/15/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release
Date: 10/15/2004
--
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]
Transactions dilemma
I have a slight dilemma. I am using transactions to insert data into multiple tables. All but one table is Innodb. That one is Myisam and it's left as such because its one text column, so I want the benefits of full text search. Still I need this transaction to somehow include this entry. Two thoughts : 1- I created a temp innodb table and then after transaction move the data over to the myisam. 2-Figure out what the text search options are in innodb and maybe if there is a way to improve on them. Any suggestions ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions question
I'm in the midst of writing out some code that will take data from a huge form and write it to the database. All the tables for insertions are innodb so i'm going (or trying too) with the one stop shop approach - transactions. Question: The first table I write too has as it's first column an auto-inc int. This recordID will serve as the recordID for all the other tables that will need insertions. Meaning the same RecordID must go into each other table. I need to get that recordID first before continuing along with the transaction. But, in my understanding (and I could be wrong) nothing gets written until the very end ? So how do I get the recordID into the other tables. Right now - i do the insert into the first table and that is fine. I imagine , haven't worked it out yet, but following that insertion I need to do a select statement and in the application layer assign that number to a variable. Anyway once again Im running my mouth , so can someone tell me if I'm right or wrong ? and if I'm neither how I can go about keeping the transaction as one step. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions question
Sorry I found out about Last Insert_ID right after writing this. I guess the correct sequence is check manual then post to list ? Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm in the midst of writing out some code that will take data from a huge form and write it to the database. All the tables for insertions are innodb so i'm going (or trying too) with the one stop shop approach - transactions. Question: The first table I write too has as it's first column an auto-inc int. This recordID will serve as the recordID for all the other tables that will need insertions. Meaning the same RecordID must go into each other table. I need to get that recordID first before continuing along with the transaction. But, in my understanding (and I could be wrong) nothing gets written until the very end ? So how do I get the recordID into the other tables. Right now - i do the insert into the first table and that is fine. I imagine , haven't worked it out yet, but following that insertion I need to do a select statement and in the application layer assign that number to a variable. Anyway once again Im running my mouth , so can someone tell me if I'm right or wrong ? and if I'm neither how I can go about keeping the transaction as one step. Thank you, Stuart -- 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]
Converting date in MySQL
Hi, I am taking user input via a calendar widget (guess it's js) Apparently, mysql does not like the format MM/DD/ Then again I tried it around , still no dice. It's intended to go into a Date column. Is there a way I can correct it right within my query ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting date in MySQL
Thanks , I know the page and have the links bookmarked! Stuart --- Jeff Smelser [EMAIL PROTECTED] wrote: On Thursday 14 October 2004 02:45 pm, Stuart Felenstein wrote: Apparently, mysql does not like the format MM/DD/ Then again I tried it around , still no dice. It's intended to go into a Date column. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html All, you every need to know if right there.. Jeff ATTACHMENT part 2 application/pgp-signature -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting date in MySQL
No :),. cause it seems that those formats are for outbound, db -. I was looking for the other direction. Stuart --- Jeff Smelser [EMAIL PROTECTED] wrote: On Thursday 14 October 2004 03:12 pm, Stuart Felenstein wrote: Thanks , I know the page and have the links bookmarked! So you got the answer from it right? Jeff ATTACHMENT part 2 application/pgp-signature -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions: Inserts in database and validation
Does application validation constitute all that is needed for qualifying data prior to an insertion in the database ? Translated, If I have checked via my forms validation things like required fields, character input, etc, are there still checks through MySQL before the insert happens ? Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What am i up against
This maybe somewhat of a silly question. Scenario - I am pretty much a noob at both relational databases and web programming. I've built most of my pages using a RAD tool that, for the most part, does a decent job , if you keep it fairly simple. One of the limitations is inserts updates are done on a one form on one page into one table basis. There is a MtM feature. Anyway, now I'm flying solo and have created a form that spans 5 pages and will insert into (I lost count) I believe 3-5 tables. I want to make sure I make provisions for rollback. All but one table is innodb. While Im reading and digging around, wondering is this a massive insert statement ? Would joins need to be involved ? I'm imagining it's more of a step by step (1 table at a time) process. With rollback, if an insertion is already done into 1 table , and the insert into table 2 fails, does that mean table 1's insertion would be deleted ? I think that is probably enough and I apologize for asking what are basic questions and a bit scattered at that. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What am i up against
See interspersed: --- Joe Audette [EMAIL PROTECTED] wrote: If you are saying the user would navigate through multiple pages updating a table on each page and you want to treat all the updates collectively as one transaction, that is a bad idea. I agree, that is what I am trying to not do. You want to pass all the data required for a single transaction in one request so it can be committed or rolled back as part of the same request. Exactly what I want to accomplish. Web pages are generally stateless unless you are using session state variables which is not a good idea in terms of scalability. You don't want to keep transactions open from page to page. I don't understand this. I assume you are referring to the application session variables (and I'm using PHP). Yes I'm using session variables to collect the data. How am I keeping transactions open ? Since I don't want to do a transaction till the very end. All I'm doing is bringing the data to last stage. After it's all been collected. If you have a transaction that updates multiple tables and you roll it back, it will negate all changes (inserts, updates, deletes) that occurred within the transaction. Hope that helps, not sure I'm understanding your question. That's because my question was somewhat convoluted due to me not completely understanding all of it myself. Stuart Regards, Joe Audette -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tying records together across multiple tables.
Shawn, When I read your examples, it is clear as a bell. When I try to map it into my situation it becomes more difficult to clarify :) Using other job boards as examples, there are a couple of ways to go. Most of the bigger players offer job seekers the ability to store 5 resumes online. These are not just the actual resume though and are part of a profile. 5 profiles can be targeting or positioning the job seeker to 5 different types, jobs, locations. Method 1: User Table: MemberID, username, password,. Profile_Table: RecordID, MemberID, locationfield, jobtitle field Industry_Table: MemberID, LocationID, ProfileID User_Profile_Table: MemberID, ProfileID, LocationID Then I wonder about the job_titles field. There is a current job title, a seeking job title and an alternate job title. Do I implement: JT_Current_Table JT_Seeking_Table JT_Alternate_Table Same thing with inudstries. Database designers can work in many industries,so can other workers. Users can choose 10 inudstries: Is there a need to have Industry_1_Table Industry_2_Table Industry_3_Table Industry_4_Table. All I can say is I believe this would be a better design, but I can't get passed thinking, what for ? Why not Profile_Table: RecordID: MemberID: Industry1 Industry2 JobTitle1 JobTitle2 .. Like I say at the start here, difficult to apply to my situation. Maybe because it doesn't apply, but at the same time I can't see the implications of doing it via the last method. So, as painful as it's been for you with me, I'll ask if you see potential problems or what the issues could be to point them out to me. Thank you, Stuart --- [EMAIL PROTECTED] wrote: (I guess this means it's example-counterexample time) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tying records together across multiple tables.
--- [EMAIL PROTECTED] wrote: Let's do this verbally and maybe the database structure will become intuitively apparent You are hosting a website that supports job seekers (maybe more but let's concentrate on just this side of it) Each seeker (a User) can have a profile in the system Each profile describes who the User is (name), where they are (address, email address) and how to contact them (main phone, cell phone) Associated with each user is a list of up to 10 industries they have belonged to. Associated with each user is up to 5 resumes. Each user can be looking for employment in any of 5 locations Each user can be looking for employment in any of 5 industries Each user can look for employment under up to 5 different job titles. Please take the time to correct me and fill in any gaps. Please DO NOT attempt to make any data definitions at this stage. I need to understand what data you are going to maintain verbally before I can help you translate it into storage requirements. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 10/06/2004 02:47:44 PM: Shawn, When I read your examples, it is clear as a bell. When I try to map it into my situation it becomes more difficult to clarify :) Using other job boards as examples, there are a couple of ways to go. Most of the bigger players offer job seekers the ability to store 5 resumes online. These are not just the actual resume though and are part of a profile. 5 profiles can be targeting or positioning the job seeker to 5 different types, jobs, locations. Method 1: User Table: MemberID, username, password,. Profile_Table: RecordID, MemberID, locationfield, jobtitle field Industry_Table: MemberID, LocationID, ProfileID User_Profile_Table: MemberID, ProfileID, LocationID Then I wonder about the job_titles field. There is a current job title, a seeking job title and an alternate job title. Do I implement: JT_Current_Table JT_Seeking_Table JT_Alternate_Table Same thing with inudstries. Database designers can work in many industries,so can other workers. Users can choose 10 inudstries: Is there a need to have Industry_1_Table Industry_2_Table Industry_3_Table Industry_4_Table. All I can say is I believe this would be a better design, but I can't get passed thinking, what for ? Why not Profile_Table: RecordID: MemberID: Industry1 Industry2 JobTitle1 JobTitle2 .. Like I say at the start here, difficult to apply to my situation. Maybe because it doesn't apply, but at the same time I can't see the implications of doing it via the last method. So, as painful as it's been for you with me, I'll ask if you see potential problems or what the issues could be to point them out to me. Thank you, Stuart --- [EMAIL PROTECTED] wrote: (I guess this means it's example-counterexample time) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tying records together across multiple tables.
See interspersed. --- [EMAIL PROTECTED] wrote: Let's do this verbally and maybe the database structure will become intuitively apparent You are hosting a website that supports job seekers (maybe more but let's concentrate on just this side of it) Yep, there is the employer side. Hopefully I can easily translate my knowledge base over. Each seeker (a User) can have a profile in the system Each user (job seeker) can have 5 profiles. Each profile describes who the User is (name), where they are (address, email address) and how to contact them (main phone, cell phone) Yes and no. Profiles maybe created to target different types of jobs. i.e. I'm a salesman, I've worked in the software industry (don't laugh), but also in the Human Resources industry. Profile 1 reflects that I'm interested in software, Profile 2 reflects that I want to go back to HR. Another exmaple, I want full time work as a salesman, but until the right spot comes along I'll take part time work as a consultant. Profile 1 reflect that I'm interested in full time sales work. Profile 2 that I want part time consulting. Some of the fields are optional. Last example - I was a research scientist at NASA, but out of work, still interested in science, but will take anything. Profile 1 reflects Multiple Doctorates, Profile 2 shows High School Diploma , highest level. Now no one is forced to enter 5, it's an option. Associated with each user is a list of up to 10 industries they have belonged to. Now here is where each profile has another level to it. I'm a C## developer, and I've worked in the Airlines, Hospitality and Financial Industry. Maybe Fiancial pays best, but I'm willing to explore other industries, or due to an employers orientation, best to choose as many possible industries that may result in a match. So there are mulitple options on certain criteria within each profile. Industry, Location and Pay type (tax term, i.e. contract, full time, contract too full time) They may not have been associated with the industries before or live in a particular location but they are throwing their gloves into those areas to increase their chance of employment. Associated with each user is up to 5 resumes. Each profile has 1 resume. 5 Profiles = 5 resumes, but each resume should be tied into the particular profile it's part of. Each user can be looking for employment in any of 5 locations Well 2, 5 , haven't made my mind up, but it will be multiple Each user can be looking for employment in any of 5 industries See above and way above. Yes, multiple industries. Each user can look for employment under up to 5 different job titles. Slight different here. There is a current or most recent , a desired one, maybe the same as current. Also an alternate. Please take the time to correct me and fill in any gaps. Please DO NOT attempt to make any data definitions at this stage. I need to understand what data you are going to maintain verbally before I can help you translate it into storage requirements. Up to you, only a suggestion if you haven't seen a job board or looked at one closer. Dice.com , Monster, CareerBuilders, all have pretty much the same standard. Essentially , the more a user fills in on their profile, the more they answer, better the chances of finding opportunities. At the same time, the more field information avaiable about users/job seekers, the more refined a search can be before resumes are text searched or pulled up. Hopefully now my original question about tying all the responses / data from one profile together is important. Let me know if this is clearer. Thank you. Stuart - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tying records together across mulitple tables.
Great example and my apologies because as you know we've been down this road before. Side note - once again I did not provide my acutal table names , this time they haven't been created as of yet. Let me skinny this down though a bit and focus on one aspect of my design connundrum. Remember I am designing for a job board. Users can choose say up to 3 locations where they would like to look for work. So if my thinking is correct , this is a possible schema: Users_table * Location_table * Users_Location_table (the - MTM) Now I think the above is right, but I ask myself, what are the real drawbacks if I do something like this: Location_table: indentifying fields .(userID's, recordID's).. Location1 Location2 Location3 This is the point I know that is sticking me - understanding why the first example is better then the second. Thank you , Stuart --- [EMAIL PROTECTED] wrote: Tables are tied together by whichever field(s) you use to store their parent's reference. For one second, imagine I am writing an inventory control program for somebody like Wal-Mart or Target. Those businesses have so many locations that they are divided into regions, each region will have multiple warehouses, each region would also have multiple stores. Each store could be within supply range of several warehouses. Each warehouse can supply several stores. . Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tying records together across mulitple tables.
I've worked through some of this but still would like some opinions. Maybe it's not clear but I haven't received any responses. Basically to tie the records together I will use the recordID (auto incrementing) in every table where the records are written. Then I can grab everything out of that recordID. I'm not sure , can't resolve in my mind if this is a M2M or something else. I have 5 tables, users may enter multiple records in each table. The only trick is (for me) is how to tie a unified record together across all of them. I'll try to illustrate, and only use 2 tables to keep it brief. Table1 - Bob has 3 records 1st record - Spoken Language is Spanish 2nd record - Spoken Language is French 3rd record - Spoken Language is English Table 2 - Bob has 3 records 1st record - I am Spanish 2nd record - I am French 3rd reocrd - I am English Okay the table strutures: Table1Table2 RecordID (int, autoinc) RecordID (int, autinc) MemberID (int)MemberID (int) Language (varchar)Nationality (varchar) I'm trying to say here is a record , that would form the result of I am Bob, I speak English, I am English I know, this probably sounds a bit weird :) Best way I can come up with right now to illustrate. If someone was searching through records, they would say I found someone who is Spanish and yes, they are Spanish. Not, I found someone who is Spanish and they speak Spanish , French and English. I considered (as this is part of a web site) generating an ID and then passing it into each table entry as the forms (that comprise the process) are submitted. Just to clarify, 5 tables - 5 forms , all part of 1 web entry. Stuart -- 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]
foreign key problem
Two tables: Table1 [innodb] userID. addtlfields. Table2 [innodb] memberID. addtlfields. I created a foreign key in Table2 for memberID to userID set to no action on both delete and update. I get this error when I try to add a record to table 2:Cannot add or update a child row: a foreign key constraint fails I try and remove the foreign key and it won't let me: 1025-Error on rename of './mydatabase/table to './mydatabase/#sql2-695b-c4ac0'(errno: 152) I think can probably forgoe the foreign key , but what's with all the error messages ? Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key problem
I think I may have discovered one of my issues, is memberID in Table2 was primary key. Should not have been. As far as the error messages in removing key, I'm still unsure. Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: Two tables: Table1 [innodb] userID. addtlfields. Table2 [innodb] memberID. addtlfields. I created a foreign key in Table2 for memberID to userID set to no action on both delete and update. I get this error when I try to add a record to table 2:Cannot add or update a child row: a foreign key constraint fails I try and remove the foreign key and it won't let me: 1025-Error on rename of './mydatabase/table to './mydatabase/#sql2-695b-c4ac0'(errno: 152) I think can probably forgoe the foreign key , but what's with all the error messages ? Thank you, Stuart -- 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]
Tying records together across mulitple tables.
I'm not sure , can't resolve in my mind if this is a M2M or something else. I have 5 tables, users may enter multiple records in each table. The only trick is (for me) is how to tie a unified record together across all of them. I'll try to illustrate, and only use 2 tables to keep it brief. Table1 - Bob has 3 records 1st record - Spoken Language is Spanish 2nd record - Spoken Language is French 3rd record - Spoken Language is English Table 2 - Bob has 3 records 1st record - I am Spanish 2nd record - I am French 3rd reocrd - I am English Okay the table strutures: Table1Table2 RecordID (int, autoinc) RecordID (int, autinc) MemberID (int)MemberID (int) Language (varchar)Nationality (varchar) I'm trying to say here is a record , that would form the result of I am Bob, I speak English, I am English I know, this probably sounds a bit weird :) Best way I can come up with right now to illustrate. If someone was searching through records, they would say I found someone who is Spanish and yes, they are Spanish. Not, I found someone who is Spanish and they speak Spanish , French and English. I considered (as this is part of a web site) generating an ID and then passing it into each table entry as the forms (that comprise the process) are submitted. Just to clarify, 5 tables - 5 forms , all part of 1 web entry. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Telephone number column not working
I have a field telephone.
Set to type :int:
Length: 11
It's not working correctly, and not sure if it's my
application or something I have wrongly set up for the
database.
We are talking about U.S. Telephone numbers here, so 7
digits (area code, exchange, unique number)
Now it seems everything works up to the storing of 6
numbers. Once I add the 7th number, everything goes
haywire. The number gets transformed to some totally
different number and / or 0 (zero).
Now I had set up a validation , which I think would be
correct for a U.S. number:
[0-9\+\-\/ \(\)\.]+
Yet, even if I remove that regexp and let it validate
solely on integers: -{0,1}\d+
Nothing.
I thought perhaps enforcing the field to unsigned
might help, but no change.
One last note, I've now added some javascript to
enforce format. This hasn't changed anything , better
or worse. Same behaviour. This is solely for making
sure client enters 111-111- format. Just wanted
to include this in my information.
Well if anyone has a clue appreicate the help.
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Telephone number column not working
I guess that is why if I enter 703111
I get this back - 4294967295
Stuart
--- GH [EMAIL PROTECTED] wrote:
One issue could be that an int column unsigned can
only hold up to
4294967295 a ten digit number. Plus if you put it in
a context of a
phone number... only area codes 428 or lower will
have ALL THE
EXCHANGES and ALL THE UNIQUE NUMBERS in the range...
with part of area
code 429
A bigint will hold the complete range you are
looking for However,
I would sugest that since you mostlikely are not
going to be doing
mathematical operations on a phone number that you
use a varchar or
char field.
Maybe someone could correct me but aren't regex for
strings only?
Gary
On Sat, 2 Oct 2004 04:59:45 -0700 (PDT), Stuart
Felenstein
[EMAIL PROTECTED] wrote:
I have a field telephone.
Set to type :int:
Length: 11
It's not working correctly, and not sure if it's
my
application or something I have wrongly set up for
the
database.
We are talking about U.S. Telephone numbers here,
so 7
digits (area code, exchange, unique number)
Now it seems everything works up to the storing of
6
numbers. Once I add the 7th number, everything
goes
haywire. The number gets transformed to some
totally
different number and / or 0 (zero).
Now I had set up a validation , which I think
would be
correct for a U.S. number:
[0-9\+\-\/ \(\)\.]+
Yet, even if I remove that regexp and let it
validate
solely on integers: -{0,1}\d+
Nothing.
I thought perhaps enforcing the field to unsigned
might help, but no change.
One last note, I've now added some javascript to
enforce format. This hasn't changed anything ,
better
or worse. Same behaviour. This is solely for
making
sure client enters 111-111- format. Just
wanted
to include this in my information.
Well if anyone has a clue appreicate the help.
Stuart
--
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: Telephone number column not working
--- Paul DuBois [EMAIL PROTECTED] wrote: Are you trying to store telephone number values with the intermediate dashes? Such values are not actually numbers. You'll need to store them as strings, or else remove the dashes. Yes, they made all the difference. Set the field type to varchar, and the input type to string. Correct numbers including hyphens now in database. Wondering about one more issue, could changing this to input string, leave me open to some type of SQL injection ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data fields from one database to another
I want to move over 2 - 4 fields from a table in one database , to a table in another. The field names are not an exact match but they function identically (i.e. username, password) Whatever I'm using , web development garbage program ;) won't allow me to connect to 2 different databases so I can't do an update or insertion through that means. Wondering how I script something like that, and probably prefer a way to do it on the fly with each new sign up , or batched on a regular basis aka, every hour , every few hours. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data fields from one database to another
So it will let me transfar individual fields ? Most of the clients allow for data transfer provided the database schema is the same, and then it is the entire record. Stuart --- Tim Hayes [EMAIL PROTECTED] wrote: Try using MYdbPAL - its a new free program that will do the job plus lots of other goodies. www.it-map.com Tim Hayes -Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: 28 September 2004 08:23 To: [EMAIL PROTECTED] Subject: Data fields from one database to another I want to move over 2 - 4 fields from a table in one database , to a table in another. The field names are not an exact match but they function identically (i.e. username, password) Whatever I'm using , web development garbage program ;) won't allow me to connect to 2 different databases so I can't do an update or insertion through that means. Wondering how I script something like that, and probably prefer a way to do it on the fly with each new sign up , or batched on a regular basis aka, every hour , every few hours. Thank you, Stuart -- 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]
RE: Data fields from one database to another
Been trying it out. Seems to be very powerful! Thank you , Stuart --- Tim Hayes [EMAIL PROTECTED] wrote: Yes. Absolutely. MYdbPAL has a complete schema-to-schema table and data field mapping capability that will also let you do things like table splits and joins. It also has inbuilt scripting and data value translation lookups. Timk -Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: 28 September 2004 09:57 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Data fields from one database to another So it will let me transfar individual fields ? Most of the clients allow for data transfer provided the database schema is the same, and then it is the entire record. Stuart --- Tim Hayes [EMAIL PROTECTED] wrote: Try using MYdbPAL - its a new free program that will do the job plus lots of other goodies. www.it-map.com Tim Hayes -Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: 28 September 2004 08:23 To: [EMAIL PROTECTED] Subject: Data fields from one database to another I want to move over 2 - 4 fields from a table in one database , to a table in another. The field names are not an exact match but they function identically (i.e. username, password) Whatever I'm using , web development garbage program ;) won't allow me to connect to 2 different databases so I can't do an update or insertion through that means. Wondering how I script something like that, and probably prefer a way to do it on the fly with each new sign up , or batched on a regular basis aka, every hour , every few hours. Thank you, Stuart -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where and or ...ughhh!
I'm back, trying to figure my way through a somewhat
confusing set of conditons.
Sort of doing a sanity check here.
First explanation:
I've put together a search (the database) form that
has 6 inputs.
Just to clarify, the fields are
1-JobTitle
2-City
3-Start
4-Industry
5-State
6-TaxTerm.
The idea is that a user can choose one , two or all
fields to put criteria into. Regardless, whichever
one they choose , there should be a records return.
More criteria of course adds more refinement.
Right now I only have 4 of the 6 fields rigged for
action- JobTitle, Start, Industry and State.
I half see what's going on, but unclear on how to set
it all up.
So right now, since Title and Start are divided by an
OR, if I chose both, then there is not refinement, I
get records that meet both criteria , not both
criteria combined , savvy ?
Industry is sort of seperate right now, and State can
be combined with industry to refine the results to
match up all industries within whatever particular
states.
I guess ultimately I need a very long series of OR's
and AND's but not sure if there is something neater
and then operator precedence in a series of AND's and
OR's would probably throw me. At least I think so.
ALright, so Im sure this is one of my inance babbles
again, but if anyone can decipher my cry for
assistance, feedback, morsels of wisdom and knowledge,
I'm listening.
Stuart
code:
where JobTitle like '%{s_JobTitle}%'
or
PostStart = DATE_SUB(CurDate(), Interval
({s_PostStart}) day )
$VendorJobs-ds-SQL.= OR (`VendorJobs`.Industry IN
(.$Projects.));
$VendorJobs-ds-SQL.= AND
(`VendorJobs`.LocationState IN (.$Projs.));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where and or ...ughhh!
My brain is warped by web development in general ;)
Thanks for your response. After I sent this , I got
an email from the PHP list, with a good chunk of code
to show how it's done. Yes, scripting is the only way
for this type of query.
Thank you for the response.
Stuart
--- [EMAIL PROTECTED] wrote:
Hi Stuart,
Getting your brains warped by logical statements,
eh?
If I remember correctly AND has precedence over OR.
That means that the
statement A or B and C evaluates to A or (B and
C) which means that
the statement will be true if A is true or if both B
and C are true.
Because of this precedence issue, you need to use
parentheses to specify a
new order of evaluation. This phrase, (A or B) AND
C will be true only
if either A or B is true at the same time that C is
also true. That is a
much different meaning than if the same statement
were written without
parentheses.
Basically OR adds records to your results (less
restrictive), AND takes
them away (more restrictive).
The easiest place to deal with the widely different
request of your users
is in your input processing script. Use your
scripting language to build
an appropriate query based on the options they
provide. No single SQL
statement will handle BOTH every combination of user
input AND process
quickly. You should build custom WHERE clauses
based on your user's
input. This is definitely NOT a one-size-fits-all
situation.
Remember to use parentheses. When in doubt, spell it
out. Don't make the
query engine read your mind.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Stuart Felenstein [EMAIL PROTECTED] wrote on
09/22/2004 09:46:46 AM:
I'm back, trying to figure my way through a
somewhat
confusing set of conditons.
Sort of doing a sanity check here.
First explanation:
I've put together a search (the database) form
that
has 6 inputs.
Just to clarify, the fields are
1-JobTitle
2-City
3-Start
4-Industry
5-State
6-TaxTerm.
The idea is that a user can choose one , two or
all
fields to put criteria into. Regardless,
whichever
one they choose , there should be a records
return.
More criteria of course adds more refinement.
Right now I only have 4 of the 6 fields rigged for
action- JobTitle, Start, Industry and State.
I half see what's going on, but unclear on how to
set
it all up.
So right now, since Title and Start are divided by
an
OR, if I chose both, then there is not refinement,
I
get records that meet both criteria , not both
criteria combined , savvy ?
Industry is sort of seperate right now, and State
can
be combined with industry to refine the results to
match up all industries within whatever particular
states.
I guess ultimately I need a very long series of
OR's
and AND's but not sure if there is something
neater
and then operator precedence in a series of AND's
and
OR's would probably throw me. At least I think
so.
ALright, so Im sure this is one of my inance
babbles
again, but if anyone can decipher my cry for
assistance, feedback, morsels of wisdom and
knowledge,
I'm listening.
Stuart
code:
where JobTitle like '%{s_JobTitle}%'
or
PostStart = DATE_SUB(CurDate(), Interval
({s_PostStart}) day )
$VendorJobs-ds-SQL.= OR (`VendorJobs`.Industry
IN
(.$Projects.));
$VendorJobs-ds-SQL.= AND
(`VendorJobs`.LocationState IN (.$Projs.));
--
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: Where and or ...ughhh!
The returned values should be exact. As far as how much data, I'm assuming your talking about amount of records ? Potentially eventually maybe quite a bit. Currently mysql statement is comprised of select and from with all the proper joins. But Im confused, when you say below actual SQL call here, is the referring to the SQL statement that would look at all the imput values and process the data ? like a series of and's or or's ? Stuart --- gerald_clark [EMAIL PROTECTED] wrote: Depending on how exact the returned values need to be, and how much data you have to sift through, you could do. ( perl example ) $job=$dbh-quote($job.'%'); $city=$dbh-quote($city.'%'); $start=$dbh-quote($start.'%); . . . $select = SELECT * from myfile where jobtitle like $job and city like $city and start like $start and industry like $industry and state like $state and taxterm like $taxterm; . . do the actual SQL call here. Stuart Felenstein wrote: I'm back, trying to figure my way through a somewhat confusing set of conditons. Sort of doing a sanity check here. First explanation: I've put together a search (the database) form that has 6 inputs. Just to clarify, the fields are 1-JobTitle 2-City 3-Start 4-Industry 5-State 6-TaxTerm. The idea is that a user can choose one , two or all fields to put criteria into. Regardless, whichever one they choose , there should be a records return. More criteria of course adds more refinement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where and or ...ughhh!
Right, I thought the rules were mentioned in the first post. Apoplogies. The user should be able to enter one value or all 6 and with each additional value added from none, the results should be more refined. i.e. Alabama chosen only - all jobs in Alabama come back Alabama and Financing - all financing jobs in Alabama come back Alabama , Financing and 5 days old, all financing jobs in Alabama within the last 5 days come back Alabam, Financing, 5 days old, and treasurer, all financing jobs in Alabama, no more then 5 days old with the job title of treasurer. You raise a good point , that I thought about and then cast to the side temporarily. No I don't want all data coming back, so probably with no values entered at all then no records come back. From my understanding now , to do something like this the query needs to be created on the fly withink the script. So if no values exist, no array exists, on to the next field with an OR, if values exists, take the values input into an array , onto next field with an AND. I think that is it , now if I only knew how to code ;) Stuart --- [EMAIL PROTECTED] wrote: Stuart. If your queries must match _all_ of your input variables, you are in the situation where you only need ANDs (no ORs needed). Just create one term in your WHERE statement for each field they filled in and make sure that there is an AND between each of them in the right places. This should be a piece of simple string building along the same lines you are already doing. I think that the reason that nobody, including myself, can tell you how to write your script is because you never told us _your_ rules on how each field is supposed to be handled. Build your WHERE clause so that it meets _your_ requirements and it should work correctly. Of course, no input from the user means you don't need a WHERE clause at all. You can regurgitate your entire database. This may be something you want to avoid by requiring at least 1 or 2 input values. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 09/22/2004 03:11:16 PM: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
Not sure exactly what you mean by a SQL injection attack. I'm thinking a string could be input as opposed to an integer ? The form itself constricts user to a set of choices. Stuart --- Harald Fuchs [EMAIL PROTECTED] wrote: I think this is bad advice, even for a novice like Stuart, because it is susceptible to SQL injection attacks. I don't know if PHP has prepared statements like Perl DBI; if not, $daterange should either be quoted or checked in PHP if it's really a number. -- 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: HelpPlease: Conditional loop confusion
It's all good information. I need to be thinking security as well as generating proper efficient code. All is appreciated. Stuart Harald Fuchs [EMAIL PROTECTED] wrote: In article , [EMAIL PROTECTED] writes: I agree and I am sorry I forgot a very basic security practice. You must always check any input from a user. Make sure that the user gives you a valid number and reject the request if it is anything out of your acceptable range or datatype. Not necessarily - most of these problems can be solved by the DBMS itself as long as you give it a chance to do so. In general DBMSs are smart enough to figure out that WHERE id = '123' really should be a numeric comparison. This means that you just need to transform your parameters to valid strings in order to be on the safe side. Simply surrounding them by single quotes is _not_ enough - you must also properly escape embedded single quotes. Most APIs are able to do that for you (e.g. Perl: $dbh-quote()). Otherwise a bad user could compromise your database or worse create havoc on your server. Yes. hf0722x, what I was trying to help correct was a basic misunderstanding of how to create dynamic SQL statements with PHP. Stuart is struggling to understand 3 environments at once (MySQL, PHP, and Dreamweaver) and I wasn't even thinking about input validation. I know that my remark (as opposed to yours) was not helpful regarding Stuart's actual problems, but precisely because he is a newbie he should not get accustomed to dangerous practices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
Shawn, Just wanted to let you know your help was appreciated. I'm close to getting it straight. Away from my console for the day so I won't know till later. However, while I did figure out the correct variable and query name, there is about 20 other lines of code that needed adjusting to make it work :). The generated code can be daunting, especially when it's not commented or documented. Lots of activity! Anyway, I'm now reading up on PHP, and while I'll continue using code generators where it makes sense, at least I'll have a better approach when necessary for manual editing. In regards to the other thread about checking the input, the code seems to be written well with lots of checks and guards for added security. Stuart [EMAIL PROTECTED] wrote: Don't do an @usrDays, just stick the number in there so that the statement you create looks exactly like the one you tested with. If you get a number from a user from a form, just put that value into the string. For instance if the user enters 43 into a field on the first page, put 43 into the query. Let's imagine that you get the value 43 into a variable (off of the request) called $daterange... and let's imagine that you build your query into a variable called $query I think this is how you would build the correct query (my PHP is rusty): $query = SELECT DateEntry from Entry_table WHERE DateEntry = DATE_SUB(CURDATE(), INTERVAL . $daterange. day); See? Pretend you are a typist and BUILD the string as you want it executed. Then do it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein wrote on 09/13/2004 03:35:35 PM: Shawn - You answered the right question. Since a SQL statement is closer to my grasp right now , today, trying that approach. I think this is something close to correct, though I'm stuck on what to assign to @usrDays:= SELECT DateEntry from Entry_Table, WHERE DateEntry = DATE_SUB(CurDate(), Interval @usrDays:= day ) I'll continue to bang away , eventually I should get it. Stuart --- [EMAIL PROTECTED] wrote: Stuart, I feel you pains. I think I can speak for most of us to say we've been there, too First, You want to try to keep your column values on the LEFT side of any inequality. MySQL just goes faster that way. SELECT WHERE EntryDate = DATESUB(CurDate(), Interval 2 day ) About using a variable for your date value, you have two options. First, you could just modify your PHP code to stick a number into the correct place of your SQL statement: $querystring = SELECT WHERE EntryDate = DATESUB(CurDate(), Interval . $daysdiff . day ) Or you can create SQL statement that sets a MySQL variable that contains the # of days you want to subtract then use that variable in your WHERE clause. Either way you are combining the value you get from the user-entry field and using it to create a valid SQL statement. Personally, I would do the first. (my logic: I don't need the variable except to use it in the very next statement and if I have to merge values and text once, anyway, why not just put the correct value into my original query) Please let me know if I answered the wrong question, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein wrote on 09/13/2004 11:54:02 AM: Shawn, partially discourage but mostly confused about what I really need to do. Bear in mind for a moment, that I'm working in a somewhat untradional manner. Meaning a) very new to database and SQL b) (and i realize this list is not for programming languages) using a RAD that allows me to point, click and insert , where it's magically transformed into code. So back to what I'm trying to accomplish. I want a way for the user to be allowed to choose from what point in the timeline records will be returned in a search. i.e. from 30 days or less, 15 days or less, 2 days or less, etc. Now, if I run this query against the entry_table.entry_date_column select EntryDate from EntryTable where Date_Sub(Curdate(), interval 2 day) = EntryDate life is good. I get the records returned just the way I want. They key though is the 2 (after interval) as I want that number to be a variable. That is why I created the table that has values of 1, 2 , 15, 30, etc). I thought if I could use those values, in either a case statement (probably not with 4.0.20) or an if statement , well magic would happen ;) I hope I explained it better this time. Apologies for the confusion. Stuart --- [EMAIL PROTECTED] wrote: Were you able to solve you problem another way or just discouraged by my response? I wasn't trying to make you feel bad, you just covered so much ground in your original post that I really
HelpPlease: Conditional loop confusion
I read through the parts of the manual that applied but I might be missing something here. I have a table where I'm trying to query the date with a conditional statement. As you'll see , if 3 was the condition then the statement should result in a list of any entries made = current date. It just is not working. Entry_Table: EntriesID (int) EntryDte (date) .(irrelevant fields). Second table (created just for query since this is going into a web app) CountBack_Table CountBackID (int) CountBack (int) Then the records in the table are CountBackID = 30 , CountBack = 30 CountBackID = 21, CountBack = 21 CountBackID = 7, CountBack = 7 .(few more)... I thought my statement should be like this: Select Entry_Table.EntryDte,CountBack_Table.CountBackID From Entry_Table, CountBack_Table if CountBackID = 1 then Date_Sub(Curdate(), interval 1 day) = EntryDte elseif CountBackID = 3 then Date_Sub(Curdate(), interval 3 day) = EntryDte ...etc. I'm not sure if the if CountBackID is wrong. Can I not use the value in the record, or perhaps I'd need to define a user variable? Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
Ok, never mind . I guess a conditional loop is not needed. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
Shawn, partially discourage but mostly confused about what I really need to do. Bear in mind for a moment, that I'm working in a somewhat untradional manner. Meaning a) very new to database and SQL b) (and i realize this list is not for programming languages) using a RAD that allows me to point, click and insert , where it's magically transformed into code. So back to what I'm trying to accomplish. I want a way for the user to be allowed to choose from what point in the timeline records will be returned in a search. i.e. from 30 days or less, 15 days or less, 2 days or less, etc. Now, if I run this query against the entry_table.entry_date_column select EntryDate from EntryTable where Date_Sub(Curdate(), interval 2 day) = EntryDate life is good. I get the records returned just the way I want. They key though is the 2 (after interval) as I want that number to be a variable. That is why I created the table that has values of 1, 2 , 15, 30, etc). I thought if I could use those values, in either a case statement (probably not with 4.0.20) or an if statement , well magic would happen ;) I hope I explained it better this time. Apologies for the confusion. Stuart --- [EMAIL PROTECTED] wrote: Were you able to solve you problem another way or just discouraged by my response? I wasn't trying to make you feel bad, you just covered so much ground in your original post that I really couldn't understand your issue. Please, post again but with a little more background. We all want to help but we aren't there looking over your shoulder so we can't see everything you see. We just need a little help to visualize your problem, OK? Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 09/13/2004 11:09:59 AM: Ok, never mind . I guess a conditional loop is not needed. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
Shawn - You answered the right question. Since a SQL statement is closer to my grasp right now , today, trying that approach. I think this is something close to correct, though I'm stuck on what to assign to @usrDays:= SELECT DateEntry from Entry_Table, WHERE DateEntry = DATE_SUB(CurDate(), Interval @usrDays:= ? day ) I'll continue to bang away , eventually I should get it. Stuart --- [EMAIL PROTECTED] wrote: Stuart, I feel you pains. I think I can speak for most of us to say we've been there, too First, You want to try to keep your column values on the LEFT side of any inequality. MySQL just goes faster that way. SELECT WHERE EntryDate = DATESUB(CurDate(), Interval 2 day ) About using a variable for your date value, you have two options. First, you could just modify your PHP code to stick a number into the correct place of your SQL statement: $querystring = SELECT WHERE EntryDate = DATESUB(CurDate(), Interval . $daysdiff . day ) Or you can create SQL statement that sets a MySQL variable that contains the # of days you want to subtract then use that variable in your WHERE clause. Either way you are combining the value you get from the user-entry field and using it to create a valid SQL statement. Personally, I would do the first. (my logic: I don't need the variable except to use it in the very next statement and if I have to merge values and text once, anyway, why not just put the correct value into my original query) Please let me know if I answered the wrong question, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 09/13/2004 11:54:02 AM: Shawn, partially discourage but mostly confused about what I really need to do. Bear in mind for a moment, that I'm working in a somewhat untradional manner. Meaning a) very new to database and SQL b) (and i realize this list is not for programming languages) using a RAD that allows me to point, click and insert , where it's magically transformed into code. So back to what I'm trying to accomplish. I want a way for the user to be allowed to choose from what point in the timeline records will be returned in a search. i.e. from 30 days or less, 15 days or less, 2 days or less, etc. Now, if I run this query against the entry_table.entry_date_column select EntryDate from EntryTable where Date_Sub(Curdate(), interval 2 day) = EntryDate life is good. I get the records returned just the way I want. They key though is the 2 (after interval) as I want that number to be a variable. That is why I created the table that has values of 1, 2 , 15, 30, etc). I thought if I could use those values, in either a case statement (probably not with 4.0.20) or an if statement , well magic would happen ;) I hope I explained it better this time. Apologies for the confusion. Stuart --- [EMAIL PROTECTED] wrote: Were you able to solve you problem another way or just discouraged by my response? I wasn't trying to make you feel bad, you just covered so much ground in your original post that I really couldn't understand your issue. Please, post again but with a little more background. We all want to help but we aren't there looking over your shoulder so we can't see everything you see. We just need a little help to visualize your problem, OK? Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 09/13/2004 11:09:59 AM: Ok, never mind . I guess a conditional loop is not needed. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dates confusion for real system
I'm having a hard time getting my arms around how a system's date process should work. Interested in knowing how a real, online system that delivers services or products to customers might work. Right now I'll just be operating in North America. As an example - a user does online banking. A bill is due on the 24th of the month. Payment received after the 24th is marked late. The company the bill is owed too, their system is on the east coast, New York. The payee is out in Los Angeles. The payee is making their payment online at 11:45pm, yet in New York it's now 2:45 on the 25th. Ok that is the scenario. Now, I'm thinking there are a few possibilities for work arounds. One would be that the date input is done via some javascript, so the time marked on the payment is relative to the payee's time zone. Second, perhaps there is some way to correlate one's location to the time the system stamps for payment. Perhaps, with this method, some complex sql statement is doing date / time calcs to properly stamp the entry. Or, most companies use one time zone to base their business around. I'm sure there are varieties of methods used. Interested in hearing about some of them at least. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anyone familiar with dbQwiksite Pro
Wondering if there is anyone on the list who knows this product . Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's Faster? MySQL Queries or PHP Loops?
I've been meaning to follow up on this post. Can either Peter or someone expand and provide an example of get all information in a single query... Thank you , Stuart --- Peter Lovatt [EMAIL PROTECTED] wrote: Hi What I do is to try and get all information in a single query and then use php from there on. I go as far as building arrays from result sets and manipulating the data using php. Can't guarantee this is best practice but I have built big sites with big visitor numbers this way and they run OK :) HTH Peter -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 08 September 2004 19:01 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: What's Faster? MySQL Queries or PHP Loops? I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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]
where oh where, help!
I have this query (below) that on it's own , directly into the database seems to return all the records just fine. Apparently though feeding it into a php loop, the lack of where is causing great distress(at least to the author) I'm wondering where to put it though. Before each join ? And then what does the from imply. Hope I make sense here, delirium is setting in. :) Select VendorJobs.JobID, VendorJobs.PostStart, VendorJobs.JobTitle, VendorJobs.AreaCode, VendorJobs.PayRate, VendorJobs.Contact, VendorJobs.Conmail, VendorSignUp.CompanyName, StaIndTypes.CareerCategories, StaUSCities.City, USStates.States, staTaxTerm.TaxTerm, staTravelReq.TravelReq From (VendorJobs VendorJobs INNER JOIN VendorSignUp VendorSignUp ON (VendorJobs.VendorID = VendorSignUp.VendorID)) INNER JOIN StaIndTypes StaIndTypes ON (VendorJobs.Industry = StaIndTypes.CareerIDs)) LEFT OUTER JOIN StaUSCities StaUSCities ON (VendorJobs.LocationCity = StaUSCities.CityID)) INNER JOIN USStates USStates ON (VendorJobs.LocationState = USStates.StateID)) INNER JOIN staTaxTerm staTaxTerm ON (VendorJobs.TaxTerm = staTaxTerm.TaxTermID)) INNER JOIN staTravelReq staTravelReq ON (VendorJobs.TravelReq = staTravelReq.TravelReqID) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's Faster? MySQL Queries or PHP Loops?
I'm confused about this response and am facing a similar situation. First, regarding the subject, what is the difference between a PHP or whatever loop and a SQL query. All the app code is doing is collecting the request and handing it back to the database. The DBMS still has to retrieve the data. My only guess is that the DBMS has to work a bit harder, as PHP would present the data in a cleaner / neater / parsed form back to the DBMS. Any of this make sense ? Stuart --- Brent Baisley [EMAIL PROTECTED] wrote: I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: What's Faster? MySQL Queries or PHP Loops?
I never thought of the return only the querying part. My predicament is I have a search form that queries a table with about 7 joins. It returns it via a Dreamweaver recordset aka SQL query. So based on what you said below , regarding the number of users, this is a bad way to go. Stuart --- Brent Baisley [EMAIL PROTECTED] wrote: The end result will be the same, it's just a matter of the structure the data will have when handed to PHP for processing to display. It can be retrieved bit by bit and broken up into multiple lists or joined and summarized by MySQL into one list. One list will make the PHP loop simpler, smaller and faster. On Sep 8, 2004, at 2:12 PM, Stuart Felenstein wrote: I'm confused about this response and am facing a similar situation. First, regarding the subject, what is the difference between a PHP or whatever loop and a SQL query. All the app code is doing is collecting the request and handing it back to the database. The DBMS still has to retrieve the data. My only guess is that the DBMS has to work a bit harder, as PHP would present the data in a cleaner / neater / parsed form back to the DBMS. Any of this make sense ? Stuart --- Brent Baisley [EMAIL PROTECTED] wrote: I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select field names?
Sorry, I'm not jumping in becasue I have an answer, sorry Jeremy. I posted a few days ago a question and wonder if this thread , at least Shawn's response has any relevance to my question. That is! ; Is there really any difference between using PHP to parse results back to the program or can a SQL statement do the same job. I'll re tell my tale of woe and how I overcame it using , if's , ands and ors. If need be. Stuart --- [EMAIL PROTECTED] wrote: You're mixing apples and oranges. PHP has the metadata available to it in the form of a column name/value pairs. You scan the results of a query in column order and get the name of the column (from the recordset, not from the data) that has the data you want, right? A native SQL query, as Paul said, is NOT aware of the column names except as you declare them for use. However, you _can_ write a query that will return a value based on a series of choices. It's much like moving your PHP column scanning loop into your SQL statement. In this case you are nesting a set of IF statements: set @targetValue = 'pid to find'; SELECT user_ID, IF([EMAIL PROTECTED], 'pid0', if(pid1 = @targetValue, 'pid1', if ( repeat for remaining columns ...))) FROM tablename WHERE ... I didn't type the full statement but I think you can see the pattern. This was just my first idea. I am sure there are others on the list that have done something like this before and can offer their solutions, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeremy McEntire [EMAIL PROTECTED] wrote on 09/07/2004 01:38:18 PM: Clarification. I'm using modular arithmetic on a table of recently viewed items. My fields are: user_id, pid0, pid1, pid2, pid3, pid4, inc user_id is the user's unique identification. pid* is the product's unique identification. inc is a number, modulo 5, corresponding to the last pid column updated for this user. So, when the user visits a page, I want to verify that they don't currently have this product in their recently viewed list. To do so, I'd like to know if the product id is in any of pid0, pid1, pid2, pid3, or pid4. To accomplish this, I could use a simple OR statement. But, the information I really want is in which column that product id appears. Using PHP, I can simply grab the key from the array returned by the query. I was hoping MySQL offered this functionality; evidently, it does not. Sincerely, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Question
I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful. There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other static tables. VendorJobs is the main table, here is the select and from's: -- SELECT `VendorJobs`.`JobID`, `VendorJobs`.`Entered`, `VendorSignUp`.`CompanyName`, `StaIndTypes`.`CareerCategories`, `StaUSCities`.`City`, `USStates`.`States`, `VendorJobs`.`AreaCode`, `staTaxTerm`.`TaxTerm`, `VendorJobs`.`PayRate`, `staTravelReq`.`TravelReq`, `VendorJobDetails`.`JobTitle`, `VendorJobDetails`.`Details`, `VendorJobs`.`PostStart` FROM `VendorJobs` INNER JOIN `VendorSignUp` ON (`VendorJobs`.`VendorID` = `VendorSignUp`.`VendorID`) INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry` = `StaIndTypes`.`CareerIDs`) LEFT OUTER JOIN `StaUSCities` ON (`VendorJobs`.`LocationCity` = `StaUSCities`.`CityID`) LEFT OUTER JOIN `USStates` ON (`VendorJobs`.`LocationState` = `USStates`.`StateID`) LEFT OUTER JOIN `staTaxTerm` ON (`VendorJobs`.`TaxTerm` = `staTaxTerm`.`TaxTermID`) INNER JOIN `staTravelReq` ON (`VendorJobs`.`TravelReq` = `staTravelReq`.`TravelReqID`) INNER JOIN `VendorJobDetails` ON (`VendorJobs`.`JobID` = `VendorJobDetails`.`JobID`) -- The where condition is going to have multiple AND's (I've considered UNION but don't think they are appropriate here) First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California. So now I want to add the where's but doing: where `VendorJobs`.`CareerCategories` = Finance is returning an error. I think because VendorJobs only knows Finance by Fin. I think what it should be : StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it. What's confusing me is the join, and how the join maintains the integrity of the record. If that makes sense, cause what I just said, doesn't to me. Yet, that's the only way I could say it for now. Alrighty, flame away Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
I think I'm on the right track but still in question After all the joins I added a and LocationState = x. I'm not totally sure, because I want to search for records based on (for now)3 conditions (state, city, industry). Two things I should mention , the somewhat strange notation is becaue I'm using one of dem fancy visual query editors. Secondly, I'm using this query in a web page that receives the codes via url / variables. So, and I hope I don't get slammed for talking web dev here. But the problem is someone may choose one field and not another , so I want a return on whether they chose 1 or all 3 variables. Hope that makes sense. i.e. They choose the state, but leave the city and industy blank. The way it's set up now with and ..., and ..., and it will only return a record if I put in all 3. If I do an OR, then a second on any additonal OR's would get bypassed if I understand correctly. Sorry, I might just be thinking outloud. Please do not boot. Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful. There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other static tables. VendorJobs is the main table, here is the select and from's: -- SELECT `VendorJobs`.`JobID`, `VendorJobs`.`Entered`, `VendorSignUp`.`CompanyName`, `StaIndTypes`.`CareerCategories`, `StaUSCities`.`City`, `USStates`.`States`, `VendorJobs`.`AreaCode`, `staTaxTerm`.`TaxTerm`, `VendorJobs`.`PayRate`, `staTravelReq`.`TravelReq`, `VendorJobDetails`.`JobTitle`, `VendorJobDetails`.`Details`, `VendorJobs`.`PostStart` FROM `VendorJobs` INNER JOIN `VendorSignUp` ON (`VendorJobs`.`VendorID` = `VendorSignUp`.`VendorID`) INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry` = `StaIndTypes`.`CareerIDs`) LEFT OUTER JOIN `StaUSCities` ON (`VendorJobs`.`LocationCity` = `StaUSCities`.`CityID`) LEFT OUTER JOIN `USStates` ON (`VendorJobs`.`LocationState` = `USStates`.`StateID`) LEFT OUTER JOIN `staTaxTerm` ON (`VendorJobs`.`TaxTerm` = `staTaxTerm`.`TaxTermID`) INNER JOIN `staTravelReq` ON (`VendorJobs`.`TravelReq` = `staTravelReq`.`TravelReqID`) INNER JOIN `VendorJobDetails` ON (`VendorJobs`.`JobID` = `VendorJobDetails`.`JobID`) -- The where condition is going to have multiple AND's (I've considered UNION but don't think they are appropriate here) First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California. So now I want to add the where's but doing: where `VendorJobs`.`CareerCategories` = Finance is returning an error. I think because VendorJobs only knows Finance by Fin. I think what it should be : StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it. What's confusing me is the join, and how the join maintains the integrity of the record. If that makes sense, cause what I just said, doesn't to me. Yet, that's the only way I could say it for now. Alrighty, flame away Stuart -- 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: Query Question
Well I feel like maybe I wasted some bandwidth here. I think what I'm looking for is a square peg in a round hole. That won't work. More to the point :) , I do not having a problem with the AND / OR / IN / NOT / etc. What I think I was attempting was to come up with a SQL statement that will work with an unknown factor. I'm now thinking that this isn't the path to take. Here is where I'm at, and as this goes into web dev, I am trying to figure out if I have any choices strictly using SQL. I have 3 choices (3 seperate fields to query) a user can submit, but none are strictly required. Chances are only 1 will be used. If I do a where x = 1 or y = 2 or z = 3 then regardless of the y or z, x is coming back. It's not even looking at y or z. All AND requires all three conditions are met. Lastly I was interested in NOT, since by default there is an assigned value to the non used form field. Yet, all of my where conditions are = resset1... which is the variable that gets passed over. Not sure how to say where LocationState NOT XXX and leave the resset in place. Sorry if this is all confusing. I am taking my time and wouldn't think of just throwing something up unless it works in the 999,999,999 ways it should. Thank you, Stuart --- Michael Stassen [EMAIL PROTECTED] wrote: Stuart Felenstein wrote: I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful. There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other static tables. VendorJobs is the main table, here is the select and from's: query reformatted so I could read it -- SELECT VJ.JobID, VJ.Entered, VSU.CompanyName, StaIndTypes.CareerCategories, StaUSCities.City, USStates.States, VJ.AreaCode, staTaxTerm.TaxTerm, VJ.PayRate, staTravelReq.TravelReq, VendorJobDetails.JobTitle, VendorJobDetails.Details, VJ.PostStart FROM VendorJobs VJ INNER JOIN VendorSignUp VSU ON VJ.VendorID = VSU.VendorID INNER JOIN StaIndTypes ON VJ.Industry = StaIndTypes.CareerIDs LEFT JOIN StaUSCities ON VJ.LocationCity = StaUSCities.CityID LEFT JOIN USStates ON VJ.LocationState = USStates.StateID LEFT JOIN staTaxTerm ON VJ.TaxTerm = staTaxTerm.TaxTermID INNER JOIN staTravelReq ON VJ.TravelReq = staTravelReq.TravelReqID INNER JOIN VendorJobDetails ON VJ.JobID = VendorJobDetails.JobID -- The where condition is going to have multiple ANDs (I've considered UNION but don't think they are appropriate here) AND and UNION are opposites. ANDs narrow your results, because only rows which match all AND conditions are selected. UNION, like OR, increases your result set, because rows only have to match any one of the conditions. That is, SELECT * FROM atable WHERE a = 1 OR b = 2; is equivalent to SELECT * FROM atable WHERE a = 1 UNION SELECT * FROM atable WHERE b = 2; See the manual for details http://dev.mysql.com/doc/mysql/en/UNION.html. First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California. So now I want to add the wheres but doing: where `VendorJobs`.`CareerCategories` = Finance is returning an error. I think because VendorJobs only knows Finance by Fin. `VendorJobs`.`CareerCategories` = Finance gives you an *error* (as opposed to no match), because you have no column named Finance. You (mysql) can tell Finance is the name of a column because it has no quotes. Of course, you meant to compare VendorJobs.CareerCategories to the constant string 'Finance', so you should use WHERE `VendorJobs`.`CareerCategories` = 'Finance' ... But, your condition should be WHERE column_name = 'a value in that column' ... So, if the string 'Fin' is what is actually stored in the CareerCategories column, you need WHERE `VendorJobs`.`CareerCategories` = 'Fin' ... I think what it should be : StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it. OK, now I'm confused. Which column of which table contains the string 'Finance' or 'Fin'? Oh, is 'Fin' an ID for the row in StaIndTypes where the full string 'Finance' resides? If that's the case, since you are joining to StaIndTypes, either match (VendorJobs.CareerCategories` = 'Fin' or StaIndTypes = 'Finance') should work. What's confusing me is the join, and how the join
Graphics and blobs
This maybe OT but perhaps somone has a clue here. I am storing gifs in a Blob, well tinyblob field. I was under the assumption they could just be pulled out and shown on a web page similar to running any other query. Apparently not, someone have a hint ? Thanks Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Graphics and blobs
My apologies for rushing in with a question that gave little in the way of details. I've straightend out the mess ;) Just in case it's of any relevance, I've opted to store the gifs in a file directory and use a varchar field to make a link to the file / directory. All is good. Sorry again! Stuart --- Paul DuBois [EMAIL PROTECTED] wrote: At 9:56 -0700 9/4/04, Stuart Felenstein wrote: This maybe OT but perhaps somone has a clue here. I am storing gifs in a Blob, well tinyblob field. I was under the assumption they could just be pulled out and shown on a web page similar to running any other query. Apparently not, someone have a hint ? You're asking us to guess what it was that you tried and also what particular symptoms of failure you were seeing. My guess: Your blob values are longer than 255 bytes, the maximum size of a TINYBLOB column. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.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]
Innodb space - Different Question
Since the recent thread with Innodb table space, I have had a concern for a while. First, I use Navicat to administer mysql. This includes table creation. I don't notice this using a myisam table but when I set up an innodb I find this item: InnoDB free: 10240 kB Does this mean the actual space provided for the records it can hold ? I was concerned, then someone (not very knowledgable) told me not to be concerned. I'm concerned. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Consideration of query impact / bandwidth
In my web design I have a side bar to provide quick info and links to account information. Examples would be account basics: Welome : Name, Company, Last Visit , .. One idea that brings me to this question: In the side bar I list out how many resumes member has on file, so: Title1 - Then I can also add an update link to the record Title2 Title3 As well a return on count() query, number_allowed e.g. Add (1) At the same time I also have a main account summary page which contains some of this plus more detailed information then I'd consider putting inthe side bar. So, I'm wondering how concerned I should be about these little queries that I'm putting in the side bar. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Before I go searching (shameless)
I'm tring to extract all records belonging to one ID in a table. BUT! I want to do it in piece meal. Meaning a record at a time. I am assuming I need some kind of count mechanism. Example: I want to see the first 3 purchases this member made. Instead of grabbing them all in one shot, I want to break those 3 out in 3 seperate results set Results Set1: Purchase 1 Results Set2: Purchase 2 Results Set3: Purchase 3 This is probably easy. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Before I go searching (shameless)
Wait!! Don't answer...it's that LIMIT thing. :) Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm tring to extract all records belonging to one ID in a table. BUT! I want to do it in piece meal. Meaning a record at a time. I am assuming I need some kind of count mechanism. Example: I want to see the first 3 purchases this member made. Instead of grabbing them all in one shot, I want to break those 3 out in 3 seperate results set Results Set1: Purchase 1 Results Set2: Purchase 2 Results Set3: Purchase 3 This is probably easy. Thank you, Stuart -- 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: Before I go searching (shameless)
Well I try to avoid the manual , but refer to some books that I've collected. Essentially the same, but more palatable for my tastes. Not that I wouldnl't check the manual. What I needed was order by Desc limit (x, x) . In my case 3 queries, with (0,1)(1,1)(2,1) . The first 3 records , 1 at a time. Stuart --- V. M. Brasseur [EMAIL PROTECTED] wrote: Here is the manual. I suggest you try a search for LIMIT in the context of a SELECT. http://dev.mysql.com/doc/mysql/en/index.html However what it sounds like you really ought to do is give a walk through the tutorial. http://dev.mysql.com/doc/mysql/en/Tutorial.html If, after exploring these two links, you still have questions feel free to post them and people will be glad to be of assistance. RTFM, --V Stuart Felenstein wrote: I'm tring to extract all records belonging to one ID in a table. BUT! I want to do it in piece meal. Meaning a record at a time. I am assuming I need some kind of count mechanism. Example: I want to see the first 3 purchases this member made. Instead of grabbing them all in one shot, I want to break those 3 out in 3 seperate results set Results Set1: Purchase 1 Results Set2: Purchase 2 Results Set3: Purchase 3 This is probably easy. Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: After I went searching
The query works fine on it's own, but generates a SQL error in my page: SELECT * FROM MemberRez WHERE LurkID=colname (I've tried it without this line) ORDER BY andnext asc limit 1,1 andnext is the pk auto inc. Page error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT -1' at line 1 Maybe formatting ? Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: Wait!! Don't answer...it's that LIMIT thing. :) Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm tring to extract all records belonging to one ID in a table. BUT! I want to do it in piece meal. Meaning a record at a time. I am assuming I need some kind of count mechanism. Example: I want to see the first 3 purchases this member made. Instead of grabbing them all in one shot, I want to break those 3 out in 3 seperate results set Results Set1: Purchase 1 Results Set2: Purchase 2 Results Set3: Purchase 3 This is probably easy. Thank you, Stuart -- 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]
Re: After I went searching
Yep, understand about the 0 and checked the code no -1's. All is weird, but here is a situation that in the back of my mind I keep thinking maybe I need a many to many. The problem is I again can't quite conceptualize it. If I'm making a rehash again , I really apologize. Here is the situation: I have a table that hold resumes , each member can store 3 resumes. the table: ResTable(myisam) AndNextID - (auto inc) just really a counter and PK MemberID - Resume Title - Resume - So if they hit their three a trigger which does a count on their records by their ID returns an error message. The problem I'm running into now is on updating. I'm really trying not to confuse app logic and mechanics with the database. So I'll cut to the quick. ResTable1 ...(same fields as above) ResTable2 (yadda yadda) Member_ResTable Member (well actually not sure what would be in here) Shawn, if your out there - I'm not ignoring your advice to stave off the coding for now , concentrate on the db design. I just haven't come to grips with it yet! LOL Stuart Would there be a benefit in a: --- Rhino [EMAIL PROTECTED] wrote: Formatting shouldn't be an issue if you entered the query exactly as you said you did. However, I'm a little dubious that you did; the error message seems to be saying that you have a negative sign in front of the first 1 in the Limit clause. Check your typing and try the query again. Also, remember that the row numbers used by the Limit clause start with 0, not with 1. In other words, you may want to try Limit 0,1, not Limit 1,1. Rhino - Original Message - From: Stuart Felenstein [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 02, 2004 12:18 PM Subject: Re: After I went searching The query works fine on it's own, but generates a SQL error in my page: SELECT * FROM MemberRez WHERE LurkID=colname (I've tried it without this line) ORDER BY andnext asc limit 1,1 andnext is the pk auto inc. Page error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT -1' at line 1 Maybe formatting ? Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: Wait!! Don't answer...it's that LIMIT thing. :) Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm tring to extract all records belonging to one ID in a table. BUT! I want to do it in piece meal. Meaning a record at a time. I am assuming I need some kind of count mechanism. Example: I want to see the first 3 purchases this member made. Instead of grabbing them all in one shot, I want to break those 3 out in 3 seperate results set Results Set1: Purchase 1 Results Set2: Purchase 2 Results Set3: Purchase 3 This is probably easy. Thank you, Stuart -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: After I went searching
K ...so I have it set up correctly. After I posted, then went back and scoured through your previous responses. Realized, it's not a many to many. I honestly think if I was only doing the database, life would be a whole lot easier :). Sorry , that's humour. Thank you Stuart --- [EMAIL PROTECTED] wrote: lurk, lurk -- yep, I'm hanging around ;-) You don't have a many-to-many relationship between resumes and members. It's a one-to-many (each member can have multiple resumes but each resume belongs specifically to only 1 member) That's done with a single table like this (not 3 different resume tables): CREATE TABLE resume ( ID int auto_increment not null, member_ID int, ...more fields to hold the resume content and metadata ... ) The member_ID can appear multiple times in the resume table but each resume is limited to only 1 member_id (one-to-many). Before you even allow a user to attempt an insert a new resume into your system, check to see if they have already met their limit: SELECT member_ID, count(1) as rescount FROM resume WHERE member_ID = ### -- put a real number/string there GROUP BY member_ID; And compare the results of the 'rescount' column to your business rule (some day you may allow 6 resumes, who knows?). This comparison happens in your application code, not the database, as you need to decide whether to give the user the screen to enter another resume or to send the user a screen that says So sorry but you already have your limit of resumes on file. Shawn Green Database Administrator Unimin Corporation - Spruce Pine --8 prior responses mangled for space ---8-- Stuart Felenstein [EMAIL PROTECTED] wrote on 09/02/2004 01:05:31 PM: I have a table that hold resumes , each member can store 3 resumes. --8-- ResTable1 ...(same fields as above) ResTable2 (yadda yadda) Member_ResTable Member (well actually not sure what would be in here) Shawn, if your out there - I'm not ignoring your advice to stave off the coding for now , concentrate on the db design. I just haven't come to grips with it yet! LOL Stuart --8-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
