Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how can i insert data into a table in postgresql database 1

Status
Not open for further replies.
gglgokop1704

gglgokop1704

Programmer
Aug 9, 2007
54
GB
Plesae how can I insert into postgresql table from a form. I have two files. One is .html form which coolects inputs and the other is .php script which connects to a table and inserts the input data. The code for the form is:

<html>
<body>

<form action="insert.php" method="post">
Criteria ID: <input type="text" name="criteriaid" />
Domain ID: <input type="text" name="domainid" />
Criteria Name: <input type="text" name="criterianame" />
Upper Limit: <input type="text" name="upperlimit" />
Lower Limit: <input type="text" name="lowerlimit" />
Range: <input type="text" name="range" />
Unit: <input type="text" name="unit" />
<input type="submit" />
</form>

</body>
</html>


and the code for the php is:

<?php
// Connecting, selecting database
$dbconn = pg_connect("host=138.250.104.227 dbname=rftDatabase user=globus password=globususer");


// Performing SQL query
$query = "INSERT INTO Criteria_Parameters (CriteriaID,DomainID,Criteria_Name,Upper_Limit,Lower_Limit,Range,Unit) VALUES ($_POST[criteriaid],'$_POST[domainid]','$_POST[criterianame]','$_POST[upperlimit]','$_POST[lowerlimit]','$_POST[range]','$_POST[unit]'";

$query = pg_query($query);
if($query)
echo "inserted successfully!";
else{
echo "There was an error! ".pg_last_error();
}

// Closing connection
pg_close($dbconn);
?>

It does not give any error but it does not insert into my table also.

Any help is appreciated
Gokop
 
Sort by date Sort by votes
You have done so well Jpadie. I did that and got this error:

Warning: implode(): Bad arguments. in /var/ on line 50

Warning: implode(): Bad arguments. in /var/ on line 50

Warning: implode(): Bad arguments. in /var/ on line 50

Warning: implode(): Bad arguments. in /var/ on line 50

Warning: implode(): Bad arguments. in /var/ on line 50

Warning: implode(): Bad arguments. in /var/ on line 50

Warning: implode(): Bad arguments. in /var/ on line 58

Warning: pg_query(): Query failed: ERROR: syntax error at or near ")" at character 34 in /var/ on line 61
Problem inserting database record. Error was ERROR: syntax error at or near ")" at character 34

I am using php4. I guess I have a version problem.

Regards
Gokop
 
Upvote 0 Downvote
please post the exact code you are using. as the code works on my computer it must be something that you are doing on yours that causes the errors (or a version incompatibility issue - which I doubt).

ensure that you post code between the code tags
 
Upvote 0 Downvote
Hi Jpadie,

See the codes:

<?php
//set the debug parameters
ini_set('display_errors','on');
error_reporting (E_ALL);

//start the motor
switchboard();

function switchboard(){
if (isset($_POST['submit'])){
processForm();
}
displayForm();
}

function displayForm(){
echo <<<HTML
<html>
<body>

<form action="{$_SERVER['PHP_SELF']}" method="post">
Criteria ID: <input type="text" name="criteriaid" />
Domain ID: <input type="text" name="domainid" />
Criteria Name: <input type="text" name="criteria_name" />
Upper Limit: <input type="text" name="upper_lim" />
Lower Limit: <input type="text" name="lower_lim" />
Range: <input type="text" name="range" />
Unit: <input type="text" name="unit" />
<input type="submit" name="submit" value="submit" />
</form>

</body>
</html>
HTML;
}

function processForm(){
//connect to the database
$dbconn = pg_connect("host=138.250.104.227 dbname=rftDatabase user=globus password=globususer");
if ($dbconn === false){
die ($dbconn->getMessage());
}

//profile the form briefly
$fields = array("criteriaid", "domainid", "criteria_name", "upper_lim", "lower_lim", "range", "unit");


$values = '';
foreach ($fields as $field){
$fields = implode (" , ", $fields);
if (!empty($_POST[$field])){
$dbValues[] = "'".pg_cleanse($_POST[$field])."'";
} else {
$dbValues[] = ' null ';
}
}

$fields = implode (" , ", $fields);

$query = "Insert into Criteria_Parameters ($fields) VALUES (" . implode (',',$dbValues) . ")";
$result = pg_query($query);
if ($result === false){
echo "<div>Problem inserting database record. Error was " . pg_last_error() ."</div>";
} else {
echo "<div>Database Record inserted correctly</div>";
}
//don't really need this
pg_close($dbconn);
}

