how to in mysql/php

[Jim Whitaker]

Expand full for viewing...
I have a parent/child table. The basic setup is as follows:
The parent table is called load sheet. It has fields such as Tripno,
carrier, loaddate, etc.
The child table contains the actual stop offs, in other words the shipper,
load city, load state,

etc. If a pick then shipper, otherwise a consignee.
I have no problem with this parent child table setup. Here's the problem:
I am migrating fom Microsoft Access to a Mysql/PHP setup. From these tables
mentioned above I

currently use vb code to loop through the tables and combine information so
as to add or edit to

a different table all together. This table is called Dispatch. Its sole
purpose is to (at a

glance), see exactly what is going on with what load. Let me explain:
On the dispatch table, I have all information for a pick or drop at a
glance, this might look

like this

tripno loaddate carrier loadfrom more fields----->
123456 04/17/2006 davis trucking abilene, tx / ballinger,tx

As you can see, the load cities from the child table is combined in one
field here.
This loop code snip looks like:

If K = 1 Then
FCITY = rstPICK!CITY & "," & " " & rstPICK!STATE
end if
If K > 1 Then
FCITY = FCITY & " / " & rstPICK!CITY & "," & " " & rstPICK!STATE
end if

The complete MS Access code is below of the load sheet after update event.

Now for the big question, What does this look like in PHP code to accomplish
the same thing????
Say I just entered A main load, and the child records have 2 picks and 3
drops. After entering

the final drop what would I do do generate What code, located where????
If I could get this part done, I have the whole project figured out.

Private Sub Form_AfterUpdate()
On Error GoTo C91ERR
Dim dbsDISPATCH As Database
Dim rstDISPATCH As Recordset
Dim rstPICK As Recordset
Dim strMessage As String
Set dbsDISPATCH = CurrentDb
Set rstDISPATCH = dbsDISPATCH.OpenRecordset("DISPATCH", dbOpenTable)
Dim QUT, STRSQL, STRTRIP
Dim CARRIER1, GRECNUM
Dim FCITY, FSTATE, TCITY, TSTATE
Dim BRATE, CRATE, TDROP
Dim MULTI, BILLTOV, SHIPPERV, CONSIGNV
Dim LDATEV, DDATEV
Dim TRIPNOV, LOADNOV, COMMV
Dim K
QUT = Chr$(34)
STRSQL = "SELECT * FROM PICKUPS ORDER BY PICKUPS.TRIPNO, PICKUPS.PICKNO;"
Set rstPICK = dbsDISPATCH.OpenRecordset(STRSQL)

TRIPNOV = TRIPNO
If Len(Trim$(CARRIER & vbNullString)) = 0 Then
CARRIER1 = ""
Else
CARRIER1 = CARRIER
End If
If Len(Trim$(BILLTO & vbNullString)) = 0 Then
BILLTOV = ""
Else
BILLTOV = BILLTO
End If
MULTI = "M"
If Len(Trim$(BILLAT & vbNullString)) = 0 Then
BRATE = 0
Else
BRATE = BILLAT
End If
If Len(Trim$(PAYAT & vbNullString)) = 0 Then
CRATE = 0
Else
CRATE = PAYAT
End If
GRECNUM = rstPICK.RecordCount
If GRECNUM = 0 Then
rstPICK.Close
rstDISPATCH.Close
dbsDISPATCH.Close
MsgBox "NO TRIPS SAVED"
NOSUC = 1
GoTo C91
End If
rstPICK.MoveFirst
STRTRIP = "[TRIPNO] = " & QUT & Me![TRIPNO] & QUT
rstPICK.FindFirst STRTRIP
If rstPICK.NoMatch Then
rstPICK.MoveLast
rstPICK.Close
rstDISPATCH.Close
dbsDISPATCH.Close
NOSUC = 1
GoTo C91
End If
K = 0
Do Until rstPICK.EOF
K = K + 1
If rstPICK!TRIPNO <> TRIPNOV Then Exit Do
If IsNull(rstPICK!CITY) Then
rstPICK.Close
rstDISPATCH.Close
dbsDISPATCH.Close
MsgBox "NO PICKUP CITY, SAVE LATER WHEN ALL IS ENTERED"
NOSUC = 1
GoTo C91
End If
If IsNull(rstPICK!STATE) Then
rstPICK.Close
rstDISPATCH.Close
dbsDISPATCH.Close
MsgBox "NO PICKUP STATE, SAVE LATER WHEN ALL IS ENTERED"
NOSUC = 1
GoTo C91
End If
If K = 1 Then
FCITY = rstPICK!CITY & "," & " " & rstPICK!STATE
If IsNull(rstPICK!SHIPPER) Then
SHIPPERV = ""
Else
SHIPPERV = rstPICK!SHIPPER
End If
If IsNull(rstPICK!LOADDATE) Then
LDATEV = ""
Else
LDATEV = rstPICK!LOADDATE
End If
If IsNull(rstPICK!LOADNO) Then
LOADNOV = ""
Else
LOADNOV = rstPICK!LOADNO
End If
If IsNull(rstPICK!COMMODITY) Then
COMMV = ""
Else
COMMV = rstPICK!COMMODITY
End If
End If
If K > 1 Then
FCITY = FCITY & " / " & rstPICK!CITY & "," & " " & rstPICK!STATE

