[RESOLVED] Create View - or - Join - or.... ?

ATS16805

currently, i'm using the following:

SELECT DATE_FORMAT(calendar,'%b %D') AS 'dayOf',
	DATE_FORMAT(calendar, '%w') as 'weekDay',
	room,
	event,
	detail FROM $table 
	WHERE calendar >=now() 
	AND calendar < now() 
	+ INTERVAL 14 DAY 
	ORDER BY calendar ASC;

because of the output from the above does NOT show the current date (which i find confusing) i want the above to be based on this sort of thing:

 DATE_SUB('dates', INTERVAL 1 DAY)

you can see that my fields, are calendar (the "user-selected" input from X entry date), and then just details of the entry item. not visible is the timestamp on-update now().

the reason for the > = now() and 14-day interval is i want the output to appear as "this weeks events", and "next weeks events": thus on a thursday for example-- we can see what's coming up on Sunday, or Monday of the coming week. when monday "of the coming week" arrives, it then becomes the monday of week one, and the "two week interval" visibility will shift, losing a week, and gaining a new one. thus should be the process, weekly.

NogDog

If I'm understanding what you want as being that the calendar always starts with the Monday of the current week, then I'd use PHP to get the most recent Monday:

if(date('D') == 'Mon')
{
   $startDate = date('Y-m-d');
}
else
{
   $startDate = date('Y-m-d', strtotime('last Monday'));
}
$query = "SELECT ...yadda-yadda-yadda... WHERE calendar BETWEEN '$startDate' AND ADDDATE('$startDate', INTERVAL 2 WEEK) ORDER BY calendar ASC"

ATS16805

NogDog wrote:
the calendar always starts with the Monday of the current week

fudge! why can't i ever learn to "talk good"!! yeah, that's what i meant to say in only 100 less words than i did.

it's this part which i didn't understand. prior to this project, i knew little of such things as INTERVAL. i learned a lot through the advice of someone in another forum-- but i don't know if he either a.) was emphasizing TOO much on my "read the mysql manual..." (after all, he wrote the SQL w/ the Interval part w/ the greater than now() bit, which didn't work-- so maybe that was his plan!), or b.) he just didn't understand as well what i was looking to achieve. in any event:

NogDog wrote:

BETWEEN '$startDate' AND ADDDATE('$startDate', INTERVAL 2 WEEK)

at a glance, appears to be what i need. if not, i'm sure some tweaking around in there, i'll get to it. BETWEEN, i had not yet learned.

i welcome any other advisory suggestions you might have, ND, on the subject of SQL / MySQL and dates, tutorials, and also-- HTML output of such data. no biggie-- but figured you might read this, so might as well take advantage of that. 😉

thanks a bunch of bananas!
...AND a bunch of grapes!
and that's a bunch of thanks, indeed good sir!!
🙂

ATS16805

ND, i just wanted to confirm w/ you that indeed your SQL worked perfectly.

i'd like to wait a couple of days to see if i get any replies about the html. if not, i'll "resolve it" for sure cause ND did an awesome job w/ the SQL!!

ATS16805

Hi there. It appears as though the SQL i used, as offered by ND isn't working properly on one of my test-production sites.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WEEK) ORDER BY calendar ASC' at line 6

i'm using 5.0.27 (standard on Fedora Core 5, afaik), and the SQL worked fine. i first realized i had an incompatibility problem w/ the 4.1 version, during an import, threw some "unusual" errros -- which turned out to be a result of the VIEWs i had setup here on localhost (5.0). once i got rid of that , exported another .sql file from my local phpmyadmin, (after dropping the views) -- i imported it a second time using "Enable for 4.0 compatibility" -- but it didn't help.

i could have done something wrong of course, but i'm currently at a loss.

what might i do to modify the query? i don't understand where it would be "wrong"... unless maybe it's in the php file... hmmm

ideas?

Roger_Ramjet

You use the CSS
table {
border-collapse: separate;
empty-cells: show;
}
The old fashioned way was to put some content in the empty cell and set it's color to the same as the backgound - a nasty way of doing things.

Oh yes,
table {
border-collapse: collapse;
}
will also show the grid around empty cells, even if whole columns or rows are empty.

ATS16805

Roger Ramjet wrote:
You use the CSS
table {
border-collapse: separate;
empty-cells: show;
}

