Ok, was wondering if somebody could help me with a bit of mysql. A query from multiple tables returns a table with the same titles for some of the columns, is there any way to change this (i.e. friendly names, i'm sure I have done this before)? If anybody is wondering I am editing the phpbb memberlist to include just what I want and data from other mods stored in different tables. E.g. SELECT a.name, a.data, b.data FROM a, b WHERE a.name = b.name; Returns: | name | data | data | I want: | name | whatever1 | whatever2 | I have searched around a little and havn't found anything yet. Like I said, I'm sure I have done this before but I don't use databases much and can't remember how. Cheers.
Figured it out (using aliases): SELECT a.name, a.data AS whatever1, b.data AS whatever2 FROM a, b WHERE a.name = b.name; (AS is optional, works without)
Got another problem now, for some of the 'names' (the way the table is joined) there is no 'whatever2' and so none of the data is appearing in the results table. Is there a way to display 'name' and 'whatever1' and then 'whatever2' if it exists else just a blank cell, i.e. Code: | name | whatever1 | whatever | ------------------------------------ | bob | edinburgh | tollcross | | bill | edinburgh | | <- empty cell, normally this row wouldn't appear | pob | glasgow |city center| Cheers.
I tried to make an example rather than posting the query because it isn't as straightforward as the question seems (the xdata mod stores the data differently) but here it is anyway: SELECT p.username, p.user_id, p.user_viewemail, p.user_posts, p.user_regdate, p.user_from, p.user_website, p.user_email, p.user_icq, p.user_aim, p.user_yim, p.user_msnm, p.user_avatar, p.user_avatar_type, p.user_allowavatar, d1.xdata_value fname, d2.xdata_value sname, d3.xdata_value company, d4.xdata_value mobile FROM phpbb_users AS p, phpbb_xdata_data AS d1, phpbb_xdata_data AS d2, phpbb_xdata_data AS d3, phpbb_xdata_data AS d4 WHERE p.user_id = d1.user_id AND p.user_id = d2.user_id AND p.user_id = d3.user_id AND p.user_id = d4.user_id AND d1.field_id = 17 AND d2.field_id = 18 AND d3.field_id = 16 AND d4.field_id = 12 ORDER BY $order_by"; Zoomer, I'll give that a go now. Thanks all...
Where it is going wrong is sometimes there is no "d4.field_id = 12" the other values are complusary so will always exist and when there is no "d4.field_id = 12" that "user_id" does not appear in the results...
I'm having trouble understanding quite what you expect from the query, what you'd like it to be doing. Unless I'm mistaken (and given the level of confusion I feel, it's more than possible) you wan't all rows where d4.field is equal to '12' to be selected, but also some that aren't?
The main problem is that the first table stores data like: Code: user_id | username | user_email | ---------------------------------------- 01 | bob | [email]bob@notmail.com[/email] | 02 | Bill | [email]bill@notmail.com[/email] | and the second table stores it like: Code: user_id | field_id | xdata_value | ---------------------------------- 01 | 12 | 07399453265 | 01 | 16 | tesco | 01 | 17 | robert | 01 | 18 | smith | 02 | 16 | tesco | 02 | 17 | will | 02 | 18 | brown | So in the first table I am picking columns out of a row, in the second I am picking rows out of a column... Now the query I have posted picks out all relevent data but if, as in some cases, one of the entries is none-existant none of the data shows up in the results table. This only hapens with field_id = 12 (mobile number) because the others are mandetory. E.g. user_id = 02 & field_id = 12 is non existant, this causes none of the data for user 02 to show in the results: Code: user_id | username | user_email | fname | sname | mobile | ---------------------------------------------------------------- 01 | bob | [email]bob@notmail.com[/email] | robert| smith | 0781.. | What I want is for the user still to show and that value just be blank: Code: user_id | username | user_email | fname | sname | mobile | ---------------------------------------------------------------- 01 | bob | [email]bob@notmail.com[/email] | robert| smith | 0781.. | 02 | Bill | [email]bill@notmail.com[/email] | will | brown | | Is that any clearer? Thanks
Argh. Well from a data level, the way you're doing things is terrible - but I suspect that's more down to wishing to use some form of phpbb generic storage method, rather than knowingly wishing to inflict more of a problem on to yourself What happens when you remove the AND d3.field_id = 16 from the query? And what application will this data be transferred to, something like PHP or simmilar? Given the varitable complexity of this, due to the fact that there's wealth of data redundancy and it's no where near third normal form, trying to get everything perfect at the database syntax level is a bit of a falseity - as the database syntax stuff is more geared up to actually servicing data structures that make sense etc.. whereas you could code a wealth of PHP-ness to format and manipulate your data in any which way.
I know it's terrible, but I'm using a premade mod (phpbb's xdata) and trying to fit what I want in with both phpbb and xdata ways of doing things. At The moment I've only had to change a little code (the sql query, extraction of data from the query results, generation of table and the template to display the table) which means it's still very compatable with other mods. Sorry, d3.field_id = 16 is one of the mandetory ones, it's d4.field_id = 12 thats the optional one. If 'AND d4.field_id = 12' is removed then the row for that user_id is repeated for every value of xdata_value. At the moment I'm thinking of changing the xdata mod to still create a row for each field_id even if the entry is blank, this way the query would work as it is. Oh yeah, this data is being passed to php (it's phpbb's memberlist I'm trying to edit with additional data from the xdata mod).
Ah, added the conditions into the left join rather than the overall query and part of it is working, time to add all the other bits... Cheers
Final query: SELECT p.user_id, p.username, p.user_viewemail, p.user_posts, p.user_regdate, p.user_from, p.user_website, p.user_email, p.user_icq, p.user_aim, p.user_yim, p.user_msnm, p.user_avatar, p.user_avatar_type, p.user_allowavatar, d1.xdata_value fname, d2.xdata_value sname, d3.xdata_value company, d4.xdata_value mobile FROM phpbb_users p LEFT JOIN phpbb_xdata_data d1 ON p.user_id = d1.user_id AND d1.field_id = 17 LEFT JOIN phpbb_xdata_data d2 ON p.user_id = d2.user_id AND d2.field_id = 18 LEFT JOIN phpbb_xdata_data d3 ON p.user_id = d3.user_id AND d3.field_id = 16 LEFT JOIN phpbb_xdata_data d4 ON p.user_id = d4.user_id AND d4.field_id = 12 WHERE p.user_id > 0 ORDER BY $order_by Thanks for the help...
