Getting ID details from SQL

[Mike Goodstadt]

Can anyone get me started...

I want to add a direct link to a user but get the fullname from the database rather than hardcoding ithe name.

Can't quite get my head around how to extract and use data from the database correctly as I'm new to SQL.

eg.

<a href="copiedLink">fullnameFromDB</a>

[rdmorss]

Something like this? (Not sure what you're linking to.)

<?php
if ($currentuser) {
  $query = "SELECT realname FROM tng_users WHERE username='$currentuser';
  $result = mysql_query($query);
  $data = mysql_fetch_assoc($result);
  echo "<a href=\"copiedLink\">$data['realname']</a>";
  }
?>

[Mike Goodstadt]

Thankyou for the fast reply.

However I need the full name of an 'individual' in my genealogy not that of a user.

For example if a key ancestor has an id in the database of I109 and I want to include a direct link from a mention of him on the front page to his personal details.

Can anyone give an example of that scenario?

In the meantime I'll use what you've given me to try and work it out.

Saludos,

Mike

[theKiwi]

Thankyou for the fast reply.

However I need the full name of an 'individual' in my genealogy not that of a user.

For example if a key ancestor has an id in the database of I109 and I want to include a direct link from a mention of him on the front page to his personal details.

Can anyone give an example of that scenario?

In the meantime I'll use what you've given me to try and work it out.

Saludos,

Mike

I don't entirely understand what you'ree trying to do. If this is going to be hard coded into the front page, just do it with an HTML link

<a href="getperson.php?personID=Iyyyy&Tree=xxxx" target=_blank>Person's Name</a>

I'm not sure how an SQL query helps with this.

If you're wanting it to rotate amongst a number of different people, that's a whole different thing, on a different level.

Roger

[rdmorss]

However I need the full name of an 'individual' in my genealogy not that of a user.

Well...I read "user" in your initial posting to start this thread, so that's what I went for. Also because (like Roger) I didn't see how an SQL query would be any improvement over a hard-coded link if you're just linking to an individual in the genealogy.

[Mike Goodstadt]

Quite right, my slipup for saying user - I thought to myself "not user" so much that I ended up writing just that!!!

Roger is right: what I want is something like:

<a href="getperson.php?personID=Iyyyy&Tree=xxxx" target=_blank>Person's Name</a>

But instead of 'Person's Name' having to be written into the HTML I would like the name to be generated from the individual's entry in the database. That way I avoid having to track and rewrite when changes or corrections occur.

Remember, I am NOT referring to users of the database but to people with their genealogy documented in TNG - hence getperson.php.

Many thanks for your time and patience.

Mike

[rdmorss]

<?php
// Define the query
$query = "SELECT gedcom,firstname,lastname FROM tng_people WHERE personID='Ixxx'";
// Run the query
$result = mysql_query($query);
// Parse the result
$data = mysql_fetch_assoc($result);
$fullname = $data['firstname'] . " " . $data['lastname'];
$tree = $data['gedcom'];
// Print the link
echo "<a href=\"getperson.php?personID=Ixxx&tree=$tree\">$fullname</a>";
?>

You just neet to specify the personID in the first and last line of code.

Edited 2/28/08 at 10:55 EST - There was an error in the code I first posted (in the $fullname assignment). I just tested the above code on my localhost TNG homepage and it works.

[Mike Goodstadt]

OK thanks to your //comments I'm starting to understand what is happening.

Should the first line also have the quotes closed? Like this:

<?php

$query = "SELECT gedcom,firstname,lastname FROM tng_people WHERE personID='Ixxx'";

Also are any of you able to advise as to whether the next two are better ways of doing it, just other means to the same end or PHP trash full of redundancy and security loopholes...

1. Getting the nickname if any (many thanks Luke):

$query = "SELECT gedcom, concat(firstname, if(length(nickname),concat(' \"',nickname,'\" '),' '), lastname) as fullNameRecord FROM tng_people WHERE personID='Ixxx'";

$result = mysql_query($query);

$data = mysql_fetch_assoc($result);

$fullname = $data['fullNameRecord'];

$tree = $data['gedcom'];

echo "<a href=\"getperson.php?personID=Ixxx&tree=$tree\">$fullname</a>";

2. Only having to put the ID Ixxx in once (useful if various individuals are being quoted):

$query = "SELECT concat('<a href=\"getperson.php?personID=',personid,'&tree=',gedcom,'\">',firstname,if(length(nickname),concat(' \"',nickname,'\" '),' '),lastname,'</a>') as fullNameData FROM tng_people WHERE personID='Ixxx'";

$result = mysql_query($query);

$data = mysql_fetch_assoc($result);

$fullNameHTML = $data['fullNameData'];

echo $fullNameHTML;

Off now to write it so that restricted users can't see the link to the individual's page if 'Living'...

Once again many thanks to all.

Mike

[rdmorss]

Should the first line also have the quotes closed?

Yes...good catch.

[Mike Goodstadt]

In case anyone is interested here is the code allowing you to put the reference code in once and also only allowing the name to be linked if you have allow_living access rights.

But can anyone explain why the $allow_living is not working? I placed two ids, one living, one deceased. Then logged in as admin - all ok- each fullname surrounded by anchor link to its page. The logged in as guest with no rights to view living - and neither id had the anchor link created around the fullname...

It's as if the deceased ancestor is rated as living! Do I need to 'flush out' the variables $linkallow and $linkclose with each use of this lump of PHPoop? Should I shove it into a funtion and call it eg. getfullname(Ixxx)?

$IDwanted = "Ixxx";
    if($allow_living) {
        $linkallow = "'<a href=\"getperson.php?personID=',personid,'&tree=',gedcom,'\">',";
        $linkclose = ",'</a>'";
    }
    else {
        $linkallow = "";
        $linkclose = "";
    }
$query = "SELECT concat(".$linkallow."firstname,if(length(nickname),concat(' \"',nickname,'\" '),' '),lastname".$linkclose.") as fullNameData FROM tng_people WHERE personID='".$IDwanted."'";
$result = mysql_query($query);
$data = mysql_fetch_assoc($result);
$fullNameHTML = $data['fullNameData'];
echo $fullNameHTML;

Now I'd like to shove it off into a function or separate PHP file to be called with the reference as variable...

Wish me luck.

PHPnewbie Mike ;)

