Jump to content

murfitUK

Members
  • Content Count

    207
  • Joined

  • Last visited

Community Reputation

0 Neutral

About murfitUK

  • Rank
    Member
  1. My brain has gone to sleep and I cannot work out how to write this simple query! Note, this is for mysql. I have a table called `calls` which is used to collect telephone call information. Some of the fields are: `id` `client_id` `date` There are others such as what the call was about, but that's not important right now. I can get the total number of phone calls received during a specified period with the query: SELECT COUNT(*) AS `total` FROM `calls` WHERE `date` BETWEEN '{$rsd}' AND '{$red}' [rsd and red are report start date and report end date] So far, so good. My problem is that I now need to find out how many clients made 1 phone call, how many made 2 calls, how many made 3 calls, etc. I do not need to know which clients called 3 times - just the total number of clients. The output of the query will be displayed something like: 241 clients called 1 time(s) 187 clients called 2 time(s) 45 clients called 3 time(s) 38 clients called 4 time(s) ... etc I've tried many combinations of count, distinct, group by etc but only succeed in getting myself even more confused. I know there is an easy way to do this but I can't find it! If anyone can help I would be very thankful.
  2. Mudsaf is correct that your website host has probably disabled this function. If you can call a script on one of your websites from a different website then it means others can do so to - and you don't want that to happen. You should google for cURL and look at some of the tutorials available. You might be able to do it that way instead, although some hosts disable this as well.
  3. murfitUK

    2 function or 1?

    Will your users know if they are regular or business users before they fill the form in? What if they fill in a business form and then find out they should have filled in the regular form (or the other way round)? To make it easier for your users - and for you - why not have just one form. Some fields will always have to be filled in (name, email etc) and some will be optional and these can all be on the same form. One of the things I have learned since taking up programming and web design is that the best way of starting a project is to get a pen and paper and write down EXACTLY what it is you want to achieve and then the steps you can take to reach it. So the first question you should ask (based on your post) if... why do I need two different forms? If you have no good answer than stick to one.
  4. murfitUK

    Mathematical help

    I'm so glad it turned out to be a php problem and not that I'd forgotten everything I had ever learned! I didn't realise that sin and cos needed radians and not degrees. Interestingly, my apache server returns PI as 0. I have to use M_PI. Don't know why. The succesful result is attached as graph2. The eventual aim is for my users to be able to generate a chart like that attached as example. At the moment they use excel but want the data stored on their client database instead. I have already got the database set up for them to enter the data and now concentrating on creating the results. Thanks everyone. graph2.pdf example.pdf
  5. murfitUK

    Mathematical help

    I am attempting to use php and a pdf creator to generate a chart. It is the mathematical bits I need help with. Basically, the chart has 7 lines (spokes) radiating at equal angles from the centre. <?php// create the pdfinclude ("class.ezpdf.php");$pdf =& new Cezpdf();$pdf -> selectFont("./fonts/Helvetica.afm"); // these value determine the position and size of the graph// (0,0 is the bottom left hand corner of the page)// x1 is the x centre of the graph$x1 = 200;// y1 is the y centre of the graph$y1 = 200;// s is the scalar$s = 100; // create the skeleton of the graphfor ($spoke=1; $spoke<=7; $spoke++) { $angle = ($spoke-1) * (360/7); $x2 = (sin($angle) * $s) + $x1; $y2 = (cos($angle) * $s) + $y1; // draw a line from (x1,y1) to (x2,y2) $pdf -> line($x1, $y1, $x2, $y2); } // output the result$pdf -> ezStream();?> The first spoke works OK but it all goes downhill from there. I have attached the pdf output so you can see what I mean. What makes this worse is that my university degree is in mathematics!!! But it was 30 years ago and I've forgotten everything I ever learned. graph.pdf
  6. After much hair-pulling and gritting of teeth I have finally solved my problem. I realised I wasn't counting sessions properly. I had COUNT(`item`.`desc`) instead of COUNT(*) which was giving incorrect results. The query I've produced is: SELECTCONCAT(`children`.`lastname`, ', ', `children`.`firstname`) AS `Name`,IFNULL(`item`.`desc`, 'Total--->') AS `Activity`,COUNT(*) AS `Sessions`,SUM(`item`.`hours`) AS `Total`FROM`record`, `children`, `item`WHERE`record`.`child_id`=`children`.`id`AND`item`.`record_id`=`record`.`id`AND`record`.`end`='0000-00-00'GROUP BY`Name`,`item`.`desc`WITH ROLLUP This give me the results I expected. However I've just realised that if two children have the exact same name then it will group them both together - the totals will still be correct but it won't differentiate between the two. I will have to include the child id and then use that to group instead of name but that will throw up its own problem because we can't use order by with groupings. So I think it would be easier to ensure we never have two children with the same name!
  7. I need some help setting up a query. There are three tables and the relevant fields are shown below: CHILDREN (id, firstname, lastname)RECORD (id, child_id, startdate, enddate) [enddate will be 0000-00-00 if not yet ended]ITEM (id, record_id, date, desc, hours) Children table might show something like17, Peter, Brown22, Sammy, Watkins Whenever a child is accepted into our service we create a record with the start date and eventually an end date when they leave. The record table will also show their support worker and other info but that's not important right now. Record table might show:6, 22, 2012-02-10, 0000-00-00 [shows Sammy's record starting 10th Feb and still ongoing as no end date]9, 17, 2012-03-25, 0000-00-00 [shows Peter starting on 25th March] Whenever a child undertakes an activity it is entered in the item table.Item table might show1, 9, 2012-03-26, Chess Club, 4.00 [Peter attended chess club for 4 hours]2, 9, 2012-03-28, Theatre Group, 2.00 [Peter - theatre group for 2 hours]3, 9, 2012-04-10, Chess Club, 3.50 [Peter - chess club for 3.5 hours]4, 6, 2012-03-28, Theatre Group, 2.00 [sammy - theatre group for 2 hours]5, 6, 2012-04-05, Music Therapy, 1.50 [sammy - music therapy or 1.5 hours]6, 9, 2012-04-17, Chess Club, 4.00 [Peter - chess club for 4 hours]7, 6, 2012-04-14, Theatre Group, 2.50 [sammy - theatre group for 2.5 hours]8, 6, 2012-04-20, Music Therapy, 1.00 [sammy - music therapy for 1 hour]and so on. Now, I need a list of all activites undertaken by each child with an open record (that is - no end date). My basic query isSELECTCONCAT(`children`.`lastname`, ' ', `children`.`firstname`) AS `name`,`item`.`desc` AS `activity`,`item`.`hours` AS `time`FROM`children`, `record`, `item`WHERE`record`.`child_id`=`children`.`id`AND`item`.`record_id`=`record`.`id`AND`record`.`end`='0000-00-00'ORDER BY `children`.`lastname`, `children`.`firstname` This gives something like:Brown Peter, Chess Club, 4.00Brown Peter, Theatre Group, 2.00Brown Peter, Chess Club, 3.50Brown Peter, Chess Club, 4.00Watkins Sammy, Theatre Group, 2.00Watkins Sammy, Music Therapy, 1.50Watkins Sammy, Theatre Group, 2.50Watkins Sammy, Music Therapy, 1.00 This will do... but I would like to group each child's activities together to count the number of sessions and then the total hours for each activity. So the results will be:Brown Peter, Chess Club, 3, 11.50 [ie he has had three sessions in the chess club totalling 11.5 hours]Brown Peter, Theatre Group, 1, 2.00 [1 session totalling 2 hours etc]Watkins Sammy, Theatre Group, 2, 4.50Watkins Sammy, Music Therapy, 2, 2.50 I'm getting confused with counts, sums and group by statements and would appreciate your help. Ideally I would also like to include some sort of roll up so that I can get Peter showing a total of 4 sessions totalling 13.5 hours and Sammy showing 4 sessions totalling 7 hours but this is not important if it is going to be too difficult.Thanks for your help.
  8. This related to MYSQL (not SQL).I have a table with a field called HBRef. This value can be null but if a value is entered it must be unique. In mysqladmin I have set the field's default value to null and the attribute to unique. Now I cannot enter a new record with HBRef as null because such a value already exists.How do I set it up so that the field HBRef has a unique value unless it is null?Thanks.
  9. It's been a while since I've used jquery but I think you do something like this:$("#date").datepicker( "option", "yearRange", '1950:2011' );
  10. Thanks Deirdre's Dad. I am updating my php as we speak - I was still on some old version. The new function certainly goes some way to simplifying things.
  11. Yes, I suppose I am just venting but my point remains: working with dates in php is not as straightforward as it should be.justsomeguy's 4 lines of code does exactly what is required and is the way I've been doing it for ages but... its still 4 lines of code just to work out next week's date.
  12. I know I can put it all into a function - ready to call up whenever its needed. But why can't it be simpler? Look at mysql. If I type this inSELECT DATE_ADD('2011-08-23', INTERVAL 7 DAY);it gives the answer. Surely php should be able to do the same just as easily? Working with dates must be a really common task when using php but it isn't really as easy as it should be.I think I am getting more impatient in my old age!
  13. I have a string with a date: $date="2011-08-23". All I want to do is add seven days to this to get "2011-08-30".There must be an easy way to do this with php. I'm fed up messing around with mktime and strtotime and exploding strings and all the rest. Please help otherwise it won't just be the string that explodes!
  14. murfitUK

    Array problem

    Sounds as though that could be the solution but not sure were I start the iteration? Do I put the for loop inside the while statement or the other way round? I've just got myself so confused with this that I can't think straight now!
  15. murfitUK

    Array problem

    Maybe there is a way to do this that is so easy I can't see it!Have a database with a table of staff schedules. The table has an ID field, a field to identify the user, a date field and a text field. Each user obviously has a unique ID. If I want to see what William's schedule (user number 12) is for April 2011 I run the querySELECT * FROM `schedule` WHERE `user`=12 AND `date` LIKE '2011-04-%' ORDER BY `date`;Then I can do the usual while ($row = mysql_fetch_array($result)){print substr($row['date'],8,2) . " - " . $row['note'] . "<br />";}which will give me something like04 - Annual Leave05 - Annual Leave14 - Training Course19 - All Day Budget Meeting27 - Out on AppointmentsWhat I would like to do is fill in a diary for the full month so the above example would look something like01 -02 - 03 - 04 - Annual Leave05 - Annual Leave06 - etcI can work out how many days there are in that particular month using the cal_days_in_month function so build the loop $i=1 to 30 etc but I just can't figure out how to use this to get the info from the query $result.Think I am just getting myself confused and can't see the wood for the trees now. All help and/or ideas gratefully received.
×
×
  • Create New...