Page 1 of 2

INNER JOIN AND hyperlinking to the joined field

Posted: Mon Feb 07, 2005 6:12 am
by mohson
If I want to hyperlink a persons first name and surname so that the link leads to loading an email program with the persons email automatically entered in the 'To' box then it is pretty simple as my 'email' field is in the same table like so:

Code: Select all

'<a href=mailto:'.$row->email.'>'.$row->firstname .'</a></td><td>'.
'<a href=mailto:'.$row->email.'>'.$row->surname .'</a></td><td>'.
But I also want the persons organisation to hyperlink to the 'web_url' the only problem is that the 'web_url' field is stored in the 'organisation' table and not in the same table as was the case in the example above - so how would I link in this case:

Code: Select all

$row->organisation.'</td><td>'.

Posted: Mon Feb 07, 2005 6:47 am
by timvw
simple ones or the ones you should be able to solve yourself....



anyway, get yourself a book on sql and learn with (inner) joins are...
http://www.w3schools.com/sql/sql_join.asp

Posted: Mon Feb 07, 2005 7:12 am
by mohson
thank you,

But I already know that the organisations.weburl will refer to the field in the table I want the problem is how will the php recognize that im referring to that field.

if I do this:

'<a href:'.$row->organisations.web_url.'>.$row->organisation.'</a></td><td>'.

then the screen goes blank.

Posted: Mon Feb 07, 2005 9:41 am
by feyd
read up on inner joins.. and check your error logs, it'll probably contain several notices about unknown constants..

INNER JOIN AND linking to the joined field

Posted: Tue Feb 08, 2005 5:23 am
by mohson
Ok I have taken on all the advice given by feyd and tim and done my research

But I am still not having any joy - im trying to link my organisation name to thier web_url but web url is only available in the organisation table and not the people table

this is my new query that tells mysql that I am interested in all the fields in the people table but also the web_url field from the organisations table

Code: Select all

// database query. Enter your query here

$query = "SELECT 
		o.org_id,
		p.person_id,p.salutation,p.firstname,p.surname,
		p.organisation,p.role,p.address1,p.address2,p.city,
		p.postcode,p.telephone,p.mobile,p.fax,p.dateoflastcontact,
		p.datecontactagain,p.notes,p.email, 

		DATE_FORMAT(dateoflastcontact, '%d/%m/%y') 
		AS dateoflastcontact, 
                DATE_FORMAT(`datecontactagain`,'%d/%m/%y') 
		AS datecontactagain 
                
                FROM people p INNER JOIN organisations o 
		ON o.org_id = p.person_id
		
                 WHERE o.org_id = p.person_id
		
                 ORDER BY firstname";
This isnt producing all the results but only a select few

and here my echo results code which tellls php to store web_url inside the related organisation.

Code: Select all

'<a href='.$row->web_url.'>'.$row->organisation . '</a></td><td>'.
Can anyone help with this??

Posted: Tue Feb 08, 2005 7:52 am
by feyd
the where clause is useless. The query will return all persons who have organization entries.

Posted: Tue Feb 08, 2005 9:57 am
by mohson
Ive got rid of the WHERE - still no joy can you see any mistakes - at the momemnt only 9 records out of a possible 300 are being displayed and im still nowhere near achieving my initial aim which is to link the organisaions field in the people table to the web_url stored in the organisations field

Code: Select all

// database query. Enter your query here

$query = "SELECT 
		o.org_id,
		p.person_id,p.salutation,p.firstname,p.surname,
		p.organisation,p.role,p.address1,p.address2,p.city,
		p.postcode,p.telephone,p.mobile,p.fax,p.dateoflastcontact,
		p.datecontactagain,p.notes,p.email, 

		DATE_FORMAT(dateoflastcontact, '%d/%m/%y') 
		AS dateoflastcontact, DATE_FORMAT(datecontactagain,    '%d/%m/%y') 
		AS datecontactagain 

		FROM people p INNER JOIN organisations o
      		ON o.org_id = p.person_id  
		ORDER BY firstname";

Code: Select all

'<a href='.$row->web_url.'>'.$row->organisation . '</a></td><td>'.
[/quote]

Posted: Tue Feb 08, 2005 10:06 am
by feyd
first off you aren't selecting the web_url field. Next, if only 9 return, then there are only 9 that have organizations. How many organizations do you have?

