Updating Date Input Calendar to display data from MSSQL database

multichild · April 30, 2012, 4:03pm

Hi all,

I’m developing a small CMS using PHP & MSSQL, i know its not the best idea but its what i got to do I’m affraid.

As part of the admin the client needs to use a date input calendar which I got from here - http://www.dynamicdrive.com/dynamicindex7/jasoncalendar.htm

Its a jscript calendar and what Im trying to do is when it comes to edit each contract they may need to change these dates, so I would like for the date calendar to show the date already in the calendar.

I’ve got this far


if(isset($_GET['cNumber']))
{
	$queryEdit="select * from CSF_Intranet where Contract_Number=$customer_Number";
	$rowsEdit = sqlsrv_query($conn, $queryEdit);
	while ($rows = sqlsrv_fetch_array($rowsEdit, SQLSRV_FETCH_ASSOC)) {
	$cStart=$rows["Contract_Start"];

And then this is the bit which then displays the date, and then if possible the client can change it too.


<script type="text/javascript">DateInput('dateoriginal', true<?php if(isset($cStart)){if (!empty($cStart)){echo ",'YYYY-MM-DD', '$cStart'";}}?>)</script>

But unfortunately as always it isnt working, its not even letting the rest of the page display so I’m doing something wrong, and I’m guessing its where I am converting the date to the variable $cStart as below:


$cStart=$rows["Contract_Start"];

Can anybody help me with this.

JeremyC · May 1, 2012, 4:32pm

What is the value of $rows[‘Contract_Start’] when it is pulled from the database? Does it have to be formatted to the correct date time format?

Mandes · May 1, 2012, 4:38pm

what format are you saving date in the database fiel Contract_Start ?

If its a unixtimestamp field then you’ll need to do some work to get it back to your YYYY-MM-DD format

try echoing out $cStart I suspect that your not formatting the date correctly.

multichild · May 2, 2012, 9:50am

Hi all,

Thanks for getting back to me…

This is what I have got at the moment…


if(isset($_GET['cNumber']))
{
	$customer_Number=$_GET['cNumber'];
	$queryEdit="select * from CSF_Intranet where Contract_Number=$customer_Number";
	$rowsEdit = sqlsrv_query($conn, $queryEdit);
	while ($rows = sqlsrv_fetch_array($rowsEdit, SQLSRV_FETCH_ASSOC)) {	
	$discardeddate = $row['Contract_Start'];
	$discardeddate_ts = strtotime($discardeddate);
	$dh = date('m-d-Y', $discardeddate_ts);
	echo $dh;

and the echo is displaying:

01-01-1970

But the date in the database is 2012-12-31

Then the code im using to change the date input calendar is below:


<script type="text/javascript">DateInput('dateoriginal', true<?php if(isset($dh)){if (!empty($dh)){echo ",'mm-dd-yyyy', '$dh'";}}?>)</script>

Which in honesty seems to working, but that is also displaying Jan 1 1970

That is as far as I have got at the moment.

Mandes · May 2, 2012, 10:04am

In your javascrpit you are using lowercase mm-dd-yyyy whereas the link you give for the calendar in your first post specifies uppercase.

ALSO

Why not set the calendar to accept the dates in MySQL format YYYY-MM-DD then you can get rid of all the conversions !!

multichild · May 2, 2012, 10:29am

Hi Mandes,

Yes I’ve been workign on it since to try and see what changes to make to get it working…

The MySQL bit your reffering too will that work as I’m using MSSQL?

If I can how do I do this?

Mandes · May 2, 2012, 11:00am

Try


if(isset($_GET['cNumber'])) 
{ 
    $customer_Number=$_GET['cNumber']; 
    $queryEdit="select * from CSF_Intranet where Contract_Number=$customer_Number"; 
    $rowsEdit = sqlsrv_query($conn, $queryEdit); 
    while ($rows = sqlsrv_fetch_array($rowsEdit, SQLSRV_FETCH_ASSOC)) {     
    $dh = $row['Contract_Start'];


[FONT=Courier New]<script type="text/javascript">
DateInput('dateoriginal', true
[COLOR=#0000bb]<?php [/COLOR][COLOR=#007700]if(isset([/COLOR][COLOR=#0000bb]$cStart[/COLOR][COLOR=#007700])){
        if (!empty([/COLOR][COLOR=#0000bb]$cStart[/COLOR][COLOR=#007700])){
          echo [/COLOR][COLOR=#dd0000]",'YYYY-MM-DD', '[/COLOR][COLOR=#0000bb]$dh[/COLOR][COLOR=#dd0000]'"[/COLOR][COLOR=#007700];
        }
      }
[/COLOR][COLOR=#0000bb]?>[/COLOR])
</script>[/FONT]

multichild · May 2, 2012, 12:15pm

Hi,

OK I tried that and all I got was that the date changer didnt appear at all, and it stopped the rest of the page from displaying.

I had to change a few of your bits as they didnt match up, so this is what I got now:


if(isset($_GET['cNumber']))
{
	$customer_Number=$_GET['cNumber'];
	$queryEdit="select * from CSF_Intranet where Contract_Number=$customer_Number";
	$rowsEdit = sqlsrv_query($conn, $queryEdit);
	while ($rows = sqlsrv_fetch_array($rowsEdit, SQLSRV_FETCH_ASSOC)) {
$cStart=$rows["Contract_Start"];
}

And then the script bit is this:


<script type="text/javascript">DateInput('dateoriginal', true<?php if(isset($cStart)){if (!empty($cStart)){echo ",'MM-DD-YYYY', '$cStart'";}}?>)</script>

If I also put an echo $cStart in the select bit that creates a problem and the whole page doesnt load at all.

Maybe its the field type in the database, as I dont know otherwise. Thats set at the moment to datetime and the data in there is:

2012-12-31 00:00:00.000

Would this mean anything, just looking at providing the full picture.

Mandes · May 2, 2012, 12:26pm

As we are supplying the date directly in the SQL format you need to change your javascript to YYYY-MM-DD

multichild · May 2, 2012, 12:35pm

Like this:


<script type="text/javascript">DateInput('dateoriginal', true<?php if(isset($cStart)){if (!empty($cStart)){echo ",'YYYY-MM-DD', '$cStart'";}}?>)</script>

If so unfoirtunately it didnt make a difference. The code stops at the point and nothing shows.

Thanks for keep on getting back to me though, I do appreciate it

Mandes · May 2, 2012, 12:49pm

can you give me a link to the page ?

as a sanity check replace $cStart with 2012-02-01 and see if the code runs

multichild · May 2, 2012, 12:55pm

Yes that worked…

I will PM it to you if thats ok

Mandes · May 2, 2012, 12:58pm

Hang on, ive just read further up that your using a datetime field in your database not a date so we need to strip out the time element.


if(isset($_GET['cNumber'])) 
{ 
    $customer_Number=$_GET['cNumber']; 
    $queryEdit="select * from CSF_Intranet where Contract_Number=$customer_Number"; 
    $rowsEdit = sqlsrv_query($conn, $queryEdit); 
    while ($rows = sqlsrv_fetch_array($rowsEdit, SQLSRV_FETCH_ASSOC)) { 
$cStart=$rows["Contract_Start"];
echo 'from database - ' . $cStart . '<br />';
$cStart=substr($cStart, 0, 10);
echo 'after subsring - ' . $cStart . '<br />'; 
}

multichild · May 2, 2012, 2:48pm

Hi,

Put that in and the page failed to load again…

The trouble seems to come from $cStart=$rows[“Contract_Start”];

It doesnt like it one bit, as when we changed it to $cStart=2012-02-01;

everything worked fine.

Mandes · May 2, 2012, 3:43pm

change [“Contract_Start”] to [‘Contract_Start’]

if that doesnt work pm me your FTP details and I’ll sort it directly online for you, be quicker.

multichild · May 2, 2012, 3:54pm

No that didnt either, and Im gutted in honesty as I was hoping that you would say that but i got to connect using a VPN and without that I dont think you can get to see the files.

I will PM you and send you the details, and you can see

Mandes · May 2, 2012, 4:50pm

OK then we need to make sure that

a) the database is accepting the query
b) that the contents of the field are being fetched.

Turn on all ERROR Reporting E_ALL in PHP
add these two lines to the top of your script

error_reporting(E_ALL);
ini_set(‘display_errors’,‘On’);

then change
$rowsEdit = sqlsrv_query($conn, $queryEdit);
for these 2
$rowsEdit = sqlsrv_query($conn, $queryEdit) or die(sqlsrv_errors());
if(sqlsrv_has_rows($rowsEdit)){ echo ‘rows fetched from DB’;}

multichild · May 3, 2012, 8:53am

Morning Mendes,

Gutted about yesterday, but we see now today and thank you again for the support.

I put the error script in and changed the other bit and straight away it came back with an error:

Catchable fatal error: Object of class DateTime could not be converted to string in \\DATASTORE101\CSFWEBDATA$\csfintranet\upload_contract.php on line 76

On line 76 is in bold:

$cStart=$rows['Contract_Start'];
[B]echo 'from database - ' . $cStart . '&lt;br /&gt;';[/B]
$cStart=substr($cStart, 0, 10);
echo 'after subsring - ' . $cStart . '&lt;br /&gt;';

Cheers

Mandes · May 3, 2012, 9:11am

Morning, and a beutiful one here to …

Lets try changing


$cStart=$rows['Contract_Start'];
echo 'from database - ' . $cStart . '<br />';
$cStart=substr($cStart, 0, 10);
echo 'after subsring - ' . $cStart . '<br />';

to


$cStart=$rows['Contract_Start'];
$cStart=date_format($cStart, "Y-m-d");
echo 'from database - ' . $cStart . '<br />';

multichild · May 3, 2012, 9:23am

BINGO!

Fair play Mendes youve been awsome, changed that script and it worked!

I’m also glad you got fine weather too, as here in the UK its what you may say a little bit damp to say the least.

Again I cant thank you enough, you have been great and now I can continue down the page getting the form to read from the database.

My next part after this is to get the upload form working, I will again give it my best shot but would you mind if I get into trouble that I can call upon you to help me with this bit as I think the rest of the form is fine, its getting the date to upload to the database now.