Re: Error with query

On 10/7/10 9:54 AM, Helgi Örn Helgason wrote:
> I'm still a postgres newbie so don't get upset if I'm sending this
> question to the wrong forum, just tell me if I am and I'll move it.
>
> I get this error message in Firefox:
> Error with query: ERROR: invalid input syntax for type date: "" LINE
> 1: ..._name, pack_tidin, pack_tidut, pack_lunch) VALUES('', '', ''...
> ^
>
> When running this query with PHP:
>
> <?php
> $db = pg_connect('host=localhost dbname=name user=me password=pass');
>
> $pack_date = pg_escape_string($_POST['pack_date']);
> $pack_week = pg_escape_string($_POST['pack_week']);
> $pack_day = pg_escape_string($_POST['pack_day']);
> $pack_pts = pg_escape_string($_POST['pack_pts']);
> $pack_name = pg_escape_string($_POST['pack_name']);
> $pack_tidin = pg_escape_string($_POST['pack_tidin']);
> $pack_tidut = pg_escape_string($_POST['pack_tidut']);
> $pack_lunch = pg_escape_string($_POST['pack_lunch']);
>
> $query = "INSERT INTO timmar(pack_date, pack_week, pack_day, pack_pts,
> pack_name, pack_tidin, pack_tidut, pack_lunch) VALUES('" . $pack_date
> . "', '" . $pack_week . "', '" . $pack_day . "', '" . $pack_pts . "',
> '" . $pack_name . "', '" . $pack_tidin . "', '" . $pack_tidut . "', '"
> . $pack_lunch . "')";
> $result = pg_query($query);
> if (!$result) {
>     $errormessage = pg_last_error();
>     echo "Error with query: " . $errormessage;
>     exit();
> }
> printf ("Detta har lagts till i databasen - %s %s %s", $pack_date,
> $pack_week, $pack_day, $pack_pts, $pack_name, $pack_tidin,
> $pack_tidut, $pack_lunch);
> pg_close();
> ?>
>
> I've search every little " and ' and , and . and all I can think of
> but it still doesn't work. I started with only 3 columns (in another
> test-database) and everything was just fine.
> Anyone who can spot something I missed?
>

This would be a lot easier to read and also safer if you used prepared
statements (see http://www.php.net/manual/en/function.pg-prepare.php), e.g.

$result =         /* no tedious quoting */
    pg_prepare($dbconn, "my_query",
    'insert into timmar(pack_date, pack_tidut) values ($1, $2)');


$result = pg_execute($dbconn, "my_query", array($pack_date, $pack_tidut));

Christian