Better way of writing this query?

[rondog]

Hi, I was wondering if their was a more efficient way of writing this query?...maybe into one query.

 

The script below works. What its doing is checking if the user that is logged in has access to the clip that is equal to $_GET['id']

 

so userA has access to clips.php?id=9, but doesnt not have access to clips.php?id=10..make sense? I jsut want to make this 1 query if even possible.

 

<?php
$binQuery 	= mysql_query("SELECT bin_id FROM clips WHERE id = '".mysql_real_escape_string($_GET['id'])."'");
$bin_id 	= mysql_fetch_array($binQuery);

$userQuery 	= mysql_query("SELECT user_id FROM bins WHERE id = '".$bin_id['bin_id']."'");
$user_id 	= mysql_fetch_array($userQuery);

if ($_SESSION['userid'] != $user_id['user_id'])
{
echo "you dont have access to that clip";
}
else
{
echo "show them clip stuff";
}
?>

 

[JAY6390]

$query = "SELECT bin_id 
FROM clips
WHERE clips.id = '".mysql_real_escape_string($_GET['id'])."' 
LEFT JOIN bins WHERE bins.id = clips.bin_id";
$result = mysql_query($query);
if(!$result) {
    //No results found code here
}else{
    $data = mysql_fetch_assoc($result);
    $user_id = $data['user_id'];

Untested, but something like that should get you started

[rondog]

I knew it was going to be something with joins so i think its on the right track. This seems to be returning no result ever time though. Also, Im not sure if you understood my question..then again I probably didnt explain it very clear.

 

I have a session called userid that holds that users id...each user can make these clip bins. Inside each bin they can add clips to it. What I want to avoid is having userA change the clip.php?id=## to some other number and then they are in a different users clip. Does that makes sense?

[pengu]

I knew it was going to be something with joins so i think its on the right track. This seems to be returning no result ever time though. Also, Im not sure if you understood my question..then again I probably didnt explain it very clear.

 

I have a session called userid that holds that users id...each user can make these clip bins. Inside each bin they can add clips to it. What I want to avoid is having userA change the clip.php?id=## to some other number and then they are in a different users clip. Does that makes sense?

 

Didn't make sense to me lol.

 

Can you show us the table structure.

[rondog]

Basically I am working from the clips table backwards..I need to ultimately get the user_id for the current clip that they are trying to open which is why I first select the bin_id...then I select the user_id based off the result of bin_id...I then test if $_SESSION['userid'] == is the user id I got back, show the clip else, thats not your clip.

 

bins

id user_id created title

1 1 2010-04-26 My first bin

2 1 2010-04-26 My Second bin

3 2 2010-04-26 Test User's First Bin

4 1 2010-04-26 Another test Bin

5 1 2010-04-26 My test Bin

6 1 2010-04-20 Testing Bin

7 1 2010-04-23 Buncha Clips

8 1 2010-04-25 S'more clips

 

and clips

id bin_id modified clip_in clip_out title description

1 1 2010-04-26 100 200 A clip title a short description

2 2 2010-04-26 100 200 A clip title a short description

3 2 2010-04-26 100 200 A clip title a short description

4 2 2010-04-26 100 200 A clip title a short description

5 2 2010-04-26 100 200 A clip title a short description

6 2 2010-04-26 100 200 A clip title a short description

7 2 2010-04-26 100 200 A clip title a short description

8 3 2010-04-26 100 200 A clip title 111 a short description

9 3 2010-04-26 100 200 A clip title 222 a short description

10 3 2010-04-26 100 200 A clip title 333 a short description

11 3 2010-04-26 100 200 A clip title 444 a short description

12 4 2010-04-26 100 200 A clip title a short description

13 5 2010-04-26 100 200 A clip title a short description

14 5 2010-04-26 100 200 A clip title a short description

15 5 2010-04-26 100 200 A clip title a short description

[rondog]

if ultimately I want to end up with the user_id and my query is going to use a join, I would do SELECT user_id FROM bins ?

[JAY6390]

Hi rondog

Try this code

$query = "SELECT
bins.user_id
FROM
bins
LEFT JOIN
clips
ON
clips.bin_id = bins.id
WHERE
clips.id = '%s'";
$result = mysql_query(sprintf($query, mysql_real_escape_string($_GET['id'])));
if (!$result) {
    die(mysql_error());
}
$user_id = mysql_fetch_array($userQuery);
if ($_SESSION['userid'] != $user_id['user_id']) {
    echo "you dont have access to that clip";
} else {
    echo "show them clip stuff";
}

Also be sure that you use session_start(); at the top of your script, or the session wont be active

[rondog]

that did it Jay, thanks! What is %s?

[JAY6390]

Excellent, no problem

[rondog]

hey sorry, I edited my post after I said thanks...what is %s do or mean?

[JAY6390]

%s is used with the sprintf() to replace areas with variables. It's just a neater way of putting variables into a string.

%s will be replaces with the $_GET['id'] variable

[rondog]

%s is used with the sprintf() to replace areas with variables. It's just a neater way of putting variables into a string.

%s will be replaces with the $_GET['id'] variable

 

Gotcha..I just checked out springf() and that makes sense..thanks again.

[JAY6390]

You're welcome