ZenDB question

11 posts by 3 authors in: Forums > CMS Builder
Last Post: July 25   (RSS)

I am trying to write a function to update the MySQL database but it depends on whether the column exists or not. I have several use cases but the one I am trying to solve involves checking if an old column exists in a particular table, copy and transforming the data to a new column using a case statement and then dropping the old column. The schema already reflects the new column information and the old column is added from the database.

The idea is to manipulate the data and clean up the database and schema. 

I have a situation where a very old table had spaces in the column name and ZenDB can't add the table on a new install from the old schema. Actually, it is the _sc_countries table from the simple cart plugin. I don't use the plugin, but I did use the countries and provinces and data from it. The problem is with the ISO data fields.

Any help is greatly appreciated.

Jeff Shields

If I am understanding you correctly, Jeff, this is a "one and done" and you probably won't be reusing the function, correct? If it is a one and done, can't you simply remove the columns from the table in the ISO file so that the spacing lines up with the current db columns/spacing? 

It is for each project that I update, over 40 clients, so worth automating. Some of the projects were done 10 years ago and each is in a slightly different state. The scenario with the sc_countries table is only the first one I hit. I will extend this functionality as I encounter more issues. I might also use it going forward as my app expands.

Jeff Shields

I figured it out. Here is the function I used in case anyone needs to check if a column exists in the database. There may be something already in the core, but I could not find it.

function columnExists($table_name, $column_name)
{
    global $SETTINGS;
    $table = getTableNameWithPrefix($table_name);
    $db = $SETTINGS['mysql']['hostname'];
    // Prepare SQL query
    // The query selects the count of columns where the table name, schema name, and column name
    // match the provided parameters.
    $sql = "SELECT COUNT(*) as count
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = {$db}
            AND TABLE_NAME = {$table}
            AND COLUMN_NAME = {$column_name}";

    // Execute the query
    // The query is executed using the mysql_do function.
    $result = mysql_do($sql);

    // Check result
    // If the query is successful, the row count is checked. If the row count is greater than 0,
    // it means the column exists. Otherwise, it does not exist.
    if ($result) {
        $row = $result->fetch_assoc();
        return $row['count'] > 0;
    } else {
        // Handle query error
        // If there is an error executing the query, the function returns false.
        return false;
    }
}
Jeff Shields

Hi Jeff, 

Thanks for sharing and glad you got it figured out.  If it's working you might not want to change it, but here's how we might write that in ZenDB for reference: 

function columnExists(string $baseTable, string $columnName): bool {
    $rows = DB::query("SELECT COUNT(*) FROM information_schema.COLUMNS
                       WHERE TABLE_SCHEMA = DATABASE()
                         AND TABLE_NAME = :fullTable
                         AND COLUMN_NAME = :columnName", [
        ':fullTable'  => DB::getFullTable($baseTable),
        ':columnName' => $columnName,
    ]);

    $columnExists = (bool) $rows->first()->col(0)->raw(); // true if count > 0
    return $columnExists;
}

ZenDB has a very flexible syntax so there are a number of ways to write things, but this is one of them.

Let me know any questions.  Cheers! :-)

Dave Edis - Senior Developer
interactivetools.com

By kitsguru - July 15 - edited: July 15

Thanks Dave,

I can use this after I test it, and it gives me what I need to know.

Jeff Shields

Follow up.

I want to update my schema and database programmatically. Over time I have renamed columns and changed the datatype. What I want to do is pass the tablename and column and remove the column from the schema, and drop the column from the database after I have transformed and copied the data to the new column.

I have done this before using the MySQL functions etc, but would like to do it using ZenDB calls. Any help would be appreciated.

Jeff Shields

Hi Jeff, 

So you want to move a field from one table to another (and one CMSB schema to another).  Is there anything else that needs to change? 

Is the database the same?  And what is changing and what's staying the same?  

Dave Edis - Senior Developer
interactivetools.com

By kitsguru - July 21 - edited: July 21

I have renamed several columns and deleted others. When I update a project using a git pull from my master project to an existing project, the schemas are updated with new columns added, and the old columns hang around but are added back because they exist in the database but no longer in the schema.

I have written a function to detect if the old column exists (that was the first question in this thread) and transfer the data to the new column with some manipulation. Now I want to drop the old column from the schema and the database without needing to do it manually across all tables and projects. Basically I want to do an erase as if I was in the database editor but by looping through an array of tables and columns.

Jeff Shields