SQLITE PDO Delete

HI,

I have the following code :

[php]<?php

require_once("…/.common/.init.php");
$tabel = ‘AanbodSpul’;
$db = new PDO(DBASE);

print <<< START

bekijk de nieuwe spullen START;

$offset = ($_GET[‘page’] - 1) * $page_limit;

function Delete($wisbutton, $id, $i) {
extract($GLOBALS);

if($wisbutton[$i]) {
						
			if (isset($id)) {
				
				$sql = "DELETE FROM $tabel WHERE id = $id;";
				$result = $db->exec($sql);
				if($result == false) echo "Not deleted !";
				if (is_file("../fotoos/$id.jpg")) {unlink("../fotoos/$id.jpg");}
										
		   } 
		
}

}

//advertenties
$result = $db->query(“SELECT * FROM $tabel ORDER BY id DESC LIMIT $page_limit OFFSET $offset;”);
$i=-1;
echo “”;

echo “

”;
foreach($result as $row)
{ extract($row);

$i++;

//$tekst = stripslashes($tekst);

if (is_numeric($lidnr)){

$plaatje="../fotoos/".$id.".jpg";
$ext = "jpg";
if (!is_file($plaatje)) 
{$plaatje='../fotoos/blanco.gif'; $ext = "gif"; }

$src="resizefoto.php?plaatje=$plaatje&ext=$ext";

$info = "Spullen:$id <small>$datum</small>";

//ad
echo “

”;
if ($status == $statuscode) {
		echo"<input type='submit' id='wisbutton' name='wisbutton[$i]' value='Delete' title='wis advertentie'>";
		Delete($wisbutton, $id, $i);

           }
	echo"</td>"; 

}

}
echo"

";

$db = NULL;

print <<<EINDE

EINDE;

?> [/php]

If I try to delete an ad only the picture is deleted the text stays. Here you can try it: http://expopunt.nl//.kwikweb/paginas/indexbody.php?status=1234

Thank you for your time !

Delete is very simple, yet so very dangerous for one can delete something they don’t want to:

me thinks you have table spelled incorrectly and I would had called table something else than table?

[php] $sql = ‘DELETE FROM table WHERE id=:id LIMIT 1’;
$stmt = $pdo->prepare($sql);

   $result = $stmt->execute(array(':id' => $id));
   /* The following just shows a redirect to a different page - just used as an example */
   if ($result) {
			header("Location:index.php");
			exit;  		   
   }	   [/php]

I also want to make a suggestion using Global variables isn’t the best programming practice.

I’m getting an error : Call to a member function execute() on a non-object.

I did this:

[php]
$table = “AanbodSpul”;

$sql = “DELETE FROM $table WHERE id=:id LIMIT 1”;
$stmt = $db->prepare($sql);
print_r($db->errorInfo());
$result = $stmt->execute(array(’:id’ => $id));[/php]

print_r gives : Array ( [0] => HY000 [1] => 1 [2] => near “WHERE”: syntax error )

I discovered that : [php]$db->exec(“DELETE FROM $tabel WHERE id = ‘$id’;”);[/php] works fine if I place this before: [php]$result = $db->query(“SELECT * FROM $tabel ORDER BY id DESC LIMIT $page_limit OFFSET $offset;”);[/php] but if i place [php]$db->exec(“DELETE FROM $tabel WHERE id = ‘$id’;”);[/php] in a function it doesn’t work.

What could be the problem it doesn’t work inside functions ?

When it’s in a function, did you bring the $db into the function too? Otherwise $db won’t hold any value.

Yes I did : [php]extract($GLOBALS);[/php]

I have never seen anyone use the extract(), not sure where or who would have told you to use such a thing. It’s still not a “good” thing to use globals in a function but instead just do this kind of thing for the global vars you need to use inside the function.
[php]global $your_var;[/php]

It has noting to do with the [php]extract($GLOBALS).[/php]I always use it. I tried also: [php]function test() {
global $db;
echo “this is a test”;
$db->exec(“DELETE FROM $table WHERE id = 2250;”);
}[/php]

Nothing happens, besides the echo.

I tried it on another page, there the: [php]$db->exec(“DELETE FROM $table WHERE id = 2251;”);[/php] works fine inside a function. Do you know what could be the problem ?

Now it works on every page but the main problem is after this query : [php]$result = $db->query(“SELECT * FROM $table ORDER BY id DESC LIMIT $page_limit OFFSET $offset;”);[/php] I can’t do this: [php]$db->exec(“DELETE FROM $table WHERE id = $id;”);[/php] How can I fix this conflict ?

