FOR LOCATE function problem

[CtrlAltDel]

MySql version 4.1.22

 

$sql= <<< END
SELECT
post_date
, DATE_FORMAT(FROM_UNIXTIME( post_date ) , '%d-%m-%Y') AS formatted_date
, LEFT(title, 30) AS title
, post
, SUBSTRING(post FROM LOCATE('http://',post) FOR LOCATE('',*****)-1) AS url
FROM posts, topics
WHERE post LIKE '%tp:%'
AND topic = topics.tid
AND forum_id = 29
ORDER BY post_date DESC
LIMIT 0 , 30
END;

 

all works well with this query except this line

, SUBSTRING(post FROM LOCATE('http://',post) FOR LOCATE('',*****)-1) AS url

 

i cannot work out what i should put there, i am trying to find the end of the URL in the post and know its something to do with -1, but i am now stuck.

 

Its driving me mad LOL

[fenway]

What's in "post"? The whole things?

[CtrlAltDel]

Hi fenway, thanks for your reply

 

if you are referring to SUBSTRING post, then everything from "http" onwards. (including all the text right to the end)

so its showing

http://www.mysite/mypic.jpg and here is the pic of my new cat blah blah blah

when i want it only to show

http://www.mysite/mypic.jpg

 

post is also a column in "postS" table

[fenway]

You should be able to search for the first whitespace.

[CtrlAltDel]

yes, thast what i am trying to do, but search what string  i should be searching in?

[fenway]

SUBSTRING(post FROM LOCATE('http://',post) FOR LOCATE(' ',post,LOCATE('http://',post))-1) AS url

[CtrlAltDel]

Thanks for that fenway.

It did make the results better, but i am still picking up some stray text

here is one of the outputted results.

 

http://mihd.net/" target="_blank">http://mihd.net/

 

I havent tested it but I will next time I upload

 

as you can see, its not picking up the whitespace for some reason?

[fenway]

That's no really possible... post the value of "post" in your example.

[CtrlAltDel]

Sorry, that post has been deletd, but here is another one

 

this is the code outputted from "post"

please please could some one help me 
Today iv read about 10 tutorials on doing things with photoshop and i keep getting problems at the end and having to give up but i want to do this one and im not giving up..

Its tricky to describe my problem so ill use pics here we go

Im trying to do this short tutorial http://majorgfx.net/Animation.htm

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

it all goes wrong because i can see my line http://img438.imageshack.us/img438/2826/36at.png

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

the line isn't there if i hide that layer 

I have a feeling its something to do with the grouping part but im not sure please could somebody take the time to see what im doing wrong
im sure some one here knows how to do this
thank you

please ask me if you want to help but that made no sense lol

 

and this the code outputted from "url"

http://majorgfx.net/Animation.htm

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

 

Both copied and pasted. Thanks

[CtrlAltDel]

Sorry i tried to add my code but it woudn't let me.

 

the code i used:

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;

[fenway]

Perhaps you're using the wrong syntax -- you might be using the second value as the length of the string to return, not the index.

[CtrlAltDel]

Im sorry fenway, i dont understand what you mean, where would i change it to test it on the index.

what parameters do i need to chnage please

Thanks

[fenway]

Apparently, I've never used this function for this purpose in this language... it always takes a length, so

 

SUBSTRING( post FROM LOCATE('http://',post) FOR ( LOCATE(' ',post, LOCATE('http://',post)- LOCATE('http://',post) ) ) -1) AS url

 

Should work.

[CtrlAltDel]

$sql= <<< END
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)- 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 tried that but although it didnt give me an error, it didnt populate the URL column with anything at all.

Do you have any other suggestions please ?

[CtrlAltDel]

(Reformatted to make more sense)

 

MySql version 4.1.22

 

this is my photo of my dog (pos 40)http://www.mysite/dog1.jpg its a lovely dog and blah blah blah ............... heer is another photo of another of my dogs

(pos 140)http://www.mysite/dog2.jpg which is also a lovely dog blah blah blah ......

 

my php page displays 3 columns "date, post, url"

I have a problem which i cannot describe properly, if i set the limit for amount of text to display after "http" to 70, then i get nicely formatted columns in my output via "ECHO"

which shows all 3 columns.

, SUBSTRING(post FROM LOCATE('http://',post) FOR 70) AS url

 