If IsNull(rstPICK!SHIPPER) Then
SHIPPERV = SHIPPERV
Else
SHIPPERV = SHIPPERV & " / " & rstPICK!SHIPPER
End If
End If
rstPICK.MoveNext
Loop
rstPICK.MoveLast
rstPICK.Close

If Len(Trim(FCITY)) > 240 Then
FCITY = Mid(FCITY, 1, 240)
End If
If Len(Trim(SHIPPERV)) > 240 Then
SHIPPERV = Mid(SHIPPERV, 1, 240)
End If

' DROPS ************************************************** ***************

Dim rstDROP As Recordset
STRSQL = "SELECT * FROM DROPS ORDER BY DROPS.TRIPNO, DROPS.DROPNO;"
Set rstDROP = dbsDISPATCH.OpenRecordset(STRSQL)

GRECNUM = rstDROP.RecordCount
If GRECNUM = 0 Then
rstDROP.Close
rstDISPATCH.Close
dbsDISPATCH.Close
MsgBox "NO TRIPS SAVED"
NOSUC = 1
GoTo C91
End If

rstDROP.MoveFirst
STRTRIP = "[TRIPNO] = " & QUT & Me![TRIPNO] & QUT
rstDROP.FindFirst STRTRIP
If rstDROP.NoMatch Then
rstDROP.MoveLast
rstDROP.Close
rstDISPATCH.Close
dbsDISPATCH.Close
NOSUC = 1
GoTo C91
End If
K = 0
Do Until rstDROP.EOF
K = K + 1
If rstDROP!TRIPNO <> TRIPNOV Then Exit Do
If IsNull(rstDROP!CITY) Then
rstDROP.Close
rstDISPATCH.Close
dbsDISPATCH.Close
MsgBox "NO DROP CITY, SAVE LATER WHEN ALL IS ENTERED"
NOSUC = 1
GoTo C91
End If
If IsNull(rstDROP!STATE) Then
rstDROP.Close
rstDISPATCH.Close
dbsDISPATCH.Close
MsgBox "NO DROP STATE, SAVE LATER WHEN ALL IS ENTERED"
NOSUC = 1
GoTo C91
End If
If K = 1 Then
TCITY = rstDROP!CITY & "," & " " & rstDROP!STATE

If IsNull(rstDROP!CONSIGNEE) Then
CONSIGNV = ""
Else
CONSIGNV = rstDROP!CONSIGNEE
End If
If IsNull(rstDROP!UNLOADDATE) Then
DDATEV = ""
Else
DDATEV = rstDROP!UNLOADDATE
End If
End If
If K > 1 Then
TCITY = TCITY & " / " & rstDROP!CITY & "," & " " & rstDROP!STATE
If IsNull(rstDROP!CONSIGNEE) Then
CONSIGNV = CONSIGNV
Else
CONSIGNV = CONSIGNV & " / " & rstDROP!CONSIGNEE
End If
End If
rstDROP.MoveNext
Loop
rstDROP.MoveLast
rstDROP.Close
If Len(Trim(TCITY)) > 240 Then
TCITY = Mid(TCITY, 1, 240)
End If
If Len(Trim(CONSIGNV)) > 240 Then
CONSIGNV = Mid(CONSIGNV, 1, 240)
End If
' ADDING TO DISPATCH
************************************************** *******
GRECNUM = rstDISPATCH.RecordCount
If GRECNUM = 0 Then
rstDISPATCH.Close
dbsDISPATCH.Close
NOSUC = 1
GoTo C91
End If
rstDISPATCH.Index = "TRIPNO"
rstDISPATCH.MoveFirst
rstDISPATCH.Seek "=", TRIPNOV
If rstDISPATCH.NoMatch Then
With rstDISPATCH
..AddNew ' Add new record.
If CARRIER1 = "" Then
!CARRIER = Null
Else
!CARRIER = CARRIER1
End If

