CodeSnippets: php code

Convert Excel column key to array index (for CSVs) [php]

to php csv excel by thebuckst0p on Jun 08, 2009 1 Comments

Scenario: CSV file created in Excel, pulled into PHP script with fgetcsv() or equivalent CSV reader. Excel's columns are alphabetical, A-Z, AA-AZ, ... ZZ, AAA, etc. CSV readers (I'm working with php-csv-utils) generate two-dimensional arrays, rows first then columns (reverse of Excel), so A2 is [1][0] (0 being the first).
I needed to pull arbitrary cells out using the Excel keys, so I created the following algorithm. It works on the premise that the Excel columns use a base-26 number system:

/**
 * get cell in array by equivalent excel column/row keys
 * need to convert alphanumeric column to numeric index
 *	(A-Z, AA-AZ, BA-BZ, etc)
 * also: $data is parsed in reverse order, as $data[$row][$column]
 * @return cell in $data at $alphaCol/$numRow position
 */
function getCell($data, $alphaCol, $numRow) {
	$numCol = 0;
	$letters = str_split( strtoupper( strrev($alphaCol) ), 1);		
	
	foreach($letters as $level=>$letter) {		// ($level is numeric key)
		$letterNum = ord($letter) - ord('A') + 1;
		$numCol += $letterNum * pow(26, $level);
	}
	
	// compensate for indeces starting at 0 
	$numCol -= 1;
	$numRow -= 1; 
	
	if (is_array($data) and isset($data[$numRow][$alphaCol])) {
		return $data[$numRow][$numCol];
	}
	
	return null;	// not found
}

Weighted, Paginated MySQL Search Query for CakePHP

to php cakephp by tracyfloyd on May 19, 2009

CakePHP formatted, weighted MySQL query

$this->paginate = array(
  'fields' => "*, (MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)*100) + (MATCH (body) AGAINST ('$q' IN BOOLEAN MODE)*10) + MATCH (clients) AGAINST ('$q' IN BOOLEAN MODE) AS rating",
  'conditions' =>  "MATCH (title,body,clients) AGAINST ('$q' IN BOOLEAN MODE)",
  'order' => 'rating DESC',
  'limit' => 10
);
$results = $this->paginate('Article');

convert URLS to links in PHP

to php regex url convert by thunderrabbit on May 07, 2009

// In PHP, detect plaintext URLs, all the way from example.com to https://username:password@sub.domain.com:9999/dir/ect/ory/file?param=val&parm2=val2 and wrap them with href tags.

$pattern = "@\b(https?://)?(([0-9a-zA-Z_!~*'().&=+$%-]+:)?[0-9a-zA-Z_!~*'().&=+$%-]+\@)?(([0-9]{1,3}\.){3}[0-9]{1,3}|([0-9a-zA-Z_!~*'()-]+\.)*([0-9a-zA-Z][0-9a-zA-Z-]{0,61})?[0-9a-zA-Z]\.[a-zA-Z]{2,6})(:[0-9]{1,4})?((/[0-9a-zA-Z_!~*'().;?:\@&=+$,%#-]+)*/?)@";

$text_with_hyperlink = preg_replace($pattern, '<a href="\0">\0</a>', $text_with_raw_URLs);

Include file

to php include by olgamaria1979 on Apr 06, 2009

<?php
include('javascript.php');
?>

Contact Form

to php javascript form contact by olgamaria1979 on Apr 05, 2009

// JavaScript Validation - save as PHP

<!--
function validate_form ( ){
 valid = true;

 if ( document.contact_form.realname.value == "" ){
  alert ( "Please enter a valid Name." );
  valid = false;
 }
 if ( document.contact_form.realname.value == "*Name" ){
  alert ( "Please enter a valid Name." );
  valid = false;
 }

 if ( document.contact_form.business_name.value == "" ){
  alert ( "Please enter a valid Business name." );
  valid = false;
 }
 if ( document.contact_form.business_name.value == "*Business" ){
  alert ( "Please enter a valid Business name." );
  valid = false;
 }

 if ( document.contact_form.phone.value == "" ){
  alert ( "Please enter a valid Phone number." );
  valid = false;
 }
 if ( document.contact_form.phone.value == "*Phone" ){
  alert ( "Please enter a valid Phone number." );
  valid = false;
 }

 if ( document.contact_form.subject.value == "" ){
  alert ( "Please enter a valid Subject." );
  valid = false;
 }
 if ( document.contact_form.subject.value == "*Subject" ){
  alert ( "Please enter a valid Subject." );
  valid = false;
 }

 if ( document.contact_form.email.value == "" ){
  alert ( "Please enter a valid Email address." );
  valid = false;
 }
 if ( document.contact_form.email.value == "*Email" ){
  alert ( "Please enter a valid Email address." );
  valid = false;
 }

 if ( document.contact_form.message.value == "" ){
  alert ( "Please enter a valid Message." );
  valid = false;
 }
 if ( document.contact_form.message.value == "*Comments" ){
  alert ( "Please enter a valid Message." );
  valid = false;
 }

 return valid;
}
//-->

// include JS

<script type="text/javascript">
<?php
include('javascript.php');
?>
</script>

// Contact Form 1

<form method="post" action="contact2.php" name="contact_form" onsubmit="return validate_form ( );">
   <select class="form" name="department">
   <option value="" selected="selected">Please choose department</option>
   <option value="technical_services" >Technical Services</option>
   <option value="online_services" >Online Services</option>
   <option value="design_studio" >Design Studio</option>
   </select>
   <input class="form" name="realname" type="text" maxlength="255" value="*Name" />
   <input class="form" name="business_name" type="text" maxlength="255" value="*Business" />
   <input class="form" name="phone" maxlength="18" value="*Phone" type="text" />
   <input class="form" name="subject" type="text" maxlength="255" value="*Subject" />
   <input class="form" name="email" type="text" maxlength="255" value="*Email" />
   <textarea class="area" name="message" rows="5" style="font-family:Verdana; font-size:12px;">*Comments</textarea>
  <div align="right">
   <input class="button_text" type="submit" name="submit" value="Submit" />
  </div>
 </form>

// Contact Form 2

<?php
// Receiving variables
$department = $_POST['department'];
$realname = $_POST['realname'];
$business_name = $_POST['business_name'];
$phone = $_POST['phone'];
$subject = $_POST['subject'];
$email = $_POST['email'];
$message = $_POST['message'];

$pfw_message = "Name: $realname\n";
$pfw_message .= "Business name: $business_name\n";
$pfw_message .= "Phone: $phone\n\n";
$pfw_message .= "$message";

if ($department==''){
 $receiver="bbecker@alternativeaspect.com";
}
if ($department=='technical_services'){
 $receiver="bbecker@alternativeaspect.com";
}
if ($department=='online_services'){
 $receiver="bbecker@alternativeaspect.com";
}
if ($department=='design_studio'){
 $receiver="obecker@alternativeaspect.com";
}

mail($receiver, $subject, $pfw_message, "From:$email");
?>