IRC Logs

Network: freenode Channel: #mysql

	Enter your search terms Submit search form
	Web www.irclog.org

[02:45]<yjuzpzzzdyrd>StR: Check out alter table
[02:47]<2j22fn>Xgc, I can make ti work with select and a real date, but not with update and a value from the field
[02:47]<2j22fn>dkr, can the specific point in time not be the current value of a field?
[02:48]<mcz>0000-00-20 00:00:00 is not in the valid range for a a datetime field, yuo can only have years like 1000 to 9999 or somethign like that
[02:48]<mcz>there we go: "The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'."
[02:49]<2j22fn>I see, so perhaps I should be using addtime or something instead?
[02:49]<mcz>or just use an int field to store number of seconds for a duration
[02:49]<mcz>or days, if that's all the granularity you need
[02:50]<2j22fn>that is a very good idea...
[02:50]<cjffygyqk>Anybody here familiar with Rollup?
[02:51]<2j22fn>thanks dkr, I will turn that in my brain a bit and see if it works for me.
[02:51]<xra>CollinY2K: Ask your question.
[02:51]<2j22fn>and thanks Xgc too :)
[02:51]<xra>bobble: No problem.
[02:51]<cjffygyqk>SELECT price, COUNT(*) FROM sold WHERE uid = $user->uid AND product = $product GROUP BY price WITH ROLLUP ; How would I point to the count statements since two will come out?
[02:52]<xra>CollinY2K: Hmmm... The rollup rows will have all other fields/columns null.
[02:54]<cjffygyqk>okay...?
[02:54]<xra>CollinY2K: The normal counts will not. Check the group by fields for null. Now, there's that one chance your group by value was null. In that case you'll want to make sure there's another field available.
[02:54]<xra>CollinY2K: In your example, price is probably never null, correct?
[02:55]<cjffygyqk>It shouldn't be
[02:55]<xra>CollinY2K: If that's true, you can look for rows where the price is null to mean you have a rollup row.
[02:56]<cjffygyqk>Hm...not sure that's what I want
[02:56]<cjffygyqk>if you look a this screenshot: www.neohangout.com/temp.jpg that is basically what I want to come out.
[02:56]<cjffygyqk>Given, I didn't add the product name in that example about
[02:56]<cjffygyqk>*above
[02:59]<xra>CollinY2K: That doesn't show any rolled up values.
[02:59]<cjffygyqk>I'm used to setting my table up by inserting the pieces with something like $info->product, $info->price etc. Normally I would do Count(*) AS count and use $info->count. Is there no way to point to the rollup value?
[02:59]<cjffygyqk>what do you mean?
[02:59]<cjffygyqk>Sold At Any Price would be the rollup value
[03:02]<xra>CollinY2K: Show the SQL. Why would you need ROLLUP for this?
[03:02]<cjffygyqk>Because I want to be able to order by the total sold reguardless of price
[03:03]<cjffygyqk>like....here, let me explain what I want
[03:04]<cjffygyqk>I have product, price, count. I am displaying every product at each different price it has been sold at along with how many of that product were sold at the price. However, I also want to display how many were sold at Any price.
[03:05]<cjffygyqk>it's not shown in that screenshot since I couldn't do the ordering like I want when I made that, but right after the first row this would be the line: Red Satin Collar, 1, 2536, 51
[03:07]<cjffygyqk>Since Red Satin Collar was sold at two different prices, it's displayed twice....once for each price. It displays the product name, the number sold at that price, the price it was sold at, then the number sold at any price (thus 51 for both rows).
[03:07]<xra>CollinY2K: I wouldn't use rollup for that.
[03:07]<cjffygyqk>Okay, well, rollup was simply someone else suggested to me for doing this
[03:07]<cjffygyqk>What would you suggest?
[03:08]<xra>CollinY2K: Just join with another derived table that provides the separate aggregate for number sold at any price.
[03:09]<vnr2jw999>Is there any good way to dump an old mysql 3 database and import it in to a mysql 4 server?
[03:09]<cjffygyqk>I'm sorry I'm new to the whole sql thing so not quite following some of your terms.
[03:09]<xra>vegbox444: Look at the upgrade notes.
[03:09]<xra>CollinY2K: One moment.
[03:11]<vnr2jw999>dang
[03:11]<vnr2jw999>for all the little upgrades
[03:11]<vnr2jw999>or big upgrades?
[03:14]<xra>CollinY2K: SELECT v1.item, v1.price, v1.cnt, v2.cnt FROM ( SELECT item, price, COUNT(*) cnt FROM table1 GROUP BY item, price ) v1 JOIN ( SELECT item, COUNT(*) cnt FROM table1 GROUP BY item ) v2 ON v1.item=v2.item ;
[03:14]<xra>CollinY2K: You can add the ORDER BY to that.
[03:15]<xra>CollinY2K: SELECT v1.item, v1.price, v1.cnt, v2.cnt FROM ( SELECT item, price, COUNT(*) cnt FROM table1 GROUP BY item, price ) v1 JOIN ( SELECT item, COUNT(*) cnt FROM table1 GROUP BY item ) v2 ON v1.item=v2.item ORDER v1.cnt DESC;
[03:15]<xra>CollinY2K: That's ordered by the total sold at that price.
[03:15]<cjffygyqk>Interesting...hm.
[03:16]<tjmjigtx>vegbox444: http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html
[03:16]<xra>CollinY2K: Sorry. I meant ORDER BY.
[03:16]<cjffygyqk>no problem
[03:17]<cjffygyqk>I understand most of that, but little confused how it works with selecting from another select
[03:18]<xra>CollinY2K: Are you familiar with VIEWs?
[03:18]<cjffygyqk>I don't believe so.
[03:18]<xra>CollinY2K: Think of v1 and v2 as views.
[03:18]<xra>CollinY2K: Well, look at it this way.
[03:18]<cjffygyqk>I undestand the v1 v2
[03:19]<xra>CollinY2K: Think of a table that was based on a SELECT of other base tables. The only data is the data from the base tables.
[03:19]<cjffygyqk>The part I don't understand the workings of is the FROM (SELECT STATEMENT)
[03:19]<xra>CollinY2K: CREATE VIEW fake_table1 AS SELECT item, price, COUNT(*) cnt FROM table1 GROUP BY item, price;
[03:19]<xra>CollinY2K: Now if we: SELECT * FROM fake_table1;
[03:20]<xra>CollinY2K: We see the result of that GROUP BY query above.
[03:20]<xra>CollinY2K: But this result is done dynamically, as though you ran SELECT item, price, COUNT(*) cnt FROM table1 GROUP BY item, price; directly.
[03:21]<xra>CollinY2K: You can now: SELECT * FROM fake_table1 WHERE cnt > 100;
[03:21]<xra>CollinY2K: Are you following?
[03:21]<cjffygyqk>Totally confused, but let me go see if I can set this up to my own table and so forth and see if I can get it to work. I may understand better ater that.
[03:21]<cjffygyqk>*after
[03:21]<xra>CollinY2K: Ok. Log this conversation and maybe it'll make sense later.
[03:22]<mrvmuga>has anyone configured nagios to monitor a slave replica?
[03:22]<xra>CollinY2K: The idea is to use the result of those sub-selects as though they were tables themselves.
[03:22]<xra>CollinY2K: The SELECT list of each sub-select becomes the columns of that derived table.
[03:23]<xra>CollinY2K: The field names or aliases become the column names of that derived table.
[03:24]<xra>CollinY2K: Take any of your tables and try this: SELECT v1.* FROM (SELECT * FROM your_table) v1;
[03:25]<cjffygyqk>I think I'm beginning to understand it
[03:25]<xra>CollinY2K: If your_table contains an integral id field: SELECT v1.* FROM (SELECT t1.id+1 id2, t1.* FROM your_table t1) v1;
[03:26]<xra>CollinY2K: That adds a new column that contains your normal id plus 1.
[03:26]<xra>CollinY2K: It's pretty simple. Just read the above slowly.
[03:27]<xra>v1 represents what is called a derived table.
[03:28]<xra>CollinY2K: Those sub-selects/derived tables can be about as complex as you need, containing other JOINs, WHERE clauses, GROUP BY expression, etc.
[03:29]<xra>CollinY2K: In fact, those derived tables can be based on other derived tables. VIEWs are a similar concept, described by that fake_table1 I showed above.
[03:31]<cjffygyqk>see anything wrong with this: $query = "SELECT v1.product, v1.price, v1.count, v2.count FROM ( SELECT product, price, COUNT(*) count FROM sold GROUP BY product, price ) v1 JOIN ( SELECT product, COUNT(*) count FROM sold GROUP BY product ) v2 ON v1.product=v2.product ORDER v1.count DESC LIMIT $eu, $limit";
[03:31]<cjffygyqk>oh, I forgo to fix order by...
[03:32]<xra>CollinY2K: The PHP expansion is a problem when trying to diagnose SQL issues. You should show expanded SQL without the PHP variables.
[03:32]<cjffygyqk>Sorry, what's that?
[03:33]<xra>CollinY2K: You should show raw SQL, without the PHP $variable fluff.
[03:33]<cjffygyqk>oh
[03:33]<xra>CollinY2K: If your PHP expands to something invalid, there's no way to know here.
[03:34]<cjffygyqk>expands?
[03:34]<xra>CollinY2K: s/expands/resolves/
[03:35]<xra>CollinY2K: The SQL string generated could be complete garbage if the PHP variables contain non-sense.
[03:36]<xra>CollinY2K: In this case, it only impacts the LIMIT clause.
[03:37]<cjffygyqk>#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'v1.count DESC LIMIT 0, 30' at line 1
[03:38]<xra>"CollinY2K: Sorry. I meant ORDER BY."
[03:38]<xra>"[20:14] <CollinY2K> no problem"
[03:38]<xra>CollinY2K: You forgot to change that.
[03:39]<cjffygyqk>Oh, I recopied that's why

Back to #mysql
Or go to some related logs:
#netbsd
#blender
#php
#cisco
#php