Incorrect results from sum
Sorry to return to this topic, I haven't found a lot to explain what's happening. I'm trying to total certain nutrients consumed on a given date (though I've removed date temporarily). You'll see I have three items (in two meals) in itemized, and two meal totals in simple. mysql select id, item, carb from my_menu where id in (10, 11, 22); ++-+---+ | id | item| carb | ++-+---+ | 10 | apples, w/skin, raw | 0.138 | | 11 | bananas, raw| 0.228 | | 22 | bread, Arnold Natural Wheat | 0.500 | ++-+---+ 3 rows in set (0.00 sec) mysql select * from itemized; +++-+-+-+--+ | id | date | time_of_day | uid | personal_id | units| +++-+-+-+--+ | 3 | 2008-04-01 | 06:15:00| jmangin | 10 | 167. | | 7 | 2008-04-01 | 12:30:00| jmangin | 11 | 52. | | 6 | 2008-04-01 | 12:30:00| jmangin | 22 | 36. | +++-+-+-+--+ 3 rows in set (0.01 sec) mysql select * from simple; +++-+-+--+-+--+ | id | date | time_of_day | uid | carb | protein | fat | +++-+-+--+-+--+ | 1 | 2008-04-01 | 12:05:00| jmangin | 85.0 |10.0 | 2.3 | | 2 | 2008-04-01 | 18:30:00| jmangin | 80.4 |10.0 | 10.0 | +++-+-+--+-+--+ 2 rows in set (0.01 sec) mysql select sum(my_menu.carb*units) from itemized left join my_menu on personal_id=my_menu.id; +-+ | sum(my_menu.carb*units) | +-+ | 52.9020 | +-+ 1 row in set (0.00 sec) mysql select sum(carb) from simple; ++ | sum(carb) | ++ | 165.4 | ++ 1 row in set (0.01 sec) select round(sum(my_menu.carb * units) + sum(simple.carb),2) from itemized inner join simple using (uid) left join my_menu on itemized.personal_id = my_menu.id; Instead of 218.3 this returns 602, which is (52.9 * 2 items in simple) + (165.4 * 3 items in itemized). Is it possible to get correct totals some other way with this table structure? Or explain why this is wrong? Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incorrect results from sum
I'm getting incorrect results from a sum and wonder if anyone sees something obviously wrong. (Won't surprise me.) Leaving 'simple' out of the equation (or adding 'simple' values manually) gets me the correct number. $menu is a personalized table of meal/recipe ingredients. itemized is a list of ingredients consumed in each meal. simple is a table of nutrient totals for each meal. (I want the user to be able to choose simple or itemized storage at any time.) $menu.carb is decimal(8,3) simple.carb is decimal(4,1) select round(sum($menu.carb * units) + simple.carb,2) from itemized inner join simple on itemized.uid = simple.uid inner join $menu on itemized.personal_id = $menu.id where itemized.uid = ? and itemized.date between ? and ? group by date; It's supposed to return 253.08, but I keep getting 260.36. Thanks alot, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect results from sum
- Original Message - From: Jake Peavy [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, April 05, 2008 11:59 AM Subject: Re: Incorrect results from sum On 4/5/08, Jonathan Mangin [EMAIL PROTECTED] wrote: I'm getting incorrect results from a sum and wonder if anyone sees something obviously wrong. (Won't surprise me.) Leaving 'simple' out of the equation (or adding 'simple' values manually) gets me the correct number. select round(sum($menu.carb * units) + simple.carb,2) I was hoping for a syntax error on the above line. This has worked fine until I added simple. from itemized inner join simple on itemized.uid = simple.uid inner join $menu on itemized.personal_id = $menu.id where itemized.uid = ? and itemized.date between ? and ? group by date; It's supposed to return 253.08, but I keep getting 260.36. Your bug is likely in the GROUP BY. -- -jp There is only one day's meals stored and a difference of 7.28 relates to no stored values. Thanks anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect results from sum
Hi, Here's the basic query I'm trying. It's supposed to return the totals of each nutrient (carb, in this case) by date. The data totals 218.31, but I get 190.80. select itemized.day_date as day_date, round(sum(my_menu.carb * units) + simple.carb,2) from itemized inner join simple using (uid) inner join my_menu on itemized.personal_id = my_menu.id where itemized.uid = 'me' and itemized.date between '2008-03-28' and '2008-04-01' group by day_date; I've tried different joins and a single date, with no change. I assume a misunderstanding on my part, and not a bug. CREATE TABLE `my_menu` ( `id` smallint(5) unsigned NOT NULL auto_increment, `category` tinyint(3) unsigned NOT NULL default '0', `item` varchar(60) NOT NULL default '', `uom` varchar(12) NOT NULL default '', `carb` decimal(8,3) unsigned NOT NULL default '0.000', PRIMARY KEY (`id`), UNIQUE KEY `cat_item` (`category`,`item`), UNIQUE KEY `item_uom` (`item`,`uom`) ) CREATE TABLE `itemized` ( `id` int(10) unsigned NOT NULL auto_increment, `day_date` date NOT NULL default '-00-00', `uid` varchar(14) NOT NULL default '', `time_of_day` time NOT NULL default '00:00:00', `personal_id` smallint(5) unsigned NOT NULL default '0', `units` decimal(8,4) unsigned NOT NULL default '0.', PRIMARY KEY (`id`), KEY `uid_date_time` (`uid`,`day_date`,`time_of_day`) ) CREATE TABLE `simple` ( `id` int(10) unsigned NOT NULL auto_increment, `day_date` date NOT NULL default '-00-00', `uid` varchar(14) NOT NULL default '', `time_of_day` time NOT NULL default '00:00:00', `uid` varchar(14) NOT NULL default '', `carb` decimal(4,1) unsigned default NULL, `protein` decimal(4,1) unsigned default NULL, `fat` decimal(4,1) unsigned default NULL, PRIMARY KEY (`id`), UNIQUE KEY `uid_date_time` (`uid`,`day_date`,`time_of_day`) ) insert my_menu (id, category, item, uom, carb) values (NULL, '15', 'apples, w/skin, raw', 'gr', '0.138'), (NULL, '15', 'bananas, raw', 'gr', '0.228'), (NULL, '17', 'bread, Arnold Natural Wheat', 'gr', '0.500'); insert itemized (id, day_date, uid, time_of_day, personal_id, units) values (NULL, '2008-4-01', 'me', '06:05:00', '1', '167'), (NULL, '2008-4-01', 'me', '12:30:00', '3', '36'), (NULL, '2008-4-01', 'me', '12:30:00', '2', '52'); insert simple (id, day_date, uid, time_of_day, carb, protein, fat) values (NULL, '2008-4-01', 'me', '12:05:00', '85', '10', '2.3'), (NULL, '2008-4-01', 'me', '18:30:00', '80.4', '10', '10'); Hope this is complete. Thanks alot, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anywhere to FTP latest mod_auth_mysql?
File downloads are enabled in IE but refuse to work. Does anyone know where I can FTP the latest version of mod_auth_mysql? Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Renaming the root user - problems.
- Original Message - From: Daniel da Veiga [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 07, 2006 9:48 AM Subject: Re: Renaming the root user - problems. On 12/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hello, my question refers to the user root in MySQL 5.0.22. Is the standard MySQL root user really required with the name root or can I rename the root user for example to myroot? No, it isn't required, you can rename it or simply create another user and: grant all privileges on *.* to 'user'@'%' with grant option; Our software vendor affirms that MySQL need the User root always but I argue the convers. They're wrong, and you're right. If the vendor's application connects to MySQL as root then you're both right. There really shouldn't be a user named root, but they require it. Naming a user root doesn't automatically mean they have super-user privileges. The application of this vendor doesn't work by renaming the root user to myroot. In my opinion the application causes the fault and not the MySQL DBMS. It is definitely the application fault, and it should be corrected, hard coding something like that is, to say the least, unappropriated. I have a DB with no root user for 2 years, it saved my ass from many intruders, because they all count on having a root user with all privileges, even IF they break in my system, the root user can't do anything on my DBs, its in fact a fake super-user and allowed me to catch a few idiots by logging their entrance... Many robots try to break into MySQL servers by brute-force with user root and many dictionary passwords. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
Section 3.6.2 of the 4.1 manual has this example using a subselect: SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); I use this basic syntax with max(date) alot. - Original Message - From: João Cândido de Souza Neto [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 28, 2006 8:39 AM Subject: Re: Need to find last price and date product was sold Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code mos [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- 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]
Result codes for insert...on duplicate key update
Hi, I'm using DBI and a dsn with 'mysql_client_found_rows=0' appended. A normal update returns 0E0 if no data has changed. The update part of insert...on duplicate key update always returns 2, whether data has changed or otherwise, plus the timestamp column is not automatically updated. Is there any way to get a different result code for unchanged data? (And an updated timestamp?) I assume not, but have to ask. I'm using 4.1 but see that 5.0 and 5.1 manuals say the same. Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: forcing leading 0 for numeric fields
- Original Message - From: George Law [EMAIL PROTECTED] To: MYSQL General List mysql@lists.mysql.com Sent: Wednesday, August 09, 2006 3:40 PM Subject: forcing leading 0 for numeric fields Hello All, I have what is probably a simple question. I have a table of phone numbers, broken into npa,nxx,station So, 8001231234npa =800 nxx=123 station=1234 Some queries pull data from this table in the format: select * from table where concat(npa,nxx,station)=8001231234 That is all good. The problem I ran into today is where the station column is 1000, ie 8001230123 station =0123 which gets stored as 123 by mysql Is there a quick and easy way to force station to 4 digits when I do the query select * from table where concat(npa,nxx,station)=8001230123 This query does not work, butselect * from table where concat(npa,nxx,station)=800123123 does. TIA! -- George If no arithmetic is to be performed, I'd store them as char fields. May not help you much :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting varchar field into primary key
I'm creating a new MySQL database from an existing Filemaker db. My problem is that some of the existing 'numbers' in one column (it was a text field in FMP) have leading zeros. eg: 003, 0007, 012, 001234. I need to maintain these numbers 'as is' - complete with zeros. I've tried all the numeric data types and they all seem to strip these leading zeros. Is it possible to have a numeric field type which will maintain those zeros? I want this column to become the primary key. leading zeros is a typical display requirement. An integer value does not have something as leading zeros. Why not make the VARCHAR the PK and keep the datatype? Martijn Tonies Database Workbench - development tool for MySQL, and more! The manual states (among other things) declaring a column: int(6) zerofill will left pad the column with zeros. 003 will become 03. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting varchar field into primary key
- Original Message - From: gerald_clark [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 16, 2006 3:08 PM Subject: Re: Converting varchar field into primary key Jonathan Mangin wrote: I'm creating a new MySQL database from an existing Filemaker db. My problem is that some of the existing 'numbers' in one column (it was a text field in FMP) have leading zeros. eg: 003, 0007, 012, 001234. I need to maintain these numbers 'as is' - complete with zeros. I've tried all the numeric data types and they all seem to strip these leading zeros. Is it possible to have a numeric field type which will maintain those zeros? I want this column to become the primary key. leading zeros is a typical display requirement. An integer value does not have something as leading zeros. Why not make the VARCHAR the PK and keep the datatype? Martijn Tonies Database Workbench - development tool for MySQL, and more! The manual states (among other things) declaring a column: int(6) zerofill will left pad the column with zeros. 003 will become 03. This won't work with his 3, 4, and 6 character examples. -- Leaving out zerofill will left pad with spaces. (Tried to encourage the OP to RTM.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
- Original Message - From: Ferindo Middleton Jr [EMAIL PROTECTED] To: Ferindo Middleton Jr [EMAIL PROTECTED] Cc: Hank [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, March 31, 2006 7:30 PM Subject: Re: TIMESTAMP field not automatically updating last_updated field Ferindo Middleton Jr wrote: Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo I'm running 5.0.19-nt. I haven't had a chance to test it but should it make any difference if I say: last_updated TIMESTAMP, than if I say all this: last_updatedTIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, I think this may be the difference in why some tables are auto incrementing and others aren't. Ferindo I'm using 4.1.11 on Solaris. I've explicitly created a table with one timestamp field and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL I haven't tried an explicit update (useless to me) but an insert...on duplicate key update does not update the timestamp field. Have you, Ferindo, had any success yet? Does anyone have any further thoughts? (I just realized I need this also) --Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating a Web Database Search Application
- Original Message - From: Douglas S. Davis [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 21, 2006 3:58 PM Subject: Creating a Web Database Search Application Hello, I commonly create webpages that need to search through a MySQL database and then display the results to the user. An example would be a database that contains the following: first name last name age gender location job interests I usually create a webpage with a combination of drop down menus, checkboxes, and radio buttons that allow the user to select what things they want to search for. An example might be that a user wants to find all results with: last name like smi age between 25 and 35 gender = female interests like golf But I've found it tricky to craft MySQL select statements because due to the way the searches can vary, the queries get complicated pretty quickly. Is there any simple way to do this type of thing? I write my scripts in Perl on Unix (Solaris). Are there webpages that will explain good ways to do this? Any tips are welcomed. Thanks, Douglas You could have a menu listing the reports available and craft the sql specially for each. The hard part is naming them so it's readily apparent what each report reports. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating a Web Database Search Application
- Original Message -
From: Douglas S. Davis [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, February 21, 2006 3:58 PM
Subject: Creating a Web Database Search Application
Hello,
I commonly create webpages that need to search through a MySQL
database and then display the results to the user. An example would
be a database that contains the following:
first name
last name
age
gender
location
job
interests
I usually create a webpage with a combination of drop down menus,
checkboxes, and radio buttons that allow the user to select what
things they want to search for. An example might be that a user
wants to find all results with:
last name like smi
age between 25 and 35
gender = female
interests like golf
But I've found it tricky to craft MySQL select statements because due
to the way the searches can vary, the queries get complicated pretty
quickly.
Is there any simple way to do this type of thing? I write my scripts
in Perl on Unix (Solaris). Are there webpages that will explain good
ways to do this?
Any tips are welcomed.
Thanks,
Douglas
[Hit send too soon]
With so few fields, sticking with one report isn't
out of the question. Don't know if you're using
this technique:
my $lname = $q-param('lname') || '%';
So all fields not filled/selected by the user are
wildcards by default. A completely empty form
pulls all data in the relevant tables.
--Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning values from an INSERT
- Original Message -
From: bob pilly [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, February 12, 2006 12:44 AM
Subject: Returning values from an INSERT
Hi everyone, im new to SQL and have a question that someone can hopefully
answer
If i am inserting a new record into a table that has an auto_increment
field in it, is it possible to get the value of that field returned
automatically instead of having to do a SELECT..
For example my user table has:
userid -- auto_increment and primary key
username
So if i:
Insert into user (username) VALUES ('Test User');
i dont know what userid was assigned to that particular user and are
having to:
SELECT userid FROM user where username = 'Test User';
to get the id.
Is that the only way to do it?
hope it isnt a stupid question and thanks for any help in advance!
Cheers
Bob
-
Yahoo! Photos - NEW, now offering a quality print service from just 8p a
photo.
Is username (as opposed to fname, lname) unique?
If so, perhaps you can make that the primary key
and bag id.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert...on duplicate key update...help
I'm trying to change a couple of replace statements to
insert...on duplicate key update (using Perl/DBI).
foreach my $key (keys %e_items) {
my $sql = insert table1
(id, date, time, uid, type, seq, value)
values
(?, ?, ?, ?, ?, ?, ?)
on duplicate key update;
my $sth = $dbh-prepare($sql);
$sth-execute($e_items{$key}-[0], $date, $e_items{$key}-[3],
$uid, $e_items{$key}-[1], $e_items{$key}-[2],
$e_items{$key}-[4]) || die $sth-errstr;
}
The manual says more is needed at the end of my sql, but
I'm not sure of the syntax. (Looks to me like all required
info is present ;) id is primary key and the only unique
index.
Thanks,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query
I got exactly that error message last night when doing a numeric comparison on a varchar column. Oops. Kind of misleading, though. - Original Message - From: David Godsey [EMAIL PROTECTED] To: George Law [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, January 24, 2006 4:43 PM Subject: RE: Lost connection to MySQL server during query Thanks for the reply. I'm not using persistend connections though. It appears that it looses the connection in the middle of the query or in other words, before the procedure returns. So that means I not getting the data I need. So for debug purposes, are you saying to do a check status from PHP or in the procedure? From the procedure it wouldn't do any good right? From PHP it would be after I didn't get the data, so I would have to reconnect and rerun the query. That won't really work for me either. David Godsey David, Are you using persistent connections? Sounds like perhaps a persistent connection is timing out. Maybe a quick work around would be to call a check status routine (ie - do a show status), just to see if the connection is still there. If this fails, just do a mysql_connect... Before continuing. -- George -Original Message- From: David Godsey [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 4:09 PM To: mysql@lists.mysql.com Subject: Lost connection to MySQL server during query I am getting this error when connecting to mysql with PHP: Lost connection to MySQL server during query This happens only when I use this procedure, but it doesn't necessarily fail when this procedure is called. The error will happen frequently, however it is not consistent. This is my first procedure I've written, so I'm sure I've done something wrong here. I assume the error message means I'm hitting some kind of timeout? Any ideas would be welcome. Thanks. create procedure getElement (IN n VARCHAR(255),IN ptime DOUBLE) BEGIN DECLARE mfid INT UNSIGNED; DECLARE pid INT UNSIGNED; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE vid INT UNSIGNED; DECLARE rfid INT UNSIGNED; DECLARE tpid INT UNSIGNED; DECLARE fdata BLOB; DECLARE fdata_tmp BLOB; DECLARE fdata_bigint BIGINT UNSIGNED; DECLARE fdata_signed INT; DECLARE fdata_unsigned INT UNSIGNED; DECLARE fdata_float DOUBLE; DECLARE data_type VARCHAR(20); DECLARE byte_order VARCHAR(20); DECLARE conv_param VARCHAR(255); SELECT major_frame_desc_id, parent_id, frame_offset, frame_length, version_id, top_level_parent_id FROM MajorFrameDescription WHERE name=n INTO mfid,pid,foffset,flength,vid,tpid; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=NormalizedType INTO data_type; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ConvParams INTO conv_param; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ByteOrder INTO byte_order; SELECT MAX(raw_major_frame_id) FROM RawMajorFrames WHERE major_frame_desc_id=tpid INTO rfid; IF rfid 0 THEN SELECT payload_time, SUBSTR(BINARY(frame_data), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))/8)) FROM RawMajorFrames WHERE raw_major_frame_id=rfid INTO ptime,fdata; call toBigInt(fdata,fdata_bigint); IF (foffset %8) 0 THEN SET @mask_off=foffset%8; call mask_data(fdata,@mask_off,fdata_bigint); END IF; IF (8-((flength+(foffset%8)) %8)) 0 THEN SELECT (fdata_bigint (8-((flength+(foffset%8)) %8))) INTO fdata_bigint; END IF; CASE data_type WHEN Float THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN
Calendar table workaround
I created a calendar table (date only), but all where clauses include a uid. Is the following a sane workaround to get a usable calendar table? Anything else I can do? my $sth = $dbh-prepare( create table $temp_tbl (date date, uid varchar(14)) select date, ? as uid from calendar where date between ? and ?); $sth-execute($uid, $bdate, $edate); Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calendar table workaround
- Original Message - From: Rhino [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 1:25 PM Subject: Re: Calendar table workaround - Original Message - From: Jonathan Mangin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 04, 2006 10:45 AM Subject: Calendar table workaround I created a calendar table (date only), but all where clauses include a uid. Is the following a sane workaround to get a usable calendar table? Anything else I can do? my $sth = $dbh-prepare( create table $temp_tbl (date date, uid varchar(14)) select date, ? as uid from calendar where date between ? and ?); $sth-execute($uid, $bdate, $edate); I have no idea what you are asking, which may explain why no one has replied to your question yet. I've been working with relational databases for 20 years and I've never heard the term calendar table. What are you trying to accomplish? If you describe clearly what you are trying to do, perhaps someone can help you devise a way to do it in MySQL. Rhino A table of dates to which to join other tables, ensuring reports that reflect days for which no data is available. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calendar table workaround
RE: Calendar table workaround A table of dates to which to join other tables, ensuring reports that reflect days for which no data is available. I forget the query but I know it can be done. But can't you just have a table (called calendar?) with each entry having it's own row with a date column that gives whatever date you need in it and then other columns for any other details you need to have to go along with the date. - Sorry, I thought this was a very common situation. And, therefore, instantly recognizable. I'll include the full story. my $bdate = '2005-08-01'; my $edate = '2005-08-14'; my $uid = 'george'; my $temp_tbl = 'calendar_' . $uid; my $sth = $dbh-prepare( create table $temp_tbl (date date, uid varchar(14)) engine = memory select date, ? as uid from calendar where date between ? and ?); $sth-execute($uid, $bdate, $edate); $sth = $dbh-prepare( (select $temp_tbl.date as date, concat(type,seq) as event, time_format(time,'%H:%i'), value as val1, '' as val2 from $temp_tbl left join table1 on table1.date = $temp_tbl.date where $temp_tbl.uid = ? and $temp_tbl.date between ? and ?) union (select $temp_tbl.date, concat(type,seq), time_format(time,'%H:%i'), t1_val, t2_val from $temp_tbl left join table2 on table2.date = $temp_tbl.date where $temp_tbl.uid = ? and $temp_tbl.date between ? and ?) order by date, event); $sth-execute($uid, $bdate, $edate, $uid, $bdate, $edate); These are $uid-specific reports (where .uid = ?) and uid, of course, doesn't exist in my standard 'calendar table.' The question: Is creating another temporary table (that does include both date and uid) the best thing to do here? Thanks.
DATETIME columns and indexing
(I used to have separate date/time cols. in all tables but changed them to datetime and buggered up some stuff. Now I'm trying to find the best way to fix this.) If I have an indexed datetime column (`date`), and say: select date, other_cols from table1 where date between '2005-08-01' and '2005-08-14' order by date; Can the index ever be used in a date-only where clause or must I include the time part also? Explain does show the index in possible_keys, but is not shown under key. (Very little data in this table). Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Will the optimizer???
I'm trying to correct a situation I've created after combining separate date/time columns into a datetime column. Here are skeletons of new table definitions. CREATE TABLE products (id mediumint unsigned primary key not null auto_increment, item varchar(40) not null, priceA decimal(6,3), priceB decimal(6,3), UNIQUE KEY `item_name` (item)) CREATE TABLE sales (id int unsigned primary key not null auto_increment, date date not null, time time not null, person varchar(25), item varchar(40), units decimal(7,4) unsigned not null, INDEX `person_date_time` (person, date, time)) I have a union much like the one below that seems to work well, but I'm hoping someone has time to advise me what the optimizer will (or won't) do given the proposed index and GROUP BY/ ORDER BY clauses, before I start changing my tables. (SELECT date AS date, time_format(time,'%H:%i') AS time, substr(sales.item,1,18) AS item, round(priceA * units,2), round(priceB * units,2) FROM sales LEFT JOIN products ON sales.item = products.item WHERE sales.person = 'george' AND sales.date BETWEEN '2005-08-01' AND '2005-08-14') union (SELECT date AS date, time_format(time,'%H:%i') AS time, 'ZZ' AS item, round(sum(priceA * units),2), round(sum(priceB * units),2) FROM sales LEFT JOIN products ON sales.item = products.item WHERE sales.person = 'george' AND date BETWEEN '2005-08-01' AND '2005-08-14' GROUP BY date, time) union (SELECT date AS date, '23:59' AS time, '' AS item, round(sum(priceA * units),2), round(sum(priceB * units),2) FROM sales LEFT JOIN products ON sales.item = products.item WHERE sales.person = 'george' AND date BETWEEN '2005-08-01' AND '2005-08-14' GROUP BY date) ORDER BY date, time, item The result itemizes a sale, totals a sale, and totals the day's sales, by person. Something like this: 2005-08-01 07:30 Apples 39.29 42.40 2005-08-01 07:30 Oranges 9.29 10.02 2005-08-01 07:30 Zucchini .65 .69 2005-08-01 07:30 ZZ 49.23 53.11 2005-08-01 08:01 Bears 9.99 10.20 2005-08-01 08:01 Lions 7.287.49 2005-08-01 08:01 Tigers .65 .69 2005-08-01 08:01 ZZ 17.92 18.38 2005-08-01 23:59 67.15 71.49 2005-08-02and so on Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index of JOINed table?
I have a simple query, (part of a union). calendar is a table of only dates, `date` as PK. sales has a multi-col index on (date, uid). (select calendar.date as date, time_format(time(sales.date),'%H:%i') as time, from calendar left join sales on date(sales.date) = calendar.date and sales.uid = 'george' where calendar.date between '2005-08-01' and '2005-08-14') First, I don't quite understand joins(!) on constants (sales.uid = 'george'). Is this proper, or should uid be included in where clause? Second, explain shows no possible keys for sales. id select_type table typepossible_keys key key_len ref rows Extra 1 PRIMARY calendarrange PRIMARY PRIMARY 3 NULL11 Using where; Using index 1 PRIMARY sales ALL NULLNULLNULLNULL23 Some versions of my query showed possible_keys, but I can't seem to find what I did and I'm frustrated. What am I doing wrong here? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index of JOINed table?
- Original Message - From: Jonathan Mangin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 21, 2005 8:48 AM Subject: Index of JOINed table? I have a simple query, (part of a union). calendar is a table of only dates, `date` as PK. sales has a multi-col index on (date, uid). (select calendar.date as date, time_format(time(sales.date),'%H:%i') as time, from calendar left join sales on date(sales.date) = calendar.date and sales.uid = 'george' where calendar.date between '2005-08-01' and '2005-08-14') First, I don't quite understand joins(!) on constants (sales.uid = 'george'). Is this proper, or should uid be included in where clause? Second, explain shows no possible keys for sales. id select_type table typepossible_keys key key_len ref rows Extra 1 PRIMARY calendarrange PRIMARY PRIMARY 3 NULL11 Using where; Using index 1 PRIMARY sales ALL NULLNULLNULLNULL23 I guess I found the problem... on date(sales.date) = calendar.date If I create separate date and time columns it lists, and uses, possible_keys. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Missing column in select??
I thought I'd found a mistake... $sql = (select date(date) as date, time_format(time(date),'%H:%i') as time, units, # I forgot to include this round(sum(item1 * units),2), but all the numbers are correct. Does mysql know to multiply by the units column _of the current row_ without my selecting it? I thought I'd read about selecting a column before the point of needing to act on it. --Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Missing column in select??
Stupid question. units is no different from item1 at this point. - Original Message - From: Jonathan Mangin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 15, 2005 2:36 PM Subject: Missing column in select?? I thought I'd found a mistake... $sql = (select date(date) as date, time_format(time(date),'%H:%i') as time, units, # I forgot to include this round(sum(item1 * units),2), but all the numbers are correct. Does mysql know to multiply by the units column _of the current row_ without my selecting it? I thought I'd read about selecting a column before the point of needing to act on it. --Jon -- 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]
Joining tables, duplicating none
I have two tables with date and uid cols. in common. Table 1 has one row per date, Table 2 has a maximum of 7 rows per date. select t1.date, t1.val, t2.val from t1 right join t2 on t1.date = t2.date where t1.date between '2005-08-01' and '2005-08-14' and t1.uid = 'me'; +--+ | t1.date| t1.val | t2.val | ++++ | 2005-08-01 | 92 | 18.3 | | 2005-08-01 | 92 | 23.3 | ++++ Is there any way to get something like this +--++ | t1.date| t1.val | t2.val | t2.val | +++++ | 2005-08-01 | 92 | 18.3 | 23.3 | +++++ instead of duplicating Table 1 rows for every row in Table 2? Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining tables, duplicating none
- Original Message - From: [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, October 07, 2005 2:09 PM Subject: Re: Joining tables, duplicating none Jonathan Mangin [EMAIL PROTECTED] wrote on 10/07/2005 02:57:28 PM: I have two tables with date and uid cols. in common. Table 1 has one row per date, Table 2 has a maximum of 7 rows per date. select t1.date, t1.val, t2.val from t1 right join t2 on t1.date = t2.date where t1.date between '2005-08-01' and '2005-08-14' and t1.uid = 'me'; +--+ | t1.date| t1.val | t2.val | ++++ | 2005-08-01 | 92 | 18.3 | | 2005-08-01 | 92 | 23.3 | ++++ Is there any way to get something like this +--++ | t1.date| t1.val | t2.val | t2.val | +++++ | 2005-08-01 | 92 | 18.3 | 23.3 | +++++ instead of duplicating Table 1 rows for every row in Table 2? Thanks, Jon You cannot get that kind of results as distinct and separate columns. Have you looked at the GROUP_CONCAT() function as a work around? SELECT t1.date, t1.val, GROUP_CONCAT(t2.val) FROM t1 RIGHT JOIN t2 on t1.date = t2.date where t2.date between '2005-08-01' and '2005-08-14' and t1.uid = 'me' GROUP BY t1.date, t1.val; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thanks Shawn, Interesting, but that won't work for me. Are two separate selects my only choice? I tried (with two different tables) including t1.id in related records of Table 2 and ran into the same problem. With all the talk of normalization, I thought this would be easier. Am I leaving something obvious out of the table designs? --Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using ifnull in a subquery
Hello all, This works if t2 is populated: select t1.item_no, t1.value1 * (select weight from t2 where item_no = t1.item_no and descrip = 'dime bag') from t1 where t1.descrip = 'marigold seeds' If t1.item_no and/or 'dime bag' don't exist in t2 I'd like to multiply by a different value (5): select t1.item_no, t1.value1 * (select ifnull(weight,5) from t2 where item_no = t1.item_no and descrip = 'dime bag') from t1 where t1.descrip = 'marigold seeds' My tests return NULL instead of t1.value1 * 5. Is there a way to do what I need? Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using ifnull in a subquery
- Original Message - From: Jonathan Mangin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, September 24, 2005 2:18 PM Subject: Using ifnull in a subquery Hello all, This works if t2 is populated: select t1.item_no, t1.value1 * (select weight from t2 where item_no = t1.item_no and descrip = 'dime bag') from t1 where t1.descrip = 'marigold seeds' If t1.item_no and/or 'dime bag' don't exist in t2 I'd like to multiply by a different value (5): select t1.item_no, t1.value1 * (select ifnull(weight,5) from t2 where item_no = t1.item_no and descrip = 'dime bag') from t1 where t1.descrip = 'marigold seeds' My tests return NULL instead of t1.value1 * 5. Is there a way to do what I need? Thanks, Jon Never mind, it's pretty obvious... select t1.item_no, t1.value1 * ifnull((select weight from t2 where item_no = t1.item_no and descrip = 'dime bag'),5) from t1 where t1.descrip = 'marigold seeds' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
String arithmetic?
Hi,
I think I'd like to store these values as strings
instead of [?]int or time types.
Is this string arithmetic? Can/should I do this?
(I see the second one won't work without single-quotes.)
mysql select '3' - '1';
+---+
| '3' - '1' |
+---+
| 2 |
+---+
1 row in set (0.00 sec)
mysql select time_format(subtime('6:10',':10'),'%H:%i');
++
| time_format(subtime('6:10',':10'),'%H:%i') |
++
| 06:00 |
++
1 row in set (0.00 sec)
Thanks,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: String arithmetic?
I see these really need to be int or time types.
Is there no way they can default to NULL or blank?
Hi,
I think I'd like to store these values as strings
instead of [?]int or time types.
mysql select '3' - '1';
+---+
| '3' - '1' |
+---+
| 2 |
+---+
1 row in set (0.00 sec)
mysql select time_format(subtime('6:10',':10'),'%H:%i');
++
| time_format(subtime('6:10',':10'),'%H:%i') |
++
| 06:00 |
++
1 row in set (0.00 sec)
Thanks,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Impossible join?
Jonathan Mangin wrote: Hello all, I'm storing data from a series of tests throughout each 24-hour period. I thought to create a table for each test. (There are six tests, lots more cols per test, and many users performing each test.) But each test is performed no more than once per day by a given user? Correct. select test1.date, test1.time, test2.date, test2.time from test1 left join test2 on test2.date=test1.date where test1.date between '2005-07-01' and '2005-07-16' and uid='me'; Something is strange here. Doesn't uid exist in both tables? I'll assume it does. Oops. Also correct. ++--++--+ | date | time | date | time | ++--++--+ | 2005-07-13 | 6:30 | 2005-07-13 | 7:30 | | 2005-07-14 | 6:32 | 2005-07-14 | 7:45 | | 2005-07-15 | 6:30 | 2005-07-15 | 7:42 | | 2005-07-16 | 6:35 | NULL | NULL | ++--++--+ Is there a join, or some other technique, that would return (nearly) these same results if test1 (or any test) has not been performed? Using 4.1.11. TIA, Jon [ SNIP! ] A better solution would be to add a table: CREATE TABLE `testdates` (`date` date default NULL, UNIQUE KEY `date_idx` (`date`) ); Insert one row into testdates for each day. Now you can use something like this: SELECT testdates.date, test1.time AS 'Test 1', test2.time AS 'Test 2' FROM testdates LEFT JOIN test1 on testdates.date = test1.date AND test1.uid = 'me' LEFT JOIN test2 on testdates.date = test2.date AND test2.uid = 'me' WHERE testdates.date BETWEEN '2005-07-11' AND '2005-07-16'; ++--+--+ | date | Test 1 | Test 2 | ++--+--+ | 2005-07-11 | NULL | NULL | | 2005-07-12 | NULL | 07:28:00 | | 2005-07-13 | 06:30:00 | 07:30:00 | | 2005-07-14 | 06:32:00 | 07:45:00 | | 2005-07-15 | 06:30:00 | 07:42:00 | | 2005-07-16 | 06:35:00 | NULL | ++--+--+ 6 rows in set (0.01 sec) Much better, don't you think? This generalizes pretty well, too. SELECT testdates.date, test1.time AS 'Test 1', test2.time AS 'Test 2', test3.time AS 'Test 3', test4.time AS 'Test 4' FROM testdates LEFT JOIN test1 on testdates.date = test1.date AND test1.uid = 'me' LEFT JOIN test2 on testdates.date = test2.date AND test2.uid = 'me' LEFT JOIN test3 on testdates.date = test3.date AND test3.uid = 'me' LEFT JOIN test4 on testdates.date = test4.date AND test4.uid = 'me' WHERE testdates.date BETWEEN '2005-07-11' AND '2005-07-16'; ++--+--+--+--+ | date | Test 1 | Test 2 | Test 3 | Test 4 | ++--+--+--+--+ | 2005-07-11 | NULL | NULL | NULL | 08:12:00 | | 2005-07-12 | NULL | 07:28:00 | 07:14:00 | 08:14:00 | | 2005-07-13 | 06:30:00 | 07:30:00 | 07:16:00 | 08:29:00 | | 2005-07-14 | 06:32:00 | 07:45:00 | 07:14:00 | 08:26:00 | | 2005-07-15 | 06:30:00 | 07:42:00 | 07:19:00 | NULL | | 2005-07-16 | 06:35:00 | NULL | NULL | NULL | ++--+--+--+--+ 6 rows in set (0.00 sec) Michael I'm guessing this is a common solution. Shame on me. How does one swiftly populate a table with an entire year (or more) of dates? Thanks very much, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Impossible join?
Hello all, I'm storing data from a series of tests throughout each 24-hour period. I thought to create a table for each test. (There are six tests, lots more cols per test, and many users performing each test.) select test1.date, test1.time, test2.date, test2.time from test1 left join test2 on test2.date=test1.date where test1.date between '2005-07-01' and '2005-07-16' and uid='me'; ++--++--+ | date | time | date | time | ++--++--+ | 2005-07-13 | 6:30 | 2005-07-13 | 7:30 | | 2005-07-14 | 6:32 | 2005-07-14 | 7:45 | | 2005-07-15 | 6:30 | 2005-07-15 | 7:42 | | 2005-07-16 | 6:35 | NULL | NULL | ++--++--+ Is there a join, or some other technique, that would return (nearly) these same results if test1 (or any test) has not been performed? Using 4.1.11. TIA, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: starting mysql 4.1.12 on Ubuntu Linux
- Original Message - From: Juan Pedro Reyes Molina [EMAIL PROTECTED] To: Hassan Schroeder [EMAIL PROTECTED] Cc: mysql general list mysql@lists.mysql.com Sent: Saturday, June 25, 2005 2:08 PM Subject: Re: starting mysql 4.1.12 on Ubuntu Linux hello, Hassan which mysql gives me /usr/bin/mysql. echo $PATH shows me that /usr/bin is the fourth try. I thougth this was windows like, where PATH is only used if program is not found in active directory. If you wish it, currrent directory (.) should be added to $PATH. export PATH=.:$PATH So, I have a script in /etc/init.d called mysql and an executable in /usr/bin also called mysql that takes precedence. /etc/init.d probably isn't (and maybe shouldn't be) in your PATH. Less confusion if you rename /etc/init.d/mysql to mysqld. I thought that when linux starts all scripts in /etc/init.d where given the start signal. If so I don't understand why mysql remains stopped on linux start up but wake up smoothly when I issue a manual /etc/init.d/mysql start. It looks like a bug in Ubuntu distro. On my Solaris box, there is a directory named /etc/rc2.d. In that directory is a symbolic link to /etc/init.d/mysqld named S99mysqld. This means that the mysql server will be started in init state 2. In /etc/rc1.d is a symbolic link to /etc/init.d/mysqld named K99mysqld. This means that the mysql server will be killed in init state 1. Your /etc/rc directory structure may vary somewhat. ln -s /etc/init.d/mysqld /etc/rc2.d/S99mysqld I have tried cp /etc/init.d/mysql /etc/init.d/mysqltmp but issuing /etc/init.d/mysqltmp gives me bash: mysqltmp: command not found mysqltmp may not be executable. chmod u+x mysqltmp Hassan Schroeder wrote: Juan Pedro Reyes Molina wrote: With my sql stopped I go to console as root and write: cd /etc/init.d mysql start At this point, try (as root) which mysql It will certainly not be /etc/init.d/mysql, but somewhere in your defined PATH; try echo $PATH to see what that is I would like to learn what's the difference between mysql start and /etc/init.d/mysql start if I'm sitting on /etc/init.d '/etc/init.d/mysql' defines the executable you want to run explicitly; 'mysql' is the first instance of an executable with that name in your PATH. I think this error is preventing mysql from automatically starting on start up. Probably not; look in your error logs for more information on that. And in any case 'mysql' is generally the *client* program; *mysqld* is the server that you want to start. Sounds like your distro has a confusingly named startup file in /etc/init.d. HTH! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient select/insert
This would be even faster if you could concatenate all of the elements
of
@array into a single list then you could say:
#My PERL skills are non existent so you need to write this part.
#I am assuming that @array is holding a list of string values.
foreach my $element (@array) {
@araylist += ' + $element + ',;
}
@arraylist = left(@arraylist, length(@arraylist) -1)
#that's to remove the trailing comma at the end of the list
my $sql = select col2, col3, col4 from table1
where col1 IN (?);
my $sth = $dbh-prepare($sql);
$sth-execute(@arraylist) or die $sth-errstr();
I've finally gotten around to this. It doesn't appear to be
possible using a placeholder.
$list # a string of an unknown number of CS numeric primary keys.
$sql = insert into table2
(col2, col3, ...)
select col2, col3, ...
from table1
where id in (?);
$sth = $dbh-prepare($sql);
$sth-execute($list) || die ...
This inserts only the first item from $list.
$sth-execute(2,4) complains about mismatched number of bind variables.
An array (@list) does the same thing (only worse:)
where id in ($list) returns an SQL syntax error.
Should I just fall back or am I missing something?
foreach $item (@list) {
$sql = insert into table2 ...
Thanks,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient select/insert
- Original Message -
From: Eamon Daly [EMAIL PROTECTED]
To: Jonathan Mangin [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, May 19, 2005 1:17 PM
Subject: Re: Efficient select/insert
my $sql = sprintf 'EOF', join(',', @array);
SELECT col2, col3, col4
FROM table1
WHERE col1 IN (%s)
EOF
my $sth = $dbh-prepare($sql);
$sth-execute() or die $sth-errstr();
Eamon Daly
Thanks, that works (I'll have to read a bit to learn why)
except for one thing I didn't mention. (Everybody Lies :)
How ugly is this?
$sql = INSERT into $table2; # dynamic name with $user_id as root
$sql .= sprintf 'EOF', join(',', @array);
(col2, col3, ...)
SELECT col2, col3, ...
FROM table1
WHERE col1 IN (%s)
EOF
- Original Message -
From: Jonathan Mangin [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, May 19, 2005 11:52 AM
Subject: Re: Efficient select/insert
This would be even faster if you could concatenate all of the
elements
of
@array into a single list then you could say:
#My PERL skills are non existent so you need to write this part.
#I am assuming that @array is holding a list of string values.
foreach my $element (@array) {
@araylist += ' + $element + ',;
}
@arraylist = left(@arraylist, length(@arraylist) -1)
#that's to remove the trailing comma at the end of the list
my $sql = select col2, col3, col4 from table1
where col1 IN (?);
my $sth = $dbh-prepare($sql);
$sth-execute(@arraylist) or die $sth-errstr();
I've finally gotten around to this. It doesn't appear to be
possible using a placeholder.
$list # a string of an unknown number of CS numeric primary keys.
$sql = insert into table2
(col2, col3, ...)
select col2, col3, ...
from table1
where id in (?);
$sth = $dbh-prepare($sql);
$sth-execute($list) || die ...
This inserts only the first item from $list.
$sth-execute(2,4) complains about mismatched number of bind variables.
An array (@list) does the same thing (only worse:)
where id in ($list) returns an SQL syntax error.
Should I just fall back or am I missing something?
foreach $item (@list) {
$sql = insert into table2 ...
Thanks,
Jon
--
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: Efficient select/insert
Thanks, that works (I'll have to read a bit to learn why)
except for one thing I didn't mention. (Everybody Lies :)
How ugly is this?
$sql = INSERT into $table2; # dynamic name with $user_id as root
$sql .= sprintf 'EOF', join(',', @array);
I see.
I suppose this produced quite a few grins.
Happy to oblige. :)
Thanks again Eamon,
--Jon
(col2, col3, ...)
SELECT col2, col3, ...
FROM table1
WHERE col1 IN (%s)
EOF
You could simplify it like this:
$sql = sprintf 'EOF', $table2, join(',', @array);
INSERT INTO %s (col2, col3, ...)
SELECT col2, col3, ...
FROM table1
WHERE col1 IN (%s)
EOF
SOme explanation of the details:
- join(',', @array)
join concats a list to a string, joined by some character.
See perldoc -f join.
- sprintf
sprintf behaves like its C counterpart and allows
interpolation of numbers and strings.
See perldoc -f sprintf.
- 'EOF'
That's a here document. We put it in single quotes so
perl-ish stuff like '$' and '@' are not interpolated.
See http://www.perlmeme.org/howtos/interpolation.html
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Efficient select/insert
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @array = $q-param(); # HTML checkboxes
foreach my $element (@array) {
my $sql = select col2, col3, col4 from table1
where col1 = ?;
my $sth = $dbh-prepare($sql);
$sth-execute($element) or die $sth-errstr();
my @row = $sth-fetchrow_array;
$sql = insert table2 (col1, col2, col3, col4)
values (NULL, ?, ?, ?);
$sth = $dbh-prepare($sql);
$sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr();
}
Is this efficient db interaction, or is there a better way?
This is 3.23 but can upgrade if necessary.
Thanks,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient select/insert
- Original Message -
From: Jonathan Mangin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, April 26, 2005 11:26 AM
Subject: Efficient select/insert
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @array = $q-param(); # HTML checkboxes
foreach my $element (@array) {
my $sql = select col2, col3, col4 from table1
where col1 = ?;
my $sth = $dbh-prepare($sql);
$sth-execute($element) or die $sth-errstr();
my @row = $sth-fetchrow_array;
$sql = insert table2 (col1, col2, col3, col4)
values (NULL, ?, ?, ?);
$sth = $dbh-prepare($sql);
$sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr();
}
Is this efficient db interaction, or is there a better way?
This is 3.23 but can upgrade if necessary.
Thanks,
Jon
Further...
I thought I could use fetchrow_arrayref and push an array
of arrays. The DBI docs say:
Note that the same array reference is returned for each fetch,
so don't store the reference and then use it after a later fetch.
Sounds like I can't use that. Now I see execute_for_fetch. Does this
sound like a job for execute_for_fetch?
--J
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient select/insert
- Original Message -
From: [EMAIL PROTECTED]
To: Jonathan Mangin [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, April 26, 2005 3:20 PM
Subject: Re: Efficient select/insert
Jonathan Mangin [EMAIL PROTECTED] wrote on 04/26/2005 12:26:20
PM:
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @array = $q-param(); # HTML checkboxes
foreach my $element (@array) {
my $sql = select col2, col3, col4 from table1
where col1 = ?;
my $sth = $dbh-prepare($sql);
$sth-execute($element) or die $sth-errstr();
my @row = $sth-fetchrow_array;
$sql = insert table2 (col1, col2, col3, col4)
values (NULL, ?, ?, ?);
$sth = $dbh-prepare($sql);
$sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr();
}
Is this efficient db interaction, or is there a better way?
This is 3.23 but can upgrade if necessary.
Thanks,
Jon
Why not use an INSERT...SELECT instead of splitting up the two steps? Is
there a reason you need to see the data before it goes into the other
table? (http://dev.mysql.com/doc/mysql/en/insert-select.html)
foreach my $element (@array) {
my $sql = INSERT table2 (col2, col3, col4) select col2, col3, col4
from table1
where col1 = ?;
my $sth = $dbh-prepare($sql);
$sth-execute($element) or die $sth-errstr();
}
This would be even faster if you could concatenate all of the elements of
@array into a single list then you could say:
I thought that's what I already had. @array contains
selected primary keys from table 1.
#My PERL skills are non existent so you need to write this part.
#I am assuming that @array is holding a list of string values.
foreach my $element (@array) {
@araylist += ' + $element + ',;
}
@arraylist = left(@arraylist, length(@arraylist) -1)
#that's to remove the trailing comma at the end of the list
my $sql = select col2, col3, col4 from table1
where col1 IN (?);
my $sth = $dbh-prepare($sql);
$sth-execute(@arraylist) or die $sth-errstr();
Oh, I see. A List. Hmmm.
And did you forget insert or are you practicing black magic?
;)
Like I said, I have no (zero, zilch, nil) PERL skills (this is really
simple code and I still got it wrong) but you should get the idea...
That last statement should move all of your records in one big batch.
Notice I didn't INSERT to table2.col1. By leaving it out of the INSERT
clause it's as though I inserted a NULL in that column for each record and
if col1 were an auto_increment, it should count up as expected.
I noticed and wondered. Thanks for clarifying.
Generally, if you don't have to look at the data in your application
(maybe because you need to massage it in some way) don't bring it back to
your client. Let the engine handle it at the server and things will go
much faster.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Excellent! Thanks a lot, Shawn.
--Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.10a packaging for Solaris
man pkgadd -J - Original Message - From: Joerg Bruehe [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Jonathan Stockley [EMAIL PROTECTED] Sent: Thursday, March 17, 2005 9:16 AM Subject: Re: 4.1.10a packaging for Solaris Hi Jonathan, all! Am Mi, den 16.03.2005 schrieb Jonathan Stockley um 22:33: I just downloaded the 4.1.10a release for Solaris 8 and 9 (32bit). It seems that it is no longer in a tar archive. Was this change intentional? Both yes and no: Yes, it was intentional to create and offer PKG format. No, it was not intended to block tar.gz. In fact, tar.gz is still being built and offered, but due to some mishandling it is not listed on the download page. Please access some mirror directly that offers a list - for example: ftp://ftp.gwdg.de/pub/misc/mysql/Downloads/MySQL-4.1/ I cant find any mention of it on the web site. How do I unpack the new format into a given directory? I assume this is possible, but I lack detailed Solaris / PKG knowledge to answer. Maybe some Solaris manual does tell? But probably you will get along better by downloading tar.gz. HTH, Joerg Bruehe -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple explain
Hello, Does this mean a key is not being used? mysql explain select vl_ts from view_log where vl_uid='bb'; +--+--+---+--+-+--+--++ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+--+---+--+-+--+--++ | view_log | ALL | vluid | NULL |NULL | NULL | 60 | where used | +--+--+---+--+-+--+--++ Thanks, -Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy data only from one table to another table
- Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Chip Wiegand [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 8:10 AM Subject: RE: copy data only from one table to another table [snip] How do I copy all data only from one table into another table? Both tables are in the same database. I have phpMyAdmin and it suppossedly does this, but it is not working, and there are no error messages. [/snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` If this is valid SQL surely grave accents are not? -Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy data only from one table to another table
- Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 9:19 AM Subject: RE: copy data only from one table to another table [snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` If this is valid SQL surely grave accents are not? [/snip] Actually? MySQL supports the use of grave accents around table and column names. I use them here for emphasis. In certain cases, with older versions of MySQL, I encourage our developers to always use them. Well, never mind then. I thought only apostrophes were valid. -Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot lock: called with 1 bind variables when 0 are needed
Hmmm, you're probably right. I got carried away. - Original Message - From: Mike Wexler [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Sent: Sunday, October 24, 2004 4:53 PM Subject: Re: Cannot lock: called with 1 bind variables when 0 are needed Try this: my $sql = LOCK tables TBUSR write, TBAUTH write, TBDATALOG write, TBAGCY write, TBREL write, TBACCESSLOG write; my $sth = $dbh-prepare($sql); $sth-execute(); On Sat, 23 Oct 2004 20:39:38 -0500, Jonathan Mangin [EMAIL PROTECTED] wrote: Hi all, With 3.23.38 I have: my $sql = LOCK tables TBUSR write, TBAUTH write, TBDATALOG write, TBAGCY write, TBREL write, TBACCESSLOG write; my $sth = $dbh-prepare($sql); $sth-execute($sql) || die Cannot lock: . $sth-errstr(); I get: Cannot lock: called with 1 bind variables when 0 are needed ... Carp.pm... --Jon -- 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: Cannot lock: called with 1 bind variables when 0 are needed
Another interesting thing. Mysql is running on Solaris. Once past the Cannot lock error, I got approximately: db.TBUSR does not exist on this server db.tbusr does. I thought table names were case insensitive on unix. Apparently not. - Original Message - From: Mike Wexler [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Sent: Sunday, October 24, 2004 4:53 PM Subject: Re: Cannot lock: called with 1 bind variables when 0 are needed Try this: my $sql = LOCK tables TBUSR write, TBAUTH write, TBDATALOG write, TBAGCY write, TBREL write, TBACCESSLOG write; my $sth = $dbh-prepare($sql); $sth-execute(); On Sat, 23 Oct 2004 20:39:38 -0500, Jonathan Mangin [EMAIL PROTECTED] wrote: Hi all, With 3.23.38 I have: my $sql = LOCK tables TBUSR write, TBAUTH write, TBDATALOG write, TBAGCY write, TBREL write, TBACCESSLOG write; my $sth = $dbh-prepare($sql); $sth-execute($sql) || die Cannot lock: . $sth-errstr(); I get: Cannot lock: called with 1 bind variables when 0 are needed ... Carp.pm... --Jon -- 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]
Cannot lock: called with 1 bind variables when 0 are needed
Hi all, With 3.23.38 I have: my $sql = LOCK tables TBUSR write, TBAUTH write, TBDATALOG write, TBAGCY write, TBREL write, TBACCESSLOG write; my $sth = $dbh-prepare($sql); $sth-execute($sql) || die Cannot lock: . $sth-errstr(); I get: Cannot lock: called with 1 bind variables when 0 are needed ... Carp.pm... --Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to download MySQL (fwd)
I have tried different URL's, including trying the ftp URL at Oakland, USA, and the Australian URL previously cited. I get the same problem - instead of getting a dialogue box giving me the option of saving the file to disk (and, in what path), the browser commences downloading the file, to open rather than to save the file. -- Bret Busby Armadale West Australia .. Note the path to file at the FTP site and use command-line FTP. It cannot fail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql still can't start up
- Original Message - From: [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Wednesday, September 15, 2004 9:25 AM Subject: mysql still can't start up when I used the command safe_mysqld start the system told me Starting mysqld daemon with databases from /var/lib/mysql 040913 19:08:45 mysqld ended when I used the command mysql the system told me ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Does /tmp/mysql.sock exist? What are the write permissions on /tmp? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql still can't start up
Mine is owned by root:root and has its sticky bit set. Does not allow a normal user to delete. - Original Message - From: Annie Xie [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, September 15, 2004 9:57 AM Subject: Re: mysql still can't start up Another question related to server start: Can I configure mysql.sock to some other dir, since /tmp/mysql.sock might easiely being deleted by someone? Thanks! Annie On Wed, 15 Sep 2004, Jonathan Mangin wrote: :) :)- Original Message - :)From: [EMAIL PROTECTED] :)To: mysql [EMAIL PROTECTED] :)Sent: Wednesday, September 15, 2004 9:25 AM :)Subject: mysql still can't start up :) :) :) when I used the command safe_mysqld start the system told me :) Starting mysqld daemon with databases from /var/lib/mysql :) 040913 19:08:45 mysqld ended :) when I used the command mysql the system told me :) :) ERROR 2002: Can't connect to local MySQL server through socket :)'/tmp/mysql.sock' (2) :) :) :) :)Does /tmp/mysql.sock exist? :)What are the write permissions on /tmp? :) :) :) :)-- :)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: HELP!!! SEVERE: VendorError: 1064 during INSERT
I didn't actually count the characters but it looks very close to a 512-character limit. (I used a pica stick on the screen, very accurate!) The error message appears to be showing a truncated line. Is possible? --Jon INSERT INTO dirxml.emp (fld_ind_id,fld_frname,fld_srname,fld_init,fld_sh_name1,fld_sh_name2,fld _posnacty_cd,fld_posnacty_name1,fld_posnacty_name2,fld_emp_job_desc1,fld _emp_job_desc2,fld_statrsn_cd,fld_statrsn_name1,fld_statrsn_name2,fld_rs n_cd,fld_aflgrp_cd,fld_aflgrp_name1,fld_aflgrp_name2,fld_city_t_name1,fl d_city_t_name2,fld_manager_indv_id,fld_comm_lang) VALUES(10087,SANIA,RANCOURT,,Miss,Mlle,TFR,T L VISION FRAN AISE R GIONALE,T L VISION FRAN AISE R GIONALE,MAKE-UP ARTIST,MAQUILLEUR (EUSE),R01,RETURN TO ACTIVE STATUS,REMISE EN ACTIVIT ,019,U2R,STARF,STARF,QUEBEC,QUEBEC,SYSM63,F); When I run this statement from MySQL Control Center, it work without any errors but when I execute it via java code, I get the following error: -- SEVERE: SQLException: Syntax error or access violation, message from server: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'QUEB' at line 1 -- SEVERE: SQLState: 42000 -- SEVERE: VendorError: 1064 Can someone please help what I may be doing wrong... Thanks, Shaffin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unable to connect mysql and jsp
If you've installed the jar in $JAVA_HOME/lib/ext it shouldn't need to be referenced in $CLASSPATH. - Original Message - From: Mahesh S [EMAIL PROTECTED] To: my sql [EMAIL PROTECTED] Sent: Thursday, August 05, 2004 1:36 AM Subject: unable to connect mysql and jsp hi all, i'm using RH linux 9 and MySQL(3.23.54a-11) that comes with the distro. i have installed the latest jdbc driver (mysql-connector-java-3.0.14-production-bin.jar from the msql site) for mysql and also have set the CLASSPATH correctly. despite this, t he jsp application i'm running is givring SQLException: No Suitable Driver found. what could be the problem? how can i get this rectified? please help me regards mahesh - Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connectiing
- Original Message -
From: John Berman [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, August 01, 2004 7:27 AM
Subject: Connectiing
Hi
Sorry if I'm off topic but I am a touch desperate
We make use of a mysql 4.x hosted by a third party and we use ASP to
access
this data
Our Connection string looks like this:
xDb_Conn_Str=DRIVER=org.gjt.mm.mysql.Driver;URL={jdbc:mysql://xxx/x
xx};uid=xxx;pwd=xx;
This has worked fine for months but for some reason we I use the ASP pages
we now get Cannot Find Server
I can access the Data Source using MySQL Control Center
I have replaced the url parameter with the ip address but still no joy
Any pointers really appreciated.
Regards
John Berman
[EMAIL PROTECTED]
Just starting with it myself, but...
the docs say the name of the class is com.mysql.jdbc.Driver
FWIW,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
