SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    My 1st and complicated Php/mysql script (help please)

    Hello,

    I would appreciate some help with my very first php script please. :-)

    What I'm trying to do is display 3 messages, sequentially, in a private "members only" portion of my site.

    The messages would be displayed as follows after a member logs in:
    Message 1 = "Welcome ...." (displayed instantly)
    Message 2 = "Blah, blah, blah" (displayed 1 day/24 hours later)
    Message 3 = "Blah3, blah3, blah3" (displayed 2 days later/48 hours)

    The messages would include the first name of the member (getting it from mysql registration table), then the plain text message.

    Here's my preliminary script - let me know what's "wrong" or how I could do it better (pretty please?):
    Code:
    <head>
    <title>Your Daily Message Center</title>
    </head>
    <body>
    
    <? php // Connect to the database
    $dbcnx = @mysql_connect('localhost', 'username', 'password');
     if (!dbcnx) {
     exit ('<p>Unable to connect at this time. Try again later.</p>');
    }
    //Select clients database
     if (!@mysql_select_db('clientsdb')) {
     exit('<p>Unable to locate you in database at this time.</p>');
    }
    // Get Members First Name
     $result = @mysql_query('SELECT First_Name FROM clientsdb');
     if (!$result) {
     exit ('<p> Error performing query: ' . mysql_error() . ' </p>');
    }
    // Display Members First Name in a paragraph
     while ($row = myssql_fetch_array($result)) {
     ech '<p Dear ' . $row['First_Name'] . ' </p>';
    }
    
    //HERE IS WHERE I'M STUMPED: Displaying the SEQUENTIAL MESSAGES:
    
     $time_today = date( );
     $time_purchased =  // HOW DO I GET FROM DATABASE???
    
     $result = @mysql_query('SELCT orderdate FROM clientdb');
     if (!$result){
     exit ('<p>Error: '!mysql_error () . ' </p>');
    }
    
     $time_difference=time_difference_function($time_purchased, $time_today);
     if ($time_diff_hours < 24) 
     {
     echo '<p>Welcome Message Here.</p'>; // Displays Immediate Message
    }
    
     if ($time)diff_hours>24 && $time_diff_hours < 48)
     {
       echo '<p>2nd Day Message.</p'>; // Displays 24 hours later
    }
     
     if ($time_diff_hours > 48)
     {
      echo '<p>3rd Day Message.</p'>; // Displays 48 hours later
    }
    ?>
    </body>
    </html>
    Thanks to all in advance! Much credit goes to Kevin Yank, SitePoints' own! For the neat little book "Build Your Own Database Driven Website..." and to forums like this one for helping the small fry out like moi.

    Kaitlyn

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have some typos in your script. Probably best to start out there
    Need any video services done? PM me
    Watermarking, Thumbnails, Video Content Submission,
    Encoding videos including Flash (.flv).

  3. #3
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    your going to need a field in your db that stored the time they placed the order. store it as a unix timestamp.

    your also going to need a way to make sure your only working with 1 users order, and maybe also account for the possibility they may make a second order a few hours/days later. but that is a bit offtopic right now, so i will focus on your question.
    im just going to assume you have a unique order_id field to use to identify which order your working with.

    PHP Code:

    <?php

    $sql 
    "SELECT `orderdate` FROM `clientdb` WHERE `order_id` = '$order_id'";
    $result mysql_query($sql) or die(mysql_error());

    $row mysql_fetch_assoc($result);
    $ordertime $row['ordertime'];

    // get the current timestamp
    $now time();

    // find our how many seconds its been since they placed the order
    $elapsed $now $ordertime;

    // 1 day is 86400 seconds

    if ($elapsed 86400) {
        
    // first day msg
    } elseif ($elapsed 86400 2) {
        
    // 2 day msg
    } elseif($elapsed 86400 3) {
        
    // 3 day msg
    } else {
        
    // its been longer than 3 days, 
        // maybe show another message if you want to or whatever
    }

    ?>

  4. #4
    SitePoint Member
    Join Date
    May 2006
    Location
    Earth/Europe/Poland/Gdansk
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Avoid multiple SQL queries for simple things - for a start ;)

    Think of the whole information set you want to get from your DB, first, and then process it with the script. For example...
    Let's say you keep following tables in your db:
    • 'books' having id, author, name fields
    • 'users' having id, username, login fields
    • 'purchases' having fields like id, book_id, user_id, datetime


    To retrieve a list of purchased books for specific user you have to query
    Code:
    "SELECT * FROM `purchases` WHERE user_id=x"
    ; right?

    You shall receive a list of book_ids and purchase dates, then...

    ...to get book details (all in single query pass) you actually want to modify the SQL query to something like this:
    Code:
    "SELECT p.*, b.name, b.author FROM purchases p, books b WHERE p.user_id=x AND p.book_id=b.id ORDER by p.datetime DESC, b.name ASC";
    which will produce a recordset with purchase_id, date, book name and author for desired user id...

    ...then - playing with date and time will be much easier

    TIP1 - try to keep php and html as much separate as possible from the very beginning of your coding, if it really has to be in the same file - put all HTML in the end and only feed with <?=$variables;?>

    TIP2 - DO watch your code - it is not going towork with these PARSE errors (comas, quotes, brackets etcetera - it all matters

    did you know it was my first post on this list, ever?
    cheers, Adam

  5. #5
    SitePoint Member
    Join Date
    May 2006
    Location
    Earth/Europe/Poland/Gdansk
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, that took me so long to write?...
    Well, just stick to whatever clamcrusher was about ;D

    Adam


Bookmarks

Posting Permissions

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