Query Was Empty???

[refiking]

I am working on a tournament script and I am trying to store the score of the game. When I ran the mysql_query, it says the query is empty, but when I echo'd the results, they appeared.  I triple checked the field names and table names.  I don't get it.  Here is my code:

 

<?php

include 'header.php';
include 'cont.php';

$sql = mysql_query("SELECT * FROM scores");

while ($row = mysql_fetch_assoc($sql)) {

   $tid = $row['tid'];
   $hscore = $row['homescore'];
   $ascore = $row['awayscore'];
   $home = $row['home'];
   $away = $row['away'];
   $timestamp = $row['timestamp'];
   $round = $row['roundname'];
   $roundh = $round."home";
   $rounda = $round."away";
   IF ($round == "r1g1") {
   $nround = "r2g1";
   }
   IF ($round == "r1g2") {
   $nround = "r2g1";
   }
   IF ($round == "r1g3") {
   $nround = "r2g2";
   }
   IF ($round == "r1g4") {
   $nround = "r2g2";
   }
   IF ($round == "r1g5") {
   $nround = "r2g3";
   }
   IF ($round == "r1g6") {
   $nround = "r2g3";
   }
   IF ($round == "r1g7") {
   $nround = "r2g4";
   }
   IF ($round == "r1g8") {
   $nround = "r2g4";
   }
   IF ($round == "r1g9") {
   $nround = "r2g5";
   }
   IF ($round == "r1g10") {
   $nround = "r2g5";
   }
   IF ($round == "r1g11") {
   $nround = "r2g6";
   }
   IF ($round == "r1g12") {
   $nround = "r2g6";
   }
   IF ($round == "r1g13") {
   $nround = "r2g7";
   }
   IF ($round == "r1g14") {
   $nround = "r2g7";
   }
   IF ($round == "r1g15") {
   $nround = "r2g8";
   }
   IF ($round == "r1g16") {
   $nround = "r2g8";
   }
   $time = date("m/d/y: H:i:s",time());
   if ($hscore > $ascore) {
       $winner = $home;
   }
   if ($ascore > $hscore) {
       $winner = $away;
   }
  echo "Next Round: $nround<br>";
  echo "Away Round: $rounda<br>";
  echo "Away Score: $ascore<br>";
  echo "Away Team: $away<br>";
  echo "Home Round: $roundh<br>";
  echo "Home Score: $hscore<br>";
  echo "Home Team: $home<br>";
  echo "Winner: $winner<br>";
  echo "ID: $tid<br><br>";
   IF ($time > $timestamp){

   $update = mysql_query("UPDATE tourney SET '$nround' = '$winner' AND '$roundh' = '$hscore' AND '$rounda' = '$ascore' WHERE tid = '$tid'");
   if ($result =  mysql_query($update)) {
  if (mysql_num_rows($result)) {
    $fetch_em = mysql_fetch_array($result);
  }
} else {
  die(mysql_error() . $update);
}
}
}
?>
  

 

This is what the ran code looks like:

 

Next Round: r2g1

Away Round: r1g1away

Away Score: 34

Away Team: Thunerocity

Home Round: r1g1home

Home Score: 37

Home Team: HittFaktory

Winner: HittFaktory

ID: t1

 

Next Round: r2g4

Away Round: r1g7away

Away Score: 46

Away Team: bidness

Home Round: r1g7home

Home Score: 21

Home Team: nonya

Winner: bidness

ID: t1

 

Query was empty

[Tyche]

mysql_num_rows only returns the number of rows for queries whihc return a record set

UPDATE queries don't return a record set - You probably want to use

mysql_affected_rows instead.

[refiking]

Same thing  :-\ And it doesn't change the db table, either.

Next Round: r2g1

Away Round: r1g1away

Away Score: 34

Away Team: Thunerocity

Home Round: r1g1home

Home Score: 37

Home Team: HittFaktory

Winner: HittFaktory

ID: t1

 

Next Round: r2g4

Away Round: r1g7away

Away Score: 46

Away Team: bidness

Home Round: r1g7home

Home Score: 21

Home Team: nonya

Winner: bidness

ID: t1

 

Query was empty

[Tyche]

You are also calling mysql_query twice and also trying to fetch records from the UPDATE query

Try this

$update =mysql_query("UPDATE tourney SET '$nround' = '$winner' AND '$roundh' = '$hscore' AND '$rounda' = '$ascore' WHERE tid = '$tid'");
if (!$update) {
  die(mysql_error() . $update);
}

[refiking]

OK.  Now, it says the following:

 

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''r2g4' = 'bidness' AND 'r1g7home' = '21' AND 'r1g7away' = '46'

 

The only thing I can see is that r2g4 has a double and a single quote as opposed to two single quotes.  Is that what it's referring to?  And if so, how can I change that?

[Tyche]

You don't need the single quotes round the field names - just the values

[refiking]

Now, I don't see any error messages, but the records aren't updated, either.  Here's what it returns:

Next Round: r2g1

Away Round: r1g1away

Away Score: 34

Away Team: Thunerocity

Home Round: r1g1home

Home Score: 37

Home Team: HittFaktory

Winner: HittFaktory

ID: t1

 

Next Round: r2g4

Away Round: r1g7away

Away Score: 46

Away Team: bidness

Home Round: r1g7home

Home Score: 21

Home Team: nonya

Winner: bidness

ID: t1

 

Next Round: r2g8

Away Round: r1g15away

Away Score: 42

Away Team: win

Home Round: r1g15home

Home Score: 38

Home Team: gotta

Winner: win

ID: t1

[refiking]

OK.  Only the $nround field is being update on the record, but it doesn't actually return the $round value.  It just returns 0.  The other fields are still unaffected.

[refiking]

Got it.  I individualized the queries and it worked fine.  Here's how it looks now:

 

mysql_query("UPDATE tourney SET $round = '$winner' WHERE tid = '$tid'");
  mysql_query("UPDATE tourney SET $roundh = '$hscore' WHERE tid = '$tid'");
  mysql_query("UPDATE tourney SET $rounda = '$ascore' WHERE tid = '$tid'");