PHP MYSQL: Find and update columns beginning with...

[lindm]

Trying to build a tiny but effective script that removes database data for all columns beginning with cx.

 

It is relating to storing checkbox status. Since an unchecked checkbox doesn't send data I need to "reset" all stored checkbox values before sending new checkbox data.

 

Any suggestions?

[rarebit]

Do you need to do this in sql...

$sql = "UPDATE t SET x = y WHERE x LIKE '%cx' ";

 

or php...

$x = 0;
if(isset($_POST['x']))
{
   $x = 1;
}

[lindm]

Tried the query but get unknown column error.

 

I have say 20 columns all with the name beginning with cx:

 

cxTEST

cxTEST2

cxTEST3

 

etc

 

Is there a simple query to empty all columns? I tried

 

UPDATE table SET x ='' WHERE x LIKE 'cx%'

 

but no go.

 

 

[rarebit]

if you used that statement as is then yeah, you will (more than probably) get an 'unknown column error'... change it to whatever is relevant to your table...

 

but your right the % should be on the right...

[lindm]

But isn't that impossible. I want x to refer to all columns that begin with cx. Instead of writing

 

SET cx1 = '', cx2 ='' etc..

 

I want to write

 

SET all columns that begin with cx to ''. Can't see how your query can work...

[fenway]

You'll need the information_schema tables in MySQL 5 to do that...