simpli

Hi, I have a query that gets these fields: client service group service clien1 TV Basic TV clien1 INTERNET Hifh speed 1 I want to add to those fields another one that lists the number of services the number of services in each service group that the user still subscribe too. I have a query that returns the number of services each subscriber has for each service category Client service group number of services Client1 Internet 1 Client2 TV 2 Client2 Cellphone 1 That last query returns millions of rows as my company has million of users and each user can have services in several service groups (internet, TV, Cellphone etc) I am looking for a way to have the first query completed with information from the second query but only for the client in the result of the first query. I dont want it running the second query for every row of the first because then it would run a query that could return 4M lines presumably tens of thousands of times. Essentially what i want is a way to say: Hey i got this information from query one can you get me that more information from query two but only for this client, and rinse and repeat for each line of the first query. Can anyone provide some helper as to how to do this? I'm a bit stuck . Also I can't do that with several steps as I am working in a business intelligence tool that can only take query so a procedural approach wont work. Thanks kindly, JR

Hi Teddy, I dont think it does. The timestamp returns the number of seconds and that is good and does not change whether it's a leap year or not. The problem is if I want to calculate years I have to count 366*24*60*60 and leap years and 365*24*60*60 in non leap years. JR

I understand how to calculate the days but my question was more what happens with DST and leap years. Because obviously if there was a leap year between the two dates i used to calculate the timestamp difference, it's 366 days that's gonna give me a year not 365.

Hi, I have been trying to get the difference in days (or week/month/year) between two dates but I haven't been able to make it work so I'm considering doing it with timestamps. This brings a question to me. If I use a timestamp, am I still going to have to worry about DST (daylight saving times) and leap years? If so anyone can tell me how I should deal with them. Any help is wanted I am really stuck and dont know what to do / where to turn to get a coherent way to calculate those differences. Thanks in advance, JR

Hi, I have a design issue which i am struggling with. I have the following table with the following data: used_currencies Forex year Month Rate USD 2010 1 0.98 USD 2010 2 0.96 CAD 2010 1 1.01 When a user edits a form client side and saves it, he deleted, added or modified some record. The new data I have is now: Forex year Month Rate USD 2010 1 0.98 USD 2010 2 0.99 USD 2010 3 1 USD 2010 4 1.01 CAD 2010 1 1.01 AUD 2010 1 1.10 What i am currently doing is deleting the old data and adding the new stufff. That worked until now because I had not added my referential integrity. I do not want the users to add amounts in a currency format that doesn't exist in the used_currencies table therefore i need referential integrity. Can anyone recommend a better way to do this? Ideally i would have one forex row for which I would specify the rate for each month in a column but I do not know in advance the number of columns i may or may not have so I have to do it like this. Thanks for any feedback, JR

Hi all, If I echo echo intval('684') and echo intval("684") they both output 684. I have the following array: $foo ( [node_id] => 684 ). If I echo intval($foo["node_id"])) i get 0. Anyone can tell me why? thanks and best regards, JR

Hi, I need to use a RESTful process. On the server side, the data is retrieved through the rawbodypost method (zend framework). I want to make a simple test page to test the service. I want to know how to submit a RESTful post to the service. Is creating a form that posts to the RESTful url enough? How do I specify the data that should go in the rawbodypost? Thanks for clarifying, JR

You are definitely right about the one mistake can have several causes. I did reload from the same software i made the backup from. the name of the table that's giving some problem is CostTreePath. It's the only table that has capital letters and the only one giving me the issue. I don't know if there's a correlation. Also when i created the data from definition there were some errors. I don't know if some bad stuff was added with time to the database that prevented the reload but I just backed up stuff and reloaded successfully. Since it's test data that I was trying to reload I consider the issue resolved and will take it from there. Thanks you all JR

show tables does not show table x. Which is all the more puzzling. JR

