Help needed please re how to adjust MySql records using PHP

  • Mark709
  • Novice
  • Novice
  • Mark709
  • Posts: 22

Post 3+ Months Ago

There are 3 MySql TABLES that are NOT RELATED inside the DataBase - GARAGE
Names of Tables - APPLE and BANANA (not related) and CHERRY (not related)
.
Table APPLE has 4 fields -
{Data continues in this APPLE table month after month unless varied via Table BANANA}
1. ID (INT, 8) (PRIMARY INDEX),
2. MAKECODE (Var, 8) (Unique Index) {consider if 2+ fields are deleted}
3. DATE (YYYYMMDD), {being date of original entry in this table}
4. STYLE (Var, 4)
{Background-Table APPLE is updated from time to time using data from Table BANANA}
.
Table BANANA has 6 fields -
{Data is added during the month - and adjusts data in APPLE every few weeks}
1. ID (INT, 8) (PRIMARY INDEX),
2. ORIGINALCODE (Var, 8) (Unique Index)
3. REPLACEMENT (Var, 8) {often this field is NULL because no replacement}
4. DATE (YYYYMMDD), {being date of original entry in this table}
5. STYLE (Var, 4) {value is of no importance}
plus field
6. UPGRADED (YYYYMMDD) - has NO DATE - until used to upgrade data in file APPLE
then field 6 will hold system current date of the date it is used to upgrade APPLE
(mabybe field 6 NEEDS an INDEX - to help find which records need to be updated this time through
(Value can be NULL - or can insert a false date eg 11111111 ie 1111/11/11)
so an INDEX on field 6 UPGRADED (as value is not unique - as many fields will have a NULL value)
(once posted this field will hold the date of the UPGRADED APPLE record)
should an INDEX be UPGRADED and ID ?
{Background - Table BANANA is updated from time to time during the month
with data such as ORIGINALCODE, DATE, STYLE (always supplied)
and occassionally with extra detail such as REPLACEMENT
but the field UPGRADED is ALWAYS LEFT BLANK until processed as per required PHP code}
.
Table CHERRY has 4 fields -
{Table just holds totals for each updating attempt whenever that occurs}
ID (INT, 8) (PRIMARY INDEX),
DATE (YYYYMMDD) (Unique Index)
REPLACED (INT, 8)
DELETED (INT, 8)
NOTFOUNDA (INT, 8)
{Background-Each record is a stand alone record and no accumulative totals are ever needed.}
.
.
I will manually start/run the
PHP program code required to function as described below
================================================
1. (at certain time eg ever 3 or 4 weeks - whenever manually processed)
required to update Table APPLE using a PHP program
such that the code works as follows-
.
Find BANANA.UPGRADED where date is NULL or 11111111 ie 1111/11/11
{in other words this record has never upgraded Table APPLE}
.
if EOF (End of File - no more records to be upgraded) show message
"All records UPDATED
there were ___ REPLACED ____ DELETED and ___ NOTFOUNDA"
Save these totals in Table CHERRY
.
if not EOF
If BANANA.REPLACEMENT not null (that is DOES HAVE a value)
get BANANA.ORIGINALCODE
Find in Table APPLE WHERE BANANA.ORIGINALCODE EQ APPLE.MAKECODE
.
if no record found - INCREMENT NOTFOUNDA
. find next record
if record found
move BANANA.REPLACEMENT to APPLE.MAKECODE
move BANANA.DATE to APPLE.DATE
move BANANA.STYLE to APPLE.STYLE
SAVE APPLE
move system current date TO BANANA.UPGRADED (YYYYMMDD)
SAVE BANANA
INCREMENT COUNTER_REPLACED
.
OR ELSE (if BANANA.REPLACEMENT has NO VALUE)
get BANANA.ORIGINALCODE
Find in Table APPLE WHERE BANANA.ORIGINALCODE EQ APPLE.MAKECODE
DELETE RECORD in APPLE - save DataBase WITHOUT that APPLE.MAKECODE record
move system current date TO BANANA.UPGRADED (YYYYMMDD)
SAVE BANANA
INCREMENT COUNTER_DELETED
.
.
LOOP to FIND NEXT RECORD (in BANANA.UPGRADED) where date is NULL
.
.
Question to be considered
If in Table APPLE - 2 or more records are deleted
what happens to the UNIQUE INDEX
should this index not be unique
that is should the INDEX be MAKECODE AND ID
or if a record (inside a series of records) is deleted - does it not count?
.
Your advice on this aspect as well - appreciated.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Mark709
  • Novice
  • Novice
  • Mark709
  • Posts: 22

Post 3+ Months Ago

Was able to edit the smilies out - thanks

Post Information

  • Total Posts in this topic: 2 posts
  • Users browsing this forum: No registered users and 82 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.