This
[php]$db->exec([/php]
Should probably be this
[php]$db->query([/php]
Why are you using all these weird function names. exec() is for running like command line stuff, not queries. I think half your problems are from using inappropriate functions for the task.

now I used [php]$db->query([/php] but i get the same problem: he is loading for a while and than nothing happens. But if I place this query before the other query it works fine.

Sorry but at this point you are confusing the crap out of us with all these weird function calls and queries all over the place inside and outside of functions that you aren’t even showing us. If you can post the entire page script along with any other needed scripts for custom functions you’ve made, then maybe we can see the flow of the whole thing and find the issue.

Here is my mainpage indexbody.php :

[php]<?php

require_once("…/.common/.init.php");

$style = ‘…/.common/default.css’;
$ip = $_SERVER[‘REMOTE_ADDR’];
//if ($ip != $status_ip1 and $ip != $status_ip2 and $ip != $status_ip3) {$status = ‘sorry’;}

$db = new PDO(DBASE);

$result = $db->query(“SELECT * FROM Teksten WHERE soort=‘adres’;”);
foreach($result as $row)
{ extract($row); $adres = $tekst;}

$telefoon = ‘Vragen? Bel ons! (dinsdag t/m vrijdag tussen 10 en 16 uur)’;
$nwsbrief = “…/nieuwsbrief/print.php”;
$nwsbriefbeheer = $nwsbrief."?status=1234";
$omschrijvingRW = <<< OMSCHR
Bij de Ruilwinkel helpen we elkaar met spullen en diensten.
De waardering gaat in punten, geld speelt geen rol.
U kunt punten verdienen door andere deelnemers te helpen, met spullen die u zelf over hebt, of door een klusje op te knappen.
Met de verdiende punten kunt u zelf spullen afnemen of hulp vragen bij dingen waar u zelf minder goed in bent.
Dankzij de punten is ruilen met gelijk oversteken niet nodig!
OMSCHR;

function setHeader() {
extract($GLOBALS);

if($_GET['amount'] != "")
$amount = "&amount=".$_GET['amount'];
else
$amount = "";
if($status == $statuscode) {

echo "<div id='header' style='width: 1010px; height: 85px; background-color: #4B259C;'>
<h2 style='position: absolute; margin-left: 30px; margin-top: 32px; color: green'>BEHEER</h2>
<a href = '../paginas/indexbody.php?status=1234$amount' target = '_top' ><img style='position: absolute; margin-left: 780px; margin-top: 15px; width: 180px; height: 50px;' src='../.common/ruilwinkel.gif' title='$omschrijvingRW' border='0'/></a>
<a href='$nwsbriefbeheer' target='_blank'><img style='position: absolute; margin-left: 570px; margin-top: 2px' src='../.common/nwsbrief.gif' title='laatste nieuwsbrief' border='0'/></a>
<div style= 'position: absolute; margin-left: 200px; margin-top: 8px; width: 500px; color: yellow; text-align: left;' title='Zie ook kaartje onderaan deze website'>$adres</div>
</div>";
}
else {
		
echo "<div id='header' style='width: 1010px; height: 85px; background-color: #4B259C;'>
<a href='indexbody.php?service=Bericht$amount' style='position: absolute; margin-left: 700px; margin-top: 12px; '><img src='../images/brief.gif' border='0' title='mail ons! Klik hier voor contact-formulier' /></a>
<img style='position: absolute; margin-left: 5px; margin-top: 3px; width: 40px; height: 40px;' src='../.common/logo.gif' title='$omschrijvingRW' border='0'>";
if($amount != "")
$amount = str_replace('&','', '?'.$amount);
echo"<a href = '../paginas/indexbody.php$amount' target = '_top' ><img style='position: absolute; margin-left: 45px; margin-top: 8px; width: 140px; height: 36px' src='../.common/ruilwinkel.gif' title='$omschrijvingRW' border='0'/></a>
<h6 style='position: absolute; margin-left: 45px; margin-top: 54px;' title = '$telefoon'>010-2151657</h6>
<a href='$nwsbrief' target='_blank'><img style='position: absolute; margin-left: 850px; margin-top: 10px; width: 135px; height: 60px;' src='../.common/nieuws.gif' title='laatste nieuws' border='0'/></a>
<div style= 'position: absolute; margin-left: 200px; margin-top: 8px; width: 500px; color: yellow; text-align: left;' title='Zie ook kaartje onderaan deze website'>$adres</div>
</div>";
}

}

function setServTitel() {
extract($GLOBALS);
if(!isset($_GET[‘service’]))
$_GET[‘service’] = “Spulaanbod”;

switch($_GET['service'])
{
case "Spulaanbod": echo $status == $statuscode ? "<a class='beheer' href = 'indexbody.php?service=Spulaanbod_Beheer&status=$statuscode'>+</a>" : ""; echo"<span class='title'>Spullen Aangeboden</span>";break;
case "Spulgevraagd": echo $status == $statuscode ? "<a class='beheer' href = 'indexbody.php?service=Spulgevraagd_Beheer'>+</a>" : ""; echo "<span class='title'>Spullen Gevraagd</span>";break;
case "Hulpaanbod": echo $status == $statuscode ? "<a class='beheer' href = 'indexbody.php'>+</a>" : ""; echo "<span class='title'>Hulp Aangeboden</span>";break;
case "Hulpgevraagd": echo $status == $statuscode ? "<a class='beheer' href = 'indexbody.php'>+</a>" : ""; echo "<span class='title'>Hulp Gevraagd</span>";break;
case "Leenaanbod": echo $status == $statuscode ? "<a class='beheer' href = 'indexbody.php'>+</a>" : ""; echo "<span class='title'>Te Leen Aangeboden</span>";break;
case "Spulaanbod_Beheer": echo "<span class='title'>Spulaanbod Beheer</span>";break;
case "Spulgevraagd_Beheer": echo "<span class='title'>Spulgevraagd Beheer</span>";break;
case "Info":echo "<span class='title'>Info</span>";break;
case "Bericht": echo "<span class='title'>Bericht Versturen</span>";break;
}

}

function setService() {

extract($GLOBALS);
switch($_GET['service'])
{
case "Spulaanbod": include '../paginas/nwspul.php';break;
case "Spulaanbod_Beheer": include '../.beheer/.nwspul_form.php';break;
case "Spulgevraagd_Beheer": break;
case "Spulgevraagd": include'../paginas/vraag.php';break;
case "Hulpaanbod": include'../paginas/dienstenA.php';break;
case "Hulpgevraagd": include'../paginas/dienstenV.php';break;
case "Leenaanbod": include '../paginas/uitleen.php';break;
case "Bericht": include '../paginas/bericht.php';break;
}

}

function setServices() {
extract($GLOBALS);
include ‘…/paginas/services.php’;
}

function setPages() {

extract($GLOBALS);
if(!isset($_GET['page']))
	$_GET['page'] = 1;
	
switch($_GET['service'])
{
case "Spulaanbod": $result = $db->query("SELECT COUNT(*) AS Aantal FROM AanbodSpul;");break;
case "Spulgevraagd": $result = $db->query("SELECT COUNT(*) AS Aantal FROM Vraag;");break;
case "Hulpaanbod": $result = $db->query("SELECT COUNT(*) AS Aantal FROM DienstenA;");break;
case "Hulpgevraagd": $result = $db->query("SELECT COUNT(*) AS Aantal FROM DienstenV;");break;
case "Leenaanbod": $result = $db->query("SELECT COUNT(*) AS Aantal FROM Uitleen;"); break;
}

if($_GET['service'] != "Info" && $_GET['service'] != "Bericht" && $_GET['service'] != "Spulaanbod_Beheer") {
foreach($result as $row)
{ extract($row); }

$_GET['pages'] = ceil($Aantal/$page_limit);

if($_GET['pages'] == 0)
	$_GET['pages'] = 1;
	
echo "<span id='pages'>Pagina ".$_GET['page']." van ".$_GET['pages']."</span>";
}

}

function setButtons() {

$page = $_GET['page'];
$pages = $_GET['pages'];
$serv = $_GET['service'];

if($_GET['amount'] != "")
$am = "&amount=".$_GET['amount'];
else
$am = "";

if($_GET['status'] != "")
$stat = "&status=".$_GET['status'];
else
$stat = "";

if($page > 1) {
echo"<a class='a' href='indexbody.php?service=$serv&page=1$am$stat'><div id='first'>|< </div></a>";
echo"<a class='a' href='indexbody.php?service=$serv&page=".($page - 1)."$am$stat'><div id='previous'> < </div></a>";
}

if($pages > 10)
$left_range = 8;
else
$left_range = 9;

if($page >= 10)
$right_range = 1;
else
$right_range = 10 - $page;
		
for($i = ($page - $left_range) ; $i <= ($page + $right_range); $i++) {
	
	if($i > 0 && $i <= $pages) {		
		if($page == $i)
		echo"<a class='a'><div id='selected'>$i</div></a>";
		else
		echo"<a class='a' href='indexbody.php?service=$serv&page=$i$am$stat'><div id='p'>$i</div></a>";
	}
		
}

if($page < $pages) {
echo"<a class='a' href='indexbody.php?service=$serv&page=".($page + 1)."$am$stat'><div id='next'> > </div></a>"; 
echo"<a class='a' href='indexbody.php?service=$serv&page=$pages$am$stat'><div id='last'> >| </div></a>";
}

}

function setNumberPerPage($id) {

$options = array(
			
			 6 => 6,
			12 => 12,
			18 => 18,
			24 => 24,
			30 => 30
		
);

if($_GET['service'] != "Info" && $_GET['service'] != "Bericht" && $_GET['service'] != "Spulaanbod_Beheer") {
	
	echo"<form name = 'form' action = 'indexbody.php' method = 'GET'>
	<label for = 'amount' id = 'label'>Aantal: </label>
	<select id='$id' name='amount' onchange='setAmount(this)'>";
	
	foreach($options as $key => $value) {
	 		
		$selected = $_GET['amount'] == $key ? 'selected' : '';
		if($key == 24 && !$_GET['amount'])
			$selected = 'selected';
	  
		echo"<option value = '".$key."' ".$selected.">".$value."</option>";
	}
	
	echo"</select></form>";
}			

}

if($_GET[‘amount’] != “”)
$amount = “&amount=”.$_GET[‘amount’];
else
$amount = “”;

if($_GET[‘status’] != “”)
$stat = “&status=”.$_GET[‘status’];
else
$stat = “”;

$menu = <<< MENU

MENU;

echo <<< START

Test

START;
?>

<? echo "
"; setHeader(); echo "
$menu
";setServTitel(); echo" ";setNumberPerPage("amount1"); echo" ";setPages(); echo" ";setButtons(); echo"
";setService(); echo "
";setServTitel(); echo" ";setNumberPerPage("amount2"); echo" ";setPages(); echo" ";setButtons(); echo"
";setServices(); echo "
$menu
Copyright © 2013 Fijenoord Ruilwinkel
"; ?>[/php]

and this is nwspul.php :

[php]<?php

require_once("…/.common/.init.php");

$db = new PDO(DBASE);

$table = “AanbodSpul”;

print <<< START

bekijk de nieuwe spullen START; function Delete($id) {
extract($GLOBALS);
if (isset($id)) {
$db->query("DELETE FROM $table WHERE id = $id;");
if (is_file("../fotoos/$id.jpg")) {unlink("../fotoos/$id.jpg");}
echo "ID: $id";
}

}

$offset = ($_GET[‘page’] - 1) * $page_limit;

//advertenties
$result = $db->query(“SELECT * FROM $table ORDER BY id DESC LIMIT $page_limit OFFSET $offset;”);
$i=-1;

echo “”;

echo “

”;

foreach($result as $row)
{ extract($row);

$i++;

//$tekst = stripslashes($tekst);

if (is_numeric($lidnr)){

$plaatje="../fotoos/".$id.".jpg";
$ext = "jpg";
if (!is_file($plaatje)) 
{$plaatje='../fotoos/blanco.gif'; $ext = "gif"; }

$src="resizefoto.php?plaatje=$plaatje&ext=$ext";

$info = "Spullen:$id <small>$datum</small>";

//ad
echo “

”;
if ($status == $statuscode) {
		echo"<input type='submit' id='wisbutton' name='wisbutton[$i]' value='Delete' title='wis advertentie'>";
		if($wisbutton[$i])
			Delete($id);

          }
	echo"</td>"; 

}

}
echo"

";

$db = NULL;

print <<<EINDE

EINDE;

?> [/php]

This is the link to the webpage: http://expopunt.nl/.kwikweb/paginas/indexbody.php?service=Spulaanbod&status=1234

You can see only the picture is deleted.

There are a ton of things wrong with your code, don’t think I can even list them all. First as I have pointed out before, the use of extract() is not wise unless you understand it’s full use which I doubt you do. If you use that anywhere on $_POST or $_GET or $_REQUEST you are just asking for security issues. This function also makes it virtually impossible to find where a var got it’s value from. So when you use it in a custom function and you use vars in that function that have no reference within the function, it’s very difficult to figure out why it doesn’t hold the value it should, hence the issue with your queries. STOP using this function and either use the global keyword like I previously showed OR bring the needed vars into your custom function via arguments in the function call.

A big issue I could see is this function
[php]
function setServices() {
extract($GLOBALS);
include ‘…/paginas/services.php’;
}
[/php]
You do understand that by including a page inside a function that the information being included is only available within the function itself. You should not be including files in this way. You can include a file within a function if there is specific info within that file that is needed, like a mailing library, but the include can’t then be used in the global space.

Your switch() in the setPages() is terribly designed. You don’t even need a switch for what you are doing. Simply sanitize the $_GET[‘service’] and then place it in the query. Repeating the query calls so many times for something that can be controlled via variable it bad coding. Also based on the queries that I see, I have a good feeling that your database design structure if highly flawed too.

What browser are you using to test your pages? I use firefox myself, but chrome has similar test tools also. Your very obviously haven’t even taken a look at the html output your code is producing, cause if you had you would have seen that you have 3 doctypes, and tags being output one 1 page. That it absurd. Plus you have a block that is being output inbetween the head and body tags. Nevermind the query issue you have, you can’t expect any browser to produce the look you want when you feed it garbage to deal with. I can tell from that and the rest of you code that you entire design structure is flawed.

What is the purpose of the added “.” in your file paths like in this line just before the common and init?
[php] require_once("…/.common/.init.php");[/php]
That is NOT common practice and again not sure where you learned such a method.

Lastly, you didn’t provide the code for init.php so I can’t comment on that. But seriously and I mean this with all sincerity, this entire thing needs to be scraped and redone. Those are only the points that I could quickly point out, there are plenty more. If you want a good source of tutorials with real world examples then check out phpacademy on youtube. Alex uses practical examples and describes things very well.

First I like to state I realize people learn programming at different speeds and even will write a script in their own way. With that said, I don’t understand how simple thing as deleting something from a database can be so hard? I mean when I first started leaning PHP, I had trouble with inserting, retrieving and even updating (though once I was pointed what I was doing wrong updating became very simple). However, I never once had a problem with deleting something, other than it was easy which made it dangerous. ;D However, I guess what is one person’s piece of cake is another person’s stumbling block. (???). I agree with fatsol and go to pcpacademy ( or even gulp W3Schools), for that is better than spinning your own wheels and others as well.

the use of extract() is not wise unless you understand it's full use which I doubt you do
.

I understand. It is used to import variables from an array.

You do understand that by including a page inside a function that the information being included is only available within the function itself. You should not be including files in this way.

So instead of [php]

";setPages(); echo"[/php] I had to do this you mean: [php]";include ‘…/paginas/services.php’; echo"[/php]
Your switch() in the setPages() is terribly designed. You don't even need a switch for what you are doing. Simply sanitize the $_GET['service'] and then place it in the query.

I know. I could have given the [php]$_GET[‘service’][/php] the name of the table inside the database. I could have done this:

[php]$service = $_GET[‘service’]; $result = $db->query(“SELECT COUNT(*) AS Aantal FROM $service;”);[/php]

What browser are you using to test your pages?

I am using firefox, chrome and opera.

Your very obviously haven't even taken a look at the html output your code is producing, cause if you had you would have seen that you have 3 doctypes, and tags being output one 1 page.

On which page you mean ? I have just one and tag.

What is the purpose of the added "." in your file paths like in this line just before the common and init?

That was not my idea. The website already existed. I have just adapted the website.

You may understand the basics of how to use it but I highly doubt you understand it’s full potential for issues. I base that statement on the fact that you don’t seem to grasp even the normal/simple tasks of php. By using that function you make it very difficult to trace down errors, which you seem to have a lot of.

Yes that would be the correct way to do it.

Yes, but you MUST sanitize the $_GET or you’re wide open for sql injection.

The page you linked to http://expopunt.nl/.kwikweb/paginas/indexbody.php?service=Spulaanbod&status=1234
If you right click and use the “view source” option you’ll see a ton of html errors. If you haven’t installed firefoxes firebug add-on, I highly suggest doing so. It is a very nice developer tool. Chrome also has this natively by pressing the F12 key.

I advise you to remove all occurrences of such code then, like I said it’s not normal. I am no expert in file path manipulation or how exactly computers interpret such things but I can’t see any good coming from doing it that way.

Since you have “adapted” this website and were not the original builder, I wonder what other very bad things lurk in the files either from you or the other builder. I said it before, scrape this code and start over from a strong code base. You will only face many many more issues if you continue to use this code.

if you right click and use the "view source" option you'll see a ton of html errors.

Yes now I have seen it. But in my developer tools dreamweaver and PSPad you don’t see the double and tags. How is that possible ?

Sponsor our Newsletter | Privacy Policy | Terms of Service