Left join, or?

[bayswater]

Hi,

 

I have a list of articles, for which it is supposed to be noted if an article is relevant for only a certain user group. I have three tables:

 

1. articles:

article_id (autonum) | article_name

 

2. userroles:

role_id (autonum) | role_name

 

3. context:

context_id (autonum) | context_article_id | context_role_id

 

As an example the article "PHP for beginners" (with article_id 3) is relevant for the user group "Students" (role_id 4) and "Teachers" (role_id 5) but NOT relevant for "Secretaries" (role_id 7)

 

 

In the database:

 

1. articles:

3 | PHP for beginners

 

2. userroles:

4 | Students

5 | Teachers

7 | Secretaries

 

3. context: (here is context_id set to 12 og 13 as an example)

12 | 3 | 4

13 | 3 | 5

 

My problem is that when editing a article - for example to change the relevant user groups I can't get the SQL to work. It should list ALL the exisisting user roles to chose from, and mark the ones (in a check box) that are selected for the specific/chosen article.

 

 

My attempt:

 

$sql_roles = mysql_query("SELECT DISTINCT r.role_id, r.role_name, c.context_id, c.context_article_id, c.context_role_id, a.article_id 

    FROM (userroles r LEFT JOIN context c ON c.context_role_id = r.role_id)

    LEFT JOIN articles a ON a.article_id = c.context_sc_id

   

    ORDER BY r.role_name asc");

 

 

But this lists some roles that are used by another article more than once (role_id in ()):

 

PICKED (5) Teachers

(5) Teachers

(7) Secretaries

( Super Users

(4) Students

PICKED (4) Students

 

 

How should my SQL look to accomplish this?

[RussellReal]

you could do this two ways, and possibly the best way first and foremost would be simply to do:

 

SELECT role_id As id, role_name As name FROM userroles ORDER BY asc

 

then do

 

SELECT context_artical_id As aid, context_role_id As rid FROM context WHERE context_artical_id = 3

 

and you'll have all the information you need, you need to understand how mysql gets its results.. for every JOIN you're effectively calling a table scan (table scans aren't too bad, if you set indexs properly), these two functions calls only 2 table scans.. vs 1 tablescan overall + 1 tablescan per row per join..

 

but if you really wanted to use JOINs

 

you could do this:

 

SELECT 
a.role_id As id,
a.role_name As name,
IF(b.context_id,1,0) As selected
FROM userroles a
LEFT JOIN context b ON (b.context_role_id = a.role_id AND b.context_article_id = 3)

[bayswater]

Thanks Russell,

 

I'm going with your last suggestion I'm afraid. It's a small site, not many users etc. and it works beautifully. Thank you very much!

 

[RussellReal]

any time!

[bayswater]

Just out of curiosity - and I guess it would be nice to actually understand this instead of merely copying a code  - your first suggestion - with the two selects, how would that look. Below is my actual SQL (I used the article example in my first post because it was easier to explain).

 

Do you make 2 $sql_roles = mysql_query(" ? Because it would need variables from both in the latter while?

 

 

 

<?php 

    $sql_roles = mysql_query("SELECT 
    a.role_id As role_id,
    a.role_name_dk As role_name_dk,
    a.role_name_en As role_name_en,
    b.context_sc_id As context_sc_id,
    IF(b.context_id,1,0) As selected
    FROM elsa_sc_roles a
    LEFT JOIN elsa_sc_context b ON (b.context_role_id = a.role_id AND b.context_sc_id = '".$edit_sc_id."')
    
    ORDER BY a.role_name_dk asc");

while ($row_roles = mysql_fetch_array($sql_roles))
    {
    $role_id = $row_roles['role_id'];
    $role_name_dk = $row_roles['role_name_dk'];
    $role_name_en = $row_roles['role_name_en'];
    $context_sc_id = $row_roles['context_sc_id'];
    
?>
  <input type="checkbox" name="sc_context[]" id="sc_context" value="<?php echo $role_id; ?>" <?php if ($edit_sc_id == $context_sc_id) echo "checked"; ?>> <?php echo $role_name_dk; ?> <span class='faded'>(En: <?php echo $role_name_en; ?>)</span><br>
<?php
}
?> 

[RussellReal]

Well, firstly, lets explain your chosen method

 

SELECT *
FROM elsa_sc_roles a
LEFT JOIN elsa_sc_context b ON (b.context_role_id = a.role_id AND b.context_sc_id = '".$edit_sc_id."')

 

SELECT

I'm sure you know what it means

FROM elsa_sc_roles

  I guess you can count as "required tables", although its not all that simple, but lets move on

LEFT JOIN elsa_sc_context

ok, now lets explain this, left join basically means that "elsa_sc_context" is now to the right of "elsa_sc_roles", and that, it will not fail to successfully pull the collected data, so long as the >>LEFT TABLE<< has all the required data

 

if the LEFT JOIN doesn't return any data, all the fields from that table are filled with NULL, now, we can use that, because in mysql NULL is false, so:

 

IF(elsa_sc_context.context_id,1,0)

 

basically means if "elsa_sc_context.context_id" is true (has a value), return 1 (bool:true) if it is FALSE(null or 0)then return 0 (bool:false)

 

but, everytime you hit an elsa_sc_roles ROW, you will be scanning elsa_sc_context for a row that matches the ON conditions.. E.G. role_id = role_id, if at the END OF THE TABLE, there is no matches, all of the values will be filled in with false(0)

 

----------------------------------------------------------------------------------------------------------------------------

 

Here is how the two queries would have looked in your script:

 

<?php
$a = mysql_query("SELECT role_id, role_name_en, role_name_dk FROM elsa_sc_roles ORDER BY asc");

$edit_sc_id = (int) $edit_sc_id;
$b = mysql_query("SELECT context_sc_id, context_role_id FROM elsa_sc_context WHERE context_sc_id =  '".$edit_sc_id."'");

$roles = array();
/* pull all the roles */
while ($row = mysql_fetch_assoc($a)) {
	$row['selected'] = false; // create a new property
	$roles["_{$row['role_id']}"] = $row;
}
/* now loop through the context rows */
while ($row = mysql_fetch_assoc($b)) {
	$roles["_{$row['context_role_id']}"]['selected'] = true; // set this role as selected
}

var_dump($roles);
?>

 

It might look a little longer, but.. you take it to a larger script, the benchmark will most likely be much better..

[bayswater]

Hi Russ,

 

Once again - thank you very much for your assistance. I'm trying to build a library of screencasts and adding/editing the roles of the individual screencasts are working fine thanks to your script. But I can't seem to get it working in the list that shows me all my screencasts, like:

 

- screencast name 1 (only for "teachers", "students")

- screencast name 2 (only for "Secretaries")

- screencast name 3

- screencast name 4 (only for "teachers", "Secretaries")

 

Can you spot how to include - some of - your code to my sql? This includes some categories and the different tools, the screencasts are made for.

 

// SHOW ALL RECORDS ACCORDING TO LANGUAGE (NOT A SEARCH)
    $sql_screencasts = "SELECT t.tool_id, t.tool_name, c.cat_id, c.cat_name_".$lang.", s.sc_id, s.sc_desc_".$lang.", s.sc_name, sc_stats    
FROM elsa_sc_tools t
JOIN elsa_sc_categories c ON c.cat_rel_to_tool = t.tool_id
JOIN elsa_sc_screencasts s ON s.sc_rel_to_cat = c.cat_id
ORDER BY t.tool_name asc,c.cat_name_".$lang." asc,s.sc_desc_".$lang." asc";
    $searching = "false";
    $result = mysql_query($sql_screencasts);
    $count_rows = mysql_num_rows($result);