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.


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):

        while($row = mysqli_fetch_array($result1)) {


          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.

3 Years
Discussion Span
Last Post by Borderline

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'];
Votes + Comments
Perfect result, many thanks!
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.