Delete MySQL row

Question

I am using PHP 7 and the latest mysql on ubuntu 16.10. This is the current code I have

<?php
        $dbhost = 'localhost:3036';
        $dbuser = 'root';
        $dbpass = 'password';
        $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    
        $sql = "DELETE FROM databasetable WHERE columnA LIKE '%Test7%'" ;
        mysql_select_db('jdbdev');
        $retval = mysql_query( $sql, $conn );
        
        if(! $retval ) {
           die('Could not delete data: ' . mysql_error());
        }
        
        echo "Deleted data successfully\n";
        
        mysql_close($conn);

?>

Your error is here. mysql_query takes two parameters. First one is your database connection, second is your query itself . as mysql_query($conn,$sql); — Rotimi, Mar 24 '17 at 14:47
@Brigitte I am going to post some code in answer. Try it and let me know if it works or not. — Dimi, Mar 24 '17 at 14:50
also, are you 100% sure that your port is 3036 and not 3306(default)? remove the :3036 part and leave your host as 'localhost' — Dimi, Mar 24 '17 at 14:51
I tried without the both default port and without as well still not working. — Brigitte, Mar 24 '17 at 14:55
@Brigitte add the or die after your connect. mysql_connect( 'host.name', 'user', 'password') or die('Could not connect to server.' ); Will it print 'could not connect to server'? — Dimi, Mar 24 '17 at 14:59
It connects and doest not print that, it gets to the retval and gives me could not delete data print out — Brigitte, Mar 24 '17 at 15:10

score 0 · Answer 1 · edited Dec 24 '20 at 08:55

0

Should change to below query

DELETE FROM databasetable WHERE columnA like '%Test7%'

There is difference between = and like operator

edited Dec 24 '20 at 08:55

marc_s

732,580
175
1,330
1,459

answered Mar 24 '17 at 14:46

Ravi

30,829
42
119
173

score 0 · Answer 2 · edited Dec 24 '20 at 08:56

0

Assuming you're trying to delete all the rows where columnA contains the test "Test7", you need to use the like operator, not the = operator:

DELETE FROM databasetable WHERE columnA LIKE '%Test7%'
-- Here --------------------------------^

edited Dec 24 '20 at 08:56

marc_s

732,580
175
1,330
1,459

answered Mar 24 '17 at 14:46

Mureinik

297,002
52
306
350

score 0 · Answer 3 · edited Dec 24 '20 at 08:55

0

If you want to use wildcard % then you have to use LIKE instead of =, so change your sql to:

"DELETE FROM databasetable WHERE columnA LIKE '%Test7%'"

Also, mysql functions are vulnerable to sql injection, you should use mysqli or even better PDO to manage your database.

edited Dec 24 '20 at 08:55

marc_s

732,580
175
1,330
1,459

answered Mar 24 '17 at 14:47

Željko Krnjić

2,356
2
17
24

Try setting your your host variable to localhost only: $dbhost = "localhost" – Željko Krnjić Mar 24 '17 at 15:00

score 0 · Answer 4 · edited Dec 24 '20 at 08:56

0

$sql = "DELETE FROM databasetable WHERE columnA = '%Test7%'" ;

should be columnA like '%test7%'

$sql = "DELETE FROM databasetable WHERE columnA like '%test7%'

Like and = both are different.

edited Dec 24 '20 at 08:56

marc_s

732,580
175
1,330
1,459

answered Mar 24 '17 at 14:47

Shashank Shah

2,077
4
22
46

score 0 · Answer 5 · edited Dec 24 '20 at 08:56

0

Try:

<?php
        $dbhost = 'localhost';
        $dbuser = 'root';
        $dbpass = 'password';
        $db_name = 'jdbdev';
        $conn = mysqli($dbhost, $dbuser, $dbpass, $db_name);

        try{
         if($conn->connect_error){
          echo 'Could not establish connection to database because '.$conn->connect_error;
            }
        }catch(Exception $ex){
          echo $ex->getMessage();
          exit;
          }
           
         $sql =  "DELETE FROM databasetable WHERE databasetable.columnA LIKE '%Test7%'";
        $retval = $conn->query($sql);

        if($retval === FALSE) {
           die('Could not delete data: ' . mysql_error());
        }

        echo "Deleted data successfully\n";

        mysql_close($conn);

?>

Sometimes, you have to reference the database table in front of the column

edited Dec 24 '20 at 08:56

marc_s

732,580
175
1,330
1,459

answered Mar 24 '17 at 14:48

Rotimi

4,783
4
18
27

then the problem must be from your database connection. – Rotimi Mar 24 '17 at 15:04
I put if ! $conn print cannot connect but it seems to connect, i get the could not delete data error on retval – Brigitte Mar 24 '17 at 15:08
Thank you but now im getting blank screen, i also tried to add i mysqli – Brigitte Mar 24 '17 at 15:42

score 0 · Answer 6 · edited Dec 24 '20 at 08:55

0

It's not very clear for me what you are trying to accomplish using this:

WHERE columnA = '%Test7%'" ;

Is the percent sign a literal that should match just like that? As you probably know, percentage sign is a wildcard in mysql and it should be use with LIKE rather than equal.

In this case, your condition will be:

WHERE columnA LIKE '%Test7%'`

edited Dec 24 '20 at 08:55

marc_s

732,580
175
1,330
1,459

answered Mar 24 '17 at 14:49

Alex

187
4

Delete MySQL row

6 Answers6