使用JOIN删除冗余SQL查询（在PHP中）

I am trying to clean up my SQL queries and use JOIN in just ONE where I once used TWO queries.

Here is former code (in PHP):

$cat = "books";  // as a test 

$query = "SELECT category, cat_id FROM master_cat WHERE category = '{$cat}'";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
    $cat_id = $row['cat_id'];
}

$sql = mysql_query("SELECT item, spam, cat_id FROM items WHERE cat_id = ' " . $cat_id . "' ORDER BY TRIM(LEADING 'The ' FROM item) ASC;");  

while ($row = mysql_fetch_assoc($sql))
    if ($row['spam'] < 2)
        $output[] = $row;


print(json_encode($output));  // added!

I am trying to just remove the top query and use a JOIN. The updated SQL statement is this:

EDIT: I made a mistake in original question. Basically user input gives us $cat = "something". There is "something" in master_cat table with a cat_id. That cat_id is also in the items table. That is where I need the tables to connect -- and the WHERE clause needs to incorporate "$cat"

UPDATED QUERY:

    $result = mysql_query("SELECT i.item, i.spam, mc.cat_id AS Category
FROM items as i
INNER JOIN master_cat as mc
ON i.cat_id = mc.cat_id
WHERE i.cat_id = '{$cat}'
ORDER BY TRIM(LEADING 'The ' FROM i.item) ASC;");

then:

   while ($row = mysql_fetch_assoc($result))
    if ($row['spam'] < 2)
        $output[] = $row;

I receive this in the browser: null.

Can someone guide me on how to properly use JOIN which I know will REALLY clean things up here and make more efficient coding. I just watched a tutorials but still am not quite getting it.

HERE IS FINAL CODE THAT WORKS

$cat = $_POST['category']; // yes, yes, injection.  this is just the short version.

$result = mysql_query("SELECT i.item, i.cat_id, i.spam,  mc.cat_id, mc.category, TRIM(LEADING 'The ' FROM i.item) as CleanItem
FROM items as i
INNER JOIN master_cat as mc
ON i.cat_id = mc.cat_id
WHERE mc.category = '{$cat}'
ORDER BY CleanItem ASC;");

while ($row = mysql_fetch_assoc($result))
    if ($row['spam'] < 2)
        $output[] = $row;

You have reference the items table by its full name in the WHERE clause, you should be using the alias you created (i).

You also have an ambiguous column reference item in your ORDER BY clause.

Try changing the last two lines to:

WHERE i.cat_id = '{$cat_id}'
ORDER BY TRIM(LEADING 'The ' FROM i.item) ASC

You should also inspect mysql_error() to get a string description of the error, which would have pointed you straight to the problem.

Try this one:

$sql = "SELECT i.item, i.spam, mc.cat_id AS Category
FROM items as i
INNER JOIN master_cat as mc ON i.cat_id = mc.cat_id
WHERE i.cat_id = '{$cat_id}'
ORDER BY TRIM(LEADING 'The ' FROM item) ASC";

If you alias items as i, you should use i everywhere else. Besides, mc.category does not seem to exist so I replaced it with mc.cat_id. What does mysql_error say?

$sql = "SELECT i.item, i.spam, mc.cat_id AS Category
FROM items as i
INNER JOIN master_cat as mc
ON i.cat_id = mc.category
WHERE items.cat_id = '{$cat_id}'
ORDER BY TRIM(LEADING 'The ' FROM item) ASC";

Since you aliased it you have to keep it as aliased: fix the second to last line:

WHERE i.cat_id = '{$cat_id}'

You are probably getting false as your response to the initial query and not a result set

You should join on i.cat_id = mc.cat_id

You should also probably perform your i.item cleaning (i.e. removing 'The ') in the select statement (even as a seperate field if you need to keep i.item intact) and then order by that field.

You should reference i.cat_id = '{$cat_id]}', not items.cat_id