Increase for 1 using REPLACE function
I have to increase 'no_of_visits' for 1. Using UPDATE function is easy: update visits set no_of_visits=no_of_visits+1 where visitor_id=123 but, how it should be (if possible at all) if I want to use REPLACE function? I tried something similar replace into visitors values ($visitor_id, (no_of_visits+1)) but it doesn't work?!? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] multiple choice dropdown box puzzle
PJ wrote:
I think this is a tough one... and way above my head:
PLEASE READ ALL OF THE ABOVE TO UNDERSTAND WHAT I AM TRYING TO DO.
Having a bit of a rough time figuring out how to formulate php-mysql to insert
data into fields using a multiple dropdown box in a form.
to post I am using the following:
snip...
$categoriesIN = $_POST[categoriesIN];
...snip...
select name=$categoriesIN[] multiple=multiple
OPTIONChoose Categories.../option
OPTION VALUE=? echo $categoriesIN; ?1
OPTION VALUE=? echo $categoriesIN; ?2
OPTION VALUE=? echo $categoriesIN; ?3
OPTION VALUE=? echo $categoriesIN; ?4
OPTION VALUE=? echo $categoriesIN; ?5
/SELECT
...snip...
$sql4 = FOR ( $ii = 0 ; $ii count($categoriesIN) ; $ii++ )
INSERT INTO temp (example) $categoriesIN[$ii] ;
$result4 = mysql_query($sql4, $db);
...snip
this does not work! The other posts work like a charm... but this...
I cannot figure out what I should be entering where... I have tried several
different configurations, but nothing seems to work...
I found this as a model for entering the selections but can't figure out how to
modify it for my needs:
select name=branch_no[] multiple=multiple size=5
option Choose your location(s) /option
option value=31003100/option
option value=31053105/option
option value=3503 3503/option
option value=3504 3504/option
/select
What I would like to do is something like the following:
select name=$categoriesIN[] multiple=multiple
OPTIONChoose Categories.../option
OPTION VALUE=1History
OPTION VALUE=2Temples
OPTION VALUE=2Pharaohs and Queens
OPTION VALUE=4Cleopatra
OPTION VALUE=4Mummies
/SELECT
and going further, I would like to be able to use a table that actually holds
these values to feed them to the code above. I am sure this is possible but it
must take some huge knowledge and experience to do it.
BUT ...
as I look at things, I am wondering if the FOR statement in the above should be
used to do several INSERTs, that is, one $sql(number) per selected category...
now, would that require many $sqls or many INSERTs within the $sql ?
first, I think, $categoriesIN is string, but in the form you made it as
an array $categoriesIN[]. I think you have to modify it a little bit,
something like {$categoriesIN}.'[]'
second, I think the php part FOR ( $ii = 0 ; $ii count($categoriesIN)
; $ii++ ) can't be part of the mysql statement, it should be outside
the statement
FOR ( $ii = 0 ; $ii count($categoriesIN) ; $ii++ )
{
$sql4 = INSERT INTO temp (example) $categoriesIN[$ii] ;
$result4 = mysql_query($sql4, $db);
}
afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CONCAT doesn't work with NULL?
Price, Randall wrote:
Could you use something like this (untried):
SELECT
CONCAT(COALESCE(r.first_name, ''), ' ',
COALESCE(r.last_name,''), '\n',
COALESCE(r.organization, ''), '\n',
COALESCE(r.title,''), '\n',
COALESCE(a.address1, ''), '\n',
COALESCE(a.city, ''), ', ',
COALESCE(a.state,''), ' ',
COALESCE(a.zip, ''), '\n',
COALESCE(r.email,''))
FROM
registrants r,
addresses a
WHERE
r.reg_id=121
this is good. though, if r.title is NULL I'll get an extra empty row on
screen:
john doe
doe, inc.
-- no title, empty row
123 main st.
testtown, TE 12345
also, I would like to hear opinion about the following query:
SELECT o.col1, o.col2, o.col3,
(
SELECT CONCAT_WS('', r.first_name, ' ', r.last_name, '\n',
r.organization, '', r.title, '\n', a.address1, '\n', a.city, ', ',
a.state, ' ', a.zip, '\n', r.email, '\nHome: ', left(r.phone_home, 3),
'-', mid(r.phone_home, 3, 3), '-', right(r.phone_home, 4), '\nWork: ',
left(r.phone_work, 3), '-', mid(r.phone_work, 3, 3), '-',
right(r.phone_work, 4))
FROM registrants r, addresses a
WHERE r.reg_id=o.registered_id
and a.reg_id=r.reg_id
and a.address_type='Business'
LIMIT 1
) as REGISTERED_BY,
pm.payment_method as payment_method_name, f.form_name FORM_NAME
FROM orders o, payment_methods pm, forms f
WHERE o.order_id=.$order_id.
AND pm.pm_id=o.payment_method
AND f.form_id=.$form_id.
is it smart/good way to have subquery this way or solution below is
better one:
SELECT o.col1, o.col2, o.col3, r.first_name, r.last_name,
r.organization, r.title, a.address1, a.city, a.state, a.zip, r.email,
r.phone_home, r.phone_work, pm.payment_method as payment_method_name,
f.form_name FORM_NAME
FROM orders o, payment_methods pm, forms f
WHERE o.order_id=.$order_id.
AND pm.pm_id=o.payment_method
AND f.form_id=.$form_id.
AND r.reg_id=o.registered_id
AND a.reg_id=r.reg_id
AND a.address_type='Business'
in this case I have to create string REGISTERED_BY by php.
reason I did it as example 1 is because I read in few books and online
people saying do whatever you can using query in mysql rather then using
php. it's faster, better, more secure,...
thoughts?
-afan
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
-Original Message-
From: Afan Pasalic [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 14, 2008 11:53 AM
To: mysql@lists.mysql.com
Subject: CONCAT doesn't work with NULL?
hi,
I have query
SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization,
'\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ',
a.zip, '\n', r.email)
FROM registrants r, addresses a
WHERE r.reg_id=121
if any of columns has value (e.g. title) NULL, I'll get as result 0
records.
If query doesn't have concat() - it works fine.
Why is that?
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reorder records in database
hi, I have a table with tasks. column status could be 1 (means todo) and 0 (meas done). also,have column order_no to sort tasks by priorities. once in a while order_no is not in order, e.g 1, 2, 3, 5, 6, 8, 11, 12, 13, 19, 20,... (some numbers are missing). is there built in function to reset order_no or I have to create php script for it? thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CONCAT doesn't work with NULL?
hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
Thanks Ewen, that's what I was looking for! :D -afan ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
First, I want to thank to everybody on such afast respond. Thank you. Second, what would be difference between concat_ws and the Randalll's solution (bellow)? -afan Price, Randall wrote: Could you use something like this (untried): SELECT CONCAT(COALESCE(r.first_name, ''), ' ', COALESCE(r.last_name,''), '\n', COALESCE(r.organization, ''), '\n', COALESCE(r.title,''), '\n', COALESCE(a.address1, ''), '\n', COALESCE(a.city, ''), ', ', COALESCE(a.state,''), ' ', COALESCE(a.zip, ''), '\n', COALESCE(r.email,'')) FROM registrants r, addresses a WHERE r.reg_id=121 Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 -Original Message- From: Afan Pasalic [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 14, 2008 11:53 AM To: mysql@lists.mysql.com Subject: CONCAT doesn't work with NULL? hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
actually, this will not work for me (or I got it wrong :D) because I need to have street, state and zip in one line and with separator defined on the beginning it will put everything in separate lines. :D ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I need to add to content somehow
Matthew Stuart wrote:
I have a DB that has a field in it that currently just holds single or
double numbers - these numbers are basically a reference to a category
in which the particular record should be displayed. However I have now
been asked if I can make it so that a particular record can be displayed
in more than one category.
All I need to do is somehow ask the field to add a forward slash to the
front and end of the data, so the data will go from this:
1
33
21
9
11
to this:
/1/
/33/
/21/
/9/
/11/
How do I get MySQL to do this? I guess I might have to do it in two
steps by firstly adding the slash to the front and then lastly to the
back. But I have no idea on how to do it. My reason for doing this is so
that I get the webpage to look for numbers that are between the slashes.
this will enable me to have numbers like this: /1/33/9/ enabling me to
have a record in more than one category.
Any help would be appreciated.
Thanks
Mat
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
First, I would like to suggest you to rethink about your whole idea
because you are going wrong directions.
It' possible to do it your way but it's not correct/the best way. For
example, one day you will need to select all products for specific
category and then you have to use LIKE to find them - what's wrong.
Think about idea to create new table in_categories with only 2
columns: prod_id and cat_id:
prod_id| cat_id
123 | 1
123 | 33
123 | 9
45 | 33
28 | 33
If you still want to do it your way, I'll suggest to use comma instead /
1,33,9 (even it really doesn't change a lot) and don't put / at the
front and at the end: 1/33/9
explode('/', '/1/33/9/'); will create 5 elements of the array
explode('/', '1/33/9'); will create 3 elements of the array - the number
you need.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I need to add to content somehow
Matthew Stuart wrote:
I have a DB that has a field in it that currently just holds single or
double numbers - these numbers are basically a reference to a category
in which the particular record should be displayed. However I have now
been asked if I can make it so that a particular record can be displayed
in more than one category.
All I need to do is somehow ask the field to add a forward slash to the
front and end of the data, so the data will go from this:
1
33
21
9
11
to this:
/1/
/33/
/21/
/9/
/11/
How do I get MySQL to do this? I guess I might have to do it in two
steps by firstly adding the slash to the front and then lastly to the
back. But I have no idea on how to do it. My reason for doing this is so
that I get the webpage to look for numbers that are between the slashes.
this will enable me to have numbers like this: /1/33/9/ enabling me to
have a record in more than one category.
Any help would be appreciated.
Thanks
Mat
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
actually, forgot to answer on your question:
if your table name is your_table and column with categories is named
categories:
UPDATE your_table SET categories=CONCAT('/', categories, '/');
it will update your whole table at once.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update but insert if not exist
try REPLACE http://dev.mysql.com/doc/refman/5.1/en/replace.html -afan Steffan A. Cline wrote: I am trying to think of a trick way to handle something. I have been successful in using the multiple inserts in one row by using the ,(xx,xx,xx),(xx,xx,xx),(xx,xx,xx) in a values block. BUT Is it possible to insert if not there for example Update if anyone not found then insert new with same criteria as update Where region_id in (2,3,4,5,6) Thanks Steffan --- T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 YAHOO : Steffan_Cline MSN : [EMAIL PROTECTED] GOOGLE: Steffan.Cline Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
something like ENUM - but it's not ENUM
hi,
I wonder if mysql has something like ENUM() but instead ONLY ONE it
could be selected ANY COMBINATION of these offered solutioins?
If column is ENUM('a','b','c','d') I can select 'a' or 'b' or 'c' or
'd'. Clear.
Tough, I need SOMETHING('a','b','c','d') that I can select 'a' and 'b',
or 'b', 'c' and 'd', or all of them (but must at least one)? Does exists
something like this in mysql?
thanks,
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: something like ENUM - but it's not ENUM
yes. just found it.
it's funny, I was looking for it last half hour and the second after I
sent the email - I found it.
:)
yes, yes... I know. RTFM
:D
thanks.
-afan
sol beach wrote:
SET Column type?
On Dec 6, 2007 2:01 PM, Afan Pasalic [EMAIL PROTECTED] wrote:
hi,
I wonder if mysql has something like ENUM() but instead ONLY ONE it
could be selected ANY COMBINATION of these offered solutioins?
If column is ENUM('a','b','c','d') I can select 'a' or 'b' or 'c' or
'd'. Clear.
Tough, I need SOMETHING('a','b','c','d') that I can select 'a' and 'b',
or 'b', 'c' and 'd', or all of them (but must at least one)? Does exists
something like this in mysql?
thanks,
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Browser - limit 1000 by default?
hi barry, this is on Linux version of MySQL Query Browser. I need the same on Win version. But, there is no such a solution (Tool Option ...). :( -afan barry wrote: I'm assuming you're talking about the Mysql Query Browser? You can change the number of records under Tools- Preferences and changing the Max Rows For Generated Queries to whatever you want, set to zero removes the limit entirely. On Sat, 2007-12-01 at 15:17 -0600, Afan Pasalic wrote: Hi, on Linux version of MySQL Browser (v 1.2.4 beta), when double-click on any table, default query is SELECT * FROM table_name LIMIT 0,1000 On Win version (v 1.2.9 rc), there is no LIMIT part - what caused me to pull so many times tens, even hundreds thousands of records. I was looking for in setting for this feature, but wasn't able to find. is there way to set the limit value on default select query, on win version? thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Browser - limit 1000 by default?
Hi, on Linux version of MySQL Browser (v 1.2.4 beta), when double-click on any table, default query is SELECT * FROM table_name LIMIT 0,1000 On Win version (v 1.2.9 rc), there is no LIMIT part - what caused me to pull so many times tens, even hundreds thousands of records. I was looking for in setting for this feature, but wasn't able to find. is there way to set the limit value on default select query, on win version? thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need query: records inserted on Monday?
Hi, I have to build a report - when (date and/or time) the records are inserted. E.g., number of records inserted on Monday - doesn't matter what month. Or, number of records inserted on specific date between 8am and 4pm. Thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need query: records inserted on Monday?
Yup! That's it! Thanks Dan. ;) The link is really helpful. I was looking for it on mysql but was able to find. Looks like I didn't try hard. :) -afan Dan Buettner wrote: Afan, you'll need to have a date and time column in the database storing a created at value, that is set on INSERT and then not changed. Assuming you have such a column - let's call it created_at - you can run queries like this: /* to get count of records created on Mondays */ SELECT COUNT(*) FROM table t WHERE DAYOFWEEK(t.created_at) = 2; /* to get count created on a given date between 8 AM and 4 PM */ SELECT COUNT(*) FROM table t WHERE t.created_at = 2007-11-20 8:00 AND t.created_at = 2007-11-20 16:00; MySQL's docs on date and time functions are here: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html HTH, Dan On Nov 20, 2007 12:16 PM, Afan Pasalic [EMAIL PROTECTED] wrote: Hi, I have to build a report - when (date and/or time) the records are inserted. E.g., number of records inserted on Monday - doesn't matter what month. Or, number of records inserted on specific date between 8am and 4pm. Thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how ti put several records of one mysql table in one row of html table?
hi,
I have standard organizations table with org_id, name, address,
city,... columns.
CREATE TABLE `organization` (
`organization_id` int(8) unsigned NOT NULL default '0',
`address_id` int(8) unsigned default NULL,
`full_name` varchar(255) default NULL,
`phone` varchar(255) NOT NULL default '',
`fax` varchar(10) default NULL,
`parent_org_id` int(8) default NULL,
`website` varchar(45) default NULL,
`country` varchar(45) default NULL,
PRIMARY KEY (`organization_id`)
) ENGINE=MyISAM
+-++-++++
| organization_id | address_id | full_name | phone | fax|
website|
+-++-++++
|8200 | 1 | 1520 | 212245 | 2122457730 |
http://www.abcinc.com |
+-++-++++
I have also custom_fields table
CREATE TABLE `custom_fields` (
`field_id` int(4) NOT NULL,
`field_display` varchar(100) character set latin1 NOT NULL,
`field_type` enum('text','date') character set latin1 NOT NULL default
'text',
`field_order` int(3) unsigned default NULL,
`choices` text character set latin1,
PRIMARY KEY (`field_id`)
) ENGINE=MyISAM
*** 1. row ***
field_id: 12
field_display: Start Date
field_type: date
field_order: 2
choices:
*** 2. row ***
field_id: 13
field_display: Cancel Date
field_type: date
field_order: 4
choices:
*** 3. row ***
field_id: 14
field_display: Membership Type
field_type: text
field_order: 6
choices: Large Member,Small Member,Associate Member,Individual Member
*** 4. row ***
field_id: 15
field_display: Referred By
field_type: text
field_order: 8
choices:
and custom field values table
CREATE TABLE `custom_field_values` (
`organization_id` int(8) NOT NULL,
`field_id` int(4) NOT NULL,
`cust_field_value` varchar(255) default NULL,
PRIMARY KEY (`organization_id`,`field_id`)
) ENGINE=MyISAM
mysql select organization_id, field_id, cust_field_value from
dir_custom_field_values where instance_id=12 and organization_id=8200;
+-+--+--+
| organization_id | field_id | cust_field_value |
+-+--+--+
|8200 | 12 | 2005-04-01 |
|8200 | 14 | Small Member |
|8200 | 16 | 1-4 |
|8200 | 21 | Retail |
+-+--+--+
I have to make a list (on screen, as html table) of organizations with
custom fields as a part of the table, e.g.
Org. ID | Org. Name | Org. Address | ... | cust_field_1 | cust_field_2 |
cust_field_3 | ...
but I can't make a query to put several records from custom_field_values
for specific org_id in one row?
Example:
+-++-+++++-+-+
| organization_id | address_id | full_name | phone | fax|
website| start date | cancel date | membership type |
+-++-+++++-+-+
|8200 | 1 | 1520 | 212245 | 2122457730 |
http://www.abcinc.com | 2005-04-01 | 2006-01-01 |Smal Member|
+-++-+++++-+-+
thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how ti put several records of one mysql table in one row of html table?
Yup! That's THE ONE!
Thanks Rajesh.
-afan
Rajesh Mehrotra wrote:
Hi,
I think GROUP_CONCAT will do it.
-Raj.
-Original Message-
From: afan pasalic [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 16, 2007 11:49 AM
To: mysql@lists.mysql.com
Subject: how ti put several records of one mysql table in one row of
html table?
hi,
I have standard organizations table with org_id, name, address,
city,... columns.
CREATE TABLE `organization` (
`organization_id` int(8) unsigned NOT NULL default '0',
`address_id` int(8) unsigned default NULL,
`full_name` varchar(255) default NULL,
`phone` varchar(255) NOT NULL default '',
`fax` varchar(10) default NULL,
`parent_org_id` int(8) default NULL,
`website` varchar(45) default NULL,
`country` varchar(45) default NULL,
PRIMARY KEY (`organization_id`)
) ENGINE=MyISAM
+-++-+++
+
| organization_id | address_id | full_name | phone | fax|
website|
+-++-+++
+
|8200 | 1 | 1520 | 212245 | 2122457730 |
http://www.abcinc.com |
+-++-+++
+
I have also custom_fields table
CREATE TABLE `custom_fields` (
`field_id` int(4) NOT NULL,
`field_display` varchar(100) character set latin1 NOT NULL,
`field_type` enum('text','date') character set latin1 NOT NULL default
'text',
`field_order` int(3) unsigned default NULL,
`choices` text character set latin1,
PRIMARY KEY (`field_id`)
) ENGINE=MyISAM
*** 1. row ***
field_id: 12
field_display: Start Date
field_type: date
field_order: 2
choices:
*** 2. row ***
field_id: 13
field_display: Cancel Date
field_type: date
field_order: 4
choices:
*** 3. row ***
field_id: 14
field_display: Membership Type
field_type: text
field_order: 6
choices: Large Member,Small Member,Associate Member,Individual
Member
*** 4. row ***
field_id: 15
field_display: Referred By
field_type: text
field_order: 8
choices:
and custom field values table
CREATE TABLE `custom_field_values` (
`organization_id` int(8) NOT NULL,
`field_id` int(4) NOT NULL,
`cust_field_value` varchar(255) default NULL,
PRIMARY KEY (`organization_id`,`field_id`)
) ENGINE=MyISAM
mysql select organization_id, field_id, cust_field_value from
dir_custom_field_values where instance_id=12 and organization_id=8200;
+-+--+--+
| organization_id | field_id | cust_field_value |
+-+--+--+
|8200 | 12 | 2005-04-01 |
|8200 | 14 | Small Member |
|8200 | 16 | 1-4 |
|8200 | 21 | Retail |
+-+--+--+
I have to make a list (on screen, as html table) of organizations with
custom fields as a part of the table, e.g.
Org. ID | Org. Name | Org. Address | ... | cust_field_1 | cust_field_2 |
cust_field_3 | ...
but I can't make a query to put several records from custom_field_values
for specific org_id in one row?
Example:
+-++-+++
++-+
-+
| organization_id | address_id | full_name | phone | fax|
website| start date | cancel date | membership type
|
+-++-+++
++-+
-+
|8200 | 1 | 1520 | 212245 | 2122457730 |
http://www.abcinc.com | 2005-04-01 | 2006-01-01 |Smal Member|
+-++-+++
++-+
-+
thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do I need two tables or one will do just fine?
On table it is! :-D Thanks to all of you who helped me with my problem. -afan John Meyer wrote: Afan Pasalic wrote: hi, I have a employees table (first name, last_name, address, city, state, zip, phone,...). though, I got a requested to add additional info about people, like phone_extension, zip+4, nick, DOB... that will not be used very often. what would be better solution: a) add these columns to employees table b) create separate table employees_addition_info with these fields and store info if any (with employee_id of course) one friend of mine suggest me to keep all data in one table since the empty fields will be NULL and there will not be a lot of wasted space. specially because I'll never have more than 200K records (right now I have about 50K records). and normalization will not improve a lot? any suggestions? Keep the one table. Unless you can make the case that all of those attributes can be logically grouped together, I'd keep them in the primary table to eliminate unnecessary joins. And as far as normalizing, you're pretty much setting up tables employee_info_1 and employee_info_2 and when you end the table name or field name with a number, that's a big clue you're not normalizing the data. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
do I need two tables or one will do just fine?
hi, I have a employees table (first name, last_name, address, city, state, zip, phone,...). though, I got a requested to add additional info about people, like phone_extension, zip+4, nick, DOB... that will not be used very often. what would be better solution: a) add these columns to employees table b) create separate table employees_addition_info with these fields and store info if any (with employee_id of course) one friend of mine suggest me to keep all data in one table since the empty fields will be NULL and there will not be a lot of wasted space. specially because I'll never have more than 200K records (right now I have about 50K records). and normalization will not improve a lot? any suggestions? thanks. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
changes in tables (developemnt - production)
hi, on production server, while developing, I did some changes on some tables (I added few new columns on several tables). now, after the new web app is finished and tested, I have to upgrade (php) the app on production server. what steps do you recommend to do the tables update on the production database? 1. backup 2. ??? 3. ??? ... thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
indexing order column
hi, if I have column order_id(int(4)) null do I have to index it too. I'm going to use it ONLY for sorting records. thanks. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexing order column
Baron Schwartz wrote: Hi Afan, Afan Pasalic wrote: hi, if I have column order_id(int(4)) null do I have to index it too. I'm going to use it ONLY for sorting records. It depends a lot on how much data is in the table, etc etc. An index will make sorting more efficient in the general case when you have a decent amount of data. But it's hard to be specific with so little information. Baron I have table products (product_id is PK). I have table categories (cat_id is PK). since the product can be in more than one category, I have prod_cat table: create prod_cat( cat_id int(8) unsigned not null, prod_id int(8) unsigned not null, order_id int(4) unsigned null, PRIMARY KEY (cat_id, prod_id) ) engine=Innodb; would it be enough info? thanks. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexing order column
Baron Schwartz wrote: Hi, Afan Pasalic wrote: Baron Schwartz wrote: Hi Afan, Afan Pasalic wrote: hi, if I have column order_id(int(4)) null do I have to index it too. I'm going to use it ONLY for sorting records. It depends a lot on how much data is in the table, etc etc. An index will make sorting more efficient in the general case when you have a decent amount of data. But it's hard to be specific with so little information. Baron I have table products (product_id is PK). I have table categories (cat_id is PK). since the product can be in more than one category, I have prod_cat table: create prod_cat( cat_id int(8) unsigned not null, prod_id int(8) unsigned not null, order_id int(4) unsigned null, PRIMARY KEY (cat_id, prod_id) ) engine=Innodb; Okay, so your order_id is really sort order, not id of the customer's request to buy something. (As an aside, perhaps sort_order would confuse you less in the future when you don't remember the column's purpose anymore). It probably makes sense to index the column if you want to use it for sorting. You could also just order by the primary key. But I understand there are times when you want to have a different ordering. Baron yes, you're right. sort_order does make more sense :) order by PK, in my case is, let's say impossible because I'm sorting products in ONE category. e.g.: cat_id|prod_id|order_id 1 | 23 | 1 1 | 25 | 2 1 | 36 | 3 1 | 13 | 4 2 | 13 | 1 2 | 45 | 2 2 | 47 | 3 2 | 51 | 4 3 | 32 | 1 3 | 33 | 2 3 | 34 | 3 3 | 35 | 4 -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Workbench
Hi, I'm looking for database modeling tool form MySQL. Anybody used the MySQL Workbench? I know the Workbench is in Alpha production, though some hints? How about DB Designer? Thanks for any respond. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT using SELECT results
INSERT INTO ztipos (type) VALUES (SELECT DISTINCT type FROM locais)
?
-afan
Miguel Vaz wrote:
Hi,
I have a table LOCAIS with:
idtypedesc
1t1blah blah
2t1uihuih
3t2pokpokp
I want to list only the distinct types and create a table with
those results.
I know how to list the distinct and i thought i got the insert
right, but it doesnt work:
INSERT INTO ztipos('type') SELECT DISTINCT type FROM locais
Whats wrong with that command?
I want the result to be:
results table ZTIPOS:
idtype
1t1
2t2
Thanks
Miguel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help to build a query for simple bulletin board
hi,
I'm trying to build a simple bulletin board, just topics and posts.
created following tables:
CREATE TABLE `topics` (
`topic_id` int(8) NOT NULL auto_increment,
`author_id` int(8) unsigned NOT NULL,
`topic_title` varchar(255) collate utf8_unicode_ci default NULL,
`topic_date_entered` datetime default NULL,
`topic_status` enum('live','hidden','locked') collate utf8_unicode_ci
NOT NULL default 'live',
PRIMARY KEY (`topic_id`),
KEY `topic_author` (`topic_author`),
KEY `topic_date_entered` (`topic_date_entered`),
KEY `topic_status` (`topic_status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=3041 ;
CREATE TABLE `posts` (
`post_id` int(4) unsigned NOT NULL auto_increment,
`topic_id` varchar(255) NOT NULL,
`author_id` int(8) unsigned NOT NULL,
`post_date` datetime NOT NULL,
`content` text,
PRIMARY KEY (`post_id`),
KEY `topic_id` (`topic_id`),
KEY `post_date` (`post_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3417 ;
CREATE TABLE `authors` (
`author_id` int(8) unsigned NOT NULL auto_increment,
`Username` varchar(20) NOT NULL default '',
`Password` varchar(20) NOT NULL default '',
`Name` varchar(30) NOT NULL default '',
`Date_Reg` varchar(30) NOT NULL default '',
PRIMARY KEY `Username` (`Username`),
KEY `Username` (`Salesperson_No`),
KEY `Password` (`Password`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I was trying to create the query but wasn't successful.
only thinig I did for now is
$query = mysql_query(
SELECT t.topic_id, t.topic_title, t.author_id,
t.topic_date_entered, a.Name
FROM topics as t
LEFT JOIN authors as a ON (t.author_id=a.author_id)
WHERE t.topic_status = 'live'
ORDER BY t.topic_id DESC
LIMIT 25);
while($result = mysql_fetch_array($query))
{
$query2 = mysql_query(
SELECT COUNT(*) AS counter, MAX(post_date) as post_date,
MAX(post_id) as post_id, author_id
FROM posts
WHERE topic_id = $result['topic_id']
GROUP BY topic_id);
while($result2 = mysql_fetch_array($query2))
{
echo 'b'.$result['topic_title'].'/b by
'.$result['Name'].' '.$result2['counter'].'
'.$result2['post_date'].'br'; # shortened version
}
}
and, of course, it's wrong.
want to have on topics listing: topic's title | author's name | no. of
replies | last post date | last post autor's name.
Thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[SOLVED] Re: #1191 - Can't find FULLTEXT index matching the column list
Hi Sheeri
No, I wanted to search through both columns in the same time (and it
will be always at the same time) - the problem was I didn't know that I
have to have one multi-column index.
:)
But, it's fixed (after really helpful comments on this mailing list) and
works perfect
:)
Thanks to everybody!
-afan
sheeri kritzer wrote:
See response below:
On 5/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
It looks like today is my day! :)
I FULLTEXT indexed my table products:
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '-00-00',
`prod_end_date` date default '-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_name` (`prod_name`),
FULLTEXT KEY `prod_description` (`prod_description`)
) TYPE=MyISAM AUTO_INCREMENT=3367 ;
When I tried this:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
I'll get some results.
But, when I tried this:
SELECT * FROM products
WHERE match (prod_name, prod_description) against ('+red +shirt');
I got this error message:
#1191 - Can't find FULLTEXT index matching the column list
What am I doing wrong?
You put 2 FULLTEXT indexes on different columns, and you're trying to
match against one multi-column index, which doesn't exist. Your table
creation allows:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
SELECT * FROM products
WHERE match (prod_description) against ('+red +shirt');
or
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt')
OR match (prod_description) against ('+red +shirt');
If you want to allow the query you originally wrote, you should have
one multi-column FULLTEXT index, like so:
FULLTEXT KEY `keyname` (`prod_name`,`prod_description`)
But I'm guessing what you want is to change your query -- which allows
you to match on either the name OR the description.
-Sheeri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting all records inserted last 1 minutes?
No, not exactly. More as there is a solution... What would be the best way to do? -afan Paul DuBois wrote: At 20:52 +0200 4/27/06, [EMAIL PROTECTED] wrote: Hi, I was trying to list all registeed users they are online last 15 minutes. I can do it by using timestamp: ?php $current_time = time(); $start_time = ($current_time - 15*60); SELECT * FROM members WHERE last_access = '$start_time' ? But, I know there is something like: SELECT * FROM members WHERE last_access BETWEEN(CURTIME(), INTERVAL 15 MIN) ?!? Thanks for any help. If you mean what is the syntax of BETWEEN?, it's in this section of the manual: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
