Barand
-
Posts
24,573 -
Joined
-
Last visited
-
Days Won
824
Everything posted by Barand
-
Please help with database query -- PHP Beginner
Barand replied to PNMorrison's topic in PHP Coding Help
My apologies to you, creating from format will create a usable date object. Put it down to my early morning low caffeine levels. (I guess my comment is still a valid warning not to format dates until final output. Work with yyy-mm-dd.) -
Don't resurrect old posts. $s and $e are the start and end angles of the arc, as imagearc() in the manual would tell you. The text is centred on the midpoint of the arc. (NOTE: the angles for the text are in a clockwise direction, whereas for imagearc they are anticlockwise) Some of the lines were out of order in that old post, so I have changed the top few lines <?php /********************************************* * Fitting text to arc * * TextOnArc * Author : Barand August2007 **********************************************/ $im = imagecreate(400,400); $white = imagecolorallocate($im, 0xFF, 0xFF, 0xFF); $grey = imagecolorallocate($im, 0xCC, 0xCC, 0xCC); $txtcol = imagecolorallocate($im, 0xFF, 0x00, 0x00); $r = 150; $cx = 200; $cy = 200; $txt1 = 'Text on an Arc'; $txt2 = 'by Barand'; $font = 'c:/windows/fonts/arial.ttf'; $size = 48; $pad = 2; // extra char spacing for text $s = 180; $e = 360; imagearc($im,$cx,$cy,$r*2,$r*2,$s,$e,$grey); textOnArc($im,$cx,$cy,$r,$s,$e,$txtcol,$txt1,$font,$size,$pad); $s = 90; $e = 90; $pad = 3; // extra char spacing for text $size = 24; textInsideArc($im,$cx,$cy,$r,$s,$e,$txtcol,$txt2,$font,$size,$pad); header("content-type: image/png"); imagepng($im); imagedestroy($im); function textWidth($txt, $font, $size) { $bbox = imagettfbbox($size,0,$font,$txt); $w = abs($bbox[4]-$bbox[0]); return $w; } function textOnArc($im,$cx,$cy,$r,$s,$e,$txtcol,$txt,$font,$size, $pad=0) { $tlen = strlen($txt); $arccentre = ($e + $s)/2; $total_width = textWidth($txt, $font, $size) - ($tlen-1)*$pad; $textangle = rad2deg($total_width / $r); $s = $arccentre - $textangle/2; $e = $arccentre + $textangle/2; for ($i=0, $theta = deg2rad($s); $i < $tlen; $i++) { $ch = $txt{$i}; $tx = $cx + $r*cos($theta); $ty = $cy + $r*sin($theta); $dtheta = (textWidth($ch,$font,$size))/$r; $angle = rad2deg(M_PI*3/2 - ($dtheta/2 + $theta) ); imagettftext($im, $size, $angle, $tx, $ty, $txtcol, $font, $ch); $theta += $dtheta; } } function textInsideArc($im,$cx,$cy,$r,$s,$e,$txtcol,$txt,$font,$size, $pad=0) { $tlen = strlen($txt); $arccentre = ($e + $s)/2; $total_width = textWidth($txt, $font, $size) + ($tlen-1)*$pad; $textangle = rad2deg($total_width / $r); $s = $arccentre - $textangle/2; $e = $arccentre + $textangle/2; for ($i=0, $theta = deg2rad($e); $i < $tlen; $i++) { $ch = $txt{$i}; $tx = $cx + $r*cos($theta); $ty = $cy + $r*sin($theta); $dtheta = (textWidth($ch,$font,$size)+$pad)/$r; $angle = rad2deg(M_PI/2 - ($theta - $dtheta/2)); imagettftext($im, $size, $angle, $tx, $ty, $txtcol, $font, $ch); $theta -= $dtheta; } } ?>
-
Please help with database query -- PHP Beginner
Barand replied to PNMorrison's topic in PHP Coding Help
You cannot compare dates in "January 2nd 1970" format. For example, April 30th 2015 is less than the above date. You have to use yyyy-mm-dd format to correctly complare. -
I need to know a couple of things before I can give you an answer. How are you outputting them to the page (the code)? What are the structures of those two tables?
-
to do the same without the join() you could $tableHeads = '<tr>'; foreach ($names as $n) { $tableHeads .= "<th>$n</th>"; } $tableHeads .= '</tr>'; echo $tableHeads;
-
need to check an array for matching combinations
Barand replied to severndigital's topic in PHP Coding Help
Does that return TRUE for blue because there is more than blue, or because you have head,body,feet all blue? -
Easiest way to get the form fields is to use serialize(). Try this example testing.html <html> <head> <title>Example</title> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script type='text/javascript'> $().ready(function() { $('#BtnInserisci').click(function() { var formdata = $("#form1").serialize(); $.post( "testing.php", formdata, function(html) { $('#BtnInserisci').before(html); }, 'html' ) }) }) </script> </head> <body> <form id='form1'> Field 1: <input type='text' name='field1' value='aaaa'><br> Field 2: <input type='text' name='field2' value='bbbb'><br> Field 3: <input type='text' name='field3' value='cccc'><br> <input type='button' name='BtnInserisci' id='BtnInserisci' value='Test'> </form> </body> </html> testing.php <?php $html = ''; if ($_SERVER['REQUEST_METHOD']=='POST') { $html = '<div style="width: 200px; border: 1px solid gray; margin:10px;padding:5px">'; foreach ($_POST as $k => $v) { $html .= "<p>$k : $v</p>"; } $html .= "</div>\n"; } echo $html; ?>
-
That is creating a "cartesian join" on those two tables which will join every record in one table with every record in the other. If that isn't what you want to do, you should use explicit join syntax as you have with the other table (ie FROM A JOIN B ON ....)
-
As muddy_funster said, don't use SELECT *. You table output only requires county, population and recruiter names so those are all you need to select. SELECT c.name as county_name , c.population , r.name as recruiter FROM recruiters r INNER JOIN counties c ON r.county_id = c.id INNER JOIN states s ON c.state_id = states_id WHERE s.id = $state_id ORDER BY county_name Alternatively, if you want all the recruiter names for a county in a single row you can SELECT c.name as county_name , c.population , GROUP_CONCAT(r.name SEPARATOR ', ') as recruiters FROM recruiters r INNER JOIN counties c ON r.county_id = c.id INNER JOIN states s ON c.state_id = states_id WHERE s.id = $state_id GROUP BY county_name
-
revised results
-
You should use prepared queries. It avoids that sort of problem.
-
I think it's fixed. Try SELECT s.week , CONCAT(DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 DAY, '%e %b'),' - ', DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 + 6 DAY, '%e %b')) as wkcomm , @slab:=@slab+ifnull(slabcount,0)-@driedcf as slab_count , @dried:=@dried+ifnull(driedcount,0)-@tiledcf as dried_count , @driedcf:=ifnull(driedcount,0) as x1 , @tiledcf:=ifnull(tiledcount,0) as x2 FROM ( SELECT @wk := 0 as init0 , @day1 := '2015-01-04' as init1 , @slab := (SELECT count(lot_id) FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE slab_date < @day1 AND IFNULL(dried_in_date,'9999-12-31')>=@day1) as init2 , @dried := (SELECT COUNT(lot.lot_id) FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) LEFT JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date < @day1 WHERE dried_in_date < @day1 AND s.lot_id IS NULL) as init3 , @driedcf := 0 as init4 , @tiledcf := 0 as init5 ) init INNER JOIN ( SELECT COUNT(lot_id) as slabcount , DATE_FORMAT(slab_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X') GROUP BY week ) s LEFT JOIN ( SELECT COUNT(lot_id) as driedcount , DATE_FORMAT(dried_in_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE DATE_FORMAT(dried_in_date, '%X') = DATE_FORMAT(@day1, '%X') AND dried_in_date is not null GROUP BY week ) d using (week) LEFT JOIN ( SELECT COUNT(s.lot_id) as tiledcount , DATE_FORMAT(complete_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) INNER JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date IS NOT NULL WHERE DATE_FORMAT(complete_date, '%X') = DATE_FORMAT(@day1, '%X') GROUP BY week ) t using (week) ;
-
Have you had a look at HighCharts http://www.highcharts.com/demo/line-basic
-
Moving the "init" subquery to the top seems to cure the problem SELECT s.week , CONCAT(DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 DAY, '%e %b'),' - ', DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 + 6 DAY, '%e %b')) as wkcomm , @slab:=@slab+ifnull(slabcount,0)-@driedcf as slab_count , @dried:=@dried+ifnull(driedcount,0)-@tiledcf as dried_count , @driedcf:=ifnull(driedcount,0) as x1 , @tiledcf:=ifnull(tiledcount,0) as x2 FROM ( SELECT @wk := 0 as init0 , @day1 := '2015-01-04' as init1 , @slab := (SELECT count(lot_id) FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE slab_date < @day1 AND IFNULL(dried_in_date,'9999-12-31')>=@day1) as init2 , @dried := (SELECT COUNT(lot.lot_id) FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) LEFT JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date < @day1 WHERE dried_in_date < @day1 AND s.lot_id IS NULL) as init3 , @driedcf := 0 as init4 , @tiledcf := 0 as init5 ) init INNER JOIN ( SELECT COUNT(lot_id) as slabcount , DATE_FORMAT(slab_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X') GROUP BY week ) s LEFT JOIN ( SELECT COUNT(lot_id) as driedcount , DATE_FORMAT(dried_in_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X') AND dried_in_date is not null GROUP BY week ) d using (week) LEFT JOIN ( SELECT COUNT(s.lot_id) as tiledcount , DATE_FORMAT(complete_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) INNER JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date IS NOT NULL WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X') GROUP BY week ) t using (week) ;
-
It may be that the @day1 value isn't setting properly at start of first run. x1 and x2 are just the carry forward values in the SELECT clause. I named them as that as they are just "dummy" values as far the required output is concerned. I pasted my results into MS Excel to get the chart
-
An explanation of what this query is doing: First it counts the slab, dried_in and completed type1 dates for each week. The slab count is a cumulative total but, each week, subtracting those that were dried_in in the previous week (@driedcf). Similarly, a cumulative total of the dried_ins as also maintained and the number of completed type 1 from the previous week (@tiledcf) is subtracted. There is also an initialization subquery which counts the outstanding slabs and drieds ins prior to the start date to get the starting figures. SELECT s.week , CONCAT(DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 DAY, '%e %b'),' - ', DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 + 6 DAY, '%e %b')) as wkcomm , @slab:=@slab+ifnull(slabcount,0)-@driedcf as slab_count , @dried:=@dried+ifnull(driedcount,0)-@tiledcf as dried_count , @driedcf:=ifnull(driedcount,0) as x1 , @tiledcf:=ifnull(tiledcount,0) as x2 FROM ( SELECT COUNT(lot_id) as slabcount , DATE_FORMAT(slab_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X') GROUP BY week ) s INNER JOIN ( SELECT @wk:=0 , @day1:='2015-01-04' , @slab:=(SELECT count(lot_id) FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE slab_date < @day1 AND IFNULL(dried_in_date,'9999-12-31')>=@day1) , @dried:=(SELECT COUNT(lot.lot_id) FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) LEFT JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date < @day1 WHERE dried_in_date < @day1 AND s.lot_id IS NULL) , @driedcf:=0 , @tiledcf:=0 ) init LEFT JOIN ( SELECT COUNT(lot_id) as driedcount , DATE_FORMAT(dried_in_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X') AND dried_in_date is not null GROUP BY week ) d using (week) LEFT JOIN ( SELECT COUNT(s.lot_id) as tiledcount , DATE_FORMAT(complete_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) INNER JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date IS NOT NULL WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X') GROUP BY week ) t using (week) ;
-
Please help with database query -- PHP Beginner
Barand replied to PNMorrison's topic in PHP Coding Help
"Jan 1st 1970" is unix day 0 and is often the result of trying to process an invalid date format -
Revised results taking into account the slabs and dried_ins outstanding prior to the start of the year
-
these are the results I'm getting so far
-
How long should a slab take to dry in? In 2015, there were 760 which dried_in on the same day as the slab_date. Of the rest there were significant numbers taking several weeks, months even, to dry in. In 2014, some took over 1 year to dry in (one took 502 days). SELECT drytime as drying_time , COUNT(lot_id) as No_of_lots FROM ( SELECT lot_id , datediff(dried_in_date, slab_date) as drytime FROM ben.lot WHERE dried_in_date >= slab_date AND year(slab_date)=2015 ) diffs GROUP BY drying_time
-
Let's make sure we both understand the rules. Lot #187 has a dried_in date in week 1 so is counted as dried_in for that week. Its schedule for work_type_1 is completed so, according to the bold type in the explanation you gave above, this is the last week we count it as dried in. This would give counts of 1, 1, 1 for the three weeks if you apply your rules
-
here's an example <?php $dates = [ '2015-09-12', '2015-10-15', '2015-11-23', '2015-12-08', '2015-12-28' ]; $now = new DateTime(); $tdata = ''; foreach ($dates as $dt) { $closed = new DateTime($dt); $age = $now->diff($closed)->days; $dateout = $closed->format('m/d/Y'); if ($age > 90) { $class = 'over90'; } elseif ($age > 60) { $class = 'over60'; } elseif ($age > 30) { $class = 'over30'; } else $class = 'default'; $tdata .= "<tr><td class='$class'>$dateout</td></tr>"; } ?> <html> <head> <title>Example</title> <style type='text/css'> td.default {color: black;} td.over30 {background-color: green; color: white;} td.over60 {background-color: orange; color: white;} td.over90 {background-color: red; color: white;} </style> </head> <body> <table> <?=$tdata?> </table> </body> </html>
-
That gives you a week or so, then, to do some data cleansing.
-
How recent is "now". It was still happening in November. Yes.
-
Another 21 when complete_date compared against the slab date SELECT lot.lot_id , lot.slab_date , s.complete_date , work_type_id , work_type_description as descrip FROM schedule s INNER JOIN lot USING (lot_id) INNER JOIN work_type USING (work_type_id) WHERE slab_date > complete_date; +--------+------------+---------------+--------------+--------------+ | lot_id | slab_date | complete_date | work_type_id | descrip | +--------+------------+---------------+--------------+--------------+ | 1219 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1220 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1221 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1222 | 2015-04-03 | 2015-03-27 | 1 | Tile | | 1223 | 2015-04-03 | 2015-03-27 | 1 | Tile | | 1269 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1270 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1271 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 1272 | 2015-04-03 | 2015-03-25 | 1 | Tile | | 11 | 2014-10-20 | 2013-09-03 | 1 | Tile | | 144 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 145 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 146 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 264 | 2015-03-24 | 2015-03-17 | 1 | Tile | | 661 | 2015-04-09 | 2015-03-17 | 1 | Tile | | 1273 | 2015-04-03 | 2015-03-17 | 1 | Tile | | 2300 | 2015-08-14 | 2015-05-29 | 1 | Tile | | 1537 | 2015-05-11 | 2015-05-08 | 2 | Backsplash | | 1219 | 2015-04-03 | 2015-03-25 | 4 | Bath tile | | 2300 | 2015-08-14 | 2015-05-19 | 15 | Window sills | | 1932 | 2015-09-01 | 2015-08-24 | 15 | Window sills | +--------+------------+---------------+--------------+--------------+ 21 rows in set (0.04 sec) and another 8 where it was dried_in before the slab was laid SELECT lot_id , slab_date , dried_in_date FROM lot WHERE dried_in_date < slab_date; +--------+------------+---------------+ | lot_id | slab_date | dried_in_date | +--------+------------+---------------+ | 311 | 2014-12-30 | 2014-10-08 | | 315 | 2014-10-08 | 2014-09-12 | | 316 | 2014-10-08 | 2014-09-12 | | 809 | 2015-03-30 | 2015-03-26 | | 942 | 2015-02-02 | 2014-02-02 | | 1541 | 2014-11-25 | 2014-02-19 | | 1601 | 2015-04-17 | 2015-03-17 | | 1629 | 2015-04-18 | 2015-03-18 | +--------+------------+---------------+ 8 rows in set (0.02 sec) Lots of "impossible" things going on. Looks like someone's going to be busy on Monday
