[SOLVED] PHP Form using "Textarea"

[dstaton]

I am trying to add a textarea to a PHP form to query for House Numbers in a database.  I was able to make this work with just a text field but need to allow my users to input several House Numbers in one query.  Here is what I tried on the form:

 

]<td class=Header valign=top align=center>Enter House Number(s)<br>to Search for:</td>
	<td><TEXTAREA NAME="HouseNum" ROWS=2 COLS=40></TEXTAREA></td>

Here is how I call that in the report:

 

...where upper(main_house_media(vc.version_copy_id)) in (upper('%" . $_POST["HouseNum"] . "%'))
...foreach ($_POST as $key=>$value){
echo '<br />key=' . $key . '...value=' . $value;
	if (substr($key,0, == 'HouseNum') {
		if ($value > "       ") {
			echo '<br />HouseNum value = on';
			$housenbrentered = true;
		}
	}
}
echo '<br />housenbrentered=' . $housenbrentered;
if (!$housenbrentered){
	$errfound = true;
	$errstring = 'A House Number must be entered\n';
  }

This works when I just use the text, but that isn't large enough.  I need the users to be able to enter as many House Numbers to look for as they want.  Some example House Numbers are 'CCB1261' and 'MCP7938'.  I can't find much info about the textarea in regards to pulling info for a report.  I can find plenty of textarea issues with updating a database, but that is not what I need.

<td class=Header valign=top align=center>Enter House Number<br>(or portion of)<br>to Search for:</td>
	<td class=Header><input type=text name=HouseNum size=7 maxlength=7></td>

[per1os]

You need to inform the user to enter them with a space or a comma in between each and than explode them at the comma or space.

 

There has to be something that signifies from one house number to the next. If you want an input type of text to be bigger, change the size and maxlength to be more. That will allow for more than just one house number.

 

The key for either is making sure the user enters them in with a space or a comma separated, unless each house number is the exact same length, than you can just use www.php.net/substr on the string.

[dstaton]

I guess I don't know how to "explode" from the textarea.  However, this query doesn't work even when I only put in one house number.

[per1os]

Well first things first, you need to santize your data before checking it against any database record.  Also your query may need some work, I do not think you can use the LIKE methods of % in the IN statement, I am unsure 100% but pretty sure.

 

Most likely either A or B is happening, A your data from POST has an extra whitespace character at the end, which it should be www.php.net/trim trimmed off.

 

B. Your query is just totally messed up, try this instead:

where main_house_media(vc.version_copy_id) LIKE ('%" . $_POST["HouseNum"] . "%')

 

I have no clue what main_house_media is. Is that the database? I never seen that type of functionality used. Also if you are trying to enter in multiple house numbers you are not trying to format them before you enter them into the statement.

 

Let's say you tell users that the house numbers must be entered seperated by commas. IE:

 

<?php
$_POST['housenum'] = "MCP7938, CCB1261";

// first we need to explode the data into an array
$house_num = explode(",", $_POST['housenum']);

// next we need to loop through and format the data.
if (is_array($house_num)) {
     $housenum = "";
     foreach ($house_num as $num) {
             $housenum .= "'" . trim($num) . "',";
     }
     $housenum = substr($housenum, 0, -1); // remove the extra comma
}

$SQL = "SELECT * FROM table_name where housenum in(" . $housenum . ")";
?>

 

That is the basic gist of what you want to do.

 

www.php.net/substr

www.php.net/trim

www.php.net/explode

[dstaton]

OK, I am pretty new to this.  Basically our reports are generated from the following:  Process.php and Select.php.  The query is in the Process.php file. The user input (form) is in the Select.php file.  So this line of code: 

$_POST['housenum'] = "MCP7938, CCB1261"; 

would be in the Process.php file.  What would it actually look like since that doens't get input until the user queries for the House Number on the form?

 

main_house_media is a function written within my company to pull the house number out of the database.  It is not a PHP or SQL function.

[per1os]

That is how the user would have to input that. The POST data comes from the FORM, I just put it in there for testing purposes so you could visually see how the user needs to enter data for it to be exploded etc. That line is irrelevant, basically when a user enters data in the text field or text area, that is how they have to format the house numbers for this script to function properly.

[dstaton]

Why do I put

$SQL = "SELECT * FROM main_house_media(vc.version_copy_id) in (" . $housenum . ") ";

as well as

where main_house_media(vc.version_copy_id) like ('%" . $_POST["housenum"] . "%') 

 

[dstaton]

I know that I am probably doing something stupid.  Can you look over the code and see what it might be?  Maybe I have not defined something correctly or at the wrong time.  It is just sending me a blank report even though I know I should have at least 4 pages.

 

<HTML>
<HEAD>
<LINK rel="stylesheet" type="text/css" href="/IncludeFiles/MainStyle.css">
</HEAD>
<BODY>
<script>
var outfilelist = new Array();
</script>
<?php 
set_time_limit(960);
require_once ('xlwriter.php');
     echo '<br />*** Post Array=';
echo '<pre>';
print_r ($_POST);
echo '</pre>***';

function GenerateReport($repstartdate,$rependdate,$connection,$housenbrmatch){;
// Build the workbook
	// first, build the workbook name and path 
$time = time();
 $time = substr($time,5); // only get the last 5 digits
 $ReportFileName = 'PromoPlayHistory-' . strftime('%Y%m%d',$repstartdate) . '-' . strftime('%Y%m%d',$rependdate) . '-' . $time . '.xls';
$ReportFilePath = '\\Report_Output\\PromoPlayHistoryReports\\';
		//build the worksheet
$wksheets['PromoPlayHistory'] = 'PromoPlayHistory';
$wksheetcnt = 1;
  	// now, create the workbook					
$excelconn = sheetCreate($ReportFilePath, $ReportFileName, $wksheets); 
    $allrowindex = 0;	
   //create the styles to be used
$title = titleStyleCreate($excelconn);
$heading = headingStyleCreate($excelconn);
$detail = detailStyleCreate($excelconn);
$detailCenter = detailCenterStyleCreate($excelconn);
$titlestyle = styleCreate($excelconn,array('HorizontalAlignment'=>2,'VerticalAlignment'=>1,'FontName'=>'Arial','FontSize'=>12,'Bold'=>1,'WrapText'=>1));
$headerstyle = styleCreate($excelconn,array('BorderLineStyle'=>2,'HorizontalAlignment'=>2,'FontName'=>'Arial','FontSize'=>10,'Bold'=>1,'WrapText'=>0));
$detailstyle = styleCreate($excelconn,array('BorderLineStyle'=>1,'HorizontalAlignment'=>1,'VerticalAlignment'=>0,'FontName'=>'Arial','FontSize'=>9,'WrapText'=>1));
$detailboldstyle = styleCreate($excelconn,array('HorizontalAlignment'=>1,'VerticalAlignment'=>0,'FontName'=>'Arial','FontSize'=>9,'Bold'=>1,'WrapText'=>1));
$detailcenterstyle = styleCreate($excelconn,array('BorderLineStyle'=>1,'HorizontalAlignment'=>2,'VerticalAlignment'=>0,'FontName'=>'Arial','FontSize'=>9,'WrapText'=>1));
	// write the Title to the worksheet		
$allrowindex = 1;
centerWorksheet($excelconn,1);	
repeatHeaderRows($excelconn,1,2,1);
  //write the Title to the worksheet
mergeCells($excelconn,$allrowindex,1,1,1,9);
rowHeight($excelconn,$allrowindex,1,40);
titleStyle($excelconn,1,$allrowindex,1,$title);
writeCell($excelconn,$allrowindex,1,1,'Promo Play History Summary Report for '. strftime('%B %d, %Y',$repstartdate) . ' through ' . strftime('%B %d, %Y',$rependdate) );
  //write the header & footer lines 
$allrowindex++;
	writeCell($excelconn,1,$allrowindex,1,'Title',$headerstyle,20);
  	writeCell($excelconn,1,$allrowindex,2,'House Number',$headerstyle);
  	writeCell($excelconn,1,$allrowindex,3,'Version',$headerstyle);
	writeCell($excelconn,1,$allrowindex,4,'TX Type',$headerstyle);
	writeCell($excelconn,1,$allrowindex,5,'Duration',$headerstyle);
  	writeCell($excelconn,1,$allrowindex,6,'Channel Code',$headerstyle);
    writeCell($excelconn,1,$allrowindex,7,'Day of Week',$headerstyle,11);
    writeCell($excelconn,1,$allrowindex,8,'TX Date',$headerstyle);
	writeCell($excelconn,1,$allrowindex,9,'Schedule Time',$headerstyle,11);
/*--------------------------------------------------------------------------------------------------------------*/
$sheetnbr = 0;
    $sheetnbr++;
	centerWorksheet($excelconn,$sheetnbr);	
	writeCell($excelconn,$sheetnbr,1,1,'Promo Play History Summary Report for ' . strftime('%B %d, %Y',$repstartdate) . ' through ' . strftime('%B %d, %Y',$rependdate) );
	$qryString = 
 	"select " .
			"t.programme_name as programme_name, " .
		"main_house_media(vc.version_copy_id) as house_number, " .
		"v.version_name as version_name, " .
		"tx_tranob.description as tx_type, " .
		"tstr(get_duration_sec(t.title_id,v.version_id)) as duration, " .
		"ts.channel_code, " .
		"to_char(to_date(ts.tx_date,'YYYYMMDD'),'Day') as day_of_week, " .
	 	"ts.tx_date, " .
	  "laptv.fix_real_time(ts.real_time) as schedule_time " .
	"from " .
		"title t, " .
		"version v, " .
		"version_copy vc, " .
		"tx_tranob, " .
		"tx_schedule ts " .
	"where " .
		"t.title_id = v.title_id and " .
		"v.version_id = vc.version_id and " .
		"v.version_id = ts.version_id and " .
      "t.tx_type = tx_tranob.tx_type and " .
		"ts.tx_date <= '" . strftime('%Y%m%d',$rependdate) . "' and ts.tx_date >= '" . strftime('%Y%m%d',$repstartdate) . "' and " .
      "main_house_media(vc.version_copy_id) like ('%" . $_POST["housenum"] . "%') " .
	"order by " .
	  "channel_code, tx_date, schedule_time " ;
echo '<br />' . $qryString;
echo "<script>top.frames.Main.showStatus('Processing ...');</script>";
$housenbrmatch = false;
$stmt = ociparse($connection,$qryString);
$startexecute = time();
     ociexecute($stmt);
$endexecute  = time();
echo '<br />ociexecute time=' . date("i:s",$endexecute-$startexecute);
     
     while (OCIFetchInto($stmt,$res,OCI_ASSOC+OCI_RETURN_NULLS)){
	$housenbrmatch = true;
	$colnbr = 1;
	$allrowindex++;
	writeCell($excelconn,1,$allrowindex,1,$res['PROGRAMME_NAME'],$detailstyle);
	writeCell($excelconn,1,$allrowindex,2,$res['HOUSE_NUMBER'],$detailcenterstyle);
	writeCell($excelconn,1,$allrowindex,3,$res['VERSION_NAME'],$detailstyle);
	writeCell($excelconn,1,$allrowindex,4,$res['TX_TYPE'],$detailstyle);
	writeCell($excelconn,1,$allrowindex,5,$res['DURATION'],$detailcenterstyle);
	writeCell($excelconn,1,$allrowindex,6,$res['CHANNEL_CODE'],$detailcenterstyle);
	writeCell($excelconn,1,$allrowindex,7,$res['DAY_OF_WEEK'],$detailstyle);
	writeCell($excelconn,1,$allrowindex,8,$res['TX_DATE'],$detailcenterstyle);
	writeCell($excelconn,1,$allrowindex,9,$res['SCHEDULE_TIME'],$detailcenterstyle);
    }
colAutoFit($excelconn,$sheetnbr,100);
rowAutoFit($excelconn,1);
echo "<script>top.frames.Main.hideStatus();</script>";
sheetClose($ReportFilePath, $ReportFileName, $wksheets, $excelconn);
return $ReportFileName;
}
// End of GenerateReport function
$errfound = false;
$errstring = '' ;
$basedirectory = 'D:/websites/ibms.laptv.com/Report_Output/';
$outputdirectory = $basedirectory . 'PromoPlayHistoryReports' . '/';
if (!VerifyDir($outputdirectory)){
  $errfound = true;
	$errstring .= 'Output directory ' . $outputdirectory . ' cannot be created\n';
}
$repstartdate = strtotime($_POST['StartDate']);
$rependdate = strtotime($_POST['EndDate']);
$housenbrmatch = ($_POST['housenum']);
$housenbrentered = false;
$channelselected = false;
    //check to see if a house number has been entered
$house_num = explode(",", $_POST['housenum']);
if (is_array($house_num)) {
   $housenum = "";
 foreach ($house_num as $num) {
          $housenum .= "'" . trim($num) . "',";
 }
 $housenum = substr($housenum, 0, -1);
}	
$SQL = "SELECT * FROM main_house_media(vc.version_copy_id) in (" . $housenum . ") ";
foreach ($_POST as $key=>$value){
echo '<br />key=' . $key . '...value=' . $value;
	if (substr($key,0, == 'housenum') {
		if ($value > "       ") {
			echo '<br />housenum value = on';
		$housenbrentered = true;
		}
	}
}
echo '<br />housenbrentered=' . $housenbrentered;
if (!$housenbrentered){
	$errfound = true;
	$errstring = 'A House Number must be entered\n';
  }
if (!$errfound){
	$reportlist = array();
	$reportindex = 0;
	$reportindex++;
     	$reportlist[$reportindex] = GenerateReport($repstartdate,$rependdate,$conn,$housenbrmatch);
  
	//----------------------------------------------------------------------
//now, go through and create list of files to 'return' to parent window
//----------------------------------------------------------------------
	if ($housenbrmatch) {
	 for ($i=1;$i<=$reportindex;$i++){
     		if (substr($reportlist[$i],5) == 'ERROR'){
         		$thisreport = $reportlist[$i];
       		}
       		else {
     			$reportlistedit[$i] = substr_replace($reportlist[$i], '', strlen($reportlist[$i])-10,-4) ;
                    $thisreport = '<a href="/Report_Output/PromoPlayHistoryReports/' . $reportlist[$i] . '" target="new">' . $reportlistedit[$i] . "</a>";
       		}
       		echo "<script> outfilelist[" . ($i-1) . "] = '" . $thisreport . "'; </script>";
     	}?>
	<script>top.frames.Main.reportComplete(outfilelist);</script><?
}
else {
	echo "<script>alert('No matching house numbers found');</script>";
  }
}
else {
	echo "<script>alert('$errstring');</script>";
}
?>
</BODY>
</HTML>

[dstaton]

OK, I think I have this working for the most part.  I changed the where line that contains the house numbers to:

 

"main_house_media(vc.version_copy_id) in ('" . $_POST['housenum'] . "') " .

 

It works perfectly for one house number.  What I don't know how to do is explain in the code to add the single quotes around each house number.  ex:  user enters CCB1261, MCP7938.  The above query should show it as 'CCB1261', 'MCP7938'.  It shows it as 'CCB1261, MCP7938'.  How do I get it to put quotes around each house number?

[trq]

How do I get it to put quotes around each house number?

 

<?php

$housenums = explode(', ',$_POST['housenum']);
echo "'".implode("','",$housenums)."'";

?>

[Psycho]

To expand on Thorpe's suggestion, I would first explode the variables just on the comma, then run through the array utilizing trim. This will take care of users that enter a comma-space and/or just a comma:

 

<?php

$housenums = explode(',',$_POST['housenum']);
foreach ($housenums as $key => $value) {
    $housenums[$key] = trim($value);
}
echo "'".implode("','",$housenums)."'";

?>

[dstaton]

Do I need to change anything in the where clause?  I still get the same results when I add in your suggestion.  Or does the code you suggested need to be in a different place then before the $housenbrentered = true where I have put it?  Does that make sense?

[dstaton]

I am still only getting results if I put in one house number.  Do I need to reference something differently in the where clause:

 

"main_house_media(vc.version_copy_id) in ('" . $_POST['housenum'] . "') " .

 

I also need to add an error message if no house numbers are entered.

 

When the script runs for multiple house numbers, here is what I get in my hidden echo screen:

*** Post Array=
Array
(
    [housenum] => CCB1261,MCP7938
    [startDate] => May 1, 2007 (Tue)
    [EndDate] => May 31, 2007 (Thu)
    [submit1] => Generate Report
)

***'CCB1261','MCP7938'
housenum value = on
housenbrentered=1sheet 1
sheet 1

select t.programme_name as programme_name, main_house_media(vc.version_copy_id) as house_number, v.version_name as version_name, tx_tranob.description as tx_type, tstr(get_duration_sec(t.title_id,v.version_id)) as duration, ts.channel_code, to_char(to_date(ts.tx_date,'YYYYMMDD'),'Day') as day_of_week, ts.tx_date, laptv.fix_real_time(ts.real_time) as schedule_time from title t, version v, version_copy vc, tx_tranob, tx_schedule ts where t.title_id = v.title_id and v.version_id = vc.version_id and v.version_id = ts.version_id and t.tx_type = tx_tranob.tx_type and ts.tx_date <= '20070531' and ts.tx_date >= '20070501' and main_house_media(vc.version_copy_id) in ('CCB1261,MCP7938') order by channel_code, tx_date, schedule_time 
ociexecute time=00:12
repfile=D:\websites\ibms.laptv.com\Report_Output\PromoPlayHistoryReports\PromoPlayHistory-20070501-20070531-47565.xls 

 

Here is the code that I tried.  I am sure it doesn't make sense.  I am trying to modify a report written by a former employee while self teaching myself at the same time.  It doesn't all make sense to me.  Any help you can provide is greatly appreciated.

	$housenbrentered = false;
$housenums = explode(',',$_POST['housenum']);
  foreach ($housenums as $key => $value) {
    $housenums[$key] = trim($value);
}
echo "'".implode("','",$housenums)."'";
foreach ($_POST as $key=>$value) {
  if (substr($key,0) == 'housenum') {
		if ($value > "       ") {
			echo '<br />housenum value = on';
		$housenbrentered = true;
		}
	}
}
echo '<br />housenbrentered=' . $housenbrentered;
if (!$housenbrentered){
	$errfound = true;
	$errstring = 'A House Number must be entered\n';
  }

[dstaton]

In case anyone reading this wants to know how I fixed this since I didn't get any more input, I was correct about the where clause.  So here is what I had to change the where clause to:

"main_house_media(vc.version_copy_id) in upper('" . implode("','",$housenums) . "') " .

 

Now this works perfectly.  Thank you to those who started me on the right path.  This was painful to figure out on my own since then, but I am sure it has been a good learning experience.