Mike Goodstadt Posted February 27, 2008 Report Share Posted February 27, 2008 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> Quote Link to comment Share on other sites More sharing options...
rdmorss Posted February 27, 2008 Report Share Posted February 27, 2008 Something like this? (Not sure what you're linking to.)<?phpif ($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>"; }?> Quote Link to comment Share on other sites More sharing options...
Mike Goodstadt Posted February 27, 2008 Author Report Share Posted February 27, 2008 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 Quote Link to comment Share on other sites More sharing options...
theKiwi Posted February 27, 2008 Report Share Posted February 27, 2008 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,MikeI 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 Quote Link to comment Share on other sites More sharing options...
rdmorss Posted February 28, 2008 Report Share Posted February 28, 2008 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. Quote Link to comment Share on other sites More sharing options...
Mike Goodstadt Posted February 28, 2008 Author Report Share Posted February 28, 2008 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 Quote Link to comment Share on other sites More sharing options...
rdmorss Posted February 28, 2008 Report Share Posted February 28, 2008 <?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 linkecho "<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. Quote Link to comment Share on other sites More sharing options...
Mike Goodstadt Posted February 28, 2008 Author Report Share Posted February 28, 2008 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 Quote Link to comment Share on other sites More sharing options...
rdmorss Posted February 28, 2008 Report Share Posted February 28, 2008 Should the first line also have the quotes closed?Yes...good catch. Quote Link to comment Share on other sites More sharing options...
Mike Goodstadt Posted February 29, 2008 Author Report Share Posted February 29, 2008 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 ;) Quote Link to comment Share on other sites More sharing options...
rdmorss Posted February 29, 2008 Report Share Posted February 29, 2008 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>"; Quote Link to comment Share on other sites More sharing options...
Mike Goodstadt Posted February 29, 2008 Author Report Share Posted February 29, 2008 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 Permissionsline 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: gedcom - the tree to which the Individual belongsCONCAT(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.living - the check box in Individual Edit which indicates whether alive or notFROM - 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 Quote Link to comment Share on other sites More sharing options...
rdmorss Posted February 29, 2008 Report Share Posted February 29, 2008 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>"; Quote Link to comment Share on other sites More sharing options...
rdmorss Posted February 29, 2008 Report Share Posted February 29, 2008 To use this as a function change the code as follows:<?phpfunction 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. Quote Link to comment Share on other sites More sharing options...
Mike Goodstadt Posted February 29, 2008 Author Report Share Posted February 29, 2008 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,MikeIf I remember right the global call if to bring the already prepared variable inside of the function from the global setup...? Quote Link to comment Share on other sites More sharing options...
Mike Goodstadt Posted March 2, 2008 Author Report Share Posted March 2, 2008 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 Quote Link to comment Share on other sites More sharing options...
Mike Goodstadt Posted March 4, 2008 Author Report Share Posted March 4, 2008 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,Mikeinsertfullname.php Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.