如何在不删除行或记录的情况下重命名和删除mysql列名？

No matter what I try, after the columns get renamed or deleted, all the records of table get deleted. What am I doing wrong?

Here are rename column name attempts (each attempt is doing 2 columns at the same time):

//method 1 (doesn't work)
ALTER TABLE products_jobs CHANGE COLUMN ".$old_name." ".$new_name." VARCHAR(255) NOT NULL, CHANGE COLUMN ".$old_price." ".$new_price." VARCHAR(255) NOT NULL;

//method 2 (doesn't work)
ALTER TABLE products_jobs MODIFY COLUMN ".$old_name." ".$new_name." VARCHAR(255) NOT NULL, MODIFY COLUMN ".$old_price." ".$new_price." VARCHAR(255) NOT NULL;

//method 3 (doesn't work)
ALTER TABLE products_jobs ADD ".$new_name." VARCHAR(255) NOT NULL AFTER qty;
UPDATE products_jobs SET ".$new_name." = CASE WHEN ".$old_name." THEN 1 ELSE 0 END;
ALTER TABLE products_jobs DROP COLUMN ".$old_name.";
ALTER TABLE products_jobs ADD ".$new_price." VARCHAR(255) NOT NULL AFTER qty;
UPDATE products_jobs SET ".$new_price." = CASE WHEN ".$old_price." THEN 1 ELSE 0 END;
ALTER TABLE products_jobs DROP COLUMN ".$old_price.";

//method 4 (doesn't work)
ALTER TABLE products_jobs ADD ".$new_price." VARCHAR(255) NOT NULL AFTER qty, ADD ".$new_name." VARCHAR(255) NOT NULL AFTER qty;
UPDATE products_jobs set ".$new_price." = ".$old_price.", ".$new_name." = ".$old_name.";
ALTER TABLE products_jobs DROP COLUMN ".$old_price.", DROP COLUMN ".$old_name.";

This is the delete method I tried. Not sure how else this can be written, so I didn't try more that this one way. And again, it's doing 2 columns at the same time.

ALTER TABLE products_jobs DROP COLUMN ".$old_name.", DROP COLUMN ".$old_price.";

So all these things work in renaming and deleting columns, but the problem is all records of table get deleted, which is not good. Need all my rows/records to stay intact. Not sure if this matters, but the table type/engine is innodb, not myisam.

UPDATE

I figured out that I had extra code in there, and it was referencing wrong database. The database variable was not correct. Once I corrected it, my problem above went away. Here is that code that was messing with me.

$sql = mysql_query("SELECT COUNT(*) totalColumns FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '".$database."' AND table_name = 'products_jobs'");
$row = mysql_fetch_assoc($sql);
$totalcolumns = $row['totalColumns'];
if ($totalcolumns < 4) {
    $sql = mysql_query("DELETE FROM products_jobs");
}

Per the MySQL manual:

ALTER TABLE t1 CHANGE a b INTEGER

"You can rename a column using a CHANGE old_col_name new_col_name column_definition clause. To do so, specify the old and new column names and the definition that the column currently has."

Source: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

So, in your case

ALTER TABLE products_jobs CHANGE ".$old_name." ".$new_name . " VARCHAR(255)"

Just beware that your SQL is vulnerable to SQL injection.

I think what may be causing your previous attempts to fail is the 'NOT NULL' descriptor. I'd refer you to this answer to a similar question: https://stackoverflow.com/a/5706625/6622781