I am new to php/coding, please help someone...

 

I have trouble using php to display the corresponding name given the ID from another table.

• I have agent_id from the job table.
  
• I need agent_name from the agent table.
  

I can write the SQL, but how do I use PHP to find this? I'm not sure how to use mysql_result function.

 

This is my code so far....

 

							   <?PHP

	  // ==========================================
	  // RUN SQL QUERY
	  // ==========================================
	  $query="SELECT * FROM job ORDER BY job_date_created DESC LIMIT 0, 2";
	  $result=mysql_query($query);
	  $num=mysql_numrows($result);

	  // ==========================================
	  // DISPLAY RESULTS
	  // ==========================================
	  $i=0;
	  while ($i < $num)
	  {
		  $jobId = mysql_result($result,$i,"job_id");
		  $jobDateCreated = mysql_result($result,$i,"job_date_created");
		  $jobDateCreated = substr("$jobDateCreated", 0, 10);  // keep first 10 characters only
		  $jobTitle = mysql_result($result,$i,"job_title");
		  $jobDateExpires = mysql_result($result,$i,"job_date_expires");
		  $agentId = mysql_result($result,$i,"agent_id");

		  $query2 = "SELECT agent_name FROM agent WHERE agent_id =\"$agentId\"";
		  $result2 = mysql_query($query2);
		  $agentName = mysql_result($result2,0,"agent_name");  // ERROR MESSAGE


		  echo
			  "<a href=\"?page=ViewListing&jobId=$jobId\"><div class=\"indexListingRow\">
			  <div class=\"indexListing-C1\">$jobDateCreated</div> 
			  <div class=\"indexListing-C2\">$jobTitle</div>
			  <div class=\"indexListing-C3\">$agentName</div>
			  <div class=\"indexListing-C4\">$jobDateExpires</div></div></a>";

		  $i++;
	  }
	  // ==========================================
	  // CLOSE CONNECTION
	  // ==========================================
	  mysql_close($link);
	  ?>

 

Error

 

Warning: mysql_result()  function.mysql-result: Unable to jump to row 0 on MySQL result index 8  in -------------------\Index.view.php on line 47

 

As you can see I manage to get the agent_name for the first row to display, but not the rows after. I'm not sure how to go about doing this.

 

 

My database structure...

 

agent

agent_id

agent_name

 

job

job_id

job_title

job_description

job_date_created

job_date_expires

agent_id

Well for starters you can do it in one query...

 

SELECT job.*, agent.agent_name FROM job LEFT JOIN agent ON job.agent_id=agent.agent_id ORDER BY job_date_created DESC LIMIT 0, 2

One easy way to get the results is to use mysql_fetch_assoc... how about this:-

 

$sql = "SELECT job.*, agent.agent_name FROM job LEFT JOIN agent ON job.agent_id=agent.agent_id ORDER BY job_date_created DESC LIMIT 0, 2";
if ($result = mysql_query($sql)) {
while ($row = mysql_fetch_assoc($result)) {
	?>
	<a href="whatever">
	<div><?php echo $row['job_date_created'] ?></div>
	</a>
	<?php
}
}

I've left out some of the divs and haven't bothered typing out the link properly in the href, but hopefully you get the idea.

I've left out some of the divs and haven't bothered typing out the link properly in the href, but hopefully you get the idea.

 

Thanks Zig...I'm going to mess with this and figure out what your suggestion does...haha..it might take an hour or two.

 

edit: for starters...this first time seeing LEFT JOIN in SQL haha

OMG I GOT IT ...ahhhhhhhhhhh SOOOO HAPPPPPYY

Cool