Page 1 of 1

Is this MS-Access feature available in PHP/MySql

Posted: Tue Oct 07, 2014 5:51 am
by bowlesj
Hi,

Being pretty new at PHP and MySql I have not discovered an example that parallels a feature that MS-Access has.
I was hoping someone could review these two sets of code to tell me if my PHP/MySql conversion of the MS-Access code is the only way (most efficient way)
or if PHP/MySql has a similar set of commands that MS-Access offers (and if PHP does if this is a good idea anyway).

This is a very simple example. I commented only the part of the code that parallels between MS-Access and PHP/MySql.
MS-Access comments start with a quote ' rather than the PHP //
The speacial MS-Access commands are .edit and .update
Both functions are tested and working.

Thanks,
John

Code: Select all

Private Function funcResetTempMembers()
    lngTempNameNumber = 0
    Dim dbs As Database
    Dim rst As Recordset
    Set dbs = CurrentDb
    lngjwMultiQueryOneOrAny2Long4 = Me.fldJLTR_Key
    Set rst = dbs.OpenRecordset("qryMemberTempList", dbOpenDynaset)
    DoCmd.SetWarnings False
    With rst
        If .RecordCount = 0 Then
            GoTo Closefiles
        End If
        .MoveFirst
        Dim TempName As String
        Dim MyDate As Date
        MyDate = Now()
        Do Until .EOF
            lngTempNameNumber = lngTempNameNumber + 1
            TempName = "Temp_" & Me.fldJLTR_Key & MyDate & "_" & lngTempNameNumber
            .Edit 'This is the special command the MS-Access has to allow direct updates to the records produced by the query. You also use .new to create a new record
            !fldMM_FirstName = TempName 'Direct update of the fields without need for a 2nd SQL statement
            !fldMM_LastName = TempName 'Direct update of the fields without need for a 2nd SQL statement
            !fldMM_MiddleName = TempName 'Direct update of the fields without need for a 2nd SQL statement
            .Update 'Again this is one of the special command MS-Access has for writing the record out
            .MoveNext
        Loop
    End With
    DoCmd.SetWarnings True
Closefiles:
    rst.Close: Set rst = Nothing
    dbs.Close: Set dbs = Nothing
End Function

Code: Select all

Function funcResetTempMembers() {
   global $con;
   global $AnyErrors;
   global $fldJLTR_Key;
   global $ResultMF;

   Include 'qryMemberTempList.php';
   $qstring = qryMemberTempList($fldJLTR_Key);
   $result = @mysqli_query($con,$qstring);
   if (!$result) {
      $AnyErrors = "Y";
      $ResultMessTemp = "Error retrieving member master for temps! (data) 1. "  . mysqli_error($con);
   }
   if ($AnyErrors == "N") {
      $MyDate = date('Y-m-d H:i:s');
      $lngTempNameNumber = 0;
      while($row = mysqli_fetch_array($result)) {
         $lngTempNameNumber = $lngTempNameNumber + 1;
         $TempName = 'Temp_' . $fldJLTR_Key . $MyDate . $lngTempNameNumber;

         //START Commands that MS-Acccess is doing with rst.edit and rst.update
         $fldMM_Key = $row['fldMM_Key'];
         $sql = "
             UPDATE
               tblMemberMaster
             SET
               fldMM_FirstName='$TempName',
               fldMM_MiddleName='$TempName',
               fldMM_LastName='$TempName'
             WHERE
               fldMM_Key='$fldMM_Key';
         ";
         if (!@mysqli_query($con, $sql)) {
            $AnyErrors = "Y";
            $ResultMF = "Error resetting the temp member names on the member master. " . mysqli_error($con);
         }
         //END Commands that MS-Acccess is doing with rst.edit and rst.update

      } //while($row = mysqli_fetch_array($result))
   } //if ($AnyErrors == "N")
} //Function funcResetTempMembers()

Re: Is this MS-Access feature available in PHP/MySql

