CtrlAltDel Posted January 21, 2008 Author Share Posted January 21, 2008 though the last statement seems to have %var% in it? Sorry, you have lost me there, but thnaks for taking the time in doing this for me. P.S. i cannot find how to donate Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-445034 Share on other sites More sharing options...
fenway Posted January 21, 2008 Share Posted January 21, 2008 though the last statement seems to have %var% in it? Sorry, you have lost me there, but thnaks for taking the time in doing this for me. P.S. i cannot find how to donate I just mean there are fields sthat still appear to be in need of interpolation... but I should be able to do it regardless. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-445449 Share on other sites More sharing options...
CtrlAltDel Posted January 21, 2008 Author Share Posted January 21, 2008 just as i thought (not really) But i really do appreciate your help Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-445589 Share on other sites More sharing options...
CtrlAltDel Posted January 23, 2008 Author Share Posted January 23, 2008 I am sorry fenway, i dont mean to hassle you but if you don't have time i am willing to ask paid help. Maybe there is something i can be doing in the meantime that will give you some better clues? Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-446871 Share on other sites More sharing options...
fenway Posted January 23, 2008 Share Posted January 23, 2008 I am sorry fenway, i dont mean to hassle you but if you don't have time i am willing to ask paid help. Maybe there is something i can be doing in the meantime that will give you some better clues? Life's been busy... I'll try and do this tonight. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-447263 Share on other sites More sharing options...
CtrlAltDel Posted January 24, 2008 Author Share Posted January 24, 2008 Ok, many thanks Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-447780 Share on other sites More sharing options...
fenway Posted January 25, 2008 Share Posted January 25, 2008 I finally got to this... but your insert statements don't match your where clause, and don't have any URLs! Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-448548 Share on other sites More sharing options...
CtrlAltDel Posted January 25, 2008 Author Share Posted January 25, 2008 Sorry, i have only just understood what you meant. Here is another insert for you bf_posts INSERT $INSERT[] = "INSERT INTO ibf_posts (pid, append_edit, edit_time, author_id, author_name, use_sig, use_emo, ip_address, post_date, icon_id, post, queued, topic_id, post_title, new_topic, edit_name, post_key, post_parent, post_htmlstate) VALUES (1, 0, NULL, 1, 'Test Poster', 0, 1, '127.0.0.1', <%time%>, 0, 'Here is a test post!<br />Its tricky to describe my problem so ill use pics here we go<br /> <br />Im trying to do this short tutorial http://majorgfx.net/Animation.htm<br /> I think i did every thing right up to here http://img438.imageshack.us/img438/844/28nh.png but at this part of the tutorial http://img438.imageshack.us/img438/641/13hz.png <br />it all goes wrong because i can see my line http://img438.imageshack.us/img438/2826/36at.png<br /> <br /><br />which i now see is gonna be a problem because the line will go across the whole pic at the end and not just the bit i selected only thing i could think of was click the eye to hide that layer but iv clicked everywhere around were iv marked the red circle http://img438.imageshack.us/img438/8876/45mo.png<br /><br /> <br /><br /> the line isn't there if i hide that layer now...', 0, 1, NULL, 1, NULL, '0', 0, 0);"; ibf_topics INSERT $INSERT[] = "INSERT INTO ibf_topics VALUES (1, 'Welcome', '', 'open', 0, 1, <%time%>, 1, <%time%>, 0, '<%admin_name%>', '<%admin_name%>', '0', 0, 1, 2, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0);"; and my query again is this SELECT post_date , DATE_FORMAT(FROM_UNIXTIME( post_date ) , '%d-%m-%y') AS formatted_date , LEFT(title, 20) AS title , post , SUBSTRING(post FROM LOCATE('http://',post) FOR LOCATE(' ',post,LOCATE('http://',post))-1) AS url FROM ibf_posts, ibf_topics WHERE post LIKE '%tp:%' AND topic_id = ibf_topics.tid AND forum_id = 58 ORDER BY post_date DESC LIMIT 0 , 60 END; I hope i have done it right this time. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-448711 Share on other sites More sharing options...
fenway Posted January 25, 2008 Share Posted January 25, 2008 OK... I will give this another try this evening. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-448902 Share on other sites More sharing options...
fenway Posted January 26, 2008 Share Posted January 26, 2008 Normally, this would work: SUBSTRING_INDEX( SUBSTRING(post FROM LOCATE('http://',post) ), ' ', 1 ) But in your case, you have a < instead of a space.... Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-450117 Share on other sites More sharing options...
CtrlAltDel Posted January 28, 2008 Author Share Posted January 28, 2008 I'm sorry, but i cannot find a < in anywhere in my query? Do i replace the line you gave me with the one ? SUBSTRING(post FROM LOCATE('http://',post) FOR LOCATE(' ',post,LOCATE('http://',post))-1) AS url or do i add it ? Normally, this would work: SUBSTRING_INDEX( SUBSTRING(post FROM LOCATE('http://',post) ), ' ', 1 ) But in your case, you have a < instead of a space.... Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-451060 Share on other sites More sharing options...
CtrlAltDel Posted January 28, 2008 Author Share Posted January 28, 2008 sorry, didnt get to edit it in time. I have replaced the line and it does grab the first instrance of "http" from the post , but it then moves on to the next post and finds the first instance of "http" there. The trouble is, it doesnt show the 2nd instance of "http" in the 1st post? http://www.mysite/pic1.jpg this is line one, post 1 http://www.mysite/pic2.jpg this is line two, post 1 http://www.mysite/pic3.jpg this is line three, post 1 http://www.mysite/pic4.jpg this is line one, post 2 so it gives me http://www.mysite/pic1.jpg http://www.mysite/pic4.jpg But so happy to get it grabbing only the URL, thanks very much fenway Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-451081 Share on other sites More sharing options...
fenway Posted January 28, 2008 Share Posted January 28, 2008 That's correct, if you want to find multiple http://, you'll have to find out how many there are first, and then do this for each one. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-451413 Share on other sites More sharing options...
CtrlAltDel Posted January 28, 2008 Author Share Posted January 28, 2008 some posts have one, others have 4 or 5. Can i just go with what i think the maximum will be and contruct it from that? so, if i thought the max would be two, do i simply add SUBSTRING_INDEX( SUBSTRING(post FROM LOCATE('http://',post) ), ' ', 1 ) that many times?, becuase i think it woul still find the first one again ? Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-451424 Share on other sites More sharing options...
fenway Posted January 28, 2008 Share Posted January 28, 2008 This would be much easier to do with a language that supports regular expressions... I suggest you do this in php. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-451440 Share on other sites More sharing options...
CtrlAltDel Posted January 28, 2008 Author Share Posted January 28, 2008 would your suggestion to do it with php mean i would have to start again and dump my query ? Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-451450 Share on other sites More sharing options...
fenway Posted January 28, 2008 Share Posted January 28, 2008 would your suggestion to do it with php mean i would have to start again and dump my query ? You didn't mention at any point that you needed to extract multiple strings... mysql does not natively support this type of functionality... a while( yourstring =~ s/<match>/g ) makes this trivial. Otherwise, you'll have to do this multi-pass... it would be quite ugly. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-451531 Share on other sites More sharing options...
CtrlAltDel Posted January 28, 2008 Author Share Posted January 28, 2008 sorry, i made mention of it in the last post on page one, this was the thread that was merged, i thought i had better concentrate on this part first, since i didn't want to be accused of duplicate postings again. anyhow, it doesnt matter how ugly it is really, it wont be used for display, only to mods of my fourm to help them find all the broken links to pictures. In essence i want to strip everything out of the posts excpet for the http (urls), i am happy for it to show that no matter how ugly and once the "big cleanup" has been done, then its only a question of running it once a week or even once a month. would a multi-pass be the easiest option ? also, can someone pls show me how to donate! Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-451750 Share on other sites More sharing options...
fenway Posted January 29, 2008 Share Posted January 29, 2008 Let me mull over the easiest way to do this... do you have access to the server directly (root access)? There are some UDFs that would be helpful. Click here to donate. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-452398 Share on other sites More sharing options...
CtrlAltDel Posted January 29, 2008 Author Share Posted January 29, 2008 Yes i have access to the server root. Thanks again Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-452434 Share on other sites More sharing options...
fenway Posted January 29, 2008 Share Posted January 29, 2008 You may want to look at mysqludf.org... the preg library has perl-like functionality... may be very easy to extract what you need. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-452777 Share on other sites More sharing options...
CtrlAltDel Posted January 30, 2008 Author Share Posted January 30, 2008 Sorry, i misled you there, i thought you meant access to my root folder on my website mysite.com/root I dont have access to the part of my server required to insatll any of the UDF's Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-453370 Share on other sites More sharing options...
fenway Posted January 30, 2008 Share Posted January 30, 2008 I see... well, then a stored procedure (which can iterate) is probably best... but I'm not that versed in these matters. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-453563 Share on other sites More sharing options...
CtrlAltDel Posted February 1, 2008 Author Share Posted February 1, 2008 Ok, well, thanks very much for all your help. looks like ill have to give up on this. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/page/2/#findComment-455628 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.