Deleting row using PHP Mysql

Question

Trying to delete from table.2 if no row count exist or < than one in table.1. using a variable ($group). I think the problem is after I delete the row in table.1 there’s no variable to go by to delete in table.2 so it doesn not delete the row. I don’t know if this makes sense. Here’s the code.

$q = mysql_query("SELECT `group name` FROM `group` WHERE cid = '$id'") or die(mysql_error());
list($group) = mysql_fetch_row($q);

$n = mysql_query("select count(`group name`) as total from `group` WHERE user_id
= '$_SESSION[user_id]' AND cid = '$id' ");

$r = mysql_fetch_array($n);

if($r['total'] = 0 ) {

mysql_query("DELETE FROM `group log`
             WHERE `group name`='$group'
            ") or die(mysql_error());
}

I’ve also tried this but it just deletes the row after user deletes all rows:

mysql_query("DELETE FROM `group log` 
 WHERE NOT EXISTS(SELECT NULL
                    FROM `group` g
                   WHERE g.`group name` = `group name`)");

Can you show the show create table yourtable output? You cannot have spaces in MySQL column names... — marekful
– marekful, Commented Jun 5, 2014 at 16:15
@Marcell Fülöp CREATE TABLE IF NOT EXISTS group ( id int(255) NOT NULL AUTO_INCREMENT, group name varchar(255) NOT NULL, created date NOT NULL DEFAULT '0000-00-00', photo varchar(255) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS group log ( id int(255) NOT NULL AUTO_INCREMENT, group name varchar(255) CHARACTER SET utf8 NOT NULL, created date NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; — Darrion
– Darrion, Commented Jun 5, 2014 at 16:21

JasonMArcher · Accepted Answer · 2014-06-05 18:16:05Z

1

If you're just looking to delete records of one table that don't have a corresponding match in a second table, the DELETE method in MySQL supports LEFT JOIN syntax:

DELETE gl.* 
FROM `group log` gl
LEFT JOIN `group` g
ON g.`group name` = gl.`group name`
WHERE g.`group name` IS NULL

Or, if you have a particular group you're looking to delete from the log table:

DELETE gl.* 
FROM `group log` gl
LEFT JOIN `group` g
ON g.`group name` = gl.`group name`
WHERE g.`group name` IS NULL
AND gl.`group name` = '$group'

edited Jun 5, 2014 at 18:16

JasonMArcher

15.1k22 gold badges59 silver badges53 bronze badges

answered Jun 5, 2014 at 17:54

sosada

1143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

exussum · Accepted Answer · 2014-06-05 16:17:33Z

1

`WHERE g.`group name` = `group name`)

Thats saying

where `group name` = `group name`

Which is always true, I suspect you meant to say

where `group name` = 'group name'

or

where `group name` = $group_name

in the first case you missed a = it should be

if($r['total'] == 0 ) {

answered Jun 5, 2014 at 16:17

exussum

18.6k8 gold badges35 silver badges67 bronze badges

1 Comment

Darrion Over a year ago

But if there is no variable to go off of, it nulls the statement and there's nothing in variable $group_name. Would sessions be best, and how would I would I do it?

malte · Accepted Answer · 2014-06-05 16:38:02Z

0

In addition to the previous answer by exussum, I would exchange

$n = mysql_query("select count(`group name`) as total from `group` WHERE user_id='$_SESSION[user_id]' AND cid = '$id' ");

with

$n = mysql_query("select count(`group name`) as total from `group` WHERE user_id= '{$_SESSION['user_id']}' AND cid = '$id' ");

To avoid an undefined constant error notice, depending on the error settings used:

$_SESSION[user_id] is actually looking for the value of the constant user_id to use as the key in $_SESSION, but defaults to the string 'user_id' (albeit with a warning).

Also, have you checked that the session is started (session_start()) and that $_SESSION['user_id'] is set?

answered Jun 5, 2014 at 16:38

malte

1,4441 gold badge11 silver badges12 bronze badges

3 Comments

Darrion Over a year ago

session_start(); is already set at the top of the script. There's no errors. The issue is with variable $group if the count(group name) is == 0 (NULL). $group does not have any data string. i.e. where group name = $group

malte Over a year ago

And the row with cid = '$id' exists for $id I guess..?

Darrion Over a year ago

$cid is it's own variable. Nothing to do with $_SESSION[user_id]

Collectives™ on Stack Overflow

Deleting row using PHP Mysql

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related