function pg_cleanse($value){
if (is_string($value)){
return pg_escape_string(trim($value));
}
if (is_null($value)) {
return null;
}
return $value;
}
?>

Gokop
 
Upvote 0 Downvote
you changed the cases that I posted. postgresql is case sensitive

try this code without changing anything.
Code: 
<?php
//set the debug parameters
ini_set('display_errors','on');
error_reporting (E_ALL);

//start the motor
switchboard();

function switchboard(){
    if (isset($_POST['submit'])){
        processForm();
    }
    displayForm();
}

function displayForm(){
    echo <<<HTML
<html>
<body>

<form action="{$_SERVER['PHP_SELF']}" method="post">
Criteria ID: <input type="text" name="CriteriaID" /><br/>
Domain ID: <input type="text" name="DomainID" /><br/>
Criteria Name: <input type="text" name="Criteria_Name" /><br/>
Upper Limit: <input type="text" name="Upper_Lim" /><br/>
Lower Limit: <input type="text" name="Lower_Lim" /><br/>
Range: <input type="text" name="Range" /><br/>
Unit: <input type="text" name="Unit" /><br/>
<input type="submit" name="submit" value="submit" />
</form>

</body>
</html>
HTML;
}

function processForm(){
    //connect to the database
    $dbconn = pg_connect("host=138.250.104.227 dbname=rftDatabase user=globus password=globususer");
    if ($dbconn === false){
        die (pg_last_error());
    }
    
    //profile the form briefly
    $fields = array('CriteriaID', 'DomainID', 'Criteria_Name', 'Upper_Lim', 'Lower_Lim', 'Range', 'Unit');
	//$fields = array("criteriaid", "domainid", "criteria_name", "upper_lim", "lower_lim", "range", "unit");
    
    
    $values = '';
    foreach ($fields as $field){
        $fields = implode (" , ", $fields);
    if (!empty($_POST[$field])){
            $dbValues[] = "'".pg_cleanse($_POST[$field])."'";
        } else {
            $dbValues[] = ' null ';
        }
    }
    
   $fields = implode (" , ", $fields);
    
    $query = "Insert into Criteria_Parameters ($fields) VALUES (" . implode (',',$dbValues) . ")";
    $result = pg_query($query);
    if ($result === false){
        echo "<div>Problem inserting database record.  Error was " . pg_last_error() ."</div>";
    } else {
        echo "<div>Database Record inserted correctly</div>";
    }
    //don't really need this
    pg_close($dbconn);
}
    
 function pg_cleanse($value){
    if (is_string($value)){
        return pg_escape_string(trim($value));
    }
    if (is_null($value)) {
        return null;
    }
    return $value;
}
?>
 
Upvote 0 Downvote
Hi Thanks,

Without changing anything, I got:

Warning: implode(): Bad arguments. in /var/ on line 51

Warning: implode(): Bad arguments. in /var/ on line 51

Warning: implode(): Bad arguments. in /var/ on line 51

Warning: implode(): Bad arguments. in /var/ on line 51

Warning: implode(): Bad arguments. in /var/ on line 51

Warning: implode(): Bad arguments. in /var/ on line 51

Warning: implode(): Bad arguments. in /var/ on line 59

Warning: pg_query(): Query failed: ERROR: syntax error at or near ")" at character 34 in /var/ on line 62
Problem inserting database record. Error was ERROR: syntax error at or near ")" at character 34
 
Upvote 0 Downvote
By commenting the $fields = implode (" , ", $fields); below foreach and above $query, I got:

Warning: pg_query(): Query failed: ERROR: syntax error at or near "Array" at character 34 in /var/ on line 62
Problem inserting database record. Error was ERROR: syntax error at or near "Array" at character 34

I guess my version does not understand the 'implode' function.

Can we do without the implode in:

