By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,812 Members | 1,288 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,812 IT Pros & Developers. It's quick & easy.

SQLite3 and PHP

P: n/a
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!

Apr 23 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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]
Apr 23 '07 #2

P: n/a
Jon
On Apr 23, 6:45 pm, Oliver Grätz <oliver.gra...@gmx.dewrote:
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!

Apr 24 '07 #3

P: n/a
Jon
On Apr 23, 6:45 pm, Oliver Grätz <oliver.gra...@gmx.dewrote:
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.

Apr 24 '07 #4

P: n/a
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]
Apr 29 '07 #5

P: n/a
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.
Apr 30 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.