[rdmorss]

But can anyone explain why the $allow_living is not working?

Because you have not included the living field in your SELECT statement and tested that against the user's allow_living permission.

Here's how I would code it (continuing with the nice CONCAT function you demonstrated above):

$IDwanted = "Ixxx"; $linkOK = 0;
$query = "SELECT gedcom,CONCAT(firstname,IF(LENGTH(nickname),CONCAT(' \"',nickname,'\" '),' '),lastname) AS fullNameRecord,living FROM tng_people WHERE personID='$IDwanted'";
$result = mysql_query($query);
$data = mysql_fetch_assoc($result);
$tree = $data['gedcom']; $alive = $data['living'];
if (!$alive || ($alive && $allow_living)) $linkOK = 1;
if ($linkOK) echo "<a href=\"getperson.php?personID=$IDwanted&tree=$tree\">";
echo $data['fullNameRecord'];
if ($linkOK) echo "</a>";

[Mike Goodstadt]

Beautifull!

Ignore what follows if you understand what rdmorss has just coded.

-------------------------------------------------------------------

For future reference and help me work through the logic of controlling access of Users to an Individual:

How to Change Content Depending on User Permissions

line 1. Set my own variables:

$IDwanted = "Ixxx"; $linkOK = 0;

$IDwanted - the ID of the Individual I want to place in my HTML content. $linkOK - a switch to set to on if it is OK to link to that Individual. line 2. Set up the mySQL query string that will pull in all necessary data from the TNG database:

$query = "SELECT gedcom,CONCAT(firstname,IF(LENGTH(nickname),CONCAT(' \"',nickname,'\" '),' '),lastname) AS fullNameRecord,living FROM tng_people WHERE personID='$IDwanted'";

SELECT - the mySQL command to... select the following:

