Forums

I added some custom tables to my database. I got the entries of each table to load in drop-down boxes in my form, and when the form is submitted and appears as an article in the front end, I can get the id of each of the items to show up on the article, but I can't figure out how to translate that id into the actual name.

For example, I have one table called jos_char1. In the drop down box, these are the options that appear:
Echo
November
Sierra
Victor

In the table, their ids are 1, 2, 3, 4 respectively. So if I select "November," and put this code in the On Submit form:

<?php

$char1 = JRequest::getVar('char1');

$fulltext_template = "<br>
<strong>Character: </strong>$char1<br><br>";

JRequest::setVar("fulltext", $fulltext_template);

$_POST['char1'];

?>

(among other unrelated code) the output is then "Character: 2"

I tried doing $char1->title and $char1->name but then it would just be blank. I'm not really sure what I'm doing, so I would appreciate the assistance. Thanks!

The problem here, is that $char1 is not connected to the database table, but is simply the form data submitted by the user (in this case, the value of the option the user selected).
To get the associated data, you'll need to do some SQL yourself.

The code would have to look something like this, although you'll probably have to correct the table field names to match your database table.

<?
//Retrieve our database object and request variable.
$db =& JFactory::getDBO();
$tmp = JRequest::getVar('char1');

//Lets prepare our query. We'll use $db->quote() to andd the correct quotes and prevent code injection exploits from nasty people.
$query = "SELECT * FROM jos_char1 WHERE id = ".$db->quote($tmp).";";

//Now that we've got our query, load it into the database object, and get the result.
$db->setQuery($query);
$char1 = $db->loadObject();

//We choose to fetch the result as an object, and thus any selected field will be represented as a property of our result.
echo $char1->name;
?>

[[>> changed $db->getEscaped to $db->quote to use the Joomla code : greyhead <<]]

Excellent! Worked perfectly - thanks!

Bob,
Wouldn't that be $db->Quote() rather than $db->quote()?
In any case, the only difference between the two, is that Quote() adds the wrapping '' in addition to calling $this->getEscaped().

I guess the proper way of doing it proper joomla+php-style would be something like one of these:

...
//ID is an integer, no need for additional escaping, just use sprintf to make sure noting but a number makes it through...
$query = sprintf("SELECT * FROM `jos_char1` WHERE `id` = %d;",
  JRequest::getInt('char1'));

//ID is a string, we'll use $db->getEscaped() to add proper escaping, and quote the string itself.
//This will also allow us to add custom characters to escape, say if we needed to protect % in LIKE expressions.
$query = sprintf("SELECT * FROM `jos_char1` WHERE `id` = '%s';",
  $db->getEscaped(JRequest::getVar('char1')));

//ID is a string, we'll use $db->Quote() to add proper escaping, and let Quote add the quoting aswell.
//This will not, however, let us add custom characters to escape...
$query = sprintf("SELECT * FROM `jos_char1` WHERE `id` = %s;",
  $db->Quote(JRequest::getVar('char1')));

Personally, I prefer keeping track of quotes myself, especially since I (hopefully) have a good understanding of the table structure when I'm writing the query.
I would say both functions are proper Joomla-code; they're both defined in the JDatabase class, and inherited/redefined in the database-dependant derived classes.

Hi mnl375,

Yes it would - you are right on both counts, apologies for messing with your post.

I tend to add the $db->Quote & $db->nameQuote simply because it reliably gets round the problems of non-quoting or mis-quoting with mixes of ` ' and " both of which are frequent bugs.

Bob

Ahh, no offence taken. Your input is always welcome.
Just raised an eyebrow with the comment "to use the Joomla code". Actually hadn't noticed the Quote() method until your comment.😶

Edit: and today I can't spell apparently..