Mysql query problem in my php script

PHP
1

I have some mysql queries in my php script and like anything else there are problems :slight_smile:


    $query = mysql_query("
select reports.report_id as rid
     , reports.report_uid as ruid
     , reports.report_army as army
     , reports.report_vsarmy as vsarmy
     , reports.report_result as rresult
     , reports.report_date as rdate          
     , users.uid as uid
     , users.name as uname
     , army.a_id as aid           
     , army.a_name as aname       
  from reports
inner join users on users.uid = reports.report_uid
inner join army on army.a_id = reports.report_army order by reports.report_id DESC LIMIT 10") or die("SELECT Error [$query]:".mysql_error());

When I print those variables (see below) I get one of them incorrectly


echo "<tr>

      <td class=\\"alt2\\" width=\\"5%\\" valign=\\"top\\"><a href=\\"reports.php?rid=".$row->rid."\\"><img src=\\"images/readmore.gif\\" border=\\"0\\" alt=\\"Read More\\"></a></td>

      <td class=\\"alt2\\" width=\\"30%\\" valign=\\"top\\">".$row->aname."</td>

      <td class=\\"alt2\\" width=\\"30%\\" valign=\\"top\\">".$row->vsarmy."</td>      

      <td class=\\"alt1\\" width=\\"30%\\" valign=\\"top\\"><a href=\\"profile.php?uid=".$row->uid."\\">".$row->uname."</a></td>

      <td class=\\"alt2\\" width=\\"5%\\" valign=\\"top\\"><img src=\\"".$row->rresult.".gif\\"></td>    

    </tr>";    

    }

the $row->vsarmy appears as the id # instead of the army name. Im not very good at php and mysql queries so I would appreciate some help so I can move along to the next problem :slight_smile:

Thanks for reading!

-Roger

2

Noone knows your table structure and even purpose and type of vsarmy column

3

I have one table with all the armies listed with names and ID, another table is used for reports that contains the ID’s for the armies. When I print the reports I want to turn the ID# into the name taken from the army table.

4

You have army name in the aname variable

5

yes, but how can I get that into the vsarmy variable too without making duplicate ?

6

but why do you want two variables with same value?

7

what I want is to have the name for the “attacking” army appear (as it does now) but I also want the name of the “defending” army to appear which is does not. Only the army ID appers cause it most likely dont know the name cause its not connected to the army table if this makes sense ? :slight_smile:

8

makes sense
you didn’t assign it in your query, I guess, the way aname were assigned

9

yes, but Im not sure how to edit the query to make it work. I tried a few times, but I got some duplicate errors

10 
SELECT reports.report_id AS rid
     , reports.report_uid AS ruid
     , reports.report_army AS army
     , reports.report_vsarmy AS vsarmy
     , reports.report_result AS rresult
     , reports.report_date AS rdate
     , users.uid AS uid
     , users.name AS uname
     , [COLOR="Blue"]attacking.a_id   AS attacking_aid
     , attacking.a_name AS attacking_aname[/COLOR]
     , [COLOR="Red"]defending.a_id   AS defending_aid
     , defending.a_name AS defending_aname[/COLOR]
  FROM reports
INNER
  JOIN users
    ON users.uid = reports.report_uid
[COLOR="blue"]INNER
  JOIN army AS attacking
    ON attacking.a_id = reports.report_army[/COLOR]
[COLOR="red"]INNER
  JOIN army AS defending
    ON defending.a_id = reports.report_vsarmy[/COLOR]
ORDER
    BY reports.report_id DESC LIMIT 10
11

Thanks for the help. That works perfectly :slight_smile: