deleting rows in a dynamic table

[vmavrou]

Hi,

I have this problem,in a php code the user can add dynamic fields at one table.

Sometimes from the php coding while doing inputs and deletions

happens that a row has all fields with the zero value.

The table does not have a primary key field and because of the whole design i cannot added one now because it will cause malfuntion.

Is there any way to have an sql query that checks if all the fields are == "0" and then delete that row?

Because we can check if all the fields , where we will get them dynamic, are == 0 but is there any way to delete that row when we don't have an id or general a primary key?

 

I include a pic of the table so you can understand the structure and the rows that i want to delete with a query automatic every time a user visit the page if such rows exist.

 

 

Thank you in advance

 

[attachment deleted by admin]

[fenway]

Wait a minute, dynamic "fields"?

[vmavrou]

supposed to say...you can add dynamic , new fields at the table

 

The point is tha the table isn't static so we don't know the number and the name of the fields before. We have to retreive them every time.

[FezEvils]

just change your sql statement

 

"delete from table where admin=0 and public=0 and dokimi =0";

 

change it to "or" then it will delete a row if one of them contains value 0

[vmavrou]

but the problem remains because we don't know that it will be these three fields,the user can deletes and add fields as he wish. So we can't have a static statement like that. And also the need is to delete rows that all fields are zero.

[fenway]

but the problem remains because we don't know that it will be these three fields,the user can deletes and add fields as he wish. So we can't have a static statement like that. And also the need is to delete rows that all fields are zero.

You should always know that... I suppose you could use the I_S tables to figure it out in real-time, but you should know that your design has some serious flaws.  Why did you decide to use dynamic FIELDS?

[vmavrou]

well this table is for the groups of users on a log in system with permissions.

The table is dynamic because because the admin can add one group or remove one.

If it seems a strange way it works really well,except when inserting and removing some times cause a row to has all it's values zero. So after a long time it will become to get this table bigger and bigger.

So every time the database opens it would be usefull to check for such rows and remove them.

The whole design is very complicated , because when i had finished my project my supervisor decided some changes that affect the whole project (university project) so much. So i had to find a way to re-construct the site and because i''m very new to php and mysql, after a long search i saw that this implementation works fine for me.

Because i searched at the mysql manual for the command I_S tables and i couldn't find it perhaps can you give a lit more info?

 

Thanks for your time.

 

[fenway]

information_schema tables.

[FezEvils]

other solution is :

1-create 1 more table to store the name of dynamic field that u have entered, so from that u know what field to be delete ..

to delete field use sql alter database

 

[vmavrou]

i have a table like that for an other usage but i don't want to delete a field, i want to delete a whole row when all the fields are zero.

 

if you check out the picture attached you will see these lines at the example

 

for example i want to do something like that :

 

$result = mysql_query("SELECT * FROM groupspu");

$fields_num = mysql_num_fields($result);

  $flag = true; 

for($i=0; $i<$fields_num; $i++){   

$field = mysql_fetch_field($result); 

                    if($field->name != "0") {

                          $flag = false; }

if($flag==true) {

$query = "DELETE FROM groupspu WHERE ??????? " }

}

$field->name ==0 for all fields ..but how to make a query like that? 

 

of course this is hust an example..needs more commands to run for the whole table.

a numrows and more..but i just put up little code to figure out exactly what i'm trying to do.

           

[fenway]

I already gave you the answer... you need to get the names of the columns first.

[vmavrou]

Sorry to bother you again, but i couldn't understand how to use the information_sceme later on the problem but getting the columns name isn't a problem .

 

$array_field = array ();

 

$result = mysql_query("SELECT * FROM groupspu");

$fields_num = mysql_num_fields($result);

for($i=0; $i<$fields_num; $i++){   

  $field = mysql_fetch_field($result); 

      $array_field[] = $field->name;           

 

}

 

$array_length = count($array_field);

 

with this code i can grab them and put them in an array.

 

But the problem starts next..i don't know what to do next..do you mean to use also information_sceme and for the later stages of solution?

[fenway]

The code you have couldn't possibly be more inefficient -- you're getting back ALL rows just to query the field names!  At the very least, use SHOW COLUMNS and parse the output, if you don't/can't use the I_S tables.  Once you have this output, it should be trivial to push onto an array and build the query for your very strange table.