PHP和MySQL，查询工作在一个不是另一个[关闭]

I have a query that works in phpMyAdmin but not when being run through php

This is the query:

DELETE FROM 'table' WHERE 'id' NOT IN ( SELECT DISTINCT id FROM ( SELECT * FROM 'table' ORDER BY scoreDESC LIMIT 10 ) foo)

Basically, it sorts the table to score descending and then keeps the top 10 and deletes the rest. I can run this fine trough phpmyadmin but my php code says no

This is the php script:

function add_highscore()
{

    mysql_query("DELETE FROM highscores WHERE id NOT IN ( SELECT DISTINCT id FROM ( SELECT * FROM highscores ORDER BY score DESC LIMIT 10 ) foo)")
    or die('0');

    echo "1";

    mysql_close($table_id);
}

There is no problems with the connection, I have more functions in the script that works.

Any ideas? Help is greatly appreciated!

Cheers, Jon

Add this to the top of your script:

error_reporting(E_ALL);

And replace:

die('0');

with:

die(mysql_error());

and that should help indicate just what variety of 'not working' the script is.

May be function is not being called or connection not established.You should try mysql_error for catching error or use try catch to catch error

Try this:

<?PHP
function add_highscore()
{
    $link = mysqli_connect($host, $user, $password, $database);
    $query = "DELETE FROM highscores WHERE id NOT IN ( SELECT DISTINCT (id) FROM ( SELECT * FROM highscores ORDER BY score DESC LIMIT 10 ) foo)";
    mysqli_query($link, $query)    or die('0');

    echo "1";

    mysqli_close($table_id);
}
?>

The MYSQL version

<?PHP
function add_highscore()
{

    $link = mysql_connect($server, $username, $password);
    mysql_select_db($database_name, $link);

    $query = "DELETE FROM highscores WHERE id NOT IN ( SELECT DISTINCT(id) FROM ( SELECT * FROM highscores ORDER BY score DESC LIMIT 10 ) foo)";
    return mysql_query($query)   or die('0');

    echo "1";

    mysql_close($link);
}

?>

I recommend you to use an ORM library, it's more easy to work with queries and it's more safety

For example: RedBean

or Search on this site