display only a part of a text string for PHP MySql feild

[Jonathan_Fortis]

i have a description (tk_job_desc) feild on a jobsearch that i only want to display say the first 5 lines of the description feild the click a read more option the take the user to a new page the read the full job outline

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  if (PHP_VERSION < 6) {

    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;   

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}

$maxRows_Recordset1 = 5;

$pageNum_Recordset1 = 0;

if (isset($_GET['pageNum_Recordset1'])) {

  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];

}

$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$var_tk_job_title_Recordset1 = "%";

if (isset($_GET["tk_job_title"])) {

  $var_tk_job_title_Recordset1 = $_GET["tk_job_title"];

}

$var_tk_job_location_Recordset1 = "%";

if (isset($_GET["tk_job_location"])) {

  $var_tk_job_location_Recordset1 = $_GET["tk_job_location"];

}

$var_tk_job_salary_Recordset1 = "%";

if (isset($_GET["tk_job_salary"])) {

  $var_tk_job_salary_Recordset1 = $_GET["tk_job_salary"];

}

mysql_select_db($database_hostprop, $hostprop);

$query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, tk_job_desc FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s", GetSQLValueString($var_tk_job_title_Recordset1, "text"),GetSQLValueString($var_tk_job_location_Recordset1, "text"),GetSQLValueString($var_tk_job_salary_Recordset1, "text"));

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $hostprop) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {

  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];

} else {

  $all_Recordset1 = mysql_query($query_Recordset1);

  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);

}

$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

?>

the html is

<table width="655" border="0" cellspacing="5" cellpadding="0">

            <tr>

              <td colspan="2" class="Titlegreen"><?php echo $row_Recordset1['tk_job_title']; ?></td>

            </tr>

            <tr>

              <td colspan="2" class="textblack"><?php echo $row_Recordset1['tk_job_desc']; ?></td>

            </tr>

            <tr>

              <td width="250"> </td>

              <td width="405"><div align="right"><span class="pos_salary"><?php echo $row_Recordset1['tk_job_salary']; ?></span><span class="pos_location">/<?php echo $row_Recordset1['tk_job_location']; ?></span></div></td>

            </tr>

          </table>

thanks

[bregent]

The simplest way is to just use a LEFT() function to limit the number of characters.

[Jonathan_Fortis]

ok thanks., i have found a few things on the internet

i followed the tutorial http://www.phpbuilder.com/board/showthread.php?t=10332476

"SELECT LEFT tk_job_title, tk_job_location, tk_job_salary, ('tk_job_desc',150)............... but it has returned and error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tk_job_title, tk_job_location, tk_job_salary, ('tk_job_desc',100) FROM think_job' at line 1

the other part of the article shows

<?php
mysql_connect("localhost", "user", "pass");
mysql_select_db("my_db");
$result = mysql_query("SELECT * FROM news ORDER BY RAND() LIMIT 0, 1");
while ($rows = mysql_fetch_array($result))
{
echo substr($rows['text'],0,100);
}
?>

looking at my code i need to add the tk_job_desc to the statement

$query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, tk_job_desc FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s", GetSQLValueString($var_tk_job_title_Recordset1, "text"),GetSQLValueString($var_tk_job_location_Recordset1, "text"),GetSQLValueString($var_tk_job_salary_Recordset1, "text"));

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $hostprop) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

would i need to put that statement in there?

[Jonathan_Fortis]

i found another site and done the following

$query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc, 200) FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s", GetSQLValueString($var_tk_job_title_Recordset1, "text"),GetSQLValueString($var_tk_job_location_Recordset1, "text"),GetSQLValueString($var_tk_job_salary_Recordset1, "text"));

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $hostprop) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {

  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];

} else {

  $all_Recordset1 = mysql_query($query_Recordset1);

  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);

}

$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

it now doesnt show and error but just does not display any results from the tk_job_desc

[bregent]

>"SELECT LEFT tk_job_title, tk_job_location, tk_job_salary,

>('tk_job_desc',150)............... but it has returned and error

Your syntax is wrong. LEFT() is a funtion. Functions are always directly followed by parenthesis that contain arguments.

SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT ('tk_job_desc',150)

It would be a really good idea to become familiar with the online MySQL reference manual. All functions are listed along with the proper usage and examples. If you are not sure which function to use, browse through the various categories. In this case, you are trying to manipulate a string, so you would look through the string functions. Same goes for php.

[Jonathan_Fortis]

It would be a really good idea to become familiar with the online MySQL reference manual. All functions are listed along with the proper usage and examples. If you are not sure which function to use, browse through the various categories. In this case, you are trying to manipulate a string, so you would look through the string functions. Same goes for php.

i am trying to become familair with both MYSQL and PHP hense trawling through the internet and coming on this forum. are there any online reference manuals you can recommend?

[Jonathan_Fortis]

LEFT('tk_job_desc',200) doesnt work

