help my consecutive number problem

[abrew]

hi ,,, sorry bothering you

i'm new in php mysql, and i've problem in "consecutive number"

then i found clue in here :

 

http://www.phpfreaks.com/forums/index.php/topic,228984.0.html

 

it's really working with my problem but i got the result is always "0" in the first row.

here's my code :

 

$strSQL="SELECT * FROM station order by ID";

$rs=mysql_query($strSQL);

while($row=mysql_fetch_array($rs)){

.

.

.

$wet = "SELECT @count := 0";

$wet = "SELECT @count := IF(TotRain > 0 , @Count + 1, 0) as wetcnt FROM daily_syn WHERE ID ='".$row[iD]."' AND YEAR(SynDate)=".$stryear." ORDER BY wetcnt DESC LIMIT 1 ";

$wetqres = mysql_query($wet);

$wetinfo = mysql_fetch_array($wetqres);

.

.

.

$disp .= "<tr><td>".$row[iD]."</td><td>".$row[sta_Name]."</td><td style=\"text-align:right;\">".$row[Lat]."</td><td style=\"text-align:right;\">".$row[Lon]."</td><td style=\"text-align:right;\">".$suhuMaksinfo[max]."</td><td style=\"text-align:right;\">".$suhuMininfo[min]."</td><td style=\"text-align:right;\">".$hujanMaksinfo[maxR]."</td><td style=\"text-align:right;\">".$Hhujaninfo[Dr]."</td><td style=\"text-align:right;\">".$Hhujan5info[Dr5]."</td><td style=\"text-align:right;\">".$Hhujan20info[Dr20]."</td><td style=\"text-align:right;\">".$Hhujan50info[Dr50]."</td><td style=\"text-align:right;\">".$Hhujan100info[Dr100]."</td><td style=\"text-align:right;\">".$wetinfo[wetcnt]."</td><td style=\"text-align:right;\">".$dryinfo[drycnt]."</td><td style=\"text-align:right;\">".$Rdensityinfo[sumR]."</td><td style=\"text-align:right;\">".$suhuMaksAbsinfo[maxAbs]."</td><td style=\"text-align:right;\">".$suhuMinAbsinfo[minAbs]."</td><td style=\"text-align:right;\">".$hujanMaksAbsinfo[maxRAbs]."</td></tr>";

 

 

it is looking for a "wetspell" from each YEARS (as input form)  and from each station to display

wetspell is the maximum consecutive rainday that th rain is greater than 1 milimeter

but i got the first row always 0 for each years ,,, but the nesxt rows result is correct till the end rows

 

and the other strange the dryspell is working properly from the first row

dryspell is the maximum consecutive NO RainDay

 

$dry = "SELECT @count := 0";

$dry = "SELECT @count := IF(TotRain <= 0 OR TotRain is NULL, @Count + 1, 0) as drycnt FROM daily_syn WHERE ID ='".$row[iD]."' AND YEAR(SynDate)=".$stryear." ORDER BY drycnt DESC LIMIT 1 ";

$dryqres = mysql_query($dry);

$dryinfo = mysql_fetch_array($dryqres);

 

 

sorry cannot show you the result now, bcouse canot reach the server

 

i hope you got what i mean and can help me with my problem

 

 

many thanks, wish somone give me a help ,,, thanks

[trq]

You forgot to tell us what the problem is.

[silkfire]

I suspect your issue is with SQL variables. mysql_query does not allow more than one query (separated with ';'). So run mysql_query two times:

 

           mysql_query('SELECT @count := 0');
$wetqres = mysql_query("SELECT @count := IF(TotRain > 0, @count + 1, 0) AS wetcnt
                        FROM daily_syn
                        WHERE ID = '$row[iD]'
                        AND YEAR(SynDate) = '$stryear'
                        ORDER BY wetcnt DESC
                        LIMIT 1");

[xyph]

.. or define the variable as a second table. I'm not sure if this is actually more or less efficient, but it does it in a single query.

 

mysql> SELECT @c := @c + 1 as count, date
    -> FROM table, (SELECT @c := 0) as c;
+-------+---------------------+
| count | date                |
+-------+---------------------+
|     1 | 2012-04-27 00:00:00 |
|     2 | 2012-04-27 00:00:00 |
|     3 | 2012-04-27 15:51:29 |
|     4 | 2012-05-23 00:00:00 |
|     5 | 0000-00-00 00:00:00 |
|     6 | 2012-04-27 16:06:36 |
|     7 | 2012-04-27 16:16:51 |
|     8 | 2012-04-27 16:45:27 |
|     9 | 2012-04-27 16:58:04 |
+-------+---------------------+
9 rows in set (0.00 sec)

[abrew]

wow ,,, thanx a lot  SILKFIRE & XYPH ,,, it's been a week with headache !!!

i don't know that mysql_query cannot run more than 1 ,,, thx MASTER !!!

it's all DONE !!!

 

THANK YOU SO MUCH ,,,