Re: need help with query...
From: Andy Shellam
To: Lamp Lists
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:48:31 PM
Subject: Re: need help with query...
Hi,
>
> Hi Andy,
> the reason I can't use this because fields (columns) in select statement
> (p.first_name, p.last_name,...) are actually dynamically created. In my
> project different client will select different fields to be shown. 99% will
> select first_name, and last_name, but some don't care about date_registered,
> some will need more org data...
>
> actually, it will be more this way:
>
> SELECT {$selected_fields} FROM people p, organization o. addresses a
> WHERE ...
>
> where
> $selected_fields = "p.first_name, p.last_name, o.org_name"
> or
> $selected_fields = "p.first_name, p.last_name, o.org_name, a.address, a.city,
> a.state, a.zip"
> or
> $selected_fields = "o.org_name, a.address, a.city, a.state, a.zip"
So just tag "AS table_field_name" to each field when you're building your list
of $selected_fields - e.g.
$selected_fields = "p.first_name AS person_first_name, p.last_name AS
person_last_name, o.org_name AS organization_org_name"
You don't have to use the full table name either - for example in the following
statement, you would then access the data using $result['p']['first_name'];
$selected_fields = "p.first_name AS p_first_name, p.last_name AS p_last_name,
o.org_name AS o_org_name"
This approach is actually easier if you're creating the query dynamically,
because you don't have to manually type a load of "AS xxx" statements after
every field.
I've recently done something similar in one of my applications to wrap
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.
Andy
Yup! That'll do it!
Thanks Andy
;-)
Re: need help with query...
From: Andy Shellam
To: Lamp Lists
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:29:08 PM
Subject: Re: need help with query...
Hi Afan
Why not prefix your field names with the table name?
select
p.first_name AS person_first_name,
p.last_name AS person_last_name,
p.status AS person_status,
p.date_registered AS person_date_registered,
o.org_id AS organization_org_id,
o.org_name AS organization_org_name,
o.org_department AS organization_org_department,
a.addres1 AS addresses_address1,
a.address2 AS addresses_address2,
a.city AS addresses_city,
a.state AS addresses_state,
a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
Then in PHP (which I guess you're using from your example) do something like:
// Get every record from the database ($result is your MySQL result from
mysql_query)
while ($row = mysql_fetch_assoc($result))
{
$result = Array();
// Run through each field in the row
foreach ($row as $field => $value)
{
// Split the field into 2 segments split by _
$fieldSplit = explode('_', $field, 1);
// $fieldSplit will be, for example, Array(0 => 'person', 1 =>
'first_name')
$result[$fieldSplit[0]][$fieldSplit[1]] = $value;
// Now you should be able to access the person's first name using
$result['person']['first_name']
}
}
This code may not be perfect as I've just typed it out from memory so it may
take a bit of tweaking.
Thanks,
Andy
Hi Andy,
the reason I can't use this because fields (columns) in select statement
(p.first_name, p.last_name,...) are actually dynamically created. In my project
different client will select different fields to be shown. 99% will select
first_name, and last_name, but some don't care about date_registered, some will
need more org data...
actually, it will be more this way:
SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...
where
$selected_fields = "p.first_name, p.last_name, o.org_name"
or
$selected_fields = "p.first_name, p.last_name, o.org_name, a.address, a.city,
a.state, a.zip"
or
$selected_fields = "o.org_name, a.address, a.city, a.state, a.zip"
I hope I'm more clear now?
Though, I can do something as you suggested while creating $selected_fields
:-)
Thanks
Jason Pruim wrote:
>
> On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
>
>> I hate when somebody put in Subject line something like I just did but after
>> 15 minutes to try to be specific just with one short sentence - I gave up.
>> So, you can hate me - I understand (though, help with my problem too) :-)
>>
>>
>> I have let say 3 tables people, organization, addresses. and they are linked
>> to each other with column person_id.
>> to select person by specified person_id it's not big deal, really simple.
>> though, I wonder how can I select records from these three tables but
>> somehow marked which table it belongs?
>>
>> e.g.
>>
>> select p.first_name, p.last_name, p.status, p.date_registered, o.org_id,
>> o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip
>> from people p, organization o, addresses a
>> where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
>>
>> I need somehow, together with result data, info which table data belogs?
>>
>> e.g.
>> not exactly this way but something like this:
>> $data = array(
>> 'people' => array('lamp', 'lists', 1, '2008-12-12'),
>> 'organization' => array(56, 'Lamp List', 'web'),
>> 'addresses' => array('123 Main St.', 'Toon Town', 'NY', '12345')
>> }
>>
>> thanks for any help.
>>
>> -afan
>
>
> It may not be the best option, but when I've done that type of stuff in the
> past, I add another field to the database and call it like "List" and put
> "People" in the people database. and then you could just query the field List
> and display it how ever you needed.
>
>
> -- Jason Pruim
> japr...@raoset.com
> 616.399.2355
>
>
>
>
need help with query...
I hate when somebody put in Subject line something like I just did but after 15
minutes to try to be specific just with one short sentence - I gave up. So, you
can hate me - I understand (though, help with my problem too) :-)
I have let say 3 tables people, organization, addresses. and they are linked to
each other with column person_id.
to select person by specified person_id it's not big deal, really simple.
though, I wonder how can I select records from these three tables but somehow
marked which table it belongs?
e.g.
select p.first_name, p.last_name, p.status, p.date_registered, o.org_id,
o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
I need somehow, together with result data, info which table data belogs?
e.g.
not exactly this way but something like this:
$data = array(
'people' => array('lamp', 'lists', 1, '2008-12-12'),
'organization' => array(56, 'Lamp List', 'web'),
'addresses' => array('123 Main St.', 'Toon Town', 'NY', '12345')
}
thanks for any help.
-afan
Re: which query solution is better?
- Original Message From: John Hicks <[EMAIL PROTECTED]> To: Lamp Lists <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Tuesday, July 8, 2008 11:20:16 AM Subject: Re: which query solution is better? Lamp Lists wrote: > hi, > I would like to get your opinions regarding which query you think is better > solution and, of course - why. > I have (very simplified example) 3 tables: orders, members and addresses > I need to show order info for specific order_id, > solution 1: > select ordered_by, order_date, payment_method, order_status > > from orders > where order_id=123 > select m.name, a.address, a.city, a.state, a.zip > from members m, addresses a > where m.member_id=$ordered_by and a.address_id=m.address_id > //$ordered_by is value from first query > solution 2: > select ordered_by, order_date, payment_method, order_status, (select m.name, > a.address, a.city, a.state, a.zip from members m, addresses a where > m.member_id=ordered_by and a.address_id=m.address_id) > > from orders > where order_id=123 > (queries are written without testing and maybe it doesn't work exactly, but > it's more to gave you the idea what I'm talking about :D) > also,what if I have to list 20,50 or 100 orders instead one order? would be > subquery still be an option? > thanks for any opinion. > -ll > I don't understand what syntax you're using for your second solution. Your first solution uses two separate queries which will accomplish the task. They could be combined into a single query like this: select * from orders left join members on member_id = ordered_by left join addresses on addresses.address_id = members.address_id where order_id = 123 -- john sorry john. my bad. I should test the query before I post it because it doesn't work that way :D though, let me modify the question: solution 1: select o.ordered_by, o.order_date, o.payment_method, o.order_status, concat(m.first_name, ' ', m.last_name) name left join members m on m.member_id=o.ordered_by from orders o where o.order_id=123 vs. select o.ordered_by, o.order_date, o.payment_method, o.order_status, (select concat(first_name, ' ', last_name) name from members where member_id=o.registered_by) name from orders o where o.order_id=123 in first solution there is join and in second subquery. what's better and why? sorry for this mess :D -ll
which query solution is better?
hi, I would like to get your opinions regarding which query you think is better solution and, of course - why. I have (very simplified example) 3 tables: orders, members and addresses I need to show order info for specific order_id, solution 1: select ordered_by, order_date, payment_method, order_status from orders where order_id=123 select m.name, a.address, a.city, a.state, a.zip from members m, addresses a where m.member_id=$ordered_by and a.address_id=m.address_id //$ordered_by is value from first query solution 2: select ordered_by, order_date, payment_method, order_status, (select m.name, a.address, a.city, a.state, a.zip from members m, addresses a where m.member_id=ordered_by and a.address_id=m.address_id) from orders where order_id=123 (queries are written without testing and maybe it doesn't work exactly, but it's more to gave you the idea what I'm talking about :D) also,what if I have to list 20,50 or 100 orders instead one order? would be subquery still be an option? thanks for any opinion. -ll
Re: can't find ft-min_word_len in /etc/my.conf ?
Thanks. Though, I added, repair table, restarted mysql - and the same :( -ll --- Warren Young <[EMAIL PROTECTED]> wrote: > Lamp Lists wrote: > > I need to change ft_min_word_len fro 4 to 3. the > > proces is very well explained on mysql.com > > though, when open /etc/my.conf can't find the > > ft_min_word_len line? > > If a value for a configurable isn't given in my.cnf, > it takes the > default value. So, add the line, restart the > server, and it will > override the default. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can't find ft-min_word_len in /etc/my.conf ?
hi, I need to change ft_min_word_len fro 4 to 3. the proces is very well explained on mysql.com though, when open /etc/my.conf can't find the ft_min_word_len line? when check is there: mysql> show variables like 'ft_min_word_len' ft_min_word_len4 am I looking at the wrong file or something? thanks. -ll Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results between FULLTEXT search and LIKE search
--- Lamp Lists <[EMAIL PROTECTED]> wrote:
>
> --- Lamp Lists <[EMAIL PROTECTED]> wrote:
>
> > hi,
> > I created table "tasks"
> > create table tasks(
> > task_id, int(4) not null primary key,
> > task text not null,
> > resolution text not null,
> > fulltext (task, resolution)
> > )engine=myisam
> >
> > when I run
> >
> > seect * from tasks match(task,resolution)
> > against('"certain service"' in boolean mode)
> >
> > I would get one record and the phrase is in
> > resolution
> > column.
> >
> > though, when I serach using LIKE
> > select * from tasks where task like '%certain
> > service%' or resolution like '%certain service%'
> > I would get 2 records. one record is the same as
> the
> > one above and the 2nd has the phrase in task
> column.
> > means there are 2 records, but fulltext shows me
> > only
> > one.
> >
> > what I'm doing wrong?
> >
> > thanks.
> >
> > -ll
> >
>
>
> just made 2nd test and got different reault too:
>
> select * from tasks match(task,resolution)
> against('+certain +service' in boolean mode)
> result: 232 records
>
> select * from tasks where (task like '%certain%' and
> task like '%service%') or (resolution like
> '%certain%'
> and resolution like '%service%')
> result: 7 records
>
> ?!?!!??
>
> -ll
>
>
>
>
>
>
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile. Try it now.
>
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
>
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
even worse:
select count(*) from tasks match(task,resolution)
against('certain')
result: 0
select count(*) from tasks where task like '%certain%'
or resolution like '%certain%';
result: 173
?
-ll
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results between FULLTEXT search and LIKE search
--- Lamp Lists <[EMAIL PROTECTED]> wrote:
> hi,
> I created table "tasks"
> create table tasks(
> task_id, int(4) not null primary key,
> task text not null,
> resolution text not null,
> fulltext (task, resolution)
> )engine=myisam
>
> when I run
>
> seect * from tasks match(task,resolution)
> against('"certain service"' in boolean mode)
>
> I would get one record and the phrase is in
> resolution
> column.
>
> though, when I serach using LIKE
> select * from tasks where task like '%certain
> service%' or resolution like '%certain service%'
> I would get 2 records. one record is the same as the
> one above and the 2nd has the phrase in task column.
> means there are 2 records, but fulltext shows me
> only
> one.
>
> what I'm doing wrong?
>
> thanks.
>
> -ll
>
just made 2nd test and got different reault too:
select * from tasks match(task,resolution)
against('+certain +service' in boolean mode)
result: 232 records
select * from tasks where (task like '%certain%' and
task like '%service%') or (resolution like '%certain%'
and resolution like '%service%')
result: 7 records
?!?!!??
-ll
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
different results between FULLTEXT search and LIKE search
hi,
I created table "tasks"
create table tasks(
task_id, int(4) not null primary key,
task text not null,
resolution text not null,
fulltext (task, resolution)
)engine=myisam
when I run
seect * from tasks match(task,resolution)
against('"certain service"' in boolean mode)
I would get one record and the phrase is in resolution
column.
though, when I serach using LIKE
select * from tasks where task like '%certain
service%' or resolution like '%certain service%'
I would get 2 records. one record is the same as the
one above and the 2nd has the phrase in task column.
means there are 2 records, but fulltext shows me only
one.
what I'm doing wrong?
thanks.
-ll
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to re-index a table?
hi, I have table, something like: create table example ( ex_id integer(8) not null auto_increment primary key, ex_col1 int(8) null, ex_col2 int(4) not null, index(ex_col1), index(ex_col2) )engine=myisam; ex_col1 and ex_col2 are indexed separately. Now I need to make one index with these to columns. Using MySQL Browser I did it no problem. I think I have to re-index them now, right? How? thanks for any help. -a Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
