cmn2 Posted March 31, 2010 Share Posted March 31, 2010 Greetings. Very new at PHP, but have experience with asp.net. This asp technique has been very useful for rolling up data with a single database hit. I would be interested to know how you would do this in php. Thank you in advance for any help you can lend. 'dt is a datatable in this example Dim aryControlInstances As New ArrayList 'put unique ids in arraylist Dim r As DataRow For Each r In dt.Rows If aryControlInstances.Contains(r.Item("IDJobTypeDefinition")) = False Then aryControlInstances.Add(r.Item("IDJobTypeDefinition")) End If Next Dim aryEnumerator As System.Collections.IEnumerator = aryControlInstances.GetEnumerator() Dim strFilter As String Dim rows() As DataRow 'query original datatable for each unique id and process each one While aryEnumerator.MoveNext strFilter = "IDJobTypeDefinition = " & "'" & aryEnumerator.Current & "'" rows = dt.Select(strFilter) If rows.Length > 0 Then 'do your processing here 'such as rolling up your data for each id End If End While Quote Link to comment Share on other sites More sharing options...
Mchl Posted March 31, 2010 Share Posted March 31, 2010 Could perhaps describe it in more detail? Many of us are not familiar with asp and can only guess how it's supposed to work. Quote Link to comment Share on other sites More sharing options...
cmn2 Posted March 31, 2010 Author Share Posted March 31, 2010 Thanks for responding. I've added better explanation to my code. 'dt is a datatable in this example 'similar to a resultset 'datatable may hold multiple records of the same id such as a customer number Dim aryControlInstances As New ArrayList 'put unique ids in arraylist 'this loops thru the entire datatable row® by row and places each id it finds into an array 'if the id is already in the array it does not add it again Dim r As DataRow For Each r In dt.Rows If aryControlInstances.Contains(r.Item("IDJobTypeDefinition")) = False Then aryControlInstances.Add(r.Item("IDJobTypeDefinition")) End If Next 'this sets up an enumerator to loop thru the array from the for\next loop above Dim aryEnumerator As System.Collections.IEnumerator = aryControlInstances.GetEnumerator() Dim strFilter As String 'this is an array that can hold a number of database rows Dim rows() As DataRow 'loop thru the array using movenext and build a query string for the current id While aryEnumerator.MoveNext strFilter = "IDJobTypeDefinition = " & "'" & aryEnumerator.Current & "'" 'populate the rows() array with the records that match our filter 'think of strFilter as a WHERE statement without the word WHERE rows = dt.Select(strFilter) 'work with the results If rows.Length > 0 Then 'do your processing here 'such as rolling up your data for each id End If 'on to the next id in our enumerator End While Quote Link to comment Share on other sites More sharing options...
ignace Posted March 31, 2010 Share Posted March 31, 2010 From what I can remember from my VB.NET classes should this be the equivalent without all the internal validation ofcourse // $_POST['records'] = array([n] => <id>) $in = implode(', ', $_POST['records']); $query = "SELECT * FROM table WHERE field IN ($in)"; $result = mysql_query($query); if ($result && mysql_num_rows($result)) { while ($row = mysql_fetch_assoc($result)) { //do your processing here } } Quote Link to comment Share on other sites More sharing options...
Mchl Posted March 31, 2010 Share Posted March 31, 2010 mysql_num_rows() check is not really necessary here. if ($result && mysql_num_rows($result)) while checks the condition before first run. Quote Link to comment Share on other sites More sharing options...
ignace Posted March 31, 2010 Share Posted March 31, 2010 mysql_num_rows() check is not really necessary here. if ($result && mysql_num_rows($result)) while checks the condition before first run. I tried to be consistent with his code Quote Link to comment Share on other sites More sharing options...
cmn2 Posted April 1, 2010 Author Share Posted April 1, 2010 // $_POST['records'] = array([n] => <id>) $in = implode(', ', $_POST['records']); $query = "SELECT * FROM table WHERE field IN ($in)"; $result = mysql_query($query); if ($result && mysql_num_rows($result)) { while ($row = mysql_fetch_assoc($result)) { //do your processing here } } Thanks for your input and your understanding in my newness to php. I think your example is close, but I do not see in your code where all the records for each individual ID can be worked as a group before processing moves to the next ID. If I'm understanding correctly, your $query grabs all records of all IDs, but does not break up the $result any finer. Remember, I'm trying to keep this to a single database hit. The asp behavior I'm trying to replicate is basically to have the ability to query $result for smaller individual subsets without more database hits. Hope that makes sense. Quote Link to comment Share on other sites More sharing options...
ignace Posted April 1, 2010 Share Posted April 1, 2010 <input type="checkbox" name="records[]" value="<?php print $row['id']; ?>"> Repeated multiple times. The user selects a record by checking the checkbox and pressing update or edit or something these ID's are then passed through my script which will load all selected records from the database for editing/updating/.. Quote Link to comment Share on other sites More sharing options...
cmn2 Posted April 1, 2010 Author Share Posted April 1, 2010 Found this solution on the web. Looks to be close to what I need. One database hit and the ability to pull a subset of that data. Still needs work to replicate my original asp function. <? $rsCourse = mysql_query("SELECT * FROM table"); $FilteredArray = Filter($rsCourse,"ProgrammeTypeID ",13); function Filter($RecordSet,$FilterField,$FilterCriteria){ $ReturnArray = array(); while($row = mysql_fetch_assoc($RecordSet)){ foreach($row as $key => $value){ if($key==$FilterField&&$value==$FilterCriteria){ array_push($ReturnArray,$row); } } } return $ReturnArray; } ?> Quote Link to comment Share on other sites More sharing options...
Mchl Posted April 1, 2010 Share Posted April 1, 2010 The solution ignace gave you would also do only one database query. Even better it would only fetch those records you need, instead of fetching whole table. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.