[SOLVED] Need help with query

[m4x3vo]

This query loops through numbers 1-139 and tries to look through the table user_ach for the numbers (ach_id) that the person is missing.  It takes the missing numbers and looks through the table ach and displays the info about that id. 

 

Now, what I want to do is order the categories that are displayed in the html table.  Each achievement has its own name, method, and cateogory.  I have a table called cat_order that contains the name of each cat and its id.  I want to order each cat by its id.  But the cat is in a different table so I need help incorporating the order by in this script.

echo "<form name='input' action='insert.php' method='get'><font size='1'><table border='0' width='650' cellspacing='10' cellpadding='10'><table border='0' width='650' cellspacing='10' cellpadding='10'><tr><td>Image</td><td>Method</td><td>Points</td><td>Category</td><td>Status</td><td>Checkbox</td></tr>";
$cookie = $_COOKIE["uid"];
for ($counter = 1; $counter <= 139; $counter++)
{
$query = "SELECT * FROM user_ach WHERE user_id = $cookie AND ach_id = $counter";
$result1 = mysql_query($query);
$res = mysql_fetch_assoc($result1);
$ach_id = $res['ach_id'];
if($ach_id == null) {
$query2 = "SELECT * FROM ach WHERE id = $counter";
$result2 = mysql_query($query2);
$res = mysql_fetch_assoc($result2);
echo "<tr><td><img src='$res[image]'></img><br>$res[name]</td><td>$res[method]</td><td>$res[points]</td><td>$res[cat]</td><td>$res[status]</td><td><input type='checkbox' name='options[$res[id]]' value=1></td></tr>";
echo "<br>";
}
}

[mentalist]

Here's a quick guess..

$query = "SELECT * FROM user_ach  LEFT JOIN cat_order on user_ach.cat = cat_order.cat WHERE user_id = $cookie AND ach_id = $counter ORDER BY cat_order.id";

[m4x3vo]

The html table loads seeming like it works, but the data is still not orderd .  I need a way to get the query outside the loop :S.

[fenway]

The html table loads seeming like it works, but the data is still not orderd .  I need a way to get the query outside the loop :S.

 

Say what?

[fenway]

There is no "after" -- join them to begin with.

 

SELECT a.*, ua.*

FROM user_ach AS ua

inner join ach AS a ON ( a.id = ua.ach_id )

LEFT JOIN cat_order AS co on ( a.name = co.cat_name )

WHERE ua.user_id = $cookie AND ua.ach_id = $counter

ORDER BY co.cat_num

[m4x3vo]

Are you sure something like that would work within a loop o.o?

[kickstart]

Hi

 

Not real need to loop through the 139 fields seperatly.

 

You can do something like this (not tested so might well be a typo):-

 

SELECT * 
FROM ach z
JOIN (
SELECT a.NumRange FROM (
SELECT a.i *100 + b.i *10 + c.i AS NumRange
FROM `integers` a, integers b, integers c
HAVING NumRange BETWEEN 0 AND 139 ) a
LEFT OUTER JOIN user_ach b
ON a.NumRange = b.ach_id
AND b.user_id = $cookie
WHERE b.ach_id IS NULL) y
ON z.id = y.NumRange

 

This relies on having a table called integers with a single integer column called i, with 10 rows with the values from 0 to 9. It uses this to generate every integer between 0 and 999, then uses the HAVING clause to limit the range to 1 to 139. Then does a LEFT OUTER JOIN of this list of numbers against the id fields for that user on user_ach, and only checks for NULL to just bring back the numbers that are not already on user_ach for that particular user. Then JOINS that list of unused numbers with the records on ach.

 

All the best

 

Keith

[kickstart]

Hi

 

Or rather simpler, assuming the full range of 1-139 achievements is in the ach table and nothing more.

 

SELECT *
FROM ach a
LEFT OUTER JOIN user_ach b
ON a.id = b.ach_id
AND b.user_id = $cookie
WHERE b.ach_id IS NULL

 

All the best

 

Keith

[fenway]

Are you sure something like that would work within a loop o.o?

That was instead of the loop.