LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 11-18-2009, 03:15 AM   #1
trist007
Senior Member
 
Registered: May 2008
Distribution: Slackware
Posts: 1,027

Rep: Reputation: 69
Having trouble with postgresql in html...


I'm using Slackware 13.0. I'm trying to build a postgresql database of games and attach it to my apache webserver via php. I know that php and apache are working together because php executes nicely when viewing my web sever through a browser. All of this is taking place in my apt internal network behind a router. My webserver/postgresql server is 192.168.1.111.

I run postgresql 8.4.1 using

Code:
postmaster -i -D /usr/local/pgsql/data >logfile 2>&1 &
as postgres user.

Then I
Code:
createdb games
psql games
Then I
Code:
create table games_t(title text not null, genre text not null);
Then I create submitform.html
Code:
<html>
<body>

<p><p>

<form action=submitform.php method=GET>

Title: <input type=text name=title size=25 maxlength=25>

Genre: <input type=text name=genre size=25 maxlength=25>

<p>

<input type=submit>

</form>
</body>
</html>
Btw, what would the input type be if I were inputing integers, dates, or even a serial(built in function that increments a number by one when adding another entry into a database)? Of course, if one of these were not null and I left it blank, then this submission would not successfully write to the database because the database required a field that is tagged not null, correct? Btw, both of these fields are not null.

and submitform.php
Code:
<html>
<body>
<?php

//Connecting, selecting database
$dbconn=pg_connect("host=192.168.1.111 dbname=games user=postgres password=") or die('Could not connect:'.pg_last_error());

//Performing SQL query
$query = 'SELECT* FROM games_t';

$result = pg_query($query) or die('Query failed:'.pg_last_error());

//Printing results in HTML
echo "<table>\n";

