mATOK Posted June 17, 2008 Share Posted June 17, 2008 Hey there... I would like to make a selection from the db and then loop through the results searching for a match and then loop through those results and so on. My problem is that GroupName can contain various things I am looking for but on different rows. eg RQID 1 Row 1 GroupName = All Stations RQID 1 Row 2 GroupName = M-F RQID 1 Row 3 GroupName = London So first I want to find all the RQids that fall between certain dates, then I go through those to see if which ones contain All stations, then I want to go through the ones that contain all stations searching for more information.... $SQLrequestID = "SELECT Request_Group.RequestId FROM Request_Group INNER JOIN Request_Date ON Request_Group.RequestId = Request_Date.RequestId WHERE Request_Date.DateFrom >= '$startdate' AND Request_Date.DateTo <= '$enddate'"; $result = mssql_query($SQLrequestID); while ($requestids = mssql_fetch_array($result)) { $SQLrequestID = "SELECT Request_Group.RequestId FROM Request_Group WHERE Request_Group.GroupName LIKE '%All Stations%'"; } In the end all I really want are the IDs that contain 4 or 5 specific entries in the GroupName field.... can anyone tell me how to go about this? Quote Link to comment Share on other sites More sharing options...
Mattyspatty Posted June 17, 2008 Share Posted June 17, 2008 you can do all that with 1 SQL query. SELECT * FROM table_Group WHERE (ID=1 OR ID=2) AND (val=1 OR val=3) Quote Link to comment Share on other sites More sharing options...
mATOK Posted June 17, 2008 Author Share Posted June 17, 2008 No that wont work as I don't want to see all stations or M-F, I want to see all stations and M-F... the problem is that the same field contains both but on a different row Quote Link to comment Share on other sites More sharing options...
craygo Posted June 17, 2008 Share Posted June 17, 2008 then use IN $SQLrequestID = "SELECT Request_Group.RequestId FROM Request_Group INNER JOIN Request_Date ON Request_Group.RequestId = Request_Date.RequestId WHERE Request_Date.DateFrom >= '$startdate' AND Request_Date.DateTo <= '$enddate' AND GroupName IN ('All Stations', 'M-F')"; Ray Quote Link to comment Share on other sites More sharing options...
mATOK Posted June 17, 2008 Author Share Posted June 17, 2008 craygo that SQL does not return anything.... Here is an example of the data I am working with.... RQ# GroupName 120 001 Geography 120 001 Marketa 120 002 Marketb 120 001 Person1 120 001 Stationname 120 002 Stationname 120 003 Stationname 120 004 Stationname 120 M...... 19:00 - 19:30 This is why I figured multiple SQL statments... Quote Link to comment Share on other sites More sharing options...
craygo Posted June 17, 2008 Share Posted June 17, 2008 Of course not. I wasn't aware you had other data in the field $SQLrequestID = "SELECT Request_Group.RequestId FROM Request_Group INNER JOIN Request_Date ON Request_Group.RequestId = Request_Date.RequestId WHERE Request_Date.DateFrom >= '$startdate' AND Request_Date.DateTo <= '$enddate' AND (GroupName LIKE '%All Stations%' OR GroupName LIKE '%M-F%')"; i don't see a problem doing this with one query but if need be you should be able to do it with more Ray Quote Link to comment Share on other sites More sharing options...
mATOK Posted June 17, 2008 Author Share Posted June 17, 2008 I appreciate the effort but that's not going to get me what I want... I want the RQ#s that contain specific data in the GroupName field... unfortunately there are multiple rows for each RQ and is why I thought of multiple SQL statments. I don't know how to loop it using php.... x = all RQs that have 'A' in GroupName y = all RQs that are found in x that have 'B' in Groupname z = all RQs that are founf in y that have 'C' in Groupname Quote Link to comment Share on other sites More sharing options...
craygo Posted June 17, 2008 Share Posted June 17, 2008 I guess I am confused now because it should work, maybe you should just use JOIN and not INNER JOIN. Why use INNER JOIN anyway?? Do you want just one of the rows to be returned for each RQ number?? Maybe an example would help, from the data you listed below, what do you expect to be returned?? Ray Quote Link to comment Share on other sites More sharing options...
mATOK Posted June 17, 2008 Author Share Posted June 17, 2008 I would like one row returned for each RQ number.. right now I get a bunch... the row #s are inconsequential to me For instance SELECT Request_Group.RequestId FROM Request_Group INNER JOIN Request_Date ON Request_Group.RequestId = Request_Date.RequestId WHERE Request_Group.GroupName LIKE '%All Stations%' AND Request_Date.DateFrom >= '2008-05-01' AND Request_Date.DateTo <= '2008-05-31' gives me the below request ids 53769 53833 53833 53833 53874 53874 53874 53874 53874 53874 54241 54528 now I want to say, which of the above request ids have Request_Group.GroupName LIKE '%London%' . Remember, each Id could be present on several rows... I just want to keep filtering down until I get a requestId that matches 4 or 5 of my patterns Quote Link to comment Share on other sites More sharing options...
craygo Posted June 17, 2008 Share Posted June 17, 2008 So in the groupName field you can have All Stations AND London in the same field and row?? Cause if you are looking for All Stations in the GroupName then none of them will have London. Try using distinct SELECT DISTINCT Request_Group.RequestId FROM Request_Group INNER JOIN Request_Date ON Request_Group.RequestId = Request_Date.RequestId WHERE Request_Group.GroupName LIKE '%All Stations%' AND Request_Date.DateFrom >= '2008-05-01' AND Request_Date.DateTo <= '2008-05-31' Ray Quote Link to comment Share on other sites More sharing options...
mATOK Posted June 17, 2008 Author Share Posted June 17, 2008 So in the groupName field you can have All Stations AND London in the same field and row?? Same field, same ID, different row Quote Link to comment Share on other sites More sharing options...
mATOK Posted June 17, 2008 Author Share Posted June 17, 2008 I am trying something like this $startdate='05/01/2008'; $enddate='05/30/2008'; $SQL1 = "SELECT DISTINCT Request_Group.RequestId FROM Request_Group INNER JOIN Request_Date ON Request_Group.RequestId = Request_Date.RequestId WHERE Request_Date.DateFrom >= '$startdate' AND Request_Date.DateTo <= '$enddate'"; $result = mssql_query($SQL1); while ($requestids = mssql_fetch_array($result)) { $numofids = count($requestids); for ($i=0; $i<=$numofids; $i++) { $SQL2 ="SELECT Request_Group.RequestId FROM Request_Group WHERE Request_Group.GroupName LIKE '%All Stations%' AND RequestId='$numofids[$i]'"; } Quote Link to comment Share on other sites More sharing options...
mATOK Posted June 17, 2008 Author Share Posted June 17, 2008 **bump** Quote Link to comment Share on other sites More sharing options...
craygo Posted June 17, 2008 Share Posted June 17, 2008 I guess I just don't get it because something like you want here: gives me the below request ids 53769 53833 53833 53833 53874 53874 53874 53874 53874 53874 54241 54528 now I want to say, which of the above request ids have Request_Group.GroupName LIKE '%London%' . Will never happen because you already searched for "All Stations" so NONE of them will have London in them. Like I said before, the INNER JOIN is probably giving you more results than you want. Ray Quote Link to comment Share on other sites More sharing options...
mATOK Posted June 17, 2008 Author Share Posted June 17, 2008 some of them will..... there are multiple rows for each ID... GroupName contains all kinds of data.... it looks like this RQID | GroupName 1 All Stations 1 London 1 M-F 2 All Stations 2 Paris 2 M-F So I want to say 1st, show me all the RQ IDs that have say M-F..... The db returns 1 & 2... then I want to say which of the returned RQids (1 & 2) contain London.... it should return 1 This is why I am not trying to do it all in one query Quote Link to comment Share on other sites More sharing options...
craygo Posted June 17, 2008 Share Posted June 17, 2008 I think I understand now. you can do a loop within a loop like so <?php $startdate='05/01/2008'; $enddate='05/30/2008'; $SQL1 = "SELECT DISTINCT Request_Group.RequestId AS reqid FROM Request_Group INNER JOIN Request_Date ON Request_Group.RequestId = Request_Date.RequestId WHERE Request_Date.DateFrom >= '$startdate' AND Request_Date.DateTo <= '$enddate'"; $result = mssql_query($SQL1); while ($requestids = mssql_fetch_array($result)) { $reqid = $requestids['reqid']; $SQL2 ="SELECT Request_Group.RequestId as reqid FROM Request_Group WHERE Request_Group.GroupName LIKE '%All Stations%' AND RequestId='$reqid'"; $result2 = mysql_query($SQL2) or die(mysql_error()); while($r = mssql_fetch_array($result2)){ echo $r['reqid']."<br />"; } } ?> Or store your first query results in an array and implode it <?php $startdate='05/01/2008'; $enddate='05/30/2008'; $SQL1 = "SELECT DISTINCT Request_Group.RequestId AS reqid FROM Request_Group INNER JOIN Request_Date ON Request_Group.RequestId = Request_Date.RequestId WHERE Request_Date.DateFrom >= '$startdate' AND Request_Date.DateTo <= '$enddate'"; $result = mssql_query($SQL1); while ($requestids = mssql_fetch_array($result)) { $req_id[] = $requestids['reqid']; } $req_ids = implode(", ", $req_id); $SQL2 ="SELECT Request_Group.RequestId as reqid FROM Request_Group WHERE Request_Group.GroupName LIKE '%All Stations%' AND RequestId IN ('$req_ids')"; $result2 = mysql_query($SQL2) or die(mysql_error()); while($r = mssql_fetch_array($result2)){ echo $r['reqid']."<br />"; } ?> Ray Quote Link to comment Share on other sites More sharing options...
mATOK Posted June 17, 2008 Author Share Posted June 17, 2008 Thanx Ray... now you see why I titled the post confused? Quote Link to comment Share on other sites More sharing options...
craygo Posted June 17, 2008 Share Posted June 17, 2008 LOL you could of said you were looking for reqids that have 2-5 specific row entries in a table. Ray 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.