PROS! Quick help with limiting output...

[brianb]

hello,

I have a search query returning all sorts of cool data from my database. My question is, how do I limit the number of characters for a certian field?

For instance, I would like to return the first 100 characters of the data in that field, then print something like:

...[more]

Of course then linking to the full article. Make sense?

Please help!

Thanks in advance.

[bladx]

Use the substr() function and you can learn about it here:

http://www.php.net/manual/en/function.substr.php

By the way, why do you use PROS! at the beginning of every question?

[mrmufin]

If you're using MySQL, you can also limit the result by using the SUBSTRING() function:

SUBSTRING(string FROM position FOR length) in your select query:

SUBSTRING('12345',2,3) returns "234"

It takes the character at string position 2, and returns it as the first of three characters following it.

If you're searching large text columns say a column named 'description', and you want to return the first 100 characters:

SELECT SUBSTRING(description,1,100) FROM table...

would return the first 100 characters from the 'description' column in the table 'table'.

While bladxs' solution using substr() within PHP would also work, limiting the data coming out of the database helps to minimize resources by only returning what you need, rather than doing post-result set processing.

[bladx]

limiting the data coming out of the database helps to minimize resources by only returning what you need, rather than doing post-result set processing.

Good point mrmufin. This really starts (for efficiency and minimizing resources) to help farther down the line too ... when (and if) you have more traffic, or have larger items in the database that will be looked up, or printed out, etc.

[brianb]

Thank guys for the help...right now I'm returning an error, but I think it's becuase I'm not sure how to do multiples SELECTs here.

here's my code:

$sql = "SELECT SUBSTRING(plot,1,25) as plot, * FROM Film WHERE $field LIKE '%$searchString%' ORDER BY title";

Is that correct sytax? If not, please help.

Andy, hahaha, why do I start every thread with PROS? Well, it's a habit I got into...just in case I didn't check the thread for a day or two it would be easy to search out. Sorry if it's an annoyance. 😃

[mrmufin]

Try (note the quotes around the column alias):

$sql = "SELECT SUBSTRING(plot,1,25) as 'plot', * FROM Film WHERE $field LIKE '%$searchString%' ORDER BY title";

Works for me...

[brianb]

Darn...still not working. I get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

[mrmufin]

Okay, so we add some error-checking:

$sql = "SELECT SUBSTRING(plot,1,25) as 'plot', * FROM Film WHERE $field LIKE '%$searchString%' ORDER BY title";

// Set $r as a result set identifier:
$r = mysql_query($sql);

// If there's no rows in the result or the query failed, exit printing out the query:
if(!$r || !mysql_num_rows($r)) {
	exit("Query $sql either failed has no rows in the result.");
}

// If we get this far, there *must* be rows:
while($data = mysql_fetch_array($r)) {
	// do shtuff with yer data here
}

// Free up the resources from the result set:
mysql_free_result($r);

Any better?

[bladx]

Does this work:

$sql = "SELECT SUBSTRING(plot,1,25) as plot, title, from Film WHERE $field LIKE '%$searchString%' ORDER BY title";

Instead of:

$sql = "SELECT SUBSTRING(plot,1,25) as 'plot', * FROM Film WHERE $field LIKE '%$searchString%' ORDER BY title";

I am wondering if it is the '*' character in there that is messing it up for you. I tried running this type of query on my MySQL and had to take it out to get it to work. If you need to select more than just the plot item, which you probably want to, just add it with a comma next to the plot area, and where title is.

[mrmufin]

Good catch, bladx.

I re-ran my test with the '' in the query and it failed. This kinda smells like a MySQL bug... or perhaps, MySQL is getting confused since it's being told to select the first 'n' characters from a column, then everything using the wildcard, ''. But I would suspect MySQL would handle that more gracefully. I do similar type queries incorporating MySQL functions on certain columns, and then select '*' on top of it without a hitch. But this seems to fail for all of us.

Anyway, it's better practice to only select what you really need...

[brianb]

None of these solution still not working. Maybe doing a select and defining the "plot" variable, then doing another select for the rest?

[mrmufin]

No, no, no, no, no! That's a kludge, and we'd all sleep much better at night knowing the code is 'as good as it can be' without wimping out and using multiple SELECTs!

? Did you make sure your alias column is quoted? Try just a one column select like this and see what happens:

$sql = "SELECT SUBSTRING(plot,1,25) as 'plot' FROM Film WHERE $field LIKE '%$searchString%' ORDER BY title"; 

I know that this works, and does what it should.

If that doesn't work, try hard-coding $field with an actual column name...

Let's see what happens...

[brianb]

Ok...

here's what I get with this new snippet of code:

Parse error: parse error, unexpected '\"' on line 130

But, this works beautifully, just doesn't crop the field I want it to:

$sql = "SELECT * FROM Film WHERE $field LIKE '%$searchString%' ORDER by title";

hhhhhhhhmmmmmmmmrrrrgggggg. Frustrating.

[mrmufin]

Could you just copy and paste yer script into the post box? This really shouldn't be this big of a hassle...

[brianb]

hahahha.....ok cool! Thanks for the help!

<?php 

$sql_=_"SELECT_SUBSTRING(plot,1,25)_as_'plot'_FROM_Film_WHERE_$field_LIKE_'%$searchString%'_ORDER_BY_title";

$res = mysql_query ( $sql ); 

