SP Property 4.1.4 – Unique ID and Database Structure Issues

Hello,

I've fixed both issues. I've shared an Akeeba backup zip in the hidden content section. Please restore it on a staging site first and check that everything works before you do anything on your live site.

How to restore the Akeeba ZIP backup on a staging site

The backup is a standard .zip that contains the site files and an installation folder, so you don't need any extra tools.

1. Download the backup zip from the hidden content section.
2. On your staging server, create an empty folder or subdomain, and an empty MySQL database with its own user.
3. Upload the zip to the staging web root and extract it. In cPanel, use File Manager → Extract. You can also extract it on your computer and upload the files by FTP.
4. Open https://your-staging-site/installation/index.php in your browser. This starts the Akeeba restoration script.
5. Enter the staging database host, name, username and password, then continue through the steps. Leave the table prefix as it is.
6. When it finishes, click Clean up, or delete the installation folder manually.
7. Log in to /administrator and go to System → Maintenance → Database. SP Property should show no problems.
8. Add a test property with a Vietnamese title and leave Unique ID empty. It should save with an ID like #00001.

When it all looks fine, repeat the same steps on your live site. Back up the live site first.

Alternative: apply the fix manually

Here is what I changed. All paths are relative to your Joomla root.

Fix 1: Unique ID error with Vietnamese titles

The ID was built from substr() of the title. That cuts multi-byte characters in half and produces invalid UTF-8.

In administrator/components/com_spproperty/helpers/spproperty.php, replace the generateID() function with:

public static function generateID($title = '', $date = null)
{
    $db    = Factory::getDbo();
    $query = $db->getQuery(true)
        ->select('MAX(' . $db->qn('id') . ')')
        ->from($db->qn('#__spproperty_properties'));
    $db->setQuery($query);
    $next = (int) $db->loadResult() + 1;

    do {
        $id    = '#' . str_pad((string) $next++, 5, '0', STR_PAD_LEFT);
        $query = $db->getQuery(true)
            ->select('COUNT(*)')
            ->from($db->qn('#__spproperty_properties'))
            ->where($db->qn('property_id') . ' = ' . $db->q($id));
        $db->setQuery($query);
    } while ((int) $db->loadResult() > 0);

    return $id;
}

IDs are now sequential (#00001, #00002, …) and no longer depend on the title.

Fix 2: Database structure errors

The checker compares your table with the extension's SQL files. The files described alias as VARCHAR(55) and price as float(8,2), which doesn't match a real database. float(8,2) also can't store large values such as VND prices. Edit these three files:

1. administrator/components/com_spproperty/sql/updates/mysql/2.0-2018-07-30.sql

   • Find this line (line 11):

     ALTER TABLE `#__spproperty_properties` CHANGE `slug` `alias` VARCHAR(55) NOT NULL DEFAULT '';

   • Change it to:

     ALTER TABLE `#__spproperty_properties` CHANGE `slug` `alias` VARCHAR(255) NOT NULL;

   • Change only this #__spproperty_properties line. The similar lines for agents, categories and propertyfeatures stay as they are.

2. administrator/components/com_spproperty/sql/updates/mysql/4.0.0.sql

   • Find this line (line 2):

     ALTER TABLE `#__spproperty_properties` MODIFY `price` float(8,2) NOT NULL DEFAULT '0.00';

   • Change it to:

     ALTER TABLE `#__spproperty_properties` MODIFY `price` decimal(18,2) NOT NULL DEFAULT '0.00';

3. administrator/components/com_spproperty/sql/install/mysql/install.sql

   • In the CREATE TABLE IF NOT EXISTS#__spproperty_properties`` block, change:

     `alias` varchar(55) NOT NULL DEFAULT '',

     to:

     `alias` varchar(255) NOT NULL,

   • In the same block, change:

     `price` float(8,2) NOT NULL DEFAULT '0.00',

     to:

     `price` decimal(18,2) NOT NULL DEFAULT '0.00',

If your database still has alias VARCHAR(55) or price float(8,2), run this in phpMyAdmin first. Replace PREFIX_ with your table prefix:

ALTER TABLE `PREFIX_spproperty_properties` MODIFY `alias` VARCHAR(255) NOT NULL;
ALTER TABLE `PREFIX_spproperty_properties` MODIFY `price` DECIMAL(18,2) NOT NULL DEFAULT '0.00';

Then open System → Maintenance → Database and the errors should be gone.

Updating or reinstalling SP Property from the official package will overwrite these edits. In that case, reapply them or wait for an official release that includes the fix.

Best regards