Mysql increment a variable

[standard]

I have only just registered, but from what i remember you seem like a cluey bunch.

Any way here is my problem i am trying to increment a variable inside a sql statement and was wondering if this is possible

and if so how.

This is what i have

$query = "select * from scores where league like \"%$trimmed%\" and week='$weekno'  
  order by week";

what i am trying to do is increment the $weekno by 1 while it is inside a WHILE loop

 

Any help or leads will be appreciated

[corbin]

I'm not sure if it applies to this situation or not as I'm not entirely sure what you're trying to do, but you can always do something like this:

 

 

UPDATE table SET field = field+1 WHERE someField = 'someValue';

[Mchl]

Or maybe you mean it like this?

 

$query = "select * from scores where league like \"%$trimmed%\" and week='".$weekno++."'  
  order by week";

[standard]

cheers buddy that is exactly what i want.

But it still doesn't work the way i want

here's the code

 

 

 

  // Get the search variable from URL
  
  $var = $_POST['Search_options'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }
$weekno='1';
include '../scripts/dbcon.php';
// Build SQL Query  

$query ="select * from scores where league like \"%$trimmed%\" and week='".$weekno++."'order by week"; 

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);


// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>" .$query;

  }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");

// display what the person searched for
echo "<p>Results: " . $var . "</p>";

// begin to show results set
echo "Results<br />";
$count = 1 + $s ;
echo "<table border='1' cellspacing='10px'><tr><th>week</th><th>Team/Name</th>th>league</th><th>score</th><th>Pinfall</th><th>Total score </th></tr>";
// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $nameteam= $row["nameteam"];
$league = $row['league'];
$week = $row['week'];
$score = $row['score'];
$pinfall = $row['pinfall'];
$totscore=$score+$totscore;
  echo "<tr><td>week: $week</td><td> Name: $nameteam </td><td>league: $league </td><td>score: $score </td><td>Pinfall: $pinfall</td><td>Total score </td></tr>" ;
  echo $query;
  }
echo "<tr><th>week</th><th>Team/Name</th>th>league</th><th>score</th><th>Pinfall</th><th>Total score=$totscore </th></tr>";
echo "</table>";
$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&nbsp ";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";}

?>

 

 

what i am trying to achieve is to select the info from the DB in order by week.

example:week 1 Blah Blah Blah Blah Blah Blah

                      Blah Blah Blah Blah Blah Blah

            week 2 Blah Blah Blah Blah Blah Blah

                      Blah Blah Blah Blah Blah Blah

At the moment i get: week 1 Blah Blah Blah Blah Blah Blah

                                      Blah Blah Blah Blah Blah Blah

 

and so on. I remember reading some where that you cant interrupt a while statement or similar is this the case.

I know i am not great at explaining.

 

[Mchl]

Yes... you can using break

 

You can also fetch just one row from database using LIMIT 1 in your query.

[standard]

Thanks for replying so quick. I still cant get it to work the way i intend it to

I will ask my teacher tomorrow and see what he thinks.

[fenway]

What is the "way you want it to work", exactly?

[standard]

hey sorry for the delay. I am a bit lazy.

But this is what i was trying to achieve using php and mysql

 

 

and this what it looks like now

 

 

The reason for keeping things in order is easy up dates of scores and force integrity.

 

the DB table i used has the name "scores" and the fields "nameteam, league, week, score, uni (unique)"

 

here the code that makes it work.

 

<?php
include '../scripts/dbcon.php';
$rows='1';
$group = $_REQUEST['id'];    //collect the ID from the web address
if ($group == '')
{$group = 'Wednesday 4s Scratch Comp';}

echo $group.' score board';
echo "<br/><br/><br/>Positions";
echo "<ol>";
$sql_pos  = "SELECT `nameteam` FROM `scores` WHERE `league` = '$group' GROUP by `nameteam` ORDER by sum(`score`) DESC ";

$result = mysql_query($sql_pos);

while ($teams = mysql_fetch_array($result,MYSQL_ASSOC))
{

echo '<li>'.$teams["nameteam"].'</li>';

}


echo "</ol>";

$sql_teams = "SELECT DISTINCT nameteam FROM scores WHERE `league` = '$group' order by nameteam";
$result = mysql_query($sql_teams);
echo "<table border='1' cellpadding='2'><tr><td>Week</td>";

while ($teams = mysql_fetch_array($result,MYSQL_ASSOC))
{

echo '<td>'.$teams["nameteam"].'</td>';
$rows++;
}
echo'</tr>';

$sql_weeks = "SELECT distinct week FROM scores WHERE `league` = '$group' order by week";
$result = mysql_query($sql_weeks);
$totweeks='0';
while ($weeks = mysql_fetch_array($result,MYSQL_ASSOC))
{
echo '<tr><td>'.$weeks["week"].'</td>';
$totweeks++;
$sql_scores = "SELECT distinct score FROM scores where week=$totweeks AND `league` = '$group' order by nameteam";
$scoreresult = mysql_query($sql_scores);
while ($scores = mysql_fetch_array($scoreresult))
{
  if($scores["score"]=="0")
     {$scores["score"]="bye";}
    
echo '<td>'.$scores["score"].'</td>';}
}

$sql_tscore = "SELECT sum( `score` )
FROM `scores`
WHERE `league` = '$group'
GROUP BY `nameteam`
ORDER BY `nameteam`";


$tscoreresult = mysql_query($sql_tscore);
echo '</tr><td>Total Score</td>';

$c = 0;
while ($tscores = mysql_fetch_array($tscoreresult,MYSQL_ASSOC))
{

foreach ($tscores as $q){ 
$whatthe[$c] = $q;
echo '<td>'.$whatthe[$c].'</td>';
$c++;




}

}
echo "</tr>";

$sql_tscore = "SELECT sum( `score` )
FROM `scores`
WHERE `league` = '$group'
GROUP BY `nameteam`
ORDER BY `nameteam`";

$tscoreresult = mysql_query($sql_tscore);
echo '</tr><td>Avg score</td>';

$c = 0;
while ($tscores = mysql_fetch_array($tscoreresult,MYSQL_ASSOC))
{

foreach ($tscores as $q){ 
$whatthe[$c] = $q;
echo '<td>'.$whatthe[$c]/$totweeks.'</td>';
$c++;




}

}

echo "</tr>";
echo'</table>';

 

The reason for $_REQUEST['id'] is the script handles multiple leagues in the same table.

Thanks for helping

I hope this makes sense. If it doesnt or you have a similar problem PM me

[JustLikeIcarus]

You have to get a little creative for this.  Oracle offers it as "rownum" but sadly mysql does not yet have it but can be accomplished like

 

select @rownum:=@rownum+1 AS week, col, col, col
from your_table, (SELECT @rownum:=0) r

 

This would output

 

0 val val val

1 val val val

2 val val val

etc......