ZenDB question

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.

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;
    }
}

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! :-)

Thanks Dave,

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

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.

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?  

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.