Loop through, compare, and update question

[diffenc]

Happy Monday!

 

I need a good way to trigger php/mysql to look at a table i have and do the following:

 

SELECT record, textdata WHERE checked IS NULL

  COMPARE textdata in that row to see if it exists in another row in the table WHERE checked IS NOT NULL

  if a match is found, WRITE the value of 'checked' (the one that's not null) into 'checked' replacing NULL

  if no match is found, leave as NULL and move on to the next value of textdata

 

 

| record  |  textdata  |  checked  |

|  1  |  pizza  |  1  |

|  2  |  pizza  |  1  |

|  3  |  tacos  |  0  |

|  4  |  candy  |  0  |

|  5  |  pizza  | NULL |

|  6  |  tacos  | NULL |

|  7  |  apple  | NULL |

 

after the script would show

 

| record  |  textdata  |  checked  |

|  1  |  pizza  |  1  |

|  2  |  pizza  |  1  |

|  3  |  tacos  |  0  |

|  4  |  candy  |  0  |

|  5  |  pizza  |  1  |

|  6  |  tacos  |  0  |

|  7  |  apple  | NULL |

 

Basically, I have a script that allows me to approve/disapprove (1/0 in the table) the text strings.  However, after I approve, I still have data populating the table, so I do not want to have to manually approve the same text string again, but rather have my code automatically find the value of 'textdata' in any new rows in the table that match previously checked rows, and write the same value to the 'checked' field.

 

Make sense?

[mem0ri]

Not sure if you completely made sense...but if you're just trying to filter out already approved texts vs. new texts coming in...and compare the new ones against the approved ones to make sure you never get duplicates...

 

...why don't you just add a boolean value column to your table stating "approved" or "new" (1 or 0)...and check all 0's against 1's....

[diffenc]

At the end of this, I'm actually counting up all of the 1's vs. 0's and some other data to report on some accuracy percentages. 

 

'checked' is a tinyint i'm using in some stuff leading up to this loop.  I have a page that displays all of the 'textdata' entries that are new, but i want it to check against existing data first so I can only display entries that are new (still NULL) for my person to approve/disapprove.

[diffenc]

anyone? 

[schilly]

select the row you want which is null

do a select on the db where the id = pizza and checked <> null

check to see if you have a row, if you do cycle through all null records where id = pizza and change the value to the value in your row from the query else do nothing

 

This is assuming all pizza entries that are not null are one constant value(All 1's or all 0's).

 

Hope that makes sense.