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 |
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
Are you new to LinuxQuestions.org? Visit the following links:
Site Howto |
Site FAQ |
Sitemap |
Register Now
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
|
 |
11-18-2009, 03:15 AM
|
#1
|
Senior Member
Registered: May 2008
Distribution: Slackware
Posts: 1,052
Rep:
|
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.
|
|
|
11-18-2009, 09:47 AM
|
#2
|
LQ Guru
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 27,467
|
Quote:
Originally Posted by trist007
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.
|
|
|
11-18-2009, 10:33 AM
|
#3
|
Senior Member
Registered: May 2008
Distribution: Slackware
Posts: 1,052
Original Poster
Rep:
|
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?
|
|
|
11-18-2009, 12:19 PM
|
#4
|
LQ Guru
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 27,467
|
Quote:
Originally Posted by trist007
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...
|
|
|
11-18-2009, 01:09 PM
|
#5
|
Senior Member
Registered: May 2008
Distribution: Slackware
Posts: 1,052
Original Poster
Rep:
|
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.
|
|
|
11-18-2009, 02:05 PM
|
#6
|
Senior Member
Registered: May 2008
Distribution: Slackware
Posts: 1,052
Original Poster
Rep:
|
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.
|
|
|
11-19-2009, 01:04 AM
|
#7
|
Senior Member
Registered: May 2008
Distribution: Slackware
Posts: 1,052
Original Poster
Rep:
|
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?
|
|
|
11-20-2009, 01:40 AM
|
#8
|
Senior Member
Registered: May 2008
Distribution: Slackware
Posts: 1,052
Original Poster
Rep:
|
bump
|
|
|
11-21-2009, 10:25 AM
|
#9
|
Senior Member
Registered: May 2008
Distribution: Slackware
Posts: 1,052
Original Poster
Rep:
|
bump
|
|
|
All times are GMT -5. The time now is 06:36 AM.
|
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.
|
Latest Threads
LQ News
|
|