Re: Запрос
Hello Johan, Sorry guys, that's just because I've seen familiar letters :-). the topic starter asked how he can "quit from this application". and I asked, from which application he wants to quit. That's all :-). -- With best regards from Ukraine, Andre Skype: Francophile My blog: http://oire.org/menelion (mostly in Russian) Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion Original message From: Johan De Meersman To: Виктор Ефимович Date created: , 1:40:35 PM Subject: Запрос Я предлагаю более отчетливо английски применения :-p - Original Message - > From: "Andre Polykanine" > To: "Виктор Ефимович" > Cc: mysql@lists.mysql.com > Sent: Thursday, 28 April, 2011 12:04:01 PM > Subject: Re: Запрос > > Hello Виктор, > > Из какого приложения?) > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Запрос
Hello Виктор, Из какого приложения?) -- With best regards from Ukraine, Andre Skype: Francophile My blog: http://oire.org/menelion (mostly in Russian) Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion Original message From: Виктор Ефимович To: mysql@lists.mysql.com Date created: , 12:01:41 PM Subject: Запрос Подскажите по чему не устанавливается или какие действия надо сделать чтобы выйти из этого приложения. -- Vitek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=an...@oire.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
Hello Halбsz, Aha. So, I should write SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId` instead of my original WHERE clause? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile My blog: http://oire.org/menelion (mostly in Russian) Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion Original message From: Halбsz Sбndor To: Andre Polykanine Date created: , 7:00:03 AM Subject: LEFT JOIN and WHERE: identical or not and what is better?, etc. >>>> 2011/04/25 17:42 +0300, Andre Polykanine >>>> Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? <<<<<<<< >>>> 2011/04/25 10:16 -0500, Johnny Withers >>>> The only difference once MySQL parses these two queries is the first one is a LEFT JOIN, which will produce all records from the blogs table even if there is no matching record in the users table. The second query produces an INNER JOIN which means only rows with matching records in both tables will be returned. ... I prefer to write the INNER JOIN out though because it leaves my WHERE clause to do filtering. <<<<<<<< and it is usual to write all about the joining in the FROM-clause --the tables and the criterion for joining them-- and reserve the WHERE-clause for filtering the result: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` That is, if you already had a table with the joined outcome, you would use the WHERE-clause to determine what of it enters into further processing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
LEFT JOIN and WHERE: identical or not and what is better?
Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Changing the timezone
Hi everyone, since I'm using the shared hosting, I can't change the default timezone for MySql. Question is: is there any query that I could launch in my connect.php before other queries to make my timezone change? For instance, I make a mysql_query("SET CHARACTER_SET_DATABASE='utf8'") or die ("Unable to change database charset: ".mysql_error()); and a mysql_query("SET NAMES 'utf8'") or die ("Unable to set names: ".mysql_error()); Maybe is there a way to change my timezone to Europe/Kiev? Thank you! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Insert only if the entry doesn't exist
Hello Rolando, So if I do "INSERT IGNORE INTO `Votes` SET `EntryId`='12345', UserId`='789'"; it *won't* insert the second row if there's a row with EntryId set to 12345 and UserId set to 789? Thanks and sorry!) -- With best regards from Ukraine, Andre Skype: Francophile My blog: http://oire.org/menelion (mostly in Russian) Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion Original message ---- From: Rolando Edwards To: Andre Polykanine Date created: , 11:01:40 PM Subject: Insert only if the entry doesn't exist If the table has Primary and/or UNIQUE Keys, then you are fine. You do not need to know what they are. If you want to see them do this: SHOW CREATE TABLE Votes\G INSERT IGNORE INTO does not require ON DUPLICATE KEY options. After all, you said earlier that you want to do nothing if the row exists. Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Andre Polykanine [mailto:an...@oire.org] Sent: Monday, February 14, 2011 3:40 PM To: Rolando Edwards Cc: João Cândido de Souza Neto; mysql@lists.mysql.com Subject: Re: Insert only if the entry doesn't exist Hello Rolando, Sorry, but if I do INSERT IGNORE INTO, then I must indicate a key (typically a unique key or a primary key), or is it false? But I don't know that key and no way to get it without more queries... -- With best regards from Ukraine, Andre Skype: Francophile My blog: http://oire.org/menelion (mostly in Russian) Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion Original message From: Rolando Edwards To: João Cândido de Souza Neto Date created: , 10:33:05 PM Subject: Insert only if the entry doesn't exist Be Careful. REPLACE INTO mechanically does DELETE and INSERT under mysqld's hood. If you want to do nothing if row exists already then do: INSERT IGNORE instead of REPLACE INTO Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, February 14, 2011 1:39 PM To: mysql@lists.mysql.com Subject: Re: Insert only if the entry doesn't exist Instead of "insert into" you can use "replace into". -- João Cândido de Souza Neto "Andre Polykanine" escreveu na mensagem news:1621362474.20110214201...@oire.org... Hi all, Thanks for your fast answer to my last question! Here's one more problem I commonly deal with. There are cases when I need to insert the row only if such a row doesn't exist, otherwise I need either to update the row or to do nothing, just skip the query. The common case is the rating increment/decrement. I have the + and - links which are AJAX. So I need to prevent fast multiple clicking and over-increasing or over-decreasing the rating of the blog entry. Here's what I'm doing, in a simplified form (assume all of the numbers are PHP variables): "SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789'"; then I'm checking if the user has already clicked a link: if (mysql_num_rows()==0) { "INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789'"; } else { // do nothing } And here is the question (at last!): How can I MySql'ly check whether a vote does exist and if it does, then insert the row, else either do nothing or update several columns? I assume I can't use ON DUPLICATE KEY UPDATE because I have no unique keys... Hope you understood my problem). Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=an...@oire.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Insert only if the entry doesn't exist
Hello Rolando, Sorry, but if I do INSERT IGNORE INTO, then I must indicate a key (typically a unique key or a primary key), or is it false? But I don't know that key and no way to get it without more queries... -- With best regards from Ukraine, Andre Skype: Francophile My blog: http://oire.org/menelion (mostly in Russian) Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion Original message From: Rolando Edwards To: João Cândido de Souza Neto Date created: , 10:33:05 PM Subject: Insert only if the entry doesn't exist Be Careful. REPLACE INTO mechanically does DELETE and INSERT under mysqld's hood. If you want to do nothing if row exists already then do: INSERT IGNORE instead of REPLACE INTO Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, February 14, 2011 1:39 PM To: mysql@lists.mysql.com Subject: Re: Insert only if the entry doesn't exist Instead of "insert into" you can use "replace into". -- João Cândido de Souza Neto "Andre Polykanine" escreveu na mensagem news:1621362474.20110214201...@oire.org... Hi all, Thanks for your fast answer to my last question! Here's one more problem I commonly deal with. There are cases when I need to insert the row only if such a row doesn't exist, otherwise I need either to update the row or to do nothing, just skip the query. The common case is the rating increment/decrement. I have the + and - links which are AJAX. So I need to prevent fast multiple clicking and over-increasing or over-decreasing the rating of the blog entry. Here's what I'm doing, in a simplified form (assume all of the numbers are PHP variables): "SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789'"; then I'm checking if the user has already clicked a link: if (mysql_num_rows()==0) { "INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789'"; } else { // do nothing } And here is the question (at last!): How can I MySql'ly check whether a vote does exist and if it does, then insert the row, else either do nothing or update several columns? I assume I can't use ON DUPLICATE KEY UPDATE because I have no unique keys... Hope you understood my problem). Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=an...@oire.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Insert only if the entry doesn't exist
Hi all, Thanks for your fast answer to my last question! Here's one more problem I commonly deal with. There are cases when I need to insert the row only if such a row doesn't exist, otherwise I need either to update the row or to do nothing, just skip the query. The common case is the rating increment/decrement. I have the + and - links which are AJAX. So I need to prevent fast multiple clicking and over-increasing or over-decreasing the rating of the blog entry. Here's what I'm doing, in a simplified form (assume all of the numbers are PHP variables): "SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789'"; then I'm checking if the user has already clicked a link: if (mysql_num_rows()==0) { "INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789'"; } else { // do nothing } And here is the question (at last!): How can I MySql'ly check whether a vote does exist and if it does, then insert the row, else either do nothing or update several columns? I assume I can't use ON DUPLICATE KEY UPDATE because I have no unique keys... Hope you understood my problem). Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Simplify 4 queries into 1
Hi all, Hope this question is appropriate here :-). I've got 4 queries: $q1=mysql_query("SELECT *FROM`CandidateQuestions`WHERE `Category`='1' ORDER BY RAND() LIMIT 1"); $q2=mysql_query("SELECT *FROM`CandidateQuestions`WHERE `Category`='2' ORDER BY RAND() LIMIT 1"); $q3=mysql_query("SELECT *FROM`CandidateQuestions`WHERE `Category`='3' ORDER BY RAND() LIMIT 1"); $q4=mysql_query("SELECT *FROM`CandidateQuestions`WHERE `Category`='4' ORDER BY RAND() LIMIT 1"); What I need is to select exactly 4 questions so that each of them would be randomly selected from each category. Note: I don't need 4 *any* questions, I need 1 question from the 1st category, 1 question from the 2nd category etc. Is there a way to make one query for this? If I do "SELECT * FROM `CandidateQuestions` WHERE `Category` IN (1,2,3,4) GROUP BY `Category` LIMIT 4", I think i would get 4 random questions, wouldn't I? Thanks in advance! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org