1

I am figuring out how to add data to a Postgresql-database via a dynamic php-based webpage. In the book "PHP and Postgresql: advanced web programming" (2002) I found some interesting code for setting up a content management system (see below). Everything goes fine when I load the data in Firefox or Chrome, but when I press the submit button no data from the fields is added to the database. Nothing happens. I wonder, is this code outdated (book is from 2002) or is there something else wrong or missing? The connection with the database seems fine (no error). 

 <?php

    $data .= "<html>
            <body>
            <h1>Content Management Tool</h1>
            Edit the table <br>";

    # connecting to the database
    $dbh = pg_connect("dbname=postgres user=postgres host=localhost password=");
    if      (!$dbh)
    {
            die ("cannot connect to database<br>\n");
    }
    # checking for insert
    if      ($number_of_fields)
    {
            $sql = "INSERT INTO $tab VALUES (";
            for     ($i = 1; $i < $number_of_fields - 1; $i++)
            {
                    $sql .= "'${$i}', ";
            }
            $max = $number_of_fields - 1;
            $sql .= "'${$max}')";
            $ret = pg_query($dbh, $sql) or
                    die ("cannot execute UPDATE operation<br>\n");
    }
    # retrieving a list of all tables in the database
    $sql = "SELECT oid, * FROM message";
    $res = @pg_query($dbh, $sql) or
            die ("cannot retrieve list of tables");

    # displaying list
    $data .= "<br><b>Details:</b><br>\n";
    $rows = pg_num_rows($res);
    $fields = pg_num_fields($res);

    # displaying header of table
    $table .= "<table border=3><tr>\n";
    for     ($i = 1; $i < $fields; $i++)
    {
            $table .= "<th>".
            pg_field_name($res, $i)."</th>";
    }
    $data .= $table."<th>action</th>\n";
    $data .= "</tr>\n";

    # displaying data
    $data .= '<form action="detail.php" method="post">';

    for     ($i = 0; $i < $rows; $i++)
    {
            $data .= "<tr>";
            $line = pg_fetch_row($res, $i);

            for     ($j = 1; $j < $fields; $j++)
            {
                    $data .= "<td>";
                    $data .= $line[$j];
                    $data .= "</td>";
            }
            $data .= '<td><a href="action.php?act=delete&oid='.
                    $line[0].'&tab='.$tab.'">Delete</a> </td> ';
            $data .= "</tr>\n";
    }
    $data .= "</table>\n";
    $data .= "<br><b>Add data to the table</b><br>\n";

    # displaying HTML for inserting data
    $data .= $table;
    $data .= "<tr>";
    for     ($i = 1; $i < $fields; $i++)
    {
            $data .= "<td>".
                    '<input type="text" name="'.$i.'"></td>';
    }
    $data .= "</tr>\n";
    $data .= "</table>\n";
    $data .= '<input type="hidden" name="number_of_fields" '.
            "value=\"$fields\"><br><br>";
    $data .= '<input type="hidden" name="tab" value="'.$tab.'">';
    $data .= '<input type="submit" name="submit" >';
    $data .= "</form>\n";

    echo $data, $tail;
 ?>

SQL:

CREATE TABLE message
(
id integer,
tstamp timestamp with time zone DEFAULT now(),
who text,
message text
)
WITH (
OIDS=TRUE
);
ALTER TABLE message
OWNER TO postgres;
GRANT ALL ON TABLE message TO postgres;
GRANT ALL ON TABLE message TO public;
|improve this question
1

"when I press the submit button no data from the fields is added to the database"

You are using if ($number_of_fields) to check if data was submitted. What you might additionally do to see, if data is incomming, is to place a var_dump($_POST); at the top after the opening <php? tag. That will show you the "incoming" data, after you pressed submit.

You might also place an additional echo 'Inserting Values'; inside the insert code block. That will show that $number_of_fields is set and the INSERT is going to happen next.

Now let's take a closer look at the insert code-block. You have a for-loop there, iterating over $i and appending ${i} to the SQL. That doesn't look right. I believe that $_POST[$i] would be a better fit here. You might try a var_dump($_POST[$i]); inside the loop to see, if you get some data values. If yes, then append that to the SQL.

            $sql = "INSERT INTO $tab VALUES (";
            for     ($i = 1; $i < $number_of_fields - 1; $i++)
            {
                    $sql .= "'${$i}', "; // <--- $_POST[$i] ???
            }
            $max = $number_of_fields - 1;
            $sql .= "'${$max}')";

            var_dump($sql); // <--- see, if SQL statement contains your data

You might also place a var_dump($sql); before the pg_query() function, to see, if the SQL statement contains your data, which would be sent to the DB.

|improve this answer
  • Thank you for your input. According to a debug tool for PHP coding, the variables "tab" and "number_of_fields" aren't defined: Notice: Undefined variable: number_of_fields in C:\Users\marko\Documents\NuSphere PhpED\Projects\detail.php on line 16. Is this correct? Notice: Undefined variable: tab in C:\Users\marko\Documents\NuSphere PhpED\Projects\detail.php on line 29 cannot retrieve list of tables) – ProgrammingHistorian Aug 13 '14 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.