[SOLVED] Mysql delete query

[jamesxg1]

what sort of mysql query do i use to delete a row i need it to delete 3 inputs (comto, comfrom, commen) and i need it to delete from $username but i dont know what query to use can someone help :S ?,

 

 

 

 

this is a dump from mysql,

 

DELETE FROM `comments` WHERE CONVERT(`comments`.`comto` USING utf8) = '$comto' AND CONVERT(`comments`.`comfrom` USING utf8) = '$comfrom AND CONVERT(`comments`.`commen` USING utf8) = '$commen' LIMIT 1;

 

 

but i need it to only delete the row if the $username is equal to the $comto, how would i do this ?

 

 

[Snart]

if($username == $comto)

{

  //Execute query

}

 

If you mean to compare two database fields:

 

DELETE FROM `comments` WHERE CONVERT(`comments`.`comto` USING utf8) = '$comto' AND CONVERT(`comments`.`comfrom` USING utf8) = '$comfrom AND CONVERT(`comments`.`commen` USING utf8) = '$commen'

AND CONVERT(`comments`.`commen` USING utf8) = CONVERT(`username`.`user` USING utf8)

LIMIT 1;

[jamesxg1]

if($username == $comto)

{

  //Execute query

}

 

If you mean to compare two database fields:

 

DELETE FROM `comments` WHERE CONVERT(`comments`.`comto` USING utf8) = '$comto' AND CONVERT(`comments`.`comfrom` USING utf8) = '$comfrom AND CONVERT(`comments`.`commen` USING utf8) = '$commen'

AND CONVERT(`comments`.`commen` USING utf8) = CONVERT(`username`.`user` USING utf8)

LIMIT 1;

 

 

 

 

hiya, so the end product should be

 

if($username == $comto)
{
<a href="DeleteComment.php">Delete Comment</a>
}

 

and then on the DeleteComment.php page it should be

 

DELETE FROM `comments` WHERE CONVERT(`comments`.`comto` USING utf8) = '$comto' AND CONVERT(`comments`.`comfrom` USING utf8) = '$comfrom AND CONVERT(`comments`.`commen` USING utf8) = '$commen'

AND CONVERT(`comments`.`commen` USING utf8) = CONVERT(`username`.`user` USING utf8)

LIMIT 1;

 

 

 

?

[Snart]

I would assume so.  It's not quite clear what you wish to compare with what else.

[jamesxg1]

I would assume so.  It's not quite clear what you wish to compare with what else.

 

 

ok i will try that now, and basically all i need it to do is check if the $username and $comto are the same (basically $comto is the id of who the comment is for) so that the user can delete only there comments and one at a time.

[Snart]

Ok, that clears a few things up.

 

Is 'username' a column in the 'comments' table?  Because now that your previous post makes a few things more clear, you're likely looking for something else than what I gave you.  Can you give us the structure of your table (which columns are in there and what kind of data they contain)?

[jamesxg1]

Ok, that clears a few things up.

 

Is 'username' a column in the 'comments' table?  Because now that your previous post makes a few things more clear, you're likely looking for something else than what I gave you.  Can you give us the structure of your table (which columns are in there and what kind of data they contain)?

 

no its a colum in the table called users but in the script i am using it converts $_SESSION['username']; to $username, and here's the sql dump:

 

-- phpMyAdmin SQL Dump
-- version 2.11.9.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 01, 2009 at 04:18 PM
-- Server version: 5.0.67
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `share`
--

-- --------------------------------------------------------

--
-- Table structure for table `comments`
--

CREATE TABLE IF NOT EXISTS `comments` (
  `comto` varchar(30) NOT NULL,
  `comfrom` varchar(30) NOT NULL,
  `commen` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `comments`
--

INSERT INTO `comments` (`comto`, `comfrom`, `commen`) VALUES
('test', 'test', 'hello'),
('jamesxg1', 'jamesxg1', 'hello'),
('jamesxg1', 'jamesxg1', 'hello\r\n');

-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(6) NOT NULL,
  `username` varchar(30) collate latin1_general_ci NOT NULL default '',
  `email` varchar(255) collate latin1_general_ci NOT NULL default '',
  `firstname` varchar(20) collate latin1_general_ci NOT NULL,
  `lastname` varchar(20) collate latin1_general_ci NOT NULL,
  `password` varchar(255) collate latin1_general_ci NOT NULL default '',
  `gender` varchar(20) collate latin1_general_ci NOT NULL default '',
  `dobMonth` varchar(20) collate latin1_general_ci NOT NULL default '',
  `dobDay` varchar(20) collate latin1_general_ci NOT NULL default '',
  `dobYear` varchar(20) collate latin1_general_ci NOT NULL default '',
  `languages` varchar(20) collate latin1_general_ci NOT NULL default '',
  `groupid` char(1) collate latin1_general_ci NOT NULL default 'P',
  `photo` varchar(255) collate latin1_general_ci NOT NULL,
  `pm_count` int(11) NOT NULL,
  `music` varchar(255) collate latin1_general_ci NOT NULL,
  `movies` varchar(255) collate latin1_general_ci NOT NULL,
  `scaredof` varchar(255) collate latin1_general_ci NOT NULL,
  `field1` varchar(255) collate latin1_general_ci NOT NULL,
  `field2` varchar(255) collate latin1_general_ci NOT NULL,
  `field3` varchar(255) collate latin1_general_ci NOT NULL,
  `field4` varchar(255) collate latin1_general_ci NOT NULL,
  `field5` varchar(255) collate latin1_general_ci NOT NULL,
  `fieldb1` varchar(255) collate latin1_general_ci NOT NULL,
  `fieldb2` varchar(255) collate latin1_general_ci NOT NULL,
  `thumbim` varchar(255) collate latin1_general_ci NOT NULL,
  `tagline` varchar(120) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`username`),
  UNIQUE KEY `id` (`id`,`username`,`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

[jamesxg1]

all i need it to do is delete a row e.g

 

IF

 

comto = $username a link will be displayed to delete that specific comment

 

and once click it will delete this

 

comto, comfrom, commen for one row that is selected by the user and it will only delete upon comto being equal to $username

[Snart]

Ok, in that case:

 

DELETE FROM `comments` WHERE CONVERT(`comments`.`comto` USING utf8) = '$comto' AND CONVERT(`comments`.`comfrom` USING utf8) = '$comfrom AND CONVERT(`comments`.`commen` USING utf8) = '$commen' AND CONVERT(`comments`.`comto` USING utf8) = '$username'

LIMIT 1;

[jamesxg1]

Ok, in that case:

 

DELETE FROM `comments` WHERE CONVERT(`comments`.`comto` USING utf8) = '$comto' AND CONVERT(`comments`.`comfrom` USING utf8) = '$comfrom AND CONVERT(`comments`.`commen` USING utf8) = '$commen' AND CONVERT(`comments`.`comto` USING utf8) = '$username'

LIMIT 1;

 

ok thanks, i cant test it at the min because this dont work for some reason :S

 

<?php
if($username == $comto)
{
echo("<a href='../main/DeleteComment.php'>Delete Comment</a>");
}
?>