2

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");
}
leoarce
  • 567
  • 2
  • 8
  • 33
  • What are the values of those php variables? None of those statements should be deleting ANY rows, unless you're getting sql injected. – Uueerdo Dec 07 '16 at 21:14
  • They could be many different things, but one example is Variable_List_name for the name one, and Variable_List_price for the price one. – leoarce Dec 07 '16 at 21:15
  • Maybe FOREIGN KEY – Artem Ilchenko Dec 07 '16 at 21:31
  • figured it out, and it had nothing to do with how the alter statements are written. it was extra code i had that was wrong. will update post to explain. – leoarce Dec 07 '16 at 21:38

2 Answers2

2

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.

Rápli András
  • 3,869
  • 1
  • 35
  • 55
Karl Buys
  • 439
  • 5
  • 12
2

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

Community
  • 1
  • 1
D Lowther
  • 1,609
  • 1
  • 9
  • 16