Database connection

[tosd]

I would like to make a database connection from a content page to select data from a particular table. I know how to set up User Defineed Tags and I know how to create the SQL query I need. What I don't know is how to actually connect to the database and retrieve data from a table. Are there smarty tags for this? I would be very grateful if anyone could.

[Vin]

Not smarty tags AFAIK... but you want to do it in UDT anyway, so just place $db = &$gCms->db before the functions. You can either use standard PHP functions or Adodblite functions. List of them are on http://adodblite.sourceforge.net/functions.php and the description is on http://phplens.com/lens/adodb/docs-adodb.htm.

[tosd]

Thanks Vin. An example of the code I am trying to use is:

global $gCms;
$db =& $gCms->GetDb();

$query = 'SELECT value FROM cms_module_fb_resp_val WHERE resp_val_id=7';

$result = &$db->Execute($query);

echo $result;

I have tested the SQL query in phpmyadmin and it returns the correct value, but when I run the above using a UDT I get the message "Object id #133" displayed on screen.  Any ideas anyone?

[Vin]

With

Code: Select all

$result = &$db->Execute($query);

you have loaded the result of the query into the variable, but you need to use another function to get something human-readable; look into the adodb manual and check if the function is supported in the adodb lite.
Sorry, but I'm just a beginner, I can't advise you which proper function to use (in fact, I'm thinking about which one to choose myself). Maybe you'll need a loop, too.

[tosd]

Thanks for the follow up Vin. You are spot on   I was thinking along the same lines and have got it working with the following:

//query the db
$query = 'SELECT value FROM cms_module_fb_resp_val WHERE field_id=41 ORDER BY resp_val_id DESC';

//return result
$result = mysql_query($query)
    or die(mysql_error());

//display result
$pc = mysql_fetch_row($result);
echo "Your postcode is: $pc[0]";

For anyone else who may be interested the result that is returned by mysql_query appears to be an array and a combination of using mysql_fetch_row (I think as a pointer) and displaying (in my case) the first element of the array did the trick. If the array was known to have multiple elements then a WHILE loop could be used to display all entries.