Illusion

Correct!!! My bad. Time to refresh my SQL skills

check this http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_hour and you don't need use date_format in condition date_format(tblOM.CreatedOn,'%Y-%m-%d')=curdate()) ;

This results duplicates. we need to exclude the records which are returned from first query by adding where clause in second query in the union .i.e " where t.date > '2012-05-03' . I still feel like there can be more optimal way to do this by making union as subquery.

You should use alias names as it is - in this case it doesn't magically know you are dealing with dates 11262012 is not same as 20121126

You can run mysqldump from php script if you have dedicated server for your site or privileges to run the invoke commands (which will not be a case with shared hosting).

from mysql documentation in that case the id range might be mysql_insert_id() to mysql_insert_id() + number of effected rows -1.

First of all the query syntax you have mentioned in your post is not correct. Changing extension form csv to sql will not have any effect , it will be still written as a flat file. If you want to dump table as a sql file you need use mysqldump.

You figured it out or you still need help ?

I would suggest you to have separate query to check whether last home game is a tie or not ... if it is a tie consider that current winning streak is 0 else take the value from the first query output select a.home, if(fth=fta,1,0 ) as tie from engp a inner join (select home,MAX(`date`) as lastdate from engp group by home) b on a.`date`=b.lastdate and a.home=b.home or select home, if(fth=fta,1,0 ) as tie from engp where `date`=(select `date` from engp order by date desc limit 1) if you want to combine , you could combine both queries as well.

Hmm, this is a my solution... set @count:=0,@home:=''; select b.home, MAX(b.flag2) as longestsequence from (Select home,if( @home!=home,@count:=0,@count) as flag1,@home:=home, ( case when fth > fta then @count:=@count+1 else @count:=0 end) as flag2 from (select home,away,fth,fta from engp where season = 'Premier League 11/12' order by home,`date`) a)b group by home order by MAX(b.flag2) desc ; regarding your other question Arsenal last home game is 8.4.2012 00:00:00 Arsenal Man City 1 0 and its not tied. how the current winning streak is calculated actually ?

I think I got it wrong.... Can anybody explain me how a winning streak is calculated? I am not into these sports

If I understand correctly... you want teams order by number of home wins they have in the current season Select home, sum( case when fth > fta then 1 else 0 end) as homewinmatchcount from engp where league = 'Premier League 11/12' group by home order by homewinmatchcount desc

Unfortunately not. A good practice may be inserting the record with a different version by having version column. if you are updating all the columns except id then actually you want to do a insert(assuming there are constraints in doing so ) and delete the old record.

Check this.. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_month

If I am not wrong ...I don't think you can combine any of these queries as none of queries output is subset of any other output. If queries are longer time find a way to optimize them.

Alias example http://www.w3schools.com/sql/sql_alias.asp

UPDATE users SET score_points = score_points + :score_points,mood_points = ( case when mood_points + :mood_points > 99 then 100 when mood_points+:mood_points < -99 then -100 else mood_points+:mood_points end case ) WHERE id = :id

It fetches the records in the order he wanted........... duplicates listed first and then single ones and its up to him how implements the display part. If he wants pure sql solution then he could just use group_concat. Select a.DOB,group_concat ( (a.firstname+ ' '+a.lastname) separator ',' ),b.cnt from info a inner join (select DOB ,Count(*) as cnt from info group by DOB ) b on a.DOB=b.DOB group by a.DOB order by b.cnt desc or Select DOB,group_concat ( (firstname+ ' '+lastname) separator ',' ),count(id) as cnt from info group by DOB order by cnt desc

try this... Select a.DOB,a.firstname,a.lastname,b.cnt from info a inner join (select DOB ,Count(*) as cnt from info group by DOB ) b on a.DOB=b.DOB order by b.cnt desc

Don't you think those two statements contradicting each other? You need to some some how maintain the sequence . Table Buses : BusID, RouteID Table BusStops: RouteID, PlaceID, SequenceID Table Places: PlaceID, PlaceName You could assign sequnceid for a place... something like this RouteID PlaceID SequnceID 12 34 10 12 23 20 12 45 30 12 12 40 suppose , in future if you want to add any station between places 34 and 23 - you could insert record with sequenceid is that > 10 and <20.

See it for yourself by run the queries using EXPLAIN from any sql client.