$query = "Insert into Criteria_Parameters ($fields) VALUES (" . implode (',',$dbValues) . ")";

Because that is where it is showing the error now.

Regards
Gokop
 
Upvote 0 Downvote
i must be too tired. a copy and paste went wrong.

please use the exact code i posted above but change this block as shown

Code: 
foreach ($fields as $field){
       [red]//[/red] $fields = implode (" , ", $fields);
    if (!empty($_POST[$field])){
            $dbValues[] = "'".pg_cleanse($_POST[$field])."'";
        } else {
            $dbValues[] = ' null ';
        }
    }
    
   $fields = implode (" , ", $fields);
 
Upvote 0 Downvote
Hi, It gave this error:

Warning: pg_query(): Query failed: ERROR: array value must start with "{" or dimension information in /var/ on line 62
Problem inserting database record. Error was ERROR: array value must start with "{" or dimension information

in line 62 i.e.
$result = pg_query($query);

which of course is from:

$query = "Insert into Criteria_Parameters ($fields) VALUES (" . implode (',',$dbValues) . ")";
 
Upvote 0 Downvote
In postgres, I declared the table fields data types as:

criteriaid as int8[]
domainid as int8[]
criteria_name as text[]
upper_lim as float8[]
lower_lim as float8[]
range as float8[]
unit as text[]

Has this got anything to do with the error. Anyway they were declaed as int8, float8 and text initially. But when I saw the same error messages, I declared them as array, but still got the same errors.

Thanks for your help.
Regards
Gokop
 
Upvote 0 Downvote
i don't believe that array is a valid type for postgresql it's a programming construct.

you have been very inconsistent about the precise database fieldnames in your posts above. sfaik, postresql is case sensitive on field and table names. you need to pay close attention to these things.

try this code. it will give you more debug information

Code: 
<?php
//set the debug parameters
ini_set('display_errors','on');
error_reporting (E_ALL);

//start the motor
switchboard();

function switchboard(){
    if (isset($_POST['submit'])){
        processForm();
    }
    displayForm();
}

function displayForm(){
    echo <<<HTML
<html>
<body>

<form action="{$_SERVER['PHP_SELF']}" method="post">
Criteria ID: <input type="text" name="CriteriaID" /><br/>
Domain ID: <input type="text" name="DomainID" /><br/>
Criteria Name: <input type="text" name="Criteria_Name" /><br/>
Upper Limit: <input type="text" name="Upper_Lim" /><br/>
Lower Limit: <input type="text" name="Lower_Lim" /><br/>
Range: <input type="text" name="Range" /><br/>
Unit: <input type="text" name="Unit" /><br/>
<input type="submit" name="submit" value="submit" />
</form>

</body>
</html>
HTML;
}

function processForm(){
    //connect to the database
    $dbconn = pg_connect("host=138.250.104.227 dbname=rftDatabase user=globus password=globususer");
    if ($dbconn === false){
        die (pg_last_error());
    }
    
    //profile the form briefly
    $fields = array('CriteriaID', 'DomainID', 'Criteria_Name', 'Upper_Lim', 'Lower_Lim', 'Range', 'Unit');
    //$fields = array("criteriaid", "domainid", "criteria_name", "upper_lim", "lower_lim", "range", "unit");
    
    
    $values = '';
    foreach ($fields as $field) {
	    if (!empty($_POST[$field])){
	            $dbValues[] = "'".pg_cleanse($_POST[$field])."'";
	    } else {
	            $dbValues[] = ' null ';
	    }
    }
   
   	$fields = implode (" , ", $fields);
    
    $query = "Insert into Criteria_Parameters ($fields) VALUES (" . implode (',',$dbValues) . ")";
    $result = pg_query($query);
    if ($result === false){
        echo "<div>Problem inserting database record.  Error was " . pg_last_error() ."</div><div>Query was $query</div>";
    } else {
        echo "<div>Database Record inserted correctly</div>";
    }
}
    
 function pg_cleanse($value){
    if (is_string($value)){
        return pg_escape_string(trim($value));
    }
    if (is_null($value)) {
        return null;
    }
    return $value;
}
?>
 
Upvote 0 Downvote
Hi Jpadie,

