Check all databases health
Hi, I am getting a lot of these ones after a power outage. I have a huge amount of tables and databases on the server. Is there any automated way to check the health of ALL databases and ALL tables? And maybe some automated way to repair if problems? Got error: 1016: Can't open file: 'guestbook.MYI' (errno: 145) when using LOCK TABLES Best regards, Best regards, Peter Lauri http://www.dwsasia.com/ www.dwsasia.com - company web site http://www.lauri.se/ www.lauri.se - personal web site http://www.carbonfree.org.uk/ www.carbonfree.org.uk - become Carbon Free
RE: Complete newbie (OSX Server - MySQL)
Do you have shell access to this server? Try to access MySQL by using command: mysql and then do: show databases; You could also try to use root as username and leave the password empty. If it works then it would mean that your MySQL is not setup with any security or anything. Did you create any database before you started the installation of the script? There might be required that you create the table site and the user site before you do anything. Just some thoughts... /Peter -Original Message- From: Scott Yamahata [mailto:[EMAIL PROTECTED] Sent: Sunday, December 24, 2006 4:29 AM To: mysql@lists.mysql.com Subject: Complete newbie (OSX Server - MySQL) Complete newbie here. Purchased software for my OSX Server 10.4.8 to use with MySQL and PHP. I get an installation window that opens and asks for Host: (with a default of localhost). User: site Password: (the one the person who sold me the script provided). Database: site License key: (the one the person who sold me the script provided). I hit the save button and get the following: Warning: mysql_connect(): Access denied for user 'site'@'localhost' (using password: YES) in /Library/WebServer/Documents/!install/install.php on line 298 Could not connect to the Database Questions: 1. I don't know if I have to do anything to MySQL to add the User and Database or whether the scripts supposed to do it for me. 2. I don't know if I'm supposed to use localhost or greenheartworld.com, which was the domain that I supplied to him (but want to change). Any help is greatly appreciated. Thanks, Scott _ Get FREE Web site and company branded e-mail from Microsoft Office Live http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SUM() of 1 and NULL is 1 ?
IF(SUM(IF(Jan IS NULL, 0, Jan))0, SUM(IF(Jan IS NULL, 0, Jan)), NULL) This was just a guess :) -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 06, 2006 11:58 AM To: mysql@lists.mysql.com Subject: SUM() of 1 and NULL is 1 ? Hi List, I need to SUM() on months from a table like: CREATE TABLE `data` ( `Jan` float default NULL, ... ) ENGINE=MyISAM; # V 5.0.15 Months may have NULL values, like: INSERT INTO data (Jan) VALUES (1), (NULL); However, when I use SELECT SUM(Jan) AS Jan, the returned value is 1 in stead of NULL. How to get a result NULL when in such a case ? TIA, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to delete all rows....
DELETE FROM table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 10:35 AM To: mysql@lists.mysql.com Subject: How to delete all rows Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to delete all rows....
Sorry, did not read carefully. Either you loop thru all tables an do DELETE FROM table Or as someone else suggested, dump the structure, drop database, recreate from dump. /Peter -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 2:56 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: How to delete all rows DELETE FROM table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 10:35 AM To: mysql@lists.mysql.com Subject: How to delete all rows Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to sort last n entries?
SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 3:41 PM To: mysql@lists.mysql.com Subject: How to sort last n entries? I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? Thanks for your help Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: How to sort last n entries?
Assuming your MySQL version supports sub queries you do like this. I have never done sub queries my self, but I know the theory :) SELECT * FROM table WHERE id = (SELECT id FROM table ORDER BY id DESC) ORDER BY date /Peter Lauri www.lauri.se - personal www.dwsasia.com - company (Web Development Bangkok Thailand) -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 3:41 PM To: mysql@lists.mysql.com Subject: How to sort last n entries? I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? Thanks for your help Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to sort last n entries?
And if your MySQL version does NOT support sub queries you can probably just create a temporary table and then sort that one. /Peter -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 4:28 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: FW: How to sort last n entries? Assuming your MySQL version supports sub queries you do like this. I have never done sub queries my self, but I know the theory :) SELECT * FROM table WHERE id = (SELECT id FROM table ORDER BY id DESC) ORDER BY date /Peter Lauri www.lauri.se - personal www.dwsasia.com - company (Web Development Bangkok Thailand) -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 3:41 PM To: mysql@lists.mysql.com Subject: How to sort last n entries? I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? Thanks for your help Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to sort last n entries?
You are correct. So that maybe leaves you with a temporary table then :) -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 4:45 PM To: mysql@lists.mysql.com Subject: Re: How to sort last n entries? Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to sort last n entries?
CREATE TEMPORARY TABLE tabletemp SELECT * FROM table ORDER BY id DESC LIMIT 30; SELECT * FROM tabletemp ORDER BY date; -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 4:45 PM To: mysql@lists.mysql.com Subject: Re: How to sort last n entries? Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to find the top most member in a hierarchy of subcategories
MySQL is not recursive. This might help you: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html /Peter www.lauri.se - personal web site www.dwsasia.com - corporate web site -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of abhishek jain Sent: Monday, September 04, 2006 4:29 PM To: mysql@lists.mysql.com Subject: How to find the top most member in a hierarchy of subcategories Hi, I have a table structure like : ID , NAME, PARENT_ID 1 , Top , 0 2 , Level 1 , 1 3 , Level 2 ,2 4 , Another Top , 0 and so on. I wanted to know the topmost cat. if i have the lowest category id ie. 3 in this case. I wanted to get like 3-2-1 Pl. help me , cn i do this in one query, also i do not know how many sublevels are there, Thanks, Abhishek jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to find the top most member in a hierarchy of subcategories
Yes, and this shows that you can not do it will MySQL purely :) But a
scripting language like php can do it for you with a recursive function as
the best option.
/Peter
-Original Message-
From: João Cândido de Souza Neto [mailto:[EMAIL PROTECTED]
Sent: Monday, September 04, 2006 7:55 PM
To: mysql@lists.mysql.com
Subject: Re: How to find the top most member in a hierarchy of subcategories
I´m not quite sure if it could help you, because it´s whole in portuguese,
but i´ll send you.
My table is called categoria and has the follow structure:
CREATE TABLE `categoria` (
`id` int(20) NOT NULL auto_increment,
`cat_id` int(20) NOT NULL default '0',
`nome` varchar(50) NOT NULL default '',
`icone` varchar(255) NOT NULL default '',
`cod_shop` varchar(5) NOT NULL default '',
`topo` char(1) NOT NULL default '',
PRIMARY KEY (`id`)
)
Where the relations each register of the tree are like this:
ID , CATEGORIA, CAT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0
And then i use the follow routine to get the tree:
$sql = SELECT
*
FROM
categoria
ORDER BY
cat_id, nome;
$con-Query($sql);
$counter = $con-count;
$categorias=array();
for($z=0;$z$con-count;$z++){
$con-Seek($z);
list($id_cat, $cat_id, $nome_cat, $icone_cat) = $con-result;
$categorias[$id_cat]=array(id_cat = $id_cat, cat_id = $cat_id,
nome_cat = $nome_cat, icone_cat = $icone_cat, familia = $id_cat, );
$continua=$cat_id!=null $cat_id0;
if ($continua) {
$qual=$cat_id;
$categorias[$id_cat][indice]=;
while ($continua) {
$categorias[$id_cat][indice]=$categorias[$qual][nome_cat].$categorias[$i
d_cat][indice];
$continua=$categorias[$qual][cat_id]!=null
$categorias[$qual][cat_id]0;
$tem_pai=$cat_id!=null $cat_id0;
if ($tem_pai) $pai=$cat_id;
while ($tem_pai) {
if (!strpos($categorias[$pai][familia],,
.$categorias[$id_cat][id_cat]))
$categorias[$pai][familia].=, .$categorias[$id_cat][id_cat];
$tem_pai=$categorias[$pai][cat_id]!=null
$categorias[$pai][cat_id]0;
$pai=$categorias[$pai][cat_id];
}
if ($continua) {
$qual=$categorias[$qual][cat_id];
}
}
}
}
reset($categorias);
$linhas=array();
foreach ($categorias as $categoria) {
$linhas[$categoria[indice].$categoria[nome_cat]]=array(id_cat =
$categoria[id_cat], nome_cat = $categoria[nome_cat], icone_cat =
$categoria[icone_cat], indice = $categoria[indice], familia =
$categoria[familia]);
}
ksort($linhas);
reset($linhas);
Hope help you.
--
João Cândido de Souza Neto
Curitiba Online
[EMAIL PROTECTED]
(41) 3324-2294 (41) 9985-6894
http://www.curitibaonline.com.br
abhishek jain [EMAIL PROTECTED] escreveu na mensagem
news:[EMAIL PROTECTED]
Hi,
I have a table structure like :
ID , NAME, PARENT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0
and so on.
I wanted to know the topmost cat. if i have the lowest category id ie. 3
in
this case.
I wanted to get like 3-2-1
Pl. help me , cn i do this in one query, also i do not know how many
sublevels are there,
Thanks,
Abhishek jain
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ALTER TABLE
Hi, I am doing this thru the phpmyadmin interface: ALTER TABLE doc ALTER docts SET DEFAULT CURRENT_TIMESTAMP However, it returns #1064 - You have an error in your SQL syntax near 'DEFAULTCURRENT_TIMESTAMP' at line 1 As you can see the error shows that DEFAULT an CURRENT_TIMESTAMP has been written together, strange, or is my syntax in the ALTER TABLE wrong? /Peter
Not sure about performance, or am I?
Hey, I have this query: SELECT team. * , COUNT(*) - IF(team_id IS NULL, 1, 0) AS numberofmember FROM team LEFT JOIN teammember ON ( team.id = teammember.team_id ) WHERE CONCAT( team.name, team.description ) LIKE '% %' AND team.status =1 AND team.inviteonly =0 GROUP BY team.id ORDER BY numberofmember DESC This works fine, but it feels like it is not optimal. I have index on team.id and teammember.team_id, so that is ok I believe. But it feels I should do the count thing separately. However, I can not do that because my system does not support sub queries. Would it be better to first just list all team and then make a new query that counts the number of members? And if you ask why I am doing the COUNT(*) - IF(team_id IS NULL, 1, 0) it is because I want to count the number of members, but if there are no members the row count will still be 1 but with NULL in the teammember fields. So if there is NULL there I know there are no members, so I need to subtract 1 from them so the result is 0. Maybe that is also slowing down the query. I have to point out that right now I do not have any performance issue, this is just theory that I am thinking about, I want to build a system that can be robust and not needs to be redeveloped. Best regards, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HELP!
Hi, I did something terrible similar to UPDATE table SET testdate=NOW() And I kind of forgot the WHERE lalalala, so now all my records are screwed. Is there any way of actually undoing this? :)
RE: HELP!
Don't have any recent, or actually I do not know, because I am not in charge of the hosting part of this, only access to upload scripts and control MySQL via phpMyAdmin. :( -Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 22, 2006 3:33 PM To: mysql@lists.mysql.com Subject: Re: HELP! On Tuesday 22 August 2006 10:29, Peter Lauri wrote: Hi, I did something terrible similar to UPDATE table SET testdate=NOW() And I kind of forgot the WHERE lalalala, so now all my records are screwed. Is there any way of actually undoing this? :) Backup? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 to many relationship
This was very good reading. And we all learn something new everyday. What you are writing makes so much sense. This also comes from me relying on that all things said on this list is true, and that all members do know what they write before they write it. I try to never post a reply to anyone unless I'm convinced that I am right. So I learned a lesson. The big lesson learned was not maybe the one about how a database optimizes a question, but rather something else. /Peter -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, August 18, 2006 1:14 AM To: mysql@lists.mysql.com Cc: Chris; Peter Lauri Subject: Re: 1 to many relationship Peter Lauri wrote: Is there not a better way to do that? What will happen there is that a large result set will be created because when you just do select * from customers c, issues i, customer_issues ci it will be like the inner product from all these tables, and then just choosing the right ones. If the table C have 1000 records, issues 5000 and customer_issues 15000 you would end up with a 75,000,000,000 rows large results set, that would not be so sweet, would it? Peter Lauri wrote: Yes, it cuts it down to that number of records in the end, so the final result set will just be a few rows that match the 'WHERE'. But the internal process of MySQL do merge all tables and then chooses the records that matches the 'WHERE' clause. No database would work very well if that were the case. You are essentially asserting that mysql has no optimizer. That just isn't so. The job of the optimizer is to devise a plan to execute the query in a way that will examine the fewest possible rows. Eliminating rows before looking at them is always preferable to eliminating them afterwards. For example, given the query SELECT * FROM customers c JOIN customer_issues ci ON c.customerid = ci.customerid JOIN issues i on ci.issueid = i.issueid WHERE c.customerid = 13; mysql will use the index on customers.customerid to select the 1 row with customerid = 13, it will then use the index on customer_issues.customerid to find matching rows in customer_issues, then finally it will use the index on issues.issueid to find matching rows in issues. This is easily verified using EXPLAIN: EXPLAIN SELECT * FROM customers c JOIN customer_issues ci ON c.customerid = ci.customerid JOIN issues i on ci.issueid = i.issueid WHERE c.customerid = 13; +-+---++-+-+--+ | select_type | table | type | key | ref | rows | +-+---++-+-+--+ | SIMPLE | c | const | PRIMARY | const |1 | | SIMPLE | ci| ref| PRIMARY | const |4 | | SIMPLE | i | eq_ref | PRIMARY | test.ci.issueid |1 | +-+---++-+-+--+ 3 rows in set (0.01 sec) (Note that I've pared the output of EXPLAIN down to a few relevant columns.) The rows column tells the story. Mysql plans to use the primary key to find the 1 matching row in customers, then use the primary key to find the 4 matching rows in customer_issues for that 1 customer, then use the primary key to find the 1 matching row in issues for each row found in customer_issues. That is, it expects to produce 1x4x1 = 4 rows, not 'size of c' x 'size of ci' x 'size of i' rows! You might want to read the optimization section of the manual for more on the subject http://dev.mysql.com/doc/refman/4.1/en/optimization.html. Chris wrote: I don't know enough about mysql internals to debate that so I'll take your word for it. 'Explain' doesn't give enough information about what happens behind the scenes so I'm not sure how to prove/disprove that and I don't know of any tools that would show you that (if there is let me know!). Having said all of that I've never had a problem doing it the way I mentioned.. ;) EXPLAIN is documented in the manual http://dev.mysql.com/doc/refman/4.1/en/explain.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using Header to post data to another site
First of all, I think your post is intended to the PHP mailing list, this is the MySQL list. Assuming that you need to send information to the other web site without actually entering it, you could setup a system with a simple Web Service running on your receiving web server, and just call it from your sending web server. Search on Google for NuSOAP and you will probably find what you need, if you like that idea. /Peter -Original Message- From: Dirk Poot [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 16, 2006 9:34 PM To: mysql@lists.mysql.com Subject: Using Header to post data to another site Hi all, I am working on a problem where I need to select data from my own database, and then post it to another website (using the Get) method. After having wasted about 4 days trying http_request, $_Get and whatnot, I stumbled upon the header command. So far, the only way I have been able to actually post data to the other site is through this header command: header (Location:$url); This has the drawback that the user gets to see the URL I am sending him to, because it contains a password. I have tried to omit 'Location', and although it doesn't generate an error, the info also doesn't reach the intended website. I hope there is a smarter way to have PHP perform this task, without me actually having to reveal sensitive info to the user. Is there anyone willing to point me in the right direction? Kind regards, Dirk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 to many relationship
[snip Chris] If you want multiple customers to be associated with each issue you need 3 tables: create table customers (customerid int auto_increment primary key, customername varchar(255)); create table issues (issueid int auto_increment primary key, issuetitle varchar(255)); create table customer_issues (issueid int, customerid int); then you can do: select * from customers c, issues i, customer_issues ci where c.customerid=ci.customerid AND ci.issueid=i.issueid; [/snip] Is there not a better way to do that? What will happen there is that a large result set will be created because when you just do select * from customers c, issues i, customer_issues ci it will be like the inner product from all these tables, and then just choosing the right ones. If the table C have 1000 records, issues 5000 and customer_issues 15000 you would end up with a 75,000,000,000 rows large results set, that would not be so sweet, would it? /Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 to many relationship
[snip Chris] The 'where' clause cuts that down to only matching records between the tables. Without the where, you'd end up with lots of rows but with the where it will be fine. [/snip] Yes, it cuts it down to that number of records in the end, so the final result set will just be a few rows that match the 'WHERE'. But the internal process of MySQL do merge all tables and then chooses the records that matches the 'WHERE' clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 to many relationship
There will hopefully be some MySQL guru to confirm if I am right or wrong. I also did the inner product version instead of JOIN's, but moved to JOIN's that are more logical in the way I work with the tables I have. /Peter -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 16, 2006 11:00 AM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: 1 to many relationship Peter Lauri wrote: [snip Chris] The 'where' clause cuts that down to only matching records between the tables. Without the where, you'd end up with lots of rows but with the where it will be fine. [/snip] Yes, it cuts it down to that number of records in the end, so the final result set will just be a few rows that match the 'WHERE'. But the internal process of MySQL do merge all tables and then chooses the records that matches the 'WHERE' clause. I don't know enough about mysql internals to debate that so I'll take your word for it. 'Explain' doesn't give enough information about what happens behind the scenes so I'm not sure how to prove/disprove that and I don't know of any tools that would show you that (if there is let me know!). Having said all of that I've never had a problem doing it the way I mentioned.. ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query needed
Not until we know the logic behind the code and how the calculations should be done. -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 1:03 PM To: Peter Lauri; mysql@lists.mysql.com Subject: Re: query needed if it is static then it works fine.but we have lots of codes in a table which should be done similar operation.instead varifying staticly with c1,c2 can we make dynamic. On 8/14/06, Peter Lauri [EMAIL PROTECTED] wrote: SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4', code, IF(code='c5', code, 0))) FROM datavalue; -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 11:26 AM To: mysql@lists.mysql.com Subject: query needed Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss regards, venu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query needed
SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4', code, IF(code='c5', code, 0))) FROM datavalue; -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 11:26 AM To: mysql@lists.mysql.com Subject: query needed Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss regards, venu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Alter Problem
Maybe:
$query = UPDATE profile SET acct_type='%at', ., genre='$g' WHERE
id=$userid
/Peter
-Original Message-
From: Nicholas Vettese [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 03, 2006 9:12 PM
To: mysql@lists.mysql.com
Subject: Alter Problem
I am working on a script that looks at the MySQL Table, checks to see if
there are any matches, and if there are, alter the table to add the
requested information. Here is the code I am using:
script
$query = ALTER profile (acct_type, username, firstname, lastname, email,
addr1, city, state, zip, nude, artistic, model_look, about_you, why_model,
genre) VALUES ('$at', '$un', '$fn', '$ln', '$e', '$ad1', '$c', '$st', '$zc',
'$nd', '$art', '$ml', '$ay', '$wm', '$g');
/script
What am I doing wrong? The error comes back saying that this is where the
problem is, and I have changed it from UPDATE to ALTER because I was trying
to get the information to be added to the table of an already registered
user.
Nick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Alter Problem
Comment: ALTER is used to change the structure of an table, for example add
an extra column or change the default values etc. Or to add an index or
similar.
-Original Message-
From: Nicholas Vettese [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 03, 2006 9:12 PM
To: mysql@lists.mysql.com
Subject: Alter Problem
I am working on a script that looks at the MySQL Table, checks to see if
there are any matches, and if there are, alter the table to add the
requested information. Here is the code I am using:
script
$query = ALTER profile (acct_type, username, firstname, lastname, email,
addr1, city, state, zip, nude, artistic, model_look, about_you, why_model,
genre) VALUES ('$at', '$un', '$fn', '$ln', '$e', '$ad1', '$c', '$st', '$zc',
'$nd', '$art', '$ml', '$ay', '$wm', '$g');
/script
What am I doing wrong? The error comes back saying that this is where the
problem is, and I have changed it from UPDATE to ALTER because I was trying
to get the information to be added to the table of an already registered
user.
Nick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select between date
What version of MySQL do you have? Depending on that, there are different methods. -Original Message- From: Penduga Arus [mailto:[EMAIL PROTECTED] Sent: Monday, July 31, 2006 6:33 PM To: mysql@lists.mysql.com Subject: select between date I want to do a program to display birthday for our staff. I have a field named birthday with date format (-mm-dd), from this field I want to display the staff who will have their birthday start from current date to 7 days a head. please help, thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHERE problem, or is it a problem?
Best group member, I have this query on MySQL version 4.0.27: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid) LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid ORDER BY numberofans DESC, part.memberid; This works fine, However, I only want the results where COUNT(*)=31. So I tried: SELECT part.memberid, part.prefname, part.email, COUNT( * ) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL AND COUNT( * ) =31 GROUP BY part.memberid ORDER BY numberofans DESC , part.memberid But then MySQL answered with: # - Invalid use of group function What is the problem here? Why can I not do a WHERE COUNT(*)=31? Is there any other way to just select the COUNT(*)=31? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE problem, or is it a problem?
That did it, thank you all! -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 2:10 AM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: WHERE problem, or is it a problem? Peter Lauri wrote: Best group member, I have this query on MySQL version 4.0.27: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid) LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid ORDER BY numberofans DESC, part.memberid; This works fine, However, I only want the results where COUNT(*)=31. So I tried: SELECT part.memberid, part.prefname, part.email, COUNT( * ) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL AND COUNT( * ) =31 GROUP BY part.memberid ORDER BY numberofans DESC , part.memberid But then MySQL answered with: # - Invalid use of group function What is the problem here? Why can I not do a WHERE COUNT(*)=31? Is there any other way to just select the COUNT(*)=31? Best regards, Peter Lauri WHERE conditions determine which rows to select. You can't count how many rows you've selected until after you've selected them. Use HAVING to filter the results after selection. Try: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid HAVING numberofans =31 ORDER BY numberofans DESC , part.memberid Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN table where not in other table
Best group member, I just made up this query, but let us work from this: SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id=table2.id) WHERE table2.prop IS NULL; This selects all rows from table1 where the id is not also in the table2. Is there any more logic way to do this, this is what I would like to do (in words): I want to select all rows in table1 that does not already have an reference in table2. Is that understandable? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
I tried that, but that generates: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM table2) LIMIT 0, 100' at line 1 Maybe it is a Version issue? What version of MySQL do support sub queries? /Peter -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 5:37 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 10:31 am, Peter Lauri wrote: Best group member, I just made up this query, but let us work from this: SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id=table2.id) WHERE table2.prop IS NULL; If I understand correct: SELECT * FROM table 1 WHERE id NOT IN (SELECT id FROM table2); -- Chris White PHP Programmer/DBacardi Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
The query in full was exactly as you wrote it (but without the typo) :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 5:56 AM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 10:53 am, Peter Lauri wrote: I tried that, but that generates: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM table2) LIMIT 0, 100' at line 1 What's the full query look like right now (btw, there was a typo earlier, that should have been table1 and not table 1 :/)? -- Chris White PHP Programmer/DBarkTree Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
4.0.27, so that is probably the reason. Any other way then with a sub query? I solved it with my stupid solution, feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 6:05 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 11:00 am, Peter Lauri wrote: The query in full was exactly as you wrote it (but without the typo) :) Alright, yes, what is your version of MySQL? I'm in the 5.0.22 series here and that works just fine. -- Chris White PHP Programmer/DBackItUp Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
The tables are of course not named table1 and table2, just using that in the discussion. :) All is working right now, but my solution is NOT that good according to the small amount of logic I have :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 6:43 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 11:30 am, Peter Lauri wrote: 4.0.27, so that is probably the reason. Any other way then with a sub query? I solved it with my stupid solution, feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) Are you using phpMyAdmin? I was told by a coworker that phpMyAdmin adds those limits in. Wondering if taking the LIMIT out might do it. Also, are the tables really named table1 and table2 (Yah, I know.. but I have to make sure :( )? -- Chris White PHP Programmer/DBoy Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULL TEXT and Asian languages
Best group member, I have a problem. I was going to use FULL TEXT search for my Thai client. It is working smooth with English text and wordings, the indexing and search works fine. The problem with Thai text is that words are not separated with a white space as in English and other languages. I think this screws up the indexing, and complete sentences are classed as a word. Assume Thai characters: Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch. I want to search for sentence, but can not. How can this be done? And will the indexing ever work? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FULL TEXT and Asian languages
That is what I am doing right now, but it is not that fast if this system
would grow, and also it is not ranking the searches.
Right now I do something like this:
$searchwords = explode( , $searchstring);
foreach($searchwords AS $value) {
$Query.= OR lajlaj LIKE '%$value%'
}
If there are many search words, the OR will grow a bit, and OR are not that
fast as I read somewhere.
/Peter
-Original Message-
From: JC [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 20, 2006 10:46 PM
To: Peter Lauri
Cc: mysql@lists.mysql.com
Subject: Re: FULL TEXT and Asian languages
don't know about indexing, but try to search: LIKE '%sentences%'
JC
On Thu, 20 Jul 2006, Peter Lauri wrote:
Best group member,
I have a problem. I was going to use FULL TEXT search for my Thai client.
It
is working smooth with English text and wordings, the indexing and search
works fine.
The problem with Thai text is that words are not separated with a white
space as in English and other languages. I think this screws up the
indexing, and complete sentences are classed as a word. Assume Thai
characters:
Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch.
I want to search for sentence, but can not. How can this be done? And
will
the indexing ever work?
Best regards,
Peter Lauri
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FULL TEXT and Asian languages
Why can the Thai and Chinese not use regular sentences and word delimiter :) So I have to stick to my LIKE thing, just to erase the FULL TEXT index I assume. -Original Message- From: Neculai Macarie [mailto:[EMAIL PROTECTED] Sent: Thursday, July 20, 2006 10:50 PM To: mysql@lists.mysql.com Subject: Re: FULL TEXT and Asian languages Peter Lauri wrote: Best group member, I have a problem. I was going to use FULL TEXT search for my Thai client. It is working smooth with English text and wordings, the indexing and search works fine. The FULLTEXT parser determines where words start and end by looking for certain delimiter characters; for example, ' ' (space), ',' (comma), and '.' (period). If words are not separated by delimiters (as in, for example, Chinese), the FULLTEXT parser cannot determine where a word begins or ends. To be able to add words or other indexed terms in such languages to a FULLTEXT index, you must preprocess them so that they are separated by some arbitrary delimiter such as ''. http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html -- mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULL TEXT search and Thai
Best group member, I have a problem. I was going to use FULL TEXT search for my Thai client. It is working smooth with English text and wordings, the indexing and search works fine. The problem with Thai text is that words are not separated with a white space as in English and other languages. I think this screws up the indexing, and complete sentences are classed as a word. Assume Thai characters: Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch. I want to search for sentence, but can not. How can this be done? And will the indexing ever work? Best regards, Peter Lauri
RE: Normailizing SQL Result Set
This is my ugly solution: SELECT MAX(IF(articles_attribs.attrib_key='content', articles_attribs. attrib_value, '')) AS content, MAX(IF(articles_attribs.attrib_key='description', articles_attribs. attrib_value, '')) AS description, MAX(IF(articles_attribs.attrib_key='keyword', articles_attribs. attrib_value, '')) AS keyword, MAX(IF(articles_attribs.attrib_key='title', articles_attribs. attrib_value, '')) AS title, FROM articles LEFT OUTER JOIN articles_attribs ON (articles_attribs.article_id=articles.id) WHERE articles.id=1 But I do not really understand why this would be normalized, it is a ugly and not so dynamic solution. /Peter -Original Message- From: Michael Caplan [mailto:[EMAIL PROTECTED] Sent: Thursday, July 20, 2006 12:54 AM To: mysql@lists.mysql.com Subject: Normailizing SQL Result Set Hi there, I am trying to work through a DB design that is giving me some trouble with the result sets. The situation is this: I have a table called articles and a related table call article_attributes Within the table articles I am maintaining basic info about an article, such as article id, active, etc. However, I am not maintaining any lanuage specific data about the article in that table (title, description, body, etc). All that info is stored in articles_attributes. The goal of storing all language specific info about an article in a seperate table is two fold: [CODE][/CODE] 1) I wish to maintain multiple language versions of an article without prior knowedge to the languages I have to handle, 2) I also wish to be able to easly add arbitrary new article attributes (eg: footnotes) without needing the modify the db tables. This is what I came up with for the structure: CREATE TABLE `articles` ( `id` int(10) unsigned NOT NULL default '0', `active` tinyint(1) NOT NULL default '0', PRIMARY KEY (`id`), KEY `active_idx_idx` (`active`) ) CREATE TABLE `articles_attribs` ( `article_id` int(10) unsigned NOT NULL default '0', `locale` varchar(5) NOT NULL default ' ', `attrib_key` varchar(255) NOT NULL default ' ', `attrib_value` longtext NOT NULL, PRIMARY KEY (`article_id`,`locale`,`attrib_key`), KEY `values_idx_idx` (`attrib_value`(767)) ) This works okay, however, when querying the database for one record, because of the join between articles = articles_attributes, I don't get one result set, but rather 1 X the number of attributes recorded for the article: +++++-+- + | id | active | article_id | locale | attrib_key | attrib_value | +++++-+- + | 1 | 1 | 1 | en_CA | content | h1Some Content/h1 | | 1 | 1 | 1 | en_CA | description | This is the article description | | 1 | 1 | 1 | en_CA | keyword | These are, article, keywords| | 1 | 1 | 1 | en_CA | title | Test article | +++++-+- + What I am struggling with is an elegant way I can normalize the result set. I want to end up with a result set that looks like this: +++++---+--- --+--+--+ | id | active | article_id | locale | content | description | keyword | title| +++++---+--- --+--+--+ | 1 | 1 | 1 | en_CA | h1Some Content/h1 | This is the article description | These are, article, keywords | Test article | +++++---+--- --+--+--+ I can programmatically go through the result set and flatten it so that all attrib_key values = corresponding attrib_value, but this is less than ideal. I'm wondering if any of you have ideas how I can acheive the desired result with some creative SQL? Thanks, Michael -- Michael Caplan - Zend Certified PHP Engineer Programming Manager Apison Communications Suite 110, 151 Provost Street New Glasgow, NS, Canada B2H 2P6 Phone: (902) 695-3375 Toll Free: (800) 845-6998 Fax: (902) 695- email: [EMAIL PROTECTED] URL: http://www.apison.com Specializing in web development, graphic design and Internet marketing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MAX_JOIN_SIZE
Best group member, I did this query on a very small database. And it gave error with some MAX_JOIN_SIZE error. I have never seen this, and how can I avoid this to happen? Do I have too many joins in one query? Should I write the joins differently? ++ SQL query: SELECT part.joindate AS dateadded, part.profile, part.prefname, part.email, pass.password, pdf.id AS pass1, pdf.password AS pass2, partype.type_desc FROM cmmember LEFT OUTER JOIN cmtest ON ( cmmember.id = cmtest.cmmember_id ) LEFT OUTER JOIN tblparticipants part ON ( part.memberid = cmtest.test_id ) LEFT OUTER JOIN parpass pass ON ( pass.memberid = part.memberid ) LEFT OUTER JOIN profilepdf pdf ON ( pdf.memberid = part.memberid ) LEFT OUTER JOIN tblpartype partype ON ( partype.type_num = part.par_type ) WHERE cmmember.id =6 AND ( pdf.pdftype = 'par' OR pdf.pdftype IS NULL ) ORDER BY part.joindate DESC , part.prefname, part.email LIMIT 0 , 30 MySQL said: #1104 - The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok ++ Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - CREATE VIEW Question
Search the Manual for CONCAT. SELECT /Peter -Original Message- From: z247 [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 04, 2006 10:02 PM To: mysql@lists.mysql.com Subject: Newbie - CREATE VIEW Question Say I have the following tables; siteID,name -- site1, XYZ site2, RSQ ID,site,data 1, site1, M 2, site2, Q 3, site2, Y 4, site1, P ... etc. And I want to create a view like this; siteID,name,data -- site1, XYZ, (M,P) site2, RSQ, (Q,Y) where all the related column data in the second table is placed in another column. How can I do this? Is there a function that can group these values into one variable or array? Thank you -- View this message in context: http://www.nabble.com/NewbieCREATE-VIEW-Question-tf1890326.html#a5168593 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT and NULL
SELECT * FROM table WHERE some_field IS NOT NULL; -Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Sunday, June 25, 2006 2:24 PM To: mysql@lists.mysql.com Subject: SELECT and NULL This my be a dumb question, but I have search the docs without finding the answer. What I want is something like: select * from table where some_field not null; But this gives me an error. I can do a 'where field is null', so I have tried different combination with 'not' etc, but without luck. All I get is an SQL error. The default value for some_field is null. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if else statement
SELECT IF(col1=3, (Select col2 from table2 where table2.id = 1), (Select
col2 from table3 where table3.id = 1)) FROM table1 WHERE id=1;
That should do it.
-Original Message-
From: Thomas Lundström [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 21, 2006 7:51 PM
To: Song Ken Vern-E11804
Cc: mysql@lists.mysql.com
Subject: Re: if else statement
Not sure what you're aming for here and how your data is structured but
why not use a join and alias and fetch all info in one select and then
solve what you need in your code?
Something in the line of:
select t2.col2 from_t2, t3.col2 from_t3
from table1 t1, table2 t2, table3 t3
where t1.id = t2.id
and t1.id = t3.id
and t1.id = 3
Maybe you can do something like that?
Regards,
Thomas L.
ons 2006-06-21 klockan 17:16 +0800 skrev Song Ken Vern-E11804:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Thanks
--
Ken
e11804
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Just need script for creating tables
You can do something like: mysqldump --no-data -Original Message- From: Xiaobo Chen [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 21, 2006 9:11 PM To: mysql@lists.mysql.com Subject: Just need script for creating tables Hi, all If I use 'mysqldump', I will get the script to create the tables and those 'insert' statements to insert the data. I am wondering if I just want the first part, i.e, the script to create the table, is there a command for this end? (I could copy paste the part from 'mysqldump' but it's not what I want to do.) Cheers. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limiting results from joins
-- Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. -- Just add: ORDER BY i.item_updated GROUP BY i.product_id (assuming that item_updated and product_id are in table i) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limiting results from joins
Take away the GROUP BY thing. And after that you just check if the rows are in the order that you want. The upper row would be the one that GROUP BY will take. Are you sure that you want the lowest value in the item_update? I would like to have the highest value. If you want the latest date, then you do ORDER BY i.item_updated DESC GROUP BY i.product_id -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kim Christensen Sent: Monday, June 12, 2006 9:15 PM To: Peter Lauri Cc: MySQL List Subject: Re: Limiting results from joins On 6/12/06, Peter Lauri [EMAIL PROTECTED] wrote: -- Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. -- Just add: ORDER BY i.item_updated GROUP BY i.product_id (assuming that item_updated and product_id are in table i) That gives me the result that I'm after, the only problem seems to be that the sorting is made before the matching - on all the rows (huge execution time) - how can I prevent this? MVH -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How To Pronounce MySQL
I say: My S-Q-L -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Thursday, June 08, 2006 7:31 PM To: MySQL List Subject: How To Pronounce MySQL This may be a really stupid question, but I hate looking stupid if I can avoid it. :-) I have been using Microsoft SQL Server for a while, and I'm now trying to switch all our applications over to use MySQL. Microsoft SQL Server is pronounced Sequel Server. Is MySQL pronounced My Sequel, or is it pronounced My S-Q-L? I mean, generally speaking? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How To Pronounce MySQL
Btw, better to ask and look stupid, then not to ask and be stupid... But this question does not give you a stupid look, more a look of a person seeking perfection :) -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Thursday, June 08, 2006 7:31 PM To: MySQL List Subject: How To Pronounce MySQL This may be a really stupid question, but I hate looking stupid if I can avoid it. :-) I have been using Microsoft SQL Server for a while, and I'm now trying to switch all our applications over to use MySQL. Microsoft SQL Server is pronounced Sequel Server. Is MySQL pronounced My Sequel, or is it pronounced My S-Q-L? I mean, generally speaking? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE from one server to another
I am not that clever, but I would just create a Web Service (WS) on the Server that the Laptop call whenever the laptop want to push the date into the server database. I heard something about something called rsync, but I think that is restricted to Linx, Unix. /Peter -Original Message- From: Jason Dimberg [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 10:35 AM To: mysql@lists.mysql.com Subject: UPDATE from one server to another I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Thank you. -- Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE from one server to another
Can you run rsync on Windows environment? -Original Message- From: Tim Lucia [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 6:32 PM To: 'Peter Lauri'; 'Jason Dimberg'; mysql@lists.mysql.com Subject: RE: UPDATE from one server to another rsync is a *nix utility that synchronizes two file systems, one local and one remote (typically). It is used to produce mirrors / backups / etc. You would not want to use it to synchronize database (raw) files via the file system. If you include a timestamp field, you can use that to copy across all records that are newer then the last time you uploaded, or, greater then the most-recent date in the main database. Tim rsync(1) - faster, flexible replacement for rcp DESCRIPTION rsync is a program that behaves in much the same way that rcp does, but has many more options and uses the rsync remote-update protocol to greatly speed up file transfers when the destination file is being updated. The rsync remote-update protocol allows rsync to transfer just the differences between two sets of files across the network connection, using an efficient checksum-search algorithm described in the techni- cal report that accompanies this package. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Monday, June 05, 2006 10:17 PM To: 'Jason Dimberg'; mysql@lists.mysql.com Subject: RE: UPDATE from one server to another I am not that clever, but I would just create a Web Service (WS) on the Server that the Laptop call whenever the laptop want to push the date into the server database. I heard something about something called rsync, but I think that is restricted to Linx, Unix. /Peter -Original Message- From: Jason Dimberg [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 10:35 AM To: mysql@lists.mysql.com Subject: UPDATE from one server to another I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Thank you. -- Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT ALL and flag [solved]
Solved it by:
SELECT table_a.name , if( table_a.id = table_b.table_a_id, 1, 0 ) AS
theindicator
FROM table_a
LEFT OUTER JOIN table_b ON ( table_a.id = table_b.table_a_id )
/Peter
Hi,
I have a table table_a and table_b:
table_a {
id
name
}
table_b {
table_a_id
b_value
}
Table A is a table with names, and table B is a table with values for a
specific name (optional, therefore a specific table).
I would like to select all records in A, done by:
SELECT name FROM table_a;
Returns:
Peter
Johan
Fredrik
But then I also would like to have a flag that flags if table_b, I want it
to return this if Peters id is the only one matching in table_b:
Peter 1
Johan 0
Fredrik 0
I tried:
SELECT name, if(table_a.id=table_b.table_a_id, 1, 0) AS indid FROM table_a
JOIN table_b;
But that generates multiple rows of the records in table_a. I tried GROUP BY
in combination with ORDER BY, but I did not manage to get it to work.
How can I do that if() without having to action do a join, I just want to
check if it exist, and then give value 1 or 0.
Anyone with ideas?
/Peter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT ALL and flag
Hi,
I have a table table_a and table_b:
table_a {
id
name
}
table_b {
table_a_id
b_value
}
Table A is a table with names, and table B is a table with values for a
specific name (optional, therefore a specific table).
I would like to select all records in A, done by:
SELECT name FROM table_a;
Returns:
Peter
Johan
Fredrik
But then I also would like to have a flag that flags if table_b, I want it
to return this if Peters id is the only one matching in table_b:
Peter 1
Johan 0
Fredrik 0
I tried:
SELECT name, if(table_a.id=table_b.table_a_id, 1, 0) AS indid FROM table_a
JOIN table_b;
But that generates multiple rows of the records in table_a. I tried GROUP BY
in combination with ORDER BY, but I did not manage to get it to work.
How can I do that if() without having to action do a join, I just want to
check if it exist, and then give value 1 or 0.
Anyone with ideas?
/Peter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Solved] Urgent problem
It was just to copy the files from the DATA folder in the installation directory. That was easier then I thought. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 6:52 AM To: mysql@lists.mysql.com Subject: Urgent problem Best group member, My computer stopped working. And I was able to install Windows on another partition and now I face one problem. I was running Windows with MySQL 4.1. I have all C: working, and can access all files. The first question: Can I recover that data from MySQL? The second question: How can I do that? (All attempts to repair Windows has failed, so that option is gone) I have gone thru the manual but all information there seems to be related to if the database crashes. And some options are there when you do make backups, but I do not have the backups. A desperate soul... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent problem
Best group member, My computer stopped working. And I was able to install Windows on another partition and now I face one problem. I was running Windows with MySQL 4.1. I have all C: working, and can access all files. The first question: Can I recover that data from MySQL? The second question: How can I do that? (All attempts to repair Windows has failed, so that option is gone) I have gone thru the manual but all information there seems to be related to if the database crashes. And some options are there when you do make backups, but I do not have the backups. A desperate soul... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recursive query
Hi, This is an example of an table that I store categories in. Each category have a id and also a parent. If the parent_id is 0 that category is a super-category. Assume that I would like to get a list of all categories and its parent category, grandparents category etc down to super-category. For example, if I take category id 13 as an example: 13 should be chosen; because that is the one I am working with 9 should be chosen; because it is the parent to 13 6 should be chosen; because it is the parent to 9 1 should be chosen; because it is the parent to 6 And that is it, because 1 has parent 0, and is therefore a super-category. Right now I am doing this with PHP and a recursive function, but is it possible to do this directly with one query? ++---+--+---+ | id | name | priority | parent_id | ++---+--+---+ | 1 | DME | 999 | 0 | | 2 | Training Material | 999 | 0 | | 3 | RND | 999 | 0 | | 4 | LEAP | 999 | 1 | | 5 | TDI | 999 | 1 | | 6 | Technical Support | 999 | 1 | | 7 | Training Module | 999 | 1 | | 8 | Detail by Component | 999 | 6 | | 9 | Step/Process | 999 | 6 | | 10 | Assessment| 999 | 9 | | 11 | Design| 999 | 9 | | 12 | Implement and Monitor | 999 | 9 | | 13 | Evaluation| 999 | 9 | | 14 | Reflection| 999 | 9 | | 15 | Transition| 999 | 9 | | 16 | TDI | 999 | 2 | | 17 | LEAP | 999 | 2 | | 18 | Other | 999 | 2 | | 19 | Tools | 999 | 3 | | 20 | RD Document | 999 | 3 | ++---+--+---+ Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting ' into database
Hi all,
Assume that I want to insert Juanita O'Connell into my database. How do I
do that? The problem is the ' in her last name. If I just put it in it will
be
INSERT INTO thetable (name) VALUES ('O'Connell');
And that does not work :) How can I solve this?
Best regards,
Peter Lauri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATEDIFF and TIMEDIFF
Best groupmember, I run version 3.23.58 and need to use something similar to DATEDIFF and TIMEDIFF to calculate difference between two a timestamp and current_timestamp(). Is there any other function that is working for version 3.23.58 that do the same job? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting the previous months documents
You should start by using MySQL date as the standard for date: -MM-DD
After that it is simple:
SELECT * FROM documents WHERE doc_date2006-05-02 ORDER BY doc_date DESC
LIMIT 1;
Othervise you could use MySQL function to take sub strings and create a
field in the query that extract it as 06-05-02 and order by that.
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:04 PM
To: mysql@lists.mysql.com
Subject: Getting the previous months documents
I have a database that stores documents relating to meetings. They have all
the usual stuff agenda, minutes etc. There are 3 paper types for each date
agenda (1 only), minutes (1 only), and a bunch of general documents titled
'papers'.
I need to display all the documents for a specific date which is easy but I
also need to retrieve the minutes for the PREVIOUS meeting which is proving
more difficult. The documents are stored by date in the format dd/mm/yy. If
someone wants the documents from 02/05/06 how do I find the minutes for the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default 'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting the previous months documents
I did this, sorry for bad format. This will do it for you.
SELECT CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) AS docdate
FROM `tempdate` WHERE CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext,
4, 2), SUBSTRING(temptext, 1, 2)) '060703' ORDER BY
CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) DESC LIMIT 1
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:28 PM
To: Peter Lauri; mysql@lists.mysql.com
Subject: Re: Getting the previous months documents
My sql is not so great. The dates are entered through a dropdown box in the
format dd/mm/ (uk date format) as a VARCHAR.
I could use strrev and str_replace (PHP) to get it in the correct format but
will the mysql query you send work on a VARCHAR which it is it present or
will I have to change the field to DATE?
Ross
- Original Message -
From: Peter Lauri [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 9:06 AM
Subject: RE: Getting the previous months documents
You should start by using MySQL date as the standard for date: -MM-DD
After that it is simple:
SELECT * FROM documents WHERE doc_date2006-05-02 ORDER BY doc_date DESC
LIMIT 1;
Othervise you could use MySQL function to take sub strings and create a
field in the query that extract it as 06-05-02 and order by that.
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:04 PM
To: mysql@lists.mysql.com
Subject: Getting the previous months documents
I have a database that stores documents relating to meetings. They have
all
the usual stuff agenda, minutes etc. There are 3 paper types for each date
agenda (1 only), minutes (1 only), and a bunch of general documents titled
'papers'.
I need to display all the documents for a specific date which is easy but
I
also need to retrieve the minutes for the PREVIOUS meeting which is
proving
more difficult. The documents are stored by date in the format dd/mm/yy.
If
someone wants the documents from 02/05/06 how do I find the minutes for
the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default
'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting the previous months documents
Ok, a little more clear:
$query = SELECT *
//Change tempdate to the table name of your board document table
FROM `tempdate`
//Change temptext to the field name of the date in your board doc table
WHERE CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) '060703'
//Same here
ORDER BY CONCAT(SUBSTRING(temptext, 7,
2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) DESC LIMIT 1;
If this does not help, give the table structure and I create the query for
you. My suggestion is to write this query in MySQL directly first, do not
use PHP to try to get a query to work.
Try to understand the SUBSTRING command and CONCAT command first. Read the
documentation on www.mysql.com.
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 5:07 PM
To: Peter Lauri
Subject: Re: Getting the previous months documents
That looks very complicated but I suppose it converts the date and compares.
What does it return? I need the returned result to be an associative array
of the previous date. Should it be select * FROM board_papers concat..
This is my code with your query in it. But it doesn't return anything.
$query= SELECT CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) AS docdate FROM `tempdate` WHERE
CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) '060703' ORDER BY CONCAT(SUBSTRING(temptext, 7,
2),SUBSTRING(temptext, 4, 2), SUBSTRING(temptext, 1, 2)) DESC LIMIT 1;
$result= mysql_query($query);
while ($row = @mysql_fetch_array($result, MYSQL_ASSOC)){
echo $row['doc_date'];
$row['fileSize'] = $row['fileSize']/ 1024;
$row['fileSize']= number_format($row['fileSize'], 0);
$size= $row['fileSize'];
$name = str_replace(_, , $row['fileName']);
$name = str_replace(.pdf, , $name);
$link= $row['content'];
$id=$row['id'];
?
Thanks for your help.
- Original Message -
From: Peter Lauri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 10:20 AM
Subject: RE: Getting the previous months documents
I did this, sorry for bad format. This will do it for you.
SELECT CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) AS docdate
FROM `tempdate` WHERE CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext,
4, 2), SUBSTRING(temptext, 1, 2)) '060703' ORDER BY
CONCAT(SUBSTRING(temptext, 7, 2),SUBSTRING(temptext, 4, 2),
SUBSTRING(temptext, 1, 2)) DESC LIMIT 1
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:28 PM
To: Peter Lauri; mysql@lists.mysql.com
Subject: Re: Getting the previous months documents
My sql is not so great. The dates are entered through a dropdown box in
the
format dd/mm/ (uk date format) as a VARCHAR.
I could use strrev and str_replace (PHP) to get it in the correct format
but
will the mysql query you send work on a VARCHAR which it is it present or
will I have to change the field to DATE?
Ross
- Original Message -
From: Peter Lauri [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, May 02, 2006 9:06 AM
Subject: RE: Getting the previous months documents
You should start by using MySQL date as the standard for date: -MM-DD
After that it is simple:
SELECT * FROM documents WHERE doc_date2006-05-02 ORDER BY doc_date DESC
LIMIT 1;
Othervise you could use MySQL function to take sub strings and create a
field in the query that extract it as 06-05-02 and order by that.
/Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 02, 2006 3:04 PM
To: mysql@lists.mysql.com
Subject: Getting the previous months documents
I have a database that stores documents relating to meetings. They have
all
the usual stuff agenda, minutes etc. There are 3 paper types for each
date
agenda (1 only), minutes (1 only), and a bunch of general documents
titled
'papers'.
I need to display all the documents for a specific date which is easy but
I
also need to retrieve the minutes for the PREVIOUS meeting which is
proving
more difficult. The documents are stored by date in the format dd/mm/yy.
If
someone wants the documents from 02/05/06 how do I find the minutes for
the
previous date when I do not know when it is?
Can I do this with mysql? Or will it be better with mktime and some php?
--
-- Table structure for table `board_papers`
--
CREATE TABLE `board_papers` (
`id` int(4) NOT NULL auto_increment,
`doc_date` varchar(10) NOT NULL default '-00-00',
`article_type` enum('agenda','minutes','paper') NOT NULL default
'agenda',
`fileName` varchar(50) NOT NULL default '',
`fileSize` int(4) NOT NULL default '0',
`fileType` varchar(50) NOT NULL default '',
`content` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
where group and inner join
Best groupmember, I am doing this query that works fine. SELECT tps.tour_player_id, sum(if(tsh.hole_number=9, tps.strokes, '0')) AS 'front_9', sum(if(tsh.hole_number=10, tps.strokes, '0')) AS 'back_9', sum(tps.strokes) AS 'score' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id GROUP BY tps.tour_player_id ORDER BY score, back_9; However, I would like to just get the result where sum(tps.strokes)90, so I added WHERE sum(tps.strokes)90 after the inner join like this SELECT tps.tour_player_id, sum(if(tsh.hole_number=9, tps.strokes, '0')) AS 'front_9', sum(if(tsh.hole_number=10, tps.strokes, '0')) AS 'back_9', sum(tps.strokes) AS 'score' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id WHERE sum(tps.strokes) 90 GROUP BY tps.tour_player_id ORDER BY score, back_9; It gives me error : Invalid use of group function Where does the error come from? And how would I solve this? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: where group and inner join
Thanks. That worked smooth as silk! -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Thursday, April 20, 2006 11:42 AM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: where group and inner join In the last episode (Apr 19), Dan Nelson said: It's useful to note that SELECT statements generally work in the order they are written. The WHERE clause applies to the records as they are read from the source tables, and at that point, no grouping has been done, so there's no sum. Try moving your filter to a HAVING clause, which comes between GROUP BY and HAVING, and applies to the which comes between GROUP BY and ORDER BY, of course :) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SOLVED] Making result rows to one row
Yes, you are correct. I tried it but I got some errors. I simplified my query and tried it, and it worked. Then I added the more complicated parts after that, and it works VERY good. This is what makes life worth living :) -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 9:55 PM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: Making result rows to one row Peter, Peter Brawley said: SELECT ..., GROUP CONCAT(LPAD(strokes,3,' ') SEPARATOR '') AS ' 1 2 3 4 5 6 7 8 9' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id WHERE tour_player_id=175 GROUP BY tsh.id This worked, however, it gives me all results in one cell. I would like to have them sorted in one cell per strokes. Is that possible? Sure, that's what the pivot table example at http://www.artfulsoftware.com/queries.php#36 does. Your query would be something like... SELECT player_id, MAX( IF(hole=1,strokes,'') ) AS 'Hole 1' MAX( IF(hole=2,strokes,'') ) AS 'Hole 22, ... FROM tbl GROUP BY player_id ... PB The reason for this is that I have to use each hole as ORDER criteria. If the SUM is equal, then I have to check the sum of the last nine holes. If they are the same, then I have to check the result of hole 18, 17, 16 etc. This is my exact query as is now: SELECT tps.tour_player_id, GROUP_CONCAT(LPAD(strokes,2,' ') SEPARATOR '-') AS hole_scores, sum(tps.strokes) AS 'score' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id GROUP BY tps.tour_player_id ORDER BY score; Best regards, Peter Lauri -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.0/306 - Release Date: 4/9/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Making result rows to one row
Peter Brawley said: SELECT ..., GROUP CONCAT(LPAD(strokes,3,' ') SEPARATOR '') AS ' 1 2 3 4 5 6 7 8 9' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id WHERE tour_player_id=175 GROUP BY tsh.id *** This worked, however, it gives me all results in one cell. I would like to have them sorted in one cell per strokes. Is that possible? The reason for this is that I have to use each hole as ORDER criteria. If the SUM is equal, then I have to check the sum of the last nine holes. If they are the same, then I have to check the result of hole 18, 17, 16 etc. This is my exact query as is now: SELECT tps.tour_player_id, GROUP_CONCAT(LPAD(strokes,2,' ') SEPARATOR '-') AS hole_scores, sum(tps.strokes) AS 'score' FROM tour_player_score tps INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id GROUP BY tps.tour_player_id ORDER BY score; Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AS in a statement
Best group member, I do this query SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par, score-par AS overpar FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id It gives error: [localhost] ERROR 1054: Unknown column 'score' in 'field list' I know where the error comes from (the tables does not have score in them), but I want to simplify the query using the AS property, and continue in the query us it. This works: SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par, tour_player_score.strokes - tour_scorecard_hole.par AS overpar FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id But I do not want to use the long name... anyone with comments? Best regards, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do if and elseif and other calculations
Best groupmember,
I have this query
SELECT count(*) AS number_of_holes, tour_player_score.strokes -
tour_scorecard_hole.par AS overpar
FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND
tour_scorecard_hole.id=tour_player_score.scorecard_hole_id GROUP BY overpar
It gives me number_of_holes and overpar.
Right now I do this with PHP, but would like to move it to sql directly:
$diff = 0;
While($Row = mysql_fetch_array()) {
If($Row['overpar'])0) $diff = $diff - $Row['overpar']*
$Row['number_of_holes'];
Elseif($Row['overpar']=3) diff = $diff - $Row['overpar']*
$Row['number_of_holes'] + 2;
}
Anyway to move this to MySQL and just have one row containing the diff?
Best regards,
Peter Lauri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do if and elseif and other calculations
Haha, yes I know that. I just created the code in the email editor. :)
-Original Message-
From: Barry [mailto:[EMAIL PROTECTED]
Sent: Monday, April 10, 2006 5:19 PM
To: mysql@lists.mysql.com
Subject: Re: Do if and elseif and other calculations
Peter Lauri wrote:
Best groupmember,
I have this query
SELECT count(*) AS number_of_holes, tour_player_score.strokes -
tour_scorecard_hole.par AS overpar
FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND
tour_scorecard_hole.id=tour_player_score.scorecard_hole_id GROUP BY
overpar
It gives me number_of_holes and overpar.
Right now I do this with PHP, but would like to move it to sql directly:
$diff = 0;
While($Row = mysql_fetch_array()) {
If($Row['overpar'])0) $diff = $diff - $Row['overpar']*
$Row['number_of_holes'];
Elseif($Row['overpar']=3) diff = $diff - $Row['overpar']*
--^
You have an error here.
You miss a ´$´
$Row['number_of_holes'] + 2;
}
Anyway to move this to MySQL and just have one row containing the diff?
Sorry can't give any more comments to that.
Looking forward to see the comments on this.
Greets
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do if and elseif and other calculations
That did work very well, thank you. The DEFAULT value was 0. I am getting closer in my attempt to generate a leaderboard without php :) From: Gabriel PREDA [mailto:[EMAIL PROTECTED] Sent: Monday, April 10, 2006 7:16 PM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: Do if and elseif and other calculations You can do something like this: SET @diff = 0; SELECT count(*) AS number_of_holes, tour_player_score.strokes - tour_scorecard_hole.par AS overpar, IF(overpar0, @diff-(overpar*number_of_holes), ELSEVALUE) FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id GROUP BY overpar; And instead of ELSEVALUE you can insert another IF... There is also a CASE in MySQL... you can also use that one... What i don't see here is a DEFAULT value... do you have any ? -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Making result rows to one row
Best group member, Many of you probably do not understand the question; neither would I if someone ask me, so I will explain a little bit more. This is what I have: SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; This generates this: +-+ | strokes | +-+ | 6 | | 4 | | 5 | | 3 | | 5 | | 4 | | 4 | | 3 | | 6 | +-+ I would like to retrieve it in one row instead of many rows with one per row. s1 s2 s3 s4 s5 s6 s7 s8 s9 6 4 5 3 5 4 4 3 6 Can this be done? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Making result rows to one row
-Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Monday, April 10, 2006 10:45 AM To: mysql@lists.mysql.com Subject: Making result rows to one row Best group member, Many of you probably do not understand the question; neither would I if someone ask me, so I will explain a little bit more. This is what I have: SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; This generates this: +-+ | strokes | +-+ | 6 | | 4 | | 5 | | 3 | | 5 | | 4 | | 4 | | 3 | | 6 | +-+ I would like to retrieve it in one row instead of many rows with one per row. s1 s2 s3 s4 s5 s6 s7 s8 s9 6 4 5 3 5 4 4 3 6 Can this be done? Best regards, Peter Lauri -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 12:47 AM To: Peter Lauri; mysql@lists.mysql.com Subject: RE: Making result rows to one row Can you tell us more about the use case? Why does this need to be in a single row? Maybe instead of figuring out how to get this into a single row we could instead figure out how to solve the problem that requires it to be in a single row. * My goal is to create a leaderboard for golf in just one query. I want one row to be for one player. The final result will be something like this: | Peter Lauri 4 3 4 6 5 4 2 5 3 36 Tiger Woods 5 5 4 4 4 4 4 2 4 37 | The information for the rows I get with this queries: //Hole scores (one resultset with 9 rows) SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; //Sum of all holes (one resultsit with 1 rows) SELECT sum(tps.strokes) FROM tour_player_score tps join tour_scorecard_hole tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY tsh.hole_number; Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting database and its tables to UTF-8
Hi, I have a database with around 40 tables that needs to be converted to UTF-8 to support multi languages. What is the best procedure to do this? And is it any way to change the default charset to UFT-8 so tables by default will become UFT-8? And can I have one table with different fields with different charset? Best regards, Peter Lauri
RE: Converting database and its tables to UTF-8
Is this the only way? I was hoping that phpMyAdmin would have a nice function for this. What is the reason for not being able to use String types when using UFT-8? Maybe I have to learn more about the UFT-8 to find the answer about that? Best regards, Peter Lauri _ From: Gabriel PREDA [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 14, 2006 6:30 PM To: mysql@lists.mysql.com Cc: Peter Lauri Subject: Re: Converting database and its tables to UTF-8 Hi Peter, That will be a lot of work ! 1. First make a back-up... it's always a good ideea ! 2. For every table in the database alter String Types into BINARY string types that means: - (VAR)CHAR(M) will become (VAR)CHAR(M) BINARY or (VAR)BINARY(M) - TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT will become respectively TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB 3. Alter the database isuing ALTER DATABASE `database_name` DEFAULT CHARACTER SET utf8; 4. Alter each table issuing: ALTER TABLE `table_name` DEFAULT CHARSET=utf8; 5. Alter back the column types. This should do it ! And because you issued the ALTER DATABASE from now on all tables in that database will be in utf8 ... and falling down any column will be utf8 if you don't specify explicitly anything else... Yes you can have a charset on the database in that database tables with different chartsets and even in a table you can have columns with other chartsets... Gabriel PREDA Senior Web Developer On 2/14/06, Peter Lauri [EMAIL PROTECTED] wrote: Hi, I have a database with around 40 tables that needs to be converted to UTF-8 to support multi languages. What is the best procedure to do this? And is it any way to change the default charset to UFT-8 so tables by default will become UFT-8? And can I have one table with different fields with different charset? Best regards, Peter Lauri
RE: YAQQ (Yet Another Query Question)
Have you tried the GROUP BY? Make something like (not sure of exact syntax, check the manual for that): SELECT COUNT(*) AS cnt, data1_id FROM data1_id GROUP BY data1_iD; /Peter -Original Message- From: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 14, 2005 11:31 PM To: MYSQL List Subject: YAQQ (Yet Another Query Question) I am using MySQL 4.0.x on a Linux machine with a JSP/Servlet front-end to display the data. I have a table with experimental data for each flight of a rocket. Conceptually, it looks like (with many more columns): Flights +---+--+--+ | flight_id | data1_id | data2_id | +---+--+--+ | 1 |1 |1 | | 2 |1 |3 | | 3 |1 |1 | | 4 |2 |2 | | 5 |2 |3 | | 6 |1 |1 | | 7 |1 |1 | | 8 |4 |4 | | 9 |1 |2 | |10 |1 |2 | |11 |1 |1 | +---+--+--+ The data1_id and data2_id are indexes for the data recorded for that flight. I want to summarize the data. One such summary is to count the number of different data1_id's and data2_id's. For example: Flight Result Summary index: 1 2 3 4 data1_id8 2 0 1 data2_id5 3 2 1 I can think of 2 ways to make this summary table. 1. Issue 4 queries per data_id of the form SELECT COUNT(flight_id) FROM Flights WHERE data1_id=** where ** is set to the values 1,2,3,4. For the table above, I would have to issue a total of 8 queries. 2. Issue one query of the form SELECT flight_id FROM Flights and do the counting in my Java code. A simple loop through the ResultSet could count the different values for the data_ids. My questions are: 1. Is there a better way than these two options for getting the data I want? A single query per data_id? 2. Generally, what is the most efficient way to do this? Is is better to issue more queries that gather the calculated data or better to issue one query for the raw data and then do the calculations in Java? I am sure there are many factors that effect the answer to this question - server resources, code design, etc. However, I am interested in a best practices type of answer or general rule of thumb from the sage experts on the list. Thanks for any insights you can provide! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hijackers?
Best group member, How can I prevent people from hijacking a query? I read this in an article about a few months ago, but now I can not find that article again. This question is maybe not so exact, and I do not know how risky it is to not protect your system from database hijackers? Can someone give an example of how a database can be hijacked or destroyed? What kind of queries is more vulnerable then others? Best regards, Peter Lauri
Database in Thai
Best groupmember, I am in the situation to develop an web site for a Thai school and it will be in Thai and English. How can I setup so that my tables understand the Thai decoding (think it is UTF-8)? Thanks
Date increment
Best group member, I have a field called expiredate of type date. I would like to add 17 days to the expiredate without doing any scripting, is that possible? Example: Expiredate is 2005-11-30 and I want to extend the expiredate with 17 days. Is there any function in MySQL that adds days to a date? A solution for this would be to write a PHP script that takes the date and adds 17 days to it (checking month overlap and stuff), but I want to skip scripting if there is an solution within MySQL that does it for me. /Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Two MySQL databases on different computers
Hi, I have two databases. Database A is located on a server that I run my web hosting from. The other database B is located on a computer with a fixed IP. How can I configure database B so I can access database B from my web server? From my A system I would like to be able to do INSERT, SELECT and UPDATE queries on the database B. Best regards, Peter Lauri
join query
Hello, I made little board, where all postings are in one table: Field Type --- topic_id int(6) unsigned topic_pid int(6) unsigned authorvarchar(50) mail varchar(255) posting_time timestamp(14) ipvarchar(15) body text reg_user tinyint(1) topic_pid shows what kind of topic it is, if 0 then its new thread else reply for exist one. Now i want to sort threads by last posting_time but because i havent mysql 4.1 in server then i cant use sub query. Is it possible to get it with joins and if yes then how? -- Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
estonia charset and upper/lower case
Hello! When I run mysql with default-charset = estonia, it makes difference between upper and lower case. With latin1 charset no such distinction are made. Could someone clarify for me, is it bug? -- Lauri Anton ( [EMAIL PROTECTED] ) ( +3727302126 ) - estonia - Server version: 3.23.49 Client characterset:latin1 Server characterset:estonia mysql select * from a; ++--+ | id | str | ++--+ | 1 | AAA | | 2 | Aaa | | 3 | aaa | ++--+ 3 rows in set (0.00 sec) mysql select * from a where str like 'AAA'; ++--+ | id | str | ++--+ | 1 | AAA | ++--+ 1 row in set (0.00 sec) - latin1 -- Server version: 3.23.49 Client characterset:latin1 Server characterset:latin1 mysql select * from a; ++--+ | id | str | ++--+ | 1 | AAA | | 2 | Aaa | | 3 | aaa | ++--+ 3 rows in set (0.00 sec) mysql select * from a where str like 'AAA'; ++--+ | id | str | ++--+ | 1 | AAA | | 2 | Aaa | | 3 | aaa | ++--+ 3 rows in set (0.00 sec) - 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
substring and a join?
Hello, I have the following query: "SELECT thara_plane.id,thara_plane.nam e,thara_plane.nick,thara_plane.bodytext,thara_category.category " ."FROM thara_plane,thara_category,substring(thara_plane.bodytext,225) as bodytext " ."WHERE ((thara_plane.category = thara_category.id)) " ."ORDER BY thara_plane.time_last DESC"; I need to select the first 255 chars from the field "thara_plane.bodytext". The previous query, however, doesn't work. I have tried it several ways, changine one row and another but I haven't yet found a solution that works. Any ideas? Yours, Lauri - 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
Isamchk
Hello, Several of my tables are corrupted. I have a Win system. How can I repair my DB's with isamchk. What would I have to enter to the command line if I would like to check all DB's and all tables? The help says that I have to specify the table on the command line - it gives the error "file not found". Do I have to specify the DB to use somehow as well. Thanks in advance. Yours, Lauri - 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
Error 127
Hello everybody, About two hours ago I had a system crash and one of my MySQL databases was extensively used at the same time. When the system went back up, the table (the table has 37000 rows) that was used at the time of the crash started to give me the error : Got error: 1030: Got error 127 from table handler when retrieving data from server What does that mean? Can I somehow fix it - a second database has started to give similar error codes as well. Thanks, Lauri Vin - 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
