Import CSV with PHP to update MySQL data

I have written a very simple script to perform a useful function for me. I figured I’d share it so that others can make use of it.

If you have a CSV file with the first row containing headers and the subsequent rows containing data that you wish to update along with an ID column, then this will work for you without any issues. If your primary key is different or you wish to match other criteria you may need to adjust the script a bit. This does not facilitate upload but only uses a csv file residing on the same directory as the script.

DirectoryPress Broken CSV export

If you use DirectoryPress 7.0.9 you may have noticed that the CSV export function isn’t working properly. This is going to be a quick “edit this here” fix.

Edit the admin-save.php file in your /wp-content/themes/directorypress/admin directory.

Find around line 1105 or so. It should have:

$dat = array_merge($dat, $FF);

Change that to:

$dat = $dat + $FF;

Save it and upload it to your server. It should now be able to export properly.

Installing memcache on PHP with CentOS 6

I was following the instructions here to install memcache on a CentOS 6 server that I’m currently configuring. I was able to install the base memcache rpm, but had trouble when installing the PECL extension for PHP.

The first error I got had something to do with not being able to phpize the script.

So, I ran the following command to install the php development package:

yum install php-devel

Then I re-ran the PECL memcached install and came across this message:

checking for the location of zlib… configure: error: memcache support requires ZLIB. Use –with-zlib-dir=<DIR>

And finally found a solution for this by running this command to install the zlib-devel package:

yum install zlib-devel

Now you should be able to successfully install and add the memcache extension to your php.ini. Follow the instructions linked above for more information.

Parse error: syntax error, unexpected $end in /app/views/layouts/default.ctp – CakePHP

I just encountered an unexpected error while creating a portable development server on a USB stick using the latest version of XAMPP portable. This particular application runs on the CakePHP framework and was copied directly from another functioning Windows based machine.

After scratching my head a few moments I realized that the default configuration for XAMPP’s php.ini may be different.

The fix is easy. Go into your php.ini for your php installation and change the following from ‘OFF’ to ‘On’:

short_open_tag = on

Then simply go and restart your apache to apply the changes.

Missing orders in Virtue Mart admin and how to fix it

I just realized that none of the recent orders in one of the Virtue Mart installations I manage were showing up. It looks like the most recent one was….three months ago. But when I search for a more recent order I get the correct results. So, the orders are in the database but they weren’t being listed on the main order list. This will go over what I’ve found in this install of Virtue Mart 1.1.2. Other versions may not suffer from this phenomenon.

Weird.

After a little digging I find that the order.order_list.php file in the /administrator/components/com_virtuemart/html folder has what I need to start my investigation. Starting at line 26 there is a bundle of SQL that joins the jos_vm_orders table to the jos_vm_order_user_info table. Well, since this is an administrator-only issue, I just echoed the $list variable at line 46, reloaded the order list in admin.

I then copied the query that was printed at the top of the page, replaced the #__{vm} with my installation’s table prefix of jos_vm and pasted the whole query in my MySQL tool of choice to query my database. Well, turns out the query isn’t returning an up to date result set. I know that the data for orders is being saved to jos_vm_orders because I can look at that table and everything is up to date. So next I move on to the table the query joins it with: jos_vm_order_user_info.

Aha! It looks like jos_vm_order_user_info wasn’t up to date at all. Weird.

According to this forum post it’s because there were custom fields added to the jos_vm_user_info database table and the jos_vm_order_user_info table no longer matched the columns. So the solution was to see what columns didn’t match up and alter the jos_vm_order_user_info table to have those missing columns. Turns out I added a single custom field and it didn’t get added to the order user info table…so after adding the column to the order user info table everything worked perfectly.

So, if you added custom fields to your user registration you should make sure your jos_vm_user_info table matches up those custom fields with the columns in jos_vm_order_user_info.

Ordering VirtueMart Child Items in Dropdowns

Ok, so a ton of people keep asking me about how to order child products in the dropdown list. I don’t develop with VirtueMart much anymore so the suggestions I give here are mere suggestions and have not been tested or put through any sort of trial. I’m just looking at the code and guessing at what to change.

This exploratory exercise uses VirtueMart 1.1.6 stable.

If it works for you let me know. If it kind of works for you but you fix something and it works better, let me know and I’ll update here.

  1. So let’s take a look at ps_product_attribute.php
  2. Somewhere in there is a bunch of functions that start with ‘list_attribute’. Let’s look at those.
  3. function list_attribute seems to be the main one that calls the other variations. It looks like by default it calls up the drop down menu for attributes. (line 221)
  4. Since that one is the default, let’s see if making modifications to any of its queries will give us the results we want. The function starts at line 241.
  5. At line 273ish you should see a SQL query: $q = “SELECT product_id,product_name FROM #__{vm}_product WHERE product_parent_id=$product_id AND product_parent_id<>0 AND product_publish=’Y'” ;
  6. This is the first query you’re going to play with. You can tack on any sort of ORDER BY directives that you wish:
    ex $q = “SELECT product_id,product_name FROM #__{vm}_product WHERE product_parent_id=$product_id AND product_parent_id<>0 AND product_publish=’Y’ ORDER BY product_name” ;
    I just ordered the SQL results by product name. You can put in whichever column or set of columns you wish to order by. It’s up to you.
  7. Now take this same principle and try it throughout the various ‘list_attribute’ functions and see if you can achieve the sorting results that you desire.