!TRIPNO = TRIPNOV
!SM = MULTI
If BILLTOV = "" Then
!BILL_TO = Null
Else
!BILL_TO = BILLTOV
End If
If SHIPPERV = "" Then
!SHIPPER = Null
Else
!SHIPPER = SHIPPERV
End If
If FCITY = "" Then
!CITY_LD = Null
Else
!CITY_LD = FCITY
End If
If CONSIGNV = "" Then
!CONSIGNEE = Null
Else
!CONSIGNEE = CONSIGNV
End If
If LDATEV = "" Then
!LOAD_DATE = Null
Else
!LOAD_DATE = LDATEV
End If
If DDATEV = "" Then
!DEL_DATE = Null
Else
!DEL_DATE = DDATEV
End If
If COMMV = "" Then
!COMMODITY = Null
Else
!COMMODITY = COMMV
End If
If LOADNOV = "" Then
!LOADNO = Null
Else
!LOADNO = LOADNOV
End If

If TCITY = "" Then
!CITY_DEL = Null
Else
!CITY_DEL = TCITY
End If
If CRATE = 0 Then
!PAYAT = Null
Else
!PAYAT = CRATE
End If
If Len(Trim$(PAYOTHER & vbNullString)) = 0 Then
!PAYOTHER = Null
Else
!PAYOTHER = PAYOTHER
End If
If Len(Trim$(DROP & vbNullString)) = 0 Then
!PK_DRP = Null
Else
!PK_DRP = DROP
End If
If Len(Trim$(LUMPER & vbNullString)) = 0 Then
!LUMPER = Null
Else
!LUMPER = LUMPER
End If
!TOTALPAY = Nz(PAYAT) + Nz(PAYOTHER) + Nz(DROP) + Nz(LUMPER)

If Len(Trim$(NOTES & vbNullString)) = 0 Then
!NOTES = Null
Else
!NOTES = NOTES
End If
..Update ' Save changes.
..Close
End With
Else
With rstDISPATCH
' EDIT DISPATCH ************************************************** **********
..Edit
If CARRIER1 = "" Then
!CARRIER = Null
Else
!CARRIER = CARRIER1
End If
'!CARRIER = CARRIER1
!TRIPNO = TRIPNOV
!SM = MULTI
If BILLTOV = "" Then
!BILL_TO = Null
Else
!BILL_TO = BILLTOV
End If
If SHIPPERV = "" Then
!SHIPPER = Null
Else
!SHIPPER = SHIPPERV
End If
If FCITY = "" Then
!CITY_LD = Null
Else
!CITY_LD = FCITY
End If
If CONSIGNV = "" Then
!CONSIGNEE = Null
Else
!CONSIGNEE = CONSIGNV
End If
If LDATEV = "" Then
!LOAD_DATE = Null
Else
!LOAD_DATE = LDATEV
End If
If DDATEV = "" Then
!DEL_DATE = Null
Else
!DEL_DATE = DDATEV
End If
If COMMV = "" Then
!COMMODITY = Null
Else
!COMMODITY = COMMV
End If
If LOADNOV = "" Then
!LOADNO = Null
Else
!LOADNO = LOADNOV
End If
If TCITY = "" Then
!CITY_DEL = Null
Else
!CITY_DEL = TCITY
End If
If CRATE = 0 Then
!PAYAT = Null
Else
!PAYAT = CRATE
End If
If Len(Trim$(PAYOTHER & vbNullString)) = 0 Then
!PAYOTHER = Null
Else
!PAYOTHER = PAYOTHER
End If
If Len(Trim$(DROP & vbNullString)) = 0 Then
!PK_DRP = Null
Else
!PK_DRP = DROP
End If
If Len(Trim$(LUMPER & vbNullString)) = 0 Then
!LUMPER = Null
Else
!LUMPER = LUMPER
End If
!TOTALPAY = Nz(PAYAT) + Nz(PAYOTHER) + Nz(DROP) + Nz(LUMPER)
If Len(Trim$(NOTES & vbNullString)) = 0 Then
!NOTES = Null
Else
!NOTES = NOTES
End If
..Update ' Save changes.
..Close
End With
End If
'************************************
dbsDISPATCH.Close
C91:
If FIRLOAD = 0 Then FIRLOAD = 1
TRIPNO.Locked = True
If NOSUC = 1 Then
Call NOSUCCES
End If
Exit Sub
C91ERR:
MsgBox "AN AFTER UPDATE ERROR OCCURRED"
Resume

End Sub