Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2005
    Location
    Ontario, Canada
    Posts
    1,750

    Wanted Tips for PHP + mySQL

    I have a bunch of content that is stored in a mySQL db. When someone views a pages on my website, I do the following:

    * test if user is logged in
    * get user information
    * figure out what files user has access to
    * get file detail info
    * calculate a bunch of stuff based on earlier db selections

    This happens every time a page is viewed. The problem that I see here is that if a lot of people are accessing my website all at the same time then my db is going to get swamped with query's.

    What can I do so that I can query the db for information the first time that the user enters my website, but for all subsequent pages, they visit, I don't need to get the information from the database, instead I can just retreive it from memory somewhere?!

    I'm using PHP for my site.
    C++, 3D OpenGL and Game Programming video tutorials:
    www.MarekKnows.com
    Play my free games: Ghost Toast, Zing, Jewel Thief

  2. #2
    Join Date
    Mar 2008
    Posts
    394
    Quote Originally Posted by mmakrzem View Post
    I have a bunch of content that is stored in a mySQL db. When someone views a pages on my website, I do the following:

    * test if user is logged in
    * get user information
    * figure out what files user has access to
    * get file detail info
    * calculate a bunch of stuff based on earlier db selections

    This happens every time a page is viewed. The problem that I see here is that if a lot of people are accessing my website all at the same time then my db is going to get swamped with query's.

    What can I do so that I can query the db for information the first time that the user enters my website, but for all subsequent pages, they visit, I don't need to get the information from the database, instead I can just retreive it from memory somewhere?!

    I'm using PHP for my site.
    Can't you store this sort of information in a session or a cookie? (A lot of users have cookies disabled, though). So, you load in the info from the database once, and store it in a session. You'll have to look it up, I don't know the syntax or anything :P

  3. #3
    Join Date
    Feb 2007
    Location
    /home/dilibau/
    Posts
    4
    yeah... you could store your data in the $_SESSION variable but what`s the point in that?
    you`d be loading to much data in the memory...
    just don`t use cookies... you`ll give the use the ability to edit the data which in most cases is not desirable

  4. #4
    Join Date
    Mar 2005
    Location
    Ontario, Canada
    Posts
    1,750
    So if I shouldn't use sessions or cookies, are there any other options?
    C++, 3D OpenGL and Game Programming video tutorials:
    www.MarekKnows.com
    Play my free games: Ghost Toast, Zing, Jewel Thief

  5. #5
    Join Date
    Feb 2007
    Location
    /home/dilibau/
    Posts
    4
    you should use sessions and database but strong checks are recommended because even sessions are based on a cookie and you should check the users browser, ip or anything else
    Last edited by DiliBau; 08-28-2008 at 08:17 AM.

  6. #6
    Join Date
    Mar 2006
    Location
    South Australia
    Posts
    4,521
    This is how I deal with sessions.

    Generate a session id. There is a PHP function to do something (get unique ID). I then pass this through MD5. The result gets stored as a session variable/cookie.

    Any other data I want to store goes into a database. I have a sessions table:

    Code:
    |           sessions             |
    |--------------------------------|
    | SessionID | Data | expire_time |
    |                                |
    |--------------------------------|
    Data is usually a serialized array. This works well because I can store as much information as I want without having to have any of it on the client side. It also means I can access this session data from a single database lookup.

  7. #7
    Join Date
    Apr 2004
    Location
    Nr London, UK
    Posts
    831
    supprised i haven't replied to this one yet!

    Take a look at this:

    http://www.cl.cam.ac.uk/~sjm217/pape...s08cookies.pdf

    Its a bit of a combination of secure cookies could see you good.

    Yes, you will need to lookup to see if the user is valid...but with a bit of reverse encrpyted cookies or maybe a sceme whereby you use an octalesque method of assigning permissions such that:

    for (execute,write,read,edit perms,delete);

    x,w,r,e,d
    1,2,4,8,16

    such that if a user has 20, they can read and delete, but not write (not sure thats useful, but neither are some combinations of the real octal system!)

    may work, its late so probably not thinking, this can be incorperated into the cookie (securely that is - mcrypt). Maybe add a hash to the cookie too...

    The overheads of which, could be slower, but it depends - its something i intend to try

    Given the complexity of an unfriendly source being able to generate valid cookies of their own without alot of information, and a easy password, and having access to the database contents (all of which occuring together is unlikely) - we now have a nice way of assigning perms.

    obviously how you organise files is totally upto you - we do not know what you are trying to achivev here!

    Are files based on groups - and users are members of groups etc.

    Anyhow...i think i need sleep so am off to bed

    oh - in addition to what michael said - combine any session generating id's with regenerating the id as soon as they login successfully...this avoids at least fixed session identifiers with people being duped into using a pre-determined session id and thus resulting in a stolen session:

    http://uk.php.net/session_regenerate_id

    http://en.wikipedia.org/wiki/Session_fixation

    Also, if a user goes to an administritive area of the site, its good practice to make them re-login to access it...similar to creating REALMS to your site.

    Also see another post here to save me writing it out again : maybe look at the rest of the thread too:

    http://www.3dbuzz.com/vbforum/showpo...00&postcount=3
    Last edited by martinco; 08-28-2008 at 04:46 PM.

  8. #8
    Join Date
    Mar 2004
    Posts
    0
    SELECT * FROM users WHERE user_id = {$_SESSION["uid"]}
    if it returns a row:
    SELECT * FROM videos INNER JOIN permissions ON permission_user = {$_SESSION["uid"]} AND permission_video = video_id WHERE video_section = {$section_name} LIMIT 0, 30

    Here is a dump for a simple database that does this.
    Code:
    -- phpMyAdmin SQL Dump
    -- version 2.11.7
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Sep 16, 2008 at 09:28 PM
    -- Server version: 5.0.51
    -- PHP Version: 5.2.6
    
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    
    --
    -- Database: `test`
    --
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `permissions`
    --
    
    CREATE TABLE IF NOT EXISTS `permissions` (
      `permission_id` int(10) unsigned NOT NULL auto_increment,
      `permission_user` int(10) unsigned NOT NULL,
      `permission_video` int(10) unsigned NOT NULL,
      PRIMARY KEY  (`permission_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;
    
    --
    -- Dumping data for table `permissions`
    --
    
    INSERT INTO `permissions` (`permission_id`, `permission_user`, `permission_video`) VALUES
    (1, 1, 2),
    (2, 1, 4);
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `users`
    --
    
    CREATE TABLE IF NOT EXISTS `users` (
      `user_id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`user_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;
    
    --
    -- Dumping data for table `users`
    --
    
    INSERT INTO `users` (`user_id`) VALUES
    (1);
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `videos`
    --
    
    CREATE TABLE IF NOT EXISTS `videos` (
      `video_id` int(10) unsigned NOT NULL auto_increment,
      `video_section` int(10) unsigned NOT NULL,
      `video_name` varchar(64) collate latin1_general_ci NOT NULL,
      PRIMARY KEY  (`video_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;
    
    --
    -- Dumping data for table `videos`
    --
    
    INSERT INTO `videos` (`video_id`, `video_section`, `video_name`) VALUES
    (1, 1, 'Intro to C++ 1'),
    (2, 1, 'Intro to C++ 2'),
    (3, 1, 'Intro to C++ 3'),
    (4, 1, 'Intro to PHP 1');
    Now down to two queries What other queries are you worried about?

  9. #9
    Join Date
    Sep 2008
    Posts
    2
    Hi !

    i am getting problem to validate xhtml mp code created in php. I tried in dreamweaver8 but there i can validate only xhtml code NOT xhtml mp code .

    On top i must also check if that works with the dtd version.

    I tried to look for an addon that can put in dreamweaver but cannot find a means to add.

    Please can U help or sugguest me something?

    Aikon

  10. #10
    Join Date
    Dec 2008
    Posts
    2
    True, sessions are like cookies but stored on the server. I would say go with sessions. Once a user logs in, set their session information. I use PHP's stdClass to load a user object which contains their user credentials from the session or null values, depending on if their logged in or not. It's just easier to write $user->id instead of $_SESSION['userid'].

    As far as storing the session info in the database, that will just require extra queries that you don't want. Using sessions, you can eliminate the need to check so much, this is along the lines of what I do...

    Each page load a check is performed without any queries. I check if the session exists and assign the user object based upon that, cleaning it of coarse. If not, just load null values. I can check if a user is logged in now with ...

    if($user->id ) {
    // Do something
    }

    As far as what you are calculating from earlier db information and the files, I can't speak on because I don't know exactly what your doing and how your doing it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •