[SOLVED] Whats wrong with this query?

[gangsterwanster1]

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

 

How come the query works when i enter it in phpmyadmin, to delete duplicates yet when i add it in the php code nothing happens?

[haku]

Are you sure $connect holds a valid connection?

[gangsterwanster1]

Are you sure $connect holds a valid connection?

$connect = mysql_connect("127.0.0.1", "root", "")

mysql_select_db("MainDB", $connect)

 

Its correct, but for some reason it doesn't work lol.

[gangsterwanster1]

I was thinking it was where i put it in my script but thats not the case. i made a blank php file and it wasnt able to delete the duplicates...

 

<?php
$connect = mysql_connect("127.0.0.1", "root", "") 
	 	 mysql_select_db("MainDB", $connect)
	 	 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);
  		 mysql_close($connect);
?>

[gangsterwanster1]

any idea?

[trq]

You can't use a JOIN within a DELETE statement.

[trq]

Oops, apparently you can. http://dev.mysql.com/doc/refman/5.0/en/delete.html

[Zane]

you don't have any semicolons

$connect = mysql_connect("127.0.0.1", "root", "")   // ;            mysql_select_db("MainDB", $connect)   // ; 

also...you need to assign mysql_query to a variable

like so

$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);
mysql_close($connect);

[gangsterwanster1]

you don't have any semicolons

$connect = mysql_connect("127.0.0.1", "root", "")   // ; <-- here
           mysql_select_db("MainDB", $connect)   // ; <-- and here

also...you need to assign mysql_query to a variable

like so

$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);
mysql_close($connect);

Ok i did the changes and still nothing happens lol..

[Zane]

by nothing you mean....?....

 

- it does literally nothing at all

- the program doesn't execute without an error

- NOTHING is deleted from the database.

- NOTHING is displayed on the screen

- sounds don't chime and jingle along with fireworks when you run the code.

-

 

so...what DOES happen...because the code seems to be fine...especially since the query works in phpmyadmin (if that's true)

[gangsterwanster1]

by nothing you mean....?....

 

- it does literally nothing at all:

- the program doesn't execute without an error

- NOTHING is deleted from the database.

- NOTHING is displayed on the screen

- sounds don't chime and jingle along with fireworks when you run the code.

-

 

so...what DOES happen...because the code seems to be fine...especially since the query works in phpmyadmin (if that's true)

 

- it does literally nothing at all: blank page

- the program doesn't execute without an error: no error

- NOTHING is deleted from the database.: nothing is deleted

- NOTHING is displayed on the screen: yep

- sounds don't chime and jingle along with fireworks when you run the code.: lol

[gangsterwanster1]

Been waiting all day for a reason?

[haku]

$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());

 

This will at least tell you what the problem is. Easier to fix then.

[gangsterwanster1]

$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());

 

This will at least tell you what the problem is. Easier to fix then.

 

Error:

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

-Using the latest version of XAMPP so i can test it locally before i put it on the net.

 

Full code

<?php
$connect = mysql_connect("127.0.0.1", "root", "") or die('Cant connect');
 mysql_select_db("MainDB", $connect) or die('Cant 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());
?>

 

 

[corbin]

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)

 

 

 

You have a trailing ).

[gangsterwanster1]

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)

 

 

 

You have a trailing ).

My bad i missed that, but now i get this;

 

Parse error: parse error in C:\xampp\htdocs\Project\test.php on line 7

Line 7 is bad_rows.id > good_rows.id)", $connect) or die(mysql_error();

[haku]

Then you made a mistake somewhere (deleted the wrong bracket maybe?) because removing the bracket from inside the quotes wouldn't have caused that error.

 

Lets see your current code.

[gangsterwanster1]

Then you made a mistake somewhere (deleted the wrong bracket maybe?) because removing the bracket from inside the quotes wouldn't have caused that error.

 

Lets see your current code.

<?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());
?>

[corbin]

bad_rows.id > good_rows.id)", $connect) or die(mysql_error()

 

You removed the wrong ) entirely...

 

 

Try

 

 

bad_rows.id > good_rows.id", $connect) or die(mysql_error())

[xtopolis]

I think you're missing a ( to surround the ON condition.

$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());

(Line4) , I modified your statement so it could be more easily read.

[gangsterwanster1]

bad_rows.id > good_rows.id)", $connect) or die(mysql_error()

 

You removed the wrong ) entirely...

 

 

Try

 

 

bad_rows.id > good_rows.id", $connect) or die(mysql_error())

I get this error now;

Parse error: parse error in C:\xampp\htdocs\Project\test.php on line 8

I think you're missing a ( to surround the ON condition.

$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());

(Line4) , I modified your statement so it could be more easily read.

With your code i get this;

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 ''good_rows' INNER JOIN account AS 'bad_rows' ' at line 2

 

This is so annoying, it works in phpadmin yet i am having all these troubles using it in php.

 

[corbin]

I didn't see the ON clause, so you actually needed the parentheses I removed.

 

 

What's your current code?

[xtopolis]

edit - im confusing myself, post current code.

[gangsterwanster1]

I didn't see the ON clause, so you actually needed the parentheses I removed.

 

 

What's your current code?

Well then i am back to this.

<?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());
?>

Do you have a database with duplicates where you can try the code out?

 

 

 

edit - im confusing myself, post current code.

Same code as before, all i changed was

$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());

But then once i got the error i changed it back to what it was earlier.

[corbin]

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());
?>