| Ricoshady | is there a function to turn a subquery and its results into a array, or concated string say I have select value from table, id like that converted into (value1, value2, etc... ) asuming that select in a subquery of a larger query |
| Xgc | Ricoshady: In what way will you use that *array* in the outer select? Ricoshady: I don't see the need for an array. Try describing the entire query. Ricoshady: But to answer your other question, for what it's worth, see GROUP_CONCAT(field) in the context of a GROUP BY, |
| Ricoshady | well I have a query that selects users. each user belongs to some number of categroies. for each user result, user_id, name, etc. I'd rather have a list of categories using a sub query rather than using a join |
| Xgc | Ricoshady: Without the GROUP BY that will generate the list over the entire set as one group. |
| Ricoshady | the GROUP_CONCAT might just work i can group by the categories |
| Xgc | Ricoshady: GROUP BY userid, category Ricoshady: No. Ricoshady: You want SELECT user_id, MAX(username), GROUP_CONCAT(category) FROM tbl GROUP BY user_id; Ricoshady: That's the strict form. MySQL will allow you to use username, without the aggregate function. But you probably shouldn't have a username in that table. The structure probably should be: tables: users, categories, user_category. |
| Ricoshady | Xgc, worked great thanks cool function im not messing with the data after the query so its better this way |
| Xgc | You're welcome. |
| Ricoshady | Xgc, im not quite getting the results i expected, if I wanted to concat the category, wouldnt I group by the category? |
| imagine | GRANT ALL PRIVILEGES ON [...] WITH GRANT OPTION does that mean that this account can use the GRANT function to make other user? |
| kimseong | Ricoshady: you want all category for every user? Ricoshady: group by the user and group_concat the cat |
| bsmith | if i'm writing an application using jdbc and accepting user input to be inserted, what's the best layer for sanitizing the input? are there standard jdbc or mysql-specific methods i should be using? |
| kimseong | use the java apps or use trigger |
| bsmith | sorry, the java apps? |
| kimseong | your java code |
| bsmith | right, i'm wondering if there is standard built-in functionality i should use before reinventing the wheel |
| kimseong | mysql has no check constraint yet you only have unique constraint for now |
| Xgc | Ricoshady: Since you asked for the list of categories PER user, the SQL is correct. If you meant to ask another question, try again. Try posting the output you expect and also show the output you actually obtained. |
| fatpelt | evening all. i'm trying to use the "grant" to create a user and it tells me "You must have privileges to update tables in the mysql database to be able to change passwords for others" what are the minimum rights in the mysql.* tables that i need to use grant to create users and give them rights to databases ? |
| Xgc | fatpelt: Try ... WITH GRANT OPTION; |
| kimseong | fatpelt: show grants for current_user(); to confirm what priv you currently have |
| fatpelt | let me pastebin some stuff http://www.copypot.com/6417 (pastebin threw a select error) |
| nils_ | bsmith: to my knowing, jdbc uses prepared statements so the input is basically sane. You may need to validate further in your app. |