My first postgres table was Criteria_Parameters and columns were 'CriteriaID', 'DomainID', 'Criteria_Name', 'Upper_Lim', 'Lower_Lim', 'Range', 'Unit'. But after running the php script it complains. I went through the postgres documentation and I saw that the script converts all names to lower case except if enclosed by double quotes " ". So I decided to put the table name and columns names to lower case. It solved the errors. It doesen't matter if in the codes they are in upper or lower case for as long as the table fields in the postgres database are in lower case. If I change one of the column fields toupper case (may be just the first letter) it will issue an error.


I ran the script above and here is the result:

Warning: pg_query(): Query failed: ERROR: array value must start with "{" or dimension information in /var/ on line 61
Problem inserting database record. Error was ERROR: array value must start with "{" or dimension information
Query was Insert into Criteria_Parameters (CriteriaID , DomainID , Criteria_Name , Upper_Lim , Lower_Lim , Range , Unit) VALUES ('102','201','parameters','200','20','14','kilometers')

The values ('102','201','parameters','200','20','14','kilometers') are the actual values I entered from the form. However it did not insert them intothe criteria_parameters. I will change the names and you see the outputs.
 
Upvote 0 Downvote
Hi,

I changed the criteria_parameters to Criteria_Parameters and see the error:

Warning: pg_query(): Query failed: ERROR: relation "criteria_parameters" does not exist in /var/ on line 61
Problem inserting database record. Error was ERROR: relation "criteria_parameters" does not exist
Query was Insert into Criteria_Parameters (CriteriaID , DomainID , Criteria_Name , Upper_Lim , Lower_Lim , Range , Unit) VALUES ('102','201','parameters','200','20','14','kilometers')


It says "criteria_parameters" does not exist.

When I changed to Criteria_Parameters to criteria_parameters see the error:

Warning: pg_query(): Query failed: ERROR: column "criteriaid" of relation "criteria_parameters" does not exist in /var/ on line 61
Problem inserting database record. Error was ERROR: column "criteriaid" of relation "criteria_parameters" does not exist
Query was Insert into Criteria_Parameters (CriteriaID , DomainID , Criteria_Name , Upper_Lim , Lower_Lim , Range , Unit) VALUES ('102','201','parameters','200','20','14','kilometers')


It is saying:

"criteriaid" of relation "criteria_parameters" does not

This is because it is written as CriteriaID. It is expecting it as criteriaid. Now I changed all to lower case and see the result again:

Warning: pg_query(): Query failed: ERROR: array value must start with "{" or dimension information in /var/ on line 61
Problem inserting database record. Error was ERROR: array value must start with "{" or dimension information
Query was Insert into Criteria_Parameters (CriteriaID , DomainID , Criteria_Name , Upper_Lim , Lower_Lim , Range , Unit) VALUES ('102','201','parameters','200','20','14','kilometers')

You see the errors about table name and column names are cleared. This is why I resorted to lower case.


Thanks for your patience
 
Upvote 0 Downvote
please post the table definition for your criteria_parameters table
 
Upvote 0 Downvote
i should have added that postgres does seem to have an array data type. but you are not, of course, inserting arrays into it. you are inserting text or numbers into it.

i suggest that you change the data types of the offending columns to numeric and character data types.

given that this is now (and probably always has been, now that i re-read the thread) a postgresql issue you may get better advice from the postresql forum.
 
Upvote 0 Downvote
Hi Jpadie,

Thank you very very much. It works fine when I changed from int8[] and float8[] to numeric and text[]to varchar. All this while it has been a postgres proiblem and not the script you gave me.

This is the message:

Database Record inserted correctly


thanks very much again
Gokop

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
250
SashaBuilder3
SashaBuilder3
EdLentz
  • Locked
  • Question
I need a simple example of how to send a table of info .
Replies
8
Views
284
EdLentz
EdLentz
P
  • Locked
  • Question
JPgraph - How to stop a line graph from dropping to zero?
Replies
2
Views
257
Chris Miller
Chris Miller
ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
306
ersatz
ersatz
Brianfree
  • Locked
  • Question
Dropdown list data generator
Replies
3
Views
203
Brianfree
Brianfree

Part and Inventory Search

Sponsor

Back
Top