Using GET to add link when using multiple tables

[clint]

Hello,

 

I am having a bit of difficulty with the following and would like a bit of guidance if possible:

I have a users table and a jobs table. When the user inserts a job into the jobs table it saves the user id in the jobs table in a user_id column. Now, I have the code working where I can view all the jobs in the table and using GET go to a job profile page which displays the full job details and user name using a join.

The part I am struggling with is to make the user name that is displayed on the job profile view page a link to the users profile. So if you view the job you can click on the company name to view their profile.

 

Here is what I have:

View list of jobs:

 

<?php
include 'dbc.php';
page_protect(); 

if($_GET['page']) // Is page defined?

    {

        $page = $_GET['page']; // Set to the page defined

    }else{

        $page = 1; // Set to default page 1

    }

$max = 10; // Set maximum to 10



$cur = (($page * $max) - $max); // Work out what results to show

//get the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search
$result = mysql_query("SELECT jobs.job_title,jobs.remuneration,jobs.id,users.full_name FROM `jobs`,users WHERE jobs.user_id=users.id ORDER BY job_title DESC LIMIT $cur, $max") or die(mysql_error()); // select the results

//grab all the content
while($r=mysql_fetch_array($result))


{	
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns
  
   $id=$r["id"];
   $job_title=$r["job_title"];
   $remuneration=$r["remuneration"];
   $full_name=$r["full_name"];
   
   echo '<a href="view_job.php? id=' . $r['id'] . '">'.$r['job_title'].' </a><br>';
   echo " $remuneration <br> ";
   echo " $full_name <br> ";
   echo "<hr>";
   }


$counttotal = mysql_query("SELECT * FROM jobs ") or die(mysql_error()); // select all records        

$counttotal = mysql_num_rows($counttotal); // count records
$total_pages = ceil($counttotal / $max); // dive the total, by the maximum results to show 

if($page > 1){ 

                $prev = ($page - 1); 

                echo '<a href="?page=' . $prev . '">« Previous</a>'; 

                }



for($i = 1; $i <= $total_pages; $i++) // for each page number

                {

                    if($page == $i) // if this page were about to echo = the current page

                        {

                            echo'<b>' . $i .'</b> '; // echo the page number bold

                                } else {

                            echo '<a href="?page=' . $i . '">' . $i . '</a> '; // echo a link to the page

                        }

                }



if($page < $total_pages){ 

                    $next = ($page + 1); 

                echo '<a href="?page=' . $next . '">Next »</a>'; // echo the next page link

                    } 
  
?>

 

And here is the view job page:

 

<?php
include 'dbc.php';
page_protect();
{

$id = $_GET['id'];
$user = mysql_query("SELECT jobs.job_title,jobs.remuneration,jobs.terms,jobs.description,jobs.id,users.full_name FROM jobs INNER JOIN users ON jobs.user_id = users.id WHERE jobs.id = '$id'");
$user=mysql_fetch_assoc($user);

}
echo "<h1><b>".$user['full_name']."<br></h1>";
echo "<h1><b>".$user['job_title']."<br></h1>";





echo "<br>";
  

echo "<br>";

?>
            
           <font color="#FF0000"><b>Job Title:</b></font> 
            <?php echo "<b>".$user['job_title']." <br>
            ";?>
             <font color="#FF0000"><b>Remuneration:</b></font> 
            <?php echo "<b>".$user['remuneration']." <br>
            ";?>
          <font color="#FF0000"><b>Payment Terms:</b></font> 
            <?php echo "<b>".$user['terms']." <br>
            ";?>
		<font color="#FF0000"><b>Job Description:</b></font> 
            <?php echo "<b>".$user['description']." <br>
            ";?>		

 

So basically on that second page is where the full name is to be a link to user profile. Do I have to add a 2nd query?

 

Thank you in advance, any help or advice is highly appreciated!