However, if i increase this to 200 (to make sure i grab all the URL's in the post) i get an extra column alongside my url column, without a title. and it seems to hold the 2nd mention of "http" that may be in any given post. (see example of a typical post above ref dog)

, SUBSTRING(post FROM LOCATE('http://',post) FOR 200) AS url

 

Is there a way of making it display the 2nd or subsequent url (http://) on another line, and not in an extra column?

 

Thanks

[fenway]

The first code that I gave you works when I tested it... what version are you using?

[CtrlAltDel]

Sorry, but is this meant for another thread?

 

MySql version 4.1.22

phpMyAdmin - 2.11.0

 

thanks

[CtrlAltDel]

Oh i see, this thread has been merged with my other one about extra columns

[fenway]

Sorry, but is this meant for another thread?

 

MySql version 4.1.22

phpMyAdmin - 2.11.0

 

thanks

The php aspect is of no consequence... I have tested the original code on your example in this version, and it produces the desired output.

 

And yes, the threads were merged...

[CtrlAltDel]

Hello Fenway thnaks for your reply to my problem again.

 

I cannot undestand why it doesn't work in that case, is your code exactly the same as mine, maybe i have a sytrax error

 

$sql= <<< END
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)- 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;

it does show the url column, but nothing is in the column.

 

Can i use the exact same code to test it in phpmyadmin ?

[fenway]

I was talking about this code, testing on the post that you supplied:

 

SUBSTRING(post FROM LOCATE('http://',post) FOR LOCATE(' ',post,LOCATE('http://',post))-1) AS url[/url]

And it shouldn't matter where you test it from.

[CtrlAltDel]

ok, have changed back to that piece of code you supplied.

But the results are still giving me text after the end of the URL (as in Post no

 

Sorry to keep troubling you, should i post in "paid help" section for someone to work on this for me, i am running out of time.

 

[fenway]

Argh... post your create table statement, as well as the insert required to produce the relevant rows.  I'll see if I can produce sample output...

 

I don't see any reason to pay for a single line of code.

[CtrlAltDel]

Many thanks, i hope i have done this right.

 

 

ibf_posts

TABLE[] = "CREATE TABLE ibf_posts (
  pid int(10) NOT NULL auto_increment,
  append_edit tinyint(1) default '0',
  edit_time int(10) default NULL,
  author_id mediumint( NOT NULL default '0',
  author_name varchar(32) default NULL,
  use_sig tinyint(1) NOT NULL default '0',
  use_emo tinyint(1) NOT NULL default '0',
  ip_address varchar(16) NOT NULL default '',
  post_date int(10) default NULL,
  icon_id smallint(3) default NULL,
  post mediumtext NULL,
  queued tinyint(1) NOT NULL default '0',
  topic_id int(10) NOT NULL default '0',
  post_title varchar(255) default NULL,
  new_topic tinyint(1) default '0',
  edit_name varchar(255) default NULL,
  post_key varchar(32) NOT NULL default '0',
  post_parent int(10) NOT NULL default '0',
  post_htmlstate smallint(1) NOT NULL default '0',
  post_edit_reason VARCHAR(255) NOT NULL default '',
  PRIMARY KEY  (pid),
  KEY topic_id (topic_id,queued,pid,post_date),
  KEY author_id (author_id,topic_id),
  KEY post_date (post_date),
  KEY ip_address (ip_address),
  KEY post_key (post_key)
);";

 

ibf_topics

$TABLE[] = "CREATE TABLE ibf_topics (
  tid int(10) NOT NULL auto_increment,
  title varchar(250) NOT NULL default '',
  description varchar(70) default NULL,
  state varchar( default NULL,
  posts int(10) default NULL,
  starter_id mediumint( NOT NULL default '0',
  start_date int(10) default NULL,
  last_poster_id mediumint( NOT NULL default '0',
  last_post int(10) default NULL,
  icon_id tinyint(2) default NULL,
  starter_name varchar(32) default NULL,
  last_poster_name varchar(32) default NULL,
  poll_state varchar( default NULL,
  last_vote int(10) default NULL,
  views int(10) default NULL,
  forum_id smallint(5) NOT NULL default '0',
  approved tinyint(1) NOT NULL default '0',
  author_mode tinyint(1) default NULL,
  pinned tinyint(1) default NULL,
  moved_to varchar(64) default NULL,
  total_votes int(5) NOT NULL default '0',
  topic_hasattach smallint(5) NOT NULL default '0',
  topic_firstpost int(10) NOT NULL default '0',
  topic_queuedposts int(10) NOT NULL default '0',
  topic_open_time int(10) NOT NULL default '0',
  topic_close_time int(10) NOT NULL default '0',
  topic_rating_total smallint(5) unsigned NOT NULL default '0',
  topic_rating_hits smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (tid),
  KEY topic_firstpost (topic_firstpost),
  KEY last_post (forum_id,pinned,last_post),
  KEY forum_id (forum_id,pinned,approved),
  KEY starter_id (starter_id, forum_id, approved),
  KEY last_post_sorting (last_post,forum_id)
);";

 

ibf_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, 'Invision Power Board Team', 0, 1, '127.0.0.1', <%time%>, 0, 'Welcome to your new Invision Power Board&#33;<br /><br />  <br /><br /> Congratulations on your purchase of our software and setting up your community.  Please take some time and read through the Getting Started Guide and Administrator Documentation.  The Getting Started Guide will walk you through some of the necessary steps to setting up an IP.Board and starting your community. The Administrator Documentation takes you through the details of the capabilities of IP.Board.<br /><br />  <br /><br /> You can remove this message, topic, forum or even category at any time.<br /><br />  <br /><br /> Go to the documentation 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);";

 

If you consider i dont have to pay, i will make a donation to the board instead, if i can find the paypal button

 

[fenway]

I'll try and work through this later on tonight... though the last statement seems to have %var% in it?