FOR LOCATE function problem

[CtrlAltDel]

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

[fenway]

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.

[CtrlAltDel]

just as i thought (not really)

But i really do appreciate your help

[CtrlAltDel]

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?

[fenway]

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.

[CtrlAltDel]

Ok, many thanks

[fenway]

I finally got to this... but your insert statements don't match your where clause, and don't have any URLs!

[CtrlAltDel]

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&#33;<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.

[fenway]

OK... I will give this another try this evening.

[fenway]

Normally, this would work:

 

SUBSTRING_INDEX(  SUBSTRING(post FROM LOCATE('http://',post) ), ' ', 1 )

 

But in your case, you have a < instead of a space....

[CtrlAltDel]

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....

[CtrlAltDel]

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

[fenway]

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.

[CtrlAltDel]

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 ?

[fenway]

This would be much easier to do with a language that supports regular expressions... I suggest you do this in php.

[CtrlAltDel]

would your suggestion to do it with php mean i would have to start again and dump my query ?

[fenway]

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.

[CtrlAltDel]

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!

[fenway]

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.

[CtrlAltDel]

Yes i have access to the server root.

Thanks again

[fenway]

You may want to look at mysqludf.org... the preg library has perl-like functionality... may be very easy to extract what you need.

[CtrlAltDel]

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

[fenway]

I see... well, then a stored procedure (which can iterate) is probably best... but I'm not that versed in these matters.

[CtrlAltDel]

Ok, well, thanks very much for all your help.

looks like ill have to give up on this.