php - Single query in location tracking system -

php - Single query in location tracking system -

i have scheme keeps track of location of items.

example tables:

constb

consid cons 1 nint 2 ps 3 xb 4 @

consloctb

locid consid loc 1 1 gs 2 2 3 2 gg 4 1 ig 5 2 ss 6 3 sm 7 3 fb 8 1 ss 9 2 sm 10 3 11 4 sm

and result show current location of items. this:

nint - ss ps - sm xb -

the logic i'm thinking right nested loop.

$res=mysqli_query($con,"select * constb order id"); while($row=mysqli_fetch_array($res)){ $consid=$row["consid"]; $res2=mysqli_query($con,"select * consloctb consid='$consid' order locid desc limit 1"); while($row2=mysqli_fetch_array($res2)){ echo $row["cons"]." - ".$row2["loc"]; } /* end of sec loop */ } /* end of first loop */

this show results wanted.

and if add together search function, same nested loop process, gonna add together status in sec loop, if searched location same loc column.

$srchloc=$_post["search"]; /* info submitted */ $res=mysqli_query($con,"select * constb order id"); while($row=mysqli_fetch_array($res)){ $consid=$row["consid"]; $res2=mysqli_query($con,"select * consloctb consid='$consid' , loc='$srchloc' order locid desc limit 1"); while($row2=mysqli_fetch_array($res2)){ echo $row["cons"]." - ".$row2["loc"]; } /* end of sec loop */ } /* end of first loop */

example search, if user search items in sm, result show

ps - sm @ - sm

doing sec alternative think might slow search process. how can accomplish using single query? or making process faster?

if want list current location of items:

select t.cons, t.loc (select constb.consid id, constb.cons cons, consloctb.loc loc constb left bring together consloctb on constb.consid = consloctb.consid order constb.consid, consloctb.locid desc) t grouping t.id;

if want result filtered searched location, can add together having clause:

select t.cons, t.loc (select constb.consid id, constb.cons cons, consloctb.loc loc constb left bring together consloctb on constb.consid = consloctb.consid order constb.consid, consloctb.locid desc) t grouping t.id having t.loc='sm';

php mysql nested-loops