Forums

I am pretty dumb when it comes to php so please be patient.

I am looking for a really good tutorial regarding CF and database queries or if anyone can "walk" me through setting this up.

I already have the entire registration form complete. My final results will be emailed which also works.

If I cannot achieve #2, I can live with #1.

What I want to achieve:
1. Textbox and Submit button. If the entered value matches a confirmed order in a database table, then redirect them to the registration form.


2. If the user enters a value that matches a confirmed order, I then want to populate all of the user info in the related textboxes.

I have completed the query using Navicat (MySQL): I have confirmed it works correctly. I also understand that Navicat may be slightly different when entering parameters (ie. [$any_name] vs :any_name vs '$any_name'

SELECT
xxx_hikashop_address.address_firstname,
xxx_hikashop_address.address_lastname,
xxx_hikashop_address.address_street,
xxx_hikashop_address.address_city,
xxx_hikashop_zone.zone_name,
xxx_hikashop_address.address_post_code,
xxx_hikashop_address.address_telephone
FROM
xxx_hikashop_address
INNER JOIN xxx_hikashop_order ON xxx_hikashop_address.address_user_id = xxx_hikashop_order.order_user_id
INNER JOIN xxx_hikashop_zone ON xxx_hikashop_address.address_state = xxx_hikashop_zone.zone_namekey
WHERE
xxx_hikashop_order.order_number = [$any_name] AND
xxx_hikashop_order.order_status = 'confirmed'

Thank you in advance,

Kelso

No takers? Ok. I'll make this easier (which I still don't know how to do in CF).

If a value exists in the DB table AND another value = "confirmed"
Go to the registration form.

If I can see an example of how to do a custom SQL Query and perform an action based on the results, I could probably figure out how to do what I want in my previous post.
My issue is... I don't know how to do it in php. I am converting myself to using Joomla! after many years of ASP.NET but I still have a lot to learn in php/Joomla!

Can anyone provide a good example of my above "If" statement using CF? If I can figure this out, I would like to start making tutorials on YouTube for others like me. God knows we need them for CF5 (in English). Like me, I'm sure there are others that have "visual" learning styles and find it difficult to read along with written tutorials that are not clearly defined.

Thank you,
KelsoUSMC

Hi KelsoUSMC,

There are two ways to do a DB Read in ChronoForms v5:

a. You can use the DB Read action, you can set the WHERE clause in the Conditions box - please see this FAQ for examples.

b. Using a Custom Code action you can add almost any valid MySQL query:

<?php
$db = \JFactory::getDBO();
$query = "
    SELECT `some_column`
        FROM `#__some_table`
        WHERE `some_column` = 'some_value' ;
";
$db->setQuery($query);
$data = $db->loadAssocList();
$form->data['some_name'] = $data;
?>

In your case to perform an action on the result you would probably use an Event Switcher action instead of the Custom Code action and set the event list to say unconfirmed:

<?php
$db = \JFactory::getDBO();
$query = "
    SELECT COUNT(`some_column`)
        FROM `#__some_table`
        WHERE `some_column` = '{$form->data['some_name']}' ;
";
$db->setQuery($query);
$data = $db->loadAssocList();
$form->data['some_name'] = $data;
?>

Then add a message and a redirect to the unconfirmed action.

If they are confirmed then continue with the following actions.

Your HikaShop query would look something like this

$db =& JFactory::getDBO();
$query = "
SELECT
    `address`.`address_firstname`,
    `address`.`address_lastname`,
    `address`.`address_street`,
    `address`.`address_city`,
    `zone`.`zone_name`,
    `address`.`address_post_code`,
    `address`.`address_telephone`
  FROM `#__hikashop_address` AS address
    INNER JOIN `#__hikashop_order` AS order 
      ON `address`.`address_user_id` = `order`.`order_user_id`
    INNER JOIN `#__hikashop_zone` AS zone 
      ON `address`.`address_state` = `zone`.`zone_namekey`
  WHERE `order`.`order_number = $form->data['any_name'] 
    AND `order`.`order_status = 'confirmed'
";
$db->setQuery($query);
$data = $db->loadAssocList();
$form->data['order_info'] = $data;
?>

Bob

Thank you for the quick response today Bob. Give me some time to absorb all of this and I'll get back if I have further questions.

Thanks,
Kelso