Need help with a weird search...

[Anon]

I have a situation where I need to determine which numbers in a sequence has been used,
as well as get the id of the record where those numbers have been used.

I have a table with several fields, the two that I'm concerned with are as follows:
id int(6) ; just the record id number, and
priority tinyint(2); which has the integer values of 1 thru 10.

Each record will have one and only one priority assigned to it.

I want to find which priorities have been assigned and the id of the record associated with it, as well as which
priorities have not yet been assigned.

Can anyone help?

[Anon]

can you use more than one query?

[Anon]

Sure.

--->Rob

Robert Treat wrote:

can you use more than one query?

[Anon]

Hrmm.. this is how I would do it.

$all_priorities = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10");
$query = "SELECT recordid, priority FROM table ORDER BY ";
$res = mysql_query($query);

while ($row = mysql_fetch_array($res)) {
$used_priorities[$row["recordid"]] = $row["priority"];
$records[$row["priority"]] = $row["recordid"];
}

$unused_priorities = array_diff($all_priorities, $used_priorities);

Now, you have any array all the priorities used, unused, and array to lookup which ID is used with what priority. I think thats what you were looking for? Dunno if it helped 😉 If you need something different, give a little more detail and I, as well as others, will be happy to help.

Chris King

[Anon]

ive come to the conclusion I dont think I understand what your after, but heres soem code that might help. (note its 1/2 psuedo code)

$row=1;
// sql stuff
// select * from table orderby priority;
// get the resultset

for ($x=1;$x<11;$x++){
list ($id,$priority) = resultset($row);
if ($priority == $x){
$row = $row +1;
echo "$id : $x";
} else {
echo "N/A : $x";
}
}

[Anon]

Sorry for the confusion, I was kind of frazzled when I wrote the original post.

What I'm building is a table that tracks tasks for facilities in various cities and prioritizes them on a scale of 1 to 10 (1 being the highest priority). Since there can only be a single priority of any level in any one city (ie New York can only have 10 tasks in queue and only one of them can be priority 1, one task with a priotiy of 2, etc.), I need to know which priorities have not been 'used' or assigned.

When a data entry person enters a new task, I want to check to see which priorities have been used, to which task (identified by id) that priority was assigned, and to also show which priorities have not been used. The data entry person will then be able to select from the priorities that have not been used. By identifying the 'used' priorities, the peron can decide whether their task should have a higher priority than what is available or accept the next in the queue.

I know this sounds kind of confusing but it should be more informative than what I posted before.

$result=mysql_query("SELECT id, city, priority FROM expedite
WHERE city='New York'
ORDER by priority", $db);

will give me the listing of all the priorities USED in that city, but not the unused priorities.

$result=mysql_query("SELECT id, city, priority FROM expedite
WHERE priority IS NOT NULL
AND city='New York' ", $db);

These two queries will show me all of the priorities that have been used, and their id's, but it will not show me what is left.

It's simple to figure out if the priorities get used in order (which they should be) by just incrementing the last one used sequentially until my total is 10. BUT, if for some reason they get assigned out of sequence, how do I figure that out?

I'm having a brain fart and can't figure out how to determine which priorities are NOT in that list.

Thanks for your help!

--->Rob

[Anon]

read back through both Chris and mines replies (since maybe we did understand what you were talking about) I overcame this by doing an 'order by' in the SQL, that way you do get them in order, and you can check each one sequentially.

[Anon]

Thanks Chris and Robert! With both of your code suggestions and ideas, I resolved it. It took me a bit longer than it should have as I wasn't familiar with array_diff() and it isn't in the version of manual on this site but is on www.php.net.

This script will list all used and unused priorities, even if they are not in sequential order; ie 1, 3, 5, 8, 10 are used and 2, 4, 6, 7, 9 are not. The snippet that used the list() function wouldn't work right when the numbers weren't in sequence.

Here is the code I used to get the basics and, now that it works, I will add the rest for the various city information:

<?php
require("./include/sql-header.inc");

$all_priorities = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10");
$query = "SELECT id, priority FROM expedite ORDER BY priority";
$result = mysql_query($query);

echo "<table border=1 cellpadding=5><tr>\n
<td>These Priorities have already been used: ";

if ($row = mysql_fetch_array($result)) {
do {
$used_priorities[$row["id"]] = $row["priority"];
printf("<a href=\"./review.php?id=%s\">%s</a> \n", $row[id],$row[priority]);
$records[$row["priority"]] = $row["id"];
} while ($row = mysql_fetch_array($result));
}

echo "</td><td> These are available for use: ";

$unused_priorities = array_diff($all_priorities, $used_priorities);

for ($x=0;$x<11;$x++) {
if ( $unused_priorities[$x] ) {
echo " $unused_priorities[$x] ";
}
}

echo "</td></tr></table>";
?>

Thanks again!

--->Rob