[SOLVED] Use result from first query in a second query

[cyberbob1uk]

Hello people,

 

Brand new to the forum, signed up through shear desperation 

 

I am designing a website for a trainspotter and I am having a slight problem.

 

(problem page: http://datarail.info/class_search_exec.php?a=37)

 

I have a few tables already made but the ones concerned are 'locomotives' and 'userhaulage'.

 

The 'locomotives' table returns all locomotives of a certain type (selected by user) along with any other details in the 'locomotives' table.

 

The 'userhaulage' contains the number of the locomotive and the username of each user that has been pulled by each locomotive on separate rows.  eg:

 

Locomotive    Username

11111            Dave

12222            Steve

12222            Dave

11112          Steve

 

If the locomotive number in 'userhaulages' matches the locomotive number in 'locomotives' the first column of the table on the page (above), needs to change to a green backrground.

 

So far, there are 3 different locomotive number's for the same user in the 'userhaulages' table, but only one of the H's in the first column is green (locomotive number 37402). 

 

What am I doing wrong?? 

 

My php code for the whole page is:

 

<?
include_once('config.php');
$class="$_GET[a]";
echo "<table>";
echo "<col width=3><col width=3><col width=30><col width=30><col width=30><col width=30><col width=30><col width=10><col width=10>";
$sql="SELECT * FROM users WHERE ipaddress='$ip'";
$result=mysql_query($sql) OR DIE (mysql_error ( ) );
if ($result)
while($row=mysql_fetch_array($result)) {
$username=$row['username'];
$ipnumber=$row['ipaddress'];
}
if ($ip==$ipnumber) {
$sql="SELECT number FROM userhaulage WHERE username='$username' GROUP BY username ";
$result1=mysql_query($sql) OR DIE (mysql_error ( ) );
while($row=mysql_fetch_array($result1)) {
$haulage=$row['number'];

}


$sql="SELECT * FROM userphoto WHERE username='$username'";
$result=mysql_query($sql) OR DIE (mysql_error ( ) );
if ($result)
while($row=mysql_fetch_array($result)) {
$photo=$row['number'];
}

$sql="SELECT * FROM locomotives WHERE class='$class' ORDER BY cnumber ASC";
$result=mysql_query($sql) OR DIE (mysql_error ( ) );
if($result)
while($row = mysql_fetch_array($result)){
$current=$row['cnumber'];
$dnum=$row['dnumber'];
$pnum1=$row['prenum1'];
$pnum2=$row['prenum2'];
$status=$row['status'];
$pool=$row['pool'];
$livery=$row['livery'];
$depot=$row['depot'];


if ($haulage==$current){
echo "<tr><td class=\"green\"><b>H</b></td>"; }
else if ($pnum1==$haulage){
echo "<tr><td class=\"green\"><b>H</b></td>"; }
else {
echo "<tr><td class=\"red\"><a href='addhaulage.php?loco=$current'><b>H</b></a></td>"; }




if ($photo==$current or $photo==$pnum1 or $photo==$pnum2) {
echo "<td class=\"green\"><b>P</b></td>"; }
else {
echo "<td class=\"red\"><b>P</b></td>"; }


echo "<td class=\"grey\"><b>$current</b></td><td class=\"grey\">(D$dnum)</td>"; 
if (empty($pnum1)) {
echo "<td class=\"grey\"></td>"; }
else {
echo "<td class=\"grey\"><i>$pnum1</i></td>"; }
if (empty($pnum2)) {
echo "<td class=\"grey\"></td>"; }
else {
echo "<td class=\"grey\"><i>$pnum2</i></td>"; }
echo "<td class=\"grey2\">$pool</td><td class=\"grey2\">$livery</td><td class=\"grey2\">$depot</td>";



if ($status==X) {
echo "<td class=\"midgrey\">Scrapped</td>"; }
else if ($status==P) {
echo "<td class=\"midgrey\">Preserved/Privately Owned</td>"; }
else if ($status==M) {
echo "<td class=\"midgrey\">In Existance</td>";
}}}
else {
echo "You need to register before using the listings"; }
?>

Please ignore the column with P's in it as if a solution to my problem with the first column is found, it will also solve this column

 

 

 

 

[luca200]

Do you really think we all have to register to your site to bring you some solution?

[fenway]

Way too many queries... narrow it down for us...

[cyberbob1uk]

First of all, I appologise for leaving the little bit of code in that checks ip numbers and doesnt let you view if its not in the database.  This line has now been removed.

 

Ive also shortened the code to include the queries that are involved with my question above.

 

//Username is fetched from a result of previos query and works fine
$username

//I want to select every row in 'userhaulage' where the username matches $username and return it in variable $haulage for use on next query.
$sql="SELECT number FROM userhaulage WHERE username='$username' GROUP BY username ";
$result1=mysql_query($sql) OR DIE (mysql_error ( ) );
while($row=mysql_fetch_array($result1)) {
$haulage=$row['number'];

}

//This query produces the page linked in the initial post

$sql="SELECT * FROM locomotives WHERE class='$class' ORDER BY cnumber ASC";
$result=mysql_query($sql) OR DIE (mysql_error ( ) );
if($result)
while($row = mysql_fetch_array($result)){
$current=$row['cnumber'];
$dnum=$row['dnumber'];
$pnum1=$row['prenum1'];
$pnum2=$row['prenum2'];
$status=$row['status'];
$pool=$row['pool'];
$livery=$row['livery'];
$depot=$row['depot'];

//If $haulage (above), matches either $current (current locomotive Number), or $pnum1 or $pnum2 (previous locomotive numbers for same locomotive), the first colum in the table turn from red to green.  Its only working for 1 locomotive number and not the three

if ($haulage==$current or $haulage==$pnum1 or $haulage==$pnum2){
echo "<tr><td class=\"green\"><b>H</b></td>"; }
else {
echo "<tr><td class=\"red\"><a href='addhaulage.php?loco=$current'><b>H</b></a></td>"; }


Hope this helps

[rarebit]

if i see right...

while($row=mysql_fetch_array($result1)) {
$haulage=$row['number'];
}

this only gets a single element (the last) and therefore wont be able to be used match multiple entries?

[cyberbob1uk]

Any idea what i'd have to replace it with to match multiple entries??

[rarebit]

a

$haulage = array();
while($row=mysql_fetch_array($result1)) {
$haulage[]=$row['number'];
}

 

b

if ( (in_array($current, $haulage)) || (in_array($pnum1, $haulage)) || (in_array($pnum2, $haulage)) ) {

 

or something like that?

[rarebit]

mmm, you could do it in a single mysql call using some kind of join...

 

$s = "CREATE TABLE userhaulage (number as int, username as varchar(32) )";
mysql_query($s, $conn);
$s = "INSERT INTO userhaulage VALUES ('11111', 'Dave'), ('12222', 'Dave'), ('11111', 'Steve'), ('11112', 'Steve') ";
$res = mysql_query($s, $conn) or die(mysql_error());


$s = "CREATE TABLE locomotives (class, cnumber, dnumber, prenum1, prenum2, status, pool, livery, depot)";
mysql_query($s, $conn);
$s = "INSERT INTO locomotives VALUES ('upper', '0', '0', '0', '0', '0', '0', '0', '0', '0') ";
$res = mysql_query($s, $conn) or die(mysql_error());

 

if you want to give a dataset for locamotives i'll give it a go...

[cyberbob1uk]

[move]THANK YOU RAREBIT[/move]

 

I used what you suggested below along with a simple JOIN.  At first it failed but then realised I had a GROUP BY on the query which JOINs the tables.  This removed and a little tweaking of the query all works fine

 

THANK YOU !!!

 

a

$haulage = array();
while($row=mysql_fetch_array($result1)) {
$haulage[]=$row['number'];
}

 

b

if ( (in_array($current, $haulage)) || (in_array($pnum1, $haulage)) || (in_array($pnum2, $haulage)) ) {

 

or something like that?