Posted: Tue Feb 08, 2005 10:25 am
by mohson
Ok now im starting to get somewhere but two problems:

1) I dont want to select a few records I want to select all of them but those records but hyperlink the organisation field of those records that have a web_url for thier organisation.

2) the records presented are being hyperlinked to the stored web_url the only problem is that its adding the stored url to the current page hence creating a link that is non existent so it is linking to my current page with the web_url added at the end - how can I solve this?

Thank you code is below

Code: Select all

// database query. Enter your query here

$query = "SELECT 
		o.org_id,o.web_url,
		p.person_id,p.salutation,p.firstname,p.surname,
		p.organisation,p.role,p.address1,p.address2,p.city,
		p.postcode,p.telephone,p.mobile,p.fax,p.dateoflastcontact,
		p.datecontactagain,p.notes,p.email, 

		DATE_FORMAT(dateoflastcontact, '%d/%m/%y') 
		AS dateoflastcontact, DATE_FORMAT(datecontactagain, '%d/%m/%y') 
		AS datecontactagain 

		FROM people p INNER JOIN organisations o
      		ON o.org_id = p.person_id
		ORDER BY firstname";

Code: Select all

'<a href='.$row->web_url.'>'.$row->organisation . '</a></td><td>'.

Posted: Tue Feb 08, 2005 10:29 am
by feyd
switching to a LEFT JOIN will return all records of the first table and any matching records of the second, null's in those fields otherwise.

To know if you can display the link check if web_url and organisation are not null. If they aren't, you can write them out.

Posted: Tue Feb 08, 2005 10:35 am
by mohson
thanks, what about the second question - why is adding the url to the end of the current page

Posted: Tue Feb 08, 2005 10:37 am
by feyd
post more code, I couldn't tell you otherwise.

Posted: Tue Feb 08, 2005 10:50 am
by mohson
thank you, heres the full whack

Code: Select all

<?php


// config-------------------------------------
$host = "******"; //your database host
$user = "****"; // your database user name
$pass = "*****"; // your database password
$db = "contact_management_system"; // your database name

$filename = "people.html"; // name of this file
$option = array (5, 10, 20, 50, 100, 200);
$default = 10; // default number of records per page
$action = $_SERVER&#1111;'PHP_SELF']; // if this doesn't work, enter the filename


// database query. Enter your query here

$query = "SELECT o.org_id,o.web_url,
		p.person_id,p.salutation,p.firstname,p.surname,
		p.organisation,p.role,p.address1,p.address2,p.city,
		p.postcode,p.telephone,p.mobile,p.fax,p.dateoflastcontact,
		p.datecontactagain,p.notes,p.email, 

		DATE_FORMAT(dateoflastcontact, '%d/%m/%y') 
		AS dateoflastcontact, DATE_FORMAT(datecontactagain, '%d/%m/%y') 
		AS datecontactagain 

		FROM people p INNER JOIN organisations o
     		ON o.org_id = p.person_id		
		ORDER BY firstname";



// end config---------------------------------

	
$opt_cnt = count ($option);

$go = $_GET&#1111;'go'];
// paranoid
if ($go == "") &#123;
$go = $default;
&#125;
elseif (!in_array ($go, $option)) &#123;
$go = $default;
&#125;
elseif (!is_numeric ($go)) &#123;
$go = $default;
&#125;
$nol = $go;
$limit = "0, $nol";
$count = 1; 

echo "<form name="form1" id="form1" method="get" action="$action">\r\n";
echo "<select name="go" id="go">\r\n";