that's unexpected. i was thinking more in terms of some sort of array_merge / pad / splice / pop/ stop / drop n roll sorta thing

oh well... time for beddy byes . him sleepys for now. 😉

thanks for the input though RR. i'll check that out. sounds like it could be useful. would definitely have saved me about 80 hours or so 😉

Roger_Ramjet

Feel like that myself - up all night hacking with class constructors cos the manual ain't what it should be. Still I lerned it. Have a dream for me cos ahs gotta stay up an go to de bank.

ATS16805

Roger Ramjet wrote:
..have a dream for me cos ahs gotta stay up an go to de bank.

hahah! right on man, right on.

so-- if it's not one thing, it's another. did i mention that this SQL isn't playing nice w/ MySQL version 4.1? well not only that-- it's looking like-- i can't even tell if i'm connecting to the db (on my client's server) as i'm getting NO feedback whatsoever -- however, i've got another db on the same server which IS working w/ the same parameters (essentially), and if i use the same files from my localhost (leaving their server db info, as it is NOT localhost on their end) -- i DO get feedback that i'm not connecting... but i've got xdebug running-- so maybe that's why i'm getting feedback here, and not on the remote? ie. same "production" files "err" on my local machine, showing can't connect to dbserver.host.com, whereas there, i get "nothing".

just looking for any ideas here. i can't believe it hit a "deadend" after having it all working just hours ago.

what about the idea of installing MySQL 4.1 alongside 5.0 here on localhost to try n debug that way. can i do that? other suggestions for getting to the bottom of this?

assuming it's a compatibility issue, then besides just combing the manual, is there any other resource which might help me to determine how to "make it" backwards compatible?

in short, what is the reasoning server admins have for using MySQL 4.1 over 5.0 anyway? just curious

thanks! sorry for the "shotgun blast" of questions there. just close your eyes, touch the monitor, and pick one. 😉

Roger_Ramjet

ATS16805 wrote:
in short, what is the reasoning server admins have for using MySQL 4.1 over 5.0 anyway? just curious

Stupidity! Laziness! Backward-compatibility! Lack of demand!

Really though, why not move to a host that does give full ver 5? Oh, it's a client's system - well talk them into it.

I don't know what effect setting it "Enable for 4.0 compatibility" has, but ADDDATE is only supported for 4.1+ , before that you had to use DATE_ADD - which still works so you might as well code that cos one is an alias for the other and only introduced to standardise the sql.

Not a lot of point installing 4.1 on your machine to test when the problem is the production server configuration.

Use ' or die(mysql_error())' to trap and display any errors that are being thrown.

ATS16805

EDIT: i've got to be overlooking something "trivial" here that's causing this. i modified the ADDDATE to DATE_ADD, set the proper db info, AND added my custom mysql error (as shown below... waaay below) -- and still-- all i get is the "masthead"

<?php
// as its an:
import 'inc/header.inc.php';
// ... sort of thing..
?>

... as if i'm viewing an incomplete page, ya know?

Next Step: Create a very simple db test page. SELECT * FROM sort of thing...

Roger Ramjet wrote:
Stupidity! Laziness! Backward-compatibility! Lack of demand!

that's what i thought [i.e. "laziness" or just no need to bump up to 5.0 from previous 4.1 installs].
i asked because [unrelated to this issue particularly, but relevant] i'm working w/ someone, co-administering a cPanel VPS. he's been in the biz since around '95, has done the BSDi thing old-school SSH style, and only now getting into the cPanel method. he opted for MySQL 4.1 in his setup. i thought it odd to actually install 4.1 instead of 5.0-- considering that he's got the actual WHM updating on nightly builds, and the newest PHP and Zend Opt., etc-- yet the Mysql4.1 threw me-- so i thought perhaps there was a specifc known "defect" in MySQL 5.0+ that i wasn't aware of, etc perhaps i can convince him to move to 5.0. i mean-- if 5.0 is a standard issue on FC5 (according to my distro at least), then it can't be all bad, right?

RogerRamjet wrote:
I don't know what effect setting it "Enable for 4.0 compatibility" has, but ADDDATE is only supported for 4.1+ , before that you had to use DATE_ADD

are you saying that:

BETWEEN '$startDate' AND ADDDATE('$startDate', INTERVAL 2 WEEK)

is a 5.0 syntax, and DATE_ADD is standard SQL, yet understood by 4.1 (and) 5.0?
OR are you saying that ADDDATE should be recognized by both as well?
if ADDDATE is 4.1+ (i think that's what you said?), and i'm using 4.1 and ADDDATE... then... it's not making much sense either.

i have the die(mysql_error()) thing [always] but i thought perhaps i needed to add something like:
die ('I cannot connect to the database because: ' . mysql_error()); (cause i ususally do as you put, w/ just the system errors).

i'm glad to hear that you recommend against 4.1 locally-- i didn't think i should need to do that either. my next move was going to be combing the manual for signs of "backward / non-backward compatibility" vs the SQL that NoggDogg provided-- so i'm glad that you pointed that bit out for me. i should have recognized it myself-- after all, i was trying to use DATE_SUB before i started this thread. doh!

thanks RR and ND for your help on this issue.

Roger_Ramjet

What I'm saying is that pre 4.1, ie 4.0 it was DATE_ADD.

Now what I wondered was how setting it for 4.0 compatibility might affect this?

DATE_ADD is supported in later versions for backward compatibility.

I assumed that the ADDDATE alias was added to make mysql more standards compliant, why bother otherwise. Probably wrong about that though cos I don't think it is any more standard, or even if such a standard exists.

That's what I meant, their hosting deal.

Yeah, I used to be a 'mouse-hater gimme command-line every time' programmer myself. Grew out of that by 95 though. Can't understand anyone using cPanel though, not since the mysql admin tools were released. Not before that either, piece of junk.

ATS16805

Roger Ramjet wrote:
Can't understand anyone using cPanel though, not since the mysql admin tools were released. Not before that either, piece of junk.

WOW! you really think so?
i'm not sure i understand what you mean by the MySQL Admin Tools

i think i'm going to PM (sorry!) about this because now you've revealed you know some'n some'n. 😉
so, i'd like to ask you a couple things about that cPanel / MySQL stuff in general... (since it's not particular to this thread)

re: 4.1 vs 4.0 compatibility...
AHHH! now i see what you mean. totally get you. cause it's taking it down to "PRE" 4.1. i didn't realize there was a diff-- so, since the ADDDATE / DATE_ADD is on the PHP side, and if i took the SQL down to PRE 4.1 (where DATE_ADD, and who knows what else might come into play...)... yes? that's kind of where you're going w/ that, yes? i think i gotcha

now that you bring it up. i'll try again. it erred at first because i had some Views setup in there on Import, so that's why i did it on second try. i'll drop and try again. couldn't hurt!

EDIT:
grrr... i don't like their MySQL admin -- i dropped the DB but when i tried to create another, it tells me one already exists [cPanel wouldn't do that! 😉]

