+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
Web Development Thread, Date? String? Pulling my hair out here! Help! in Coding and Web Development; Hi. I'm developing something at the moment which uses phpgrid to show data from a MySQL database depending on what ...
  1. #1

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    882
    Thank Post
    274
    Thanked 139 Times in 112 Posts
    Blog Entries
    27
    Rep Power
    42

    Date? String? Pulling my hair out here! Help!

    Hi.
    I'm developing something at the moment which uses phpgrid to show data from a MySQL database depending on what is selected, etc, etc.

    I'm setting the variable, like this as its being set from a form...
    Code:
    $selecteddate = $_POST['dbDate'];
    And the MySQL Query string is being set as follows...:
    Code:
    select_command = "SELECT * FROM tbldetentions WHERE DateSetFor = '". $selecteddate ."'";
    This shows no results within the phpgrid element. However, if I change the variable to:
    Code:
    $selecteddate = date("Y-m-d");
    or even
    Code:
    $selecteddate = "2013-05-30";
    Then it works! I've even put this is the query string itself just to confirm it works...
    Code:
    select_command = "SELECT * FROM tbldetentions WHERE DateSetFor = '2013-05-30'";
    I can echo this variable and it shows it as whatever the date has been set to, eg: 2013-05-30 but it just won't play ball. What am I missing here?

    Thanks

    Pete

  2. #2
    jaminben's Avatar
    Join Date
    Oct 2012
    Location
    Norfolk
    Posts
    254
    Thank Post
    50
    Thanked 19 Times in 18 Posts
    Rep Power
    7
    Not sure if I remember correctly but try something like:


    Code:
    $date = $_POST['dbDate'];
    $selecteddate = STR_TO_DATE('$date', '%Y-%m-%d');
    Last edited by jaminben; 29th May 2013 at 03:27 PM.

  3. Thanks to jaminben from:

    FragglePete (30th May 2013)

  4. #3

    Join Date
    Apr 2007
    Location
    Cornwall
    Posts
    268
    Thank Post
    16
    Thanked 74 Times in 46 Posts
    Rep Power
    36
    Hi,

    I'm no PHP developer, but as a thought, if you echo your select_command variable after setting it using your:

    select_command = "SELECT * FROM tbldetentions WHERE DateSetFor = '". $selecteddate ."'";

    What do you get compared to the test command you listed last:
    select_command = "SELECT * FROM tbldetentions WHERE DateSetFor = '2013-05-30'";

    My gut feeling is that this is a UK/US date formatting issue - caused my no end of problems until I made sure that dates are always specified in yyyy-mm-dd format.

    HTH,

    Meldrew

  5. Thanks to Meldrew from:

    FragglePete (30th May 2013)

  6. #4

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    882
    Thank Post
    274
    Thanked 139 Times in 112 Posts
    Blog Entries
    27
    Rep Power
    42
    Quote Originally Posted by jaminben View Post
    Not sure if I remember correctly but try something like:


    Code:
    $date = $_POST['dbDate'];
    $selecteddate = STR_TO_DATE('$date', '%Y/%m/%d');
    Thanks. The STR_TO_DATE bit only works in the MySQL Statement, and for me it doesn't! So I tried...

    Code:
    $datedate = $_POST['dbDate'];
    select_command = "SELECT * FROM tbldetentions WHERE DateSetFor = STR_TO_DATE('" . $datedate . "','%Y-%m-%d')";
    Still nothing. It's strange cause if I throw a string at it it will work, eg....
    Code:
    $selecteddate = "2013-05-30";
    Grrrr....

  7. #5

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    882
    Thank Post
    274
    Thanked 139 Times in 112 Posts
    Blog Entries
    27
    Rep Power
    42
    Quote Originally Posted by Meldrew View Post
    Hi,

    I'm no PHP developer, but as a thought, if you echo your select_command variable after setting it using your:

    select_command = "SELECT * FROM tbldetentions WHERE DateSetFor = '". $selecteddate ."'";

    What do you get compared to the test command you listed last:
    select_command = "SELECT * FROM tbldetentions WHERE DateSetFor = '2013-05-30'";

    My gut feeling is that this is a UK/US date formatting issue - caused my no end of problems until I made sure that dates are always specified in yyyy-mm-dd format.

    HTH,

    Meldrew
    I'm being quite specific on the date format keeping everything as yyyy-mm-dd so not sure that's the issue.

    Thanks

    Pete

  8. #6
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    893
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82
    Right, well step one would be to echo $_POST['dbDate'] and see what format the post data comes thru as...

    Does DateSetFor contain exactly the date, or does it contain a timestamp? Without seeing the data in the table, and the data going into the form, it's very hard to analyse...

    Code:
    $selecteddate = $_POST['dbDate'];
    echo 'SUBMITTED DATE: '.$selecteddate.'';
    $select_command = "SELECT * FROM tbldetentions WHERE DateSetFor = '". $selecteddate ."'";
    echo 'SQL COMMAND: '.$select_command.'';
    Change your code to the above, submit some data, copy and paste the two echo'd lines (ensure it's all getting stuck together correctly).

    Also, what data type is your DateSetFor column?
    Last edited by Marci; 29th May 2013 at 03:47 PM.

  9. Thanks to Marci from:

    FragglePete (30th May 2013)

  10. #7
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    893
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82
    You could ensure it goes thru as string by casting it...

    Code:
    $selecteddate = (string)$_POST['dbDate'];

  11. #8

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    882
    Thank Post
    274
    Thanked 139 Times in 112 Posts
    Blog Entries
    27
    Rep Power
    42
    Quote Originally Posted by Marci View Post
    Right, well step one would be to echo $_POST['dbDate'] and see what format the post data comes thru as...

    Does DateSetFor contain exactly the date, or does it contain a timestamp? Without seeing the data in the table, and the data going into the form, it's very hard to analyse...

    Code:
    $selecteddate = $_POST['dbDate'];
    echo 'SUBMITTED DATE: '.$selecteddate.'';
    $select_command = "SELECT * FROM tbldetentions WHERE DateSetFor = '". $selecteddate ."'";
    echo 'SQL COMMAND: '.$select_command.'';
    Change your code to the above, submit some data, copy and paste the two echo'd lines (ensure it's all getting stuck together correctly).
    Output shows:
    Code:
    SUBMITTED DATE: 2013-05-30
            SELECT * FROM tbldetentions WHERE DateSetFor = '2013-05-30'
    The Select Command is being formed as such:
    Code:
    $h->select_command = "SELECT * FROM tbldetentions WHERE DateSetFor = '" . $selecteddate ."'";
    So, I've echo'd the $h->select_command

    ??

    Pete

  12. #9

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    882
    Thank Post
    274
    Thanked 139 Times in 112 Posts
    Blog Entries
    27
    Rep Power
    42
    This gotta be an issue with the phpgrid element, as when I just set the variable as '$selecteddate = "2013-05-30";' the output of the above is the same and the grid is correctly filled.

    I am at a loss here... !

    Pete

  13. #10
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    893
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82
    Try casting it as my post directly above your last... if that doesn't work, try flipping your quotes...

    Code:
    $h->select_command='SELECT * FROM tbldetentions WHERE DateSetFor="'.$selecteddate.'"';

  14. #11

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    882
    Thank Post
    274
    Thanked 139 Times in 112 Posts
    Blog Entries
    27
    Rep Power
    42
    Quote Originally Posted by Marci View Post
    You could ensure it goes thru as string by casting it...

    Code:
    $selecteddate = (string)$_POST['dbDate'];
    Tried that, same result.. WTF?

    Pete

  15. #12

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    882
    Thank Post
    274
    Thanked 139 Times in 112 Posts
    Blog Entries
    27
    Rep Power
    42
    Quote Originally Posted by Marci View Post
    Try casting it as my post directly above your last... if that doesn't work, try flipping your quotes...

    Code:
    $h->select_command='SELECT * FROM tbldetentions WHERE DateSetFor="'.$selecteddate.'"';
    Nope

    Pete

  16. #13
    jaminben's Avatar
    Join Date
    Oct 2012
    Location
    Norfolk
    Posts
    254
    Thank Post
    50
    Thanked 19 Times in 18 Posts
    Rep Power
    7
    Just throwing out an idea here...

    My URL is:

    My php code is like:

    Code:
    require 'php/phpConnection.php';
    
    
    $date = $_GET['myDate'];
    
    
    $sqlString = "SELECT ID FROM `supportstaff_section1_b` WHERE DateBirth = '$date'";
    
    
    $sqlQuery = mysql_query($sqlString);
    
    
    $appliationsRows = array();
        while ($r = mysql_fetch_assoc($sqlQuery)){
                $appliationsRows[] = $r;
        }
    
    
    mysql_close($con);
    
    
    print json_encode($appliationsRows);
    I know its not exactly what your after but it works.

    $_GET gets the value of the variable from the url.
    $_POST handles values from the html
    .

    EDIT

    My bad... your using a form.. you can ignore this.
    Last edited by jaminben; 29th May 2013 at 04:01 PM.

  17. Thanks to jaminben from:

    FragglePete (30th May 2013)

  18. #14
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    893
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82
    I'm lost then. Am looking at PHPGrid docs, no mention of a select_command function, and the query is set when you instantiate PHPGrid...

    What version are you using?

  19. #15

    Join Date
    Feb 2008
    Location
    Wiltshire
    Posts
    882
    Thank Post
    274
    Thanked 139 Times in 112 Posts
    Blog Entries
    27
    Rep Power
    42
    Quote Originally Posted by Marci View Post
    I'm lost then. Am looking at PHPGrid docs, no mention of a select_command function, and the query is set when you instantiate PHPGrid...

    What version are you using?
    http://www.phpgrid.org/docs/ - Down the page to the 'Custom SQL Query' section.

    I've got it running queries against other things quite happily, just don't want to work as soon as I use the $_POST element. I've got it working against a $_SESSION variable in another grid.

    Really appreciate everyones help, I shall spread the Thanks around later!

    Pete

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. Registry Edit Problem, ripping my hair out here!!
    By phillip_croxford in forum Windows 7
    Replies: 4
    Last Post: 21st September 2012, 02:19 PM
  2. FTP - Pulling my Hair Out!
    By techyphil in forum Internet Related/Filtering/Firewall
    Replies: 17
    Last Post: 20th May 2011, 12:21 PM
  3. Pulling my hair out - cannot access port on server
    By JPS in forum Internet Related/Filtering/Firewall
    Replies: 8
    Last Post: 14th December 2010, 04:33 PM
  4. Frog - Slow ! Pulling my hair out
    By FragglePete in forum Virtual Learning Platforms
    Replies: 21
    Last Post: 20th September 2010, 07:58 PM
  5. Help needed (before I pull my hair out :) )
    By Talorin in forum General Chat
    Replies: 13
    Last Post: 29th June 2009, 09:26 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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