Convert Numerical ID Value to Text Value

[hobbiton73]

I wonder whether someone may be able to help me please. I'm fairly new to PHP programming so please bear with me.

 

I currently have a HTML user input form, where two of the fields are dependable drop down boxes. The user selects text values from these and, along with other pieces of information, the record is saved to a mySQL database. However, rather than saving the text value I save the 'id' field value for each selection made.

 

On another 'Read Only' form, the user can then go back to have a look at the records they have previously saved.

 

The problem I have is that rather than the user being able to see the text values selected from the drop down menus, they can only see the id for each of the selections made. I just wondered whether it would be at all possible please that someone could show me what I need to do so that the user can see the 'text' rather than the 'id' value from the selections they have made.

 

The code that I am using to load the information into the read only form is below.

 

<?php 
require("phpfile.php"); 

// Start XML file, create parent node 

$dom = new DOMDocument("1.0"); 
$node = $dom->createElement("markers"); 
$parnode = $dom->appendChild($node); 

// Opens a connection to a MySQL server 

$connection=mysql_connect ("hostname", $username, $password); 
if (!$connection) { die('Not connected : ' . mysql_error());} 

// Set the active MySQL database 

$db_selected = mysql_select_db($database, $connection); 
if (!$db_selected) { 
die ('Can\'t use db : ' . mysql_error()); 
} 

// Select all the rows in the table 

$query = "SELECT findid, locationid, detectorid, searchheadid "; 

$result = mysql_query($query); 
if (!$result) { 
die('Invalid query: ' . mysql_error()); 
} 

header("Content-type: text/xml"); 

// Iterate through the rows, adding XML nodes for each 

while ($row = @mysql_fetch_assoc($result)){ 
// ADD TO XML DOCUMENT NODE 
$node = $dom->createElement("marker"); 
$newnode = $parnode->appendChild($node); 
$newnode->setAttribute("findid",$row['findid']); 
$newnode->setAttribute("locationid",$row['locationid']); 
$$newnode->setAttribute("detectorid",$row['detectorid']);
$newnode->setAttribute("searchheadid",$row['searchheadid']);
} 

echo $dom->saveXML(); 

?>

 

 

The two fields concerned are 'detectorid' and 'searchheadid'. The tables holding the 'id' and 'text' values for each are in the following tables 'detectors' and searchheads'.

 

Many thanks and kind regards

 

Chris

[monkeytooth]

well if they are saved in the db one way.. and displayed another then you need something conditional to revert the output to match what they once saw..

 

so lets say something simplistic.. your storing numeric id's from what i gather from your post. so when you pull them out of the DB your only getting the number you stored, there is no automated logic that will convert it from what you put in vs what the end user saw.. soo you have to build the logic.. the simple concept is

 

 

cow always equals 1 in the db

chicken always equals 2 in the db

cat always equals 3 in the db.... etc..

 

$outputValue1 = $newnode->setAttribute("detectorid",$row['detectorid']);

if($outputValue1 == 1){echo 'cow';}
elseif($outputValue1 == 2){echo 'chicken';}
elseif($outputValue1 == 3){echo 'cat';}
else{echo 'scratch-n-sniff, something broke';}

[requinix]

If these ID/text values are stored in the database then you use a JOIN in your query. Along the lines of

SELECT t.findid, t.locationid, t.detectorid, t.searchheadid, d.text AS detector, sh.text AS searchhead
FROM table t
JOIN detectors d ON t.detectorid = d.id
JOIN searchhead sh ON t.searchheadid = sh.id

Then your code can use "detector" and "searchhead" along with the IDs (which I like to include in export-esque stuff for use as unique keys).

while ($row = mysql_fetch_assoc($result)) {
$newnode = $parnode->appendChild($dom->createElement("marker"));
$newnode->setAttribute("findid", $row["findid"]);
$newnode->setAttribute("locationid", $row["locationid"]);

$detector = $newnode->appendChild($dom->createElement("detector", $row["detector"]));
$detector->setAttribute("id", $row["detectorid"]);

$searchhead = $newnode->appendChild($dom->createElement("searchhead", $row["searchhead"]));
$searchhead->setAttribute("id", $row["searchhead"]);
}

Detector
Search Head

(That XML is how I would do it. The actual structure is up to you, of course.)

[hobbiton73]

Both, sincere thanks for replying to my post.

 

'monkeytooth' many thanks for your suggestion. If I understood it correctly, I believe I would need to set up an 'elsesif' line for each detector added by users? I was hoping to try an automate this as much as possible because the number of detectors and searchheads will continue to grow, and I wouldn't want to have to manually add this line each time, forgive me if I've got this wrong. 

 

'requinix' again, many thanks for your suggestion. As I said at the beginning I am fairly new to this, so forgive me for asking the dumb questions. I understand the '$newnode' part of the codng but I'm a little lost on the first and last sections. Could you perhaps explain what the 't.', 'd.' and text bits of the code are in the first section and I'm also sorry but I'm not quite clear what I would need to do with the last section. Could you perhaps explain this a little further please?

 

Kind regards

 

Chris