PDA

View Full Version : Hey DJ! or anyone that may know.


Creative Insanity
09-28-2006, 07:08 PM
OK.. here is the lowdown.
I am building a helpdesk and all is fine and dandy except for the replies.

I have the tickets in one table and the replies in another table and in the recordset I have an inner join on IDs, but that only works for the first reply (reason is obvious)
FROM ticket INNER JOIN reply ON tick_ID = reply_id
so what I wanna know is how do you join the 2 databases so it picks all the replies that are related to that ticket.

Ta muchly. ;)

ranjan
09-28-2006, 10:23 PM
Why inner join?


FROM ticket, reply
WHERE ticket.tick_id=reply.reply_id

Creative Insanity
09-29-2006, 12:31 AM
Thanks heaps ran.. reading what you put there made me start bashing my head saying "dumb C dumb C" as so simple when someone else kicks you.. must of been the 3 days lack of sleep.
Ta for the kick LOL

Creative Insanity
09-29-2006, 06:38 AM
Sorry Ran.. you idea never worked.
The tickets do not show on the detail page.

davidj
09-29-2006, 09:44 AM
if i understand correctly you can have 1 ticket and many replies


select * FROM ticket LEFT JOIN reply ON ticket.tick_ID = reply.reply_id

Creative Insanity
09-29-2006, 10:51 AM
Yes you are correct DJ and I tried a left join also and did not work.

Just to shed a little more light on what I am doing (maybe wrong), I have the ticket subject and txt in the ticket table and the replies in another. But only one reply is showing.

davidj
09-29-2006, 12:24 PM
ok

are you sure that your ticket ID = reply ID

example...

ticket ID = 10

reply ID =10
reply ID =10
reply ID =10
reply ID =10

davidj
09-29-2006, 12:40 PM
i have created your senario

try this then work out where your going wrong

CREATE TABLE `ticket` (
`ticket_id` int(11) default NULL,
`data` int(11) default NULL
) TYPE=MyISAM;

CREATE TABLE `reply` (
`reply_id` int(11) default NULL,
`data` int(11) default NULL
) TYPE=MyISAM;

select * from ticket
left join reply on reply.reply_id = ticket.ticket_id

this works as i have tested it

make sure your table names/fields are underscored instead of spaced

ticket ID <- wrong
ticket_ID <- right

etc