while ( $arr = mysql_fetch_array ( $res ) ) 
{ 
echo "<table width='100%' border='0' cellspacing='0' cellpadding='3'>
  <tr>
    <td width='0%' valign='top'><a href='/movies/filmFINAL.php?filmID=$arr[filmID]' class='dvdimages'><img src='/movies/images/thumbs/$arr[filmID].jpg' width='60' height='89' border='1'></a></td>
    <td width='100%'>
      <p><a href='/movies/filmFINAL.php?filmID=$arr[filmID]' class='main'>$arr[title]</a><br>
        <img src='/media/random/1pix_black_dot.gif' width='100%' height='1' vspace='4'><b>Starring:</b> 
        $arr[starring]<br>
        <b>Plot:</b> $arr[plot]</p>
      </td>
  </tr>
</table><br>
";
} 
?>

Thanks!

[mrmufin]

// We build the query. Since the result references in your HTML table reference more columns, I've changed this to select the columns you reference:
$sql = "SELECT SUBSTRING(plot,1,25) AS \"plot\", filmID, starring FROM Film WHERE $field LIKE \"%$SearchString%\" ORDER BY title";

// We try to get a result set handle:
$res = mysql_query($sql);

// We veryify that we got a result set handle, and if not, we display the query to make sure it looks like what we expect it to look like:
if(!$res || !mysql_num_rows($res)) {
	echo "Query $sql did not execute properly or there are no rows in the result set.";
} else {
	// If we make it to here, there must be a result that has data associated with it:
	// Also, since you're not using the indexes, providing a more specific array type to fetch_array saves some memory:
	while($arr = mysql_fetch_array($res, MYSQL_ASSOC)) {
		// start a table for each record:
		echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"3\">";

	// start a table row
	echo "<tr>";

	// start the first table data cell, edit this to meet your display needs. I've shortened it for brevity:
	echo "<td align=\"top\">";
	printf("<a href=\"/movies/filmFINAL.php?filmID=%s\"><img src=\"/movies/images/thumbs/%s.jpg\"></a>", $arr['filmID'], $arr['filmID']);
	echo "</td>";

	// start the next td cell:
	echo "<td width=\"100%\">";

	//echo "<some image tag>"
	printf("<p><b>Starring:</b> %s <br /><b>Plot:</b> %s"</p>, $arr['starring'], $arr['plot']);
	// end the td cell:
	echo "</td>";
	// end the table row
	echo "</tr>";

	// end the table for each record:
	echo "</table>";
}

}

[Weedpacket]

I'm thinking that a reason why

SELECT SUBSTRING(plot,1,25) as 'plot', * FROM Film WHERE $field LIKE '%$searchString%' ORDER BY title

would fail would be because you're trying to return a column containing the substring, and calling it plot, then all the columns in the table - one of which is called plot. But the two plots don't contain the same information. So which one is it supposed to be?

The solution would be to drop the * and list the fields required explicitly. For one thing, it reduces the amount of data sent by the database back to PHP to what is actually wanted, making things faster at both ends. For another, it's a waste of time asking for SUBSTRING(plot) if you're just going to ask for the entire plot at the same time.

There is an alternative workaround that's pretty straightforward, but I don't want to encourage it - it's one I'd describe as "cheap-n-nasty".

[mrmufin]

Weed--

as we got further into this and I got brianb to post the code, I noticed that he's selecting one thing, a SUBSTRING(plot,1,25) on the plot column, but refering to more columns when dealing with the results from fetch_array().

I don't use the MySQL SUBSTRING() function too much, but I have used other MySQL functions in the SELECT portion of a query and then followed up with a wildcard, '' and had it work just fine. This is what leads me to believe that MySQL should handle this situation more gracefully then not returning anything. Yeah, it's not the most wonderful programming practice, but MySQL should friggen do you what you tell it to do, as long as it can* do what you tell it, whether it's good or bad...

[Weedpacket]

Yeah, but did you give the computed column the same name as an existing column (that is also being returned)? (That, incidentally, is an allusion to my "cheap-n-nasty", which is why I didn't bring it up). I'm not clear on what the current SQL spec says about this, but it's probably not good.

I mean, will it kill anyone if he said

SELECT SUBSTRING(plot,1,25) AS plot, filmID, starring FROM Film WHERE $field LIKE '%$searchString%' ORDER BY title

?

As for whether or not MySQL should do it if it can whether good or bad (though in this case it looks like MySQL can't) - well, that's one of the reasons why there is so much scitan HTML around - because IE can render it. It's something of a philosopical question, though.

On a bit of a tangent; one thing sometimes I do if I realise I'm going to be SELECTing a transformed column on a frequent basis, I often store both original and the transformed versions - so the the transformation need only be done once during insertion and then selected as often as I like without needing to do the transformation again. It's the old time/space tradeoff; and these days memory is cheap, time is scarce.

[mrmufin]

'scitan' - now there's a word I need to go look up.

Uh, no I always aliased my computed columns with special names (like 'UPDATE' or 'DELETE'). I wonder what the E.F. Codd immaculati have to say about that!

And, yes, because MySQL 'can' do something, I believe it should do it. I should be allowed to suffer the consequences for any sloppy coding practice in which I care to indulge... I can over-clock my processors, because my motherboard allows it. If I screw 'em up, it's my fault. The ABIT manual has some strict warnings about processor over-clocking, yet it lets me do it. The MySQL manual has plenty of splendid advice on writing fast, efficient queries, proper data structure design, indexing and other such wonderful thangs... I should be allowed to ignore it and suffer any performance penalties or weirdnesses which may result...