tarsier

Hello All, I have a SQL question and hope someone can point me in the right direction. I would like to do a query and have several columns reduce into a single result column. I will try to give a simple example here. I have a table that looks something like this account_name account_phone1 account_phone2 name1 555-555-1111 555-555-2222 name2 555-555-3333 555-555-4444 I would like to do a search like: SELECT {SQL MAGIC} WHERE account_name = 'name1' and get the result account_name phone name1 555-555-1111 name1 555-555-2222 Where phone1 and phone2 are combined into a single column. Is this possible? It will greatly reduce the complexity of my program. Thanks for any input you have. - Dave

Thanks for the reply. This is all running on a windows server. Will this still work? -Dave

Hello. I was wondering if anyone has any advice on how to start a 30-40 minute process from a web page using PHP. The process I want to start is actually a PHP program that starts from the command line and does a huge file-to-database transfer. I would love to have users be able to start this process from a web-page. I've looked at starting child processes but it looks like any child I make from the web-process is still bound by the maximum execution time for CGI scrips. Any ideas? Thanks. - Dave

Thanks for the advice. I ended up with the following. [pre]UPDATE table1 SET phone_full = IF(phone IS NOT NULL AND phone != '', IF(EXT IS NOT NULL AND EXT != '', CONCAT(Phone, ' ext.', EXT), phone), IF(EXT IS NOT NULL AND EXT != '', EXT, NULL)) WHERE 1[/pre]

Thanks for the response. The reason I need to do this is that I'm moving some customer info from one database to another. The new database does not have an EXT field so I need to merge the two fields into one before I do the move. I could move the data into a new table before I export.

Hello all, I am having trouble grasping how to do self referential SQL statements. Let me give you an example. Here is a sample table. [pre]+------------------------------------+ | table1 | +-------+-------+-----------+--------+ | id | name | phone | ext | +-------+-------+-----------+--------+ | 1 | Ted | 1111 | NULL | | 2 | Dave | 2222 | 44 | | 3 | Lucy | 3333 | | | 4 | Ann | 4444 | 23 | +-------+-------+-----------+--------+[/pre] I want to take all records that have data in the 'ext' field and append that data into the 'phone' field, whit the prefix of "EXT:" So the result will be the following [pre]+--------------------------------------+ | table1 | +-------+-------+-------------+--------+ | id | name | phone | ext | +-------+-------+-------------+--------+ | 1 | Ted | 1111 | NULL | | 2 | Dave | 2222 EXT:44 | 44 | | 3 | Lucy | 3333 | | | 4 | Ann | 4444 EXT:23 | 23 | +-------+-------+-------------+--------+[/pre] Here is the SELECT statement I've come up with. [pre]SELECT * from FROM table1 WHERE ext IS NOT NULL AND ext != ''[/pre] I can't figure out how to do the actual UPDATE statement. Any help? Any good SQL tutorials people can recommend? Thanks. - Dave

Thanks. That did it.

I have a simple (in principle) query I am having a hard time getting the working. I want to select a subset from table1 that does not appear in a subset of another table2 table1 --index (primany) --part_num --serial_num table2 --index(primary) --part_num --file_id I want to select everything from table1 where table1.serial_num='1234' AND table1.part_num does not match table2.part_num in the subset of table2 where file_id is not null this is to say that there is a subset in table2 of part_num's that have non-NULL file_id's. I want a list of all table1.part_num's that are not part of this table2 subset and have a given serial number. Does this make sense? Any help would be great. - Dave

I am using a MySql database to mirror another non-MySql database. The reason for doing this is the only access into this database is over an ODBC connection and is very slow. I do a data-copy 4 times a day to check for changes in the old database. There are about 5 tables that I copy over, with a total of about 100,000 records. This is what I do: - Copy the whole table out of the Master database over ODBC into a temp MySql table with the same form at the final mirror copy. - Do an SQL compare to look for any new records. - Do an SQL compare to look for modified records. - Do an SQL compare to look for deleted records. I did not want to update the table by just dropping the old table and moving the copy over because of the chance of users doing a search at that point and getting bad data. I also could not use a REPLACE because some of the tables that I am moving over do not have primary keys. My questions is if there is a better way to do this? Would love to get some feedback on this. Thanks - Dave

Thanks for all the input. It's running on a Win32 system, so no such luck with a find command. The purpose of the project is to store and orgnize pdf files of mechanical prints. The drafting department creates drawings, which can be placed anywhere in a large directory structure. The thought was to have an automatic process scan the structure several times a day and copy any new or updated files into the databaes. The php program parses the part number, revision, and page from the filename. It then removes duplicate files. Finily, it compares the list of scaned files to the ones it has already and makes a copy into the database of the new or modified ones. Maybe there is a better way to scan for duplicate files. I added indexes to the table, of which I had none before, and it is now going MUCH faster. I have not timed it yet but it's easlly twice or three times faster. I will have to look into using "INSERT ........ON DUPLICATE KEY UPDATE" The only issue I might have is that there are several indexes for a given file. Filename is not unique. Again, thanks for the input.

Wildbug, Thanks for the replay. Here is a little more info about what I'm doing. I'm doing a scan of a huge directory tree and looking for new files to copy into the database. This is a task that will probably run several times a day. First, I scan the directory tree, and add all file names, paths, and creation dates to a table on the database. I then remove all duplicate files and older versions of the same file. Lastlly, I look at the files that I have and copy it into the database, if I don't have it yet. I have not had to deal with tables this large before, so I have a lot to learn about that. I did not have any indexes, aside from the primary key. I've added some indexes and I'll see how that goes. I will also try to do some of the quicker opperations first. Thanks for the sugestions.

Hello all, I wrote the following SQL command (with some help) to analize a table with 25000 records and delete the lines with duplicate file_name fields. It deletes the older file, by file_date. If two files have the same data, it deletes the first on it finds. When I ran this on my test table, it works great. I tried it on the big table and it takes a very long time to run, something like 5-10 min. Is this normal? Is there a way to speed this up? Is there a better way to do this? DELETE tmp_ph_part_files FROM tmp_ph_part_files JOIN ( SELECT a.file_id FROM tmp_ph_part_files AS a JOIN tmp_ph_part_files AS b ON a.file_name = b.file_name AND a.file_id <> b.file_id AND ((a.file_date < b.file_date) OR (a.file_date = b.file_date AND a.file_id > b.file_id)) ) AS sub WHERE tmp_ph_part_files.file_id = sub.file_id Thanks for any help you can give.

That did the trick. Thanks!

I have a SQL question, for any of you SQL experts out there. I have the following SQL statement to return all the lines that are different between two tables. This is to say that if there is a line in table_2 that differs from any line in table_1 in field a, b, or c, it gets selected. SELECT * FROM table_2 WHERE NOT EXISTS (SELECT * FROM table_1 WHERE table_2.a = table_1.a AND table_2.b = table_1.b AND table_2.c = table_1.c ) My problem is that this, for some reason, also selects any row that has a NULL in field a, b, or c, even if it's NULL in both table_1 and table_2. Any ideas why this would be and how to correct for it? - Dave