Creative Insanity
09-29-2006, 08:43 PM
ah huh.. cheers DJ will test that.
My tables look like this:
CREATE TABLE `reply` (
`reply_ID` int(11) NOT NULL auto_increment,
`txt` text NOT NULL,
`From` varchar(30) NOT NULL default '',
PRIMARY KEY (`reply_ID`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;


CREATE TABLE `ticket` (
`tick_ID` int(11) NOT NULL auto_increment,
`subject` varchar(50) NOT NULL default '',
`status` varchar(10) NOT NULL default '',
`date` varchar(30) NOT NULL default '',
`prio` varchar(10) NOT NULL default '',
`ticket` text NOT NULL,
`starter` varchar(50) NOT NULL default '',
`cat` varchar(10) NOT NULL default '',
PRIMARY KEY (`tick_ID`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

So maybe my database is not right.. gotta be cause I just about tried everything else. I will have a crack with your idea and then add to the ticket table a bity at a time and see what I happens.
I will keep you informed on how it all goes DJ.

BTW, hows the new home going?

davidj
10-02-2006, 07:47 AM
home looking great

want to upgrade my HIFI and found the perfect Amp but wife said no (see Below)

also want new LCD TV but wife said no

Looking for a games machine but wife ...... err.. you get the idea

Creative Insanity
10-02-2006, 08:04 AM
Yeah I get the idea.. your wife wares the pants in your house LOL.

No luck with the reply display either.. (just to stay on topic). LOL

davidj
10-02-2006, 08:07 AM
emulate what i have written...

add 1 to the ticket ID and add many 1's to the reply ID

then run your Query Join

i got back the joined results!

how are you writing your SQL Join

davidj
10-02-2006, 08:13 AM
use this DB creation script so we are both singing from the same hymn sheet (< business jargon - dont you just love it)

davidj
10-02-2006, 10:27 AM
you are auto-incrementing both ID's ????

how will that link both tables??

i would auto increment the ticket ID then get that auto id and do a manual insert on the reply id

Creative Insanity
10-02-2006, 05:57 PM
Ahh after many days the penny drops.. daaa Ken hehe.

So if I were to have the reply ID manual and when a reply is entered have a hidden feild place the ID for THAT ticket into the reply ID.. ahh huh now this is making better sence. I am really treading on new ground here and what with this and tackeling css/div as well me thinks my wee brain is overloaded LOL.

BTW you didn't answer the question about the wife and the pants LOL.. but no answer gives me the clear picture.. you have a huge thumb print on the top of your head ah LOL.

davidj
10-03-2006, 09:28 AM
i disagree

i get to keep 20% of my wage and i would have somethin to say if she tried to take any more off me... She knows when to draw the line...

Ooo there would be harsh words which i would think about saying to her.

I would even give her one of my looks.. Which are quite intimidating

Creative Insanity
10-03-2006, 09:35 AM
You real man DJ LOL

edbr
10-03-2006, 09:54 AM
I would even give her one of my looks.. Which are quite intimidating
not the 1000 yard stare!

davidj
10-03-2006, 09:55 AM
god NO

i wouldnt dare stare

its more of a glance... from a different room!

Creative Insanity
10-03-2006, 10:34 AM
its more of a glance... from a different room!

while she is not looking LOL

Creative Insanity
10-03-2006, 08:12 PM
Ok further on this baby.

Ok I am getting there with my database and you ideas and so far so good.

BUT (ain't there bloody always one LOL) the tick_ID is not echoing to the forum text field.

Explain Ken whatcha doing huh.

What I thought I would do is in the reply form echo the ticket ID into a field and have that as a hidden field along with the user name (which is a session) and all they need to is enter their reply and walla!
I have tried by entering the ID manually and it works.. but this is not really a good senario I think for a online app used by many and I REALLY want to have that echoed into the database automaticly.

As you can see the session user name is working (hence the dan which is what I am logged in as) but there is no ticket ID in thew next field (should say 1).
Could ya give me another kick DJ to wake me up please.
As you can see the ID does echo (see top of ticket in image, so why not in text field?

See nice little pic below.

Creative Insanity
10-03-2006, 09:02 PM
Oh man the problems with this thing EVER going to end! Now the tickets are repeating in display x the number of replies.. this is driving me NUTS!
I think it is tut hunting time or atleast where is that damn php book LOL.

Creative Insanity
10-03-2006, 09:42 PM
I give up. Now all the tickets are showing the replies to all.. stuff this I am not doing this.
Too many sleepless nights over this already.. but man I do hate to be beaten and it doesn't happen often. Also from the master page the correct ID is not being picked up.
driving me nuts.

I might just start over and see what the hell is going on.. Oh I hate starting over and over.

davidj
10-04-2006, 07:57 AM
ok

start from the first prob and we'll work through them

davidj
10-04-2006, 08:04 AM
if your echo-ing the var tick_ID to the page then its no different to echo to a text field


<input type="text" name="textfield" value="<?php echo $row['tic_id'] ?>"/>

then run the page to see if it returned

once you get this working just change the textfield to a hidden field

Creative Insanity
10-04-2006, 07:31 PM
That is exacly what I am doing there DJ
<input name="reply_ID" type="text" id="reply_ID" value="<?php echo $row_DetailRS1['tick_ID']; ?>" size="4" />

Creative Insanity
10-04-2006, 08:47 PM
Umm I created a file so you can see what I have got and what I am doing. (http://kenmight.com/ci/doing/)
If it doesn't start, press F5
Sorry about the reso.. hehe forgot to change the size at encode time Opps!

davidj
10-10-2006, 07:36 AM
sorry for the delay. Been ill so through a sicky

did you get anywhere with this prob