Query over multiple tables

[alka]

Hi,

 

I'm in the process of creating a site, for learning purpose but also for my own use and leter on a released

service to the public...

 

Its a basic tracker atm.. with 3 tables... users, trackers and tracked_data..

 

I need to add "Reset" tracker data, but that turned out to be a little challenge...

 

The data I want to reset is in tracked_data.. tracked_data does not link directly to

users... so I need to run a query where I make sure the user who is trying to delete

this tracker actually owns the tracker..

 

basically "DELETE FROM tracked_data WHERE tid = X" works ok.. it deleted the tracker with ID X..

HOwever I need to make sure the user owns the tracker.. like WHERE should be tid = X and ID = Y where

it fetches the ID from the users table..

 

I don't know if I managed to explain that well enough.. I hope so 

 

 

 

[cags]

You don't really explain how to tell if a user owns the data. Is there a user_id field in tracked_data? How do you identify who owns the data, I know thats essentially what your trying to code, but I mean how would you do it by manually looking at the tables.

[alka]

Sorry... Here is how its connected...

 

users contains users data.

trackers contains tracker data - It does have a field for users id so they are linked that way

tracked_data contains the actual data that the tracker has collected.. it contains the trackers id as 'tid'. Since this one does not contain any link to users I don't know how to  "link it all together" and make a query for the purpose I wrote above in the original post .

 

 

 

[cags]

So trackers contains an id for a the user and tracked_data contains an id for trackers, like a chain? Sound like your looking for a JOIN, I've never personally done a JOIN on a DELETE but I assume it works in the same manner. If that is the case you'd be looking for something along the lines of...

 

DELETE FROM tracked_data td
JOIN trackers t ON t.id=td.tid
JOIN users u ON u.id=t.user_id
WHERE tid = X AND u.id= Y

[alka]

Geez.. okey... Hmm.. yeah thats the thing.. when you start learning PHP and MySQL its like learning two languages.. That query sure got me confused and I'm like  , but thats of course because I've not learned it yet.

 

In any case.. Do you care to clarify a little with what parts I need to replace with actual rows in my database? ;-)...

 

Here are some more details.

 

trackers_data contains and id of course for each unique line tracked. Its called 'id' .. The tracker it self is referred to as 'tid' in trackers_data

trackers contains and id as well for each unique tracker. Its again called 'id'. Users id is stored as 'uid' in trackers.

and finally users just contain users data with each unique user having its own 'id'..

 

I could not place the proper parts in the proper place, when I tried I got an error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN trackers t ON t.id=td.tid JOIN users u ON u.id=t.user_id WHERE tid = 18 AND' at line 1

 

X and Y have been replaced by variables of course and they work with other simpler queries.

 

Hope I'm not asking for too much haha I'm a quick learner though, once I figure out the bits and pieces in this query I'll gain a lot.

 

Thanks

 

 

[JustLikeIcarus]

If you also want to display an error if the user doesnt own the tracker you may find it a better solution to first do

 

select count(*) as cnt from trackers where id = ? and uid = ?

 

then using the result of that query

 

if ($count == 1){
//run your delete
}else{
//return your error
}

[alka]

That's a great idea.. thanks for the tips I'm new to all this. .. I thought the php part would be harder, but its the mysql part I'm struggling with.. Anyway... regarding the original issue... I have come this far after learning some join:

 

If I run:

SELECT trackers.id, tracked_data.tid
FROM trackers
LEFT JOIN tracked_data ON trackers.id = tracked_data.tid
WHERE trackers.uid = X
AND trackers.id =Y

 

Then I get a result and it selects the correct rows. But when I replace SELECT with DELETE like:

 

DELETE trackers.id, tracked_data.tid
FROM trackers
LEFT JOIN tracked_data ON trackers.id = tracked_data.tid
WHERE trackers.uid = X
AND trackers.id =Y

 

It says: #1109 - Unknown table 'id' in MULTI DELETE

 

So apparently select and delete can't be interchanges in this situation.. Any tips?

 

 

[alka]

Ok.. I sat down, took a deep breath and tried to understand the query... Then noticed something I'm not actually specifying properly what I want to delete

when I just replace select with delete.. so I did this:

 

DELETE trackers.*, tracked_data.* FROM trackers LEFT JOIN tracked_data ON trackers.id = tracked_data.tid WHERE trackers.uid = Y AND trackers.id = Y

 

It worked.. At least it deleted the proper trackers ID along with tracked data for the correct user.. Now I basically need a confirmation from someone who underrstands what I'm doing better than me.. rofl..