Brain Dump – Database Issues

[Warning…. Entering a slightly geeky zone]

A couple of few things have cropped up in the past two days that have caused a fair bit of hair pulling and have such simple solutions it’s not true.

I’m sure at some point I will need to remember this information so I hope you don’t mind that I put it here for future reference even if it is a bit off topic for this blog.

If you’re not of a technical nature then feel free to fall asleep now… 😉

Problem 1 – Combining multiple SQL queries with sub-select statements (MYSQL).

Recommended: Find the Last inserted Auto increment ID in MYSQL with ASP

It’s taken me ages to find out how to do this (what turns out to...

Read Article »

I have a users table that I wish to join to a table containing the baskets that they have created and the languages of each item the user has selected.

I could have done this using multiple select statements or some kind of array and loop but this proved to be hugely processor intensive – something that we couldn’t have as it the code’s due to run on a shared server.

So, a colleague and I sat down and came up with a rather splendid query, much better than tonnes of evil joins .

Part of the problem also lay in the fact that we wanted a nice tabular layout for the results which wouldn’t work with all of the queries I tried. It was fine for a single language but a right royal mess for table layouts.

Table Layout

Users Table
--------------------------
|User ID   |  User Name  |
--------------------------
Basket Table
---------------------------------------------------------------
|User ID    | Basket ID |  Language ID  | Catalogue Processed |
---------------------------------------------------------------

SQL Statement

SELECT UserTable.UserID, UserName, LanguageID, CatalogueID, Basketid,
(SELECT Count(LanguageID) FROM (BasketTable) where BasketTable.UserID = t.UserID AND 
CatalogueProcessed=1 AND CatalogueID=1 and LanguageID = 1) AS English,
(SELECT Count(LanguageID) FROM (BasketTable) where BasketTable.UserID = t.UserID AND
CatalogueProcessed=1 AND CatalogueID=1 and LanguageID = 2) AS French,
(SELECT Count(LanguageID) FROM (BasketTable) where BasketTable.UserID = t.UserID AND
CatalogueProcessed=1 AND CatalogueID=1 and LanguageID = 3) AS German,
(SELECT Count(LanguageID) FROM (BasketTable) where BasketTable.UserID = t.UserID AND
CatalogueProcessed=1 AND CatalogueID=1 and LanguageID = 4) AS Italian
FROM (UserTable INNER JOIN BasketTable t on UserTable.CD_ID=t.UserID)WHERE 
CatalogueProcessed=1 AND catalogueID=1 GROUP By UserID

Output

 --------------------------------------------------------------------
| UserID | UserName | BasketID | English | French | German | Italian |
 --------------------------------------------------------------------
|   1    |   Katy   |   1234   |    4    |   0    |   2    |   0     |
 --------------------------------------------------------------------
|   2    |   Pierre |   1235   |    0    |   5    |   0    |   4     |
 --------------------------------------------------------------------

Hooray!

Problem 2 – Memo fields being truncated to 255 characters (MS Access).

This one had me stumped for ages. Originally the script did truncate the field in question but it still did so when I removed the truncation – weird! Anyway, after much hair pulling it turned out to be the “SELECT DISTINCT” clause in the SQL statement. I’m not sure if this is a bug in the code or in Access itself and the way it handles DISTINCT but removing it from the SQL Statement returned the full contents of the memo field.

Weird!

Problem 3 – Scheduled Database Backups failing (MYSQL).

This was another annoying one. All user permission were set correctly and the backup ran when manually activated but wouldn’t run as a scheduled task.

It turns out that the run command generated by MYSQL contains an error. Here’s the command auto-generated by MYSQL:

"C:Program FilesMySQLMySQL Administrator 1.1MySQLAdministrator.exe" 
"-UDC:Documents and SettingsKaty WhittonApplication DataMySQL" 
"-c" "-bpTest Backup" "-btC:" "-bxTest Backup"

You can see on the third line there’s a command called “-c”, changing this to “-C” or removing completely allows the backup to run as a scheduled task successfully.

Recommended: Code Upgrades In HTML5 Brought For Front-End Development

The update about the release of new features in HTML5 has drawn all the attention since last year. It is the new generation language for front end developers which is packed with all-new cool features. It has come with a lot of improvements and fresh elements. The developers are still...

Read Article »

Annoying!

[End slightly geeky zone]

Sorry for the off topic brain dump but if I didn’t put it somewhere I’d forget what I’d done!

About The Author

Katy is always trying to be more productive one day at a time! Whether it's analogue, digital, motivational or psychological who'll try any system that will help her get things done and get organised. As well as running FlippingHeck.com, she also loves making music and reviewing things.

• Follow Katy Whitton on:
• 
• 
• 
• 
• 
• 
• 
•