good manner for handling multiple languages dynamic websites
Hello, I have, for the first time, a website to do in several languages. I'll do a variable replacement whereever needed, with several lang files (as I've seen so many times in open source PHP applications) but I am wondering how in sql I could do for translating items' names.. I explain : I have a site in english, where the user posts an ad, when he has to choose the color of his object, he chooses RED I will store RED,BLUE,GREEN,etc. in a table 'color' so I can build a dropdown list for the user to choose from well now, if a visitor browse the ads in french. I'd like the color to read 'rouge' not 'red'. How should I go about that, database items ? Cheers if you see what sort of problem I am facing right now. Damien, - 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: Charset and French Diacritic Characters
Hello Xavier, can't you do the transforming of your keywords before doing the query ? to transform all special characters in php it would be : $keywords = strtr($keywords,éè^éëàãâä,_); add other special characters and a JOKER sign in mysql '_' for each. Cheers, Damien - 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
How to get the sum of rows in 2 tables ?
Hello and hope that everybody on this list will pass a wonderful 2003 year, Here's my worry: I have 3 tables, one for genre, one for html and one for shockwave html and shockwave have a foreign key genreID I want to count for each genre how many html and shockwave entries have the same genreID, so I can have as a result : genreID ! nbgenre 1 ! 6 2 ! 2 3 ! 8 where for exemple genreID 1 has 4 html and 2 shockwave entries. Here's one query I've tested and that doesn't return the addition of html and shockwave entries, but their multiplication !!!?? (in the exemple I obtain for genbreID 1 = 4 * 2 = 8 instead of 6) $query = SELECT COUNT(*) as nbgenre, g.intitule, g.genreID FROM genre g, html h, shockwave s WHERE g.genreID = s.genreID AND g.genreID = h.genreID GROUP BY g.intitule, g.genreID ORDER BY nbgenre DESC, g.intitule I've tried using LEFT JOIN from exemples on the web, but I don't have enough experience in SQL to use them correctly in my own queries, and I am not sure it would solve the problem or if it's just another way of obtaining the same (wrong) result. Cheers, Damien - 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
COUNT() two tables
Hello, I have been unable to get a better result than the multiplication of 2 tables where I want the addition. I explain : 3 tables, one for genre, one for html and one for shockwave html and shockwave have foreign key genreID I want to count how many html and shockwave entries have the same genreID, so I can have as a result : genreID ! nbgenre 1 ! 6 2 ! 2 3 ! 8 where for exemple genreID 1 has 4 html and 2 shockwave entries. Here's one query I've tested and that doesn't return the addition of html and shockwave entries, but their multiplication !!!?? (in the exemple I obtain for genbreID 1 = 4 * 2 = 8 instead of 6) $query = SELECT COUNT(*) as nbgenre, g.intitule, g.genreID FROM genre g, html h, shockwave s WHERE g.genreID = s.genreID AND g.genreID = h.genreID GROUP BY g.intitule, g.genreID ORDER BY nbgenre DESC, g.intitule I've tried using LEFT JOIN from exemples on the web, but I don't have enough experience in SQL to use them correctly in my own queries, and I am not sure it would solve the problem or if it's just another way of obtaining the same (wrong) result. Cheers, Damien - 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: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
Hello, I've tried your way, but my SQL reports error, that I have specified more than ONE primary key, so I can't do the primary key on both fields. Is that a mySQL 3.X limitation ? My tables are ISAM. Following your advice, I've deleted the relation_person_carID, so only cardID and personID are left i nthe table, with no primary key. Is it a good idea, and is it safe, not to have a primary key ? Cheers, Damien COLA -Message d'origine- 3) table relation_person_car personID int 11 carID int 11 PRIMARY KEY (personID,carID) something like : table relation_person_car relation_personID primary int 11 autoincrement personID int 11 carID int 11 - 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: BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
Hello, you're right, creating the table with 2 primary keys works fine. What I was doing is try to convert my personID and carID fields into primary, and it says: Error MySQL : Invalid SQL: ALTER TABLE `varmalinalliax`.`tgl_rel_rest_card` CHANGE `restID` `restID` INT (11) DEFAULT '0' NOT NULL , CHANGE `cardID` `cardID` INT (11) DEFAULT '0' NOT NULL , ADD PRIMARY KEY(`restID`), ADD PRIMARY KEY(`cardID`) Error My SQL number: 1068 (Multiple primary key defined) What simple query could I make to transform the fields in primary ? I don't have access to the mysql server via command line, I use a php package (eSKUeL, it's like phpMyAdmin) Cheers, Damien COLA -Message d'origine- Could you post your create table statement? It should be something like: CREATE TABLE relation_person_car( personID int 11 NOT NULL, carID int 11 NOT NULL, PRIMARY KEY (personID,carID) ); - 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
Sorting UK Postcodes (WAS Sorting Results)
Hi, What about if you want to sort UK Postcodes ? They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? Cheers, Damien COLA - 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: Sorting UK Postcodes (WAS Sorting Results)
Sorry, I forgot to say that postcode can be one or 2 letters in front of the numbers. -Message d'origine- They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? - 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: Sorting UK Postcodes (WAS Sorting Results)
Thank you and Yes, separating the postcodes into field letters and numbers may be the only way to be able to order a sequence like that: CV1,CV2,..,CV10,.. It would also work since there could as many letters as needed Anyone has a simple query for odering sequences correctly ? Cheers, Damien COLA -Message d'origine- Could you seperate the E's in one column and the numbers in another... - 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: Sorting UK Postcodes (WAS Sorting Results)
Hi, yes, that would do, except that I don't know how many letters are in front of the numbers. And since they're not the same letters perhaps ORDER by 1,2 would have done it perfectly, if only we could substring() intelligently first. Cheers, Damien COLA -Message d'origine- SQL: select substring(code, 1, 1) as letter, substring(code, 2, 2) + 0 as number from epost order by 2 - 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: Sorting UK Postcodes (WAS Sorting Results)
Sorting by number is not important, it was more a general question about sequences of variousnameXXX and how to order them correctly with a simple query. Splitting in 2 fields seems the only way then. For the postcode it's just to provide the user with an easy lookup table, but it's not *that* important since the way it's ordered now is the same as many (if not all) file systems do. If sorting by number is important, then split them on input and store them in two separate files. But I don't really know why you'd want to sort them numerically anyway - UK postcodes aren't sequential in operation: E1 is not necessarily adjacent to E2 on the map. So having a sequence of E1,E10,E2, etc is no less applicable than E1,E2,E10 would be. - 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
BEGINNER: in mysql, better to do 1 table for one relationship, even with a one to one relation ?
The way I've always done it, and I wonder if I am not doing lots of things wrong: i have 2 tables, one person and one address for a one to one relation, the way I do it: table person personID primary int 11 autoincrement addressID int 11 table address addressID primary int 11 autoincrement if it's a one ot many relationship i do it like that: table person personID primary int 11 autoincrement table car addressID primary int 11 autoincrement personID int 11 NOW, reading this great mailinglist, I've come to believe the right way to do it that works in both relationship types : table person personID primary int 11 autoincrement table car carID primary int 11 autoincrement table relation_person_car relation_personID primary int 11 autoincrement personID int 11 carID int 11 I understand it's closer to the real way to represent relations in database design, but I wonder what else can be done with mySQL to simplify the work. for example, should I make something special with the type of fields personID and addressID in table relation_person_car ? another example, it would be easier to name all the primary key fields 'id' instead of '*nameOfTheTable*ID', but what would be the backdraws when coding queries and server scripts ? Any critics on my beginner way of handling relations is most welcome. Cheers, Damien COLA Cordialement, __ Alliax ~CV : http://LingoParadise.com/cv.php Un site pour Toulon : http://www.ToulonParadise.com Un site pour Renaud : http://www.rfaucilhon.com Un site pour Director : http://www.LingoParadise.com Un site pour Harmonica: http://www.LingoParadise.com/mp3 -Message d'origine- De : Michael T. Babcock [mailto:[EMAIL PROTECTED]] Envoyé : lundi 9 décembre 2002 15:15 À : [EMAIL PROTECTED] Objet : Re: QUICK: What is the optimal way to store opening times ? On Fri, Dec 06, 2002 at 05:54:53PM +0100, Alliax wrote: I have one question : if I go the RestTimes route, that is having a row per day and so 7 row per restaurants : can I, in one SQL request, know if THAT restaurant is open or close now ? I now there are NOW() function in SQL and probably many others, but I am not at ease to use them since I have pretty basic SQL skills. This should be a personal work assignment for you, but try: SELECT * FROM RestTimes WHERE RestID = ... AND OpenTime now() and CloseTime now(); FWIW, you'll have to do a calculation in there such that OpenTime is midnight today + seconds from day offset. Its not difficult; find some calendaring code for examples. -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - 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: QUICK: What is the optimal way to store opening times ?
Hello, yes I've thought of using less fields, separated by commas, but I'd like to use much more SQL queries than PHP to process the result. I have one question : if I go the RestTimes route, that is having a row per day and so 7 row per restaurants : can I, in one SQL request, know if THAT restaurant is open or close now ? I now there are NOW() function in SQL and probably many others, but I am not at ease to use them since I have pretty basic SQL skills. How can I make a query that would return as well an extra field with a boolean flag (open/close) ? Cheers, Damien -Message d'origine- Have you thought about storing just M/T/W/etc and then storing the Opening, Closing times as a comma delimited string? Maybe like Monday: 11AM,1PM,5PM,1AM Tuesday 10AM,1PM,6PM,12AM You'll only need 7 fields. - 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
QUICK: What is the optimal way to store opening times ?
Hello, I have a table of restaurants, I want to store opening times so I can display in real time if the restaurant is open or not. What is the optimal way to store the times ? Since there is no common denominator for opening times, I thought of having another table with 28 (!!) fields. I'm doubtful this is optimal. The 28 fields would be of type INT : Monday-morning-start Monday-morning-end Monday-afternoon-start Monday-afternoon-end Tuesday-morning-start Tuesday-morning-end Tuesday-afternoon-start Tuesday-afternoon-end etc. and in my PHP page I would determine what day of the week we are and if it's morning or afternoon, and then I would query the database to get the start and end times and verify if we're within the boundaries. Cheers, Damien COLA - 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
Limit and Order by
I am not sure I understand your question, I thought the line you showed would give proper results (100 total-ordered lines) but maybe : So there is a similar thread running now, Re: SQL Select Idea [ORDER BY] by Michael, he says creating a temporary table is the way to go : create temporary table top100 select * from mytable limit 100; select * from top100 order by total desc; I don't know if you need to erase the temporary table or if it's automatic once the query has been processed, but I wonder if there is another way to do that in one command.. anyone? -Message d'origine- How can I limit the result after the order by has been executed? This stops efter 100 rows and the result is not as I intended... Select * from mytable ORDER by total desc limit 0, 100 Must this be done in 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: QUICK: What is the optimal way to store opening times ?
Thank you for this and for the previous answer about create temporary table, your RestTimes is a nice way to do it, but it multiplies the number of rows of the table. So you say it's better to have more rows than have less row, when doing simple select query? I'll do it your way then, except if somebody has a better idea ? -Message d'origine- As opposed to something like: RestTimes ( id ..., restaurantid ..., weekday tinyint unsigned, open ..., close ..., label varchar ); - 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
A question about mySQL scalability
Hello, I am new to the list, I've subscribed in last ressort when I couldn't find an answer, neither the web or the list archives... I've had a question from a client that I've answered in this way : One other question, when you have built the database, will it be expandable to the size for 10,000 (an estimate) houses etc.? Yes, mySQL prior to 3.23 handles table of a maximum of 4GB, now it's as large as 20GB depending on the file system used. each row in table house for instance will be way less than 5KB and 5KB * 10 000 is only about 50MB (1/80 of the former maximum limit) Do you think I am right trusting mySQL on this one ? What is the upper limit database wide ? I know it depends on the disk space and the CPU and probably the file system.. But what sort of figures do you have in mind when answering this type of question about mySQL ? Cheers, Damien - 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
