RE: totalizing of Rows please help!!
i have three Values in each row of my MySQL database, im needing to sum them in the table as they are displayed per Row ?? how do i do this ? Projectname Elecremain Controlremainotherremain ?php ? Project1 2300 1600 250 ?php (Sum) ? Project2 4300 600 150 ?php (Sum) ? Select a,b,c, a+b+c as GrandSum From `table` Try next time to give a name to your table and to use simpler field names, like a, b, c, etc... No need to group, here, really. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Nico Rittner wrote: hi, are you using the InnoDB storage engine for your tables? yes, i do. example: $ mysqldump -d core groups : CREATE TABLE `groups` ( `id` smallint(5) unsigned NOT NULL default '0', `name` varchar(32) NOT NULL default '', `parent_id` smallint(5) unsigned default NULL, `setting_` text NOT NULL, `r__groups_users_status__id` tinyint(3) unsigned NOT NULL default '0', `_ctime` int(10) unsigned NOT NULL default '0', `_mtime` int(10) unsigned NOT NULL default '0', `_uid` smallint(5) unsigned NOT NULL default '0', `_gid` smallint(5) unsigned NOT NULL default '0', `_mod` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `_uid` (`_uid`), KEY `_gid` (`_gid`), KEY `_mod` (`_mod`), KEY `parent_id` (`parent_id`), KEY `r__groups_users_status__id` (`r__groups_users_status__id`), CONSTRAINT `groups_ibfk_10` FOREIGN KEY (`_gid`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_11` FOREIGN KEY (`_uid`) REFERENCES `users` (`id`), CONSTRAINT `groups_ibfk_7` FOREIGN KEY (`parent_id`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_8` FOREIGN KEY (`r__groups_users_status__id`) REFERENCES `groups_users_status` (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups'; As you can see, the foreign keys - statements are included, but without the 'action parts' ( on update,on delete ); thanks, Nico Hi Nico, not sure what is going on here. In a simple test I did, everything was fine as yo can see below. I've also created your groups table (without the references to other tables) and added and on delete clause, which was dumped fine. Could be a bug in 4.1.14 of course. Sorry to ask this questions, but are you confident that the action clauses work properly, i.e. the tables were created correctly? What do you see when you execute show create table groups? Cheers Frank mysqldump -d test child -- MySQL dump 10.9 -- -- Host: 127.0.0.1Database: test -- -- -- Server version 4.1.13 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; [snip] -- -- Table structure for table `child` -- DROP TABLE IF EXISTS `child`; CREATE TABLE `child` ( `parent_id` int(11) NOT NULL default '0', PRIMARY KEY (`parent_id`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
I'm so confused. I'm finally getting around to needing to do a 'store locator' thing. I procured myself some zip/lat/long databases from various places. Then I noticed that for the same zip code, I got different values in different databases??!!! So for a sanity check, I decided to look online and punch in some to see what the real lat/long should be. Well, different sites give different values, and not only are they slightly off, but sometimes they're _positive_ or _negative_!? UGH! http://www.zipinfo.com/cgi-local/zipsrch.exe?ll=llzip=98119Go=Go Seattle WA 98119 47.6388 122.3700 http://www.census.gov/cgi-bin/gazetteer?city=state=zip=98119 Location: 47.637917 N, 122.364272 W http://www.csgnetwork.com/cgi-bin/zipcodes.cgi?Zipcode=98119 http://www.myzipdb.com/mzdb_demo.php http://www.nearby.org.uk/coord.cgi?p=98119 City Seattle State Abbreviation WA State Washington Latitude 47.638770 Longitude -122.366941 So I try the east coast: http://www.zipinfo.com/cgi-local/zipsrch.exe?ll=llzip=14526Go=Go Penfield NY 14526 43.1532 77.4485 http://www.census.gov/cgi-bin/gazetteer?city=state=zip=14526 Location: 43.139638 N, 77.456043 W http://www.csgnetwork.com/cgi-bin/zipcodes.cgi?Zipcode=14526 http://www.nearby.org.uk/coord.cgi?p=14526f=full City Penfield State Abbreviation NY State New York Latitude 43.145342 Longitude -77.458499 Please help me to understand WTF is going on? Which lat/long do I need? How does the negatives effect the magic formulas to calculate distances? (I'm no trig wiz). I assume that the more precise the numbers, the better the results will be. To what decimal point should I be concerned with? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Individual Row Addition ((!!!help!!)
Brian E Boothe wrote: well i have this $sql2=SELECT ordernumber,Elecrem, CtrlProjrem, OthrProjrem SUM(Elecrem+CtrlProjrem+OthrProjrem) AS btstotal9 FROM orders GROUP BY ordernumber ORDER BY ordernumber; only add's up the first row it hits and adds it to the remaining rows ,, I need individual Row Addition Please helppp OrdernumberElecrem, CtrlProjrem,OthrProjrem 12311314234 123 123 = ? row ? = 480 12354314100 123 123 = ? row ? = 346 You forgot some exclamation marks. here i give you some: !! !!! !! That should fit you. -- 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]
Top N selections + rest row
Hi All, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. Regards, Cor
Re: Top N selections + rest row
C.R.Vegelin wrote: Hi All, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. Regards, Cor Writing it into a cache_table. Write the first rows into the cache_table and then the rest of it. -- 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]
Re: Top N selections + rest row
Hi, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 11:06 AM Subject: Re: Top N selections + rest row Hi, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.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: Top N selections + rest row
--- C.R.Vegelin [EMAIL PROTECTED] wrote: Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 11:06 AM Subject: Re: Top N selections + rest row Hi, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html Shawn Green __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html That sounds like what he needs, excellent :-) Martijn Tonies Database Workbench - development tool for Firebird and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
On Tue, 2006-04-25 at 00:43 -0700, Daevid Vincent wrote: So for a sanity check, I decided to look online and punch in some to see what the real lat/long should be. Well, different sites give different values, and not only are they slightly off, but sometimes they're _positive_ or _negative_!? UGH! Not sure what your confusion is. It is a matter of notation. The negative value represents West where it is negative (as would be the East; note how there is no W mentioned there). So read about their presentation formats. I think 4 decimal points are plenty, especially if they follow the rounding rules. :-) Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
... err, as would be South... N+, S-, E+, W- Ed :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Hi Shawn, Cor, all! Shawn Green wrote: --- C.R.Vegelin [EMAIL PROTECTED] wrote: Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html I checked it here: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html It seems that WITH ROLLUP is not adequate for Cor's needs, see this quote: | LIMIT can be used to restrict the number of rows returned to the | client. LIMIT is applied after ROLLUP, so the limit applies against | the extra rows added by ROLLUP. Cor, what about a UNION? Untested: ( Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25 ) UNION ( SELECT World, Sum(Sales) AS Sales From myTable Where Year=2005 ) ; Note the extra parentheses, according to the manual they are needed to ensure that the limit is applied to the first select only. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
Read these http://en.wikipedia.org/wiki/Longitude http://en.wikipedia.org/wiki/Lattitude And no, you cannot drive my yacht ;-) -Original Message- From: Gmail User [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 9:05 AM To: mysql@lists.mysql.com Subject: RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude) ... err, as would be South... N+, S-, E+, W- Ed :-) -- 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]
Stumped again by joins
As a relative newbie, and an almost total newbie to the use of left joins, I'm aware that there's some difference in the way joins work between MySQL 3.x and 5.x, but in my ignorance I can't figure out what the heck it is from reading the 'upgrading' pages on dev.mysql.com. When I first joined this list (joined - geddit?), Barry in particular solved a search problem for me by introducing left joins. A simplified version of my query is this (it's a database of tourist guides, where I've entered 'olympic' into the catch-all text field at the bottom): select [fields I want to display] from guides as g left join biography as b on b.guide_id = g.id left join interests as i on i.guide_id = g.id left join tours as t on t.guide_id = g.id left join walks as w on w.guide_id = g.id left join lectures as l on l.guide_id = g.id where show_on_web = '1' and (b.biography like '%olympic%' or i.interests like '%olympic%' or t.tours like '%olympic%' or w.walks like '%olympic%' or l.lectures like '%olympic%') order by ... This worked like a charm (with fulltext indices on the text fields being searched in those five tables) in 3.23.x, but now it falls over and finds nobody at all in 5.0.19. The rest of the search is fine - there are various selects and checkboxes on which you can search and as long as I type nothing into the catch-all it behaves perfectly, but as soon as I do I get a zero result. (The whole bit with the left joins only gets added to the query if there's something in the catch-all.) The other major change is that I'm now using the utf8 charset throughout the database and scripts, whereas before, with 3.23 not supporting it, I was utf8_decode()ing everything that went to MySQL and utf8_encode()ing everything that came out of it. I did try putting back the utf8_decode() round the catch-all search string, but (as I expected) it made no difference. At first I thought the upgrade or utf8 might be having some effect on the way like '%...%' works, but another simpler search uses that and it's fine. The whole point of having five separate tables for those elements is that guides can record their information in a number of languages, so there's a row per guide per language in each table - or maybe none at all (not so many guides offer lectures, for example). I want users to be able to find text in any of the languages on offer. Where am I going wrong? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ The lead car is absolutely unique, except for the one behind it which is identical. -- Murray Walker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Thanks Shawn, According: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html: When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. ... Regards, Cor - Original Message - From: Shawn Green [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; Martijn Tonies [EMAIL PROTECTED]; mysql@lists.mysql.com; [EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 1:18 PM Subject: Re: Top N selections + rest row --- C.R.Vegelin [EMAIL PROTECTED] wrote: Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 11:06 AM Subject: Re: Top N selections + rest row Hi, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html Shawn Green __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Accumilating Blog Comments
Hi Folks, Here is the table for the articles: CREATE TABLE `blg_article_art` ( `id_art` int(11) NOT NULL auto_increment, `idtop_art` int(11) NOT NULL default '0', `title_art` varchar(100) NOT NULL default '', `description_art` blob NOT NULL, `text_art` longtext NOT NULL, `date_art` datetime default NULL, PRIMARY KEY (`id_art`) ) TYPE=MyISAM AUTO_INCREMENT=117 ; Here is the table for the comments: CREATE TABLE `blg_comment_com` ( `id_com` int(11) NOT NULL auto_increment, `idart_com` int(11) NOT NULL default '0', `text_com` text NOT NULL, `idusr_com` int(11) NOT NULL default '0', `date_com` datetime default NULL, `time_com` time default NULL, `valid_com` tinyint(4) NOT NULL default '0', PRIMARY KEY (`id_com`), FULLTEXT KEY `text_com` (`text_com`), FULLTEXT KEY `text_com_2` (`text_com`) ) TYPE=MyISAM AUTO_INCREMENT=128 ; And here is two attempts below that Ive tried thus far to the best of my ability: attept 1: mysql_select_db($database_connBlog, $connBlog); $query_rsComments = sprintf(SELECT blg_comment_com.idart_com, blg_user_usr.username_usr, blg_comment_com.text_com, blg_comment_com.date_com, blg_comment_com.valid_com FROM (blg_comment_com INNER JOIN blg_user_usr ON blg_user_usr.id_usr=blg_comment_com.idusr_com) WHERE blg_comment_com.idart_com=%s AND blg_comment_com.valid_com=1 ORDER BY date_com ASC, $KTColParam1_rsComments); $rsComments = mysql_query($query_rsComments, $connBlog) or die(mysql_error()); $row_rsComments = mysql_fetch_assoc($rsComments); $totalRows_rsComments = mysql_num_rows($rsComments); attempt 2: mysql_select_db($database_connBlog, $connBlog); $query_rsComments = SELECT idart_com, COUNT(id_com) FROM blg_comment_com GROUP BY idart_com; $rsComments = mysql_query($query_rsComments, $connBlog) or die(mysql_error()); $row_rsComments = mysql_fetch_assoc($rsComments); $totalRows_rsComments = mysql_num_rows($rsComments); I am trying to have a field in my main page that says 'comments(some number)'. Right now it says only 'comments'. I know one thing about the two attempts above, my problem is on the line of $query_rsComments and the commands placed to the database. Totaling up the values for $totalRows_rsComments and everything else I can do, but I'm not getting correct response from the base. What is the correct communication for the tables, or where am I going wrong with the two above attempts? Thank you, -Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Hallo, Sorry to ask this questions, but are you confident that the action clauses work properly, i.e. the tables were created correctly? In the case of a crash i need to restore the dump including the on delete/update clauses. What do you see when you execute show create table groups? CREATE TABLE `groups`( ( the same as mysqldump shows - but without 'on delete/update' ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups' Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Hi Joerg, All, I would like to have something like: Country Type20042005 -- GermanySales13357 19843 Belgium Sales12224 16767 France Sales15443 16602 Un. States Sales11995 14332 Japan Sales14234 13364 Rest Sales17663 12563 -- if a user requires a Top-5 selection for 2005, and where Totals of both 2004 and 2005 are 100%. Regards, Cor - Original Message - From: Joerg Bruehe [EMAIL PROTECTED] To: Shawn Green [EMAIL PROTECTED]; C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 2:17 PM Subject: Re: Top N selections + rest row Hi Shawn, Cor, all! Shawn Green wrote: --- C.R.Vegelin [EMAIL PROTECTED] wrote: Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html I checked it here: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html It seems that WITH ROLLUP is not adequate for Cor's needs, see this quote: | LIMIT can be used to restrict the number of rows returned to the | client. LIMIT is applied after ROLLUP, so the limit applies against | the extra rows added by ROLLUP. Cor, what about a UNION? Untested: ( Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25 ) UNION ( SELECT World, Sum(Sales) AS Sales From myTable Where Year=2005 ) ; Note the extra parentheses, according to the manual they are needed to ensure that the limit is applied to the first select only. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
Chris Sansom schrieb: As a relative newbie, and an almost total newbie to the use of left joins, I'm aware that there's some difference in the way joins work between MySQL 3.x and 5.x, but in my ignorance I can't figure out what the heck it is from reading the 'upgrading' pages on dev.mysql.com. Updating is always such a bad idea ;P Do you know: never touch a running system? ^_^ This worked like a charm (with fulltext indices on the text fields being searched in those five tables) in 3.23.x, but now it falls over and finds nobody at all in 5.0.19. The rest of the search is fine - there are various selects and checkboxes on which you can search and as long as I type nothing into the catch-all it behaves perfectly, but as soon as I do I get a zero result. (The whole bit with the left joins only gets added to the query if there's something in the catch-all.) And you don't see any misdone queries when you echo them, right? Hope you checked that. The other major change is that I'm now using the utf8 charset throughout the database and scripts, whereas before, with 3.23 not supporting it, I was utf8_decode()ing everything that went to MySQL and utf8_encode()ing everything that came out of it. I did try putting back the utf8_decode() round the catch-all search string, but (as I expected) it made no difference. ENCODE = NOT CODED into CODED DECODE = CODED into NOT CODED So encode the input into query and encode it afterwards :) At first I thought the upgrade or utf8 might be having some effect on the way like '%...%' works, but another simpler search uses that and it's fine. So in simple words. You tried also to query the Table without encoding it first into UTF-8? The whole point of having five separate tables for those elements is that guides can record their information in a number of languages, so there's a row per guide per language in each table - or maybe none at all (not so many guides offer lectures, for example). I want users to be able to find text in any of the languages on offer. Where am I going wrong? There are various, and the main spot here is the ENCODING of UTF-8. More infos will be great. It surely is tricky. select [fields I want to display] from guides as g left join biography as b on b.guide_id = g.id left join interests as i on i.guide_id = g.id left join tours as t on t.guide_id = g.id left join walks as w on w.guide_id = g.id left join lectures as l on l.guide_id = g.id where show_on_web = '1' and (b.biography like '%olympic%' or i.interests like '%olympic%' or t.tours like '%olympic%' or w.walks like '%olympic%' or l.lectures like '%olympic%') order by ... I am not quite sure but using biography.guide_id instead of b.guide_id would probably solve the problem. I know that using aliases in WHERE clauses don't work really good, so try this also please. 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]
Re: Accumilating Blog Comments
-Patrick schrieb: Hi Folks, Here is the table for the articles: CREATE TABLE `blg_article_art` ( `id_art` int(11) NOT NULL auto_increment, `idtop_art` int(11) NOT NULL default '0', `title_art` varchar(100) NOT NULL default '', `description_art` blob NOT NULL, `text_art` longtext NOT NULL, `date_art` datetime default NULL, PRIMARY KEY (`id_art`) ) TYPE=MyISAM AUTO_INCREMENT=117 ; Here is the table for the comments: CREATE TABLE `blg_comment_com` ( `id_com` int(11) NOT NULL auto_increment, `idart_com` int(11) NOT NULL default '0', `text_com` text NOT NULL, `idusr_com` int(11) NOT NULL default '0', `date_com` datetime default NULL, `time_com` time default NULL, `valid_com` tinyint(4) NOT NULL default '0', PRIMARY KEY (`id_com`), FULLTEXT KEY `text_com` (`text_com`), FULLTEXT KEY `text_com_2` (`text_com`) ) TYPE=MyISAM AUTO_INCREMENT=128 ; And here is two attempts below that Ive tried thus far to the best of my ability: attept 1: mysql_select_db($database_connBlog, $connBlog); $query_rsComments = sprintf(SELECT blg_comment_com.idart_com, blg_user_usr.username_usr, blg_comment_com.text_com, blg_comment_com.date_com, blg_comment_com.valid_com FROM (blg_comment_com INNER JOIN blg_user_usr ON blg_user_usr.id_usr=blg_comment_com.idusr_com) WHERE blg_comment_com.idart_com=%s AND blg_comment_com.valid_com=1 ORDER BY date_com ASC, $KTColParam1_rsComments); $rsComments = mysql_query($query_rsComments, $connBlog) or die(mysql_error()); $row_rsComments = mysql_fetch_assoc($rsComments); $totalRows_rsComments = mysql_num_rows($rsComments); attempt 2: mysql_select_db($database_connBlog, $connBlog); $query_rsComments = SELECT idart_com, COUNT(id_com) FROM blg_comment_com GROUP BY idart_com; this looks right so far $rsComments = mysql_query($query_rsComments, $connBlog) or die(mysql_error()); $row_rsComments = mysql_fetch_assoc($rsComments); $totalRows_rsComments = mysql_num_rows($rsComments); Please. It will be a lot easier without the PHP around it when you post a MySQL related Problem. Most people here are coding in JAVA for example and don't get those functions at all. What does echo $totalrows_rscomments give? I am trying to have a field in my main page that says 'comments(some number)'. Right now it says only 'comments'. I know one thing about the two attempts above, my problem is on the line of $query_rsComments and the commands placed to the database. Totaling up the values for $totalRows_rsComments and everything else I can do, but I'm not getting correct response from the base. What is the correct communication for the tables, or where am I going wrong with the two above attempts? -- 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]
Re: Stumped again by joins
At 15:56 +0200 25/4/06, Barry wrote: Updating is always such a bad idea ;P Do you know: never touch a running system? ^_^ Hmmm... And you don't see any misdone queries when you echo them, right? Hope you checked that. Yes, they look just fine - in any case they're unchanged from when it was working perfectly in 3.23 ENCODE = NOT CODED into CODED DECODE = CODED into NOT CODED So encode the input into query and encode it afterwards :) Yes, tried that - no good. In any case... At first I thought the upgrade or utf8 might be having some effect on the way like '%...%' works, but another simpler search uses that and it's fine. ...there's no en/decoding involve there and it works fine. So in simple words. You tried also to query the Table without encoding it first into UTF-8? With and without. But anyway, as I understand it, something like 'olympic' or 'london' (another thing I'm test-searching for because just about every guide mentions London somewhere in their biography - they're London guides, see? :-) ) is the same whether or not it's encoded... no? There are various, and the main spot here is the ENCODING of UTF-8. That's more or less the conclusion I've come to as well, but I can't seem to make a difference whichever way round I do it. I'm also wondering now if it might be a PHP issue after all - something I've missed about form input, but I realise this list isn't the right place to follow that up. More infos will be great. It surely is tricky. Yep. I am not quite sure but using biography.guide_id instead of b.guide_id would probably solve the problem. I know that using aliases in WHERE clauses don't work really good, so try this also please. OK, I have, and it still makes no difference. Anyway, I reiterate: it worked perfectly in 3.23.x - surely something basic like this couldn't have got /worse/ through the version upgrades? And for what it's worth I tried taking the 'as' out too, which I gather is now optional - no diff. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Star Wars won't work. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNODB database size
Hello I have inherited an INNODB database. I am new to MySQL and may not be describing my problem correctly so any suggestions or questions are welcome. I have data files named ib_data_00 through ib_data_14 all of which are 2.0G. I also have ib_data_15 which is 26G. I am receiving errors saying that the innodb space is full. I know that the space isn't full because it is still working. I am wondering how I can get a true size of the space used. I would like to get back into the 2G segments. Any suggestions. Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Accumilating Blog Comments
Sorry about that.. $totalrows_rsComments gives a value of 0. But no matter what I do I can't seem to alter it. It stays at zero. -Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB database size
Todd you need to look at how InnoDB is configured and learn a bit about how Innodb uses and manages its tablespace. if you look in the my.ini options file you should see how innodb is set up for your installation. Take a look at the link below that explains how InnoDB can be set up: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html Regards Pure Web Solution http://www.purewebsolution.co.uk PHP, MYSQL, Web Design Web Services Todd Smith [EMAIL PROTECTED] wrote: Hello I have inherited an INNODB database. I am new to MySQL and may not be describing my problem correctly so any suggestions or questions are welcome. I have data files named ib_data_00 through ib_data_14 all of which are 2.0G. I also have ib_data_15 which is 26G. I am receiving errors saying that the innodb space is full. I know that the space isn't full because it is still working. I am wondering how I can get a true size of the space used. I would like to get back into the 2G segments. Any suggestions. Todd Pure Web Solution http://www.purewebsolution.co.uk PHP, MYSQL, Web Design Web Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB database size
Look at your my.cnf for a configuration directive called 'innodb_data_file_path'. This is where you configure the files for the innodb table space. The last one is probably an auto-grow. My guess is that every time it complains, it's just added 8MB to the file. If you remove the auto-grow (and I can't remember what the configuration name is for that.. sorry), and add another 2GB file, it should be fine. You'll want to keep an eye on it though, so you can add 2GB files more into the future. That is, unless you add the autogrow to the last file. The mysql online docs have lots of good info on this. On 4/25/06, Todd Smith [EMAIL PROTECTED] wrote: Hello I have inherited an INNODB database. I am new to MySQL and may not be describing my problem correctly so any suggestions or questions are welcome. I have data files named ib_data_00 through ib_data_14 all of which are 2.0G. I also have ib_data_15 which is 26G. I am receiving errors saying that the innodb space is full. I know that the space isn't full because it is still working. I am wondering how I can get a true size of the space used. I would like to get back into the 2G segments. Any suggestions. Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error wiht VB 5 and MySQL
On 4/24/06, Gabriel Mahiques [EMAIL PROTECTED] wrote: but, when the fields are float type, this error don't happen. When the fileds are float the result is the correct (if I have 1.2569 in the table, in the application I see 1.2569.) Ok, so, I didn't quite understand. Let me see, you can see 1.2569 correctly, but can't see 1,2569 (note the comma instead of the dot). Isn't this a problem with your app? I believe MySQL is returning the correct values, your app is just showing them incorrectly, check your language manual about WHY the appears and the causes for it. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is this? -- errno=2006 errmsg=Server gone
On 4/17/06, Martin Olsson [EMAIL PROTECTED] wrote: This is software I use: D:\MDmysql --version mysql Ver 14.7 Distrib 4.1.12, for Win32 (ia32) Upgrade your MySQL version to the last 4.1.x release. I had problems like this when issuing specific queries on 4.1.12. The problem was gone after an upgrade, try it and let us know. C:\Apache\Apache2\binApache.exe -v Server version: Apache/2.0.54 Server built: Apr 16 2005 14:25:31 C:\Apache\Apache2\binver Microsoft Windows 2000 [Version 5.00.2195] Exactly which parameters can I tweak to fix this error (i.e. errno=2006 errmsg=Server gone)?? At this stage _any_ help/hint/guess would be really appreciated. Post your query, also, try the same query at the console and see if mysql crashes for some reason. The problem is that MOST clients don't have a crash recovery system that reconnect and resend the query on a server error, but the console has, so you'll see exactly what is happening. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accumilating Blog Comments
On 4/25/06, -Patrick [EMAIL PROTECTED] wrote: Hi Folks, Here is the table for the articles: CREATE TABLE `blg_article_art` ( `id_art` int(11) NOT NULL auto_increment, `idtop_art` int(11) NOT NULL default '0', `title_art` varchar(100) NOT NULL default '', `description_art` blob NOT NULL, `text_art` longtext NOT NULL, `date_art` datetime default NULL, PRIMARY KEY (`id_art`) ) TYPE=MyISAM AUTO_INCREMENT=117 ; Here is the table for the comments: CREATE TABLE `blg_comment_com` ( `id_com` int(11) NOT NULL auto_increment, `idart_com` int(11) NOT NULL default '0', `text_com` text NOT NULL, `idusr_com` int(11) NOT NULL default '0', `date_com` datetime default NULL, `time_com` time default NULL, `valid_com` tinyint(4) NOT NULL default '0', PRIMARY KEY (`id_com`), FULLTEXT KEY `text_com` (`text_com`), FULLTEXT KEY `text_com_2` (`text_com`) ) TYPE=MyISAM AUTO_INCREMENT=128 ; And here is two attempts below that Ive tried thus far to the best of my ability: attept 1: mysql_select_db($database_connBlog, $connBlog); $query_rsComments = sprintf(SELECT blg_comment_com.idart_com, blg_user_usr.username_usr, blg_comment_com.text_com, blg_comment_com.date_com, blg_comment_com.valid_com FROM (blg_comment_com INNER JOIN blg_user_usr ON blg_user_usr.id_usr=blg_comment_com.idusr_com) WHERE blg_comment_com.idart_com=%s AND blg_comment_com.valid_com=1 ORDER BY date_com ASC, $KTColParam1_rsComments); $rsComments = mysql_query($query_rsComments, $connBlog) or die(mysql_error()); $row_rsComments = mysql_fetch_assoc($rsComments); $totalRows_rsComments = mysql_num_rows($rsComments); attempt 2: mysql_select_db($database_connBlog, $connBlog); $query_rsComments = SELECT idart_com, COUNT(id_com) FROM blg_comment_com GROUP BY idart_com; $rsComments = mysql_query($query_rsComments, $connBlog) or die(mysql_error()); $row_rsComments = mysql_fetch_assoc($rsComments); $totalRows_rsComments = mysql_num_rows($rsComments); I am trying to have a field in my main page that says 'comments(some number)'. Right now it says only 'comments'. I know one thing about the two attempts above, my problem is on the line of $query_rsComments and the commands placed to the database. Totaling up the values for $totalRows_rsComments and everything else I can do, but I'm not getting correct response from the base. What is the correct communication for the tables, or where am I going wrong with the two above attempts? Thank you, -Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This is not a MySQL related problem, more a PHP one. Check this: http://www.php.net/manual/en/function.mysql-num-rows.php -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get result set from stored procedure?
Hello mysql, May be, I'm a stupid... create procedure test () select 1; mysql call test(); +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.05 sec) In C: mysql=mysql_init(NULL); mysql_real_connect(mysql, NULL, User, pwd, test, MYSQL_PORT, NULL, 0); mysql_query(mysql, call test()); results to error: PROCEDURE test.test can't return a result set in the given context What's wrong? -- Best regards, Juri mailto:[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 get result set from stored procedure?
Hello Juri, Tuesday, April 25, 2006, 5:49:25 PM, you wrote: JS mysql=mysql_init(NULL); JS mysql_real_connect(mysql, NULL, User, pwd, test, MYSQL_PORT, NULL, 0); JS mysql_query(mysql, call test()); JS results to error: PROCEDURE test.test can't return a result set in the given context JS What's wrong? I've solved it: CLIENT_MULTI_STATEMENTS. Thanks to All! -- Best regards, Jurimailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accumilating Blog Comments
Daniel da Veiga wrote: On 4/25/06, -Patrick [EMAIL PROTECTED] wrote: Hi Folks, Here is the table for the articles: CREATE TABLE `blg_article_art` ( `id_art` int(11) NOT NULL auto_increment, `idtop_art` int(11) NOT NULL default '0', `title_art` varchar(100) NOT NULL default '', `description_art` blob NOT NULL, `text_art` longtext NOT NULL, `date_art` datetime default NULL, PRIMARY KEY (`id_art`) ) TYPE=MyISAM AUTO_INCREMENT=117 ; Here is the table for the comments: CREATE TABLE `blg_comment_com` ( `id_com` int(11) NOT NULL auto_increment, `idart_com` int(11) NOT NULL default '0', `text_com` text NOT NULL, `idusr_com` int(11) NOT NULL default '0', `date_com` datetime default NULL, `time_com` time default NULL, `valid_com` tinyint(4) NOT NULL default '0', PRIMARY KEY (`id_com`), FULLTEXT KEY `text_com` (`text_com`), FULLTEXT KEY `text_com_2` (`text_com`) ) TYPE=MyISAM AUTO_INCREMENT=128 ; And here is two attempts below that Ive tried thus far to the best of my ability: attept 1: mysql_select_db($database_connBlog, $connBlog); $query_rsComments = sprintf(SELECT blg_comment_com.idart_com, blg_user_usr.username_usr, blg_comment_com.text_com, blg_comment_com.date_com, blg_comment_com.valid_com FROM (blg_comment_com INNER JOIN blg_user_usr ON blg_user_usr.id_usr=blg_comment_com.idusr_com) WHERE blg_comment_com.idart_com=%s AND blg_comment_com.valid_com=1 ORDER BY date_com ASC, $KTColParam1_rsComments); $rsComments = mysql_query($query_rsComments, $connBlog) or die(mysql_error()); $row_rsComments = mysql_fetch_assoc($rsComments); $totalRows_rsComments = mysql_num_rows($rsComments); attempt 2: mysql_select_db($database_connBlog, $connBlog); $query_rsComments = SELECT idart_com, COUNT(id_com) FROM blg_comment_com GROUP BY idart_com; $rsComments = mysql_query($query_rsComments, $connBlog) or die(mysql_error()); $row_rsComments = mysql_fetch_assoc($rsComments); $totalRows_rsComments = mysql_num_rows($rsComments); I am trying to have a field in my main page that says 'comments(some number)'. Right now it says only 'comments'. I know one thing about the two attempts above, my problem is on the line of $query_rsComments and the commands placed to the database. Totaling up the values for $totalRows_rsComments and everything else I can do, but I'm not getting correct response from the base. What is the correct communication for the tables, or where am I going wrong with the two above attempts? Thank you, -Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This is not a MySQL related problem, more a PHP one. Check this: http://www.php.net/manual/en/function.mysql-num-rows.php -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- Thanks Dan. I already have that request in the processing.. - my-sql_numrows. I wrote to this list because I believe the fault lies within the communication to the database.. as far my statement for $query_rsComments.. so you think there's nothing wrong with that statement? becuase i am already familiar with mysql_num rows and have it setup just like in that link you provided. Thanks again -Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Hi Cor, all! C.R.Vegelin wrote: Hi Joerg, All, I would like to have something like: Country Type20042005 -- GermanySales13357 19843 Belgium Sales12224 16767 France Sales15443 16602 Un. States Sales11995 14332 Japan Sales14234 13364 Rest Sales17663 12563 -- if a user requires a Top-5 selection for 2005, and where Totals of both 2004 and 2005 are 100%. My impression is your requirements are slowly changing: - Originally, I read world, now I read rest. The problem IMO is that rest is difficult to compute, because you need to sum on a set difference (all rows minus the top N rows) which you can only build by first determining the top N. - You started asking for 2005, now show 2005 + 2004 (minor). If you really want top N, and the sum of all others, IMHO the best way is to do both the limitation and the summing of the other rows in your application: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC Loop over the results, display the first N, do the summing for all others, display that sum. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
At 15:56 +0200 25/4/06, Barry wrote: And you don't see any misdone queries when you echo them, right? Hope you checked that. Hi Barry I was wrong about its being a PHP issue: it's definitely a MySQL error. I realised I hadn't handled the error in such a way that I could see what it was, but now I have, so... The full query, in all its hideousness (but prettied up a bit in the formatting :-) ) is: -- select count(distinct uid) as c from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') -- and the error I get back is: Unknown column 'r.uid' in 'on clause' ...but I can assure you there is definitely a 'uid' column in aptg_guides_restricted. If I take out the 'r.' from those left joins (there's no uid in any other table mentioned here) I get basically the same error: Unknown column 'uid' in 'on clause'. And if I spell out 'aptg_guides_restricted.uid' in the joins I /still/ get the error: Unknown column 'aptg_guides_restricted.uid' in 'on clause' So what /is/ the problem here? I say again: this and /exactly/ this worked perfectly in MySQL 3.23, so there's obviously some change in syntax handling or whatever between versions. In fact, this is a preliminary query to establish the total. If there is a total, I then run this: -- select distinct uid, firstname, lastname, year_qualified, other_qualifications, guide_driverguide, guide_photo_1 from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') order by from_unixtime(unix_timestamp(guide_last_updated)) * (rand(1569933185) + ((length(guide_photo_1) 1) / 3)) desc -- ...and if I run that directly in the SQL window in phpMyAdmin, I get the same error: Unknown column 'r.uid' in 'on clause'. ¿Qué? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I'm on a seafood diet - I see food, I eat it. -- Dolly Parton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Accumilating Blog Comments
$query_rsComments = sprintf(SELECT id_com WHERE idart_com=%s ORDER BY date_com ASC, $KTColParam1_rsComments); can anyone see what Im trying to do here? basically, I want to match the id_com with an idart_com to produce a value and report back through the other chunk of code already given to output a number.. using mysql_num_rows(). But Im getting syntax and check line errors.. Any thoughts? -Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld vs. mysql-max
Hey, I got a box that is dying, it is currently running MySQL-Max, I want to move the DB from it to a box that is running just plain jane MySQL, what will happen and will it work. I know, strange but I am not sure what the Max does. Payne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Locking Question
Nigel wrote: mod_php will persist the MySQL connection holding open any lock or syncronisation token obtained through any of the three methods : begin/commit, lock/unlock tables or get_lock/release_lock. PHP does ensure that even in the event of timeouts or fatal errors any shutdown handlers registered are still executed so it is possible to clean up properly whichever method is used. http://uk.php.net/manual/en/function.register-shutdown-function.php If you use php's pdo with transactions it perform a rollback for you on abort or completion. What is a pdo? Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
Chris Sansom wrote: At 15:56 +0200 25/4/06, Barry wrote: And you don't see any misdone queries when you echo them, right? Hope you checked that. Hi Barry I was wrong about its being a PHP issue: it's definitely a MySQL error. I realised I hadn't handled the error in such a way that I could see what it was, but now I have, so... The full query, in all its hideousness (but prettied up a bit in the formatting :-) ) is: -- select count(distinct uid) as c from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') -- and the error I get back is: Unknown column 'r.uid' in 'on clause' ...but I can assure you there is definitely a 'uid' column in aptg_guides_restricted. If I take out the 'r.' from those left joins (there's no uid in any other table mentioned here) I get basically the same error: Unknown column 'uid' in 'on clause'. And if I spell out 'aptg_guides_restricted.uid' in the joins I /still/ get the error: Unknown column 'aptg_guides_restricted.uid' in 'on clause' So what /is/ the problem here? I say again: this and /exactly/ this worked perfectly in MySQL 3.23, so there's obviously some change in syntax handling or whatever between versions. Yes. 3.23 was not correct in the order of precedence. This has been answered many times here. You need to change your comma join to an inner join. select count(distinct uid) as c from aptg_guides_restricted as r inner join aptg_guides as g on g.guide_uid = r.uid left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') In fact, this is a preliminary query to establish the total. If there is a total, I then run this: -- select distinct uid, firstname, lastname, year_qualified, other_qualifications, guide_driverguide, guide_photo_1 from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') order by from_unixtime(unix_timestamp(guide_last_updated)) * (rand(1569933185) + ((length(guide_photo_1) 1) / 3)) desc -- ...and if I run that directly in the SQL window in phpMyAdmin, I get the same error: Unknown column 'r.uid' in 'on clause'. ¿Qué? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accumilating Blog Comments
2006/4/25, -Patrick [EMAIL PROTECTED]: $query_rsComments = sprintf(SELECT id_com WHERE idart_com=%s ORDER BY date_com ASC, $KTColParam1_rsComments); can anyone see what Im trying to do here? basically, I want to match the id_com with an idart_com to produce a value and report back through the other chunk of code already given to output a number.. using mysql_num_rows(). But Im getting syntax and check line errors.. Any thoughts? -Patrick You should use count(*) to get the rows number, if it's the only thing you want. Why is your inner join enclosed in parenthesis ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
2006/4/25, Chris Sansom [EMAIL PROTECTED]: At 15:56 +0200 25/4/06, Barry wrote: And you don't see any misdone queries when you echo them, right? Hope you checked that. Hi Barry I was wrong about its being a PHP issue: it's definitely a MySQL error. I realised I hadn't handled the error in such a way that I could see what it was, but now I have, so... The full query, in all its hideousness (but prettied up a bit in the formatting :-) ) is: -- select count(distinct uid) as c from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') -- and the error I get back is: Unknown column 'r.uid' in 'on clause' in 5.0.12 comma precedence was changed : http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html so try with parenthesis, your implicit join and left join should then works correctly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump and table exclusion
Hi, I've read the manual, but it seems there is not the option I'm looking for. I would like to dump all tables but one. Is that possible ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
I'm so confused. I'm finally getting around to needing to do a 'store locator' thing. I procured myself some zip/lat/long databases from various places. Then I noticed that for the same zip code, I got different values in different databases??!!! So for a sanity check, I decided to look online and punch in some to see what the real lat/long should be. Well, different sites give different values, and not only are they slightly off, but sometimes they're _positive_ or _negative_!? UGH! http://www.zipinfo.com/cgi-local/zipsrch.exe?ll=llzip=98119Go=Go Seattle WA 98119 47.6388 122.3700 http://www.census.gov/cgi-bin/gazetteer?city=state=zip=98119 Location: 47.637917 N, 122.364272 W http://www.csgnetwork.com/cgi-bin/zipcodes.cgi?Zipcode=98119 http://www.myzipdb.com/mzdb_demo.php http://www.nearby.org.uk/coord.cgi?p=98119 City Seattle State Abbreviation WA State Washington Latitude 47.638770 Longitude -122.366941 So I try the east coast: http://www.zipinfo.com/cgi-local/zipsrch.exe?ll=llzip=14526Go=Go Penfield NY 14526 43.1532 77.4485 http://www.census.gov/cgi-bin/gazetteer?city=state=zip=14526 Location: 43.139638 N, 77.456043 W http://www.csgnetwork.com/cgi-bin/zipcodes.cgi?Zipcode=14526 http://www.nearby.org.uk/coord.cgi?p=14526f=full City Penfield State Abbreviation NY State New York Latitude 43.145342 Longitude -77.458499 Please help me to understand WTF is going on? Which lat/long do I need? How does the negatives effect the magic formulas to calculate distances? (I'm no trig wiz). I assume that the more precise the numbers, the better the results will be. To what decimal point should I be concerned with? - Use the value in degrees and decimals of degrees, not minutes, seconds, etc... For the Western longitude, use the negative value. For instance the longitude and latitude of my zip code is -111.64338 and 33.436767. There is already a PHP Class to do that job, never tested it, but I programmed this already for SQL Server, I am sure that doing it for MySQL would not be that complicated. It does not matter if you are not 100% accurate, you are usually off by one mile or two, but it gives an idea. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
[snip] I procured myself some zip/lat/long databases from various places. Then I noticed that for the same zip code, I got different values in different databases??!!! [/snip] Latitudes and longitudes are often represented based on their location relative to the equator and the prime meridian; | pos lat | pos lat neg long | pos long | | --- | neg lat | neg lat neg long | pos long | | Sometimes the coordinates are based on positive distances (non-standard lat/long) from the prim meridian and equator, yielding a pai of positive numbers for each coordinate that would not be the same numbers as given by the standard system. Google Maps understands the standard system well, I do not know if it understands the non-standard system -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with SELECT BETWEEN
I want to create a SELECT statement using BETWEEN like: SELECT * FROM mytable WHERE myfield BETWEEN value_1 AND value_2. The field I'm applying my BETWEEN clause is a varchar. Now, if value_1 and value_2 are numbers the select statement works as is. If value_1 and value_2 are characters I need to enclose them with apostrophes like: SELECT * FROM mytable WHERE myfield BETWEEN 'value_1' AND 'value_2'. If the appropriate format (enclosing or not enclosing with apostrophes) is not followed the query fails. Is there a way to generalize my SELECT statement so value_1 and value_2 can be numbers or characters? Thanks cw -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
Hello, Not wanting to hijack this thread, but what is the best source for a zip code database? The best free one I could find was from census data from the last census. Sometimes it won't recognize all the zips from large cities. Thanks, R -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Return virtual records
Thanks for the response Shawn but there's nothing covert here. I really need a list of partnumbers based on the Sum of that part in the table. My users will be marking off the parts in the list and if there a more than one of a partnumber then it needs to show up in the list more than once. - Thanks again Shawn Green [EMAIL PROTECTED] 4/12/06 12:15:56 PM --- Ed Reed [EMAIL PROTECTED] wrote: Thanks for the response but neither one of the responses I've received does exactly what I need since they don't return multiple rows. Are there any other ideas out there? Thanks Jay Blanchard [EMAIL PROTECTED] 4/7/06 12:37:32 PM [snip] Anyone have an idea on this? Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part# Qty 1254 5 1414 2 14758 1 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() [/snip] Please do not hijack threads, open a new e-mail and send it to the list address. SELECT REPEAT(Part#, count(Part#)) FROM table WHERE Part# = '1254' What you are asking MySQL to do is not a normal request. There are probably better ways to solve your issue than by creating fake or virtual data. As was posted before, what is the real reason you want to auto-generate separate rows of data? If we understood your REAL problem (not your request, we understand that) we could probably help you find a faster, more robust solution than the one you are proposing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: mysqldump and table exclusion
There is an option to ignore tables from being dumped --ignore-table=*db_name.tbl_name* Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times. so you would say #mysqldump -uuser -ppassword --ignore-table = db_name.tbl_name db_name Kishore Jalleda http://kjalleda.googlepages.com/ On 4/25/06, Philippe Poelvoorde [EMAIL PROTECTED] wrote: Hi, I've read the manual, but it seems there is not the option I'm looking for. I would like to dump all tables but one. Is that possible ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SELECT BETWEEN
On Tuesday 25 April 2006 09:33 am, Chris wrote: I want to create a SELECT statement using BETWEEN like: SELECT * FROM mytable WHERE myfield BETWEEN value_1 AND value_2. The field I'm applying my BETWEEN clause is a varchar. If the appropriate format (enclosing or not enclosing with apostrophes) is not followed the query fails. Is there a way to generalize my SELECT statement so value_1 and value_2 can be numbers or characters? What happens if you enclose them in quotes no matter what? All parts of ASCII considered that should still work. If not then maybe a stored procedure, but I'm not as savy with stored procedures to give a definite answer on that. Thanks cw -- Chris White PHP Programmer / DB Monkey in training Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
At 11:10 -0500 25/4/06, gerald_clark wrote: Yes. 3.23 was not correct in the order of precedence. This has been answered many times here. Sorry - I haven't been on the list all that long. You need to change your comma join to an inner join. Lovely! That's it - many thanks. At 17:15 +0100 25/4/06, Philippe Poelvoorde wrote: in 5.0.12 comma precedence was changed : http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html I did look at this but, my understanding of joins still being somewhat shaky, I didn't take in its full implications. so try with parenthesis, your implicit join and left join should then works correctly. In fact it's easier in my script to use inner join as Gerald suggested. Many thanks for your patience though. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I think I think; therefore I think I am. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Datatype MEDIUMTEXT
do MySQL store this in fixed-size or variable-sized fisk space? I need to be able to store large fields, but usually the size will be just a few kilobytes. - This sig is dedicated to the advancement of Nuclear Power Tommy Nordgren [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is not not valid floating point for field
Hi friends. I have another problems with database migration from MySQL 4 to MySQL 5. We have many applications developments in Visual Basic 5. Under Mysql 4 if I put , (comma) for decimal value (we are in Argentina and we use comma for decimal separation) and war a valid value. When we migrated to MySQL 5, the application give us the error is not not valid floating point for field. They acept only . (point) for decimal value, but we need that to accept comma. Where do I have to configure this point? Mysql 5.20 Operating Systema: Linux Mandrake -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and table exclusion
2006/4/25, Kishore Jalleda [EMAIL PROTECTED]: There is an option to ignore tables from being dumped --ignore-table=db_name.tbl_name Excellent :-) My man page is not up-to-date, and wasn't showing it. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SELECT BETWEEN
--- Chris [EMAIL PROTECTED] wrote: I want to create a SELECT statement using BETWEEN like: SELECT * FROM mytable WHERE myfield BETWEEN value_1 AND value_2. The field I'm applying my BETWEEN clause is a varchar. Now, if value_1 and value_2 are numbers the select statement works as is. If value_1 and value_2 are characters I need to enclose them with apostrophes like: SELECT * FROM mytable WHERE myfield BETWEEN 'value_1' AND 'value_2'. If the appropriate format (enclosing or not enclosing with apostrophes) is not followed the query fails. Is there a way to generalize my SELECT statement so value_1 and value_2 can be numbers or characters? Thanks cw Whether you compare against string literals or numeric literals (quotes or no quotes) depends on the data type of the field `myfield`. Strings will be automagically converted to numbers if necessary but that's a performance killer if you have to run this query frequently. Basically, the answer is no because what you are comparing it against may not be both numeric AND text a the same time. Practically, if you quote your numbers, the autoconversion should kick in and you should be comparing numbers to numbers again. Please read for more details: http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html http://dev.mysql.com/doc/refman/5.0/en/number-syntax.html Shawn Green __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import from unknown format (.bdd .ind .mor .ped)
Greetings. I'm building an application and I need to import data to mysql from a db format I don't know. Unfortunately the person in charge of the data won't be reachable for the next 2 weeks and I want to continue my work. I was wondering if anyone knows the format extensions like: .bdd .ind .mor .ped Thanks in advance. Apologies for my bad English. Pedro.
Re: Accumilating Blog Comments
Philippe Poelvoorde wrote: 2006/4/25, -Patrick [EMAIL PROTECTED]: $query_rsComments = sprintf(SELECT id_com WHERE idart_com=%s ORDER BY date_com ASC, $KTColParam1_rsComments); can anyone see what Im trying to do here? basically, I want to match the id_com with an idart_com to produce a value and report back through the other chunk of code already given to output a number.. using mysql_num_rows(). But Im getting syntax and check line errors.. Any thoughts? -Patrick You should use count(*) to get the rows number, if it's the only thing you want. Why is your inner join enclosed in parenthesis ?? Or, can I use count to make statements inside the query? I almost have it... but it's off because now it's gathering every id_com and putting them all in one idart_com.. not to it's relative idart_com.. very frustrating.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Return virtual records
--- Ed Reed [EMAIL PROTECTED] wrote: Thanks for the response Shawn but there's nothing covert here. I really need a list of partnumbers based on the Sum of that part in the table. My users will be marking off the parts in the list and if there a more than one of a partnumber then it needs to show up in the list more than once. - Thanks again Shawn Green [EMAIL PROTECTED] 4/12/06 12:15:56 PM --- Ed Reed [EMAIL PROTECTED] wrote: Thanks for the response but neither one of the responses I've received does exactly what I need since they don't return multiple rows. Are there any other ideas out there? Thanks Jay Blanchard [EMAIL PROTECTED] 4/7/06 12:37:32 PM [snip] Anyone have an idea on this? Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part# Qty 1254 5 1414 2 14758 1 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() [/snip] Please do not hijack threads, open a new e-mail and send it to the list address. SELECT REPEAT(Part#, count(Part#)) FROM table WHERE Part# = '1254' What you are asking MySQL to do is not a normal request. There are probably better ways to solve your issue than by creating fake or virtual data. As was posted before, what is the real reason you want to auto-generate separate rows of data? If we understood your REAL problem (not your request, we understand that) we could probably help you find a faster, more robust solution than the one you are proposing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Then, no, without some form of looping control (v5.0+ and SPROCs) you have no chance of splitting a single row into clones of itself. I think, though, that it will be more efficient to do what you want to do client-side (in your application) than it will be to try it server-side (using SQL statements). Shawn Green __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Undeleting an entire Database?
Hey guys, Well, I was unable to find anything on Google and I don't expect to find a miracle... but I figured why not. On Sunday morning I was trying to delete a Table through PHPMyAdmin and well... I dropped the entire DB without backing it up before. Stupid mistake, I know. So, I'm using Fedora Core OS/Linux and I was wondering if there was any way to undelete this db? I lost about 350 new members on my site + all kinds of blogs, comments, messages, etc. Is there ANY way to get this info back? If not, It's not a huge deal... I suppose. I just wanted to see if anyone might know of any special ways to get some of the data back. Thanks! - Clint Lenard
RE: Datatype MEDIUMTEXT
http://dev.mysql.com/doc/refman/5.0/en/blob.html http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html -Original Message- From: Tommy Nordgren [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 1:27 PM To: mysql@lists.mysql.com Subject: Datatype MEDIUMTEXT do MySQL store this in fixed-size or variable-sized fisk space? I need to be able to store large fields, but usually the size will be just a few kilobytes. - This sig is dedicated to the advancement of Nuclear Power Tommy Nordgren [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: (Errcode: 13) after moving data directory
boll wrote: Dominik Klein wrote: Did you check FAT-permissions? When mounting a FAT-partition, you have to set explicit permissions while mounting as FAT does not understand the unix permission concept. Try to mount this way: mount -t vfat -o uid=mysql,gid=mysql,rw,umask=007 /dev/[yourdevicename] /your/mountpoint Hi Dominik, In /etc/fstab the partition is mounted with this line: /dev/hdb2/mnt/FATvfatuid=27,gid=27,umask=000 0 0 ...which I think is the same as what you recommend (uid 27 is mysql) . What I really don't understand is: Why mysqld will start up and use the dataq on the FAT partition (as I want it to do) if I start it with mysqld_safe, but when I boot the computer or try, as root, service mysqld start, it fails to start with these log errors: 060421 08:43:10 mysqld started 060421 8:43:11 [Warning] Can't create test file /mnt/FAT/mysqldata/localhost.lower-test /usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' (Errcode: 13) 060421 8:43:11 [ERROR] Aborting I'm guessing that since mysqld_safe runs as user mysql, maybe mysqld runs as a different user? How would I find that out? I will keep reading the manual, but will be grateful for any ideas. I was able to get mysqld to start at boot, by disabling selinux. Not happy to do it that way, but couldn't find any alternative. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb file per table
Hi all, I've inherited an innodb database that is configured like: innodb_file_per_table innodb_data_file_path = ibdata1:3000M;ibdata2:3000M;ibdata3:3000M;ibdata4:3000M:autoextend Um, doesn't this allocate 12G that winds up being unused, since innodb_file_per_table is set? If so, what is the correct way to reclaim the 12G? Thanks! Regards, Rich Duzenbury -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
Chris, select count(distinct uid) as c from aptg_guides_restricted as r, aptg_guides as g ...snip... See the extensive notes on comma and SQL2003 joins at http://dev.mysql.com/doc/refman/5.1/en/join.html. Lose the comma join, make it a SQL2003 (explicit inner) join. PB - At 15:56 +0200 25/4/06, Barry wrote: And you don't see any misdone queries when you echo them, right? Hope you checked that. Hi Barry I was wrong about its being a PHP issue: it's definitely a MySQL error. I realised I hadn't handled the error in such a way that I could see what it was, but now I have, so... The full query, in all its hideousness (but prettied up a bit in the formatting :-) ) is: -- select count(distinct uid) as c from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') -- and the error I get back is: Unknown column 'r.uid' in 'on clause' ...but I can assure you there is definitely a 'uid' column in aptg_guides_restricted. If I take out the 'r.' from those left joins (there's no uid in any other table mentioned here) I get basically the same error: Unknown column 'uid' in 'on clause'. And if I spell out 'aptg_guides_restricted.uid' in the joins I /still/ get the error: Unknown column 'aptg_guides_restricted.uid' in 'on clause' So what /is/ the problem here? I say again: this and /exactly/ this worked perfectly in MySQL 3.23, so there's obviously some change in syntax handling or whatever between versions. In fact, this is a preliminary query to establish the total. If there is a total, I then run this: -- select distinct uid, firstname, lastname, year_qualified, other_qualifications, guide_driverguide, guide_photo_1 from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') order by from_unixtime(unix_timestamp(guide_last_updated)) * (rand(1569933185) + ((length(guide_photo_1) 1) / 3)) desc -- ...and if I run that directly in the SQL window in phpMyAdmin, I get the same error: Unknown column 'r.uid' in 'on clause'. ¿Qué? -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 4/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accumilating Blog Comments
-Patrick wrote: $query_rsComments = sprintf(SELECT id_com WHERE idart_com=%s ORDER BY date_com ASC, $KTColParam1_rsComments); No FROM clause. Also, don't you need single quotes around %s? PB - can anyone see what Im trying to do here? basically, I want to match the id_com with an idart_com to produce a value and report back through the other chunk of code already given to output a number.. using mysql_num_rows(). But Im getting syntax and check line errors.. Any thoughts? -Patrick -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 4/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
Thanks for the graph. So are you saying that I should use the database that has the negative values, and not the one that uses positive values? I'm in the USA. I don't care about anywhere else (for my location needs that is). -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Latitudes and longitudes are often represented based on their location relative to the equator and the prime meridian; | pos lat | pos lat neg long | pos long | | --- | neg lat | neg lat neg long | pos long | | Sometimes the coordinates are based on positive distances (non-standard lat/long) from the prim meridian and equator, yielding a pai of positive numbers for each coordinate that would not be the same numbers as given by the standard system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
My confusion is that I have some formulas to plug in these values, but it seems to me that if I use the wrong set of data, my zipcodes will be wrong too. I also don't understand why there is even such a difference. I can understand a few decimal points being different, but I don't understand how they are positive and negative, when it's supposed to be based upon the equator and the prime meridian. Since they're US zipcodes, it's not like we're in different parts of the world or anything. -Original Message- From: Gmail User [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 6:03 AM To: mysql@lists.mysql.com Subject: RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude) On Tue, 2006-04-25 at 00:43 -0700, Daevid Vincent wrote: So for a sanity check, I decided to look online and punch in some to see what the real lat/long should be. Well, different sites give different values, and not only are they slightly off, but sometimes they're _positive_ or _negative_!? UGH! Not sure what your confusion is. It is a matter of notation. The negative value represents West where it is negative (as would be the East; note how there is no W mentioned there). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
[snip] Thanks for the graph. So are you saying that I should use the database that has the negative values, and not the one that uses positive values? I'm in the USA. I don't care about anywhere else (for my location needs that is). [/snip] Yes, that would be using the proper notation for lat and long. To see it in action take a look at http://maps.google.com. Enter an address with a city, state and zip code (such as your own) and then click 'Link to This Page'. Look at the URL and you will see properly notated coordinates. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help for my jointure
I have a table confs like this: id int 5 auto_increment primary key; conf text; and another table conf_ip like this: id int 5 auto_increment primary key; conf_id int 5; ==foreing key of confs ip varchar 150; I would like to select id, conf from confs where ip!='some val'; how can I do this? thanx in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
Daevid Vincent wrote: My confusion is that I have some formulas to plug in these values, but it seems to me that if I use the wrong set of data, my zipcodes will be wrong too. I also don't understand why there is even such a difference. I can understand a few decimal points being different, but I don't understand how they are positive and negative, when it's supposed to be based upon the equator and the prime meridian. Hasn't that already been explained here? Sign is entirely a matter of convenience and convention. PB -Original Message- From: Gmail User [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 25, 2006 6:03 AM To: mysql@lists.mysql.com Subject: RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude) On Tue, 2006-04-25 at 00:43 -0700, Daevid Vincent wrote: So for a sanity check, I decided to look "online" and punch in some to see what the "real" lat/long should be. Well, different sites give different values, and not only are they "slightly" off, but sometimes they're _positive_ or _negative_!? UGH! Not sure what your confusion is. It is a matter of notation. The negative value represents West where it is negative (as would be the East; note how there is no W mentioned there). No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 4/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
You could further explain the lats and longs being slightly off by the use of a different datum. There are many many datums utilised by different geographical/geological authorities. This difference could become quite large dependent upon the datum used. As gmail user as noted, negative = West and South, positive = North and East. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 April 2006 11:17 AM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude) Daevid Vincent wrote: My confusion is that I have some formulas to plug in these values, but it seems to me that if I use the wrong set of data, my zipcodes will be wrong too. I also don't understand why there is even such a difference. I can understand a few decimal points being different, but I don't understand how they are positive and negative, when it's supposed to be based upon the equator and the prime meridian. Hasn't that already been explained here? Sign is entirely a matter of convenience and convention. PB -Original Message- From: Gmail User [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 6:03 AM To: mysql@lists.mysql.com Subject: RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude) On Tue, 2006-04-25 at 00:43 -0700, Daevid Vincent wrote: So for a sanity check, I decided to look online and punch in some to see what the real lat/long should be. Well, different sites give different values, and not only are they slightly off, but sometimes they're _positive_ or _negative_!? UGH! Not sure what your confusion is. It is a matter of notation. The negative value represents West where it is negative (as would be the East; note how there is no W mentioned there).
Re: mysqldump and foreign keys
Nico Rittner wrote: Hallo, Sorry to ask this questions, but are you confident that the action clauses work properly, i.e. the tables were created correctly? In the case of a crash i need to restore the dump including the on delete/update clauses. What do you see when you execute show create table groups? CREATE TABLE `groups`( ( the same as mysqldump shows - but without 'on delete/update' ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups' Thanks, Nico Hi Nico, what I meant was: are you sure that the tables which are in your database are defined correctly, i.e. have the actions? The fact that show create table does not display them, implies that they might have been lost somewhere. Can you post the statement which has been used to create the groups table in the first place. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help for my jointure
First of all, I'm going to guess that English is not your first language and tell you that jointure is not the word normally to describe the process of combining two tables in a database: the word you want is joining. Second, there are many kinds of joins and you haven't specified which kind you want to do. If you look in the MySQL manual, you will see that there are cross joins, inner joins, straight joins, natural joins, left joins, right joins, etc. You need to figure out which kind of join you want because your decision will affect the way you need to write your SQL. Third, the manual gives some information and examples on how to do joins. You haven't specified which version of MySQL you are using but if it is Version 5.0, the topic you want is http://dev.mysql.com/doc/refman/5.0/en/join.html. If you are using a different version, you can find the various editions on this page http://dev.mysql.com/doc/. Fourth, the manual does _not_ do a very good job of explaining the differences between the types of joins. This has been a known deficiency for some time and I am disappointed that this has (apparently) not been addressed yet. I wish I could suggest a good place to see a clear description of how the join types differ but I can't. Maybe someone else here has seen a decent tutorial on the differences between the types of joins However, if you plan to do an inner join, which is the kind most people do most of the time, your syntax will look like this: select id, conf from confs as c inner join conf_id as i on c.id = i.id where id != '101.33.55.123' If you need to do a different kind of join, please specify which kind you want to do and perhaps someone here can suggest the right syntax. -- Rhino - Original Message - From: Patrick Aljord [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 8:48 PM Subject: need help for my jointure I have a table confs like this: id int 5 auto_increment primary key; conf text; and another table conf_ip like this: id int 5 auto_increment primary key; conf_id int 5; ==foreing key of confs ip varchar 150; I would like to select id, conf from confs where ip!='some val'; how can I do this? thanx in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 24/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 24/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Hi Frank what I meant was: are you sure that the tables which are in your database are defined correctly, i.e. have the actions? Yes, if i try to delete or update a record which is referenced by another i get mysql error #1217 , which should be correct. might have been lost somewhere. Can you post the statement which has been used to create the groups table in the first place. First, i created them without foreign key clauses. After that i added them using 'alter table', but i think this does not matter. Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
