EDIT: This IS resolved. It was a simple case of querying the wrong schema name. The rest of the code works (even if it is not great coding practice).
This same question was asked here:
SQL statement not deleting in PHP
I don't see a solid resolution on that thread though. Basically my issue is that it says the delete was successful, but nothing was deleted. I'm pretty sure it has to do with the $1 in the delete statements, but I don't know what else to try. Could someone explain to me why this doesn't work? Here is my code:
$mySearch = $_POST['row'];
if($_POST['tbl'] == "country"){
$query = 'DELETE FROM lab2.country WHERE country.country_code = $1';
}
elseif($_POST['tbl'] == "city"){
$query = 'DELETE FROM lab2.city WHERE city.id = $1';
}
elseif($_POST['tbl'] == "language"){
$query = 'DELETE FROM lab2.country_language WHERE country_language.country_code = $1';
}
$stmt = pg_prepare($conn, $mySearch, $query); //prepare statement
$result = pg_execute($conn, $mySearch, array($mySearch)); //execute
if(!$result){ //error if no value in $result
die("Unable to execute: " . pg_last_error($conn));
} else{
//results are good so output them to HTML
echo "Delete was successful <br />";
echo "Return to <a href=\"" . $_SERVER['HTTP_REFERER'] . "\">search page</a>";
}
pg_free_result($result);
pg_close($conn);
return;
It seems a bit odd to use $mySearch both as the actual parameter and as the name of the prepared statement, especially since you're preparing 3 different statements that are not actually a function of that variable ($_POST['row']) but depend on $_POST['tbl'] instead.
Since you're redefining these statements every time, with a new name for the each new arguments, you're not actually gaining anything from re-use. This is likely to cause problems if you're searching with new arguments (since you can't re-use the same name in the same session, unless it's the empty string). You might as well use pg_query_params instead.
If you really want to use prepared statements, give them 3 fixed different names (one for each of your 3 queries): it's pg_execute that will take care of using different parameters for these same queries the following time.
One of the problems you may run into with these prepared statements, depending on the code around this, is if you're trying to redefine them again with the same name in the same session. I can't find a binding for libpq's PQdescribePrepared in PHP, but you could use the pg_prepared_statements view to see if there already is a prepared statement against the names you choose in the current session, before using pg_prepare.
The result of pg_prepare should also be a result that you should check for errors (it's not really a statement, as the $stmt variable name you've used seems to suggest).
EDIT:
As I said in a comment (which seems to lead to the suitable solution), it's also worth checking that lab2 is the right schema name. I was just a bit surprised by this because it's unusual to use schema if you're new to PostgreSQL (most people would just use public to start with), but I guess this may come from existing data provided by someone else. (Using "database_name.table_name" would also have been an easy mistake to make if you'd come from a MySQL background.)
According to the documentation in the pg_prepare, you have to put as second parameter the name to give the prepared statement. Must be unique per-connection. If "" is specified, then an unnamed statement is created, overwriting any previously defined unnamed statement. and in the pg_execute the second parameter is the name of the prepare statement.
This:
$stmt = pg_prepare($conn, $mySearch, $query); //prepare statement
$result = pg_execute($conn, $mySearch, array($mySearch)); //execute
Should be:
$result = pg_prepare($conn, "my_query", $query); //prepare statement
$result = pg_execute($conn, "my_query", array($mySearch)); //execute