MySql Error Help Please

[tacobell]

Hello, I'm new to the forum, but I need help with an error I have on my site. I'm building an online MMORPG similar to KoC but I can't solve this error. It reads:

 

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 '' at line 2

 

I just can't figure it out. I was up all night last night searching through my code to find the error, but was unable to find it. I have supplied the code below. Please help. Thanks!

 

<?php
if($_COOKIE[auth] != "yes") {                                 // ### USER LOGGED IN?
  header("Location: authno.php");
}else{
?>
  <?php include("inc/header.inc"); ?>
<?php
  if(time() % 1200 < 20 OR time() % 1200 > 1190) {
    $output = "A turn is taking place. Please try again in a minute";
    include("inc/output.inc");
  }else{
?>
    <?php include("inc/connect.php");
    $connection = mysql_connect($host, $user ,$password);
    $db = mysql_select_db($database, $connection);

    $userid = $_GET[id];

    $sql = "SELECT user FROM user
            WHERE user.id='$userid'";
    $result = mysql_query($sql);
    $user = mysql_fetch_assoc($result);
    if(mysql_num_rows($result) == 0) {
      echo '<center>';
      echo '<table width="300" style="border-collapse: collapse; border: 1px solid black" cellpadding="10" cellspacing="0">';
      echo "<tr>";
      echo '<td bgcolor="navy">';
      echo "<font color='yellow'>Invalid User ID: This user may have deleted his/her account." . '</font></td>';
      echo "</tr>";
      echo '</table></center>';
      echo '<br><br>';
    }else{

      $sql = "SELECT stats_gold FROM stats
              WHERE stats.id=$userid";
      $result = mysql_query($sql);
      $stats = mysql_fetch_assoc($result);
      mysql_free_result($result);

      $sql = "SELECT user FROM user
              WHERE user.id=$userid";
      $result = mysql_query($sql);
      $user = mysql_fetch_assoc($result);
      mysql_free_result($result);

      $time = time();
      $sql = "SELECT date FROM log_battle
              WHERE attacker = '$_COOKIE[id]' AND attacked = '$userid' AND date > $time - 86400";
      $result = mysql_query($sql) or die(mysql_error());
      $flurry = mysql_num_rows($result);
?>

<div align="center">
<h3>BATTLE</h3>
<br>
<br>
<br>
<?php
echo "<b>" . $user['user'] . "</b>";
?>

<br>

<br>
<br>
<?php
    echo "<i>Attacks on " . $user[user] . " in the last 24 hours: " . $flurry . " / 5</i>";
    echo "<br>";
    echo "<br>";
    echo "<br>";
    echo "<b>STEAL GOLD</b><br>";
    echo "<form method='post' action='do_battle.php'>";
    echo "<b>Power</b> (1 - 10): <input type='text' size='3' name='fld_power'>";
    echo "<input type='hidden' name='attackid' value='" . $userid . "'>";
?>

<input class="button" type="submit" value="Charge">
<?php
    echo "</form>";
    echo "<br>";
    echo "<br>";
    echo "<br>";
    echo "<br>";
    echo "<b>SEND SPIES</b><br>";
    echo "<form method='post' action='do_spy.php'>";
?>

<input class="button" type="submit" value="Spy">
</div>
<?php
      echo "<input type='hidden' name='attackid' value='" . $userid . "'></form>";
    }
  }
}
?>

<?php include("inc/footer.inc"); ?>

[gizmola]

print_r or echo out the $sql and see what it looks like.  I don't see wehre you define $userid or $time, and of the two I suspect that $time might not be defined, or might be in a format that is incompatible.  Also, you're just better off not messing around with moving date and time between php and mysql and just staying with mysql built in date handling functions.

 

I've written about this topic quite a bit on my blog:

 

http://www.gizmola.com/blog/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

http://www.gizmola.com/blog/archives/99-Finding-Next-Monday-using-MySQL-Dates.html

http://www.gizmola.com/blog/archives/93-Too-much-information-about-the-MySQL-TIMESTAMP.html

 

 

 

[tacobell]

---

Ah, I'm sorry I just realized I posted the wrong file. I'm not getting an error on that page. It's THIS one:

 

<?php include("inc/connect.php");
      include("inc/header.inc"); ?>

