CODEWITHSUNDEEP
phpmysql
Shiven
Shiven

Reputation: 206

query does not delete data from the db

this is my query 

$db2=mysql_connect("localhost","root","");
    mysql_select_db("my_requests",$db2);

    $query=mysql_query("SELECT * FROM details INNER JOIN product ON details.user_id = product.user_id"); // find the city
    $row=mysql_fetch_array($query);// save record

        $id=$row['user_id'];
        echo "$id";


        $query1=mysql_query("DELETE * FROM details WHERE details.user_id=$id");
        $query2=mysql_query("DELETE * FROM product WHERE product.user_id=$id");

Here i have two tables products and detail ,user id is the primary key in details and made foreign key in product.

lets suppose i have two entries in the database with user_id =12 and another with user_id=11 1. when i take the innerjoin of two tables and try to display the user_id's it shows only one user_id 12 2. when i am trying to delete data using the user_id,it does not delete the data from the table.

sorry for the bad english

Upvotes: 2

Views: 454

Answers (2)

jbrtrnd
jbrtrnd

Reputation: 3833

In your SELECT query, you have to specify tha table to get the * like details.* or products.*, becasue you're joinning two tables. In your case, it's the same result.

You also need to place $row=mysql_fetch_array($query); in a loop, to fetch each rows, then, you have a bad DELETE syntax :

while($row=mysql_fetch_array($query)) {
    $id=$row['user_id'];
    echo "$id";

    $query1=mysql_query("DELETE FROM details WHERE details.user_id=$id");
    $query2=mysql_query("DELETE FROM product WHERE product.user_id=$id");


}

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270609

The proper syntax of a DELETE query does not include *

DELETE FROM product WHERE product.user_id=$id
//^^^^^^^^^

If you do some basic error checking and debugging, the API will report a syntax error.

// This query has a syntax error and will return FALSE...
$query1 = mysql_query("DELETE * FROM details WHERE details.user_id=$id");
if (!$query1) {
  // On failure, see what your error was.
  echo mysql_error();
}

The above will report an error similar to:

ERROR 1064 (42000): 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 '* FROM

Upvotes: 7

Related Questions