How to Recall data from databse

I have a ChronoConnectivity form linked to a table called jos_event.
In the form there is a field called Chef corresponding to the username field in the jos_users table.

I am trying to get the user id of chef in the jos_users table in order to use it in a link.

I Found an entry in the Chronoform How to Forums and tried the code in the body of the form but the system gives me an error.

Here the code I am using (For testing purpose I "hardwired" the field with the actual data aa123):

<?php
$db =& JFactory::getDBO();
$query = "
    SELECT `id`
        FROM `#__jos_users`
        WHERE `username` = 'aa123';";
$db->setQuery($query);
$data = $db->loadObjectList();

<?php echo $data->id; ?>
?>

And this is the message I get:

Notice: Trying to get property of non-object in E:\$$DEVELOPMENT\HTDOCS\MySQL\COT\components\com_chronoconnectivity\libraries\connection.php(303) : eval()'d code on line 16

The error happens at the line where I try to echo $data->id

Would appreciate any help.

Hi Larion476,

There are a typo in your query - the #_ prefix replaces 'jos' in the table name

$query = "
    SELECT `id`
        FROM `#__users`
        WHERE `username` = 'aa123';";

I think that you can also get the info you need directly from the Joomla! User Object:

<?php
$some_user =& JFactory::getUser('aa123');
echo $some_user->id;
?>

Bob

Sorry Bob, neither works, the first one gives me a similar error message.

Notice: Trying to get property of non-object in E:\$$DEVELOPMENT\HTDOCS\MySQL\COT\components\com_chronoconnectivity\libraries\connection.php(303) : eval()'d code on line 17


The second one gives this error message


Fatal error: Call to undefined function JFactory() in E:\$$DEVELOPMENT\HTDOCS\MySQL\COT\components\com_chronoconnectivity\libraries\connection.php(303) : eval()'d code on line 8

Hi Larion476,

Oops there was a typo in the second box in this line (now corrrected):

$some_user =& JFactory::getUser('aa123');

The first error seems to come from Line 17 of one of the CC code boxes - you can't tell which from the error message unfortunately.

Bob

Hello Bob

Here is the complete code and the error message

<tr>
<td><?php echo substr($MyRow->DateTime, 0, 10); ?></td>
<td> 
<a style='text-decoration:none; color: #FF0000;' href="http://www.hilair.com/chef/index.php?option=com_comprofiler&task=userprofile&user={cf_user_id}Itemid=53">{Venue}  </a></td>
<td> 
<?php
$db =& JFactory::getDBO();
$query = "
    SELECT `id`,`name`
        FROM `#__users`
        WHERE `username` = 'aa123';";
$db->setQuery($query);
$data = $db->loadObjectList();
?>
<a style='text-decoration:none; color: #FF0000;' href="http://www.hilair.com/chef/index.php?option=com_comprofiler&task=userprofile&user=<?php echo $data->id; ?>&Itemid=53"><?php echo $data->name; ?></a>
 </td>
<td> {City} </td>
<td align="center"> <a style='text-decoration:none; color: #FF0000;' href="http://www.hilair.com/chef/index.php?option=com_chronoforms&chronoform=See-Event&token={EvCode}">*</a>
</tr>
</tr>

Notice: Trying to get property of non-object in E:\$$DEVELOPMENT\HTDOCS\MySQL\COT\components\com_chronoconnectivity\libraries\connection.php(303) : eval()'d code on line 15

I believe it crashes when I a try to output the data with $data->id

Hi Larion476,

You probably just want $data = $db->loadObject(); as there is only a single result.

loadObjectList() returns an array not an object.

Bob

Thanks Bob this works when I plug in a real value like aa123.

Problem is I am getting the value from a field in the form {Chef} and I get the same error message
when I tried to put it tin the SQL Statement.

Both this methods yield the same error:

$query = "
    SELECT `id`,`name`
        FROM `#__users`
        WHERE `username` = '{chef}';";

$query = "
    SELECT `id`,`name`
        FROM `#__users`
        WHERE `username` = {chef};";

How can I plug this value in the SQL Statement?

Hi Larion476,

You can't use the 'curly quote' syntax in PHP :-(

Instead use $MyRow->Chef e.g.

$query = "
    SELECT `id`,`name`
        FROM `#__users`
        WHERE `username` = '{$MyRow->Chef}';
";

I am having similar problems, tried some of the advice here, but not getting anywhere.

<?php $db =& JFactory::getDBO(); ?>
<?php
$query = "
    SELECT name
        FROM `#_users`
        WHERE `id` = {cf_user_id}" . ";";
$db->setQuery( $query );
$result =& $db->loadObject();
print_r($db);
?>

<tr>
     <td width="17%"><?php echo $result->name; ?></td>
     <td width="17%">{location}</td>
     <td width="17%">{serial}</td>
     <td width="17%">{type}</td>
     <td width="17%">{installdate}</td>
     <td width="17%">{hours}</td>
</tr>

This is in the body section. I get this debug message:

JDatabaseMySQL Object ( [name] => mysql [_nullDate] => 0000-00-00 00:00:00 [_nameQuote] => ` [_sql] => SELECT name FROM `#_users` WHERE `id` = 325; [_errorNum] => 1064 [_errorMsg] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}' at line 3 SQL=SELECT name FROM `#_users` WHERE `id` = 325; [_table_prefix] => jos_ [_resource] => Resource id #23 [_cursor] => [_debug] => 0 [_limit] => 0 [_offset] => 0 [_ticker] => 0 [_log] => Array ( ) [_utf] => 1 [_quoted] => Array ( ) [_hasQuoted] => [_errors] => Array ( ) ) 

I don't understand what I'm doing wrong. When I plug in the query into phpmyadmin it works fine.

Hi djmeph ,

I think that the Debug message is from the print_r($db); line in your code.

What are you trying to do? Where do you have this code? Whay do you need a DB Query here?

Have you see the ChronoConenctivity tutorials here?

Bob

For each record from a custom table that is updated through Chronoforms, I'm trying to get a name from the jos_users database based on the user id that was saved in the record. It shows a list of registered products and associates their account name with them. I added the print_r ($db); line to show you the error message I'm seeing, because it's inconsistent with the results I get when I plug the query into phpmyadmin. I must be doing something wrong, but I can't figure out what.

When it works, the registered name will show up in the left column.

Hi djmeph,

OK, so this is in the Body box. The problem is that you can't use the Curly brackets syntax in the PHP. Please try

<?php
$user =& JFactory::getUser($MyRow->cf_user_id);
?>
. . .  
<td width="17%"><?php echo $user->name; ?></td>

Bob

Thanks! I will buy you a beer soon.