0

How can i get values from mysql database usin while loop?

Sry for my bad english and i'm new in vb.net so please do not insult me because I do not know vb.net, I'm trying to learn, i'll try to explain what i need. :)

Lets go:

We know that EmailAddress is real email address, now first query must be something like this 

Query1 = "select ID from database.table_users where email='" & EmailAddress & "'"

Now we know that Query1 will return value id from table_users. Now we need create new query like this 

Query2 = "select friend_id from database.table_friends where user_id='" & query1 & "'"

Okay, now Query2 return friend_id from table_friends. One more query like this 

Query3 = "select displayname from database.table_users where id='" & Query2 & "'"

Query3 must return displaynames of all users which we got in query2.

Example:

First i need query ID from table_users where email=EmailAddres Then i need query friend_id from table_friends where user_id=ID After that i nedd query displayname from table_users where id=friend_id

*these are just examples and they are not correct as you can see :D

I need 3 querys in while loop, and only last query (displayname) will go to the listbox. While must return all mysql values from table_friends where ID= id of EmailAddress user.

Improve this question
3
  • it's only for this example, i actualy have different names, but because my english is bad i cannot explain :( Commented Jul 14, 2015 at 12:43
  • 1
    Someone can bang out a single query with a join Commented Jul 14, 2015 at 12:47
  • 2
    1) You don't need to loop over these values, you need to look up the JOIN syntax. 2) Your queries are open to SQL injection, please look up parameterized queries. Commented Jul 14, 2015 at 12:47

2 Answers 2

Reset to default
1

Join your tables into a single query. Something like this:

SELECT
    Friends.displayname
FROM
    table_users AS Users
    INNER JOIN table_friends AS Connections
        ON Users.user_id = Connections.user_id
    INNER JOIN table_users AS Friends
        ON Connections.friend_id = Friends.user_id
WHERE
    Users.email = @EmailAddress

This creates two references to the table_users table, one for the initial user and one for the friends, connected by your many-to-many linking table. (Note also the use of a query parameter, which I highly recommend instead of directly concatenating input into the query.)

SQL is very powerful for querying structured data (hence its name), you'll be much better off building your query logic into SQL rather than keeping the SQL queries overly simple and making multiple trips to the database.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

why loop and why can't you make it single query like below using JOIN

select tf.displayname from table_users tu
join table_friends tf on tu.id = tf.friend_id
and tu.email = '" & EmailAddress & "';
Improve this answer

7 Comments

hm, loop beucase there is more than one values which must be returned, all user which are friends with EmailAddress.
@Gogo, there is no need of a loop; you can do a join with other table and will get desired result. Try the posted query in answer and see your self.
I make that, i got correct number of rows, but for every row displayname is my displayname, how edit that to get friends displayname? select tu.displayname from users tu join friends tf on tu.id = tf.friend and tu.email = '" & EmailAddress & "';
@Gogo, I though you are getting desplayname from users and not from friends. If you want it from friends table then change the table alias in select query. Try the edited query in answer and see if that helps.
I'll try change something, thank you, this is what i need.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.