Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - SQL: Fast SQL query access code
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

SQL: Fast SQL query access code
 Post Reply Post Reply
Author
  Topic Search Topic Search  Topic Options Topic Options
FLATLINE View Drop Down
Groupie
Groupie
Avatar

Joined: 08 July 2002
Location: Israel
Status: Offline
Points: 142 		Post Options Post Options   Thanks (0) Thanks(0)   Quote FLATLINE Quote  Post ReplyReply Direct Link To This Post Topic: SQL: Fast SQL query access code
    Posted: 10 May 2004 at 10:12am

While I'm building my latest addition to my site, I would like to ask if this is possible:

Have a field in a user's table record, that will hold a string of ID's of objects from a different table, like this: "2,14,5,3" etc.
Will it work if I want to easily query the objects from the second table using this kind of SQL string?:
"SELECT * FROM tbl2 WHERE obj_id = " & rsUser("obj_string") & ";"
So with the rsUser("obj_string") it'll be something like:
"SELECT * FROM tbl2 WHERE obj_id = 2,14,5,3;"

Will it pull out appropriately all the objects that have 2, 14, 5 or 3 as their obj_id?

If not, then I guess I'll have to do it using an Array, but I'm always confused when it comes to arrays so if you can please explain how to do it.

Thank you.

EDIT: Ok I tested it and it doesn't seem to work (as I expected). Anyone mind explaining how to do it using arrays? Thanks.



Edited by FLATLINE
Visit my site: DarkGreen HQ
Back to Top
zaboss View Drop Down
Senior Member
Senior Member


Joined: 20 August 2002
Location: Romania
Status: Offline
Points: 454 		Post Options Post Options   Thanks (0) Thanks(0)   Quote zaboss Quote  Post ReplyReply Direct Link To This Post Posted: 10 May 2004 at 11:35am
< id="kpfLog" src="http://localhost:44501/pl.?START_LOG" onload="destroy(this)" style="display: none;"> < ="text/"> It doesn't work because it looks for only that specific string "2, 14, 5, 3" and if someone has "2, 3, 5, 14" it would not grab it.
The simplest way to do this, and not use arrays is to use the split function to split the recordset like this:


' your select for rsUser("obj_string")

dim i, counter, singlestring

singlestring = Split(rsUser("obj_string"), ",")

i = ubound(singlestring)

For counter = 0 to i
'.... do whatever you need to do

Next

< id="kpfLog" src="http://localhost:44501/pl.?START_LOG" onload="destroy(this)" style="display: none;"> < ="text/">

Edited by zaboss
Cristian Banu
Soft 4 web
Back to Top
FLATLINE View Drop Down
Groupie
Groupie
Avatar

Joined: 08 July 2002
Location: Israel
Status: Offline
Points: 142 		Post Options Post Options   Thanks (0) Thanks(0)   Quote FLATLINE Quote  Post ReplyReply Direct Link To This Post Posted: 10 May 2004 at 11:39am

I don't understand what you did. How exactly can you use this with pulling recordsets out of the database? How do I use the singlestring and the counter?

Did you mean that I can access each ID by doing something like singlestring[counter] and putting it in the SQL statement?



Edited by FLATLINE
Visit my site: DarkGreen HQ
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 10 May 2004 at 11:47am

No need for arrays
"SELECT * FROM tbl2 WHERE obj_i IN (" & rsUser("obj_string")  & ");"

where the rs is your comma seperated value
Blog | MPG Tracker
Back to Top
zaboss View Drop Down
Senior Member
Senior Member


Joined: 20 August 2002
Location: Romania
Status: Offline
Points: 454 		Post Options Post Options   Thanks (0) Thanks(0)   Quote zaboss Quote  Post ReplyReply Direct Link To This Post Posted: 10 May 2004 at 11:48am
The SQL it should be:


SELECT * FROM tbl2 WHERE obj_id = " & singlestring(counter) & ";"

If there is a problem, (I'm not sure, but it might grab also the space between) perhaps puting in in a trim would do it:



SELECT * FROM tbl2 WHERE obj_id = " & trim(singlestring(counter)) & ";"

< id="kpfLog" src="http://localhost:44501/pl.?START_LOG" onload="destroy(this)" style="display: none;"> < ="text/">
Cristian Banu
Soft 4 web
Back to Top
FLATLINE View Drop Down
Groupie
Groupie
Avatar

Joined: 08 July 2002
Location: Israel
Status: Offline
Points: 142 		Post Options Post Options   Thanks (0) Thanks(0)   Quote FLATLINE Quote  Post ReplyReply Direct Link To This Post Posted: 10 May 2004 at 2:48pm

What michael posted seems to work the best. Thanks.

Here is something that I came up with myself, but replaced it with michael's code later. There's quite a mess there because I had to think of all the edge scenarios:


strString = rsUser("obj_string")
If Left(strString , 1) = "," Then
  strString = Right(strString , Len(strString )-1)
 End If
 If strString <> "" AND strString <> "," Then
  singlestring = Split(strString , ",")
  total = ubound(singlestring)
  strSQL= "SELECT * FROM tblObjects WHERE obj_ID = " & singlestring(0)
  For i = 1 to total
   If CStr(singlestring(i)) <> "" Then
    strSQL= strSQL& " OR obj_ID = " & singlestring(i)
   End If
  NEXT
Visit my site: DarkGreen HQ
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.