Using PHP (or anything) to format SQL results on a webpage

kaisaj · August 31, 2010

After much tearing out of my hair I've got a simple database query using PHP to work!

But now I've got results that are cut off because one of the table fields contains data that is a little long. Not War and Peace - just a small paragraph. It is as follows

"Client(s) has/have retained firm for legal representation in debt matters. Pursuant to the Fair Debt Practices Collection act, 15 U.S.C. § 1692,

client(s) may no longer be contacted by collection representatives. All further collection based communications must be directed to client/s'

retained legal counsel, ******. All written collection correspondence must be directed to: *******."

Right now it's cutting off at the middle of communications. It seems like a simple problem but I can't find the solution anywhere. How do I format my SQL search results using PHP? Or anything for that matter.

Here's the code w/o database connection language.

mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());

// Set up our error check and result check array

$error = array();

$results = array();

// First check if a form was submitted.

// Since this is a search we will use $_GET

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

$searchTerms = trim($_GET['search']);

$searchTerms = strip_tags($searchTerms); // remove any html/javascript.

if (strlen($searchTerms) < 3) {

$error[] = "Search terms must be longer than 3 characters.";

}else {

$searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.

}

// If there are no errors, lets get the search going.

if (count($error) < 1) {

$searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE ";

// grab the search types.

$types = array();

$types[] = isset($_GET['body'])?"`sbody` LIKE '%{$searchTermDB}%'":'';

$types[] = isset($_GET['title'])?"`stitle` LIKE '%{$searchTermDB}%'":'';

$types[] = isset($_GET['desc'])?"`sdescription` LIKE '%{$searchTermDB}%'":'';

$types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

if (count($types) < 1)

$types[] = "`sbody` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked

$andOr = isset($_GET['matchall'])?'AND':'OR';

$searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `stitle`"; // order by title.

$searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");

if (mysql_num_rows($searchResult) < 1) {

$error[] = "The search term provided {$searchTerms} yielded no results.";

}else {

$results = array(); // the result array

$i = 1;

while ($row = mysql_fetch_assoc($searchResult)) {

$results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";

$i++;

}

function removeEmpty($var) {

return (!empty($var));

}

?>

<html>

<title>My Simple Search Form</title>

<h1>Client Database Search Form</h1>

<li>Enter your customer's name or the last six digits of his or her social security number to ascertain case status</li>

#error {

color: red;

}

</style>

<body>

<?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>

<form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">

Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /><br />

Search In:<br />

SSN: <input type="checkbox" name="body" value="on" <?php echo isset($_GET['body'])?"checked":''; ?> /> |

Name: <input type="checkbox" name="title" value="on" <?php echo isset($_GET['title'])?"checked":''; ?> /> |

Case Status: <input type="checkbox" name="desc" value="on" <?php echo isset($_GET['desc'])?"checked":''; ?> /><br />

Match All Selected Fields? <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?><br /><br />

</form>

<?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>

</body>

</html>

HELP Please. I'm so close. Any links, referrals, anything, I will take.

jayarsee · August 31, 2010

A girl that likes law and PHP?? IM HERE TO HELP!!!

When you say "cuts off" you mean you're not getting the full result back from the database? Or you are and when you try to format it text gets lost?

This conditional operation does not appear to have any curly braces:

if (count($types) < 1)

And its code is not on the same line

jayarsee · August 31, 2010

Here is a more legible version of that code so others might be able to study it:

<?php

mysql_select_db($dbDatabase) or 
trigger_error("Failed to connect to database {$dbDatabase}. Error: " . 
mysql_error());

// Set up our error check and result check array
$error = array();
$results = array();

// First check if a form was submitted.
// Since this is a search we will use $_GET
if (isset($_GET['search']))
{
$searchTerms = trim($_GET['search']);
 // remove any html/javascript
$searchTerms = strip_tags($searchTerms);

if (strlen($searchTerms) < 3)
{
	$error[] = "Search terms must be longer than 3 characters.";
}
else
{
	// prevent sql injection.
	$searchTermDB = mysql_real_escape_string($searchTerms);
}

// If there are no errors, lets get the search going.
if (count($error) < 1)
{
	$searchSQL = "	SELECT sid, sbody, stitle, sdescription
					FROM simple_search
					WHERE ";

	// grab the search types
	$types = array();
	$types[] = isset($_GET['body'])	? "`sbody` LIKE '%{$searchTermDB}%'" 		: '';
	$types[] = isset($_GET['title'])? "`stitle` LIKE '%{$searchTermDB}%'" 		: '';
	$types[] = isset($_GET['desc'])	? "`sdescription` LIKE '%{$searchTermDB}%'" : '';

	// remove any item that was empty (not checked)
	$types = array_filter($types, "removeEmpty");

	if (count($types) < 1)
	{
		// use the body as a default search if none are checked
		$types[] = "`sbody` LIKE '%{$searchTermDB}%'";
	}

	$andOr = isset($_GET['matchall']) ? 'AND' : 'OR';
	// order by title.
	$searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `stitle`";

	$searchResult = mysql_query($searchSQL) or 
					trigger_error("There was an error.<br/>" . 
					mysql_error() . "<br />SQL Was: {$searchSQL}");

	if (mysql_num_rows($searchResult) < 1)
	{
		$error[] = "The search term provided {$searchTerms} yielded no results.";
	}
	else
	{
		$results = array(); // the result array
		$i = 1;
		while ($row = mysql_fetch_assoc($searchResult))
		{
			$results[] = 	"{$i}: {$row['stitle']}
							<br />{$row['sdescription']}<br />
							{$row['sbody']}<br /><br />";
			$i++;
		}
	}
}
}

function removeEmpty($var)
{
return (!empty($var));
}
?>
<html>
   <title>My Simple Search Form</title>
   <h1>Client Database Search Form</h1>
   <li>Enter your customer's name or the last six digits of his or her social security number to ascertain case status</li>
   <style type="text/css">
      #error {
         color: red;
      }
   </style>
   <body>
      <?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>
      <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
         Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /><br />
         Search In:<br />
         SSN: <input type="checkbox" name="body" value="on" 		<?php echo isset($_GET['body']) ? "checked" : ''; ?> /> |
         Name: <input type="checkbox" name="title" value="on" 		<?php echo isset($_GET['title'])? "checked" : ''; ?> /> |
         Case Status: <input type="checkbox" name="desc" value="on" <?php echo isset($_GET['desc']) ? "checked" : ''; ?> /><br />
                 Match All Selected Fields? <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?><br /><br />
         <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
   </body>
