Wednesday 20 May 2009

Baws deep in SQL once more

So, I've installed XAMPP and I'm pretty sure it all works. I've got PHP & MySQL for Dummies on my lap and a spreadsheet with all the MPs expenses and majorities data.

I used to dabble with SQL in my last job, using Open Office Base to try to create an MRPII system, but that was six months ago, things are different now.

So I've got me one new table, which looks a little like this:-

ID
Name
Attribute
Value
1
Afriyie, Adam
Constituency
Windsor
2
Burns, Simon
Constituency
Chelmsford
3
Cameron, David
Party
Conservative
4
Streeter, Gary
Constituency
Devon, SW
5
Abbott, Dianne
2005 Majority
7427
6
Wyatt, Derek
Expense fiddle link
http://www.telegraph.co.uk/news/newstopics/mps-expenses/5330808/Labour-MP-Derek-Wyatt-billed-75p-for-scotch-eggs-MPs-expenses.html

I think keeps it all kind of simple, rather than having to add columns or create new tables everytime something new happens. Its probably some technique they teach you in first year database management, I swear I read some techy article about this sort of way of arranging databases years and years ago.

Anyhoo, whilst its easy to do a wee
SELECT Name, Value FROM mpinfo WHERE 'Attribute' = 'Constituency' ORDER BY Name
or
SELECT Name, Value FROM mpinfo WHERE 'Attribute' = 'Party' ORDER BY Party, Name
to get lists of each homogenous atrribute. I'm wanting to display a table which has columns for 'MPs name', 'Constituency', 'Party' and whatever else.

Lazyweb, can you help?

It should simply be a case of joining the results of the two previous SQL queries
SELECT Name, Value FROM mpinfo WHERE 'Attribute' = 'Constituency'
LEFT JOIN
SELECT Name, Value FROM mpinfo WHERE 'Attribute' = 'Party'
ON Name=Name
But that's clearly not going to work, so can I run an SQL query that creates two new tables, one for each of the previous queries, then joins them, spits forth the desired information, and then drops the tables.

And can I do this within phpMyAdmin?

1 comment:

  1. The price of 'keeping the table simple' is that you then have to do all sorts of mad things, such as creating temporary tables and then dropping them again, to get the results you want. Go ahead and add more columns to the table. It doesn't matter if many, or even most, are blank or NULL.

    Then your query is kept simple (SELECT * from mpinfo) and you don't go mental trying to figure it out.

    ReplyDelete