SELECT would take a very long time
Hi, Got this problem and setting the SQL_BIG_SELECTS=1 is not an option. Is there a way to change the WHERE so the query would work? Error SQL-query : SELECT distinct(fincodedesc), industry.code, industry.level1, industry.level2, industry.level3, industry.level4, industry.level5 FROM industry,branchinfo WHERE branchinfo.code1=industry.code OR branchinfo.code2=industry.code OR branchinfo.code3=industry.code OR branchinfo.code4=industry.code OR branchinfo.code5=industry.code ORDER by industry.code ASC LIMIT 0, 30 MySQL said: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok TABLE SCHEMA's CREATE TABLE industry ( kid int(11) NOT NULL auto_increment, code varchar(8) NOT NULL default '', svecodedesc text NOT NULL, fincodedesc text NOT NULL, level1 varchar(8) NOT NULL default '', level2 varchar(8) NOT NULL default '', level3 varchar(8) NOT NULL default '', level4 varchar(8) NOT NULL default '', level5 varchar(8) NOT NULL default '', industry_modified timestamp(14) NOT NULL, PRIMARY KEY (kid), UNIQUE KEY kid (kid), KEY kid_2 (kid,code,level1,level2,level3,level4,level5), FULLTEXT KEY svecodedesc (svecodedesc,fincodedesc) ) TYPE=MyISAM; CREATE TABLE branchinfo ( did int(10) unsigned NOT NULL auto_increment, aid int(10) unsigned default NULL, svebranch text, finbranch text, engbranch text, gerbranch text, code1 varchar(8) default '0', code2 varchar(8) default '0', code3 varchar(8) default '0', code4 varchar(8) default '0', code5 varchar(8) default '0', branchinfo_modified timestamp(14) NOT NULL, PRIMARY KEY (did), UNIQUE KEY did (did,aid), KEY did_2 (did,code1,code2,code3,code4,code5,aid), FULLTEXT KEY branchinfo_search (svebranch,finbranch,engbranch,gerbranch) ) TYPE=MyISAM; jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to change DATETIME format ??
mysql,select,query,sql mysql / query -- I have a DATETIME column type with format -MM-DD and I'd like to have format DD-MM- what do I have to do ? Does it matter in what format it is in the database? you can select the date using DATE_FORMAT(date,format) more on manual page http://www.mysql.com/doc/D/a/Date_and_time_functions.html using that you get the output in what format you want to DATE_FORMAT( datetimecolumn, %d-%m-%Y ) should output what you want. If it matters in what format it is in the database you cannot use DATETIME field but CHAR,VARCHAR,TEXT but you lose the simple system of setting the date with just calling NOW(). JAri Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
uppercase for first letter
sql,query Hi, How could I turn the first letter of each entry to Uppercase letter? the UCASE turns each and every letter and all I need is the first one to be changed JAri Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple FULLTEXT searches
sql,query
Hi,
how one can do a FULLTEXT search from multiple tables?
SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('database');
above format works for one table but how I need to alter it for it to
search multiple tables?
JAri Mäkelä
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
many small queries or one big one
sql,query HI, which one would be best for performance, many simple queries or one complex? jari mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Changing character X to Y in a table
Hi, have not figured out if one can do character changes with mysql, any one know how to do the following for fields in a table Altering all the x-characters to character y ? Jari Mäkelä mysql,query,select,insert - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
changing allowed number of connections
Hi, Where one can alter the number of connections going to MySQL? Jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT by similarity
Hi, I have a table that has following fields; code, explanation, level1, level2, level3, level4, level5 A A A01 A01 A0101 A0101 etc The problem is that I need to get explanation SELECT explanation based on two things; WHERE code=level(x) AND level(x) LIKE level(x-1) All levels are the same , starting from the beginning and each level adds more defining attributes. Simple LIKE does not work but is there some alternative functions that would allow the comparison? Jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Getting the length of a field
Hi, can you count the number of characters in a field with MySQL? The length(str) function seemed like a choice tool but can it be used to get the length of data in a field or is there some other way? Jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
using DISTINCT
Hi, Is it possible to get all data; SELECT * FROM and still get some fields filtered by DISTINCT? Jari Mäkelä database,sql,query,table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Doing queries using field labels as criteria
Hi, Is it possible to search with LIKE for a field name? I have database that has various data and the field names are named by the language it is aimed for. So can MySQL be used to search that field list for all fields having specific string in them and then selecting all data pertinent to the found fields? jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JOINS what is the difference in?
database,sql,query,table Hi, have been told to use joins and after reading the manual a bit I do not know what it is about. I have added couple of examples. Could anyone tell me what difference is in them if any? the MY: is what I have been using and SUGGESTED is what was offered to be instead of MY: MY: select * from baseinfo,addressinfo where baseinfo.aid=addressinfo.aid; SUGGESTED: select * from baseinfo LEFT JOIN addressinfo ON baseinfo.aid=addressinfo.aid; SUGGESTED: select * from baseinfo LEFT JOIN addressinfo USING (aid); MY: Select * from baseinfo,addressinfo,postalinfo Where baseinfo.aid=addressinfo.aid and addressinfo.postalnumber=postalinfo.postalnumber; SUGGESTED: select * from baseinfo LEFT JOIN addressinfo ON baseinfo.aid=addressinfo.aid LEFT JOIN postalinfo ON addressinfo.postalnumber=postalinfo.postalnumber; What kind of practical uses do you have for JOINS? Jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL crashing/stopping in ABRIASOFT's installation
1048576 max_binlog_cache_size 4294967295 max_binlog_size1073741824 max_connections100 max_connect_errors 10 max_delayed_threads20 max_heap_table_size16777216 max_join_size 4294967295 max_sort_length1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_recover_options OFF myisam_sort_buffer_size8388608 net_buffer_length 16384 net_read_timeout 30 net_retry_count10 net_write_timeout 60 open_files_limit 0 pid_file c:\ohjelmatiedostot\abria merlin\mysql\data\tbird.pid port 3306 protocol_version 10 record_buffer 131072 query_buffer_size 0 safe_show_database OFF server_id 0 skip_locking ON skip_networkingOFF skip_show_database OFF slow_launch_time 2 socket MySQL sort_buffer2097144 table_cache64 table_type MYISAM thread_cache_size 0 thread_stack 65536 transaction_isolation READ-COMMITTED timezone Suomen kesäaika tmp_table_size 1048576 tmpdir c:\windows\temp\ version3.23.36 wait_timeout 28800 Last Lines from Err File 010904 14:25:14 Aborted connection 1 to db: 'Project' user: 'root' host: `localhost' (Unknown error) C:\Ohjelmatiedostot\Abria Merlin\MySQL\bin\mysqld-opt.exe: ready for connections 010904 14:20:21 C:\Ohjelmatiedostot\Abria Merlin\MySQL\bin\mysqld-opt.exe: Shutdown Complete 010904 14:20:21 Aborted connection 2 to db: 'unconnected' user: 'root' host: `localhost' (Unknown error) 010904 14:20:21 Aborted connection 6 to db: 'Project' user: 'root' host: `localhost' (Unknown error) 010904 14:20:21 Aborted connection 4 to db: 'Project' user: 'root' host: `localhost' (Unknown error) 010904 14:20:21 Aborted connection 8 to db: 'Project' user: 'root' host: `localhost' (Unknown error) 010904 14:20:21 Aborted connection 1 to db: 'Project' user: 'root' host: `localhost' (Unknown error) 010904 14:20:21 Aborted connection 3 to db: 'Project' user: 'root' host: `localhost' (Unknown error) 14:20:21 Aborted connection 5 to db: 'Project' user: 'root' host: `localhost' (Unknown error) Jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL crashing in abria merlin server
Hi, have met a following problem, I have on my Win32 work machine the Abria softs merlin server package but the MySQL part of it keeps failing. You do not have to do a thing and it fails, does not give out data anymore. I have to close both Mysql and Apache for it to work again for a few moments. Does anyone else have similar troubles with it? Jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LAST_INSERT_ID()
Hi, in the manual there is this LAST_INSERT_ID() and it is what I actually need but there is some confusion over how to use it in many tables. exerpt http://www.mysql.com/doc/O/D/ODBC_and_last_insert_id.html Or, if you are just going to insert the ID into another table, you can do this: INSERT INTO foo (auto,text) VALUES(NULL,'text'); INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); exerpt http://www.mysql.com/doc/O/D/ODBC_and_last_insert_id.html I have database where most of the data is entered at once to multiple tables and the linking reference is the ID of the main table. Would this command just take the first ID and use it in other tables or would it take it from the new id of the other tables? What I mean is, would this code use the ID of first insert in all inserts ? INSERT INTO foo (auto,text) VALUES(NULL,'text'); INSERT INTO foo2 (id,auto1,text) VALUES(0,LAST_INSERT_ID(),'text'); INSERT INTO foo3 (id2,auto2,text) VALUES(0,LAST_INSERT_ID(),'text'); or would it take the first last_insert_id() from first line and second from second line? jari mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LAST_INSERT_ID()
At 13:19 22.8.2001 +0200, you wrote:
LAST_INSERT_ID() should return the last autoincrement value of
the last insert
Yep it sure did, managed to get to machine and test it out. Total mess.
but;
$query = SELECT LAST_INSERT_ID();
$result = mysql_query($query);
if ($result) {
$nrows = mysql_num_rows($result);
$row = mysql_fetch_row($result);
$lastID = $row[0];
}
was a code sent to me and if it helps anyone there it is, do not know who
is the original author but this takes the Id from first insert in you put
it after it and then you can use the $lastID in following inserts, works fine.
Jari Mäkelä
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
No identical entries in a field
Hi, how a field ( names of products ) of database should be defined so that you could not enter identical entries at the field? Or does one need to do the validation before inserting info? Jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
database basics
Hi, was wondering as am starting a database with just some 5000 different ID entries and each entry having some 60 datafields, which would work best/faster a single table of the data or divided into separate tables? Jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database accepts only 127 records
Hi, got a problem as mysql does not allow writing but 127 entries to a database, any idea how to correct this abnormality? Jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
another question to continue
hi again, am not quite sure of this as Mysql is too new thing to me but apostrophes like plain 'and é in text being written in has to be dealt with somehow, is the say 0'keefe solution the proper way to do that. meaning that O\'keefe works? Jari Mäkelä - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
adding many entries to database
Hi,
could any one tell what am doing wrong with this. I have a file of which
parts needs to be transferred to database but after successfully moving 66
entries into the database it dies at the mysql_query. There would be about
10 times more entries remaining. The file is delimited with ; but it is not
to be transferred to database just one parts of it.
?php
$connect = mysql_connect(host,user,password) or die( connection
error );
mysql_select_db(arkipelag,$connect) or die(No database error !!!);
$lines = file('info.txt');
foreach ($lines as $line)
{ $p = explode(';',$line);
$string[4] = substr ($p[4], 1, -1);
$string[5] = substr ($p[5], 1, -1);
$string[6] = substr ($p[6], 1, -1);
$query = INSERT INTO names VALUES
('0','$string[4]','$string[5]','$string[5]');
$result = mysql_query ($query,$connect) or die (input error!!!);
}
?
Jari Mäkelä
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
