Dreamweaver MX extracting week number

[curtgus]

My form collects a date (which the user picks from a pop-up calendar). That date is submitted to a field (datBegin) in the MySQL database. Fine.

In the process of submitting that date, I want a portion of it (the "week-number" represented by the date that the user entered) to be submitted to another field (week_nbr) in the same MySQL database and table.

In other words, the user enters the date once. When he clicks submit, the date (like 2007-5-17) is placed in the datBegin field, and the week number (like 20) should be placed in the week_nbr field.

The formating of the week number is not a problem. It is done with DATE_FORMAT(datBegin,'%u'). But the submitting of it to the database is what I am not able to accomplish. The date and the week number are of course part of the same record (row).

If anyone can help, I'd greatly appreciate it. (I have run out of things to try!) Most of my attempts have been to create a value for week_nbr in a hidden field. I have also tried the DATE_FORMAT in Recordsets. None of this has been succesful in getting a week_nbr value (based on the just entered datBegin) stored to the MySQL database.

I use PHP and work with Dreamweaver MX.

[Newsgroup_User]

curtgus wrote:
> In the process of submitting that date, I want a portion of it (the
> "week-number" represented by the date that the user entered) to be submitted to
> another field (week_nbr) in the same MySQL database and table.

You don't need to. The information stored in datBegin already contains
that information, as you point out yourself.

> The formating of the week number is not a problem. It is done with
> DATE_FORMAT(datBegin,'%u').

You're approaching the problem the wrong way. You presumably want to
store the week number so that you can search according to week number or
display results by week number. The way to do it is to use
DATE_FORMAT(datBegin,'%u') as part of your query. For example:

SELECT thisCol, thatCol, DATE_FORMAT(datBegin,'%u') as week_num
FROM mytable
ORDER BY week_num DESC

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[curtgus]

Hi David Powers,

Thanks for your input regarding the week number problem.

re.

> The way to do it is to use
DATE_FORMAT(datBegin,'%u') as part of your query. For example:

SELECT thisCol, thatCol, DATE_FORMAT(datBegin,'%u') as week_num
FROM mytable
ORDER BY week_num DESC

This is exactly what I have tried over and over again, many times. This
approach fails when you add the WHERE statement:

WHERE kalendar.week_nbr = servicegrupp.vecka

However, when I hard code the week_nbr into the kalendar table, then the
WHERE statement works as intended. There is a match, in other words.

It appears that the reason it does not work unless the week_nbr is in the
table, is that "week_num" (as you have it) from the query is not a field but
something else, perhaps a variable. I'm not sure about the terminology. To
match output from the table with output from an AS statement in the query
does not work -- at least not in my experience.

I guess the scenario is one of relations. In other words the "week_nbr"
from the kalendar table relates to the "vecka" in the servicegrupp table.
When both are present in their respective tables, the match occurs.

To try to match them at the output stage, using the AS statement in the
query, does not work. The wrong "week_nbr" is inserted -- always the number
from the top record in the table, it appears.

I know that in the case of relational tables, it is possible to output the
id from one table to the foreign key in the related table. It is this
facility I need to find, somehow, even though I am not dealing with the ids.

These are my reasonings at the moment. But there is probably a better, more
elegant way to accomplish the needed match.

Thanks again for your willingness to help.

Curt L. Gustafsson


----- Original Message -----
From: forums@adobe.com
To: gustafsson@ibtnet.org
Sent: Thursday, May 31, 2007 7:31 PM
Subject: Dreamweaver Support subscription update.


Newsgroup User has posted a message entitled Re: Dreamweaver MX extracting
week number.

Message Posted on: Thursday May 31,2007 10:31:12 AM
curtgus wrote:
> In the process of submitting that date, I want a portion of it (the
> "week-number" represented by the date that the user entered) to be
submitted to
> another field (week_nbr) in the same MySQL database and table.

You don't need to. The information stored in datBegin already contains
that information, as you point out yourself.

> The formating of the week number is not a problem. It is done with
> DATE_FORMAT(datBegin,'%u').

You're approaching the problem the wrong way. You presumably want to
store the week number so that you can search according to week number or
display results by week number. The way to do it is to use
DATE_FORMAT(datBegin,'%u') as part of your query. For example:

SELECT thisCol, thatCol, DATE_FORMAT(datBegin,'%u') as week_num
FROM mytable
ORDER BY week_num DESC

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/


You can view the message at
http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=189&threadid=1272474&forumid=12.

[Newsgroup_User]

On 31 May 2007 in macromedia.dreamweaver.appdev, curtgus wrote:

> This is exactly what I have tried over and over again, many times.
> This approach fails when you add the WHERE statement:
>
> WHERE kalendar.week_nbr = servicegrupp.vecka

etc.

Thoroughly frustrating, because I was able to use

SELECT `Opp`, `gameDate`, WEEK(`gameDate`) AS myWeekNum
FROM `Schedule`
ORDER BY myWeekNum