Why you need totalhectares column in your table when you are not storing any data into it? Here php solution for your problem Declare two variable for totals and accumulate fieldhectares and landhectares values into these two variables while iterating through the rows <?php require("html2fpdf.php"); $server = ''; $username = ''; $password = ''; $database_name=''; $dbconn = mysql_connect($server, $username,$password,false) or die("Could not establish connection"); mysql_select_db($database_name, $dbconn) or die ("Could not select database"); if (!$dbconn) { die('Something went wrong while connecting to MSSQL'); } ob_start(); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <meta content="text/html; charset=ISO-8859-1" http-equiv="content-type"> <title></title> <h5 align="center"><U>confidential</U></h5> </head> <body> <table style="text-align: left; width: 715px; height: 32px;" border="1" cellpadding="2" cellspacing="2"> <tbody> <tr> <td colspan="2" rowspan="1" style="font-style: italic;">List-Field</td> </tr> <tr> <td style="font-style: italic;">Field No</td> <td style="font-style: italic;">Ex Field No</td> <td style="font-style: italic;">Type</td> <td style="font-style: italic;">Planting Year</td> <td style="font-style: italic;">Field Hectares</td> <td style="font-style: italic;">Reference</td> <td style="font-style: italic;">Location ID</td> <td style="font-style: italic;">Statement Year</td> <td style="font-style: italic;">Total Hectares</td> <br /> </tr> <? $total1=0; $query="SELECT * FROM tblfield"; $result=mysql_query($query); while($row=mysql_fetch_array($result)) { $fieldno = $row['fieldno']; $exfieldno = $row['exfieldno']; $type = $row['type']; $plantingyear = $row['plantingyear']; $fieldhectares = $row['fieldhectares']; $total1=$total1+intval($fieldhectares); $reference = $row['reference']; $locationid = $row['locationid']; $statementyear = $row['statementyear']; $totalhectares = $row['totalhectares']; echo "<tr><td>$fieldno</td>"; echo "<td>$exffieldno</td>"; echo "<td>$type</td>"; echo "<td>$plantingyear</td>"; echo "<td>$fieldhectares</td>"; echo "<td>$reference</td><"; echo "<td>$locationid</td>"; echo "<td>$statementyear</td>"; echo "<td>$totalhectares</td></tr>"; } echo "<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>Total</td><td>$total1</td></tr>"; ?> </tbody> </table> <table style="text-align: left; width: 715px; height: 32px;" border="1" cellpadding="2" cellspacing="2"> </tbody> <tr> <td colspan="2" rowspan="1" style="font-style: italic;">List-Land Utilities</td> </tr> <tr> <td style="font-style: italic;">Description</td> <td style="font-style: italic;">Land Hectares</td> <td style="font-style: italic;">Statement Year</td> <td style="font-style: italic;">Location ID</td> <td style="font-style: italic;">Total Hectares</td> </tr> <? $total2=0; $query="SELECT * FROM tbllandutilities"; $result=mysql_query($query); while($row=mysql_fetch_array($result)) { $description = $row['description']; $landhectares = $row['landhectares']; $total2=$total2+intval($landhectares); $statementyear = $row['statementyear']; $locationid = $row['locationid']; $totalhectares = $row['totalhectares']; echo "<tr><td>$description</td>"; echo "<td>$landhectares</td></tr>"; echo "<td>$statementyear</td></tr>"; echo "<td>$locationid</td></tr>"; echo "<td>$totalhectares</td></tr>"; } echo "<tr><td></td><td></td><td></td><td>Total</td><td>$total2</td></tr>"; echo "<tr><td>Grand Total</td><td>$total1 + $total2</td></tr>"; ?> </tbody> </table> <br> </body> </html> <?php $var = ob_get_clean(); $pdf = new HTML2FPDF('P', 'mm', 'Letter'); $pdf->AddPage(); $pdf->WriteHTML($var); $pdf->Output('test.pdf', 'I'); ?>

Seems like the data sorted by javascript. You need to check that.

@PFMaBiSmAd Oops... thanks for clarifying. I should have looked at HTML carefully .

I didn't understand this part <tr> <td><?php echo $row[2] ?></td> <td><?php echo $row[1] ?></td> <td><?php echo $row[4] ?></td> <td><a href="#"><?php echo $row[3] ?></a></td> </tr> if you are scrambling the rows ... how do you expect them to be displayed in the same order you have fetched the records from database?