Re: from excel to the mySQL
Hi, Ive always used navicat for mysql and it does wonders for import/export (wether it be from excel or any other file type) and also general database management. Actually, too many features to list here, check the website: http://www.navicat.com/en/products/navicat_mysql/mysql_overview.html Pag On Tue, Aug 3, 2010 at 10:23 AM, HaidarPesebe haidarpes...@gmail.comwrote: Dear All, I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? Thanks HaidarPesebe
Re: table structure problem
Hi, Shawn, Thanks for replying. What i meant is that i would also like to create a table with site types, where i would have a listing of possible sites, like arqueology, natural, etc. and maybe use it to redirect the queries instead of having to hardcode the table name when i need to list a specific type of site. :-) site_types id_site_type name this table would have: 1 arqueology 2 natural 3 generic ... how could i use this to be able to query dynamically? Maybe use the field id_site_type in the table sites as a link? Thanks. Pag PS - Shawn, forgive the duplicate email. I replied only to you first, instead of all. On Mon, Jul 12, 2010 at 4:37 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 7/8/2010 11:29 PM, Miguel Vaz wrote: Hi, I am having some uncertainty while designing the following structure: I have two sets of data: * arqueology sites (can be natural): id name description id_category id_period x y * natural sites (can be arqueological also - bear with me -, so there will be duplicate records in the above table and this): id name description altitude x y and i would like to put these two sites in the same data set and maybe add a new table called site types to categorize each record (maybe a relation table to allow many to many): how can i go about doing it? is this solution decent enough: * sites (generic): id_site name description x y * site_natural id id_site altitude * site_arqueology id id_site id_category id_period But i seem to be missing something. How can i have this in a way that its easy to list only arqueology sites for example. I feel the solution is simple enough, even for me, but its eluding me. Any help in the right direction would be very appreciated. You have done a nice job at normalizing. All of you sites are tagged and identified in one table then specific differentiating details are stored in separate tables. When you ask about listing only the arqueology sites that's just a simple query: SELECT * from site_arqueology inner join sites on sites.id_site = site_arqueology.id_site I can't see where your problem is. Can you provide some additional details? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN
table structure problem
Hi, I am having some uncertainty while designing the following structure: I have two sets of data: * arqueology sites (can be natural): id name description id_category id_period x y * natural sites (can be arqueological also - bear with me -, so there will be duplicate records in the above table and this): id name description altitude x y and i would like to put these two sites in the same data set and maybe add a new table called site types to categorize each record (maybe a relation table to allow many to many): how can i go about doing it? is this solution decent enough: * sites (generic): id_site name description x y * site_natural id id_site altitude * site_arqueology id id_site id_category id_period But i seem to be missing something. How can i have this in a way that its easy to list only arqueology sites for example. I feel the solution is simple enough, even for me, but its eluding me. Any help in the right direction would be very appreciated. Thanks Pag
Re: Count records in join
Thanks all for the feedback. Here's what i did: select p.id_prog,count(r.id_event) e from programas p left join(events r) on(p.id_prog=r.id_prog) group by r.id_event This gives me a list of all the distinct progs with a count of how many events on each. I then delete the empty ones. It would be nice to be able to delete the empty ones on the same query. MV On Wed, Dec 16, 2009 at 1:48 PM, Johan De Meersman vegiv...@tuxera.bewrote: If the aim is purely to find the progs without events, it might be more efficient to use something like select * from progs where not exist (select id_prog from events where id_prog = progs.id_prog); My syntax might be off, check not exists documentation for more info. On Tue, Dec 15, 2009 at 8:14 PM, Gavin Towey gto...@ffn.com wrote: Hi Miguel, You'll need to use LEFT JOIN, that will show all records that match and a row in the second table will all values NULL where there is no match. Then you find all those rows that have no match in your WHERE clause. Regards, Gavin Towey -Original Message- From: Miguel Vaz [mailto:pagong...@gmail.com] Sent: Tuesday, December 15, 2009 10:43 AM To: mysql@lists.mysql.com Subject: Count records in join Hi, I am stuck with a suposedly simple query: - i have two tables (: PROGS id_prog name EVENTS id id_prog name How can i list all records from PROGS with a sum of how many events each have? I want to find the progs that are empty. I remember something about using NULL, but i cant remember. :-P Thanks. MV This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Re: Count records in join
Yes, that would do what you mentioned, show all programs with a count on events, but i need the opposite, show (and delete) all that dont have any events. Well, just have to use IS NULL instead. Thanks. MV On Wed, Dec 16, 2009 at 3:17 PM, Jerry Schwartz jschwa...@the-infoshop.comwrote: -Original Message- From: Miguel Vaz [mailto:pagong...@gmail.com] Sent: Wednesday, December 16, 2009 9:39 AM To: Johan De Meersman Cc: Gavin Towey; mysql@lists.mysql.com Subject: Re: Count records in join Thanks all for the feedback. Here's what i did: select p.id_prog,count(r.id_event) e from programas p left join(events r) on(p.id_prog=r.id_prog) group by r.id_event [JS] Add HAVING COUNT(*) 0 is one way. I haven't been following the thread, but would = SELECT `p`.`id_prod`, COUNT(`r`.`id_event`) `e` FROM `programas` `p` LEFT JOIN `events` r ON `p`.`id_prod` = `r`.`id_prod` WHERE `r`.`id_prod` IS NOT NULL GROUP BY `p`.`id_prod`; = do what you want? That should find only those rows in `programmas` that match rows in `events`, and give you the number of events for each one. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com This gives me a list of all the distinct progs with a count of how many events on each. I then delete the empty ones. It would be nice to be able to delete the empty ones on the same query. MV On Wed, Dec 16, 2009 at 1:48 PM, Johan De Meersman vegiv...@tuxera.be wrote: If the aim is purely to find the progs without events, it might be more efficient to use something like select * from progs where not exist (select id_prog from events where id_prog = progs.id_prog); My syntax might be off, check not exists documentation for more info. On Tue, Dec 15, 2009 at 8:14 PM, Gavin Towey gto...@ffn.com wrote: Hi Miguel, You'll need to use LEFT JOIN, that will show all records that match and a row in the second table will all values NULL where there is no match. Then you find all those rows that have no match in your WHERE clause. Regards, Gavin Towey -Original Message- From: Miguel Vaz [mailto:pagong...@gmail.com] Sent: Tuesday, December 15, 2009 10:43 AM To: mysql@lists.mysql.com Subject: Count records in join Hi, I am stuck with a suposedly simple query: - i have two tables (: PROGS id_prog name EVENTS id id_prog name How can i list all records from PROGS with a sum of how many events each have? I want to find the progs that are empty. I remember something about using NULL, but i cant remember. :-P Thanks. MV This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Count records in join
Hi, I am stuck with a suposedly simple query: - i have two tables (: PROGS id_prog name EVENTS id id_prog name How can i list all records from PROGS with a sum of how many events each have? I want to find the progs that are empty. I remember something about using NULL, but i cant remember. :-P Thanks. MV
Join using Table1 or Table2 - depending on content of rel table
Hi, How would one go about doing this: - I have 3 tables: - A relationship table(REL), then TABLE1 and TABLE2: REL TABLE has fields: . ID . TYPE - type of event . ID_EVENT - id of event, but this id will either point to TABLE1 or TABLE2, depending on the content of the field TYPE Is it possible to do everything on the same select? I mean, the join will use a different table depending on the content of one of the fields. This join will retrieve the name of the event, either from TABLE1 or 2. Or should i just do a select to get the first row content, and then get the rest afterwards? Thanks, MV
Several languages for content
Hi, This is my first post, and could use some points of view on the subject. Have a project that will have several languages for its records, for places, for example, whose names and descriptions will be in 3 languages. Have already read several sites about it but always end up with several perspectives: 1 - Tables for each language: places_pt, places_en, places_es, with fields: id, name, description; Seems superfluous, but will leave less records on each table, although adding a great amount of tables, the more languages i have; 2 - Rows for each language: id, lang, name, description - the field lang will have pt, en or es, and when displaying, i will have to lookup the active language and get the proper record; Will add tremendously to the record #; 3 - Fields for each language: table places, with fields: id, name_pt, name_en, name_es, description_pt,description_en,description_es - not likely to add a new language, but still adding new field adds complexity. Is there a proper way to do things? Any usual way of handling several languages on a project? Any help is highly appreciated, thanks. MV
Re: Several languages for content
My 3rd option seems to be the most simple, although i admit it doesnt sound
quite right. Having fixed table fields for languages doesnt seem correct.
I can see disavantages in all 3 options, thats why i wanted to ask
everybody's opinion on something like this, i mean, a assume that dealing
with a multi language project is something that everybody will come across
eventually, right? :-)
Regarding your case, Engel, i would go for something like session or cookie,
thatsets a global var, so you can do the query like this:
$active_lang = $_SESSION...whatever; // will be en or es
$query=select name_.$active_lang. name from recipes;
$res = mysql_query($query) or die(blah blah);
... retrieve always the field named name, which will always be in your
active language. Hope it helps. :-)
MV
On Fri, Nov 27, 2009 at 4:13 PM, Engel Sanchez en...@solunion.com wrote:
I am really interested in the same answer...
I am making a script.. and as I will only have 2 languages I decided to use
you 3rd option (Fields for each language), because of less rows.
But I really would like to know if there is a better approach to this.
*eg. php function:*
function getRecipeName($id) {
global $database;
$query = mysql_query(SELECT name_english,name_spanish
FROM .$database['prefix'].recipes
WHERE id = '$id'
LIMIT 1
) or die(mysql_error());
$RECIPES = mysql_fetch_object($query);
if (ACTIVE_LANG == spanish $RECIPES-name_spanish != ) {
$return = .$RECIPES-name_spanish.; }
elseif (ACTIVE_LANG == spanish $RECIPES-name_spanish == ) {
$return = .$RECIPES-name_english. [Sólo inglés disponible]; }
elseif (ACTIVE_LANG == english $RECIPES-name_english != ) {
$return = .$RECIPES-name_english.; }
else {$return = .$RECIPES-name_spanish. [Only spanish available]; }
return $return;
}
Engel
2009/11/27 Elizabeth Mattijsen l...@dijkmat.nl
On Nov 27, 2009, at 4:34 PM, Miguel Vaz wrote:
This is my first post, and could use some points of view on the
subject.
Have a project that will have several languages for its records, for
places,
for example, whose names and descriptions will be in 3 languages.
Have already read several sites about it but always end up with several
perspectives:
1 - Tables for each language: places_pt, places_en, places_es, with
fields:
id, name, description; Seems superfluous, but will leave less records
on
each table, although adding a great amount of tables, the more
languages
i
have;
2 - Rows for each language: id, lang, name, description - the field
lang
will have pt, en or es, and when displaying, i will have to
lookup
the
active language and get the proper record; Will add tremendously to the
record #;
3 - Fields for each language: table places, with fields: id, name_pt,
name_en, name_es, description_pt,description_en,description_es - not
likely
to add a new language, but still adding new field adds complexity.
Is there a proper way to do things? Any usual way of handling several
languages on a project? Any help is highly appreciated, thanks.
This really depends on whether you have all content available in all
languages. And if not, how you want to fall back to other languages.
And
how often you add new languages.
Liz
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=en...@solunion.com
--
Engel Sanchez
Solunion Group|| Esfera Digital
www.solunion.com || www.esferadigital.com
E-mail: en...@solunion.com
Re: stuck on a query
Got it to work perfectly, thanks, although it takes some time (CILS has 150.000 records). There was no need for unique since all the results are already surprisingly unique. Thank you, it was a different way of doing things. Well, different to me, at least. :-) In the meantime, i found an alternative way of doing things, although honestly i think its a bit dirty (prod_enc is JOB, prod_enc_cil is CILS): select t1.num_of from (select pe.num_of,GROUP_CONCAT(cast(pec.id_cor as char) ORDER BY pec.id_cor DESC SEPARATOR '|') cores from prod_enc pe join(prod_enc_cil pec) on(pe.num_of=pec.num_of) group by pe.num_of order by pe.num_of desc) t1 where cores like '%cyan%magenta%' I cant get it to work if i only keep the inner select: select pe.num_of,GROUP_CONCAT(cast(pec.id_cor as char) ORDER BY pec.id_cor DESC SEPARATOR '|') cores from prod_enc pe join(prod_enc_cil pec) on(pe.num_of=pec.num_of) group by pe.num_of order by pe.num_of desc where cores like '%cyan%magenta%' Like this it complains about not having a column called cores, which i find very weird. Any insight on that? And yet another way, although it results in a different record count. How can it be possible? select num_of, count(num_of) c from prod_enc_cil where id_cor='cyan' or id_cor='magenta' group by num_of having c1 order by num_of desc What this does is list all CILS that have cyan or magenta, and shows the num_of that are doubled (thus have both colors). Pag On Wed, Dec 17, 2008 at 3:15 PM, c...@l-i-e.com wrote: Hopefully your CILS table is not too many rows... select * from JOB, CILS as cyan, CILS as magenta where cyan.num_of = JOB.num_of and magenta.num_of = cyan.num_of and cyan.color = 'cyan' and magenta.color = 'magenta' or something not unlike that... You may want UNIQUE JOB.id_enc or somesuch, because this will get TWO JOBs each, since one is cyan and one is magenta. If any of these tables are large this could be an enormous number of temp records. Do an explain on the query to see just what sort of pain you are looking at... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=pagong...@gmail.com
select with table name
Hi, I have a small issue that i can get my head around to solve: Is it possible to do a select from two tables using a union all (select * from t1 union all select * from t2), and have it display the table name in front of each row? What i need is, on the big resulting list, to know from which table the row came from. Example: Table: t1 --- id name --- 1 john 2 mary Table: t2 --- id name --- 1 paul 2 peter I need these results: id namefromtable 1 johnt1 2 maryt1 3 pault2 4 peter t2 Is this possible? If so, how? Thanks! Pag -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change field types from several tables
Hi, I am trying to import a big database of around 140 tables from xls to mysql. It works fine using navicat mysql, but i am running into a problem: Navicat tries to predict the field types, but makes a mistake on long text fields whose type needs to be text, instead it makes them varchar(255), which is not enough for the content. I can solve the issue if i manually choose all field types to be text, but since mysql has the alter table command to change field type, i was wondering if there is a way to change all fields from all tables to text automatically. This way i could import the table structure first, change all fields to type text and import tha data afterwards (the import is for an intermediary process, so no worries on all fields being text at this point). Something like: ALTER TABLE * CHANGE * * TEXT But obviously this doesnt work. Is this even possible using MYSQL? I could do it programmatically using PHP, i guess, but a single command would be far better. :-) Miguel Vaz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join between databases
Hi, I am building a new system for the place i work and i was thinking about organizing my tables in several databases, like management, financial, production_line to make things more tidy. I will have tons of tables, so is it a usual procedure in organizing tables? The other problem is about doing a join between tables that are on different databases, is that possible? For example: database: people table: users fields: id, name, email database: production table: machines fields: id, machine_job_num, id_user, etc I want to do a select on table machines that gets the user names from the table users, which is on a different databse. How can i do it? Is it even remotely possible? Sorry if the question is basic, but i searched the net all over the place and i couldnt get any answer to this. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT using SELECT results
Hi,
I have a table LOCAIS with:
id typedesc
1 t1 blah blah
2 t1 uihuih
3 t2 pokpokp
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:
id type
1 t1
2 t2
Thanks
Miguel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
to join or not to join, that is the query
Hi, i am having some difficulty to write a query for the following problem: I have three tables: TABLE Person - id_person, name, id_levelA, id_sizeA, id_levelB, id_sizeB TABLE Levels - id, desc TABLE Sizes - id, desc I need a query that returns everything from the Person table, replacing the id_levelA... with the desc from the tables Levels and Sizes. I can get a result with one of them replaced using JOIN, but not several replacements using the same reference tables (levels and sizes). :-P Heres what i need as a result: - id_person, name, descA, sizeA, descB, sizeB descA, etc, being the id_levelA, etc replaced, and i assume i need to give it a new name to fetch the results, right? Can you guys point me in the right direction? Thanks! Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple alternate query
Hi, I Cant get a simple query to work, heres what i am looking for: table1 -- id name 1 peter 2 john 3 mary 4 lisa 5 me I need a select that always retrieves id 1 and an interval of my choice: select * from table1 where id =3 and id=5 WORKS and gets record 3,4 and 5 select * from table1 where id=1 and id =3 and id =4DOESNT WORK i understand this last select shouldnt work for ilogical id's, but how can i get record 1,3,4 and 5? Thanks Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple alternate query
I am dumb, so sorry: select * from table1 where id=1 or id =3 and id =4 Have to go spank myself until i bleed now, thanks. Miguel At 02:15 28-01-2007, Miguel Vaz wrote: Hi, I Cant get a simple query to work, heres what i am looking for: table1 -- id name 1 peter 2 john 3 mary 4 lisa 5 me I need a select that always retrieves id 1 and an interval of my choice: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select from multiple tables
Hi guys, I am kinda new to mysql and on my endeavour to build a backend for a site i am building, i need to fetch data from a couple of tables, but dont know how to do it with a single select. Heres the problem: first table (products): id id_type name desc price second table (types): id_types name What i need is to list all the products and show the type name of each of them. On the products table, on the id_type field, i have only a number pointing to its type on the types column, what i want to know is how i can do a select to fetch all the product records and replace the number on id_type with its equivalente name from the types table. Sorry if i cant explain it correctly, but heres an example: table products: id id_type namedescprice 1 2 prod1 blah45 table types: id_type name 1 razor 2 string I want the list to show: prod1 string blah45 Thanks, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