1. gedcom - the tree to which the Individual belongs
2. CONCAT(firstname,IF(LENGTH(nickname),CONCAT(' \"',nickname,'\" '),' '),lastname) AS fullNameRecord
   • CONCAT joins together the various parts of the individuals name.
   • IF construct in the middle tests for a nickname and adds it in parentheses if found.
   • AS gives the fully joined up name the title fullNameRecord to easy reference.
3. living - the check box in Individual Edit which indicates whether alive or not

FROM - reference to the table in the TNG database, i.e. tng_people where Individuals' data are stored . WHERE - condition of data selection, i.e. "from indiviudal specified by $IDwanted. line 3. Run the query:

$result = mysql_query($query);

line 4. Sort/check (parse) the result:

$data = mysql_fetch_assoc($result);
$tree = $data['gedcom']; $alive = $data['living'];

$data = mysql_fetch_assoc($result); - place results into an array $data for easy manipulation. $tree = $data['gedcom']; - variable $tree set to name of the Individual's tree, got from array $data. $alive = $data['living']; - variable $alive set to whether Individual living or not, got from array $data. line 5. Check if Individual is NOT (!$alive) alive. If not true (i.e is alive) then check if the User is allowed to see the living. If either case is true true then allow linking to Individual.

if (!$alive || ($alive && $allow_living)) $linkOK = 1;

lines 6+. write HTML including link if found to be allow by $linkOK.

if ($linkOK) echo "<a href=\"getperson.php?personID=$IDwanted&tree=$tree\">";
echo $data['fullNameRecord'];
if ($linkOK) echo "</a>";

------------------------------------------------------------------------------------

Now I think I understand.

Many thanks to all.

Happy coding.

Even happier researching

Mike

[rdmorss]

A follow-up note of caution....this above solution assumes that personID is unique, which it should be if you have just one tree. But if you have multiple trees in your database, the personID you set as $IDwanted likely will not be unique. The above code would still work, displaying info on the first matching personID in the result set. To get around this you need to define $tree on the first line of code and modify the SELECT query WHERE clause.

$IDwanted = "Ixx"; $tree ="zzzzzz"; $linkOK = FALSE;
$query = "SELECT CONCAT(firstname, if(length(nickname),CONCAT(' \"',nickname,'\" '),' '),lastname) AS fullNameRecord,living FROM tng_people WHERE personID='$IDwanted' AND gedcom='$tree'";
$result = mysql_query($query);
$data = mysql_fetch_assoc($result);
$alive = $data['living'];
if (!$alive || ($alive && $allow_living)) $linkOK = TRUE;
if ($linkOK) echo "<a href=\"getperson.php?personID=$IDwanted&tree=$tree\">";
echo $data['fullNameRecord'];
if ($linkOK) echo "</a>";

[rdmorss]

To use this as a function change the code as follows:

<?php
function namefromDB($IDwanted,$tree) {
  global $allow_living;
  $linkOK = FALSE;
  $query = "SELECT CONCAT(firstname, if(length(nickname),CONCAT(' \"',nickname,'\" '),' '),lastname) AS fullNameRecord,living FROM tng_people WHERE personID='$IDwanted' AND gedcom='$tree'";
  $result = mysql_query($query);
  $data = mysql_fetch_assoc($result);
  $alive = $data['living'];
  if (!$alive || ($alive && $allow_living)) $linkOK = TRUE;
  if ($linkOK) echo "<a href=\"getperson.php?personID=$IDwanted&tree=$tree\">";
  echo $data['fullNameRecord'];
  if ($linkOK) echo "</a>";
  }
?>

To call the function:

<?php namefromDB("Ixxx","zzzzzz"); ?>

Just make sure you have defined the function before making any calls to it.

[Mike Goodstadt]

Wow, Robert you just saved me a long evening struggling through documentation and scripts - my family will be happy to have my company after a long week

I will however studied and learn. Hadn't realised that IDs might be duplicated.

Thats more often than not the trick with these things - knowing the ins and outs of each coding system or program which takes time and experience to pick up.

Other than that I feel I'm starting to get to grips with the coding.

Thanks once again,

