title: How do I rename and delete mysql column names without deleting rows or records?
body: <p>No matter what I try, after the columns get renamed or deleted, all the records of table get deleted. What am I doing wrong?</p>
  
  <p>Here are rename column name attempts (each attempt is doing 2 columns at the same time):</p>
  
  <pre><code>//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.";
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>ALTER TABLE products_jobs DROP COLUMN ".$old_name.", DROP COLUMN ".$old_price.";
  </code></pre>
  
  <p>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.</p>
  
  <p>UPDATE</p>
  
  <p>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.</p>
  
  <pre><code>$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 &lt; 4) {
      $sql = mysql_query("DELETE FROM products_jobs");
  }
  </code></pre>