Database connection problem

Hi all,

i am having a problem setting up a connection to the database. up until now i have been using the built in Actions like the DB multi Record Loader to connect to the database. However now thing are geeting a bit more complicated i want to start using a custom code action and connect using code as i need to do a lot of manipulation of the data, running different queries depending on feedback from the form etc.

I have read a lot on how to do this. I am not a coder so this is all a learning curve for me. So i am going to need a few pointers as i go down this road🙂

I have a table in the database called hma_vehicles, and i want to select all the records belonging to the user.

I have the following code in a custom code action in the On_Load event of my form. and a debugger after the custom code, this shows nothing when i test the form, and nothing is loaded into the $form->data.

<?php
$user = &JFactory::getUser();
$db =& JFactory::getDBO();
$query = "
    SELECT 'vehicle_id', 'vrm'
        FROM 'hma_vehicles'
         WHERE 'user_id' = .$user->id
";
$db->setQuery($query);
$data = $db->loadAssocList();
$form->data = array_merge($form->data, $data) ;
?>

So what am i doing wrong here, is there a step i am missing before this? is the line

$db =& JFactory::getDBO(); 

all i need to open a connection to the Joomla! database (all my table are stored in the same DB

Thanks in advance

Allan

OK, i have got it to work using the following

<?php
$user = &JFactory::getUser();
$db = JFactory::getDBO();
$query = "
  SELECT `vehicle_id`, `vrm`, `make`, `model` 
    FROM `hma_vehicles` 
      WHERE `user_id` = $user->id ;
";
$db->setQuery($query);
$data = $db->loadAssocList();
$form->data['Vehicles'] = array_merge($form->data, $data) ;

?>

though i am getting a weird issue when i list the items in the array using the following code

<?php foreach($form->data['Vehicles'] as $detail): ?>
<table>
<tr>
<td><?php echo $detail['vrm']; ?></td>
</tr>
</table>
<?php endforeach; ?>

It's adding 3 extra tables, all of which has one cell with either a letter or digit inside, so the output has 3 lines above the data. i cant see anything on the form that i have added that would explain this?

Kind Regards,

Allan

Hi Slone,

Thanks for your reply. i have modified the code to the following

<div>
<table class="Details">
<tr><td>Auto</td><td>Purchase Price</td><td>Sold Price</td></tr>
<?php foreach($form->data as $detail): ?>
<?php echo "<tr><td>" .$detail['vrm']. " " .$detail['make']. " " .$detail['model']."</td><td>" .$detail['purchase_price']."</td><td>" .$detail['sold_price']."</td></tr>"; ?>
<?php endforeach; ?>

</table>
</div>

This is what i end up with


https://drive.google.com/file/d/0Byq3X1lZet31c3prX3lITDdPa0U/edit?usp=sharing
It's the first 3 rows that i have no idea where it comes from.

Any ideas?

Hi Allenbeth,

My guess would be some old test data in the database table.

Bob

Hi Guys, this is what the Debugger shows

Array
(
    [Itemid] => 144
    [option] => com_chronoforms
    [view] => form
    [0] => Array
        (
            [vehicle_id] => 1
            [vrm] => Test 001
            [make] => Ford
            [model] => Fiesta
            [purchase_price] => 7840
            [sold_price] => 
        )

    [1] => Array
        (
            [vehicle_id] => 2
            [vrm] => Test 002
            [make] => Ford
            [model] => Focus
            [purchase_price] => 13220
            [sold_price] => 0
        )

    [2] => Array
        (
            [vehicle_id] => 3
            [vrm] => Test 003
            [make] => BMW
            [model] => 3 Series
            [purchase_price] => 23450
            [sold_price] => 0
        )

    [3] => Array
        (
            [vehicle_id] => 4
            [vrm] => Test 004
            [make] => BMW
            [model] => A Class
            [purchase_price] => 32000
            [sold_price] => 0
        )

    [4] => Array
        (
            [vehicle_id] => 5
            [vrm] => Test 005
            [make] => Ford
            [model] => Fiesta
            [purchase_price] => 9995
            [sold_price] => 0
        )

    [5] => Array
        (
            [vehicle_id] => 6
            [vrm] => Test 006
            [make] => Ford
            [model] => KA
            [purchase_price] => 6000
            [sold_price] => 0
        )

    [6] => Array
        (
            [vehicle_id] => 7
            [vrm] => Test 007
            [make] => Ford
            [model] => Galaxy
            [purchase_price] => 12500
            [sold_price] => 0
        )

    [7] => Array
        (
            [vehicle_id] => 8
            [vrm] => Test 008
            [make] => Vauxhall
            [model] => Vectra
            [purchase_price] => 9850
            [sold_price] => 0
        )

)
Validation Errors: 
Array
(
)

Nothing extra in there and the table in the database.

Really strange, unless i'm missing something glaringly obvious

OK, i found it. the problem was this line of code

$form->data = array_merge($form->data, $data) ;

I removed the first variable so i was not merging 2 arrays but just the one containing the data from the connection

$form->data = array_merge($data) ;

There must have been some data in the $form->data array as the page loads

Is this still the best code to use to get the data into the $form->data array?

Hi allanbeth,

Using array_merge() with one parameter seems to work OK but just redefines $form->data to be the same as $data.

What is this line supposed to be doing anyhow?

$form->data['Vehicles'] = array_merge($form->data, $data) ;

Bob

PS ChronoForms loads any existing entries in the GET and POST arrays (that is from the calling URL and any preceding form) into the $form->data array when the form loads.