Recalling data from the database

I have a form that I use to enter data in a table called events
In the form I validate 2 fields, one called Chef the other Venue against data in the cb_comprofiler table and to make user the user is registered.
I also want to retreive 2 values from that table (Chefname & Chefid) in order to populate 2 hidden fields in the form ( to enter the values in my events table).
The following validation works:

<?php
$uname = $form->data['Restaurant'];   
$db=& JFactory::getDBO();           
$query = "
SELECT COUNT(*)
FROM  `#__users` AS u
LEFT JOIN `#__comprofiler` AS cb
ON u.`id` = cb.`user_id`
WHERE cb.`cb_typeofregistration` = 'Venues' AND u.`username` = '$uname'
";               
$db->setQuery($query);
$count = $db->loadResult();
if ( !$count )   {
  $form->validation_errors['Restaurant'] = "Wrong Restaurant User Name";  
return False;                                   
}
?>

When I tried to retreive values with the following code I consistently get the message "Wrong Chef User Name"

<?php
$uname = $form->data['Chef'];   
$db=& JFactory::getDBO();           
$query = "
SELECT `name`,`id`
FROM  `#__users` AS u
LEFT JOIN `#__comprofiler` AS cb 
ON u.`id` = cb.`user_id`
WHERE cb.`cb_typeofregistration` = 'Chefs' AND u.`username` = '$uname'
";               
$db->setQuery($query);
$result = $db->loadResult();

if ( !$result)   {
  $form->validation_errors['Chef'] = "Wrong Chef User Name";  
  return False;                                   
}
else {
$form->data['Chefname'] = $result['name'];
$form->data['Chefid'] = $result['id'];
}
?>

I tried with and without the COUNT(*) in the SELECT Statement
I tried the following methods: loadAssoc, loadObject, loadResult, loadObjectList
I even tried to use $num_records in the IF statement to avoid !$result

All without success.

Would appreciate any help

Hi Larion476,

I don't see anything obviously wrong with your code here.

The first thing I would so do is to add some debug code to show the query and see if that is looking OK, if there's any doubt I'd copy and paste it into PHPMyAdmin to test. Here's my debug code, the result displays as a system message so it does requrie that your template shows Joomla! system messages!

$mainframe =& JFactory::getApplication();
$mainframe->enqueuemessage('$query: '.print_r($query, true).'<hr />');

Bob

PS If your query returns an array or an object then if ( !$result) will always be false - an empty array or a full one look the saem to this test. Use count($result) instead.

Hello Bob,
The Query is fine I tested it.
Where should Iplace the debug code?

In the mean time I created a very simple form with one text field for user name and a customs code field to display result.

Here is the custom Code (The echo "hello" is just to see that the custom code displays something):

<?php
$db =& JFactory::getDBO();
$query = "
    SELECT `id`,`name`
        FROM `#__users`
        WHERE `username` = $form->data['username']";
$db->setQuery($query);
$result = $db->loadObjectList();
echo "hello";
echo $result->name;
?>

I tried loadObjectList, loadObject,loadResult and loadAssoc and consistently get the following output:

hello
Notice: Trying to get property of non-object in E:\$$DEVELOPMENT\HTDOCS\MySQL\COT\administrator\components\com_chronoforms\form_actions\custom_code\cfaction_custom_code.php(14) : eval()'d code on line 10

Clearly I cannot display the result of the query

Me again,

I Just put your debug code in the custom code field of my test form and got this message:

Notice: Undefined variable: query in E:\$$DEVELOPMENT\HTDOCS\MySQL\COT\administrator\components\com_chronoforms\form_actions\custom_code\cfaction_custom_code.php(14) : eval()'d code on line 3
helloaa123
Notice: Trying to get property of non-object in E:\$$DEVELOPMENT\HTDOCS\MySQL\COT\administrator\components\com_chronoforms\form_actions\custom_code\cfaction_custom_code.php(14) : eval()'d code on line 12
here is the code:

<?php
$mainframe =& JFactory::getApplication();
$mainframe->enqueuemessage('$query: '.print_r($query, true).'<hr />');
$db =& JFactory::getDBO();
$query = "
    SELECT `id`,`name`
        FROM `#__users`
        WHERE `username` = $form->data['username']";
$db->setQuery($query);
$result = $db->loadObject();
echo "hello". $form->data['username'];
echo $result->name;
?> 
?>

Hi Larion476,

Well yes, you can't output $query before it is defined. You need to move the $mainframe->enqueuemessage() line down to a point after the $query = " . . . line

Bob

Bob

Put the debug code after the $result = $db->LoadObject();

<?php
$mainframe =& JFactory::getApplication();
$db =& JFactory::getDBO();
$query = "
    SELECT `id`,`name`
        FROM `#__users`
        WHERE `username` = $form->data['username']";
$db->setQuery($query);
$result = $db->loadObject();
$mainframe->enqueuemessage('$query: '.print_r($query, true).'<hr />');
echo "hello". $form->data['username'];
echo $result->name;
?>

and got this,

message

$query: SELECT `id`,`name` FROM `#__users` WHERE `username` = Array['username']

hellobb234
Notice: Trying to get property of non-object in E:\$$DEVELOPMENT\HTDOCS\MySQL\COT\administrator\components\com_chronoforms\form_actions\custom_code\cfaction_custom_code.php(14) : eval()'d code on line 12

IF I replace WHERE `username` = $form->data['username'] with WHERE `username` = 'aa123' it works, here is the result:

message

$query: SELECT `id`,`name` FROM `#__users` WHERE `username` = 'aa123'

helloaa123Albert Angus

It means I have a problem putting variables in the SQL statement.

I have exactly the same problem with ChronoConnectivity, I updated a post of mine in ChronoConnectivity Forum yesterday stating the same thing: How do I put a variable in my SQL statement?.

Hi Larion476,

This line needs some sorting

WHERE `username` = $form->data['username']";

Try

WHERE `username` = '{$form->data['username']}' ";

Bob

Works, Great!

Thanks Bob

Hope you can help on the ChronoConnectivity one!