Re: [PHP] Avoid to open mysql querries then times in the page

[Michael A. Peters]

tedd wrote:

On 5/4/09, Matthieu spama...@gmail.com wrote:

 Hello,

 I'm a totally newbie to php/Mysql but I'd like to know if it is 
normal that

 I have to connect 3 times to the db in one page.

 For example, I have

 1. A connection for the login / pass a $_SESSION['login'] before the 
HTML

 tags

 2. I need to say hello to the user so I reconnect, run a query to 
select the

 user having the login and echo 'Hello '.$user['login'].'!''

 3. I need to show him his friends a bit later, so I have to connect a 
last
 time and re-run a querry because I can't use the data $user that I 
used in

 my upper php code...


 Is there a walkthroug to have only one connection for the page?

 Thanks

 Matthieu



Matthieu:

The way I usually have a user navigate a protected site is to first to 
have them identify themselves via a logon/password script -- and then I 
store their user_id in a SESSION. Note, I do not store all their data in 
a SESSION, just their user_id. The user_id should be an unique 
auto_increment integer primary key from your users' table.


At the start of each protected page, I have:

?php session_start();

$user_id = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : 0;

if($user_id == 0)
   {
   header('location: login.php);
   exit();
   }

// proceed with processing

As such, I check if $user_id  0 -- if so, then I process the request as 
directed. If not, then I send the user back to login.


That's basically what i do.
I don't store much in sessions, just the id of the logged in user (set 
to 0 for not logged in) and maybe a few temporary things (IE a page that 
requires login, if the uid is set to 0 I'll store the page in the 
session so that after login they can be redirected back). There's a few 
other things I do in session data, but not much.


Since I only use non persistent cookies for security reasons, almost 
anything worth saving is worth saving as a db record tied to the user 
id. Sessions for me mostly are just a way to know a user is 
authenticated and who they are authenticated as.




As for connecting to the database, I connect as needed to get 
information needed. I do not use SESSIONs to store all the data to be 
passed from page to page, I gather only what's needed for that page.


I also make sure that when I open a connection, I close the connection I 
may have several open/close statements within a page, but normally I try 
to avoid that.


I just use pear mdb2 - they make it easy to deal with multiple different 
databases etc. and I just let the connection close when the page 
finished executing, I don't explicitly close any connections.


I do explicitly unset prepared statements, but only on pages that do 
many queries (short fast pages free up the memory when the page finishes 
executing anyway).


Since I generally use the same database for session handling as I use 
for rest of the app, the database will be opened when the page starts 
and need to be open when the page finishes execution for writing any new 
session data, so it doesn't make sense to me to explicitly close the 
connection except for my search engine (it uses a different database) - 
but when the search query has run, the search results are displayed and 
the script finishes executing anyway, so closing that connection isn't 
needed anyway - the job is done and the script exits quickly closing the 
connection on it's own.




HTH's

tedd




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Avoid to open mysql querries then times in the page

[tedd]

On 5/4/09, Matthieu spama...@gmail.com wrote:

 Hello,

 I'm a totally newbie to php/Mysql but I'd like to know if it is normal that
 I have to connect 3 times to the db in one page.

 For example, I have

 1. A connection for the login / pass a $_SESSION['login'] before the HTML
 tags

 2. I need to say hello to the user so I reconnect, run a query to select the
 user having the login and echo 'Hello '.$user['login'].'!''

 3. I need to show him his friends a bit later, so I have to connect a last
 time and re-run a querry because I can't use the data $user that I used in
 my upper php code...


 Is there a walkthroug to have only one connection for the page?

 Thanks

 Matthieu



Matthieu:

The way I usually have a user navigate a protected site is to first 
to have them identify themselves via a logon/password script -- and 
then I store their user_id in a SESSION. Note, I do not store all 
their data in a SESSION, just their user_id. The user_id should be an 
unique auto_increment integer primary key from your users' table.


