SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 47 of 47

Thread: Subtract Dates

  1. #26
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup Bingo!

    It returned just three results
    Is it possible to round the difference of the two dates? - Would this be a good idea?

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    it's already rounded -- down

    integer division occurs when you divide one integer by another integer

    the answer is an integer

    do 14764 / 365 on a calculator... throw away the decimal portion... that's integer division

    neat, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It Works!!!

    But now I have another problem.
    How do I sort the username's alphabetically, upon clicking on a link?

    Cheers for your help, you doing great!

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by F.Danials View Post
    How do I sort the username's alphabetically, upon clicking on a link?
    the "clicking on a link" part will have to be done with html and php

    sorting in SQL is done with the ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So the link will be something like the following?:

    Code:
    echo '<a href="">Sort by First Name<a>';
    echo '<a href="">Sort by Last Name<a>';
    How do I get the link to carry out a query, upon being clicked?
    Last edited by F.Danials; Jul 15, 2008 at 04:38.

  6. #31
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm Stuck

  7. #32
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    wish i could help you, but i don't do php

    notice, though, that a link like this won't actually do anything --
    <a href="">Sort by First Name<a>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #33
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    This should get you going...

    PHP Code:
    <?php

    //--> Your regular SQL statement
    $sSQL 'SELECT * FROM member WHERE DATEDIFF(CURRENT_DATE,DOB)/365 BETWEEN 18 AND 27';

    //--> We'll try and detect from the URL if we wish the results sorted.
    if( isset($_GET['sortBy']) )
    {
        switch (
    $_GET['sortBy'])
        {
            case 
    'username':
                
    //--> Adding this to the end of the SQL statement.
                
    $sSQL .= ' ORDER BY username ASC;';
            break;
            
            case 
    'id':
                
    //--> Adding this to the end of the SQL statement.
                
    $sSQL .= ' ORDER BY id ASC;';
            break;
                    
            default:
                
    //--> Adding this to the end of the SQL statement because nothing else previous was found
                
    $sSQL .= ' ORDER BY username DESC;';
            break;
        }
    }

    //--> Prints out the link to sort by username.
    echo sprintf('<a href="pagename.php?sortBy=username">Sort By Username</a><br />');

    //--> Prints out the link to sort by id.
    echo sprintf('<a href="pagename.php?sortBy=id">Sort By Username</a><br />');

    ?>

  9. #34
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK. Cheers.
    But I have one more question for ya:
    I'm trying to store a six figure currency (500000) in the database, but each time I enter the figure, the database stores 999.99. Why is this?

    Code:
    $sql = "CREATE TABLE cars 
    (
      price decimal(5,2) NOT NULL default '00000.00'
    )";

  10. #35
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    AFAIK, it's technically better to put the price as an integer, in pence/cents. I.e. $500,000 == 50,000,000 cents.

    But your problem is because the 5 relates to the amount of digits in the whole figure, i.e. 999.99 has 5 digits.

    So to put in 500,000.00, you'd need DECIMAL(8,2)
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  11. #36
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Final thing, how do I create a random number, that is always 6 digits in length? - Obviously, I need to use the rand function, but how do I set the length to always be 10 digits?

    Thanks for all ya help. You've been excellent!

  12. #37
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    rand(100000, 999999)

    Or, if you want it to be able to have less than 100,000 but have leading Zeros, you can use sprintf():
    PHP Code:
    <?php
    $rand 
    rand(0999999);
    $rand sprintf('%06d'$rand);
    ?>
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  13. #38
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Or if you meant with MySQL:
    Code:
    SELECT FLOOR(RAND() * 1000000);
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  14. #39
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No the PHP Code version is fine.

    If the user clicks a link to sort the data by a certain field in ascending order, is it possible to convert the link so that it then sorts the data in descending order, if clicked a 2nd time?

  15. #40
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yeah.

    Code php:
    <?php
    $allowedlist = array('username', 'id');
    $directionlist = array('asc', 'desc');
    $sort = (isset($_GET['sortBy']) && in_array($_GET['sortBy'], $allowedlist)) ? $_GET['sortBy'] : 'username';
    $order = (isset($_GET['order']) && in_array(strtolower($_GET['order']), $directionlist)) ? $_GET['order'] : 'asc';
    $order = strtoupper($order);
    $sSQL = "SELECT * FROM member WHERE DATEDIFF(CURRENT_DATE,DOB)/365 BETWEEN 18 AND 27 ORDER BY {$sort} {$order}";
    $flag = (($sortBy == 'username' && $order == 'asc') ? true : false);
    printf('<a href="pagename.php?sortBy=username&order=%s">Sort By Username %s</a><br />', ($flag ? 'desc' : 'asc'), ($flag ? '/\\' : '\\/'));
    $flag = (($sortBy == 'id' && $order == 'asc') ? true : false);
    printf('<a href="pagename.php?sortBy=id&order=%s">Sort By ID %s</a><br />', ($flag ? 'desc' : 'asc'), ($flag ? '/\\' : '\\/'));
    ?>
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  16. #41
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quick question, what's wrong with this?:

    Code:
    onClick="window.open("view.php?name=$row['name']",'mywindow','width=400,height=200')"
    I get the following error:

    Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' in F:\php\htdocs\display.php on line 28

  17. #42
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    What's the whole line?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  18. #43
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    echo "<td><img src='".$row['image]."' onClick="window.open("view.php?name=$row['name']",'mywindow','width=400,height=200')"></td>";

  19. #44
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    echo <<<IMAGE
    <td><img src="{$row['image']}" onClick="window.open('view.php?name={$row['name']}','mywindow','width=400,height=200');"></td>
    IMAGE; 
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  20. #45
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What does "<<<IMAGE" do?

    I know get the following error message:

    Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in F:\php\htdocs\display.php on line 32

    Code:
    echo <<<IMAGE
    
    <td><img src="{$row['image']}" onClick="window.open('view.php?name={$row['name']}','mywindow','width=400,height=200');"></td>
    IMAGE; 
    
      echo "<td>" . $row['DOB'] . "</td>"; //This is line 32

  21. #46
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ?

  22. #47
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


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
  •