#mysql - Tue 8 May 2007 between 00:47 and 01:47



Ricoshadyis 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
XgcRicoshady: 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,
Ricoshadywell 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
XgcRicoshady: Without the GROUP BY that will generate the list over the entire set as one group.
Ricoshadythe GROUP_CONCAT might just work
i can group by the categories
XgcRicoshady: 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.
RicoshadyXgc, worked great thanks
cool function
im not messing with the data after the query so its better this way
XgcYou're welcome.
RicoshadyXgc, im not quite getting the results i expected, if I wanted to concat the category, wouldnt I group by the category?
imagineGRANT ALL PRIVILEGES ON [...] WITH GRANT OPTION does that mean that this account can use the GRANT function to make other user?
kimseongRicoshady: you want all category for every user?
Ricoshady: group by the user and group_concat the cat
bsmithif 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?
kimseonguse the java apps or use trigger
bsmithsorry, the java apps?
kimseongyour java code
bsmithright, i'm wondering if there is standard built-in functionality i should use before reinventing the wheel
kimseongmysql has no check constraint yet
you only have unique constraint for now
XgcRicoshady: 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.
fatpeltevening 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 ?
Xgcfatpelt: Try ... WITH GRANT OPTION;
kimseongfatpelt: show grants for current_user(); to confirm what priv you currently have
fatpeltlet 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.

Page: 2 9 16 23 30 37 44 51 58 

IrcArchive