Specify Stylesheets for Different Media Types with $html helper

I’m expanding my CakePHP experience and now I’ve delved into displaying the application on multiple devices and of course, print.

Print stylesheets can be tricky to design, but it took me a while to figure out how to have cake automatically add them to the template using the $html helper.

&lt;?php 
	//you specify the file name first, then the media types at the end.
	echo $html-&gt;css('simple', null, array('media'=&gt;'print'));
	//
	
	echo $html-&gt;css('regular', null, array('media'=&gt;'screen')); 
	echo $html-&gt;css('simple', null, array('media'=&gt;'print')); 
?&gt;

This will output something like this:

&lt;link rel=&quot;stylesheet&quot; type=&quot;text/css&quot; href=&quot;/css/simple.css&quot; media=&quot;print&quot; /&gt;

CakePHP: Join results from multiple tables

Have you ever wanted to expand on your pre-set model queries with some table joins or other fancy footwork? You can join your model query to another table or tables like so:

				
$market = $this-&gt;Market-&gt;find('list',
	array(
		'joins' =&gt; array(
		array(
			'table' =&gt; 'companies_markets',
			'alias' =&gt; 'CompaniesMarkets',
			'type' =&gt; 'left',
			'foreignKey' =&gt; 'CompaniesMarkets.market_id',
			'conditions'=&gt; array('CompaniesMarkets.market_id = Market.market_id')
		)
	),
	'conditions' =&gt; array(
			'CompaniesMarkets.company_id' =&gt; $company
			),
			'fields'=&gt;array('Market.market_id','Market.market_name')
	)					
);

What’s we’re doing here is specifying the ‘joins’ option along with a slew of other pieces to designate what to join, how, and by which field. The ‘conditions’ simply sets which fields I want to check for a certain value and which fields I want to use for this list.

How To: CakePHP form input without labels

One of the great things about CakePHP is how easy it is to get very common tasks accomplished in a short amount of time. Form building is no exception, but there are many options to specify – and at times those options are not immediately apparent.

The standard input code syntax appears like this:

input(string $fieldName, array $options = array())

To have your form inputs not show labels, you can specify ‘label’=>false in the input options.

For example:

<?php
echo $form->create(‘User’, array(‘action’ => ‘login’, ‘class’=>’header-login’));
echo $form->input(‘username’, array(‘label’ => false));
echo $form->input(‘password’, array(‘label’ => false));
echo $form->end(‘Login’);
?>

This will show your form inputs without the label elements.

Dynamically Sync Your Files With Amazon S3

I’ve been getting used to using the cloud over the past week or so and I’ve got to say it’s much easier to manage than I had imagined. I know there are tons of great desktop clients to manage your S3 buckets and files, but I wanted something to do all of the management for me – to make things simple.

I’ve put together a script using Undesigned’s awesome PHP S3 class that will scan a directory, compare all of the files in the directory to those in a given directory on your bucket and then upload missing files or replaced unmatched files.

You could set this PHP script up to do a lot more than it does currently, as it was set up to be a quick and dirty solution. Here’s the basis of it. You can download the full script and classes below.

//check to see if the file exists and can return file info
if (($info = $s3-&gt;getObjectInfo($bucket, $uri)) !== false) {
//if the local hash doesnt match the remote hash, upload and replace the file
if($info['hash'] &lt;&gt; $local_md5){
print_r('Can\'t match file: '.$the_file.' -- Now uploading');
echo "Putting $the_file . . . at $uri ";
//this will upload files with private permissions - you need to set the permissions as
//needed by changing S3::PRIVATE to whichever level you need.
if($s3-&gt;putObject(S3::inputFile($object), $bucket, $uri,  S3::ACL_PRIVATE,
array(),
array( // Custom $requestHeaders
"Cache-Control" =&gt; "max-age=315360000",
"Expires" =&gt; gmdate("D, d M Y H:i:s T", strtotime("+5 years"))
)))
echo "OK\n";
else
echo "ERROR!\n";
}
else{
echo "The hash values for $the_file are equal ";
}
}else{
//file doesn't exist in your bucket, so upload it
print_r('Can\'t find that file: '.$the_file.' -- Now uploading');
echo "Putting $the_file . . . at $uri ";
if($s3-&gt;putObject(S3::inputFile($object), $bucket, $uri,  S3::ACL_PUBLIC_READ,
array(),
array( // Custom $requestHeaders
"Cache-Control" =&gt; "max-age=315360000",
"Expires" =&gt; gmdate("D, d M Y H:i:s T", strtotime("+5 years"))
)))
echo "OK\n";
else
echo "ERROR!\n";
}
Get the Class Get the sync script