but when I went to use myWeekNum in a WHERE clause, it errored out on
me. I suspect that this might be a bug in MySQL.

In any case, I was able to get this to work:

SELECT `Opp`, `gameDate`, WEEK(`gameDate`) AS myWeekNum
FROM `Schedule`
WHERE WEEK(`gameDate`) = '40'

This was tested on MySQL 4.1.20

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

[Newsgroup_User]

curtgus wrote:
> This is exactly what I have tried over and over again, many times. This
> approach fails when you add the WHERE statement:
>
> WHERE kalendar.week_nbr = servicegrupp.vecka

Without experimenting with your actual data, it's hard to work out why
this doesn't work. However, you should be able to insert the week number
like this

INSERT INTO kalendar (datBegin, week_nbr)
VALUES ('beginningDate', DATE_FORMAT('beginningDate', '%u'))

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[curtgus]

Repeated to ensure delivery

David,

Thanks for checking this out, and for finding the same problem as I found.

re.


INSERT INTO kalendar (datBegin, week_nbr)
VALUES ('beginningDate', DATE_FORMAT('beginningDate', '%u'))

I looked at this as well, earlier, and this probably works. But, since I'm working in Dreamweaver MX (2004), I don't know where to put it. I tried to put it in Values for a Hidden Field. First of all, that accepts only one line of code, but the syntax also does not fit there, seemingly. Then I tried to find a way to put this into the INSERT sction in the code itself. There, I couldn't figure out how to enter the values. Here is where I'm at on that:

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form2")) {
$insertSQL = sprintf("INSERT INTO kalendar (datBegin, week_nbr, tid, mote, kommentar) VALUES (%s, %s, %s, %s, %s)",
GetSQLValueString($_POST['datBegin'], "date"),
GetSQLValueString($_POST['week_nbr'], "date"),
GetSQLValueString($_POST['tid'], "date"),
GetSQLValueString($_POST['mote'], "text"),
GetSQLValueString($_POST['kommentar'], "text"));

mysql_select_db($database_Sala_Pingst, $Sala_Pingst);
$Result1 = mysql_query($insertSQL, $Sala_Pingst) or die(mysql_error());
}
?>
<?php require_once('../../Connections/Sala_Pingst.php'); ?>
<?php
mysql_select_db($database_Sala_Pingst, $Sala_Pingst);
$query_Recordset1 = "SELECT kalendar.id, kalendar.datBegin, kalendar.tid, kalendar.mote, kalendar.kommentar, servicegrupp.id, servicegrupp.name, servicegrupp.vecka FROM kalendar, namn, servicegrupp ";
$Recordset1 = mysql_query($query_Recordset1, $Sala_Pingst) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

If you could just advise me on where to put:

INSERT INTO kalendar (datBegin, week_nbr)
VALUES ('beginningDate', DATE_FORMAT('beginningDate', '%u'))

the problem is likely going to be solved.

Thanks and greetings,

Curt Text

[curtgus]

David,

Thanks for checking this out, and for finding the same problem as I found.

re.


INSERT INTO kalendar (datBegin, week_nbr)
VALUES ('beginningDate', DATE_FORMAT('beginningDate', '%u'))

I looked at this as well, earlier, and this probably works. But, since I'm working in Dreamweaver MX (2004), I don't know where to put it. I tried to put it in Values for a Hidden Field. First of all, that accepts only one line of code, but the syntax also does not fit there, seemingly. Then I tried to find a way to put this into the INSERT sction in the code itself. There, I couldn't figure out how to enter the values. Here is where I'm at on that:

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form2")) {
$insertSQL = sprintf("INSERT INTO kalendar (datBegin, week_nbr, tid, mote, kommentar) VALUES (%s, %s, %s, %s, %s)",
GetSQLValueString($_POST['datBegin'], "date"),
GetSQLValueString($_POST['week_nbr'], "date"),
GetSQLValueString($_POST['tid'], "date"),
GetSQLValueString($_POST['mote'], "text"),
GetSQLValueString($_POST['kommentar'], "text"));

mysql_select_db($database_Sala_Pingst, $Sala_Pingst);
$Result1 = mysql_query($insertSQL, $Sala_Pingst) or die(mysql_error());
}
?>
<?php require_once('../../Connections/Sala_Pingst.php'); ?>
<?php
mysql_select_db($database_Sala_Pingst, $Sala_Pingst);
$query_Recordset1 = "SELECT kalendar.id, kalendar.datBegin, kalendar.tid, kalendar.mote, kalendar.kommentar, servicegrupp.id, servicegrupp.name, servicegrupp.vecka FROM kalendar, namn, servicegrupp ";
$Recordset1 = mysql_query($query_Recordset1, $Sala_Pingst) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

If you could just advise me on where to put:

INSERT INTO kalendar (datBegin, week_nbr)
VALUES ('beginningDate', DATE_FORMAT('beginningDate', '%u'))

the problem is likely going to be solved.

Thanks and greetings,

Curt

[curtgus]

David,

Just a quick comment on:

