php - 不能让 mySQL 做我想做的事。希望它在给定 ID 后返回数据新数据

Question

我只是想传入一个 ID 号(主键)并取回自该 ID 以来添加的所有条目。每个条目的 ID 都会递增，因此这应该是为客户端获取新条目的安全方法。

但是我收到一个错误字符串返回给我的客户

18Error retrieving scores 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 'id>18 ORDER BY id ASC LIMIT 0,100' at line 1

我一直在努力改变它的尝试方式和改变查询，所以它现在可能真的一团糟。但无论如何我的代码如下:

$table = "highscores";

// Initialization
$conn = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);
mysql_select_db(DB_NAME, $conn);

// Error checking
if(!$conn) {
    die('Could not connect ' . mysql_error());
}

$type   = isset($_GET['type']) ? $_GET['type'] : "global";
$offset = isset($_GET['offset']) ? $_GET['offset'] : "0";
$count  = isset($_GET['count']) ? $_GET['count'] : "100";
$sort   = isset($_GET['sort']) ? $_GET['sort'] : "id ASC";

// Localize the GET variables
$udid  = isset($_GET['udid']) ? $_GET['udid'] : "";
$name  = isset($_GET['name']) ? $_GET['name']  : "";
$clubname  = isset($_GET['clubname']) ? $_GET['clubname']  : "";
$theid  = isset($_GET['theid']) ? $_GET['theid']  : ""; 


// Protect against sql injections
$type   = mysql_real_escape_string($type);
$offset = mysql_real_escape_string($offset);
$count  = mysql_real_escape_string($count);
$sort   = mysql_real_escape_string($sort);
$udid   = mysql_real_escape_string($udid);
$name   = mysql_real_escape_string($name);
$clubname   = mysql_real_escape_string($clubname);
$theid   = mysql_real_escape_string($theid);

    echo $theid;

// Build the sql query
$sql = "SELECT * FROM $table WHERE ";
//$sql = "SELECT * FROM $table WHERE id>$theid ";

switch($type) {
    case "global":
        $sql .= "1 ";
        break;
    case "device":
        $sql .= "udid = '$udid' ";
        break;
    case "name":
        $sql .= "name = '$name' ";
        break;
    case "clubname":
        $sql .= "clubname = '$clubname' ";
        break;
}

$sql .= "id>$theid ";
$sql .= "ORDER BY $sort ";
$sql .= "LIMIT $offset,$count ";

$result = mysql_query($sql,$conn);

if(!$result) {
    die("Error retrieving scores " . mysql_error());
}
//echo $result;
$rows = array();
while($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
}

mysql_free_result($result);
mysql_close($conn);
echo json_encode($rows);

有人可以让我走上正确的轨道吗？

非常感谢，-代码

Answer 1

您的 where 子句中始终有 2 个条件，但两者之间缺少 OR 或 AND...对于通过代码的可能路径之一，这是提供给服务器的内容:

SELECT * FROM $table 
WHERE udid = '$udid' id>$theid ORDER BY $sort LIMIT $offset,$count

但它应该是这样的

SELECT * FROM $table 
WHERE udid = '$udid' AND id>$theid ORDER BY $sort LIMIT $offset,$count

但作为一般性评论，Oli 和 Arjan 的建议是极好的建议，一个简单的

echo $sql 

可以很有启发性。