Jump to content

SUBSTRING with joined table


Recommended Posts



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

<?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 advice would be very much appreciated.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...