Posted: Tue Oct 07, 2014 4:03 pm
by Christopher
I'm not sure what the question is? And it is a little confusing because you are doing some complex query to get a bunch of keys and then using them to set a bunch of records first/last names numbered temp value?!? So maybe take a step back because something does not seem right here.

Re: Is this MS-Access feature available in PHP/MySql

Posted: Wed Oct 08, 2014 4:04 am
by Weirdan
What's the feature you're looking for, in simple words? Is it using object methods like .Update instead of doing direct db queries?

Re: Is this MS-Access feature available in PHP/MySql

Posted: Wed Oct 08, 2014 9:34 am
by bowlesj
I'm not sure what the question is? And it is a little confusing because you are doing some complex query to get a bunch of keys and then using them to set a bunch of records first/last names numbered temp value?!? So maybe take a step back because something does not seem right here.
Sorry guys. Yeah, hindsight is 20/20 vision. I realized yesterday I could greatly simplify my original MS-Access query by dumping one of the keys directly into the Member Master record (into a field that is not used for temporary members). It simplified it down to a very simple one file query also avoiding having to set up a special key field. I also made a bunch of other little improvements (did a test and it seems to work for both). I updated the original posted code with less comments (only comments where the special Access commands are, hopefully making it easy to isolate the question).
Is it using object methods like .Update instead of doing direct db queries?
Yes, I have been thinking about what MS-Access must be doing. I think the .edit command (which is actually rst.edit) is going back to analyse the record set that has been pulled into memory and somehow getting a pointer so the programmer can update the fields with the rst!FieldName = "whatever" commands then using the .update command (rst.update) to do the write of the record out. There is also an rst.new command (not shown ) that can create a new record. During the use of the rst.new command there is no need to do another query to get the new autonumber index (another savings of code). This obtainment of the new primary key autonumber value must be done before the rst.update command is executed.

Last Minute Update:
related to the above paragraph, I was doing some debugging in MS-Access to detect/eliminate duplicate key errors happening and I noticed that when the rst.edit command executes the function that sends one of my keys to the query (to build it) is being executed (I pick it up in the debugger). So it appears that MS-Access is doing with these special commands another sql query update just as I have set up in PHP/MySql. Interesting.

Re: Is this MS-Access feature available in PHP/MySql

Posted: Wed Oct 08, 2014 11:47 am
by Christopher
bowlesj wrote:
I'm not sure what the question is? And it is a little confusing because you are doing some complex query to get a bunch of keys and then using them to set a bunch of records first/last names numbered temp value?!? So maybe take a step back because something does not seem right here.
Sorry guys. Yeah, hindsight is 20/20 vision. I realized yesterday I could greatly simplify my original MS-Access query by dumping one of the keys directly into the Member Master record (into a field that is not used for temporary members). It simplified it down to a very simple one file query also avoiding having to set up a special key field. I also made a bunch of other little improvements (did a test and it seems to work for both). I updated the original posted code with less comments (only comments where the special Access commands are, hopefully making it easy to isolate the question).

Yes, I have been thinking about what MS-Access must be doing.
You have made improvements, but I am wondering if maybe rethinking the whole solution now that you are using SQL with a fully relational database. I think my first question would be: why do you need update all these existing records with numbered temp names? What problem do those numbered temp names solve?

Re: Is this MS-Access feature available in PHP/MySql

Posted: Wed Oct 08, 2014 2:18 pm
by bowlesj
I can't really give away the exact details of the website but it is a new (much more efficient) way of doing something that has been done for a very long time. Especially when the site first gets out there the existing people doing what they do would have unexpected arrivals (none members) who they may want to put in as temp members and who they would likely want to get to join as members. In some cases they may just want to not use the temp member feature (totally optional and it would depend if they are using an opotional screen plugged into the computer). So anyway, bit by bit, if this website takes off there would be less and less use of the temp member records. Someone as a temp member would join later. So the total temp member records to actual member records would be extremely low (maybe .05%) even at the start. The reason it would start out low is not all people running a location would have a need for temp member records at all (I would be the first one out and I have no need for it). I thought of having temp member records turned into real records but this would slow down things too much. Its better for a location to have 5 or 6 temp records ready if they are going to use that feature. It is basically just a real member record with not all the details filled out, no need to log in and some automatons related to it. The other reason is the location may not have web access and the MS-Access download offline program may be in use (no way to put a real record on the website). So essentially I am covering all bases for roll out of this website by having this feature.

