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 strongm 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
try debugging with the following at the start of your code
Code: 
ini_set('display_errors','on');
error_reporting(E_ALL);
 
Upvote 0 Downvote
Thanks Jpadie,

It gave this error:

Warning: pg_query(): Query failed: ERROR: syntax error at end of input at character 159 in /var/ on line 13
There was an error! ERROR: syntax error at end of input at character 159

When I added the 2 lines. See new code again:

<?php
//Debug
ini_set('display_errors','on');
error_reporting(E_ALL);

// 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);
?>

Kind regards
Gokop
 
Upvote 0 Downvote
well ... there's an error in your syntax!

two things:

1. you should cleanse all your query parameters (see below)
2. you need to close the round bracket at the end of the values segment.

Code: 
function pg_cleanse($value){
 if (is_binary($value)){
   return pg_escape_bytea($value);
 }
 if (is_string($value)){
   return pg_escape_string(trim($value));
 }
 if (is_null($value) {
   return null;
 }
 return $value;
}
 
Upvote 0 Downvote
Hi,

There is no more errors. Thanks. But it does not insert the data entered in the form to the Criteria_Parameters table. The golobus user has priviledge to create databases, but is not a superuser, cannot update catalogs. Is this a problem when inserting data to the table. globus is the owner of the table.

See the nee code:

<?php
//Debug
ini_set('display_errors','on');
error_reporting(E_ALL);

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

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

// Performing SQL query
$query = "INSERT INTO Criteria_Parameters (CriteriaID, DomainID, Criteria_Name, Upper_Lim, Lower_Lim, 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);
?>


Kind regards
Gokop
 
Upvote 0 Downvote
i am surprised that there are no errors showing up.

your query string is incorrect

Code: 
$query = "INSERT INTO Criteria_Parameters (CriteriaID, DomainID, Criteria_Name, Upper_Lim, Lower_Lim, Range, Unit) VALUES ($_POST['criteriaid'], $_POST['domainid'], $_POST['criterianame'], $_POST['upperlimit'], $_POST['lowerlimit'], $_POST['range'], $_POST['unit'][s][red]"[/red][/s])[red]"[/red];

i.e. the double quotes should be outside, encapsulating the string.

as posted above, you should always cleanse the data before using it in a query. I note that you are not doing so.

It is also a bad idea to reuse variables in the way that you do with $query and a good idea to get into the habit of explicitly testing the result for type. for example, in some languages, some queries might return an integer as to the number of affected rows from a _query. if the integer was zero then a (!$result) test might not be what you are looking for. test explicitly for false.

Code: 
$results = pg_query($query) 
if ($results === false){
  die ('There was an error! '. pg_last_error());
}


 
Upvote 0 Downvote
I have put the " where it should be as suggested. It does not give errors. I don't know why. It use to give errors but no more. Infact anything I put, does not send errors. I guess something is wrong somewhere. Is there a place to activate error messages? I have also put the code:
$results = pg_query($query)
if ($results === false){
die ('There was an error! '. pg_last_error());
}

which you suggested, it still did not give errors. It equally does not insert the data.

Regards
Gokop
 
Upvote 0 Downvote
echo the query out to the browser and see whether anything looks wrong. try cutting and pasting the output into a native client.
 
Upvote 0 Downvote
Hi Jpadie,

That is what I am doing, sending results to the browser. I have 2 files (criteria.htm which implements the form to enter the data and insert.php which is supposed to insert the the data and send results to the browser). See the two files below:

criteria.htm
<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 insert.php is

<?php
//Debug
ini_set('display_errors','on');
error_reporting(E_ALL);

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

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

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

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

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


I used to get errors and then perform debug, no errors show even if I type rubbish in the codes.

Regards
Gokop
 
Upvote 0 Downvote
i can't tell why you are getting errors, and i don't have a postgresql installation to test this on.

i have rewritten the code though. suggest you try this

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" />
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" />
</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 = implode (',', $fields);
	$values = '';
	foreach ($fields as $field){
		if (!empty($_POST[$field])){
			$dbValues[] = "'".pg_cleanse($_POST[$field])."'";
		} else {
			$dbValues[] = ' null ';
		}
	}
	$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_binary($value)){
		return pg_escape_bytea($value);
	}
	if (is_string($value)){
		return pg_escape_string(trim($value));
	}
	if (is_null($value)) {
		return null;
	}
	return $value;
}
?>
 
Upvote 0 Downvote
Hi Jpadie,

It looks ok, however it does not insert data into the table. I have even granted insert, select, udate priviledges to the globus user.

I will try working on configurations in httpd.conf and php.ini and see what happens.

Thanks for your great help.

Gokop
 
Upvote 0 Downvote
change the input button to give it a name

Code: 
<input type="submit" name="submit" value = "submit" />
 
Upvote 0 Downvote
Hi Jpadie,

Thanks. At least I saw errors now with this change in the input button type. See errors displayed on the browser:

Warning: Invalid argument supplied for foreach() in /var/ on line 49

Notice: Undefined variable: dbValues in /var/ on line 56

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

Warning: pg_query(): Query failed: ERROR: syntax error at or near ")" at character 108 in /var/ on line 57
Problem inserting database record. Error was ERROR: syntax error at or near ")" at character 108
Criteria ID: Domain ID

I will try debugging here also

Thanks
Gokop
 
Upvote 0 Downvote
that's my fault: as i said i dont have a pg installation to test on so have not been able to debug properly.

try this code

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" />
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){
		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_binary($value)){
		return pg_escape_bytea($value);
	}
	if (is_string($value)){
		return pg_escape_string(trim($value));
	}
	if (is_null($value)) {
		return null;
	}
	return $value;
}
?>
 
Upvote 0 Downvote
i guess the function does not exist on your version of php. use this version of pg_cleanse instead

Code: 
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
Itried to comment the is_binary and leave the is_string. I got this error:

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


Regards
Gokop
 
Upvote 0 Downvote
Yes that solves the problem also. I got some errors such as criteria_parameters relations, but I solved them. The error is :

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

Regards
Gokop
 
Upvote 0 Downvote
well, substituting the postgres functions for sqlite, it works fine for me.

i suspect that you have not moved the line
Code: 
	$fields = implode (',', $fields);

to the location i showed above. it must be below the $foreach and above the $query = line.
 
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
228
SashaBuilder3
SashaBuilder3
EdLentz
  • Locked
  • Question
I need a simple example of how to send a table of info .
Replies
8
Views
260
EdLentz
EdLentz
P
  • Question
JPgraph - How to stop a line graph from dropping to zero?
Replies
2
Views
220
Chris Miller
Chris Miller
ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
280
ersatz
ersatz
Brianfree
  • Locked
  • Question
Dropdown list data generator
Replies
3
Views
181
Brianfree
Brianfree

Part and Inventory Search

Sponsor

Back
Top