Confused

[mATOK]

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?

[Mattyspatty]

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)

[mATOK]

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

 

[craygo]

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

 

 

[mATOK]

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...

[craygo]

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

[mATOK]

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

[craygo]

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

[mATOK]

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

[craygo]

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

[mATOK]

So in the groupName field you can have All Stations AND London in the same field and row??

 

Same field, same ID, different row

[mATOK]

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]'";

              }

[mATOK]

**bump**

[craygo]

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

[mATOK]

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

[craygo]

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

 

[mATOK]

Thanx Ray... now you see why I titled the post confused?

[craygo]

LOL you could of said you were looking for reqids that have 2-5 specific row entries in a table. 

 

Ray