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.