php function to get data from foreign key

[bickyz]

Hi I am trying to create a php page that pulls data from MSSQL table. Following is the code I am using:

<?php  
$objConnect = mssql_connect("localhost","sa","");  
$objDB = mssql_select_db("mydatabase");  
$strSQL = "SELECT * FROM tblPC ORDER BY pc_name ASC";  
$objQuery = mssql_query($strSQL) or die ("Error Query [".$strSQL."]");  
$Num_Rows = mssql_num_rows($objQuery);  
  
$Per_Page = 20;   // Per Page  
  
$Page = $_GET["Page"]; 
if(!$_GET["Page"])  
{  
$Page=1;  
}  
  
$Prev_Page = $Page-1;  
$Next_Page = $Page+1;  
  
$Page_Start = (($Per_Page*$Page)-$Per_Page);  
if($Num_Rows<=$Per_Page)  
{  
$Num_Pages =1;  
}  
else if(($Num_Rows % $Per_Page)==0)  
{  
$Num_Pages =($Num_Rows/$Per_Page) ;  
}  
else  
{  
$Num_Pages =($Num_Rows/$Per_Page)+1;  
$Num_Pages = (int)$Num_Pages;  
}  
$Page_End = $Per_Page * $Page;  
IF ($Page_End > $Num_Rows)  
{  
$Page_End = $Num_Rows;  
}  
  
?>  
<table width="600" border="1">  
<tr>  
<th width="20%">PC ID</th>
<th width="40%">PC Name</th>  
<th width="20%">Make</th>  
<th width="20%">Room</th>   
</tr>  
<?php  
for($i=$Page_Start;$i<$Page_End;$i++)  
{  
?>  
<tr>  
<td><?=mssql_result($objQuery,$i,"pc_id");?></td>  
<td><?=mssql_result($objQuery,$i,"pc_name");?></td>  
<td><?=mssql_result($objQuery,$i,"makemodel");?></td>  
<td><?=mssql_result($objQuery,$i,"room_id");?></td>
</tr>  
<?php  
}  
?>  
</table>  
  
<br>  
Total <?= $Num_Rows;?> Record : <?=$Num_Pages;?> Page :  
<?php  
if($Prev_Page)  
{  
echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$Prev_Page'><< Back</a> ";  
}  
  
for($i=1; $i<=$Num_Pages; $i++){  
if($i != $Page)  
{  
echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i'>$i</a> ]";  
}  
else  
{  
echo "<b> $i </b>";  
}  
}  
if($Page!=$Num_Pages)  
{  
echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Next_Page'>Next>></a> ";  
}  
mssql_close($objConnect);  
?>

tblPC

pc_id
pc_name
makemodel
room_id

 

tblROOM
room_id

room_name

building

 

The room_id field on tblPC and tblRoom are in database relationship.

 

I have following function that obtains room name from tblROOM. How can I use this function in the above page so that room_id on <td><?=mssql_result($objQuery,$i,"room_id");?></td> will display the room name instead of the id.

function ObtainRoomName($identifier)
{
	global $objDB, $objConnect;
	mssql_select_db($objDB, $objConnect);
	$query_rsfunction = sprintf("SELECT tblROOM.room_name FROM tblROOM WHERE tblROOM.room_id = %s", $identifier);
	$rsfunction = mssql_query($query_rsfunction, $objConnect) or die("Couldn't connect to SQL Server on $objDB");
	$row_rsfunction = mssql_fetch_assoc($rsfunction);
	$totalRows_rsfunction = mssql_num_rows($rsfunction);
	return $row_rsfunction['room_name'];
	mssql_free_result($rsfunction);
}

I have use this functuin with my other project by using <?php echo ObtainRoomName($row_rs1['room_id']); ?> but here Im lost with <?=mssql_result($objQuery,$i,"room_id");?>

 

Any help would be much appreciated, thank you.

[Barand]

The last thing you want to do is query one table then, as you process that table's records, run a separate query on each record. Do not run queries in loops, use a JOIN instead.

 

You first query should be

SELECT p.pc_id, p.pc_name, p.makemodel, r.room_name, r.building
FROM tblpc as p
    INNER JOIN tblroom as r USING (room_id)
ORDER BY p.pc_name

By using a join it finds the matching room record using the room_id and you can get the room name and building in the one query

[bickyz]

hi barand, thank you for looking at it.

The above query throws following error

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'USING'.

I am using MS SQL server.

[Barand]

Sorry, I forgot you were usinf MSSQL and not MySQL.  Use

SELECT p.pc_id, p.pc_name, p.makemodel, r.room_name, r.building
FROM tblpc as p
    INNER JOIN tblroom as r ON p.room_id = r.room_id
ORDER BY p.pc_name

[bickyz]

thank you very much Barand