[SOLVED] Whats wrong with this query?

[gangsterwanster1]

Missing a ).

 

 

This should work .

 

 

<?php
$connect = mysql_connect("127.0.0.1", "root", "") or die('Could not connect');
    mysql_select_db("MainDB", $connect) or die('Could not select database');
    $query = mysql_query("delete bad_rows.*
                    from account as good_rows
                       inner join account as bad_rows on bad_rows.username = good_rows.username and
                                         bad_rows.id > good_rows.id)", $connect) or die(mysql_error());
    mysql_close($connect) or die(mysql_error());
?>

AHHHH

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 4

 

[xtopolis]

That would leave him with an extra ) inside the query, at the very end.

Why can't you just make it a bit less confusing?

$connect = mysql_connect("127.0.0.1", "root", "") or die('Could not connect');
    mysql_select_db("MainDB", $connect) or die('Could not select database');

$sql = "DELETE bad_rows.*
       FROM account
        AS good_rows
       INNER JOIN account
        AS bad_rows
          ON (bad_rows.username = good_rows.username
            AND
              bad_rows.id > good_rows.id)";

$query = mysql_query($sql, $connect) or die(mysql_error());
mysql_close($connect) or die(mysql_error());

[corbin]

Oh my bad.... I thought the ( after the ON was still there.

 

 

I should have just left this thread alone haha.

[gangsterwanster1]

That would leave him with an extra ) inside the query, at the very end.

Why can't you just make it a bit less confusing?

$connect = mysql_connect("127.0.0.1", "root", "") or die('Could not connect');
    mysql_select_db("MainDB", $connect) or die('Could not select database');

$sql = "DELETE bad_rows.*
       FROM account
        AS good_rows
       INNER JOIN account
        AS bad_rows
          ON (bad_rows.username = good_rows.username
            AND
              bad_rows.id > good_rows.id)";

$query = mysql_query($sql, $connect) or die(mysql_error());
mysql_close($connect) or die(mysql_error());

Finally!! Thanks a LOT everyone, finally solved it.

 

~i have one minor question with the code you posted and then i am done.

If i wanted it to delete duplicates based on more then one row would i do this?

 

ON (bad_rows.username = good_rows.username
            AND
ON (bad_rows.password = good_rows.password
            AND
              bad_rows.id > good_rows.id)";

 

 

Because i tried that and it comes up with this erorr;

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 'ON (bad_rows.password = good_rows.password AND bad_r' at line 8

 

[xtopolis]

I believe you would just add to the AND conditions

...
ON (bad_rows.username = good_rows.username
            AND
              bad_rows.id > good_rows.id
            AND
              bad_rows.something = good_rows.something)

 

Is that what you mean, or am I misunderstanding?

[gangsterwanster1]

Thanks for all the help.