SQLite3 and PHP

[Jon]

Hi,

I'm having some trouble trying to get DELETE to work with SQLITE3 and
PHP and was wondering if anyone could be of some assistance?

I have verified that it is set up correctly and I'm able to insert /
create / select from tables but I'm unable to delete specific entries.

Given the following 2 tables:

Table A Table B
Name Cost Name Cost
A 1 D
3
B 2 E
5
C 3 F
7

how would I delete entries with the same cost?

my current code is:

<?php
function deleteRel($year) {

$db_file = "/../".$year.".sqlite3";
try{
$dbHandle = new PDO('sqlite:'.$_SERVER['DOCUMENT_ROOT'].$db_file);
}catch ( PDOException $exception ){
die($exception->getMessage());
}

$sqlDeleteVal = sprintf("DELETE table_1 FROM table_1, table_2 WHERE
table_1.cost = table_2.cost");

}
$year = 2010;
deleteRel($year);
?>

Please note that I do not wish to use UNIQUE since this is just a
simplification of what I am trying to achieve.

Thanks for your responses!

[=?ISO-8859-1?Q?Oliver_Gr=E4tz?=]

Jon schrieb:

$sqlDeleteVal = sprintf("DELETE table_1 FROM table_1, table_2 WHERE
table_1.cost = table_2.cost");

Wrong SQL syntax:
http://sqlite.org/lang_delete.html

OLLi

--
"You see, Mr. President: The worlds hates America. And for good reason.
(I wont bother going into details)"
[24 319]

[Jon]

On Apr 23, 6:45 pm, Oliver Grätz <[e-mail]:

Jon schrieb:

$sqlDeleteVal = sprintf("DELETE table_1 FROM table_1, table_2 WHERE
table_1.cost = table_2.cost");

Wrong SQL syntax:http://sqlite.org/lang_delete.html

OLLi

--
"You see, Mr. President: The worlds hates America. And for good reason.
(I wont bother going into details)"
[24 319]

Thanks OLLi, yes I updated but forgot to post: this is the current
fixed syntax.
$sqlDeleteVal = "DELETE * FROM table_1, table_2 WHERE
round(table_1.payment,2) = round(table_2.payment,2)*-1";

With switching DELETE with SELECT, it yields exactly the results I
want to delete. However, with the above fix to my code, no deletion
occurs. I'm very confused!

[Jon]

On Apr 23, 6:45 pm, Oliver Grätz <[e-mail]:

Jon schrieb:

$sqlDeleteVal = sprintf("DELETE table_1 FROM table_1, table_2 WHERE
table_1.cost = table_2.cost");

Wrong SQL syntax:http://sqlite.org/lang_delete.html

OLLi

--
"You see, Mr. President: The worlds hates America. And for good reason.
(I wont bother going into details)"
[24 319]

syntax is fixed.

"DELETE * FROM table_1, table_2 WHERE round(table_1.payment,2) =
round(table_2.payment,2)*-1";

Checked it by replacing DELETE with SELECT it and it shows the items I
wanted DELETED but DELETE is still not working. Doesn't do anything
but no warnings/errors.

[=?ISO-8859-1?Q?Oliver_Gr=E4tz?=]

Jon schrieb:

syntax is fixed.

No it isn't. DELETE takes no column argument since it only deletes
complete rows.

"DELETE * FROM table_1, table_2 WHERE round(table_1.payment,2) =
round(table_2.payment,2)*-1";

Try "DELETE FROM".
OLLi

--
Krach macht das Board nur beim Runterfallen - die Northbridge ist passiv
gekühlt.
[Mainboardtest auf hartware.de]

[Tim Roberts]

Jon <yu*****@gmail.comwrote:

>Hi,

I'm having some trouble trying to get DELETE to work with SQLITE3 and
PHP and was wondering if anyone could be of some assistance?

I have verified that it is set up correctly and I'm able to insert /
create / select from tables but I'm unable to delete specific entries.

Given the following 2 tables:

Table A Table B
Name Cost Name Cost
A 1 D
3
B 2 E
5
C 3 F
7

how would I delete entries with the same cost?

You want to delete from BOTH tables?

Deleting from one table is easy:

DELETE FROM tablea WHERE cost IN
(SELECT cost FROM tableb);

To delete from both tables, you'll have to copy the costs that are
duplicated into a temporary table, and then use that in a subselect. Or,
grab the SELECT results into a variable and construct the DELETE yourself.

SELECT tablea.cost FROM tablea,tableb WHERE tablea.cost=tableb.cost;
--
Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.