[SOLVED] Problems excluding fields from generated mysql query

[lindm]

I have a script that erases all fields of a mysql table according to the script below. Some fields need to be left untouched however and I need help with this in my script. Perhaps there is a better solution to my script?

 

 

<?php

include ('mysqlconnnection.php'); //include connection

$qColumnNames = mysql_query("SHOW COLUMNS FROM $table") or die("mysql error");
$numColumns = mysql_num_rows($qColumnNames);
$x = 0;
while ($x < $numColumns)
{
    $colname = mysql_fetch_row($qColumnNames);
    $col[$colname[0]] = $colname[0];
    $x++;
}
$querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col ) .'=\'\' WHERE `userName` = \''. $user2 .'\'';
mysql_query($querycx);


?>

 

 

[.josh]

I don't get it. Are you trying to drop an actual table column(s)? Your script doesn't do anything of the sort, except for to initially get the column names...I mean, it sounds like you want a list of your columns and you want to delete some (but not all) of them, but since your script isn't doing anything remotely close to that, I kind of have to wonder if maybe you need to explain something a little more?

[lindm]

I have around 100 columns in my table. I want to create a script to reset approx. 90 of these. Instead of executing a long manual query in php I want to generate this on the go (in case I add new columns later on). So 10 of these are not to be "touched".

 

Hope it gets a bit clearer.

[.josh]

<?php

// example array of columns not to touch
$keepers = array('id','name');

$sql = "show columns from tablename";
$result = mysql_query($sql);

while ($list = mysql_fetch_row($result)) {
   if (!in_array($list[0], $keepers)) {
      // mysql allows you to drop more than 1 column at a time
      // but in order to remain compatible with other sql's, we're
      // gonna do it 1 at a time
      $sql = "alter table tablename drop column {$list[0]}";
      $result2 = mysql_query($sql);
   } // end if not in array
} // end while $list
?>

[Barand]

CV,

I think the aim is just to clear the contents of the columns an not remove them altogether.

 

lindm,

I don't know what those 90 cols are, but I'd guess you have a serious db design problem and you may as well be using a flat file if you're using MySQL like that.

 

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[lindm]

I am making a budget service for my family members with different cost categories. Each column is the figure (cost) of a cost category. Is mysql not a good solution for this?

[Barand]

MySQL is excellent for it, but it's a RDBMS, not a spreadsheet.