to clarify:
ADDDATE = SQL standard
DATE_ADD = MySQL proprietary?

Roger_Ramjet

No, I had a look around and ADDDATE is no more standard than DATE_ADD, less so in fact. I just assumed they added it for that reason but apparently not so who knows.

mysql tools installs locally.

ATS16805

here's where i am.

1.)
first of all-- my reason for doing so w/ the "failing connection" as mentioned in prev. few posts-- is that's going to be THE production server-- unless i can convince them to drop that host-- which it's looking like might be the smart choice because...

2.)
using this same site on "MY" VPS server setup, i have NO PROBS w/ the Connection-- at least i'm getting the mysql_error.

3.)
using

$this->allque = "SELECT DATE_FORMAT(calendar,'%D') AS 'dayof',
	DATE_FORMAT(calendar, '%w') as 'weekday',
	room,
	event,
	detail FROM $tableall WHERE calendar BETWEEN '$startDate' AND DATE_ADD('$startDate', INTERVAL 2 WEEK) ORDER BY calendar ASC";

i'm still getting the error:

test-server_feedback wrote:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WEEK) ORDER BY calendar ASC' at line 5

EDIT: is there a misspelling? syntax error that you can see? [recall, considering this works on MySQL 5.0 just fine!]
how might i 'debug' this in a more direct manner (i.e. locally, i'd open terminal (cmd.exe since i'm on XP) and do the query right off of mysqld> prompt ... but that's not possible from remote is it? err... SSH? what's a good SSH client for XP?)