> WHERE kalendar.week_nbr = servicegrupp.vecka

> Without experimenting with your actual data, it's hard to work out why
this doesn't work. However, you should be able to insert the week number
like this

Logically, the data on which the query acts comes from the database. In our case, now, we need to direct the data TO the database BEFORE the query can act on it. It's a matter of timing, in other words. INSERT has to happen before SELECT can take place.

Nothing profound about that.

Curt

[Newsgroup_User · Accepted solution]

curtgus wrote:
> Here is where I'm at on that:
>
> $insertSQL = sprintf("INSERT INTO kalendar (datBegin, week_nbr, tid, mote,
> kommentar) VALUES (%s, %s, %s, %s, %s)",
> GetSQLValueString($_POST['datBegin'], "date"),
> GetSQLValueString($_POST['week_nbr'], "date"),
> GetSQLValueString($_POST['tid'], "date"),
> GetSQLValueString($_POST['mote'], "text"),
> GetSQLValueString($_POST['kommentar'], "text"));

Change the section above to this:

$insertSQL = sprintf("INSERT INTO kalendar (datBegin, week_nbr, tid,
mote, kommentar) VALUES (%s, DATE_FORMAT(%s,'%%u'), %s, %s, %s)",
GetSQLValueString($_POST['datBegin'], "date"),
GetSQLValueString($_POST['datBegin'], "date"),
GetSQLValueString($_POST['tid'], "date"),
GetSQLValueString($_POST['mote'], "text"),
GetSQLValueString($_POST['kommentar'], "text"));

Basically, you just add the DATE_FORMAT() around the second %s, and set
the second GetSQLValueString() argument to accept $_POST['datBegin'].
Because sprintf() interprets the percentage sign as a PHP format
specifier, you need to escape the percentage sign in '%u' like this: '%%u'.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[curtgus]

David,

Thanks for this:

-----------------
Change the section above to this:

$insertSQL = sprintf("INSERT INTO kalendar (datBegin, week_nbr, tid,
mote, kommentar) VALUES (%s, DATE_FORMAT(%s,'%%u'), %s, %s, %s)",
GetSQLValueString($_POST['datBegin'], "date"),
GetSQLValueString($_POST['datBegin'], "date"),
GetSQLValueString($_POST['tid'], "date"),
GetSQLValueString($_POST['mote'], "text"),
GetSQLValueString($_POST['kommentar'], "text"));
-----------------------

It works!!

If your help site had grades and points, like Experts Exchange does, I'd give you an A+ and 500 points (the max)!

Very much appreciated. Thank you. And thank you especially for the explanation of why it works. This is very helpful, because I'm sure the problem will come up again.

I have two other questions regarding grouping the output of this data, but I'll post those later.

Sincerely,

Curt L. Gustafsson

[Newsgroup_User]

curtgus wrote:
> It works!!

Hooray!

> If your help site had grades and points, like Experts Exchange does, I'd give
> you an A+ and 500 points (the max)!

Thanks. It's nice to be able to solve a problem without too much difficulty.

> Very much appreciated. Thank you. And thank you especially for the
> explanation of why it works. This is very helpful, because I'm sure the
> problem will come up again.

I wish more people were like you. So many seem to want just the answer,
but don't really care why it works. The problem with the Dreamweaver
code is that it doesn't make it easy to write custom SQL queries like
this, so you need to understand the code to be able to customize it.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[curtgus]

Yes, Hooray!

David said:
> The problem with the Dreamweaver code is that it doesn't make it easy to write custom SQL queries like this, so you need to understand the code to be able to customize it.

Yes, this is what I was stuck on! I figured the problem would be in the INSERT area, but how to pass the value was a mystery.

Now as for the output, it currently looks like this:

http://www.sala.pingst.se/htm/pr/sp_kalender_view.php

There are two goupings that need to be implemented. 1) the "v. ## Servicegrupp Xxxxxx Xxxxxxxxx" should be then main group, with all the dates for that week sorted under this heading, and 2) within each main group, the activities for identical dates should be grouped together and ordered by time -- when there is more than one event for a given date, the date should only appear above the first event in that group.

Example:

v. 19 Servicegrupp Elisabet Hedman
Tisdag 17 april
13:00 Bön
17:00 Äldstemöte
19:00 Ungdomsmöte

Onsdag 18 april
19:00 Körövning

Torsdag 19 april
etc.

v. 20 Servicegrupp Alsiö/Andersson
etc.

I have experimented with "Region" in DW but this does not seem to offer a solution -- there are limitations. I have also searched for code to accomplish this, but have not found anything helpful yet. Best would be if it can be done within Dreamweaver, of course.

Once this webpage grouping is solved, the next task is to commit this data to paper where it must "snake" over three columns on a landscape format page. But that's another story!

Maybe you have some helpful suggestions on the grouping problem. This will be much appreciated. (By the way, I can do it in Access, but I'm stuck in DW, and PHP/MySQL.)

Sincerely,

Curt L. Gustafsson