CtrlAltDel Posted January 13, 2008 Share Posted January 13, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/ Share on other sites More sharing options...
fenway Posted January 14, 2008 Share Posted January 14, 2008 What's in "post"? The whole things? Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-438767 Share on other sites More sharing options...
CtrlAltDel Posted January 14, 2008 Author Share Posted January 14, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-438998 Share on other sites More sharing options...
fenway Posted January 14, 2008 Share Posted January 14, 2008 You should be able to search for the first whitespace. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-439092 Share on other sites More sharing options...
CtrlAltDel Posted January 14, 2008 Author Share Posted January 14, 2008 yes, thast what i am trying to do, but search what string i should be searching in? Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-439112 Share on other sites More sharing options...
fenway Posted January 15, 2008 Share Posted January 15, 2008 SUBSTRING(post FROM LOCATE('http://',post) FOR LOCATE(' ',post,LOCATE('http://',post))-1) AS url Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-439435 Share on other sites More sharing options...
CtrlAltDel Posted January 15, 2008 Author Share Posted January 15, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-439712 Share on other sites More sharing options...
fenway Posted January 15, 2008 Share Posted January 15, 2008 That's no really possible... post the value of "post" in your example. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-439832 Share on other sites More sharing options...
CtrlAltDel Posted January 15, 2008 Author Share Posted January 15, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-440016 Share on other sites More sharing options...
CtrlAltDel Posted January 15, 2008 Author Share Posted January 15, 2008 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; Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-440021 Share on other sites More sharing options...
fenway Posted January 15, 2008 Share Posted January 15, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-440171 Share on other sites More sharing options...
CtrlAltDel Posted January 15, 2008 Author Share Posted January 15, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-440186 Share on other sites More sharing options...
fenway Posted January 15, 2008 Share Posted January 15, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-440234 Share on other sites More sharing options...
CtrlAltDel Posted January 16, 2008 Author Share Posted January 16, 2008 $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 ? Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-440928 Share on other sites More sharing options...
CtrlAltDel Posted January 16, 2008 Author Share Posted January 16, 2008 (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 Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-441079 Share on other sites More sharing options...
fenway Posted January 17, 2008 Share Posted January 17, 2008 The first code that I gave you works when I tested it... what version are you using? Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-441468 Share on other sites More sharing options...
CtrlAltDel Posted January 17, 2008 Author Share Posted January 17, 2008 Sorry, but is this meant for another thread? MySql version 4.1.22 phpMyAdmin - 2.11.0 thanks Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-441692 Share on other sites More sharing options...
CtrlAltDel Posted January 17, 2008 Author Share Posted January 17, 2008 Oh i see, this thread has been merged with my other one about extra columns Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-441693 Share on other sites More sharing options...
fenway Posted January 17, 2008 Share Posted January 17, 2008 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... Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-441757 Share on other sites More sharing options...
CtrlAltDel Posted January 17, 2008 Author Share Posted January 17, 2008 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 ? Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-441871 Share on other sites More sharing options...
fenway Posted January 17, 2008 Share Posted January 17, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-441877 Share on other sites More sharing options...
CtrlAltDel Posted January 17, 2008 Author Share Posted January 17, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-441969 Share on other sites More sharing options...
fenway Posted January 17, 2008 Share Posted January 17, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-442065 Share on other sites More sharing options...
CtrlAltDel Posted January 17, 2008 Author Share Posted January 17, 2008 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!<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 Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-442093 Share on other sites More sharing options...
fenway Posted January 18, 2008 Share Posted January 18, 2008 I'll try and work through this later on tonight... though the last statement seems to have %var% in it? Quote Link to comment https://forums.phpfreaks.com/topic/85824-for-locate-function-problem/#findComment-442785 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.