php postgresql login script help

Posted on 2012-03-12
Last Modified: 2012-04-09
What are the steps to make a simple login? I've found several scripts for php/mysql; I happen to be using postgresql, and thought it may be easier just to write it myself since the free content that's available written for postgresql is a little on the light side.

The script doesn't immediately need many of the features that the more professional, fleshed-out mysql scripts include. The number of users is so small right now, that I'll be issuing usernames and passwords (I don't think the functionality to add accounts dynamically will be too difficult to add later).

So given all that, how is it best to save to a user's computer that he or she is logged in? Do I simply give him a cookie logged_in and set the value to true? Do I save the ip of the "logged in computer" to the database and remember it that way. Many of these php/mysql scripts seem to use $_SESSION variables as well; do I need to use those somehow?

I realize this is a pretty vague question, I'm just sort of brainstorming exactly what I need to do.

Or, if anyone's aware of a good postgresql login script, I'd be happy to explore that as well ;)

Question by:hibbsusan
  • 5
  • 2
  • 2

Author Comment

ID: 37712405
Or, since I have my users table in postgresql and there are plenty of good php/mysql login  scripts out there, is there some way I can just mirror that users table in mysql so I can use one of the open source login scripts, accessing the users through mysql..?

Expert Comment

by:Patrick Tallarico
ID: 37712430
You should be able to modify the login script of your choice to look at the postgresql database rather than a mysql db.  The php functions for postgresql are for the most part the same as mysql, with the exception that they are prefixed with 'pg_...' rather than 'mysql_...'
You would also have to check the syntax on the mysql queries just to make sure that the slight differences in syntax are accounted for.

As for synchronizing between pg, and mysql, I have used Php to synchronize data between mssql and mysql.  Pull the data from one server into an array, and loop through the rows, creating insert queries, or importing the array into a temp table and running an update to another local table...

If you attach a sample of code that makes sense to you, I'm sure we can work it to fit your needs.

Author Comment

ID: 37712452
I have used this login script before, but I don't know if updating it will be as simple for me. It uses a lot of php classes that I don't fully understand the functionality of. So I don't know if I can just do a replace all mysql¿pg.

I think the way php connects to the database differs as well. Maybe you can take a look and let me know how feasible you think it will be... :)

Thanks for your help!

Author Comment

ID: 37712469
that link was incorrect. This is the right one:
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

LVL 108

Accepted Solution

Ray Paseur earned 500 total points
ID: 37712677
This article describes the design pattern.  It should be readily adaptable for PostGreSQL.

After you have read it over please post back with any specific questions.  Best, ~Ray

Expert Comment

by:Patrick Tallarico
ID: 37715513
There is only one file that has actual database calls in it.   Tha is the file includes/fg_membersite.php

In there there are a few different mysql functions being used, all of which have a direct correlating function in pg.  The one that is most different is the mysql_connect, and mysql_select_db functions in the php function DBLogin().  All of the options that are handled in this php function can be handled in the pg_connect() function.  pg_connect() uses a single connection string argument rather than the three arguments for the mysql_connect().
what you would look to use would be something like:
pg_connect("host=localhost port=5432 dbname=test user=name password=pass options='--client_encoding=UTF8'")
This would actually take care of all of DBLogin() options in one single call.

The rest of the file has a few more mysql calls, they relate to pg as follows:

mysql_query($query,$connection)  ==> pg_query($connection,$query)
mysql_num_rows($result)  ==>  pg_num_rows($result)
mysql_fetch_assoc($result) ==>  pg_fetch_assoc($result)
mysql_real_escape_string($string) ==>  pg_escape_string($string)
mysql_error()  ==>  pg_last_error($connection)

Note that the query arguments are reversed in order from one to the other.  Also I am not sure that the pg_last_error will be exactly the same as the mysql_error in what it returns, but it will give you something close enough that the program should work.

By adjusting this file, you should be able to use this same login program with a pg database as you would have with a mysql.  Note you will still need to adjust the membersite_config.php and adjust the requisite user table in pg.  In terms of feasibility, I think it shouldn't be too difficult.

There are a lot of functions that this script creates and uses.  Unfortunately I don't have the time to go over them with a fine tooth comb, but it seems to be pretty well designed and organized.  

If you are unsure of what all is going on with this program you may wish to go with something a bit simpler, or at least something that you write yourself.  There are some decent tutorials out there.  A good reference is the article that Ray_Paseur posted. You may be able to utilize that to create something for yourself so that you have exactly what you want rather than this other code.

Author Comment

ID: 37720159
Mr Paseur,
I think this article is really great. I am still working through it. Thank you so much for writing it and pointing me to it.

Author Closing Comment

ID: 37825197
Hi Ray,

I was daunted by making this work for postgresql for quite a while, but I finally devoted some time, and it is now working very well.

LVL 108

Expert Comment

by:Ray Paseur
ID: 37825597
Thanks for the points.  I've done a little work with PostGreSQL and found it to "be SQL" in much the same way that other SQL data bases are alike.  What I like better about MySQL is the more robust collection of helpers.  phpMyAdmin is much better than phpPGAdmin (or whatever it's called nowadays).  But the principles of SQL seem to come through.  I'm glad they came through for you!  All the best, ~Ray

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now