Question about SELECT statement.

[seahorse123]

I have a table "tbl" which contains field "column1", and an array $v1 which contains strings "a1,a2,a3,a4", I want to populate all data from "tbl" that column1 matches "a1" or "a2" or "a3" or "a4", how to do that? do I have to split the array first and run this SELECT statement repeatly?

SELECT * from tbl where column1=<...>

thanks for the help.

 

[SQLBill]

First, are you using Microsoft SQL Server? (If not, you've posted in the wrong forum).

-SQLBill

Posting advice: FAQ481-4875

 

[DotNetGnat]

try this:

SELECT * from tbl where column1 IN ('"&replace(myarray,",","','")&"')

-DNG

 

[seahorse123]

Yes, I use Microsoft SQL server, I tried that code, but it doesn't work.
Let me explain more detail, I have two tables ("groups" and "users"), each group has one or more users, table "group" contains group name and array that users inside this group(format in array: user1,user2,user3)
Now I want to print group name and individuals under this group line by line.
Group1 user1
Group1 User2
Group1 user3
Group2 user2
Group2 user3

I tried the following code, however it hangs because of the loop.

$query= "SELECT group,id FROM groups";
$result= mssql_query($query); 
for ($i = 0; $i < mssql_num_rows($result); ++$i)
 {
  $line = mssql_fetch_row($result0);
  $v_group=$line[0];
  $v_id=$line[1];

  print "Group=$v_group";
$qry="SELECT firstname,lastname from users where indid IN ($v_id)";
$rt= mssql_query($qry); 			
for ($i = 0; $i < mssql_num_rows( $rt ); ++$i)
	{
	$line = mssql_fetch_row($rt);
	print "$line[0] $line[1]";
	}		 
}

I don't think these code will work effectively, but I have no idea how to optimize it, anyone can help me?

Thanks.

 

[rac2]

Maybe get all the data in one query, then use one loop to move through the rows which each have exactly the data you require.

SELECT group, firstname,lastname
FROM users
JOIN groups ON groups.id = users.indid

Of course you cannot do it this way with one row per group in the groups table. It would be necessary to redefine the groups table to have one row per group member with columns for the group and the individual member id. So a group with 40 members would have 40 rows in the groups table.

As for your script, it seems like it will work. I wonder what you mean when you say "it hangs because of the loop"?

Do you obtain a string similar to this for each group as a value for $qry?

SELECT firstname,lastname from users where indid IN (1,2);

That is good SQL and would yield two rows.

I am not familiar with the ODBC module you are using so I cant comment on those lines of code. But do you really mean this-

mssql_fetch_row($result0);

Or perhaps do you mean

mssql_fetch_row($result);