</html>

kaisaj · August 31, 2010

Yes, I am much better at the law thing than the PHP but we persevere.

Thank you for fixing my code

It is getting cut off on the page.

This is what I get:

1: Name

Client(s) has/have retained firm for legal representation in debt matters. Pursuant to the Fair Debt Practices Collection act, 15 U.S.C. § 1692, client(s) may no longer be contacted by collection representatives. All further collection based communicatio (This is at the edge of my page)

SSN:XXX-XX-XXXX

jayarsee · August 31, 2010

Isn't it Fair Debt Collection Practices Act?

Just to make sure no time has wasted and this isn't a styling/DOM issue, have you checked the actual source HTML output and do you see the text getting cut off in the source also?

Everything from before <title> and after <style> should be in a set of <head> tags or the browser will get confused. Like this:

<html>
<head>
   <title>My Simple Search Form</title>
   <style type="text/css">
      #error {
         color: red;
      }
   </style>
</head>
   <body>

   <h1>Client Database Search Form</h1>
   <li>Enter your customer's name or the last six digits of his or her social security number to ascertain case status</li>

and <li> tags should always be nested inside of a parent <ol> or <ul> tag set.

mikosiko · August 31, 2010

could you post your table simple_search description?.... or tell us what datatype/length the column sdescription has... the example that you posted is exactly 254 characters long.... could be your sdescription field a VARCHAR(255) ?

kaisaj · August 31, 2010

Mikosiko - I did change the column sdescription from VARCHAR to text but you are right, it is acting just like a VARCHAR field.

Here's the structure:

Jayarsee, you are right - it's Act. Dumb brain work too hard - smart brain take vacation.

I viewed the source and it is getting cut off in the HTML output as well:

<br />Client(s) has/have retained firm for legal representation in debt matters. Pursuant to the Fair Debt Practices Collection act, 15 U.S.C. § 1692, client(s) may no longer be contacted by collection representatives. All further collection based communicatio<br />

[attachment deleted by admin]

jayarsee · August 31, 2010

I meant I think Collection and Practices got mixed up

Writing scary legal letters is a sick hobby of mine so I know the act intimately

I might have a theory about what happened. When it was a VARCHAR originally, MySQL truncated the contents to fit into a varchar field. When you changed it back to text later, the content that got truncated doesn't reappear - it's gone forever. I bet if you browse that field in PHPMyAdmin it is gone/truncated there and you have to repaste in the original into PHPMyAdmin.

kaisaj · August 31, 2010

YOUR THEORY WAS RIGHT! They were truncated because of the field's former VARCHAR exploits.

I reimported my csv file into the database and now the thing works great. Thank you so very much!

Sign In

Using PHP (or anything) to format SQL results on a webpage

Recommended Posts

kaisaj

Link to comment

Share on other sites

jayarsee

Link to comment

Share on other sites

jayarsee

Link to comment

Share on other sites

kaisaj

Link to comment

Share on other sites

jayarsee

Link to comment

Share on other sites

mikosiko

Link to comment

Share on other sites

kaisaj

Link to comment

Share on other sites

jayarsee

Link to comment

Share on other sites

kaisaj

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information