Member Avatar for Borderline

Afternoon

I set up a substring on the raceid column of my database; the raceid column consists of the season+individual race number, to produce a unique id number. Image of the database below - my code was doing what I expected (hoped for?!) and outputting the four digit season.

http://www.arabianraceform.co.uk/form/horses/dbimg.jpg

However, I've since done an inner join on the two tables, and the season is now missing from the output i.e. there's just a gap where I would expect the season to appear.

<?php $result1 = mysqli_query($con,"SELECT runners.raceid, SUBSTRING(runners.raceid,1,4), runners.horse, runners.age, 
                       runners.colour, runners.gender, runners.trainer, runners.t_link, runners.owner, runners.o_link,
                       horse.horse, horse.sire, horse.s_ctry, horse.dam, horse.d_ctry 
                  FROM         runners 
            INNER JOIN     horse 
                    ON     runners.horse = horse.horse
                 WHERE     runners.h_link = '01335.php'
              ORDER BY     runners.raceid DESC LIMIT 1");?>

The output (this is an include):

<?php
        while($row = mysqli_fetch_array($result1)) {

        include($_SERVER['DOCUMENT_ROOT'].'/inc/links.php');

          echo "Last raced at the age of ";       
          echo $row['age'];
          echo " during the ";  
          echo $row['SUBSTRING(raceid,1,4)'];
          echo " season.";      
          echo "</br>";        
          echo "</br>";
          echo " <b>Colour/Gender:</b>";  
          echo "</br>";        
          echo $row['colour'] . " " . $row['gender'];         
          echo "</br></br>";
          echo " <b>Trainer:</b>";    
          echo "</br>";  
          echo "<a href=".$trainerlink . ">" .$row['trainer'] . "</a>";   
          echo "</br></br>";
          echo " <b>Owner:</b>";  
          echo "</br>";  
          echo "<a href=".$ownerlink . ">" .$row['owner'] . "</a>";   
          echo "</br></br>";
          echo " <b>Sire:</b>";   
          echo "</br>";  
          echo "<a href=".$sirelink . ">" .$row['sire'] . "</a>"; 
          echo "</br></br>";
          echo " <b>Dam:</b>";    
          echo "</br>";  
          echo "<a href=".$damlink . ">" .$row['dam'] . "</a>";             
          }
    ?> 

Any assistance would be greatly appreciated.

The code on line 9 is wrong, it wont return what you expect. You could change your query a bit and ad AS to the substring expression in the query:

SELECT runners.raceid, SUBSTRING(runners.raceid,1,4) AS raceyear,...

and then change line 9 in the code to:

echo $row['raceyear'];
commented: Perfect result, many thanks! +3
Member Avatar for Borderline

Perfect, many thanks for the speedy reply!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.