PHP and MySQL, Query works in one not the other

Question

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

What is the PHP error message ? There is no problem with your query — Hamza, Feb 08 '13 at 20:25
Identifiers (like table/field names) should be in backticks (or double quotes in ANSI mode), if you use them. Single-quoted words are *values*. I'm rather shocked your query worked in myadmin. — cHao, Feb 09 '13 at 21:03

score 0 · Answer 1 · answered Feb 08 '13 at 20:27

0

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

answered Feb 08 '13 at 20:27

Bhaskar Bhatt

1,265
12
19

Sory, the php-code isn't correct up in my post. It should say "mysql_query" not "mysqli_multi_query". The mysqli was one of my tries to get it working. – Jon Nyström Feb 08 '13 at 20:32

Mr. Radical · Answer 2 · 2013-02-08T21:00:10.690

0

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);
}

?>

edited Feb 08 '13 at 21:00

answered Feb 08 '13 at 20:30

Mr. Radical

1,833
1
17
27

Doesnt work, the mysqli_query was a typo in my OP. I use mysql_query. – Jon Nyström Feb 08 '13 at 20:39
1

stop using MYSQL it will be depreciated from version 5.5 onwards. – Mr. Radical Feb 08 '13 at 20:40
@JonNyström this query is not the same as the one in your PHP code. DELETE FROM 'table' WHERE 'id' NOT IN ( SELECT DISTINCT id FROM ( SELECT * FROM 'table' ORDER BY scoreDESC LIMIT 10 ) foo). Furhermore, you are using quote instead of backticks so I suspect this doesn't work in the command line. – Mr. Radical Feb 08 '13 at 20:49
I got it fixed. A permission problem. Thanks for your help! – Jon Nyström Feb 08 '13 at 20:57
@JonNyström great good luck with your project. – Mr. Radical Feb 08 '13 at 21:00

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

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

edited May 23 '17 at 12:12

Community

1
1

answered Feb 08 '13 at 20:38

ursuleacv

1,011
12
16

score 0 · Accepted Answer · answered Feb 08 '13 at 20:44

0

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.

answered Feb 08 '13 at 20:44

Sammitch

25,490
6
42
70

Thank you!!! Oh god i've been wtrying to fix this one for a long time. With error reporting (dont know how I could not priorities that) it became very clear that my mysql user didnt have permission to do delete. :) Thanks again! – Jon Nyström Feb 08 '13 at 20:54
1

@JonNyström is you question solved by sammitch answer? Please accept it then. – Mr. Radical Feb 08 '13 at 20:57

PHP and MySQL, Query works in one not the other

4 Answers4