array – joedag32

I was building a search for a site I was working on and needed the search to work on the words a user might type in separately not as one string. For instance, if they typed: “wiffle ball in Louisville”, it would search: wiffle, ball, and Louisville.

Building my SELECT statement was a little tricky at first.

First I had to take my string that’s being passed and store each word in an array. The below is a very simple example, I highly recommend that you clean up the string first!

$searchString = $_POST['who-search'];
$searchArray = explode(" ", $searchString);

This takes our string and uses the explode function to break up our string into an array whenever whitespace is found. Again, this should be refined a bit more in a production level product.

Now that we have an array of the words that were entered, a way to work this array into the SQL statement had to be figured out. Using a couple of loops to go through the array and create OR’s did the job.

$sql = "SELECT DISTINCT * FROM table WHERE ("; 
 
while (list($key,$val) = each($searchArray)) {
	$val = addslashes($val);
	if ($val<>" " and strlen($val) > 0) {
		$sql .= "column1 LIKE '%$val%' OR column2 LIKE '%$val%'  OR column3 LIKE '%$val%') OR";
	}
}
$sql = substr($sql,0,(strLen($sql)-3));//this will eat the last OR
$sql .= ") ORDER BY column1 DESC";

The while loop goes through our array the addslashes function is called to the current value. This escapes apostrophes from throwing off the SQL statement.

If the current value isn’t empty then it’s inserted in the SQL and it’s value compared to the columns you want to compare it against in your SQL. In my example above I’ve used LIKE against 3 columns.

Finally you have to remove the final OR from the SQL statement. A simple substr function is used to remove it from the end.

I got a little stumped when attempting to insert a new row in a Google Docs Spreadsheet using the Zend Framework, so I figured I’d share this in case someone else runs in the same problems. The documentation provided is really great, but I was getting caught in how the row data was supposed to be getting passed. It just needs a basic array with key/value pairs.

First you need to have the Zend Framework installed on your server. Google Code has a great article on getting started. Then you need to have a spreadsheet in Google Docs.

Now we’re going to write a simple PHP script that will input a new row into a basic spreadsheet. First you need to call the following Zend Framework include files for the script to work:

require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Http_Client');
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');

Next we’re going to set two variables with your Google Docs login info to authenticate our row creation. Enter you Google Account login:

$email = '<your_google_account_username>';
$pass = '<your_google_account_password>';

Now we authenticate ourselves with Google Docs and create a Zend_Gdata_Spreadsheets object.

$authService = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$httpClient = Zend_Gdata_ClientLogin::getHttpClient($email, $pass, $authService);
$spreadsheetService = new Zend_Gdata_Spreadsheets($httpClient);

Next we have to let Google Docs know which of our spreadsheets we want to write to. To do this we need the spreadsheet key. Obtaining the key is fairly simple, just grab it from the spreadsheet’s URL.

For instance:

https://spreadsheets.google.com/a/atlantic.edu/ccc?key=pqtY4KMBjwqlinsu9-f1PEg&hl=en

the key is:

pqtY4KMBjwqlinsu9-f1PEg

Not only do we need the spreadsheet key, but you also have to know what the worksheet id is too. Obtaining the worksheet id can be a little tricky, but if you only have one worksheet then it usually always is od6.

$spreadsheetKey = 'pqtY4KMBjwqlinsu9-f1PEg';
$worksheetId = 'od6';

Now we have to create the row data that we want to add to the above mentioned spreadsheet. You have to pass it an array with key/value pairs. The key is the column label in all lower case.

So to add a new row containing the string smurf to a column named stuff you build the following array:

$rowData = array('stuff' => 'smurf');

That’s it. If you were adding to more columns you would just pass more key/value pairs to the variable.

Now you just call the insertRow method and you’re all set.

$insertedListEntry = $spreadsheetService->insertRow($rowData, $spreadsheetKey, $worksheetId);

Pretty easy and very cool stuff.