Hello. I have a downloads site which uses a mySQL database back-end. There's a single table which has most of the information associated with each file, including and more importantly, the mirror links for each file. The reason the mirrors links are important is because I use these to point to my download servers where the files actually reside. The way it's setup now is in the table, there's a column called 'mirrors' and one called 'mirrornames'. The 'mirrors' one has the actual URL links in it (one per line for each row), while the 'mirrornames' has the name for each link (one per line for each row). So, for example, for a certain file in the table, the 'mirrors' field would have: http://***/PAVUpdat.exe http://***/PAVUpdat.exe Code (markup): NOTE: *** = link removed so I don't get any leechers... While the 'mirrornames' would have: Mirror 1 Mirror 2 Code (markup): Now, things have changed recently. I'm getting rid of one of my download servers and thus need to remove the 2nd mirror link from ALL files. This means removing the 2nd line from both 'mirrors' and 'mirrornames'. I currently have over 300 files in the database. I thought about using the replace function in a query to do what I need, but it ends up replacing the entire field instead of just the one line in the field. Is there any way that I can do my edits database-wide easily enough, without having to spend a ton of time making a query for every single file? Thanks.
Not sure if I understand correctly, but do you mean that currently for each file there are two records which hold the actual mirrors? Or do you mean that there is a single record for each file and the 'mirrors' field contains two entries separated by CRLF? The first case would be trivial: "DELETE FROM table WHERE table.mirrors LIKE '%nameofthemirrorsite.com%'" The second case would be more tricky, though not really impossible with pure SQL commands. Using a quick and dirty PHP app might be easier probably. Poor database design in that case, I'd say.
You are right, it's the second case, and yes I know it's poor design, but it's not mine, lol. I'm actually switching scripts altogether eventually but that's besides the point. How would I go about it? PHP is not my forte. If you can help, I'd be willing to pay a small fee even. Thanks.
I'm not a friend of PHP either It's ALWAYS the second line that has to be removed? In that case something like this might work: UPDATE table SET table.mirrors = LEFT(table.mirrors, POSITION(CONCAT(CHR(13),CHR(10)) IN table.mirrors)-1) The CONCAT() is only needed if the delimiter is CRLF. If it's unix-like "\n" CHR(10) would be enough. You have a test tablle to play around with, I hope. Because I haven't tried or tested it. It jumped right out of my brain and might be dangerous
Yes, it's always the second line. I've already backed everything up, so I'll try that out on a few rows and see what happens. I'll let you know. Thanks. Oh, one thing, will that work also for the 'mirrornames' field as well (basically same thing, just different data)? Thanks.
OK, I eventually ran this query: UPDATE `dl_links` SET `mirrors` = LEFT(`mirrors`, POSITION(CONCAT(CHR(13),CHR(10)) IN `mirrors`)-1) WHERE 'did' = '7' Code (markup): but got this error: SQL query: UPDATE `dl_links` SET `mirrors` = LEFT( `mirrors` , POSITION( CONCAT( CHR( 13 ) , CHR( 10 ) ) IN `mirrors` ) -1 ) WHERE 'did' = '7' MySQL said: Documentation #1305 - FUNCTION littlbug_downloads.CHR does not exist Code (markup): Any ideas? I'm using mysql 5. Thanks.
sry. I guess I mixed it up with MS ACCESS. MySQL should use CHAR() instead. CONCAT() not needed then. CHAR(13,10) for example should work too. Same function would work for the mirrornames field.
Sorry for my late reply. I've tried the following two queries without success: UPDATE `dl_links` SET `mirrors` = LEFT(`mirrors`, POSITION(CHAR(13, 10) IN `mirrors`)-1) WHERE 'did' = '7' Code (markup): and UPDATE `dl_links` SET `mirrors` = LEFT(`mirrors`, POSITION(CONCAT(CHAR(13), CHAR(10)) IN `mirrors`)-1) WHERE 'did' = '7' Code (markup): Though they don't error, 0 rows are affected so nothing happens. Did I do something wrong? Thanks.
if using this way 'did' is a string. If you mean a field, either drop quotes or use the correct ones Otherwise you only affect records, where 'apples' = 'oranges'.
Oh sorry, I spoke too soon. It seems to work for the field 'mirrors' but not for the field 'mirrornames'. For mirrornames, it just deletes everything. Any ideas? Maybe some other character than the return? Thanks.
Most likely. If chr(13,10) isn't found, pos will return 0. Left(something,-1) probably is ... nothing try with char(10)?
change CHAR(13, 10) to CHAR(10). if that doesn't work either ... ???? Since there are two "lines" there must be some kind of linebreak. Not much possibilities to test I guess.