while($line = pg_fetch_array($results,null,PGSQL_ASSOC)) {
echo "\t<tr>\n";
foreach($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}

echo "</table>\n";

//Free resultset and closing
pg_free_result($result); pg_close($dbconn);
?>
I put the submitform.html and submitform.php on my webserver.

I open up a browser and go to submitform.html on my webserver and enter a new title and genre say
title and fps respectively.

Then I go back into my database with
Code:
psql games
select * from games_t;
and the new entry was not added to the database. I do not have a password for connecting to my postgresql database. I will be adding one later. While we're at it, how would I go about adding a password?

I'm wondering, I installed postgresql after the default installation of slackware 13.0. Slackware 13.0 came with mysql but I decided to go with postgresql. I like the post family, namely postfix(better than sendmail). Anyhow, I'm thinking maybe I had to ./configure the postgresql build with php support?

For instance, in submitform.php that function $dbconn=pg_connect(); How would I check if that is indeed working?

How else can I troubleshoot the communication between php and postgresql?

Last edited by trist007; 11-18-2009 at 03:24 AM.
 
Old 11-18-2009, 09:47 AM   #2
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 17,916

Rep: Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689
Quote:
Originally Posted by trist007 View Post
I'm using Slackware 13.0. I'm trying to build a postgresql database of games and attach it to my apache webserver via php. I know that php and apache are working together because php executes nicely when viewing my web sever through a browser. All of this is taking place in my apt internal network behind a router. My webserver/postgresql server is 192.168.1.111.

I run postgresql 8.4.1 using

I put the submitform.html and submitform.php on my webserver.

I open up a browser and go to submitform.html on my webserver and enter a new title and genre say title and fps respectively.
Btw, what would the input type be if I were inputing integers, dates, or even a serial(built in function that increments a number by one when adding another entry into a database)? Of course, if one of these were not null and I left it blank, then this submission would not successfully write to the database because the database required a field that is tagged not null, correct? Btw, both of these fields are not null.
The input type relates to the button on the form. It can have several different actions, SUBMIT being one of them. The data types don't enter into it, that's just a web page form button action.
Quote:
Then I go back into my database with
and the new entry was not added to the database. I do not have a password for connecting to my postgresql database. I will be adding one later. While we're at it, how would I go about adding a password?

I'm wondering, I installed postgresql after the default installation of slackware 13.0. Slackware 13.0 came with mysql but I decided to go with postgresql. I like the post family, namely postfix(better than sendmail). Anyhow, I'm thinking maybe I had to ./configure the postgresql build with php support?

For instance, in submitform.php that function $dbconn=pg_connect(); How would I check if that is indeed working?

How else can I troubleshoot the communication between php and postgresql?
Well, the new entry isn't there, because in what you've posted, you've never actually inserted a record into the database. You need some sort of line that does the insert, such as:
Code:
$insertquery = "INSERT INTO <table name> values (\"$<variable 1>\",\"$<variable2>\")";
then execute that query, based on the variable names. Also, to see if you're connecting, try:
Code:
mysql_select_db(<database name>) or die ("Could not select database; contact administrator<BR>");
the example here is for MySQL, but PG should be similiar, if not identical.
 
Old 11-18-2009, 10:33 AM   #3
trist007
Senior Member
 
Registered: May 2008
Distribution: Slackware
Posts: 1,027

Original Poster
Rep: Reputation: 69
Oh ok, well the submitform.php should at least make a query, which it is not evern doing. However, I figured out why. I viewed
Code:
<?php
  phpinfo();
?>
in a browser and apparently, my build of php only has mysql support and no postgresql support. I need to compile php --with-pgsql=/usr/local/pgsql.

What would be the best way to do this? Should I just uninstall php? It came with the default installation of Slackware 13.0. If it was a package I could just use removepkg php. Or can I leave as is, and download php source from the internet, compile and install over the original? What should I do?
 
Old 11-18-2009, 12:19 PM   #4
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 17,916

Rep: Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689Reputation: 3689
Quote:
Originally Posted by trist007 View Post
Oh ok, well the submitform.php should at least make a query, which it is not evern doing. However, I figured out why. I viewed
Code:
<?php
  phpinfo();
?>
in a browser and apparently, my build of php only has mysql support and no postgresql support. I need to compile php --with-pgsql=/usr/local/pgsql.

What would be the best way to do this? Should I just uninstall php? It came with the default installation of Slackware 13.0. If it was a package I could just use removepkg php. Or can I leave as is, and download php source from the internet, compile and install over the original? What should I do?
Cleanest thing would be to uninstall the PHP you've got, and re-install, with the correct options. Building from source can be challenging, so I'd look for a pre-compiled package first, that has the stuff you need. If you can't find one, go for the source build...
 
Old 11-18-2009, 01:09 PM   #5
trist007
Senior Member
 
Registered: May 2008
Distribution: Slackware
Posts: 1,027

Original Poster
Rep: Reputation: 69
Finally I got it working. I uninstalled the default slackware 13 installation php package. I downloaded php-5.3.0 from source and did

Code:
./configure --with-apxs2=/usr/sbin/apxs --with-pdo-pgsql=/usr/local/pgsql --with-pgsql=/usr/local/pgsql
However I ran into a problem. Everytime I would try and run apache it would not start and would give me an error saying that libphp5.so was an undefined symbol. After doing some research, I found that doing a make clean before I configure would solve the problem, and indeed it did.

Now after checking out phpinfo(); in the browser, I now have pgsql and pdo_pgsql support. Sweet!! (I noticed that according to phpinfo(); I do not have SSL support for pgsql , there was no option in configure --help to configure php to have SSL support for pgsql, any ideas?) Also, what's the main difference between pgsql and pdo-pgsql?

I tried running the above submitform.php in firefox and I can tell php and my postgresql server are communicating because now I get php/postgresql related errors returned in my browser where as before the browser would stay blank.

Now I'm trying to experiment with making queries and inserting values via php.

On the above submitform.php I make a query "select * from games_t;" which is my games table. However, I get an error

Code:
Warning: pg_fetch_array() expects parameter 1 to be resource, null given in /var/www/htdocs/submitform.php on line 16
I tried replacing that null with a 1 and there was no change. Could somebody explain this error to me?

I've been looking for a good resource for learning php with postgresql, but they're hard to come by.

Last edited by trist007; 11-18-2009 at 01:16 PM.
 
Old 11-18-2009, 02:05 PM   #6
trist007
Senior Member
 
Registered: May 2008
Distribution: Slackware
Posts: 1,027

Original Poster
Rep: Reputation: 69
Got it to work!

For those who are interested in doing something similar. My query.php looks like this.

Code:
<html>
<body>
<?php

//initiate connection
$dbconn = pg_connect("host=localhost dbname=games user=postgres password=") or die('Could not connect:'.pg_last_error());

//check if user connected
if (!$dbconn) {
  echo "A connection error occured.\n";
  exit;
}

//execute query
$result = pg_query($dbconn, "SELECT genre FROM games_t");
if (!$result) {
  echo "A query error occured.\n";
  exit;
}

//display results
while ($row = pg_fetch_row($result)) {
  echo "Title: $row[0]";
  echo "<br />\n";
}

//free memory
pg_free_result($result);

//close connection
pg_close($dbconn);

?>
</body>
</html>
I've been at this for a few days. Feels great to get it working. I can't wait to start building my database. w00t.
 
Old 11-19-2009, 01:04 AM   #7
trist007
Senior Member
 
Registered: May 2008
Distribution: Slackware
Posts: 1,027

Original Poster
Rep: Reputation: 69
I got a few follow-up questions

1. When using php through a website to access my postgresql database(pg_connect), should I consider creating another user besides postgres that would login and make queries, or is postgres enough? Also, should I add a password for logging in with pg_connect? If I do, won't hackers be able to view the source code of say query.php which would include the line where I have my pg_connect function that includes my clear text password? Also, I ran phpinfo(); and under postgresql support, it said that I did not have SSL support. How do I add it? I don't remember seeing an option for configuring php to have SSL support? Is this related to apache's mod_ssl module?

2. I still don't understand why some binaries can be execute by simplying typing in the command while others require you to run ./example for it to run. I know that by using the '.' you run the binary from that directory, but I still don't understand why you would need to do that? Could somebody post a scenario where one would be used(without '.') and where the other(with '.') would be used?

3. When setting up proftpd server, what permissions should I give for the files so that users who run windows(ntfs fs) would be able to download it and have full control(rwx permissions) of the file?
 
Old 11-20-2009, 01:40 AM   #8
trist007
Senior Member
 
Registered: May 2008
Distribution: Slackware
Posts: 1,027

Original Poster
Rep: Reputation: 69
bump
 
Old 11-21-2009, 10:25 AM   #9
trist007
Senior Member
 
Registered: May 2008
Distribution: Slackware
Posts: 1,027

Original Poster
Rep: Reputation: 69
bump
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
HTML Form for a postgresql database Evan P. Linux - Software 1 02-12-2007 08:43 AM
Search & Replace Technique to Fix html HREFs w/o html extension donv2 Linux - Newbie 1 01-31-2007 12:59 AM
easy html trouble abrouwers Programming 3 01-04-2007 03:45 PM
Konqueror + file:/usr/share/doc/HTML/index.html jon_k Linux - Software 2 11-25-2003 06:06 AM
postgresql -odbc & postgresql-jdbc installation kjsubbu Linux - Software 0 06-19-2003 03:50 AM


All times are GMT -5. The time now is 06:34 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration