Subquery Problems in mySQL

I'm having trouble selecting data for a web-based application. There are three tables, Articles, Keywords and KeywordsInArticle. There is a many-to-many relationship between the articles and keywords, which is handled by the piggy-in-the-middle KeywordsInArticle table. So, if we have the following scenario:

Articles
articleID  articleName
1             First Article
2             Second Article
3             Third Article
4             Fourth Article

Keywords
keywdID  keyWord
1             apples
2             oranges
3             bananas
4             grapes

KeywordsInArticle
articleID  keywdID
1             1
1             3
2             1
2             2 etc

We know that "First Article" is all about apples and bananas while "Second Article" is about apples and oranges etc. And for article #1, I can pull out a list of the relevent keywords by using the following SQL statement:

SELECT keywordsinarticle.articleID, keywordsinarticle.keywdID, keywords.keyWord
FROM keywords, keywordsinarticle
WHERE ((keywordsinarticle.keywdID=keywords.keywdID) AND (keywordsinarticle.articleID=1))

This would give the following result:

articleID   keywdID   keyWord
1             1              apples
1             3              bananas

So far, straighthforward. However, in the Content Management side of the application, there needs to be a way of managing the keywords associated with each article, to update the KeywordsInArticle table. However, this has a compound key value made up from the articleID and the keywdID. It cannot contain duplicate rows, so when adding a keyword entry to the list, I want to be able to select all keywords not already associated with that article. In the example, I need to select only "oranges" and "grapes" from the keywords table, to give them as options to add.

I want to be able to invert my selection, so I tried a using the above as a subquery and negating the result:

SELECT *
FROM keywords
WHERE keywdID NOT IN (SELECT keywordsinarticle.articleID, keywordsinarticle.keywdID, keywords.keyWord
FROM keywords, keywordsinarticle
WHERE ((keywordsinarticle.keywdID=keywords.keywdID) AND (keywordsinarticle.articleID=1)))

Sadly, mySQL throws a wobbler at this, telling me:

Message = "Operand should contain 1 column(s)"

Looking up this error at the mySQL website directed me towards the Row Subqueries page, but I'm not convinced this fits my problem either. So now I'm rather stuck. If anyone has any ideas, I'd love to hear them!

A Stitch In Time

I've got a probem with date sorting.

I have a PHP/mySQL application which requires sorting a dataset by year.

As mySQL won't let you store just the year, I originally used a varchar (text) field to store the year, YYYY, which sorts nicely.

However, I now have a client who wants to be more specific with some dates only, ie they want to be able to store YYYY, YYYY/MM or YYYY/MM/DD in the same field and still have it sort nicely.

OK, so I can force them to input the full date (when required) in the YYYY/MM/DD format, but that looks really ugly when output to the web page as a human-readable date.

So my problem is this:

• If I want human-readable dates, like 1 January 2007, and go for a text field, they won't sort properly as 1 April 2007 comes before 1 January 2007 in that case.
• If I opt for the date field, it would sort properly but I can't store dates which are just month/year or year only, as mySQL throws a wobbler and protests when you don't have the full date input.

Storing the "date" as a string: 2007/08/01 (the pubDate) sorts nicely, and I had hoped using the following PHP:

<?php echo date('j F Y', strtotime($row_rsByDate['pubDate'])); ?>

would solve the problem, since it gives a nice date 1 August 2007. But there are still issues with just storing part of the date - see screenshot below:

The entries are sorted correctly (most specific at the top, descending order). The first date (red) is the nice output of the PHP function - the second (puce green) is the actual stored text.

It falls over horribly when only the YYYY/MM is stored (returning 1st January 1970) or YYYY (returning today's date).

EUREKA!

After much discussion (via the comments on this post and my buddies on Twitter) , I was almost ready to explode with frustration at not being able to find a solution. Turns out, that's exactly what I needed to do!

James Aylett suggested I use the PHP explode() and implode() functions to write a custom parser. I didn't actually need the implode() half as it happens. Being a relative newbie to PHP, I'd never heard of the explode function, so thanks for the pointer, James!

So, to recap. The database column should be stored as TEXT or VARCHAR(10) not DATETIME. That way, all variations of YYYY, YYYY/MM or YYYY/MM/DD are acceptable, and will sort correctly. This will look horrible if put straight into the page, so use something like the following to present it nicely to the web page:

<?php $split = explode("/",$row_rsByDate['pubDate'],3);
print_r($split[2]);
echo " ";
if ($split[1]==1) print_r("Jan");
elseif ($split[1]==2) print_r("Feb");
elseif ($split[1]==3) print_r("Mar");
elseif ($split[1]==4) print_r("Apr");
elseif ($split[1]==5) print_r("May");
elseif ($split[1]==6) print_r("Jun");
elseif ($split[1]==7) print_r("Jul");
elseif ($split[1]==8) print_r("Aug");
elseif ($split[1]==9) print_r("Sep");
elseif ($split[1]==10) print_r("Oct");
elseif ($split[1]==11) print_r("Nov");
elseif ($split[1]==12) print_r("Dec");
echo " ";
print_r($split[0]);
?>

explode() - separates the string found in pubDate into its component parts (the separator is set by the first parameter, "/"), giving the $split array. This containts up to 3 elements, depending on how specific the stored "date" is.
$split[2] is the DD day element and is printed directly.
$split[0] is the YYYY year element and can be printed directly.
$split[1] is the MM month element, which requires the pretty formatting. So a match against each month number prints out a more human-readable month. Voilà!

Another screenshot - this time the green output is the unprocessed pubDate text field, and the preceding red date is the bespoke-parsed version - allowing for all cases of "date" format.

Some caveats:
I wouldn't normally recommend spoofing the date like this - if it's a real date (specified to day, month, year for every record) then use the DATETIME type in your database - this will sort and you can perform arithmetic on it quite readily (eg next month, last week etc).

I only went through this pain because of the special case I was finding - the publication "dates" of newspaper articles (day, month, year), journals (month, year) and books (year only) which my data required. Using this solution lets it all sort properly and look nice to viewers. Win!

Disconnected

When I first started cutting my teeth on dynamic web site development, I was pleased that Dreamweaver has plenty of tools to help out. Because we used it at work for sites, I chose the ASP.NET/C# server model, and used Access as the database (perfectly adequate for the size and features of the sites I was building).

Now I'm beginning to get to grips with PHP/mySQL for development. That's going well too. And having used mySQL for a while, I can see the advantages over Access. But what I'd really like to do is upsize a few of my Access databases to mySQL and have them connect to my existing .NET applications, without having to re-write the damned things.

The data migration tools available to s smooth job, and the data is sitting there, ready to go. But now I've hit a brick wall. How to tell Dreamweaver I want to use a mySQL database instead of access! So I began looking for the correct connection string, this was some help. I also downloaded MySQL ODBC 3.51 and installed it.

Then I set the web.config file entry to this (bold being the connection string given in the link above):

Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDatabase;User=myUser;
Password=myPassword;Option=3;" />

I tried the TEST button and Dreamweaver connected successfully! But try running the site via the browser (or Dreamweaver Live Data View) and it falls over with an error:

System.ArgumentException: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.
at System.Data.OleDb.OleDbConnectionString.ValidateParse()

So tried a different connection method, using Connector/Net 1.0.9 with this syntax in the web.config file - but got very similar results. Then I found this following in an Adobe Tech Note (my emphasis):

Do I have to use ODBC?
No. For ASP sites you can also connect to a database using OLEDB. For ASP.NET sites you must use OLEDB or the native ASP.NET SQL Server connector.

So it looks like it has to be OLEDB, but how to put the correct provider in the connection string? Nothing I've tried seems to work! Any clues? Has anybody actually got this combination to work?

• Dreamweaver 8
• ASP.NET/C# server model
• mySQL database

Cowboys (Me) And Indians (That'll be Apache)

I'm A PHP Newbie
For some weeks, I've been meaning to try my hand at some PHP development, having done most of my projects to date with .NET. I bought the excellent book Blog Design Solutions in September, and have been gradually reading my way through it in my spare time. It gives advice on installing and tweaking some of the most common blog engines such as Movable Type, ExpressionEngine, WordPress and TextPattern, but the last chapter leads you through building your own blogging solution.

I thought this was a good place to start for a PHP newbie, since there were copious examples and plenty of advice about setting up your test environment, a notorious minefield to tread safely on your own.

Setting Up The Test Environment
Unfortunately, this is where the pain and suffering began... I downloaded the lastest stable Apache release (as adivsed by the book), which was supposedly 2.2.3, as the Win MSI installer. It half loaded up, but would not run as a service on my WinXP Pro machine. The Apache icon appared in my SysTray, but the context menu was blank, and it did not appear in my list of services to start manually! After going round the loop several times, I gave up and went back for the 2.0.59 release instead - which worked first time!

Because I've already got IIS running as my default web server on localhost, I had to tell Apache to use a different port - 8080 is the conventional one for a second web server. Then you can use this in your URL to call on Apache to serve your pages:

http://localhost:8080/blog/index.php

I had already installed MySQL 5.0.22 a while back, along with useful tools such as the accompanying Administrator, Query Browser and Migration Toolkit, but had not really used any of it in anger since.Consequently, it took a few minutes to remember what I'd chosen as the root password for the MySQL Administratior package! Eventually, I set up my database (all very straightfoward with the Admin plugin, you don't have to go messing about with SQL statements to make a new table etc, it's all done from a neat little GUI). I'd also got PHP 5.2.0 installed by now.

Connecting To The Database
The next hurdle came when I tried actually running a PHP page with a database connection. I kept getting an error:

Call to undefined function mysql_connect()

After some reading around in my book Beginning PHP and MySQL 5 book (another one which has been propping up the coffee table of late but came into it's own for this), it turns out that PHP5 does not ship with native MySQL support embedded; you have to download some extra libraries and then go fiddling around with the php.ini file. I found this tutorial page really useful in explaining what was needed. And for all the knocking that Microsoft gets in various quarters, I don't ever remember this much effort being required to set up IIS to run with the .NET framework! Bah, humbug.

Once the environment was properly configured, the actual blog development wasn't too bad. I had a few "moments" of frustration trying to chase down some syntax typos which caused various things to blow up, but you get used to that with hand coding!

Telling The Time
Another tricky thing to get right is date and time formatting. My PHP book gave me info if you want to use PHP to display the current date:

<?php echo "".date("l, jS F, Y"); ?>

Gives you "Wednesday, 6th December 2006".

There are occasions when you want to format the date in the SQL statement, and trying to get your head round a seemingly-arbitrary set of case-sensitive parameters in the format string is difficult. Which is where Dan Winchester's guide to MySQL date_format was also very handy. You might use something like this:

SELECT post_id, title, post
DATE_FORMAT(postdate, '%W, %D %M %Y') AS dateposted, DATE_FORMAT(postdate, '%H:%i') AS timeposted
FROM posts WHERE post_id=$post_id LIMIT 1

dateposted would display "Wednesday, 6th December 2006" as before, and the timeposted variable shows "22:45". I split these in two so the parsed date string could be displayed separately from the time portion - if you made two posts in a day, it's nice not to repeat the day/date element every time. If you wanted to lump them together, just use this instead:

SELECT post_id, title, post
DATE_FORMAT(postdate, '%W, %D %M %Y' at %H:%i) AS dateposted
FROM posts WHERE post_id=$post_id LIMIT 1

This will give "Wednesday, 6th December 2006 at 22:25" as one string.

Future Developments?
So now, I have my own blogging engine running on my localhost using PHP and MySQL. I'm not about to share the new blog with the world, as it largely consists of a personal diary and various rants, but it's been a very worthwhile exercise in dipping my toes in the murky PHP waters.

I may decide to develop the code further, and perhaps use it to host this blog on my own server in due course, but for the moment, it's staying right here at blogger.