looping in php

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

Jim, Just a little advise on posting if I may:

You posted a big piece of VB code, which is ment to run in an application.
Things like:
MsgBox "AN AFTER UPDATE ERROR OCCURRED"
Resume

are nonsense in PHP. No such thing as a messagebox on the server (I hope).

And also: Maybe somebody is friendly enough to go through all the code, but
most, like me, are a bit too lazy for that and expect a to-the-point
question concerning PHP instead of a puzzle in a 'strange' language.

You are of course your own man, and can choose to post like this, but keep
in mind that you'll get quicker and more responses if you keep your
questions clear and to the point (PHP).
(No offense intended, just a friendly advise)

Regards,
Erwin Moller

If you look at the part above the code, I asked the to the point question.
The code was included, because some people insist on seeing code. You
forget, there may be someone else on this newsproup that also has programmed
in visual basic that can help me convert this to PHP. I don't care about
the stupid message box, I WANT TO KNOW HOW TO LOOP THROUGH RECORDS AND PLUCK
INFORMATION FROM THEM. Now, that is to the point.

"Erwin Moller"
<since_humans_read_this_I_am_spammed_too_much@spam yourself.com> wrote in
message news:44437594$0$31653$e4fe514c@news.xs4all.nl...
> Jim, Just a little advise on posting if I may:
>
> You posted a big piece of VB code, which is ment to run in an application.
> Things like:
> MsgBox "AN AFTER UPDATE ERROR OCCURRED"
> Resume
>
> are nonsense in PHP. No such thing as a messagebox on the server (I hope).
>
> And also: Maybe somebody is friendly enough to go through all the code,
but
> most, like me, are a bit too lazy for that and expect a to-the-point
> question concerning PHP instead of a puzzle in a 'strange' language.
>
> You are of course your own man, and can choose to post like this, but keep
> in mind that you'll get quicker and more responses if you keep your
> questions clear and to the point (PHP).
> (No offense intended, just a friendly advise)
>
> Regards,
> Erwin Moller
>

Jim Whitaker wrote:
> I WANT TO KNOW HOW
> TO LOOP THROUGH RECORDS AND PLUCK INFORMATION FROM THEM. Now, that
> is to the point.

$result = mysql_query(some_query);
while(mysql_fetch_array($result,MYSQL_ASSOC){
//do stuff with it
}

Rik, where is you `stuff'? ;)

$res = mysql_query('select * from foo') or die(mysql_error());
while ($row = mysql_fetch_assoc($res))
print($row['colname']);
mysql_free_result($res);

I would say that it is up to the OP to fill in the 'stuff'. If they
want to hire us to do their job for them, then a request should be made
to that effect. As a rule, **I** will answer questions but not write
the entire code segment. If I have something similar where I can
cut/paste, then that is one thing - writing it from scratch, testing it
etc.. - well that is up to the OP.

no-1 wrote:

> I would say that it is up to the OP to fill in the 'stuff'. If they
> want to hire us to do their job for them, then a request should be made
> to that effect. As a rule, **I** will answer questions but not write
> the entire code segment. If I have something similar where I can
> cut/paste, then that is one thing - writing it from scratch, testing it
> etc.. - well that is up to the OP.

Hey No-1,

:-)
Agree, and I also kinda lost my appetite to help the OP in the right
direction.

/me think I quit giving friendly advise, some people don't get it.
Maybe I should add more sugar on top.
;-)

Regards,
Erwin Moller

In the below, say I did whatever I wanted to do to with this record, ie.,
retrived a city to a temp variable. How do I move or goto the next record?
Example in english of what I am asking:
Say the result set is as follows:
ID FROMCITY
23 CLEVELAND
24 DETROIT

I want to goto record with ID of 23 first, and store CLEVELAND in a tempvar,
so temppvar = FROMCITY.
Now, I want to move to ID 24 and store DETROIT like this:
tempvar = tempvar + " / " + FROMCITY
At this point tempvar would be CLEVELAND / DETROIT.
This is what I'm after, loop through a recordset. I mean in the background
in code, not manually do this.
You can't tell me you can't loop through a recordset using php.

> $result = mysql_query(some_query);
> while(mysql_fetch_array($result,MYSQL_ASSOC){
> //do stuff with it
> }
>
>

As milahu and Rik said this is PHP looping through a result
set...(maybe not in the VB sense). There isn't a direct equivalent to
the VB6 recordset, but you don't need one.

//executes the query
$res = mysql_query('select * from foo') or die(mysql_error());

//loops until the end of the result set.
while ($row = mysql_fetch_assoc($res))

//Your tempvar thingy could go in here...
$tempvar = $tempvar . " / " . $row['colname']

//releases the result set reference.
mysql_free_result($res);

Obviously that should have been:

//executes the query
$res = mysql_query('select * from foo') or die(mysql_error());

//loops until the end of the result set.
while ($row = mysql_fetch_assoc($res))

//Your tempvar thingy could go in here...
$tempvar = $tempvar . " / " . $row['colname'];

//releases the result set reference.
mysql_free_result($res);