david
|
| Posted: 04/07/2004, 1:33 AM |
|
Hi all,
scenario:
i hav two tables
1) members
2) messages
fields in members are
1)member_id
2)nickname
3)...
fields in messages table are
1)message_id
2)from_id (UserID from session)
3)to_id
4)title
5)message
6)---
i want to display the title and nickname of the from_id and title in grid form where messages.to_id = UserID(Session)
i hav used VQB.but it results all usernames in result..
how can i achieve the goal?..that i need
display the title and nickname of the from_id and title in grid form where messages.to_id = UserID(Session)
anyone plz ..
thanks in adv
david
|
|
|
 |
peterr
Posts: 5971
|
| Posted: 04/07/2004, 1:41 AM |
|
OK, so I'm assuming that your query pulls the right data, and the only issue is with limiting/filtering the results so that only records of the current user are shown?
If so, then in the "Data Source" Dialog/Window you should click on the "+" button to add a new parameter. Here is the screenshot of that parameter window: http://docs.codecharge.com/studio/html/UserGuide/Forms/ug285.png
Once there, you'd enter the following information:
Field Name: to_id
Field Type: Integer
Condition: equals (=)
Parameter source Name: UserID
Parameter source Type: Session
Default Value: 0
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
ignat
Posts: 55
|
| Posted: 04/07/2004, 2:02 AM |
|
David,
Select messages and then left join members.
Regards,
Ignat.
|
|
|
|
David
|
| Posted: 04/07/2004, 9:09 AM |
|
Quote :OK, so I'm assuming that your query pulls the right data, and the only issue is with limiting/filtering the results so that only records of the current user are shown?
I have already tried what u have told even before posting.but the result is having more column than i needed.
I have then changed it to SQL query.but then also it's similer.i think it's a query problom.giv ur BEST as u can.i'll do my BEST.and if i get solution,i'll post here (the problom and solution,the best way to make CCS superb and make using it easy)..
probloms are opportunities..especially for improvement.
|
|
|
|
David
|
| Posted: 04/07/2004, 9:15 AM |
|
Quote ignat:
David,
Select messages and then left join members.
Regards,
Ignat.
Ignat, there is no common field to make a JOIN.but i hav tried it by custom SQL but no way..
|
|
|
|
David
|
| Posted: 04/07/2004, 9:16 AM |
|
This forum seems nice ..may i get this forums code as CCS file or any type of source?..
if so ..plz let me know..
thanks all..CCS is a Great product.i love it.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/07/2004, 11:38 AM |
|
David,
What do you mean by more columns? You don't need to use all fields in your Grid. You can delete the Grid columns that you don't need.
Regarding JOIN, I think that you should join the fields from_id and member_id. If no fields can be joined then something may be wrong with the database design.
I cannot get the code for this forum therefore I doubt that you could (at least as of now), but you may contact www.ultraapps.com to see if they would make it available.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
David
|
| Posted: 04/08/2004, 12:22 AM |
|
Quote peterr:
David,
What do you mean by more columns? You don't need to use all fields in your Grid. You can delete the Grid columns that you don't need.
Regarding JOIN, I think that you should join the fields from_id and member_id. If no fields can be joined then something may be wrong with the database design.
I cannot get the code for this forum therefore I doubt that you could (at least as of now), but you may contact www.ultraapps.com to see if they would make it available.
Hi Peter,
I actally mean more rows than more columns.look,i'll tell the problom,
suppose i have 2 messages in messages table.10 members in members table,
my need is,
getting the messages for the member_id '2'(suppose that is UserID).
I have used VQB like,i have selected 2 tables in VQB,and in parameter specification,i have made messages.to_id = UserID(Session).
then i checked the page,
the result is,
all 10 member nicknames and from_id's and title(repeated according to nickname).totally 10 rows.
i needed only 1 row becoz in messages table there is only one message whose to_id is 2(we supposed as UserID).
how to resolve?..there is some more complex things coming up ..i think this is simple with someone experienced.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/08/2004, 12:27 AM |
|
I must say that this issue seems to be related to an incorrect database design. The database should be relational and if you're using two tables in a ingle query then they should be "join'able".
However, I wrote above that "you should join the fields from_id and member_id" and I still believe that this is the right solution. If this works then this part of your database may be OK.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
David
|
| Posted: 04/09/2004, 2:38 AM |
|
can u tell what's the problom with my DB design?..i have made 2 foreign keys in messages.that can be linked with member_id in members table.but MySQL does'nt support foreign keys so VQB does'nt joining it.
as i told previousely,it's a query problom.and i resolved it now.
it's all SQL.custom query is required,table cannot solve.
thanks for all the help
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/09/2004, 4:37 AM |
|
The problem may be only what you wrote earlier:
Quote :Ignat, there is no common field to make a JOIN
Which is the reason that I responded:
Quote :The database should be relational and if you're using two tables in a single query then they should be "join'able".
You can join any fields you want in the VQB and instead of creating a query manually you can create the same query by joining tables in VQB. Neither MySQL nor VQB requires support for foreign keys - you create such support by joining two tables together via common fields (even if they have different nams), either in VQB or via custom SQL. Both VQB and SQL must use tables and they work the same way. VQB just creates your SQL automatically so that you don't need to type it manually.
VQB cannot handle all situations (like DISTINCT and GROUP BY statements), but in this case I don't see a reason why it shouldn't work.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
David
|
| Posted: 04/09/2004, 11:43 PM |
|
Dear Peter,
Plz tell me how to join the simple tables in my condition in VQB.i have already done it using query but i like VQB solution,so in future,i can avoid query.
i m having some other issues also i'll ask u later,
thanks for the replies.
|
|
|
|
David
|
| Posted: 04/09/2004, 11:54 PM |
|
Hi Peter,
The other issue is a 3 way JOIN
as i told,having 2 tables,
1) members
2)messages
and now another,
3)contacts
fields in contacts table are,
1) contact_id
2)member_id
3)buddy_id
member_id and buddy_id are foreign keys to members table
fields in members table are
1)member_id
2)nickname
3)...
fields in messages table are
1)message_id
2)from_id (UserID from session)
3)to_id
4)title
5)message
6)date_entered
i need nickname of my contacts (contacts.buddy_id)from members table.
title from messages table.
date_entered from messages table
where contacts.member_id = UserID(Session).
3 table is to be joined,i m getting more rows than i needed.
can u suggest a solution?.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/10/2004, 12:54 AM |
|
Hi,
The method of joining tables in VQB is mentioned at http://docs.codecharge.com/studio/html/UserGuide/Forms/FormDataSource.html :
Quote :You can create new join links by dragging a field from one table to another field in a different table. Once the join link appears between the tables, you can use the properties button to confirm the joined fields as well as set the join type and the operator used for the join criteria [e.g. equals (=) , Less than (<), Is Null, etc].
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
David
|
| Posted: 04/10/2004, 10:08 PM |
|
[codeselect memnickname as buddyname
, msg.title
, msg.message
, msg.date_entered
from messages msg
inner
join contacts con
on msg.to_id = con.member_id
inner
join members mem
on con.buddy_id = mem.member_id
where msg.to_id = UserID(Session)
this is the query i needed.can this may done with VQB?.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/13/2004, 3:13 PM |
|
David,
Yes. I don't see a problem with creating such query in VQB. Just connect the join fields together (to_id with member_id and buddy_id with member_id) by dragging them onto each other in VQB.
Some of the screenshots in our docs (and our examples and tutorials) utilize quite complex queries. For example take a look at http://docs.codecharge.com/studio/html/UserGuide/Forms/ug283.png
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
David
|
| Posted: 04/16/2004, 3:41 AM |
|
HI Peter,
Thanks buddy..
|
|
|
|
krishna
|
| Posted: 06/02/2004, 3:36 AM |
|
I have one multi record block and when i enter data then i want to prevent
to enter duplicate rows in the grid is that aany body send me code for this
|
|
|
|
lotus
|
| Posted: 09/13/2004, 3:34 AM |
|
How do I do multiple insert in the table for rows which are dynamically in using html.I am using php and mysql.
|
|
|
|