<?php 
if(time() % 1200 < 20 OR time() % 1200 > 1190) {
$output = "A turn is taking place. Please try again in a minute";
}else{
?>

<h3 align="center">BATTLE</h3>

<?php
echo "<div align='center'>";
echo "<table cellpadding='5' border='0'><tr><td>";
echo "Jump to Page</td><td>";
echo "<form action='rankings.php' method='get'>";
echo "<input class='text' type='text' name='page' size='5'>";
echo "<input class='button' type='submit' value='Go'>";
echo "</form></td></tr></table>";
echo "</div><br>";

$connection = mysql_connect($host, $user ,$password);
$db = mysql_select_db($database, $connection);
                                                                 // ### GETTING INFO - IDS OF RANKED PEOPLE

if(!isset($_GET

---

)) {
  $_GET

---

 = 1;
}
  $sql = "SELECT id FROM ranking WHERE rank > 0";
  $result = mysql_query($sql) or die(mysql_error());
  $num_rows = mysql_num_rows($result);
  $num_rows = ceil($num_rows / 50);

  if($_GET

---

 < 1) { $_GET

---

 = 1; }
  if($_GET

---

 > $num_rows) { $_GET

---

 = $num_rows; }
  settype($_GET

---

, "int");
  $arr_id = array();
  $arr_name = array();
  $arr_gold = array();
  $arr_sett = array();
  $arr_pop = array();
  $add_sql = "";
  $start_rank = 50 * ($_GET

---

 - 1);
  $start_rank = ($start_rank >= 0) ? $start_rank : 0;
  $sql = "SELECT id, rank FROM ranking
          WHERE rank > 0
          ORDER BY rank
          LIMIT 50 OFFSET $start_rank";
  $result = mysql_query($sql) or die(mysql_error());
$x = 1;
  while($ranking = mysql_fetch_assoc($result)) {
   $arr_id[$ranking[id]]= $ranking[rank];
    $add_sql = "id=" . $ranking[id];
    if($x > 1){
       $add_sql .= " OR id=".$ranking[id];
    }
    $x++;
  }
// $arr_min = min($arr_id);
  $arr_id = array_flip($arr_id);
  mysql_free_result($result);
  $add_sql = substr($add_sql, 4);
  $sql = "SELECT stats.id, stats_gold, stats_population, stats_clan FROM stats
          WHERE $add_sql";
  $result = mysql_query($sql) or die(mysql_error());
  while($stats = mysql_fetch_assoc($result)) {
    $arr_gold[$stats[id]] = $stats[stats_gold];
    $arr_pop[$stats[id]] = $stats[stats_population];
    $result2 = mysql_query($sql);
    $clan = mysql_fetch_assoc($result2);
    mysql_free_result($result2);

    if($clan[members] > 9) {
      $arr_clan[$stats[id]] = $clan[tag];
    }
  }
  $sql = "SELECT user, id FROM user
          WHERE $add_sql";
  $result = mysql_query($sql) or die(mysql_error());
  while($user = mysql_fetch_assoc($result)) {
    $arr_name[$user[id]] = $user[user];
  }

  echo "<table align='center' border='0' cellpadding='5' cellspacing='0'>\n<tr><td align='center' width='50'><b>Rank</b></td><td  align='center' width='150'><b>Username</b></td><td align='center' width='120'><b>Population</b></td><td><b>Gold</b></td></tr>";
  for($i = $arr_min; $i < $arr_min + 50; $i++) {
    $pos = $arr_id[$i];
    if(isset($arr_name[$pos])) {
      echo "<tr><td align='center'>" . number_format($i, 0, '.', ',') . "</td>";
      echo "<td align='center'><a href='stats.php?id=" . $pos . "'>" . $arr_name[$pos] . "</a></td>";
      echo "<td align='center'>" . number_format($arr_pop[$pos], 0, '.', ',') . "</td>";
      echo "<td align='center'>" . number_format($arr_gold[$pos], 0, '.', ',') . "</td></tr>\n";
    }
  }
  echo "</table>";
}


if(isset($output)) {
include('inc/output.inc');
}else{
$next_page = $_GET

---

 + 1;
$prev_page = $_GET

---

 - 1;
echo "<br><br>";
echo "<table align='center'>";
echo "<tr>";
echo "<td align='center' width='100'>";
if($_GET

---

 > 1) {
echo "<a href='rankings.php?page=" . $prev_page . "'>Previous Page</a>";
}
echo "</td>";
echo "<td align='center'>";
echo " Page " . $_GET

---

 . " of " . $num_rows . " ";
echo "</td>";

echo "<td align='center' width='100'>";
if($_GET

---

 != $num_rows) {
echo "   <a href='rankings.php?page=" . $next_page . "'>Next Page</a>";
}
echo "</td>";
echo "</tr>";
echo "</table>";

?>

<?php
echo "<div align='center'>";
echo "<table cellpadding='5' border='0'><tr><td>";
echo "Jump to Page</td><td>";
echo "<form action='rankings.php' method='get'>";
echo "<input class='text' type='text' name='page' size='5'>";
echo "<input class='button' type='submit' value='Go'>";
echo "</form></td></tr></table>";

echo "<br>Search for Player";
echo "<form action='rankings_search.php' method='get'>";
echo "<input class='text' type='text' name='user' size='20'> ";
echo "<input class='button' type='submit' value='Search'>";
echo "</form>";
echo "</div>";
}

?>





<?php include("inc/footer.inc"); ?>

 

And the time is to record how many times that your player has attack the other in a 24 hours period, because each player is only allowed to attack each other player 5 times per 24 hours.

[gizmola]

Again... you need to figure out which query it is that is failing, so it would be good to change your die() statements to die($sql . ' | ' . mysql_error());

 

In regards to your comment about time, I got the gist of that .. what I was saying, and still am, is that you can do date arithmetic in your queries directly, rather than using timestamps and subtracting seconds.  I show how to do all sorts of things using the built-ins and DATE_ADD() for comparison purposes.  Much cleaner, easier to understand, and takes advantage of mysql's features. 

[tacobell]

Ok thanks, I still don't know what's going on with it though. I fixed the first error and replaced my or die(mysql_error()); statements with  or die($sql . ' | ' . mysql_error()); statements and now it says:

 

SELECT id, stats_gold, stats_population, FROM stats WHERE | 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 'FROM stats WHERE' at line 1

 

This is line 65 - 71

  $sql = "SELECT id, stats_gold, stats_population, FROM stats
          WHERE $add_sql";
  $result = mysql_query($sql) or die($sql . ' | ' . mysql_error());
  while($stats = mysql_fetch_assoc($result)) {
    $arr_gold[$stats[id]] = $stats[stats_gold];
    $arr_pop[$stats[id]] = $stats[stats_population];
    }

 

 

[gizmola]

Well there's 2 obvious problems with the query...

 

First you can't have a comma before the FROM, and secondly you have an empty where clause, because your $add_sql variable is apparently empty when the query is executed.  Fix those and you should be happy.