Skip to main content
J
Jonathan_Fortis
Inspiring
March 9, 2012
Answered

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

  • March 9, 2012
  • 1 reply
  • 2657 views

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

This topic has been closed for replies.
Correct answer bregent

>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?


>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.


1 reply

B
bregent
Participating Frequently
March 9, 2012

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

J
Jonathan_FortisAuthor
Inspiring
March 10, 2012

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?



B
bregent
Participating Frequently
March 12, 2012

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);


>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)

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices