dracoblue.net

In my blog I have the following structure (short version):

entry = id,title
category = id,name
entry_in_category = category_id, entry_id

I want to query for the following:

id | title           | categories
---------------------------------
1  | My Entry        | php,mysql
2  | Second ...      | mysql,java

So if you want to get all entries with the corresponding categories you may do a refetch on each of the entries id in the entryincategory table. But if (for example for tags) you really just want to have the name of the categories and nothing more, here comes a pretty gentle way to solve that issue.

SELECT 
    e.id, e.title,
    (
        SELECT <a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat">GROUP_CONCAT</a>(c.name)
        FROM entry_in_category e_i_c 
            LEFT JOIN category c ON c.id=e_i_c.category_id
        WHERE e_i_c.entry_id = e.id
    ) categories
FROM entry e

This solution uses the

GROUP_CONCAT function, which was intruduced in mysql 4.1 and concats all results name-fields (oviously not grouped by anything) into a single string.

By default mysql concats the entries by using a ',' but you may use a different seperator by replacing the group-concat-part with the following:

GROUP_CONCAT(c.name SEPARATOR '-')

Thanks to Christian for bringing up a similiar issue, hope that solution helps you to get an idea on how to deal with similiar cases.

If you want to override the propel configurations for charset (encoding) for the connection you usally set the settings['charset'] value to for example utf8.

But if you are using agavi you want to configure propel properly by using agavi's databases.xml.

So after trying a while, here is how you can set the charset (magic is

bold):

<?xml version="1.0" encoding="UTF-8"?>
&lt;ae:configurations
  xmlns:ae="http://agavi.org/agavi/config/global/envelope/1.0"
  xmlns="http://agavi.org/agavi/config/parts/databases/1.0">
  &lt;ae:configuration>
    &lt;databases default="propel">
      &lt;database name="propel"
        class="AgaviPropelDatabase">
        &lt;ae:parameter name="config">%core.app_dir%/config/propelproject-conf.php&lt;/ae:parameter>
        &lt;ae:parameter name="overrides">
          &lt;ae:parameter name="connection">
            &lt;ae:parameter name="dsn">mysql:dbname=icanhazagavirelease;host=127.0.0.1&lt;/ae:parameter>
            &lt;ae:parameter name="user">w00t&lt;/ae:parameter>
            &lt;ae:parameter name="password">not_default_pass&lt;/ae:parameter>
            [strong]&lt;ae:parameter name="settings">
              &lt;ae:parameter name="charset">
                &lt;ae:parameter name="value">utf8&lt;/ae:parameter>
              &lt;/ae:parameter>
            &lt;/ae:parameter>[/strong]
          &lt;/ae:parameter>
        &lt;/ae:parameter>      
      &lt;/database>
    &lt;/databases>
  &lt;/ae:configuration>
&lt;/ae:configurations>

The AgaviFromPopulationFilter is handy to re-fill a form after validation failed and the user needs to check the values again (without refilling them by hand again).

But the FormPopulationFilter (FPF) is also capable of pre-filling forms.

Just get into your view and add the following:

if ([strong]'read'[/strong] == $this->getContext()->getRequest()->getMethod()) {
        $this->getContext()->getRequest()->setAttribute('[strong]populate[/strong]',
            new AgaviParameterHolder(
                array(
                    'name' => 'Test Entry',
                    [strong]'description'[/strong] => 'This is a short description'
                )
            ), 'org.agavi.filter.FormPopulationFilter'
        );
    }

and your Formfields 'name' and 'description' will be prefilled.

The template looks tidy now:

<input type="text" name="name" />
<textarea name="[strong]description[/strong]"></textarea>

Thanks to

veikko and saracen.

Today (while migrating the userdata for dracoblue.net) I was facing the issue, that I needed to transform the unix timestmaps (used by SimpleMachinesForum) into date-objects.

After trying different things, I noticed that the easiest way is the java-function to create a new Date-Object.

Converting UnixTimestamp (int/long) to Date:

new java.sql.Timestamp(new Long(row1.registerDate)*1000)

Explanation: Since the Timestamp expects milliseconds since 01/01/1970, but I have seconds, I need to multiply be 1000. But since the initial Value may be a int, which can't be that big we need to convert it to Long first.

Converting Date String (e.g. dd.MM.yyyy) to UnixTimestamp:

(TalendDate.parseDate("dd.MM.yyyy",row1.registerDate)).getTime()

Converting Date Object to UnixTimestamp:

row1.registerDate.getTime()

If you want to run latest stable 2.1 version of PDT (the eclipse extension for PHP) you'll find a all-in-one version at the

eclipse pdt download page.

Just scroll down a bit, until you find 2.1.0 Stable Builds and download All-In-One (around 167MB).

Once downloaded the .zip file, copy the contents of that file to a directory.

Launch the eclipse(.exe) in that folder.

Eclipse will launch and ask you where to put the workspace to. The workspace will be used as

cache and will store configuration files for each of your projects. You are still able to locate the projects outside of your workspace!