PHP code open, select, update, save MySql records

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

Post 3+ Months Ago

Can someone please help me - I believe I know how to install but not code
so I could be described as a REAL NOVICE.

I have a MySql Database with about 100,000 records
Need to have PHP code that will OPEN the DataBase
and go thru each record in ID (PRIMARY INDEX) order to EOF (End of File).

As EACH record is FOUND
CREATE/GENERATE a RANDOM number (between 1 and 200,000) (does NOT have to be a unique number)
and WRITE that RANDOM number in a field name RANDORDER
then to SAVE that record
then INCREMENT to find the next record or End of File
CLOSE FILE and exit

Ideally a MESSAGE to inform the User of
SELECT TABLE.FILE
START or Exit
WORKING
COMPLETED TASK would be a "nice to have".

PHP version is 5.0
MySql version is 5.0

Many thanks for your time and expertise
may wonderful things happen to you for your kindness.
Mark (Australian currently in Hong Kong)
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

Connecting to the database:
PHP Code: [ Select ]
<?php
  $host = 'localhost';
  $username = 'username';
  $password = 'password';
  $database = 'database';
  $connection = mysql_connect($host,$username,$password) or die('Could not connect to the database' . mysql_error());
  mysql_select_db($database,$connection);
?>
  1. <?php
  2.   $host = 'localhost';
  3.   $username = 'username';
  4.   $password = 'password';
  5.   $database = 'database';
  6.   $connection = mysql_connect($host,$username,$password) or die('Could not connect to the database' . mysql_error());
  7.   mysql_select_db($database,$connection);
  8. ?>


going through all the rows:
PHP Code: [ Select ]
<?php
  $sql_select = 'SELECT * FROM `table_name`';
  $sql_query = mysql_query($sql_select);
  if(!$sql_query){
    echo 'There was an error while performing the search';
  }
  else{
    if(mysql_num_rows($sql_query) > 0){
      while($row = mysql_fetch_assoc($sql_query)){
        $sql_update = "UPDATE `table_name` SET
                      `randorder`='" . rand(1,200000) . "'
                      WHERE `id`={$row['id']}";
        $sql_query2 = mysql_query($sql_update);
        echo (!$sql_query2) ? "There was an error while updating row {$row['id']}<br />" : "Row {$row['id']} has been updated.";
      }
    }
    else{
      echo 'There are no rows in the database';
    }
  }
?>
  1. <?php
  2.   $sql_select = 'SELECT * FROM `table_name`';
  3.   $sql_query = mysql_query($sql_select);
  4.   if(!$sql_query){
  5.     echo 'There was an error while performing the search';
  6.   }
  7.   else{
  8.     if(mysql_num_rows($sql_query) > 0){
  9.       while($row = mysql_fetch_assoc($sql_query)){
  10.         $sql_update = "UPDATE `table_name` SET
  11.                       `randorder`='" . rand(1,200000) . "'
  12.                       WHERE `id`={$row['id']}";
  13.         $sql_query2 = mysql_query($sql_update);
  14.         echo (!$sql_query2) ? "There was an error while updating row {$row['id']}<br />" : "Row {$row['id']} has been updated.";
  15.       }
  16.     }
  17.     else{
  18.       echo 'There are no rows in the database';
  19.     }
  20.   }
  21. ?>


Hope that helps ...
  • Mark709
  • Novice
  • Novice
  • Mark709
  • Posts: 22

Post 3+ Months Ago

Dear Scuffle
.
Many, many thanks for your execellent and clear code.
I was able to follow it - but never in a fit be able to write it.
I did not get it to work the first 2 runs then IT WORKS LIKE A CHARM.
I am most grateful for your help.
Now if I might be so bold ==================================
.
Would you possibly be able to assist me further ============
with another set of code and answer to one important question
.
I just need to fix the issue described below ===============
so if you are able I would appreciate your input as I do like your
programming style because it is extremely logical to follow.
.
So if you have the time I would appreciate your CODING help again.
. ================================================================
.
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 file month to month unless varied via Table BANANA}
ID (INT, 8) (PRIMARY INDEX),
MAKECODE (Var, 8) (Unique Index) {consider if 2+ fields are deleted}
DATE (YYYYMMDD), {being date of original entry in this table}
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}
ID (INT, 8) (PRIMARY INDEX),
ORIGINALCODE (Var, 8) (Unique Index)
REPLACEMENT (Var, 8) {often this field is NULL because no replacement}
DATE (YYYYMMDD), {being date of original entry in this table}
STYLE (Var, 4) {value is of no importance}
plus field
UPGRADED (YYYYMMDD) - has NO DATE - until used to upgrade data in file APPLE
then will hold system current date of the date it is used to upgrade APPLE
(NEEDS 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 UPGRADED (not unique - as many fields -with 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.}
.
.
PHP program code required to be manually started - (to function as below)
================================================
1. (at certain time eg 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 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.
  • Mark709
  • Novice
  • Novice
  • Mark709
  • Posts: 22

Post 3+ Months Ago

Hi Scuffle
In the reply I have just made I see "the Yellow man" has appeared and replaced the length of fields that I had inserted.
Please assume all fields lengths are 8 characters
Thanks
Mark

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 76 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
 
 

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