$query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT('tk_job_desc',200) FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s", GetSQLValueString($var_tk_job_title_Recordset1, "text"),GetSQLValueString($var_tk_job_location_Recordset1, "text"),GetSQLValueString($var_tk_job_salary_Recordset1, "text"));

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $hostprop) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

[bregent]

>LEFT('tk_job_desc',200) doesnt work

You put quotes around your column name (and so did I when I copied your text). You are referencing a column, so don't includes single quotes:  LEFT(tk_job_desc, 200)

[Jonathan_Fortis]

i changed the statement to LEFT(tk_job_desc, 200) but didnt work. I did look at the full code and noticed that the below was missing

$var_tk_job_salary_Recordset1 = "%";

if (isset($_GET["tk_job_salary"])) {

  $var_tk_job_salary_Recordset1 = $_GET["tk_job_desc"];

}

------------------------

OR tk_job_desc LIKE %s

------------------------

GetSQLValueString($var_tk_job_desc_Recordset1, "text")

so i added them

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  if (PHP_VERSION < 6) {

    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;   

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}

$maxRows_Recordset1 = 5;

$pageNum_Recordset1 = 0;

if (isset($_GET['pageNum_Recordset1'])) {

  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];

}

$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$var_tk_job_title_Recordset1 = "%";

if (isset($_GET["tk_job_title"])) {

  $var_tk_job_title_Recordset1 = $_GET["tk_job_title"];

}

$var_tk_job_location_Recordset1 = "%";

if (isset($_GET["tk_job_location"])) {

  $var_tk_job_location_Recordset1 = $_GET["tk_job_location"];

}

$var_tk_job_salary_Recordset1 = "%";

if (isset($_GET["tk_job_salary"])) {

  $var_tk_job_salary_Recordset1 = $_GET["tk_job_salary"];

}

$var_tk_job_salary_Recordset1 = "%";

if (isset($_GET["tk_job_salary"])) {

  $var_tk_job_salary_Recordset1 = $_GET["tk_job_desc"];

}

mysql_select_db($database_hostprop, $hostprop);

$query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,100) FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s OR tk_job_desc LIKE %s", GetSQLValueString($var_tk_job_title_Recordset1, "text"),GetSQLValueString($var_tk_job_location_Recordset1, "text"),GetSQLValueString($var_tk_job_salary_Recordset1, "text"),GetSQLValueString($var_tk_job_desc_Recordset1, "text"));

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $hostprop) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {

  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];

} else {

  $all_Recordset1 = mysql_query($query_Recordset1);

  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);

}

$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

?>

but it still doesnt work

[bregent]

>but it still doesnt work

Can you be more specific?

[Jonathan_Fortis]

it is not returning and error just not displaying any results for the tk_job_desc

[bregent]

How are you referencing that field in the php code? You didn't create an alias in your SELECT statement so you can't reference it by name. Create an alias and reference that in your script - otherwise you need to reference it by position.

[Jonathan_Fortis]

>How are you referencing that field in the php code?

all the code is in the above post

.>You didn't create an alias in your SELECT statement so you can't reference it by name

SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,100) FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s OR tk_job_desc LIKE %s

i thought LEFT(tk_job_desc,100) and tk_job_desc LIKE %s we what needed to go into the SELECT script?

[bregent · Accepted solution]

>i thought LEFT(tk_job_desc,100) and tk_job_desc

>LIKE %s we what needed to go into the SELECT script?

Yes, but tk_job_desc is not actually in your SELECT list, so you can't reference that in your php script.  What IS in your SELECT list is a SQL function that uses tk_job_desc as a parameter. But the name of the column that is returned for that field is unknown and is definitely not tk_job_desc. You either need to give it an alias, or reference it by its position in the select list. So try something like:

SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,100) as truncated_job_desc FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s OR tk_job_desc LIKE %s

And then be sure to use truncated_job_desc in your php script when you display the value.

[Jonathan_Fortis]

thats brilliant, that worked perfectly

thanks so much

[bregent]

The official reference manuals are here:

http://dev.mysql.com/doc/refman/5.1/en/

http://www.php.net/manual/en/langref.php

http://www.php.net/quickref.php

[Jonathan_Fortis]

The official reference manuals are here:

http://dev.mysql.com/doc/refman/5.1/en/

http://www.php.net/manual/en/langref.php

http://www.php.net/quickref.php

Thanks i will look at them

[bregent]

>echo substr($rows['text'],0,100);

Here you are using php to truncate the text. You need to either truncate in your script, or in your SQL SELECT, not both. If you are only going to display the truncated text from that query, then do it in the SQL. If you are going to display the truncated text, and also display the full text without re-querying the data, then do it in your php script.

[Jonathan_Fortis]

so you mean if i only want to use the left function on one of the results DONT put the left function in the SELECT put it in the <?php echo $row_Recordset1['tk_job_desc']; ?>