Page 1 of 1

Echo query until search variable is found using substr()

Posted: Tue Mar 09, 2004 9:05 am
by The Monkey
Hello, I'm new here!

I'm working on my website redesign, and except for a few things, it's gone smoothly. Now I'm actually inserting the data into the mySQL database, and I found that I need somekind of UNTIL function when grabbing data from the database.

I have two tables in my database, 'directory', and 'pages'. My site is organised into directorys, then pages inside directorys.

So in the 'directory' "Lego Creations" I have "Lego Plane", "Lego Car" linked to "Lego Creations" in my 'pages' table.

What I want to do is have the main 'directory' pages have a little bit of information about my "Latest Creation" or update, by

Code: Select all

SELECT * FROM pages WHERE name = '$directoryname' LIMIT 1
Now, I inside the 'pages' entrys I have them divided into 'subsections' using monkey_code. For instance, in the pages that are in my "Lego Movies" directory, I have the directors writeup, then I put [downloadinfo] and my php script replaces that with

Code: Select all

<hr><b>Download Information:</b>
.

What I want the main page of each of my directorys to do is echo the content of the latest creation in that category, UNTIL it sees [downloadinfo] or some such other variable.

So, to recap:

my code should grab the latest entry in the 'pages' table, but only from the entries that are tied to the correct 'directory' row. My code should then echo the content in respective 'pages' row, UNTIL it sees [downloadinfo], when it should just stop printing the data from the db.

Also, I tried substr(), but since it only works with numbers, or so I've been told, it didn't work out well. The length of my entries changes each time! ;)

Is this even possible? I've never heard of such a thing, but with php anything is possible, so I'm sure it can be done!

If I did not make something clear enough, I apologize, I will be glad to clear up any questions you may have.

- The Monkey

Posted: Tue Mar 09, 2004 11:29 am
by mark-s
I may have misunderstood, but it sounds like you are retrieving a row from a database with only 1 column, and several bits of information are held within that column. Is that correct?

If so, is there a reason why you're not using columns for each piece of information? i.e a column for directors writeup, another for creation name or whatever. that way you can specify what information is returned.

Also, if you have an 'ID' column, populated by a sequence or auto_increment value, you can use this to get the latest addition.

FYI, substr() does work with strings(hence the name!) - it returns a number which is the location of your found charcter(s).

If I've completely got the wrong end of the stick - I apologise!

Posted: Tue Mar 09, 2004 3:15 pm
by The Monkey
For organizing reasons, I really wanted just two tables, with all of the content in one collumn.

However, after you put it the way you did, I see that I could have 2-3 'content' collumns, and if there is no need for content in the 2nd-3rd one, just leave it null.

But I'm liking the substr() way best, and selecting just the content before the [downloadinfo].... how would I split the $content from the db in this way and just echo the part I want?

I DO have more then one column in my 'pages' table, it has id,name,title,content,lastupdate,directory. It selects the last entry based on id.

Here is my php code, and my Db setup:

PHP Code: (pertaining to problem)

Code: Select all

<?php
// $name is what directory we are in, in this case "monkeystudios", or my film studios name 
$contentquery = "SELECT content FROM directory WHERE name = '$name'";
$contentresult = mysql_query($contentquery);
$row = mysql_fetch_array($contentresult);
$content = $row['content'];
$content = nl2br($content);
echo $content;
$latestfilm = "SELECT * FROM pages WHERE directory = 'monkeystudios' ORDER BY id DESC LIMIT 1";
$result = mysql_query($latestfilm);
$row = mysql_fetch_array($result);
$content = $row['content'];
$latestname = $row['name'];
$latesttitle = $row['title'];

// This query selects the number of characters to display with substr, this code will disappear once I know how to work substr the way I want
    $limitquery = "SELECT * FROM config WHERE config_name = 'textlimit_ms'";
		$textlimit_ms = mysql_query($limitquery) or die(mysql_error());
			$row = mysql_fetch_array($textlimit_ms);
				$textlimit_ms = $row['config_value'];

$filminfo = substr($content, 0, $textlimit_ms) . "...";
$filminfo = nl2br($filminfo);
echo '<p class="header1" align="center"><a href="monkeystudios.php?page=' .$latestname. '">' .$latesttitle. '</a></p>';
echo '<img src="http://yolegoman.com/files/monkeystudios/' .$latestname. '.jpg" hspace = "15" vspace = "10" align="left"';
echo ' border = "5">';
echo $filminfo;
echo '<br /><br />';
?>
SQL Table:

Code: Select all

CREATE TABLE `pages` (
  `id` tinyint(10) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL default '',
  `title` varchar(30) NOT NULL default '',
  `content` longtext NOT NULL,
  `lastupdate` date NOT NULL default '0000-00-00',
  `directory` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;
Thank you! I hope we can get this solved soon!

Posted: Tue Mar 09, 2004 5:08 pm
by Illusionist
to anyswer your question about how to search and retrieve text before [downloadinfo] try soemthing like this:

Code: Select all

<?php
$str = "a bunch of junk.<br>\nits not needed for anything useless!!<br>\n[downloadinfo]<br>\nfile: cool.txt<br>\nsize: 0b<br>\nfunction: nothing";
$search = "[downloadinfo]";
echo substr($str, 0,strpos($str,$search))
?>
And that would return:

Code: Select all

a bunch of junk.
its not needed for anything useless!!

Posted: Tue Mar 09, 2004 6:13 pm
by The Monkey
Wow! Thanks guys! That's awesome!

Here is the finished code, for anybody else who needs help:

Code: Select all

<?php
$contentquery = "SELECT content FROM directory WHERE name = '$name'";
$contentresult = mysql_query($contentquery);
$row = mysql_fetch_array($contentresult);
$content = $row['content'];
$content = nl2br($content);
echo $content;
$latestfilm = "SELECT * FROM pages WHERE directory = 'monkeystudios' ORDER BY id DESC LIMIT 1";
$result = mysql_query($latestfilm);
$row = mysql_fetch_array($result);
$content = $row['content'];
$latestname = $row['name'];
$latesttitle = $row['title'];

   // THE CODE THAT I NEEDED HELP WITH

	$filminfosearch = '[download]';
	$filminfo = nl2br($content);
	$filminfo = substr($filminfo, 0, strpos($filminfo,$filminfosearch));
	
echo '<p class="header1" align="center"><a href="monkeystudios.php?page=' .$latestname. '">' .$latesttitle. 

'</a></p>';
echo '<img src="http://yolegoman.com/files/monkeystudios/' .$latestname. '.jpg" hspace = "15" vspace = "10" 

align="left"';
echo ' border = "5">';
echo $filminfo;
echo '<br /><br />';

?>
THANKS AGAIN!

EDIT: I discovered a tiny, nearly insignificant, bug after posting this. The nl2br function on the $latestfilm query was not set up perfectly. It works now!

Posted: Tue Mar 09, 2004 6:24 pm
by Illusionist
welcome! np