Mike

If I remember right the global call if to bring the already prepared variable inside of the function from the global setup...?

[Mike Goodstadt]

Is this the correct way to go about 'translating' a function into a php file? It seems to work...

Added code into file with some general includes and a connection to the database at the start.

This is then called in index.php after the call for checklogin.php as:

include($cms[tngpath] . "insertfullname.php");

The file attached contains the following:

<?php
//=====================================================
//   getfullname.php
//   Author: Robert Morss http://morssweb.com/genealogy
//   in conversation with Mike Goodstadt on TNG Forums
//   Usage with 1 Tree: namefromDB("ID")
//   Usage if multiple trees: namefromTree("ID","Tree")
//=====================================================

include_once("config.php");
include_once("genlib.php");
include_once("checklogin.php");

tng_db_connect($database_host,$database_name,$database_username,$database_password) or exit;

// When only one tree is in the database.
function namefromDB($IDwanted) {
//needed?  global $allow_living;
  $linkOK = FALSE;
  $query = "SELECT gedcom,CONCAT(firstname,IF(LENGTH(nickname),CONCAT(' \"',nickname,'\" '),' '),lastname) AS fullNameRecord,living FROM tng_people WHERE personID='$IDwanted'";
  $result = mysql_query($query);
  $data = mysql_fetch_assoc($result);
  $tree = $data['gedcom'];
  $alive = $data['living'];
  if (!$alive || ($alive && $allow_living)) $linkOK = TRUE;
  if ($linkOK) echo "<a href=\"getperson.php?personID=$IDwanted&tree=$tree\">";
  echo $data['fullNameRecord'];
  if ($linkOK) echo "</a>";
  }

// When there is more than 1 tree in the database.
function namefromTree($IDwanted,$tree) {
//needed?  global $allow_living;
  $linkOK = FALSE;
  $query = "SELECT CONCAT(firstname, if(length(nickname),CONCAT(' \"',nickname,'\" '),' '),lastname) AS fullNameRecord,living FROM tng_people WHERE personID='$IDwanted' AND gedcom='$tree'";
  $result = mysql_query($query);
  $data = mysql_fetch_assoc($result);
  $alive = $data['living'];
  if (!$alive || ($alive && $allow_living)) $linkOK = TRUE;
  if ($linkOK) echo "<a href=\"getperson.php?personID=$IDwanted&tree=$tree\">";
  echo $data['fullNameRecord'];
  if ($linkOK) echo "</a>";
  }
  
?>

insertfullname.php

[Mike Goodstadt]

And just for good measure here's the same but with one combined function:

<?php
//=====================================================
//   insertfullname.php
//   Author: Robert Morss http://morssweb.com/genealogy
//   in conversation with Mike Goodstadt on TNG Forums:
//   http://www.tngforum.us/index.php?showtopic=3239
//   Usage e.g. namefromTree("I123");
//   or with tree e.g. namefromTree("I456","Gummy-Bears");
//  Remember to wrap in php tags...
//=====================================================

include_once("config.php");
include_once("genlib.php");
include_once("checklogin.php");

tng_db_connect($database_host,$database_name,$database_username,$database_password) or exit;

function namefromDB($IDwanted,$tree = NULL) {
   global $allow_living;
  $linkOK = FALSE;
  $query = "SELECT ".(!$tree?"gedcom, ":"")."CONCAT(firstname, if(length(nickname),CONCAT(' \"',nickname,'\" '),' '), lastname) AS fullNameRecord, living FROM tng_people WHERE personID='$IDwanted'".($tree?" AND gedcom='$tree'":"");
  $result = mysql_query($query);
  $data = mysql_fetch_assoc($result);
  if (!$tree) $tree = $data['gedcom'];
  $alive = $data['living'];
  if (!$alive || ($alive && $allow_living)) $linkOK = TRUE;
  if ($linkOK) echo "<a href=\"getperson.php?personID=$IDwanted&tree=$tree\">";
  echo $data['fullNameRecord'];
  if ($linkOK) echo "</a>";
   }

?>

Saludos,

Mike

insertfullname.php