1

I'm attempting to update my calendar's title based on the new user's input.

The variables are as follows:

$titleNew = 'New Title'
$titleOld = 'Old Title'

The old title exists inside the database and should update accordingly, but with this code I'm getting an error message that says my prepared statement does not exist.

$result = pg_prepare($conn, "update", "UPDATE calendar SET title = $1 WHERE title LIKE $2");

$result = pg_execute($conn, "update", array($titleNew, $titleOld));
Luigi Siri
  • 2,068
  • 2
  • 19
  • 27
user2415335
  • 53
  • 1
  • 2
  • 9
  • 2
    Did you test `$result` for success on the prepare? What does `pg_last_error()` tell you after `pg_prepare()`? – Michael Berkowski Jun 03 '13 at 18:13
  • 1
    You're assuming the prepare call succeeded. You should AT LEAST have soemthing like `$result = pg_prepare(...) or die(pg_last_error());`, or explicitly test $result for boolean false (`if($result === FALSE) ...`. Never EVER assume success with DB operations. Even if your sql statement is syntactically perfect, there's far too many other reasons for failure to NOT check. – Marc B Jun 03 '13 at 18:14
  • pg_last_error says prepared statement "update" does not exist. – user2415335 Jun 03 '13 at 18:16
  • Is it the result error of the `pg_prepare` execution, or of the `pg_execute`? You need to be sure that the `pg_prepare` is executing without error, so you need the error message returned by the `pg_prepare` method. – eternay Jun 03 '13 at 18:23
  • 1
    I don't recommend to use a `LIKE` there. – Luigi Siri Jun 03 '13 at 18:28
  • pg_prepare is saying that the relation calendar does not exist but I insert into it in another connection with the same reference. And what should I use instead of LIKE? – user2415335 Jun 03 '13 at 18:29
  • Make sure you are connected to the same DB. Instead of LIKE use equals... `WHERE title = $2` – Luigi Siri Jun 03 '13 at 18:35
  • Everything for the connection is the exact same. I also changed the LIKE to equals. The code that I have works if I run it in postgres, but when I run it inside the php it won't work. – user2415335 Jun 03 '13 at 18:37

1 Answers1

2

To find out, whether the table calendar actually exists in the database you are connected to, run in the same session:

SELECT n.nspname As schema_name, c.relname As tbl_name
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname ILIKE 'calendar';

I suspect the underlying problem is a confusion with table names or connection parameters.You may have:

  • confused the database.
  • confused the port.
  • a search_path setting that does not include the schema the table lives in. Check your search_path with: SHOW search_path;
    Is the schema name included?
  • misspelled the table name.
  • confused a double-quoted mixed-cased name like "Calendar" with the (generally preferable) lower case name calendar.

If you don't have the actual schema in your search_path: Either set the search_path properly or schema-qualify the table name:

UPDATE calendar.calendar SET title = $1 WHERE title = $2
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I ran the code and it returned a row schema name: calendar table_name: calendar. – user2415335 Jun 03 '13 at 18:42
  • @user2415335: I added simple query to check the current `search_path` of your session. Follow the link in my answer for more information about the `search_path`. – Erwin Brandstetter Jun 03 '13 at 18:45
  • I'm just not sure this is right because I have the exact same code with a different query to insert into the table and it works. I copy and pasted it. – user2415335 Jun 03 '13 at 18:56
  • the search_path is calendar so I changed my code to calendar.calendar and it still isn't working. – user2415335 Jun 03 '13 at 18:58
  • @user2415335: If the `search_path` is set to `calendar`, then adding the schema-name to the query is optional. Is `pg_prepare` still saying that the relation `calendar` does not exist? Or do you run into another error? You are aware, that double-quoting preserves the case in Postgres, right? `"Calender"` <> `calendar` ... – Erwin Brandstetter Jun 03 '13 at 19:01