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

[FragglePete]

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...

$selecteddate = $_POST['dbDate'];

 

And the MySQL Query string is being set as follows...:

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

 

This shows no results within the phpgrid element. However, if I change the variable to:

$selecteddate = date("Y-m-d");

or even

$selecteddate = "2013-05-30";

Then it works! I've even put this is the query string itself just to confirm it works...

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

[jaminben]

Not sure if I remember correctly but try something like:

 

 

$date = $_POST['dbDate'];[font=Verdana]
[/font][color=#333333]$selecteddate = [/color]STR_TO_DATE('$[font=Verdana]date[/font]', '%Y-%m-%d');

Edited May 29, 2013 by jaminben

[Meldrew]

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

[FragglePete]

Not sure if I remember correctly but try something like:

 

 

$date = $_POST['dbDate'];[font=Verdana]
[/font][color=#333333]$selecteddate = [/color]STR_TO_DATE('$[font=Verdana]date[/font]', '%Y/%m/%d');

 

Thanks. The STR_TO_DATE bit only works in the MySQL Statement, and for me it doesn't! So I tried...

 

$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....

$selecteddate = "2013-05-30";

 

Grrrr....

[FragglePete]

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

[Marci]

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...

 

$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?

Edited May 29, 2013 by Marci

[Marci]

You could ensure it goes thru as string by casting it...

 

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

[FragglePete]

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...

 

$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:

SUBMITTED DATE: 2013-05-30
       SELECT * FROM tbldetentions WHERE DateSetFor = '2013-05-30'

 

The Select Command is being formed as such:

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

 

So, I've echo'd the $h->select_command

 

??

 

Pete

[FragglePete]

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

[Marci]

Try casting it as my post directly above your last... if that doesn't work, try flipping your quotes...

 

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

[FragglePete]

You could ensure it goes thru as string by casting it...

 

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

 

Tried that, same result.. WTF?

 

Pete

[FragglePete]

Try casting it as my post directly above your last... if that doesn't work, try flipping your quotes...

 

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

 

Nope

 

Pete

[jaminben]

Just throwing out an idea here...

 

My URL is:

 

[url="http://server2008/application_form/xx.php?myDate=2013-05-06"]http://someaddress/application_form/xx.php?myDate=2013-05-06[/url]

 

My php code is like:

 

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.

Edited May 29, 2013 by jaminben

[Marci]

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?

[FragglePete]

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

[ZeroHour]

I have never used phpgrid but the next step is to check the actual function which is taking your sql input provided by them using exit's and break's to output whats going on.

Its possible there is some checks to prevent sql injection etc and its automatically filtering your string.

Also what version of php are you running?

[FragglePete]

Ok - Firstly, thanks for you all your help people. I'll be hitting that 'Thank Post' button alot.

 

I got in contact with the developer, and got a response that if using $_POST I need to set a session variable and use that. It seems to work now.

 

Really like this phpgrid, so much so I brought a license for the school for what I'm doing as it just makes things so easy.

 

Cheers all

 

Pete

[S.C.]

Have you got PHPmyAdmin running on your webserver? A handy little way to debug your query is to echo back your query and copy and paste it into SQL tab of the database table (supportstaff_section1_b) in question. I've solved half a dozen issues like that. I'd also be tempted to echo something from your phpConnection.php page just to make sure that connection is working. Quince

[thesk8rjesus]

Hi not sure if you got this sorted already but hopefully this code will help

 

Object oriented

$date = new datetime( $_POST['dbDate'] );
$selecteddate = $date->format( 'Y-m-d' );

 

Procedural

$date = date_create( $_POST['dbDate'] );
$selecteddate = date_format( $date, 'Y-m-d' );

 

Also with your query you could write the follow as the variable will work in the double quotations.

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