this is exhausting! at least i'm "learning something" i suppose. 😐

Roger_Ramjet

And there you have it: WEEK is ver 5 only. You have to use 14 DAY not 2 WEEK.

Guess I got sidetracked with all the other stuff and forgot to look at the query itself.

ATS16805

Roger Ramjet wrote:
WEEK is ver 5 only.

rock n roll, man! rock AND roll!!

🙂
thank you for your eyes, and experience, and steadfast attention to this issue!!!!

yeah-- sheesh i've been doing the research-- silly me not to look at something like that immediately. this is learning i suppose.

check this-- i dunno if you're into this sort of thing (the collective "you" out there), but might interest you. i know sometimes it can be difficult to find anything of "substance", but this [methinks] seemed legit:
http://www.mysqlperformanceblog.com/files/presentations/UC2006-MySQL-5.0-vs-4.1.pdf
(it's a comparison of 5.0 vs 4.1 -- some neato stuff there)

couple other "random" things which shed some light-- seeing what i did in the PDF above-- i was about to go and look for the 4.1 syntax on stuff re: BETWEEN and dates, etc.
http://searchopensource.techtarget.com/tip/1,289483,sid39_gci1130131,00.html

IMO, a quality resource all around for MySQL enthusiasts:
http://www.mysqlperformanceblog.com/category/benchmarks/

MySQL and smokin' Pipes -- J. Pipes that is!!:
http://www.jpipes.com/index.php?/archives/54-Do-the-New-Features-of-MySQL-5-Cause-Performance-Degradation.html
(btw: J. Pipes is a great guy, from my experience w/ the dude in comm at mysql.org forums)

and this for, as A. Powers might say, for ships and wiggles:
http://swik.net/MySQL/Latest+Updates+from+MySQL+AB
(comparing MySQL 5 to MS SQL)

edit: will resolve the thread soon as i test and it works. 😉

ATS16805

TO SUMMARIZE (for other readers, learning):
this MySQL query, which works fine in MySQL 5.0+, does not work in 4.1:

$query="SELECT . . . WHERE calendar BETWEEN '$startDate' AND ADDDATE('$startDate', INTERVAL 2 WEEK) ORDER BY calendar ASC"

the reason is the Syntax "WEEK", which is NOT recognized by versions previous to 5.0.
to make this perfectly logical SQL compliant with the version of the MySQL server i must use in production, it was required to modify the SQL from 2 WEEK to 14 DAY, like so:

$query="SELECT . . . WHERE calendar BETWEEN '$startDate' AND ADDDATE('$startDate', INTERVAL 14 DAY) ORDER BY calendar ASC"

and now the Query is functional for MySQL 4.1. The problem is a result of the Syntax being a MySQL proprietary term, and not a "standard"
[correct me if i'm wrong there, please]

had i known better (only be reading the manual, practicing [as in this case], and most important, i believe, had i known more STANDARD (or "universal") SQL, i might have had less problem with this issue.

i'd like to thank R.R. for his helping me w/ a few different issues, leading to the ultimate resolve of this db issue, and to N.D. for the insight into the possibilities of MySQL and dates, etc.

Roger_Ramjet

So you edited your post. Did you get the connection working?

A lot of times, mysql server has been set up to only allow connection from localhost or from within the ISP network. So don't be surprised when you can't connect and test from your local machine. I don't do a lot of web sites, usually just for the company, but I quickly learnt to do the development work on the same platform that the production system will run on. Otherwise you run into these sorts of problems when you want to test or go live.

ATS16805

Roger Ramjet wrote:
So you edited your post. Did you get the connection working?

unfortunately, no.

for now, i'm going to keep going w/ it-- since i can try to persuade him to use my facility-- or perhaps even link to a db on my facility (?).

it's really weird to me because i have another "test project" there, which a big part of the viewed content comes from MySQL, and it's working fine. -- same parameters, same "db server", etc. -- and DreamWeaver (only because it's got a pretty wicked FTP / Project Mgt setup) did in fact connect to the db [of this thread], using the other "project's" parameters-- db name adjusted as appropriate, but same userid n pass. so, it makes no sense-- and i DW did access it w/ the proper parameters, but then "lost connection". weird.

like you [alluded], it might be some sort of security? but then why would they force their users to administer their DB 's like that?