for ($i = 0; $i <= $opt_cnt; $i ++) &#123;
if ($option&#1111;$i] == $go) &#123;
echo "<option value="".$option&#1111;$i]."" selected="selected">".$option&#1111;$i]."</option>\r\n";
&#125; else &#123;
echo "<option value="".$option&#1111;$i]."">".$option&#1111;$i]."</option>\r\n";
&#125;
&#125;

echo "</select>\r\n";
echo "<input type="submit" name="Submit" id="Submit" value="Go" />\r\n";
echo "</form>\r\n";

$connection = mysql_connect ($host, $user, $pass) or die ("Unable to connect");
mysql_select_db ($db) or die ("Unable to select database $db");



// control query------------------------------
/* this query checks how many records you have in your table.
I created this query so we could be able to check if user is
trying to append number larger than the number of records
to the query string.*/
$off_sql = mysql_query ("$query") or die ("Error in query: $off_sql".mysql_error());
$off_pag = ceil (mysql_num_rows($off_sql) / $nol);
//-------------------------------------------- 


$off = $_GET&#1111;'offset'];
//paranoid
if (get_magic_quotes_gpc() == 0) &#123;
$off = addslashes ($off);
&#125;
if (!is_numeric ($off)) &#123;
$off = 1;
&#125;
// this checks if user is trying to put something stupid in query string
if ($off > $off_pag) &#123;
$off = 1;
&#125;

if ($off == "1") &#123;
$limit = "0, $nol";
&#125;
elseif ($off <> "") &#123;
for ($i = 0; $i <= ($off - 1) * $nol; $i ++) &#123;
$limit = "$i, $nol";
$count = $i + 1;
&#125;
&#125; 




// Query to extract records from database.
$sql = mysql_query ("$query LIMIT $limit") or die ("Error in query: $sql".mysql_error()); 



// Define your colors for the alternating rows

$color1 = "#ADD8E6";$color2 = "#E0FFFF";
$color = $color2;echo 

"<table width="50%" border="0" cellpadding="2" cellspacing="2">

    <tr>
		<td><b><small>RecNo</small></b></td>
		<td><b><small>ID</small></b></td>
		<td><b><small>Title</small></b></td>
		<td><b><small>First Name</small></b></td>
		<td><b><small>Surname</small></b></td>
		<td><b><small>Organisation</small></b></td>
		<td><b><center><small>Role</small></center></b></td>
		<td><b><small>Address(1)</small></b></td>
		<td><b><small>Address(2)</small></b></td>
		<td><b><small>City</small></b></td>
		<td><b><small>Post Code</small></b></td>
		<td><b><small>Telephone</small></b></td>
		<td><b><small>Mobile</small></b></td>
		<td><b><small>Fax</small></b></td>
		<td><b><small>Last Contact</small></b></td>
		<td><b><small>Contact Again</small></b></td>
		<td><b><small>Notes</small></b></td>";





while ($row = mysql_fetch_object($sql)) 






&#123;($color==$color2)? $color = $color1 : $color = $color2;


echo "<tr bgcolor="$color"><td>".$count . '</td><td> ' . $row->person_id .'</td><td>'.        
	
	$row->salutation .'</td><td>'.         
	
	'<a href=mailto:'.$row->email.'>'.$row->firstname .'</a></td><td>'.
	'<a href=mailto:'.$row->email.'>'.$row->surname .'</a></td><td>'.       
	
	'<a href='.$row->web_url.'>'.$row->organisation . '</a></td><td>'.

	$row->role.'</td><td>'.       
 	$row->address1 .'</td><td>'.        
	$row->address2 .'</td><td>'.       
 	$row->city .'</td><td>'.       
 	$row->postcode .'</td><td>'.        
	$row->telephone .'</td><td>'.        
	$row->mobile .'</td><td>'.        
	$row->fax .'</td><td>'.        
	$row->dateoflastcontact.'</td><td>'.        
	$row->datecontactagain  .'</td><td>'.          
	$row->notes .'</td></tr>';

$count += 1;
&#125;

echo "</table>"; 

echo "<br /><br />\r\n";
if ($off <> 1) &#123;
$prev = $off - 1;
echo "&#1111; < <a href="$filename?offset=$prev&go=$go">prev</a> ] \r\n";
&#125;
for ($i = 1; $i <= $off_pag; $i ++) &#123;
if ($i == $off) &#123;
echo "&#1111;<b> $i </b>] \r\n";
&#125; else &#123;
echo "&#1111; <a href="$filename?offset=$i&go=$go">$i</a> ] \r\n";
&#125;
&#125;
if ($off < $off_pag) &#123;
$next = $off + 1;
echo "&#1111; <a href="$filename?offset=$next&go=$go">next</a> > ] \r\n";
&#125;

echo "<br /><br />\r\n";
echo "Page $off of $off_pag<br />\r\n";



?>

Posted: Tue Feb 08, 2005 10:54 am
by feyd
got a link to a ~live version of this?

Posted: Tue Feb 08, 2005 10:56 am
by mohson
no not really because its on my University server and youll require access rights