random extraction from db

Overview

The issue occurs when attempting to use SQL's ORDER BY RAND() function within the form's data read settings, leading to syntax errors.
In the 'order by' field of the 'Read data' action, use a PHP return statement with a colon prefix before the RAND() function to ensure proper SQL formatting.

mc mcasamassima 23 May, 2017

I need to extract random records through the order by rand () function, how can i do it?

Max_admin 24 May, 2017

Hi mcasamassima,

Which version you are using ?

Best regards,
Max

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

mc mcasamassima 24 May, 2017

I'm using the latest version of ccv5

Max_admin 24 May, 2017

Hi mcasamassima,

Do you have the SQL syntax to do that ?

Best regards,
Max

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

mc mcasamassima 24 May, 2017

Hi, the syntax is select field from table where cond order by rand().
Thanks
Michele

Max_admin 24 May, 2017

Hi Michele,

Please try to use this code in the order by:


<?php
return array("rand()");

Best regards,
Max

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

mc mcasamassima 25 May, 2017

i receive this error
"1305 - FUNCTION operatori_economici_incarichi.rand does not exist"
Thanks Mike

Max_admin 25 May, 2017

Hi Mike,

Try this then:

<?php
return array(mt_rand() / mt_getrandmax());

Best regards,
Max

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

mc mcasamassima 25 May, 2017

Unfortunately it does not work...

Max_admin 29 May, 2017

Hi Mike,

Could you please try this code in the order box then:


<?php
return 0.369500624360052;

dos it work ?

Best regards,
Max

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

mc mcasamassima 29 May, 2017

i receive this error:
1064 - 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 '<?`operatori_economici_incarichi`.`php` `operatori_economici_incarichi`.`retur' at line 1

Thanks Mike

Max_admin 30 May, 2017

Hi Mike,

Then I think you have placed the code incorrectly ?

Bet regards,
Max

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

mc mcasamassima 30 May, 2017

Mistake me, but is it without the "array" right?
I think i only want the form "return array" within the field "order by"

mc mcasamassima 30 May, 2017

Answer

1 Likes

solved !! You have to put two points ahead!
in the order box :

<? Php
Return array (": rand ()");
?>

ra rafaelscouto 01 Feb, 2018

Good morning, I would like to take this topic. I need to create a lottery system, I have a database like this: ID, NAME, REGISTRATION.

I would like to return this data randomly, based on ID for example.

There will be five draws, so I need the data that is drawn to be repeated, how can I do that?

I'm using CC6

Gr GreyHead 01 Feb, 2018

HI rafaelscouto,
Please check this StackOverFlow answer
Bob

ra rafaelscouto 01 Feb, 2018

Thanks for the contact, but I have no idea how to resolve this.

ra rafaelscouto 01 Feb, 2018

Please, I need to resolve this.

Max_admin 01 Feb, 2018

Hi Rafael,
In the "read data", set the "select type" to "first matching record", then write the following code in the "offset" box:

{var:random}

Now add a "PHP" function, set the name to "random" and write this code:

return rand(0, 9); //assuming you have 10 records in the table

Best regards

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

ra rafaelscouto 01 Feb, 2018

I did exactly as you said, but only returns the 1st ID.
Here are pictures of how it is
https://imgur.com/a/kkRRZ

Max_admin 01 Feb, 2018

Hi Rafael,
Did you call the PHP function before calling the "Read data" in your event ?
Best regards

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

ra rafaelscouto 01 Feb, 2018

1 Likes

I made that change and it's working now, thank you very much.

But, you still have two doubts

1 - How do not show a value that has already been "drawn"?

2 - How to display 5 "draws" (5 lines) at the same time?

ra rafaelscouto 02 Feb, 2018

I was able to read the data randomly using an "x" table, now it needs to be different from data and saved in the "y" table, how can I do that?
I'm sorry for my English, I'm brazilian

ra rafaelscouto 02 Feb, 2018

Good afternoon, I've been able to solve these problems.
Now I need to know how to improve a loading screen. When clicking a button and displaying a loading screen, so it's just a matter of design

ra rafaelscouto 02 Feb, 2018

How to use "Task Button" or anywhre butom for loading screen or popup information?

Max_admin 04 Feb, 2018

Hi rafael,
The question is not clear, please give an example ?
Best regards

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

Max_admin 06 Feb, 2018

Hi Rafael,
The "Link" view has a dynamic setting which can be used to open a modal with dynamic content!
Best regards

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

This topic is locked and no more replies can be posted.

random extraction from db

How to extract random records from a database in ChronoForms.

Overview