[SOLVED] Exclude column from php generated query

[lindm]

I have this query to set all columns of a table to nothing. Now I want to exclude some of the columns from the query. How to?

<?php

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

$result = mysql_query("SHOW COLUMNS FROM $table") or die("mysql error");


$numColumns = mysql_num_rows($result);

$x = 0;
while ($x < $numColumns)
{
    $colname = mysql_fetch_row($result);
    $col[$colname[0]] = $colname[0];
    $x++;

}

$querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col ) .'=\'\' WHERE `userName` = \''. $user2 .'\'';

echo $querycx;




?>

 

 

[unkwntech]

Your going to have to explicitly name each colum I believe.

UPDATE tableName SET col1='' col2='' col5='' WHERE username = 'username'

And just dont specify the colums you don't want to change.

[samshel]

<?php

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

$arrExclude = array('col1', 'col2');

$result = mysql_query("SHOW COLUMNS FROM $table") or die("mysql error");


$numColumns = mysql_num_rows($result);

$x = 0;
while ($x < $numColumns)
{
    $colname = mysql_fetch_row($result);
    if(!in_array($colname, $arrExclude) {
        $col[$colname[0]] = $colname[0];
    }	
    $x++;
}

$querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col ) .'=\'\' WHERE `userName` = \''. $user2 .'\'';

echo $querycx;

?>

 

$arrExclude can contain all columns that need not be updated.

 

[lindm]

Get an error

Parse error: syntax error, unexpected '{' in /Library/WebServer/Documents/clear.php on line 16

 

 

Current script

<?php

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

$arrExclude = array('id', 'userName', 'userPass', 'firma', 'orgnr', 'Scroll');

$result = mysql_query("SHOW COLUMNS FROM $table") or die("mysql error");


$numColumns = mysql_num_rows($result);

$x = 0;
while ($x < $numColumns)
{
    $colname = mysql_fetch_row($result);
    if(!in_array($colname, $arrExclude) {
        $col[$colname[0]] = $colname[0];
    }	
    $x++;
}

$querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col ) .'=\'\' WHERE `userName` = \''. $user2 .'\'';

mysql_query($querycx);

?>

 

 

[samshel]

Try this line: added ")" at the end of condition

 

if(!in_array($colname, $arrExclude)) {

 

sorry the code was not tested properly.

[lindm]

Hi samshel,

 

The code executes now but the excluded columns are still included in the query, strange. If you see any obvious solution please let me know. Here is the current code (echo query instead of mysql_query until the code is ready).

<?php

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

$arrExclude = array('id', 'userName', 'userPass', 'firma', 'orgnr', 'Scroll');

$result = mysql_query("SHOW COLUMNS FROM $table") or die("mysql error");


$numColumns = mysql_num_rows($result);

$x = 0;
while ($x < $numColumns)
{
    $colname = mysql_fetch_row($result);
    if(!in_array($colname, $arrExclude)) {
        $col[$colname[0]] = $colname[0];
    }	
    $x++;
}

$querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col ) .'=\'\'';

echo $querycx;



?>

 

 

[lindm]

Solved it now. Used array_diff to create a new array:

<?php

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

$arrExclude = array('userId', 'userName', 'userPass', 'firma', 'orgnr', 'Scroll');

$result = mysql_query("SHOW COLUMNS FROM $table") or die("mysql error");


$numColumns = mysql_num_rows($result);

$x = 0;
while ($x < $numColumns)
{
    $colname = mysql_fetch_row($result);
    if(!in_array($colname, $arrExclude)) {
        $col[$colname[0]] = $colname[0];
    }	
    $x++;
}


$col2= array_diff($col,$arrExclude); //removes arrExclude from the created array $col

$querycx = 'UPDATE '.$table.' SET '. implode( '=\'\', ', $col2 ) .'=\'\''; //new array is used in mysql query

mysql_query($querycx);



?>

 

Thanks for all help!