Forums

Hello,
For a special list, I need to do this :
In a mysql table, I have a column named 'parameters' which have a text like this
For id = 1 : parameters="param1=xxx;param2=yyy;param3=zzz"
For id = 2 : parameters="param1=aaaaaa;param2=bbbbb;param3=ddddd"
etc ...

I want to display those parameters in 3 differents colums :

----------------------------------------------------------------------
ID PARAM1 PARAM2 PARAM3
----------------------------------------------------------------------
1 xxx yyy zzz
2 aaaaaa bbbbb ddddd

My query needs to be like this : "select parameters as col1, parameters as col2, parameters as col3 from ....." for using PHP Functions :
col1:myfunction1($cell); --> Extract param1 value
col2:myfunction2($cell); --> Extract param2 value
col3:myfunction3($cell); --> Extract param3 value

How can I do this ?

Thanks

Hello graaooor,
I'm not a Chrono professional, but after checking your new post, I think that the following links may help:
How can I add a new column to a database table or change a column type?
P.S: I'm just an automated service😉

Hi graaooor,

You can use explode to convert the list back to an array:

<?php
$params = explode(';', $row['params']);
?>

!! Not tested !!

The individual params would then be in $params[0], $params[1], $params[2]

I'm not certain where exactly is the best place to do this.

Bob

Hi,

col2:return explode('=', explode(';', $row['params'])[1])[1];

You will need to change the first [1] based on the param number!

Best regards,
Max

Not sure you understand what I want to do.
I work with virtuemart and I want to display the virtuemart_order_items for a particular order (here number 13410).
In debug mode I have :

SELECT 
`item`.`virtuemart_order_item_id` AS `item.virtuemart_order_item_id`,
. . .
`item`.`order_status` AS `item.order_status`,
`item`.`product_attribute` AS `item.product_attribute`,
`item`.`delivery_date` AS `item.delivery_date`, 
. . .
`item`.`locked_by` AS `item.locked_by` 
FROM `flex_virtuemart_order_items` AS `item` 
WHERE `item`.`virtuemart_order_id` = '13410' LIMIT 30

I want to do the same thing but like this :

SELECT 
`item`.`virtuemart_order_item_id` AS `item.virtuemart_order_item_id`, 
. . .
`item`.`order_status` AS `item.order_status`,
`item`.`product_attribute` AS `item.col1`, 
`item`.`product_attribute` AS `item.col2`, 
`item`.`product_attribute` AS `item.col3`,
`item`.`delivery_date` AS `item.delivery_date`, 
. . .
`item`.`locked_by` AS `item.locked_by` 
FROM `flex_virtuemart_order_items` AS `item` 
WHERE `item`.`virtuemart_order_id` = '13410' LIMIT 30

Then, I could call a different php function for each column (col1, col2 or col3) and extract each parameter separately .

The problem is how to associate virtuemart_attribute to col1, col2 and col3 ?

It should work fine using the first query, but you can use dummy fields names, in the columns list, use this:

Model.param1:title
Model.param2:title2
Model.param3:title3

and in the php functions:

Model.param2::return explode('=', explode(';', $row['product_attribute'])[1])[1];

Sorry, I don't understand what is Model.param1:title ... in your response.

A small example :
I have a mysql table with only 2 fields : ID and NAME.

I want to generate a list with id and name. It's ok for me.
The select is "SELECT model.ID,model.NAME from model.TABLE"

Now I want to generate a list with the ID, the NAME, and the NAME again.
The select is "SELECT model.ID,model.NAME AS COL1, model.NAME AS COL2 from model.TABLE"

How can I do that with CCV5 ?

under "Models" > Fields >

<?php
return array("model.ID", "model.NAME" => "COL1", "model.NAME" => "COL2");

I think this should do what you need!

Best regards,
Max

I try this :

Models / Fields :

<?php
return array("item.virtuemart_order_id", "item.order_item_sku","item.product_attribute" => "COL1","item.product_attribute" => "COL2","item.product_quantity");
?>

Front List / Columns List :
item.order_item_sku:PRODUIT
item.COL1:TF
item.COL2:CF
item.product_quantity:QTE

But the generated select is wrong :

Array
(
    [0] => SELECT `Extension`.`id` AS `Extension.id`, `Extension`.`name` AS `Extension.name` FROM `flex_chronoengine_extensions` AS `Extension` WHERE `Extension`.`enabled` = '1' ORDER BY `Extension`.`ordering` ASC
    [1] => SELECT `Connection`.`id` AS `Connection.id`, `Connection`.`title` AS `Connection.title`, `Connection`.`params` AS `Connection.params`, `Connection`.`extras` AS `Connection.extras`, `Connection`.`published` AS `Connection.published` FROM `flex_chronoengine_connections` AS `Connection` WHERE `Connection`.`title` = 'detailCommande' AND `Connection`.`published` = '1'
    [2] => SELECT COUNT(`item`.`virtuemart_order_item_id`) AS `item.count` FROM `flex_virtuemart_order_items` AS `item` WHERE `item`.`virtuemart_order_id` = '13410'
    [3] => SELECT `item`.`virtuemart_order_id` AS `item.virtuemart_order_id`, `item`.`order_item_sku` AS `item.order_item_sku`, `item`.`product_attribute` AS `item.COL2`, `item`.`product_quantity` AS `item.product_quantity` FROM `flex_virtuemart_order_items` AS `item` WHERE `item`.`virtuemart_order_id` = '13410' LIMIT 30
)