Re: Is this MS-Access feature available in PHP/MySql

Posted: Wed Oct 08, 2014 3:47 pm
by Christopher
I guess I am just wondering why you need per-filled temp records. And even then, why the numbered first/middle/last names? Can't you just create the records as they are requested? Or at least leave the first/middle/last names blank and fill them in when first used?

Re: Is this MS-Access feature available in PHP/MySql

Posted: Wed Oct 08, 2014 5:10 pm
by bowlesj
They actually will be created as requested with a button click then the name info is changed to the unexpected participant's name.
Current records are for my tests of course.
Once created I will just prepare them for the next event (the person hosting the vent kicks it off of course).
I originally was finding them by a middle name search for "Temp_2%" ("Temp_2*" in MS-Access).
Switching to finding them by the UserID instead was one of the improvements I just made (a protected, hidden, unchanged field starting with "Temp_2").
So I suppose now I could blank out the names so the person operating the event has less work and is also not confused.
Good idea. Thanks. Its funny how one improvement often leads to a series of improvements like dominoes falling.
I am trying to make this thing as slick as I possibly can (speed wise basically) so every bit helps.
I think writing it initially in MS-Access which I know fairly well was a good move because it gives a 2nd pass on the design with a different view point partly due to the new technology.
No doubt Its better to fix things like this before roll out. The realization that some events would have unexpected participants came somewhat late in the MS-Access design so its an evolution.

Re: Is this MS-Access feature available in PHP/MySql

Posted: Thu Oct 09, 2014 7:05 am
by Weirdan
Something like you described exists in PHP world in form of various ORM frameworks. For example, with Propel (an ORM framework) the code would look similar to:

Code: Select all

$persons = PersonPeer::doSelect((new Criteria)->add(PersonPeer::ISTEMPORARY));
foreach ($persons as $person) {
   $person
     ->setFirstName(uniqid())
     ->setLastName(uniqid())
     ->save(); // updates existing record
}

$newPerson = new Person;
$newPerson
   ->setFirstName(uniqid())
   ->setLastName(uniqid())
   ->save(); // creates a new Person record

Re: Is this MS-Access feature available in PHP/MySql

Posted: Thu Oct 09, 2014 2:27 pm
by Christopher
bowlesj wrote:They actually will be created as requested with a button click then the name info is changed to the unexpected participant's name.
Current records are for my tests of course.
Once created I will just prepare them for the next event (the person hosting the vent kicks it off of course).
I originally was finding them by a middle name search for "Temp_2%" ("Temp_2*" in MS-Access).
Switching to finding them by the UserID instead was one of the improvements I just made (a protected, hidden, unchanged field starting with "Temp_2").
So I suppose now I could blank out the names so the person operating the event has less work and is also not confused.
Good idea. Thanks. Its funny how one improvement often leads to a series of improvements like dominoes falling.
I am trying to make this thing as slick as I possibly can (speed wise basically) so every bit helps.
I think writing it initially in MS-Access which I know fairly well was a good move because it gives a 2nd pass on the design with a different view point partly due to the new technology.
No doubt Its better to fix things like this before roll out. The realization that some events would have unexpected participants came somewhat late in the MS-Access design so its an evolution.
If you don't need the incremental value in those fields then you could perhaps do the whole thing with one query by doing:

REPLACE INTO tblMemberMaster SELECT ...

where the SELECT is the one you showed above, but with all the columns for tblMemberMaster filled in with either the existing data or updated/blank values.

Re: Is this MS-Access feature available in PHP/MySql

Posted: Mon Jan 12, 2015 6:22 pm
by bowlesj
Thanks guys for your example ideas.

Again, sorry for the long delay in replying. I am not getting emails for this site which is the way I know a response has come in. I will see if I can figure it out. Maybe I will email Benjamin.