I'm desperate. I am using mysql 5.0.77 on Mac with PHP Version 5.2.9. I am using navicat for mysql for mac as a client. I have a backup which I am trying to reload. When I'm trying to do that I receive a message saying table 'x' already exists. the table doesnt show up in the list of tables that were restored. when I try to create the table in phpmyadmin i get a message that the table exists. But when i try to drop it I got a message saying it doesnt exist. This is an innodb table. Anyone can tell me why I am trouble with this table? Any help is much appreciated. JR

Hi, I have an issue, I've been turning it around in my head but I don't know whats the best way to handle it. Say for example I have an example where table A contains the following field with different values (this is simplified for sake of clarity). field idAmountfrequencyrepeats 1100per month3 times I have table B which I want to be a detailed version of table A so it would take the data of table A and spit something like that: field IDmonthamount 1month 1100 1month 2100 1month 3100 So far so good. My problem is if the user decides that whats in table A is now 600 dollars for 5 months The data in table A can easily be updated to reflect that. It becomes: field idAmountfrequencyrepeats 1600per month5 times To reflect this however I cannot just update table B I have to delete everything pertaining to field ID=1 before i can add the following: field IDmonthamount 1month 1600 1month 2600 1month 3600 1month 4600 1month 5600 I don't know if this is the best way to handle this and I want your experts' advice. with this current method my worry is the fact that we are going to need to delete data from table B whenever data from table A changes. I also have the option to keep only table A and get my controllers (I'm using Zend Framework) to derive the equivalent of table B. My problem with that approach is it works for one record but since it doesnt create a table it doesnt allow me to retrieve data pertaining to several field IDs and to sum them by amount for instance. I also have looked at using stored proc but I'm just not convinced if they are the proper solution to this problem? Can anyone give me some advice? I'd like to get a good design from the start to save myself later aggravation. Thanks in advance, JR

Apart from validating the parameter at input time is there anything i can do to prevent the casting? I would like the query to return no data instead of the row with node_id = 0?? Thanks, JR

Hi, I have the following sql statement: SELECT `tree`.* FROM `test-company`.`tree` WHERE (node_id = 'dojoUnique5') . There is absolutely no record with node_id = 'dojounique5' in my table yet it returns me the record where node_id = 0. In fact if i replace dojounique5 by z or xxx, it still returns me that record. Anyone can tell me why and whats wrong with my sql?

yes it does in a sense as it shows one way of organizing the hierarchical tree data. But that's done for me. And it works. Now I'm trying to COPY a node and all its children and it's that operation I'm having trouble conceptualizing. JR

Hi, I have quite an advance issue in my database and I need your input to find out how I should tackle. I have a table (table A, auto-increment primary key) that represents tree items. Scenario_id node_id 1 28 1 29 nodes 28 and 29 are children of the same parent. there is an accompanying table (table B) that tracks the position of the items in the tree. It's called a closure table in the literature. Scenario_id ancestor_id descendant_id 1 28 28 1 29 29 1 29 37 (this above means that node 28 is is own ancestor, node 29 also and 37 is a descendant of 29) I am trying to develop a functionality where I can make a copy a whole parent item. Copying the data from table A poses no problem. It would basically copy the items I had earlier. Because of the auto-increment feature however we would have 1 30 and 1 31. At that point however, the copy is not complete. We still need to reproduce the path that the copied parent had. We cannot however copy the data that is in the closure table as it is refering to the original data. My question is therefore what's the easiest way to recreate the path? I think copying each table A line and the corresponding table B lines would be possible but I am not sure this is the most efficient way of dealing with this. Anyone can chip in and maybe point me to ways of handling this i havent tought of? Thanks in advance.

Hi, I had designed my system prior to starting developping my application but that was a long time ago and there are differences between what was drawn originally and what ended up in the database. Is there a module I can plug in to extract my tables information from mysql and show me the relationships between the different tables? (A little bit like the relationship viewer in Access). I am on a mac, I have mysql and have access to phpMyAdmin and navicat. Any help or pointers are welcome. Thanks, JR

What it actually does ( I just found out) is give me a choice from the table I am linked through referential integrity. It is in this table that the ids are 40, and 41. I guess it's giving me then the choice to enter -40,-41, 40 and 41 as they are equal the unsigned value. I'm just not sure I understand why. If the type is unsigned, shouldn't I just have 40 and 41? Thanks

Hi all, I have a primary key field that I designed as an unsigned, auto-increment integer. I did that because I figure I would get a wider range of number without needing a bigger data type. When I'm in phpMyadmin however, if I am adding a row of data directly on my table and I click on that field I have a drop down list where I have a negative AND a positive version of whatever number happens to already be in my table. For instance if in my table I had rows with the ids 40, 41 and 42, in the dropdown menu I would have choices of -40, -41, -42, 40, 41, 42. I find this a weird behaviour and I am now wondering if I did well to go with an unsigned type. Should I just stick with a double or a plain integer? Is my reasoning that an unsigned int would give me more numbers for the same space wrong? Thanks for clarifying. JR

this all started when I wanted to test if my 'non null' specification was working. I basically proceed in navcat to create a record without specifying anything in the 'non null' field, thinking that the record would be rejected as invalid but it wasnt. It was just created with nothing. That was disturbing to me as I thought the 'non null' was supposed to protect from '' as well. What everyone seems to be saying is that specifying that a field does not accept null will not be enough to ensure that it is not empty either. My understanding from the posts is that the validation to prevent the insertion of an empty field must be done in the business logic. Is that correct? JR

Hi, I am having problems with the border container when I am trying to set its width like that (width=100% height=100%) instead of (width=1200px height=1200px). I am using dojo on firefox. I am also using declarative creation for the widgets. I have a css file in which I specify the following: body, html { width:100%; height:100%; margin:0; padding:0 } #borderContainer { width:100%; height:100% } When I access the page, I see the border container and its content for half a second and then it disappears. When I look with firebug, I see <div id="dijit_layout_BorderContainer_0" class="dijitContainer dijitBorderContainer" livesizing="true" style="padding: 0px; width: 100%; height: 100%;" design="headline" dojotype="dijit.layout.BorderContainer" widgetid="dijit_layout_BorderContainer_0"> </div> <div style="display: none;"/> Can someone help me fix this? Thanks, JR

Yikes, you mean that with the data type only I can't control that no null or empty string are entered? That's a bit of a surprise I must say. JR

the datatype of my column is indeed varchar. I want it to be not null but I also want it to be not empty. How can I specify this?

Hi, I have created a column in mysql with the a utf8 charset and unicode_ci collation. I also set the null options to no, as I do not want null values in that field. When I enter data in it however I am able to leave it blank and the it's accepted. This is counterintuitive and is contrary to the behavior I want for that field. When I look at the data in navicat or phpmyadmin with the hexa view I see nothing at all so it seems there is really nothing in the field. Can anyone explain to me why a field that is set to explicitly not accept nulls does accept them. And what can I do to ensure that a field is not empty or null? Thanks for the help, JR

Hi, my previous question was too general so I refocused it in the hope that it will be easier maybe to address. If I create a layout made of 5 bordercontainers (Zend / dojo) (top,bottom,left,right and center) and I want to assign a tree to the left border container and a list of item to the right border container, what is the proper way to do this using ZF + dojo? Thanks for any help, JR

Hello, I'l try to keep this short. Let's say in my ZF MVC application I have so far 3 objects with each their model, view and controller. - Object 1 is a tree, through wich users will be able to add, remove and displace items. - Object 2 is a list of items that can be drag and dropped into the tree. - Object 3 is the detail of the items. I have these three objects which make up different part of my application. My question is: Do I need an "application" object that I would create over these 3 objects that would in fact instantiate each one and mesh it all together? Thanks for clarifying. JR