Revoke User's Global Privileges?
How does one go about revoking a user's GLOBAL privileges with a REVOKE statement? TIA, Tore
Another permission question...
MySQL 3.23.36 (yeah, I know it's getting old...): What permission do I need to run the following query: REVOKE ALL ON mydb.* FROM 'sdaf'@'%'; I get an error 1044: Access denied for user: 'Admin@IP address' to database 'mydb' My user has GRANT permission on mydb (database) and GRANT permission on all tables except two (with only SELECT permission). I tried removing all table based permissions, but that didn't help. I assigned GRANT permissions to all tables in the database, but that didn't help either. Assigning SELECT, UPDATE permissions on the mysql database didn't help either? TIA, Tore.
HELP - Permission denied with ODBC 3.51 - not with tools...
Why do I get a permission denied when I try to execute the following SQL statement via
ODBC 3.51?
UPDATE mysql.user SET Password = Password('Blank') WHERE user = 'sdaf'
I can execute the statement from a tool such as SQLYog (logged in as 'myAdmin').
The error I get is:
DIAG [S1000] [MySQL][ODBC 3.51 Driver][mysqld-3.23.36]select command denied to user:
'myAdmin@ip address' for column 'user' in table 'user' (1143)
The user has the following privileges (to the mysql database):
User: No Global Privileges
Db (mysql): No Database wide privileges
Table Privilege to User table in mysql Db: SELECT
Column Privileges to User column in User table in mysql Db: SELECT
Column Privileges to Password column in User table in mysql Db: SELECT, UPDATE
I am doing this to allow an application administrator to reset user passwords using
my application.
TIA,
Tore.
GRANT and REVOKE issue...
I seem to be having some trouble with using GRANT and REVOKE. I am implementing a user management form in my application that does the following: REVOKE ALL ON mydb.* FROM 'Username'@'%' I get an error Access denied for user: 'MyAdmin@ipaddress' to database 'mydb'. The Revoke statement is being executed (via MyODBC 3.51) connected as a user with the permission defined in the following script: -- Create User account if not exists: GRANT USAGE ON mydb.* to 'MyAdmin'@'%' identified by 'MyPwd'; GRANT UPDATE ON mysql.* to 'MyAdmin'@'%' identified by 'MyPwd'; FLUSH PRIVILEGES; -- Make sure no previously created user by this name has anything beyond the required privileges: REVOKE ALL ON mydb.* FROM 'MyAdmin'@'%'; FLUSH PRIVILEGES; USE mydb; -- assign required privileges: GRANT USAGE ON mydb.* TO 'MyAdmin'@'%' identified by 'MyPwd' WITH GRANT OPTION; GRANT DELETE, INSERT, SELECT, UPDATE ON TableX TO 'MyAdmin'@'%' identified by 'MyPwd' WITH GRANT OPTION; GRANT DELETE, INSERT, SELECT, UPDATE ON TableY TO 'MyAdmin'@'%' identified by 'MyPwd' WITH GRANT OPTION; GRANT SELECT ON TableZ TO 'CL.Admin'@'%' IDENTIFIED BY 'CL.4d31n' WITH GRANT OPTION; FLUSH PRIVILEGES; Any ideas why this doesn't work as I expect it to? I couldn't find any related info in the MySQL doc'n. TIA, Tore. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HAVING vs. WHERE
HAVING is for qualifying result rows based on the value of aggregate functions, WHERE is for qualifying result rows based on individual (column) values. So in you case you should use WHERE. Although useful in the right situation, HAVING is used much less than a WHERE clause. One example of how to use HAVING would be to show values that have duplicate entries in the database: SELECT FirstName, LastName, Count(*) FROM MyTable GROUP BY FirstName, LastName HAVING Count(*) 1 HTH, Tore. - Original Message - From: Jonathan Arnold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 11:17 AM Subject: HAVING vs. WHERE In the MySQL reference, it warns against using HAVING for items that should be in a WHERE clause. I'm not sure what items should be in a WHERE clause. My exact problem is I want to select some records that have a dotted IP address as one of the fields. And I want to filter out the multicast addresses, which are the addresses that begin with the numbers in the range of 224. thru 239. This does it: SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,.)) 224 OR left(inetAdr,instr(inetAdr,.)) 239 and this works as well: SELECT *,left(inetAdr,instr(inetAdr,.)) as ia FROM Client HAVING ia 224 OR ia 239 and it is a little cleaner, although as I'm going to be doing this in a PHP script, cleanliness isn't all that important. So I guess I have 2 questions: 1] Which should I use? 2] Is this the easiest way to check for the multicast address? -- Jonathan Arnold (mailto:[EMAIL PROTECTED]) Amazing Developments http://www.buddydog.org It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so. Mark Twain - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perplexed by reverse SELECT statement.
I'm sure you are aware that this is a non-normalized database design, and that those tend to make designing queries more difficult and/or inefficient. The LIKE operator should give you what you want, but you need to be careful: (PHP string def:) $sSQL = SELECT list FROM table WHERE readBy LIKE '$UserID,%' OR readBy LIKE '%,$UserID' OR readBy LIKE '%,$UserID,%' You need to look for the three situations to avoid matching the wrong entries in for example (11,20) (5,11) or (5,11,20). HTH, Tore - Original Message - From: Rich Hutchins [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 04, 2003 8:42 PM Subject: Perplexed by reverse SELECT statement. Man, this was a tough MySQL question to assign a Subject to. Hopefully the answer is easier. I have a table called news which contains a column called readBy. The read by column is of type TEXT and it contains a CSV list of IDs of users who have read a certain news item. For example, if my user ID is 1 and I have read news item 50, the readBy column might look like this: 2,10,9,73,1 When I do a SELECT of all news items from this news table, I want to select only those items that I have not read - ones where the user ID 1 is NOT in the readBY column. I have attempted to pull this off using find_in_set and where not like, but have not been able to get the results I expect. Any advice? Thanks in advance. Rich - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Reshuffling unique integers
Allow negative values in the column (but don't use them). When modifying the values, give them their correct numeric value but make them negative. Then update the negative values to positive. HTH, Tore. - Original Message - From: Amittai Aviram [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Tuesday, March 04, 2003 11:28 PM Subject: Reshuffling unique integers I have a table in which the first column represents the order in which the data in the rows will appear in an HTML table on a Web page. The column is called ordr (to avoid conflict with the reserved keword order). This column is a primary key, but the values are not auto-incremented, they are assigned by hand at first and supposed to be revised by a PHP script. This is where the problem comes in. Suppose I want to change the order of a record with respect to the HTML table by changing the value of the ordr column for that row. Suppose I have five rows: 1 2 3 4 5 And I want to move the row currently at ordr 4 to ordr 1. Step 1. First, I can set the row with ordr 4 to ordr 0 to set it aside: 1 2 3 0 5 Step 2. Then I would raise each item's ordr value by 1 if it is = 1 and 4: 2 3 4 0 5 Step 3. Finally, I would change 0 to the desired target ordr value, 1: 2 3 4 1 5 But how can I accomplish step 2? It would seem at first as if the following should work: update table my_table set ordr = ordr + 1 where ordr = 1 and ordr 4; But this results in an error. When MySQL tries to update the first row from 1 to 2, it causes a (temporary) duplication of the value 2 in this unique (primary key) column. How else am I supposed to do this? Thanks! Amittai Aviram [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using SUM in a select statement.
What you are looking for appears to be report layout. Keep in mind that SQL will only return multiple rows, all with the same columns populated. To even get close to what you want, you can create a query that returns: TeamOwner, Player, Position, Goals, Points And then your application can massage it into the output format you want. A query to return BOTH detail information on players AND summary information on owners would have to be a union of two queries - one returning the player information and the other returning owner summary information (UNION requires version 4.0): SELECT O.OwnerName, 1 as SortOrder, P.Player, P.Position, Goals, Points FROM owners as O INNER JOIN teamplayers as T ON O.OwnerID = T.OwnerID INNER JOIN players as P ON T.PlayerID = P.PlayerID UNION SELECTO.OwnerName, 2 as SortOrder, 'Total' as Player, P.Position, Sum(Goals) as Goals, Sum(Points) as Points FROM owners as O INNER JOIN teamplayers as T ON O.OwnerID = T.OwnerID INNER JOIN players as P ON T.PlayerID = P.PlayerID GROUP BY O.OwnerName, SortOrder, Player, P.Position ORDER BY O.OwnerName, SortOrder, Player, P.Position Based on this, it is basically a question of suppressing repeated values of OwnerName. Adjust the query to match your actual situation. If you are using version 3.23, AFAIK you'll either have to use two separate queries or calculate the totals in your app from the detail data. HTH, Tore. - Original Message - From: C. Reeve [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 11:54 AM Subject: Using SUM in a select statement. Hi, Not sure how to explain this - but here is what I need. I have a hockey pool database with three tables - one has the players and their stats, one has the team owner, and the 3rd is the reference file that ties the two together. I want to be able to do a query for all the players on a particular team that are forwards and get a grand total of all their points. I already have a query that does something similar, but it just displays the individual players stats. Example: Team 1PlayerPosition GoalsPoints *Bob Bonk F 2250 HossaF3341 Total5591 This is what I want to achieve - and then the same for the rest of the teams. *This is my query that get the individual players points. $query = select team.name, player, position, gp, goals, ppg, gwg, shg, ass, pm, shots, pim, points from roster join reference join team where team.idn=reference.idn and reference.idp=roster.idp and team.idn = '$team' and position like '$position' order by points desc; TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: update question
Personally, I usually like Reference books better than Idiot's Guide books. And my favorite reference handbook for the SQL language (ANSI SQL-92 standard) is Martin Gruber, SQL Instant Reference (SYBEX). There is (was) at least a 2nd edition available. No fluff, just standard syntax and a good description of the key things you need to know for writing SQL queries. You'll still need to check the MySQL documentation to find out what is/isn't supported, what differes froim the standard, and what specific functions are available, etc. HTH, Tore. - Original Message - From: chip wiegand [EMAIL PROTECTED] To: Tore Bostrup [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 4:25 PM Subject: Re: update question On Sun, 2 Mar 2003 02:06:40 -0500 Tore Bostrup [EMAIL PROTECTED] wrote: I assume you are storing your dates in a char/varchar column - not a good choice to start with... :- yes, varchar. I'm still learning this stuff, and experimenting with it. Assuming all the values are supposed to be stored as MM-DD-YY (anothoer marginal choice, but the problem may not rear its head again for another 96+ years), you can do the following: heh, heh, I don't think I'll be around another 96 years to find out. Lets see, I would be 139 years old. Probably wouldn't be pushing too many keys on the keyboard at that age. This particular database/tables are not for business use, just my own learning. UPDATE mytable SET mydatestr = LEFT(mydatestr, LENGTH(mydatestr) - 3) WHERE mydatestr LIKE '__-__-__-__' Thanks for the help, worked great. I didn't know about the underscore being a wildcard character. I should look for a better MySQL book, the ones I have don't cover that info. Any suggestions for one that does? -- Chip HTH, Tore. - Original Message - From: chip wiegand [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 1:46 AM Subject: update question I need to make a change to a field in a table and don't know how to write the correct sql statement. I made an error and now have a date field with the year repeated twice - 01-01-03-03 - there are aproximately 100 rows like this, and maybe 20 or so that are formatted properly. How can I remove the last 3 characters while leaving other rows that do not have this problem alone? (other than manaully editing each row of course) Thanks, Chip W. www.wiegand.org --- -- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Duplicate
Simply create a UNIQUE Index on the column to avoid duplicates. That will be case insensitive, and trailing spaces AFAIK, but spaces inside the columns text have significance. HTH, Tore. - Original Message - From: Dee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 9:33 PM Subject: Duplicate Hi, I am still learning MySQL and trying to find certain things I use to do in Access. I having been looking and can not find an way to set a field so that it can not be duplicated. Since I can not find that I tryed looking for the record using SELECT and WHERE but doing an IF on the result of the QUERY does not help. Does anyone know how I can check for a duplicate in a database before adding a record. I am checking a text field for duplicate entry. Which means that I have to ignore case and spacing. Thanks for any help. Dee - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: update question
I assume you are storing your dates in a char/varchar column - not a good choice to start with... :- Assuming all the values are supposed to be stored as MM-DD-YY (anothoer marginal choice, but the problem may not rear its head again for another 96+ years), you can do the following: UPDATE mytable SET mydatestr = LEFT(mydatestr, LENGTH(mydatestr) - 3) WHERE mydatestr LIKE '__-__-__-__' HTH, Tore. - Original Message - From: chip wiegand [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Sunday, March 02, 2003 1:46 AM Subject: update question I need to make a change to a field in a table and don't know how to write the correct sql statement. I made an error and now have a date field with the year repeated twice - 01-01-03-03 - there are aproximately 100 rows like this, and maybe 20 or so that are formatted properly. How can I remove the last 3 characters while leaving other rows that do not have this problem alone? (other than manaully editing each row of course) Thanks, Chip W. www.wiegand.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
For readability, using the INNER JOIN clause makes it immediately clear what/how you are joining the tables, but it isn't too hard to read that from a WHERE clause either. Of course, for other types of join, definitely use the JOIN statement. It is *possible* that the optimizer may treat an INNER JOIN differently from a join in the WHERE clause - I don't know how the MySQL optimizer works. Regards, Tore. - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: Tore Bostrup [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, February 28, 2003 7:58 AM Subject: Re: SELECT DISTINCT question Tore, Thanks very much for this. Question: Is it better to use INNER JOIN than a WHERE clause (for readability)? They do the same thing, don't they? I will try this out as soon as a finish another piece of my site. I'm under a big deadline crunch. I'll be interested to see if others know of other ways of handling this. - Sheryl - Original Message - From: Tore Bostrup [EMAIL PROTECTED] To: Sheryl Canter [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, February 28, 2003 1:41 AM Subject: Re: SELECT DISTINCT question DISTINCT works on the result set and across the entire select list. It will suppress duplicate result *rows*. This is an interesting problem when using MySQL due to the lack of support for nested SELECTs. In other database systems, I'd use a correlated subquery in the where clause, but with MySQL a different solution would be required. There may be a more direct way, but the following works on both version 4 and 3.23: DROP TABLE IF EXISTS tmproymax; CREATE TEMPORARY TABLE tmproymax (ProgramID int, Royalty decimal(10,2)); INSERT INTO tmproymax SELECT ProgramID, Max(Royalty) as MaxRoyalty FROM royalties GROUP BY ProgramID; SELECT R.ProgramID, R.Royalty, A.AuthorID, A.FirstName, A.LastName FROM authors as A INNER JOIN royalties as R ON A.AuthorID = R.AuthorID INNER JOIN tmproymax as RM ON R.ProgramID = RM.ProgramID AND R.Royalty = RM.Royalty; For using this with PHP, I'm pretty sure you'll have to run each statement separately, but using the same connection, and you should get the correct result from the last select. I'd be interested to hear if there is another trick to working without a correlated subquery for finding details off of a row identified by Min(), Max(), etc. I tried using a CREATE TEMPORARY TABLE tmproymax SELECT ... but couldn't get the aggergate column named (in version 4), so I could use it in the join in the last statement. For version 3.23, that construct would work: DROP TABLE IF EXISTS tmproymax; CREATE TEMPORARY TABLE tmproymax SELECT ProgramID, Max(Royalty) as Royalty FROM royalties GROUP BY ProgramID; SELECT R.ProgramID, R.Royalty, A.AuthorID, A.FirstName, A.LastName FROM authors as A INNER JOIN royalties as R ON A.AuthorID = R.AuthorID INNER JOIN tmproymax as RM ON R.ProgramID = RM.ProgramID AND R.Royalty = RM.Royalty; HTH, Tore. - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:49 AM Subject: SELECT DISTINCT question I need help with a query. I have a 'royalties' table that looks like this: AuthorIDProgramIDRoyalty -- Author1 Program1 0.15 Author2 Program1 0.10 Author3 Program2 0.25 Author4 Program3 0.05 Author5 Program3 0.20 The primary key of this table is a combination of AuthorID and Program ID. Author information is stored in a separate table: AuthorIDFirstNameLastName Author1 Joe Smith Author2 BrianJones Author3 Jeff Tucker Author4 MichaelMoore Author5 MarkMann The main page of my Web site has a program list that includes the program name and author name (and other information). I want it to show the author receiving the highest royalty amount. Right now I'm not considering the possibility that more than one author can work on a program (since currently none is), and my SELECT statement looks similar to this: SELECT * FROM programs p, authors a, royalties r WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID I could change this to SELECT DISTINCT * ..., but then which author would I get? If it's always the first encountered row, then could I avoid checking the royalty by always inserting the authors into the table in the correct order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT to choose which row to return? If I wanted to do it right and select the author receiving
Re: query or php with a join
Since you haven't told us your table designs, I have to guess, but something
like:
SELECT I.ItemSKU,
I.ItemName,
I.ItemDescription,
I.PostCode,
I.Category,
I.CityID,
I.CTelephone,
I.ItemID,
I.Cfax,
I.Cemail,
I.Caddress,
I.CTown,
I.Cwww,
C.CityName
FROM items as I
INNER JOIN city as C
ON C.CityID = I.CityID
WHERE C.CityID='$CityID'
Your Order By CityID is not required, since your query only selects a
single city anyway.
I prefer formatting the query as above to make it easire to read. For the
same reason, I also prefer to use table aliases. With short table names
like items and city, this is not a big deal, but when the table names get
longer, the query can get obscured by the prefixes.
HTH,
Tore
- Original Message -
From: Andrew [EMAIL PROTECTED]
To: MySQL-Lista [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 10:41 AM
Subject: query or php with a join
PHP Guys Dolls I have a sight display issue that I just need to resolve
:)
After a select I end up with a record = 9 but I want to display the record
as
the name not the value.
The value was inserted as a value so I need to make a join to the original
table
in the query, but alas I have tried a few things without any luck. So
it's the
experts whom I need to help out:)
The query is:
$result=mysql_query(SELECT items.ItemSKU, items.ItemName,
items.ItemDescription, items.PostCode, items.Category, items.CityID,
items.CTelephone, items.ItemID, items.Cfax, items.Cemail, items.Caddress,
items.CTown, items.Cwww FROM items WHERE CityID='$CityID' ORDER BY
CityID);
while ($row = mysql_fetch_row($result)) {
$City=$row['5'];
}
the display is:
? echo $City;
So I need to create a query that then joing the CityID from items to the
CityName in table city. Or is it the php that needs to be altered?
Andrew
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql: change column to row
The technique to do this is called a cross-tab(ulation) query or a pivot table. A simple (and pretty static) cross-tab query can be created by the use of CASE WHEN. But if you want a more generic solution that works for all values, it gets a bit more complex. Spend some time investigating Google hits for searches on MySQL Crosstab, etc. HTH, Tore. - Original Message - From: Vivian Wang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 28, 2003 10:29 AM Subject: mysql: change column to row mysql, Can I change the table one column to one row like this? Table1: key, count A 123 B 456 C 789 to Table2: A B C 123 456 789 Thanks, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
Your guess is correct, DISTINCT works on the result set - i.e. if the result set contains two result rows that contain exactly the same values, it will eliminate the duplicate from the result set. SELECT DISTINCT is equivalent with using a GROUP BY without an aggregate function. SELECT DISTINCT x, y, z Form Table is equivalent with SELECT x, y, z FROM Table GROUP BY x, y, z HTH, Tore. - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Frank Peavy [EMAIL PROTECTED] Sent: Friday, February 28, 2003 11:50 AM Subject: Re: SELECT DISTINCT question Frank, Sorry that I seem to be explaining this so poorly. I'll try to clarify what I'm trying to do and what my questions are. Someone on this list did give me code that I think will work. I haven't had a chance to try it yet. My question about how DISTINCT works is this: does it operate on the *result set*? So depending on my SELECT statement and which fields I bring in, different rows may be distinct? It's my guess that it works this way. I want to confirm. I certainly don't want to display the author's royalty percent on my Web site. I just want to use it to select the principle author, and display the principle author's name with the program name. So I'll have: Program NameAuthorDescriptionUpload date - Program1Smith Program2Jones Program3Harvey When more than one author works on a program, the royalty *percents* (not dollar values) will be apportioned according to their contributions to the project. On my Web site, I only want to display the name of the principal author--defined as the author who is paid the highest royalty percent *on that project*. Most projects will have only one author. A few will have multiple authors when someone had to take over the code or two programmers decided to collaborate. Which is it, grouped by Program Id and ordered by royalty percent Or Just the Max in each group. You need to make up your mind I don't think I'm having trouble making up my mind here--just having trouble explaining my purpose. I think you can see now that I want to group by ProgramID and then find the highest royalty percent within that group so I can identify which author name to display. Thanks for your help. - Sheryl - Original Message - From: Frank Peavy [EMAIL PROTECTED] To: Sheryl Canter [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, February 28, 2003 11:22 AM Subject: Re: SELECT DISTINCT question Sheryl, I'm trying to determine which author has the highest royalty percent FOR EACH PROGRAM, not overall. I'm displaying a list of programs and authors, and when there is more than one author, I want to show the principal author (i.e., the one earning the highest royalty percent). Ok, so your desired result will look like... what??? Program ID, Author, Royalty Amount 1, smith, $100 2, jones, $250 Right? Ok, so what should your SELECT statement look like...? Shouldn't be too hard to figure out... Also, I don't think you want SELECT DISTINCT * I think SELECT DISTINCT a.AUTHOR, r.ROYALTIES. etc. is better Also remember, a.AUTHOR will give you different results than r.AUTHOR.. How will the results differ? First of all, since you have tables, have you tried populating the tables with data. What did you get? I guess I'm not clear on how DISTINCT works. Think of DISTINCT as meaning unique... Or, another way of looking at it, it answers the question, what are the distinct (or unique) pieces of data in a column?. Will it only look at combinations of a.AUTHOR and r.ROYALTIES if I write it that way? Yes, if you write it that way... Assuming Table a contains this: Author -- Smith Jones Johnson Assuming Table r contains this: Author Royalties Smith $100 Johnson $100 Based on the logic I described above, how do you think the queries results will differ, depending upon if I used Table a or Table b in my DISTINCT statement? SELECT DISTINCT a.Author, will resulting in: Smith Jones Johnson SELECT DISTINCT r.Author, will resulting in: Smith Johnson but... no Jones, because Jones has no record in Table b. I will say it again, it answers the question, what are the distinct (or unique) pieces of data in a column?. I don't want all the authors in order of royalty percent. I want the them to be in groups by Program ID and ordered by royalty percent within that (or just take the max within each group). Which is it, grouped by Program Id and ordered by royalty percent Or Just the Max in each group. You need to make up your mind - Before posting, please check: http://www.mysql.com/manual.php (the manual)
Re: I'm not sure if this is HTML question or PHP...
The technique you are looking for is usually referred to as recordset paging. A Google search for PHP Recordset Paging brought back a slew of hits. Find one that appeals to you/explains what it does so you can use it. HTH, Tore. - Original Message - From: Stitchin' [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 11:48 AM Subject: I'm not sure if this is HTML question or PHP... I've had great success with my first PHP/mySql project. It's a catalog to showcase my embroidery designs. My embroidery database is working, the php code I embedded in my FrontPage2002 pages is working great, I can click on my admin button and upload info into the database. Because of the pictures that come up with each line item of design detail ...I'm concerned that when the member searches for the designs, if it brings up too many on one screen that the users will be able to take a nap while the screen loads. Is there a way for me to tell the web page to only load, lets say 6 on a page and have a button to go to the next 6 until all the designs that fit the criteria are gone through? Thanks to those out there who have responded to my previous issues ... it's nice to know that there are members of this group willing to take time to answer questions from such greenies out there like me with respect and kindness. You've given me the courage to continue and I'm almost done with my first adventure into this wonderful new world for me. Renee :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inner join question!
The only reason I could see for this would be if you have duplicates (across date num1 num2 time) in both tables, or quadruplicates in one. You join looks correct, so take a closer look at your data. Try running the following queries: SELECT A.date, num1, num2, A.time, Count(*) FROM A GROUP BY A.date, num1, num2, A.time HAVING Count(*) 1 SELECT B.date, num1, num2, B.time, Count(*) FROM B GROUP BY B.date, num1, num2, B.time HAVING Count(*) 1 HTH, Tore. - Original Message - From: Ramesh Pillai [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 10:36 PM Subject: Inner join question! All, I have two tables like the following table A date num1 num2 time table B date num1 num2 time When I run a query like the following select * from A as a inner join B as b on a.num1 = b.num1 and a.date=b.date and a.num2=b.num2 and a.time=b.time I am getting the results repeated 4 times, could someone tell me why I am getting 4 rows and how can I elliminate it? Thanks. Ramesh __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
DISTINCT works on the result set and across the entire select list. It will suppress duplicate result *rows*. This is an interesting problem when using MySQL due to the lack of support for nested SELECTs. In other database systems, I'd use a correlated subquery in the where clause, but with MySQL a different solution would be required. There may be a more direct way, but the following works on both version 4 and 3.23: DROP TABLE IF EXISTS tmproymax; CREATE TEMPORARY TABLE tmproymax (ProgramID int, Royalty decimal(10,2)); INSERT INTO tmproymax SELECT ProgramID, Max(Royalty) as MaxRoyalty FROM royalties GROUP BY ProgramID; SELECT R.ProgramID, R.Royalty, A.AuthorID, A.FirstName, A.LastName FROM authors as A INNER JOIN royalties as R ON A.AuthorID = R.AuthorID INNER JOIN tmproymax as RM ON R.ProgramID = RM.ProgramID AND R.Royalty = RM.Royalty; For using this with PHP, I'm pretty sure you'll have to run each statement separately, but using the same connection, and you should get the correct result from the last select. I'd be interested to hear if there is another trick to working without a correlated subquery for finding details off of a row identified by Min(), Max(), etc. I tried using a CREATE TEMPORARY TABLE tmproymax SELECT ... but couldn't get the aggergate column named (in version 4), so I could use it in the join in the last statement. For version 3.23, that construct would work: DROP TABLE IF EXISTS tmproymax; CREATE TEMPORARY TABLE tmproymax SELECT ProgramID, Max(Royalty) as Royalty FROM royalties GROUP BY ProgramID; SELECT R.ProgramID, R.Royalty, A.AuthorID, A.FirstName, A.LastName FROM authors as A INNER JOIN royalties as R ON A.AuthorID = R.AuthorID INNER JOIN tmproymax as RM ON R.ProgramID = RM.ProgramID AND R.Royalty = RM.Royalty; HTH, Tore. - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:49 AM Subject: SELECT DISTINCT question I need help with a query. I have a 'royalties' table that looks like this: AuthorIDProgramIDRoyalty -- Author1 Program1 0.15 Author2 Program1 0.10 Author3 Program2 0.25 Author4 Program3 0.05 Author5 Program3 0.20 The primary key of this table is a combination of AuthorID and Program ID. Author information is stored in a separate table: AuthorIDFirstNameLastName Author1 Joe Smith Author2 BrianJones Author3 Jeff Tucker Author4 MichaelMoore Author5 MarkMann The main page of my Web site has a program list that includes the program name and author name (and other information). I want it to show the author receiving the highest royalty amount. Right now I'm not considering the possibility that more than one author can work on a program (since currently none is), and my SELECT statement looks similar to this: SELECT * FROM programs p, authors a, royalties r WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID I could change this to SELECT DISTINCT * ..., but then which author would I get? If it's always the first encountered row, then could I avoid checking the royalty by always inserting the authors into the table in the correct order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT to choose which row to return? If I wanted to do it right and select the author receiving the maximum royalty, how would I adjust the SELECT statement? TIA, - Sheryl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index questions
4: The index name allows you to for instance delete an index, specify it in a hint, etc. But for the most part, a developer won't care what the name of an index is. 5: AFAIK, the InnoDB tables support some of the more professional RDBMS features, such as Transactions and Foreign Keys. I do not know what the impact of moving to InnoDB tables would be. There may be some syntax differences(?), performance, and size impacts. And a different set of bugs, etc. Declaring the FOREIGN KEY constraint in a database/on tables that do not actually implement them can be a two-edged sword: You at least *document* the *intent* that way, but if anyone sees the declaration and expects it to do something, they could be in for a surprise. Regards, Tore. - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: Tore Bostrup [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 10:29 AM Subject: Re: index questions snip 4: The name of an index does not determine whether it gets used or not. So if I have a primary index on CustID, it will be used if I reference CustID rather than PRIMARY? Why does the index have a name if it's never used? Are there situations where you should use the index name rather than the name of the column that is indexed? 5: snip The FORIGN KEY statement is there to implement referential integrity in the database through declarative rules. Of course, you can be sloppy and just not declare the relationships, just construct your queries as if the data is OK... But I won't recommend it. I checked the manual and only InnoDB tables support these rules. I've been using MyISAM. Do you think I should change to InnoDB? snip I have been unable to verify whether the CHECK constraint is actually implemented with any table types or in any versions of MySQL. I rechecked the manual (now that I can read it more easily). This is only available on InnoDB tables on MySQL 3.23.44 or later (like the FOREIGN KEY and REFERENCES syntax). Thanks again for your help. - Sheryl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with SQL...
It is usually called a self-join, and it is a very useful technique. You have to do this when you need to obtain or access two separate subsets from a single table and somehow use those in a join. In this case, you want the groupids a specific member belongs to (set one) as well as all members belonging to the same group (set two). And the two instances of the table have to be identified through use of table aliases. Regards, Tore. - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: Don Read [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 10:56 AM Subject: Re: Help needed with SQL... I've never seen opening a table twice and then doing a join back with itself. Am I wording this correctly? Is that how to describe what you're doing? Thanks for posting this to the full list. - Sheryl - Original Message - From: Don Read [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 5:11 AM Subject: RE: Help needed with SQL... On 26-Feb-2003 [EMAIL PROTECTED] wrote: Yes , I had use this query statement in my MySQL server, But I have a query about it. Why are you use WHERE a.groupname=b.groupname AND members.id=b.memberid AND a.memberid=1? Can you give me a explain or give me a advise! Sure. mysql SELECT DISTINCT members.* FROM members, groups as a, groups as b - WHERE a.groupname=b.groupname AND members.id=b.memberid - AND a.memberid=1; The 'a.memberid=1' clause looks up the groups that member 1 belongs to in the groups table (as a), giving 'group1' 'group2'. Then it joins back against the groups table (as b) with the 'a.groupname=b.groupname' clause to get the folks that belong to these group(s). Finally the 'members.id=b.memberid' bit selects the records out of the members table, with the DISTINCT function suppressing any duplicates. Clear as mud? (I could've be a little more obvious if I'd put the 'a.memberid=1' clause first. Sorry ...) Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Column Type help
The only two considerations I can think of to choose one type of TEXT column over another would be: 1: The added storage required by a LONGTEXT over a TINYTEXT is only 3 bytes per row. Compared to the anticipated average size of the data, this would be neglibible. 2: If you want to impose somewhat of an upper limit on the amount of space used by one resume, you *could* use a specific text type to do so. However, I don't see a good reason to use a TINYTEXT where a varchar(255) should be fine, and I believe practical considerations would kick in for anything beyond TEXT (max 65535). So I'd probably go with the LONGTEXT, too. HTH, Tore. - Original Message - From: Tom Ray [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:28 AM Subject: Column Type help Hey there, I'm kinda new to using mysql to its full potential and I was wondering something. I want to store resume information, but I just want the user to cut and paste the resume in the Resume field and then store all that in one column in the table. Which table type should I use for this? Longtext? TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Random 32bit number for columns?
I assume the RAND() function can be used in in an insert: INSERT INTO target (MyRandVal, OtherColumns) VALUES (RAND() * (MaxVal - MinVal) + MinVal, OtherData) OR INSERT INTO target (MyRandVal, OtherColumns) SELECT RAND() * (MaxVal - MinVal) + MinVal, OtherData FROM ... HTH, Tore. - Original Message - From: 1LT John W. Holmes [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Scott Brown [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 3:59 PM Subject: Re: Random 32bit number for columns? I am trying to find a means to create a column that self-populates with unique random 32bit integers in MySQL. Do I need to do this in code, or is there a column spec/extra that I can use to populate a column with random numbers on an insert? Ideally, the database itself would manage this column... The defaults for a column must be a constant. You'll have to do this in code. ---John Holmes... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql:Question about definition and index for performance
alter table tableA add index(key1, acct) creates a single index across the two columns. Trying to visualize the index as a sorted list (of course it is a b-tree), it will look something like: (key, acct:) 1,1 1,2 1,3 1,5 1,8 2,1 2,4 2,5 2,6 3,2 3,9 ... etc. Therefore, this index is not very useful when you are using (only) acct (the second column in the index) for your join. Instead, create the two separate indexes with alter table tableA add index(key1) alter table tableA add index(acct) This will let MySQL determine which index(es) to use for optimizing the queries. If many other queries use both key1 and acct together, you may consider hcreating all three indexes, or the composite index as well as the separate index for acct. HTH, Tore. - Original Message - From: Vivian Wang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 11:52 AM Subject: mysql:Question about definition and index for performance mysql, tableA, definition: key1 char(17) not null, acct char(12), other char (5) tableB is same definiton like tableA. I add index for both tableA and tableB like: alter table tableA add index(key1, acct) I will do left join with tableA and tableB like : tableA left join on tableB on tableA.acct=tableB.acct where tableB left join on tableA on tableB.acct=tableA.acct where The tableA has 32,000,000 records and tableB has 500,00 records. I feel some thing wrong about how define the table fields, add index or something, because I only use acct field for left join on. I hope I can change some table definition or about add index to improve the performance. Also, I like to know what is difference between: alter table tableA add index(key1, acct) or alter table tableA add index(key1) alter table tableA add index(acct) Any suggestion? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with MySQL, MyODBC, Access 2002 and Japanese.
Don't know, but: Are you using the Japanese Access 2002? What locale is your OS configured for? HTH, Tore. - Original Message - From: Juan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 6:12 AM Subject: Problem with MySQL, MyODBC, Access 2002 and Japanese. Help!, I am developing a multilingual site in English, Spanish and Japanese. I am using MySQL with PHP in the server side, and Access 2002 in the local side, using MyODBC to communicate MySQL with Access 2002. MySQL and PHP works toguether ok in Japanese, English and Spanish (using EUC_JP in both PHP and MySQL), Access 2002 (XP operating system) works ok in japanese (XP use UNICODE). The big problem is MyODBC. When I connect Access 2002 with MySQL (via MyODBC) to view the japanese characters stored in MySQL, in Access 2002 I only see a lot of strange characteres, I view the same characters without problems using phpMyAdmin. I have been several days searching the web without success. Any suggestion to solve this problem (in English or Spanish, no Japanese :-)? ___ Yahoo! Móviles Personaliza tu móvil con tu logo y melodÃa favorito en http://moviles.yahoo.es - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Reference to a downloadable file
How do you serve up the other data from the database? Are the ZIP files in a directory that is accessible from the web? In the simple case, all you need to do is create an a href=zipfileURLDownload Design/a construct in your output from the php script. So if the column returned from the MySQL query is called ZIPFile, and you have a rowset object $row, you would use something like: $DownloadURL = $row-ZIPFile; echo a href='$DownloadURL'Download Design/a; for each line item. Of course you can use an img tag instead, but the principle is the same. HTH, Tore. - Original Message - From: Stitchin' [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 9:34 PM Subject: Reference to a downloadable file I am building a web application for my embroidery business which is basically a catalog program within a members only subscription site. On each line item, I want to have a spot for a zip file for members to click on to download the embroidery design file. I have a field in my mySql database to hold the filename of that zip file. The PHP and mySql books I have here don't have any examples of a web page with a download link and how to have it show on the detail lines being brought up with a query on a specific design category. Does anybody know of any resources on the web that may show an example of this for PHP and mySql? Renee Toth Stitchin' Up A Storm www.stitchinupastorm.com -Original Message- From: Stefan Hinz [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 12:37 PM To: Amy Joseph Kormann Cc: [EMAIL PROTECTED] Subject: Re: Continuing problem Ami, Joseph, I'm running the Windows mysqld application and attempting to connect to it using the CygWin libraries without any success. The mysql and winmysqladmin all connect fine to mysqld. The error I get from my application is 'Error 2002, cannot connect through socket MySQL' or it's about not finding port 3306 or it cannot find /tmp/mysql.sock (which is never created by the mysqld even after specifying it in my c:\my.cnf and ~/.my.cnf files). Apparently, this is a socket problem. You have no Unix socket on your Windows MySQL server through which to connect. Try to use TCP/IP, by connecting with the host specified, where host is something else but 127.0.0.1 or localhost; like this: mysql -h machine_name Where machine machine_name is the host name of your Windows machine. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index questions
If my answers are too basic, please accept my apology. 1: There is no difference: KEY is a synonym for INDEX. 2: In a syntax description, the use of [square] brackets means that the part in brackets is optional. Whether you include it or not doesn't change the semantics. 3: The PRIMARY KEY is a special index that: * Has to be UNIQUE * Can not be NULL * There can only be one PRIMARY KEY on a table (biut multiple columns can be part of the primary key). I don't know why Dreamweaver adds the KEY PRIMARY KEY definition. There are different ways to define the primary key, AFAIK. 4: The query optimizer will determine which index(es) is (are) likely to provide the best results (most efficient query execution) for your query. As a rule of thumb, index columns that are frequently used in joins and/or where clauses. If you use a function on an indexed column in such a clause, do NOT expect the index to be usable - so avoid using functions on columns in those cases. AFAIK, in v.4.x you can specify hints in your query if the optimizer does not select the best index for a particular query. The name of an index does not determine whether it gets used or not. There are several factors that contribute to which index(es) are used for each query. I am not familiar with MySQL's query optimizer algorithm. 5: I'm not sure if any other types of tables implemenrt this in a later version, but AFAIK the FOREIGN KEY in MySQL used to be there only for compatibility, but had no real impact on the database. In MySQL Server 3.23.44 and up, InnoDB tables support checking of foreign key constraints. Theoretically, a FOREIGN KEY is a constraint that is placed on a column/index. It means that the column references a (matching) column (usually the primary key) in another table. The FOREIGN KEY represents the child in a parent/child relationship between two tables. To visualize this, consider a simple Department/Employee table relationship: Departments (DeptID, DeptName) 1, Sales 2, Accounting 3, Personnel 4, RD 5, Manufacturing Employees (EmpID, EmpName, DeptID) 1, Johnny, 3 2, Sammy, 2 3, Terry, 1 4, Sandy, 1 5, Jeannie, 4 6, Billy, 5 7, Stevie, 5 ...etc. You would have the Employees.DeptID defined as a FOREIGN KEY referencing Departments.DeptID. This constraint/restriction means that you will not be allowed to insert or update a row in Employees where the Dept column did not contain a value already existing in the Departments table. It also means you will not be allowed to update the DeptID in the Departments table (or delete a row) if there are dependent rows in the related (Employees) table. The different actions that you can specify with this constraint determine how MySQL will react if you violate the restriction (constraint). Action CASCADE means that a change to the master table will also be applied (CASCADEd) to the child table, i.e. related values will be updated or deleted. AFAIK, action RESTRICT means you'll be denied these actions (error generated and statement not performed). I'm not sure what the SET NULL | NO ACTION | SET DEFAULT actions do, but you can guess... The FORIGN KEY statement is there to implement referential integrity in the database through declarative rules. Of course, you can be sloppy and just not declare the relationships, just construct your queries as if the data is OK... But I won't recommend it. 5b: I'm not sure if this is still true, but AFAIK, the CHECK constraint in MySQL used to be there only for compatibility, but had no real impact on the database. The CHECK (expression) is a constraint on what values are legal in a particular column (or potentially combinations of values in a row). A general description of CHECK Constraints can be fund at http://www.datanamic.com/support/ta001.html. I have been unable to verify whether the CHECK constraint is actually implemented with any table types or in any versions of MySQL. HTH, Tore. - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 7:07 PM Subject: index questions I have some basic questions that are driving me nuts, and I can't find answers anywhere. I've been banging my head against the wall for hours and hours. I've searched everywhere on the internet and looked in every book I own. I can't find the information. Here are my questions. In the CREATE TABLE statement, there are options to create keys and indexes, and this is where I'm confused. I'll try to be as clear as possible in my questions because I'm really desperate for help. 1. What is the difference between a KEY and an INDEX? These are two separate options: KEY [index_name] (index_col_name,...) INDEX [index_name] (index_col_name,...) I think the difference can't be uniqueness because UNIQUE is also an option: UNIQUE [INDEX] [index_name] (index_col_name,...) 2. In the UNIQUE option above and in the FULLTEXT option, one of the optional parameters is [INDEX]. What
Re: Large table or several tables ?
4 million rows is a large table, but not a huge table. 400 is a large number of tables. Does the data in the 400 arrays describe the same type of entity or do they provide *generic* attribute info for different types of entities? If so, they would logically belong in a single table, otherwise not. I would start out by using the logical grouping, and worry about partitioning the table(s) later - if required for performance. HTH, Tore. - Original Message - From: gregory lefebvre [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 10:07 AM Subject: Large table or several tables ? Hye the list, There is my problem. I have to register about 400 large arrays (~1 lines each) in my MySQL database and I don't know which of the following choices is better. Either create a large tables as a stack, which contains all arrays Either create as many tables as there are arrays and use references to each table. Any advice is welcome. Thank you very much Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unknown table in field list
Just what it says. Your first query attempts to list a column from a table that is not selected from (building). Your second query attempts to assign the alias building to both the parcels table and the building table, and then subsequently does a join on the building table. Use the following (iuse table aliases for increased readability): SELECT P.DXF as 'record', B.address as 'results1', P.relname as 'results2' FROM parcels as P INNER JOIN building as B ON P.DXF = B.DXF WHERE P.relname LIKE '%jones%' ORDER BY P.relname desc; HTH, Tore. - Original Message - From: Diver8 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 11:46 AM Subject: Unknown table in field list Hi again. I've tried searching the list archives for this all morning, but they don't seem to be working (never get any results, page times out). I found one report of this on google but the guy said he figured out the problem but didn't say what he did to fix it! I just added a table to my database (about four hours ago, actually). This table is called 'building'. My overall database looks like this: mysql show tables; ++ | Tables_in_tmp_db_work | ++ | building | | parcels| ++ 2 rows in set (0.00 sec) I'm trying to run the following query: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels where parcels.relname like '%jones%' order by parcels.relname desc; That query returns: ERROR 1109: Unknown table 'building' in field list If I try this query instead: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels building INNER JOIN building as building on parcels.DXF = building.DXF where parcels.relname LIKE '%jones%' order by parcels.relname desc; The query returns: ERROR 1066: Not unique table/alias: 'building' So what's wrong?? Thanks. __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Directory structure
Depending on how you plan to use the data, you may be interested in the Nested Set Model. There are articles listed at searchdatabase.techtarget.com if you search for that string, but you may need to register there (can't supply links as they contain user ID...). Or if you have Joe Celko's SQL for Smarties, a whole chapter is dedicated to this model. If you are presenting one level at a time, the adjacency model (which is the one you are using) may be a good choice. If you want to perform cross-section operations or operate on entire subtrees, the nested set model would be better suited. HTH, Tore. - Original Message - From: Adam de Zoete [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 12:46 PM Subject: Directory structure Hi, I am trying to figure out the best method for creating a directory with mySQL (Lasso). Let me first say that I am new-ish to SQL, so might be jumping into the deep-end. I am building this for a site and am looking for a fast method of displaying where users are in the directory, or where an entry exists within it's structure. At the moment I have this table (just for the areas of the directory) and another table for all the entries that live in the directory: ID BIGINT Category VARCHAR(35) Parent_ID BIGINT Branch VARCHAR(255) ++---+---+--+ | ID | Category | Parent_ID | Branch | ++---+---+--+ | 1 | Home | NULL | Home | | 6 | Software | 1 | Home/Software| | 13 | Internet | 6 | Home/Software/Internet | | 34 | Servers |13 | Home/Software/Internet/Servers | ++---+---+--+ I am not sure whether I have got any of this right, but I have developed it this way in order to minimize the amount of searches that need to be performed in order to show the Branch of multiple entries within my search results. i.e. as far as I am aware if I don't store the Branch alongside then I have to loop through IDs and ParentIDs with multiple searches to build the correct Branch for each entry that I am displaying in my search results. This amounts to a lot of searches. My questions are: Should I be using separate tables for each category deep? If, so how could I make sure it could grow deeper? Can I generate the Branch dynamically within my table(s)? Can anyone suggest a proven method for creating a flexible directory structure that can grow and have categories that can have multiple parents? Any suggestions would be greatly appreciated, Thanks in advance, Adam -- // Adam de Zoete \\ [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: # of Business Days?
If you truly want only business days - i.e. you don't want to count Holidays - the only solution is to have a tables with all the business days. You'd populate this periodically with future dates as required, and a human may be required to mark off the holidays (unless you can create an algorithm that generically describes your company's Holidays - and they never change...). Once you have that table, use a Count(*) on dates between a and b. HTH, Tore. - Original Message - From: Lucas Cowgar [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Sent: Monday, February 24, 2003 12:58 PM Subject: # of Business Days? Can anyone out there help me with a SQL query? I need to find the number of business days between date a and b? Obviously finding simply the number of days is easy, but I have no clue how to find the number of business days. TIA! Lucas Cowgar Information Technologies Department Eldorado Services Group Inc. http://www.eldoserv.com [EMAIL PROTECTED] (330) 861-3009 All your base are belong to us - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unknown table in field list
You are getting two rows not because there is one in parcels and one in building, but because one of the tables has two rows and the other has one that matches according to the WHERE clause and the join criteria. To see the source data, do separate selects on the two tables: select * from parcels where DXF = '150-3-6' select * from building where DXF = '150-3-6' and ADDRESS LIKE '%21369 Vails%' Looking at the resulting data will help you determine why you are getting two result rows. If the source data is correct/as you want it to be, the keyword DISTINCT will suppress duplicate rows in the output. I usuallu try to avoid using it unless strictly required, since its use tends to hide an undesired Cartesian Products in a badly written query (usually from incomplete or improper join criteria). After reviewing your source data, try running your query with SELECT DISTINCT ... (rest of query unchanged). It should return 1 row in the sample you show, but if you had 2 DIFFERENT addresses, it would return both rows. (DISTINCT works across the entire select list (unless used in an aggregate) - this tends to be a source of some confusion among people who have limited SQL experience). HTH, Tore. - Original Message - From: Diver8 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:36 PM Subject: RE: Unknown table in field list Jon and Tore, thanks so much for your kind help. I greatly appreciate it. I still have one bit of confusion about this whole thing. I'm reading through the manual as I type this so maybe I'll find the answer. If someone can help clarify, I'd appreciate that as well. Running this query: mysql SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', building.ADDRESS as 'results2' from parcels INNER JOIN building on parcels.DXF = building.DXF where building.ADDRESS LIKE '%21369 Vails%' order by building.ADDRESS desc; Returns these results: +-+-+-+ | record | results1| results2 | +-+-+-+ | 150-3-6 | 21369 VAILS MILL RD | 21369 VAILS MILL RD | | 150-3-6 | 21369 VAILS MILL RD | 21369 VAILS MILL RD | +-+-+-+ 2 rows in set (2.08 sec) Okay. Basically, what's happening is that I'm ending up with a duplicate result. I think I understand why that's happening - I'm searching two tables it's returning the results from each table that match the 'DXF' entry. My problem is that I need the query to be smart enough to figure out if the result is a true duplicate if so, to discard that second result. To further complicate the issue... there will be instances where duplicate entries *are* to be expected. For instance, one parcel of land may have two addressed buildings on it (for instance a duplex or apartment building). If that's the case, the DXF entry would be the same for both addresses, and I would expect to get two results from the query. In the case of the query cited at the top of this message, that happens to be one parcel of land with one addressed structure on it. Therefore, I would only want to get one result back. My point in mentioning this is that I don't think a simple LIMIT 1 would work here. I know it won't because I've tried it. Maybe this is going to be impossible to do? The parcels table has a unique key - 'DXF'. The buildings table does not. There may be duplicate 'DXF' entries in that table... the only common link between the two tables, though, is the 'DXF' entry. I'm stumped. Any suggestions? Thanks. --- Jon Wagoner [EMAIL PROTECTED] wrote: Change the query to: SELECT parcels.DXF as 'record', building.ADDRESS as 'results1', parcels.relname as 'results2' from parcels INNER JOIN building on parcels.DXF = building.DXF where parcels.relname LIKE '%jones%' order by parcels.relname desc; __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select from multiple tables
First, you need to perform a JOIN on the two tables, otherwise you will get all combinations of rows from the two tables. But that is not what you are asking about. Using SELECT * is considered a bad programming practice. Always specify the select list (the columns you want to see/need to work on) unless you are simply selecting data in an ad hoc query to review the data in a row or a few rows. Once you specify what columns to select, you can use column aliases, which allows you to give columns that have the same name in the two tables different names. In the query, prefix the column names by the table name - or better - a table alias. For example: SELECT D.ID, D.Name as DeptName, D.Manager as DeptManager, E.ID as EmpID, E.DeptID, E.Name as EmpName, E.HiringDate as EmpHireDate FROM departments as D INNER JOIN employees as E ON E.DeptID = D.ID WHERE D.CompanyID = 36 HTH, Tore. - Original Message - From: Frank de Bot [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 5:33 PM Subject: Select from multiple tables Hi, I got the following query: SELECT * FROM t1,t2 In the result I get as column just the column name only, but I like to get the table name with it, just I must use it in the where clause. How can I do this? Thanks in advanced, Frank de Bot - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with a putting a slash into a record in mysql...
Try prefixing the \ (the ***x escape character) with a \, i.e.: update test set f2 = 'c:\\temp' where f1 = 'location' HTH, Tore. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 5:52 PM Subject: Help with a putting a slash into a record in mysql... I have mysql installed on Red Hat 7.3. I have created a table like the example below: create table test(f1 char(30), f2 char(30)) Here is my problem I am trying to update this table with this: update test set f2 = 'c:\temp' where f1 = 'location' There is already a record with location in f1, but the problem seems to be that mysql doesn't like the \ in c:\temp Is there something in mysql that doesn't allow you to put a slash in a record??? All of the other database I have worked with don't have this issue.. Any help would be appreciated - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select based on related date
Assuming that you *don't* want ALL purchases made prior to the range, but only those of subjects not purchased recently, the following query appears to work: -- First gather info about recent purchases Create TEMPORARY Table recent SELECT B.id, B.date_purch, S.subj FROM subj as S INNER JOIN book as B ON S.book_id = B.id -- Last 6 months: WHERE B.date_purch = DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH); -- Then do an outer join with ALL purchases SELECT B.id, B.date_purch, S.subj FROM subj as S INNER JOIN book as B ON S.book_id = B.id LEFT OUTER JOIN recent as R ON R.subj = S.subj -- but list only those with no recent purchases: WHERE R.id IS NULL; If you only want the subjects returned (and once each), remove B.id and B.date_purch from the (2nd) SELECT list, and include a DISTINCT. As a MySQL newbie, I couldn't get it to work as a single statement which logically (AFAIK) should have returned the same data on 4.0 (returned no result on 4.0, gave error on 3.23): SELECT B.id, B.date_purch, S.subj FROM subj as S INNER JOIN book as B ON S.book_id = B.id LEFT OUTER JOIN (book as B2 INNER JOIN subj as S2 ON S2.book_id = B2.id AND B2.date_purch = DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH) ) ON S2.subj = S.subj WHERE B2.id IS NULL Is there a restriction, quirk, or bug in MySQL that prevents parenthesised joins from working properly? HTH, Tore. - Original Message - From: Jesse Sheidlower [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 24, 2003 8:09 PM Subject: Select based on related date I'm having trouble with a query that I thought would be pretty straightforward. To simplify, I have a database of books that has, say, two tables: CREATE TABLE book ( id INT, date_purch DATE ) CREATE TABLE subj ( book_id INT, subj TEXT ) Each book can have any number of subjects, and each book has at least one subject. I'd like to get all subjects that are in the subject table that are _not_ represented in a particular date range. For example, if I have bought books with subjects 'Computing', 'Cooking', 'Baseball', and 'Fiction', but in the last six months I have only bought Fiction and Baseball, I'd like a query that will give me Computing and Cooking. I played around with a few LEFT JOINS but I still seem to be missing something. Thanks. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query syntax help?
Try: SELECT FF.name AS thename, MAX(FF.label) AS thelabel, F.name AS fieldsname FROM regformfields as FF INNER JOIN regfields as F ON (FF.name = F.Name) WHERE FF.label != '' GROUP BY FF.name, F.name I don't think you can include the ORDER BY F.saveorder (another column) in this case, unless you include it (F.saveorder) in the SELECT and GROUP BY list. HTH, Tore. - Original Message - From: Scott Brown [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 22, 2003 1:57 PM Subject: Query syntax help? OK, I am having a bit of trouble designing a MySQL query that returns what I want. Here is the query as I have it thus far: SELECT DISTINCT regformfields.name AS thename, regformfields.label AS thelabel, regfields.name AS fieldsname FROM regformfields INNER JOIN regfields ON (regformfields.name = regfields.Name) WHERE regformfields.label != '' ORDER BY regfields.saveorder; In this particular query, there can be multiple occurrences of thename(can be filtered by DISTINCT), therefore multiple occurrences of thelabel (which can't be filtered by DISTINCT, as it is always different for the same thename), but fieldsname is always unique. I don't care which thename or which thelabel is returned, but I only want one (these two tables, together with some others, construct a schema for yet others...), i.e thename = 'email' may be returned twice in this result set, but I only want it to appear once. DISTINCT, as it is used here, does not return what I want, as thelabel will rarely, if ever, be distinct. The ideal query would force the DISTINCT to be related ONLY to thename, and return whatever thelabel it happens to grab, based on however it is indexing, which would be the first saveorder it stumbles upon. Any help would be appreciated! TIA, --Scott Brown - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Another Query Table Structure
When designing a database, think of it this way: The kinds of things that you need to keep information about would map to the first cut of what tables you need. So if you keep information about Companies and Cities, you need (at least) the tables Companies and Cities. Keep information (attributes) of companies in the Companies table, and information about the cities in the Cities table. The attributes (atomic information elements) gives you the first cut at what columns you need in each table. Establish simple (One to Many) relationships between the tables by creating a column in the related table that contains a copy of the primary key in the other table. For instance to represent that a Company is located in (relationship: located in) a certain City, add a column (for instance called CityID) in the Companies table. However, since some companies may exist in multiple cities, and multiple companies exists in each city, you probably want to represent a Many to Many relationship, which is implemented with an intermediary table - let's call it CompanyCities - and give it two columns: copies of the primary keys from Cities and Companies. This table gets populated with the combinations of the values of the Cities PK and the Companies PK representing what companies exist in which cities. For information that is specific to a company's location/city, you would most likely put this in the CompanyCities table as well. Using these simple principles will help you well on the way to a third normal form (3NF) database design. So you'll have the following design (sample): Cities: CityIDPK CityName County Population CityWebSite ...etc. Companies: CompanyIDPK CompanyName CorporateHQ CorporateWebSite ...etc. CompanyCities: CityIDPart of PK - References Cities(CityID) CompanyIDPart of PK - References Companies(CompanyID) Address Phone ...etc. To get a listing of Companies in a specific City, use a join something like this: SELECT CO.CompanyName, CO.CorporateWebSite, CC.Address, C.CityWebSite FROM Cities as C INNER JOIN CompanyCities as CC ON CC.CityID = C.CityID INNER JOIN Companies as CO ON CO.CompanyID = CC.CompanyID WHERE C.CityName = 'London' Hope this gives you something to work with. For more information about normalization, check out http://www.15seconds.com/issue/020522.htm HTH, Tore. - Original Message - From: Andrew Maynes [EMAIL PROTECTED] To: MySQL-Lista [EMAIL PROTECTED] Sent: Saturday, February 22, 2003 3:13 PM Subject: Another Query Table Structure This may be simple for most of you MySQL experts out there so a little help would be nice :) I have an index page that is using a Navigation system based on the ID from a table City: The link then goes a DIR for that City. http://www.theaddress.co.uk/City/ The navigation within this DIR is based on Traders for this City so my problem is? Do I create a table for each City (holding Companies) or is there a way to structure the MySQL DB that can hold all companies in the same table? So when the City is selected all the companies for that City will be listed, baring in mind that all the TraderID's are generic (the same as City) It's only the Company that is specific to City? Help greatly needed and appreciated Andrew - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: big table, slow queries...???
Unless your WHERE clause includes relationships for all the tables, you are dealing with a Cartesian product - that is always slow. And if you do have all the relationships included, this is still a monster query. Do you have the proper indexes defined? Do you really need to join these 5 tables in the query? Based on your description, it is hard to tell what you are really trying to do, and what would be the best way to do it. HTH, Tore. - Original Message - From: Ferhat BINGOL [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, February 22, 2003 9:21 AM Subject: big table, slow queries...??? Hi, I am new at this group. Nowadays I am using PHP/MySQL more than ever. I have a problem with making queries on my huge database (totally more than 20 million rows and 9 GB). The main problem is it is rather slow. For example I am making a query which is checking the 5 biggest tables for, from 2 where options to 20. I am using something like that; SELECT table1.field1, table1.field2, table1.field3, table2.field2, table3.field2, table4.field1, table5.field1, table5.field2 FROM table1, table2, table3, table4, table5 WHERE (table1.field1=table2.field1) AND (table3.field4 BETWEEN case1 AND case2) (Where statement is longer than this) and so on. I think you understand the structure I use for making queries. But it is too slow. I mean I was expecting to be faster. Can you advice me a different kind of query string? I read some articles about using JOIN, GROUP or so on. But I am little bit confused about it. What is the best way to make queries on big table? Thanks, FERHAT P.s : I am using MySQL 3.23 and PHP/Apache on a Windows XP machine. 386 MB RAM with Pentium3 350. Ferhat - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: big table, slow queries...???
Use EXPLAIN to determine what indexes are chosen in the different cases. AFAIK, version 4 will allow you to specify HINTs in the query, so you may be able to force the query to use certain indexes. There are a lot of factors that affect the execution and speed of queries, including (but not limited to) the database design, indexes defined, query design, how much of the database can be kept in memory (cached), how much other processing takes place on the same server, the speed of the processor(s) and disk subsystem, etc. Different database engines use different algorithms for their query optimization, and different servers/OS'es are good at different things. From previous posts on this list, I have heard that MySQL performs better on Linux than it does under Windows. Beyond this, you are not providing any useful information for determining whether there is anything that can be improved in your query or database design. Since we have no idea what speed or country means in terms of your database (what tables are they in, how large are those tables, are the columns indexed, what is the selectivity of those indexes), what indexes are used by your different queries, the exact nature of those queries, etc., anything else would be guesswork. HTH, Tore. - Original Message - From: Ferhat BINGOL [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Saturday, February 22, 2003 4:55 PM Subject: Re: big table, slow queries...??? Tore, Yes, it is necasarry to pick up all the values from 5 tables. I made indexes for each table. My queries are between 12 to 26 seconds now. I am generating a WHERE statement before make the query. If the user do not enter the some min and max values I am skipping this WHERE statements. The interesting thing is (for me) when I enter lots of limits for lots of input values query is faster. When I just select a country for example the query is slower. I mean lets say that speed field is between 10 and 40 and I want to chose a country. if I enter 10 and 40 for range and chose country it takes 12 seconds but if I do not enter speed range (as you see to enter or not is getting the same result) and choose a country it is 18 seconds. The main reason I asked this to the group is the same query was faster on interbase server. Than a question come to my mind is interbase server is faster in such huge queries? if so WHY? Cause on MySQL the simple queries are faster. As I said I am using MySQL 3.23 PHP Apache on WinXP. Does it help if I upgrade to MySQL 4.0 or is this come from XP... I am just trying to figure out if it is (much much) better to use Linux? TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie - How To Link Two Databases
First, it appears that you are confusing database with table. A Database is a collection of tables. Each table has rows of data which is organized in columns. From what I understand, you have the following situation: Table: Parcels Columns: dxf (Primary Key, auto_number) [Description - used for illustration below] ...etc. columns for data about the parcels Table: Buildings Columns: [BuildingID (Primary Key)] dxf FOREIGN KEY REFERENCES Parcels(dxf) Address ...etc. If you have the parcel id (the dxf) already, you can list all the buildings on it by simply: SELECT Address FROM Buildings WHERE dxf = value of dxf If you have some other information about the parcel that you want to use as the identifier: SELECT P.Description, B.Address FROM Parcels as P INNER JOIN Buildings a B ON P.dxf = B.dxf WHERE P.Description = 'some description' Or similarly, if you have the Address, and you want to show the Parcel Description: SELECT P.Description, B.Address FROM Parcels as P INNER JOIN Buildings a B ON P.dxf = B.dxf WHERE B.Address = 'some address' HTH, Tore. - Original Message - From: Diver8 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 22, 2003 7:17 PM Subject: Newbie - How To Link Two Databases Hi - I am new to MySQL SQL in general. I have been reading the documentation at www.mysql.com, but I've become quite confused. I hope that one of you can clear up a few issues for me. I have a database called parcel. The primary key in this database is called dxf, which contains a unique number corresponding to each record in the database. I have a second database called building. One of the columns in this database is called address. I need to be able to pull the data from this column when querying the parcel database. The problem I'm having is that while the building database does contain a column called dxf, in that database dxf isn't unique. To clarify, I may have multiple rows in the buildings database with the same entry for dxf. To clarify further... the parcel database contains information regarding land parcels. The building database contains information regarding buildings on these land parcels. dxf is the parcel id number. While you can only have one parcel of land, you may have multiple buildings on that parcel, each with a unique address. The parcel information is in the first database the address info is in the second database. In the first database, dxf is unique (as you can only have one land parcel). In the second database, dxf doesn't necessarily have to be unique as you may have multiple buildings on that one land parcel. I hope that made sense. The problem seems to be that, while each database contains a dxf entry, there really isn't a unique link between the two. Anyways... when I run a query on the parcel database, I need to be able to pull any address associated with that parcel from the building database. I can't quite figure out how to do this. Greatly appreciate any help! __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: From Win2000/IIS to Linux/Apache ?
Moving the database is childs play. I'm sure other tools allow you to do this, but when I recently started working with MySQL, I found PremiumSoft MySQL Studio (30 day trial, $78 to purchase) from http://www.mysqlstudio.com It is a pretty decent tool, and among other things includes a Backup/data transfer wizard that lets you very easily move a database from one server to another. Add to that, I designed my first MySQL database on MySQL version 4 on my own Win2k box, and transferred it to verion 3.23.xx on a Linux server across the web (just make sure port 3306 is open on the target server). Regards, Tore. - Original Message - From: Theisen, Gary [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 21, 2003 12:59 PM Subject: From Win2000/IIS to Linux/Apache ? Hi all, I have PHP MySQL installed on a Win2000/IIS system. I may be moving (due to requirements at work) to a Linux/Apache system in the near future. I'm wondering if it's a hassle, or even possible, to move the MySQL database from the windows/IIS box to the Linux/Apache box? Any corruption, or other problems associated with this type of move? Thanks! P.S. I also have the option of leaving Win2000 in place but switching to Apache service. Would this be worth it? In other words, would that setup be a better web server environment (for PHP MySQL) than the Win2000/IIS setup? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: decimal type
When you don't specify the precision for a DECIMAL column, it is assumed to be 0. This is documented in the previously posted link. create table pricelist (product varchar(45), cost dec(18, 2)) HTH, Tore. - Original Message - From: Bryan Koschmann - GKT [EMAIL PROTECTED] To: gerald_clark [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, February 21, 2003 4:00 PM Subject: Re: decimal type My Apologies, table name is pricelist with 2 colums: create table pricelist (product varchar(45), cost dec); then load data infile /home/omni/OmniPrice.csv into table pricelist fields terminated by ','; the datafile looks like this Jaton Modem,15.5 Teac Floppy,7.5 Celeron 1.7,54.5 Logitech KB,11 but a query returns this: mysql select * from pricelist; +-+--+ | product | cost | +-+--+ | Jaton Modem | 15 | | Teac Floppy |7 | | Celeron 1.7 | 54 | | Logitech KB | 11 | +-+--+ 4 rows in set (0.00 sec) I read the docs on the decimal type, but I dont quite understand it. Thanks, Bryan On Fri, 21 Feb 2003, gerald_clark wrote: |Show us. |We have no idea how you defined your tables, |loaded your data, or structured your query. | |Bryan Koschmann - GKT wrote: | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: decimal type
You are less likely to run into the issue when there are no calculations involved, but I wouldn't guarantee it. If you want to be on the safe side when using floating pont, do not check for EQUAL to, but within a range (+/- some small delta). As a rule of thumb, avoid floating point except for situations where you really need to represent very small fractions or extremely large numbers with less than 100% accuracy, or for real number math application (engineering, science). Regards, Tore. - Original Message - From: Stitchin' [EMAIL PROTECTED] To: Tore [EMAIL PROTECTED] Sent: Friday, February 21, 2003 5:35 PM Subject: RE: decimal type OH THANK YOU SO MUCH for that info!!! My first little database I created today in mySql, I used that floating stuff on two columns, but they are only to hold the height and width of my embroidery designs ... so I want to be able to enter 3.25 (as in inches) and 10.12 etc... I'm not planning on calculating with the fields, they're really just there for people to view and search on if their embroidery machine can't do a design bigger than 4 x 4 they can search and make sure those width and height columns don't exceed 4. So is my usage of float ok? Renee :) -Original Message- From: Tore [mailto:[EMAIL PROTECTED] Sent: Friday, February 21, 2003 5:29 PM To: mysql Subject: Re: decimal type Floating point should not be used for money or for representing any other discrete numeric values. You will get in trouble because the floating point value can deviate from the discrete value it is intended to represent. Floating point introduces (small) rounding errors. Say an account has a balance of $265.50 and the user wants to withdraw the entire amount, if your code checks that the withdrawal amount is less or equal to the balance, it MAY not allow the withdrawal. Depending on how the original value was established, you could see something like: In the table AccountBalances AccountID = 999 Balance = 265.49967(value for illustration purposes only) Application screen shows two decimals: Balance = $265.50 Customer withdrawal amount: $265.50 Query (or application logic with floating point values) that includes a balance check: UPDATE AccountBalances SET Balance = Balance - 265.50 WHERE AccountID = 999 AND Balance = 265.50 This will affect 0 rows, which your code is smart enough to determine meaning insufficient funds. So, the moral of this story is to use fixed precision numerical values to store information such as money... :- HTH, Tore. - Original Message - From: Stitchin' [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 21, 2003 5:00 PM Subject: RE: decimal type Why couldn't you use float(10,2) ... just an example ... where the first number in the parentheses is the total characters for the number and the second number represents how many of those are right of the decimal point?) I'm a TOTAL newbie to this stuff, I just set up my first mySql database today ... so I hope I'm not too far off base. Renee Stitchin' Up A Storm -Original Message- From: Bryan Koschmann - GKT [mailto:[EMAIL PROTECTED] Sent: Friday, February 21, 2003 4:01 PM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: decimal type My Apologies, table name is pricelist with 2 colums: create table pricelist (product varchar(45), cost dec); then load data infile /home/omni/OmniPrice.csv into table pricelist fields terminated by ','; the datafile looks like this Jaton Modem,15.5 Teac Floppy,7.5 Celeron 1.7,54.5 Logitech KB,11 but a query returns this: mysql select * from pricelist; +-+--+ | product | cost | +-+--+ | Jaton Modem | 15 | | Teac Floppy |7 | | Celeron 1.7 | 54 | | Logitech KB | 11 | +-+--+ 4 rows in set (0.00 sec) I read the docs on the decimal type, but I dont quite understand it. Thanks, Bryan On Fri, 21 Feb 2003, gerald_clark wrote: |Show us. |We have no idea how you defined your tables, |loaded your data, or structured your query. | |Bryan Koschmann - GKT wrote: | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try:
Re: INSERT ... SELECT - A slightly different question
The typical syntax for this would be: INSERT INTO phone (personID, phoneNr, email, type) SELECT personID, '1-xxx-xxx-', \N, 'OFF' FROM persons WHERE lastName='' HTH, Tore. - Original Message - From: Kyle Lange [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 20, 2003 6:59 PM Subject: INSERT ... SELECT - A slightly different question Hi all, I'm trying to load a 1:M table with n rows. I've been thru the manual and discussion list and each comes close but not quite. I'm trying to load a full table with 4 cols, but one of them needs to be a key to another table SELECTed enroute. 'LAST_INSERT_ID()' doesn't help because the main table has also been loaded with n rows. The 1st row of my statement looks like; INSERT INTO phone (personID, phoneNr, email, type) VALUES ((SELECT personID FROM persons WHERE lastName='), '1-xxx-xxx-', \N, 'OFF'), or INSERT INTO phone SET personID=(SELECT personID FROM persons WHERE lastName=''), phoneNr='1-xxx-xxx-', email=\N, type='OFF' or INSERT INTO phone (personID, phoneNr, email, type) SELECT personID FROM persons WHERE lastName='', '1-xxx-xxx-', \N, 'OFF', None of which seem to work. At this point the only alternative I can see is to multiple 'personID SELECTs each followed by an INSERT INTO phone. Or is there some fundamental SQL or mySQL concept I'm missing please? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Join or Group By?
What you are looking for is a report generator. SQL queries will return result sets in a table layout (since the fonts etc. messes up attempt at tabulat layout, I'm presenting the data as comma separated): (SeriesTitle, LinkTitle) 1, 1 1, 2 1, 3 2, 1 2, 2 3, 3 If the Series column in the Links table corresponds to the SeriesID column in the Series table, you could write a query like: SELECT S.SeriesTitle, L.LinkTitle FROM Series as S INNER JOIN Links as L ON S.SeriesID = L.Series ORDER BY S.SeriesID, L.LinkID HTH, Tore. - Original Message - From: Mike Walth [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 20, 2003 11:36 PM Subject: Join or Group By? Hello: I'm fairly new to MySQL and think there is an easy query to get what I need done, but not sure how to do it. I thank everyone for their help in advance. I have two tables. Table1: Series SeriesID SeriesTitle Table2: Links LinkID Series LinkTitle I need a query that I can group the links by Series to generate a result similar to: SeriesTitle(1) LinkTitle(1) LinkTitle(2) LinkTitle(3) SeriesTitle(2) LinkTitle(1) LinkTitle(2) LinkTitle(3) Links.Series holds the value of SeriesID that it is under. Thanks again for your help. Mike Walth CinoFusion - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