At the start of each protected page, I have:

?php session_start();

$user_id = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : 0;

if($user_id == 0)
   {
   header('location: login.php);
   exit();
   }

// proceed with processing

As such, I check if $user_id  0 -- if so, then I process the request 
as directed. If not, then I send the user back to login.


As for connecting to the database, I connect as needed to get 
information needed. I do not use SESSIONs to store all the data to be 
passed from page to page, I gather only what's needed for that page.


I also make sure that when I open a connection, I close the 
connection I may have several open/close statements within a page, 
but normally I try to avoid that.


HTH's

tedd

--
---
http://sperling.com  http://ancientstones.com  http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Avoid to open mysql querries then times in the page

[Daevid Vincent]

 

 -Original Message-
 From: tedd [mailto:tedd.sperl...@gmail.com] 
 Sent: Tuesday, May 05, 2009 7:55 AM
 To: php-general@lists.php.net
 Subject: Re: [PHP] Avoid to open mysql querries then times in the page
 
 On 5/4/09, Matthieu spama...@gmail.com wrote:
   Hello,
 
   I'm a totally newbie to php/Mysql but I'd like to know if 
 it is normal that
   I have to connect 3 times to the db in one page.
 
   For example, I have
 
   1. A connection for the login / pass a $_SESSION['login'] 
 before the HTML
   tags
 
   2. I need to say hello to the user so I reconnect, run a 
 query to select the
   user having the login and echo 'Hello '.$user['login'].'!''
 
   3. I need to show him his friends a bit later, so I have 
 to connect a last
   time and re-run a querry because I can't use the data 
 $user that I used in
   my upper php code...
 
 
   Is there a walkthroug to have only one connection for the page?
 
   Thanks
 
   Matthieu

I usually include a db.inc.php file that opens up a connection in the header
of the page and I continue to use the connection throughout till the end.

A basic example is do something like this:

//this will hold each db connection so we'll only create one at a time. like
a singleton.
$GLOBALS['DB_CONNECTIONS'] = array(); 

function connect_to_db_reliability() 
{
if ($GLOBALS['DB_CONNECTIONS']['reliability']) 
return $GLOBALS['DB_CONNECTIONS']['reliability'];

global $global_db_dsn_reliability;

$options = array(
'debug'   = 0,
'persistent'  = FALSE,
'portability' = DB_PORTABILITY_ALL
);

$db_connection = DB::connect($global_db_dsn_reliability, $options);
if ( PEAR::isError( $db_connection ) )
die( $db_connection-getMessage() );

$GLOBALS['DB_CONNECTIONS']['reliability'] = $db_connection;

$GLOBALS['DB_CONNECTIONS']['reliability']-setFetchMode(DB_FETCHMODE_ASSOC);

return $GLOBALS['DB_CONNECTIONS']['reliability'];
}

Then in all your code you can either do a simple call at the top of your
page:

$db_connection = connect_to_db_reliability();

and later on, you don't have to worry about it because you have already made
a connection.

$db_result = $db_connection-getAll($sql);

Be aware of scope and such, for example if you're inside a function or
method. But that's easy enough to handle since calling $db_connection =
connect_to_db_reliability(); will return you the same connection anyways, so
it's free to use wherever you are unsure.

This method is a fairly simple singleton like methodology that handles
multiple database connections (here at my job, we have NINE databases we
connect to at any given time.

In my other jobs/projects where I only have a single database (which is the
norm), I do this same sort of logic just not in a multi-dimensional array
and I have more wrapper functions that handle the connection if it doesn't
exist and do all sorts of useful things like colorized debug output,
backtraces. Search the PHP list archives for my posts and SQL_QUERY,
SQL_CONNECT, etc in a db.inc.php file I shared...

http://daevid.com


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] Avoid to open mysql querries then times in the page

[Daevid Vincent]

 

 -Original Message-
 From: tedd [mailto:tedd.sperl...@gmail.com] 
 Sent: Tuesday, May 05, 2009 7:55 AM
 To: php-general@lists.php.net
 Subject: Re: [PHP] Avoid to open mysql querries then times in the page
 
 On 5/4/09, Matthieu spama...@gmail.com wrote:
   Hello,
 
   I'm a totally newbie to php/Mysql but I'd like to know if 
 it is normal that
   I have to connect 3 times to the db in one page.
 
   For example, I have
 
   1. A connection for the login / pass a $_SESSION['login'] 
 before the HTML
   tags
 
   2. I need to say hello to the user so I reconnect, run a 
 query to select the
   user having the login and echo 'Hello '.$user['login'].'!''
 
   3. I need to show him his friends a bit later, so I have 
 to connect a last
   time and re-run a querry because I can't use the data 
 $user that I used in
   my upper php code...
 
 
   Is there a walkthroug to have only one connection for the page?
 
   Thanks
 
   Matthieu
 
 
 Matthieu:
 
 The way I usually have a user navigate a protected site is to first 
 to have them identify themselves via a logon/password script -- and 
 then I store their user_id in a SESSION. Note, I do not store all 
 their data in a SESSION, just their user_id. The user_id should be an 
 unique auto_increment integer primary key from your users' table.
 
 At the start of each protected page, I have:
 
 ?php session_start();
 
 $user_id = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : 0;
 
 if($user_id == 0)
 {
 header('location: login.php);
 exit();
 }
 
 // proceed with processing
 
 As such, I check if $user_id  0 -- if so, then I process the request 
 as directed. If not, then I send the user back to login.
 
 As for connecting to the database, I connect as needed to get 
 information needed. I do not use SESSIONs to store all the data to be 
 passed from page to page, I gather only what's needed for that page.
 
 I also make sure that when I open a connection, I close the 
 connection I may have several open/close statements within a page, 
 but normally I try to avoid that.
 
 HTH's
 
 tedd

I would disagree with this. I strongly believe you should use a
User.class.php and store that instance in the $_SESSION. This gives you the
ID, name, login name, login state, roles, and every other thing you could
want to know about a user on a given page in a handy to access object. In
fact, a User class is one of those things that really benefits from being
OOP.

The only trick here Matthieu is to make sure you
require_once('User.class.php') BEFORE you session_start(); or it won't work.

Opening and closing the connections is also IMHO a bad idea. The connection
is one of the slowest parts. In mySQL it's not so bad, but it's very narrow
sighted to think in terms of only one RDBMS. Other RDBMS like Oracle for
example have a very slow connection. Why not just keep it around. Your page
only lasts a few seconds and will automatically close when completed
anyways.

If you're writing a command line tool like a daemon, then that's another
story, but I doubt the OP is doing that.

Lastly, why all the extra variable, assignment and checking stuff here?

$user_id = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : 0;

Why not just this?

if( intval($_SESSION['user_id'])  1 ) { ... }

What I normally do is set a $_SESSION['login'] = true; 

A boolean is always faster to check and in your code you could do this:

if ( !$_SESSION['login'] ) { ... }

or if you're really paranoid use the '===':

if ( $_SESSION['login'] !== true ) { ... }


D.Vin
http://daevid.com





-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Avoid to open mysql querries then times in the page

[Matthieu]

Hello,

I'm a totally newbie to php/Mysql but I'd like to know if it is normal that 
I have to connect 3 times to the db in one page.


For example, I have

1. A connection for the login / pass a $_SESSION['login'] before the HTML 
tags


2. I need to say hello to the user so I reconnect, run a query to select the 
user having the login and echo 'Hello '.$user['login'].'!''


3. I need to show him his friends a bit later, so I have to connect a last 
time and re-run a querry because I can't use the data $user that I used in 
my upper php code...



Is there a walkthroug to have only one connection for the page?

Thanks

Matthieu 



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Avoid to open mysql querries then times in the page

[Bastien Koert]

Yep, though I would still query 2x. Once for the login data, no sense
retrieving more if the log on fails. Then the second query is to get
all the user data into a session object. There are two schools of
thought about this, pull all the required data into the session, which
doesn't scale well but if your site is not that busy, it won't matter
all that much. The other option is to query each time the user needs
some data and just keep the user_id in the session and run small quick
queried to get the data you need.

This is where you need to understand architectural trends. The REST
architecture is all about true stateless application design where the
app does not use sessions which allows for massive horizontal scaling.
The app could connect to any server in the environment for access to
the data and any server could deliver any data.  Many large scale
applications use this design to scale out. Facebook is one.

Be honest about how busy the site will be and use that as a guiding
principle, but build it in layers to make swapping out bits easier.

Hth



On 5/4/09, Matthieu spama...@gmail.com wrote:
 Hello,

 I'm a totally newbie to php/Mysql but I'd like to know if it is normal that
 I have to connect 3 times to the db in one page.

 For example, I have

 1. A connection for the login / pass a $_SESSION['login'] before the HTML
 tags

 2. I need to say hello to the user so I reconnect, run a query to select the
 user having the login and echo 'Hello '.$user['login'].'!''

 3. I need to show him his friends a bit later, so I have to connect a last
 time and re-run a querry because I can't use the data $user that I used in
 my upper php code...


 Is there a walkthroug to have only one connection for the page?

 Thanks

 Matthieu


 --
 PHP General Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php




-- 

Bastien

Cat, the other other white meat

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Avoid to open mysql querries then times in the page

[Ashley Sheridan]

On Mon, 2009-05-04 at 23:09 +0030, Bastien Koert wrote:
 Yep, though I would still query 2x. Once for the login data, no sense
 retrieving more if the log on fails. Then the second query is to get
 all the user data into a session object. There are two schools of
 thought about this, pull all the required data into the session, which
 doesn't scale well but if your site is not that busy, it won't matter
 all that much. The other option is to query each time the user needs
 some data and just keep the user_id in the session and run small quick
 queried to get the data you need.
 
 This is where you need to understand architectural trends. The REST
 architecture is all about true stateless application design where the
 app does not use sessions which allows for massive horizontal scaling.
 The app could connect to any server in the environment for access to
 the data and any server could deliver any data.  Many large scale
 applications use this design to scale out. Facebook is one.
 
 Be honest about how busy the site will be and use that as a guiding
 principle, but build it in layers to make swapping out bits easier.
 
 Hth
 
 
 
 On 5/4/09, Matthieu spama...@gmail.com wrote:
  Hello,
 
  I'm a totally newbie to php/Mysql but I'd like to know if it is normal that
  I have to connect 3 times to the db in one page.
 
  For example, I have
 
  1. A connection for the login / pass a $_SESSION['login'] before the HTML
  tags
 
  2. I need to say hello to the user so I reconnect, run a query to select the
  user having the login and echo 'Hello '.$user['login'].'!''
 
  3. I need to show him his friends a bit later, so I have to connect a last
  time and re-run a querry because I can't use the data $user that I used in
  my upper php code...
 
 
  Is there a walkthroug to have only one connection for the page?
 
  Thanks
 
  Matthieu
 
 
  --
  PHP General Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php
 
 
 
 
 -- 
 
 Bastien
 
 Cat, the other other white meat
 
I'd do it with 2 queries too, but I'd pull the name from the DB as
you're checking their credentials. Basically, just query the database
for their username and password, but alter the SELECT statement to pull
their name along with any other details you need. Later, pull the list
of friends as you need them. I just think this is a bit neater, as you
only need their name once, in one row of data, but I'm guessing the
friends list would pull more than one row, and it wouldn't make sense to
have the name of the person who just logged in in each row, repeated.


Ash
www.ashleysheridan.co.uk


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php