I have the item.product_attribute AS item.COL2 only.
There is nothing for COL1 !!!

I think this may not work in the current version without a core code change then, so I suggest that you simply process the value using the functions box, why do you need to get the same value under 2 columns names ? what do you want to do next ?

I work for a company which sell glasses.
The client who is logged on the website can choose a model, the size of the face, its color, the size of each branch and the color.
All these informations are stocked in the field product_attribute.

Everyday, a list must be create on all orders. This list specifie for each item order the model, the size, colours, ...

The list is like this :

model | size face | color face | size branches | color left branch | color right branch

Model is the field product_item_sku : No problem here.
The others ones ( size face | color face | size branches | color left branch | color right branch ) are stocked in the field product_attribute.

So I need to extract 5 differents informations, and create 5 columns to the list with only ONE field.
Its why I want to generate a select like :
select ...., product_attribute as col1, product_attribute as col2, ... col5 from ....
and use a php function for each one (col1, col2 ...) to extract for col1 the size of the face, col2 the colour of the face, ..,.

I don't know if it's clear but it's realy a problem for me to do this with CCV5.









I work for a company which sell glasses.
The client who is logged on the website can choose a model, the size of the face, its color, the size of each branch and the color.
All theses informations are stocked in the field product_attribute.

Ok, we will do it with one field only, what do you have in the "columns list" box under your connection edit page now ?

Is it what you want to know ?

The result is like this :
I have nothing in the column name TF (COL1).

The select generated is :
SELECT `item`.`virtuemart_order_id` AS `item.virtuemart_order_id`, `item`.`order_item_sku` AS `item.order_item_sku`, `item`.`product_attribute` AS `item.COL2`, `item`.`product_quantity` AS `item.product_quantity` FROM `flex_virtuemart_order_items` AS `item` WHERE `item`.`virtuemart_order_id` = '13410' LIMIT 30

COL1 is not present !

If you get the data in the format you explained earlier then you could use this in the php functions box:

item.COL2:return explode(";", $cell)[0];

BUT, the image you have now show that you have a list in JSON format and you actually have the ids, not the text, there is no way to load this data in CCv5.

Sorry but you are wrong !
The problem is that the query is malformed.
If you simply replace Models / Fields by :

<?php
return array("item.virtuemart_order_id", "item.order_item_sku" => "PRD1", "item.order_item_sku" => "PRD2" );
?>

The query generated is wrong !

There is only the "AS PRD2" in the query. The first "AS PRD1" is omitted by CCV5 !

Ok, but the array value is simply not passed to the query, this is not a big issue, how the 2nd copy of the value can help the situation here ? you can do everything with the field value you load.

The problem is really that the array is not passed completly to the query and I don't know how to do.

Can you show me how to do this for this simple example : Display the column "item_order_sku" twice in the list.
The first column with the original string, the second inversed letters !

The list will be like this :
KIDI01 10IDIK

Hi graaooor,

I think that this condition won't work because you have two array keys that are the same.

array(
  "item.virtuemart_order_id", 
  "item.order_item_sku" => "PRD1", 
  "item.order_item_sku" => "PRD2" 
);

Bob

Hi Bob,
It's what I want !
I need to extract the same field twice, or more, with a alias ( "AS COL1", "AS COL2" for example), and apply a php function on each alias to do something different on each value..
It's impossible here because the query isn't generated correctly.

I used in the past a joomla component where I could write my own queries.
Infortunately, this component is définitaly abandonned since a few years. It was writted for a joomla 2.5.
I think that CFV5 is a good component, easy to use to display a simply list.
But when you want to do more complex things, It's really long to understand how to do, when it's possible !.
My question is, for me, simple : How to display a table field twice in the list.
A request like "select field_name as a, field_name as b from ..." isn't a common query, but in my case, it's a priority :
Write a php function to extract some informations with "a", and write another php function to extract other information with "b".

A suggestion for CCV5 would be for me to have a zone where I could write my own query and use all the others facilities offered with the rest of this component.
There is also a lack of examples, especially when you want to use both CFV5 and CCV5.

For example, I have a form with some research fields and, when the form is validated, I would display the result list using CCV5.
After a lot of research in the forum, I have succeed to show my list on the first page, but when I show the page 2, 3 ... the research fields are lost ! Why ? I don't know.
It will be a question in the forum when my post about display a field twice will be resolved !

showing the same field twice is easy and is just as I have explained earlier, assuming the field name is "field", just have this in the columns list:

model.field
model.field_copy

and in php functions

model.field_copy: return $row["field"];

Regards,
Max

Hello.
It's ok and it's works but the php function must be :

model.field_copy: return $row["model"]["field"];

Thanks a lot.
Regards,
Graaooor