Random fecth and display data from Table

Hi to all,
I imported through phpmyadmin a db which I named *_chronoforms_quotazioni, and I'm trying to do the following by Chronoforms:

First of all I need to fetch all the records (except ID) of my db, but I need to display them in random order and in numbered groups of six rows. In other words I want to be sure that all the records are fetched and displayed, and that the results are not repeated in displaying. I tried to do this but I didn't succeed so far... Any suggestions?

Thanks to all.
Davide

Hi Davide,

You can probably do this with a Custom Code action using hand-coded MySQL and PHP. I think that MySQL has a RAND function but I'm not sure if you can apply this to the row ordering. The grouping I think has to be done in PHP.

Bob

I actually tried to use Custom Code and write down Php and MySql routines to do this, but with no luck. I think I should connect manually to the table, but I doubt about the method and the password to do it. My idea is to setup an array with all the data fetched and ordered, then randomize them through the array_rand() function, then display them in the way I described.

Thank you for all the suggestions, while I'm trying to do all of it by myself🙂

At the end I'll let you know🙂

Davide

Hi Davide,

That sounds OK.

What code are you using to read the data from the table?

Bob

Actually I began trying to construct a numeric random function without connecting the db, only to test how it works. After I didn't succeed in getting my desired results (ex.: numbers repeated twice, the first number never extracted), I thought the idea I posted before your answer. I'll try to construct it asap, and I'll let you know

Here the code I wrote down

$host = "http://www.podisticapomezianew.joomlafree.it";
$user = "myuser";
$pass = "mypassword";
$dbname = "jhost_chronoforms_data_quotazioni";

mysqli_connect("$host","$user","$pass") or die ("errore di connessione al db");
mysqli_select_db("$dbname") or die ("db errato o inesistente");

$query = "SELECT * FROM jhost_chronoforms_data_quotazioni"; /* recupera tutti i record */
$result = "msqly_query($query)";

$array=array();
while ($r = mysqli_fetch_array($result));
$array[] = $r[0];
print_r($array); /* prova il funzionamento prima di fare random */
?>

As you can see my first test is about db connection. This snippet, infact doesn't randomize the array. Anyway I got a db connection error. Is the connection method right?

Thanks
Davide

Hi Davide,

Please see this FAQ for making a connection to a second database from Joomla!.

Bob

PS your code has a typo 'myqly'

Thanks a lot, I got through the first step which was db connection. Thanks also for the typo of which I was aware by chance just a minute before reading your post🙂

Davide

Passed the first step, now the matter is displaying table data. Here below is the snippet I wrote down to do it:

$db2 = & JDatabase::getInstance($options);
$query = "SELECT * FROM jhost_chronoforms_data_quotazioni"; /* recupera tutti i record */
$db2->setQuery($query);
$data = $db2->loadObjectList();

$datarray=array($id,$cognome,$squadra,$ruolo,$quot_base);  /* the same names I used in the table */
while ($r = mysqli_fetch_array($datarray)); {
$datarray[] = $r[1];
print_r($cognome,$squadra,$ruolo,$quot_base);
}

In other words I need to display all table fields except ID. Where is my mistake, since this snippet doesn't behave as expected?

I thought, as an alternative, to fetch only ID in an array, then create a new array with randomized ID numbers and then set up a new query based on ID for displaying the records in random order. By the way, at last I have to display all the records, so this matter is going to come back unsolved later, if I do not solve it now.
Thanks a lot.

Davide

I tried so far but no result displayed...

Here below my last code:

<?php
//*inizializza db
$options = array();
$options['driver']    = 'mysqli'; // Database driver name
$options['host']      = 'www.podisticapomezianew.joomlafree.it'; // Database host name
$options['user']      = 'myuser'; // User for database authentication
$options['password']  = 'mypassword'; // Password for database authentication
$options['database']  = 'jhost_chronoforms_data_quotazioni'; // Database name

$db2 = & JDatabase::getInstance($options);
$query = "SELECT * FROM $options['database']"; /* recupera tutti i record */
$db2->setQuery($query);
$data = $db2->loadObjectList(); 

//$result = mysqli::query($query); /* I tried before these alternatives, I left them commented */
//$astarray=array('id','cognome','squadra','ruolo','quot_base');
//while ($r = mysqli_fetch_array($result)); {;
$astarray=array($data);
foreach ($astarray as $r => $v); {
$id = $r[0];
$cognome = $r[1];
$squadra = $r[2];
$ruolo = $r[3];
$quot_base = $r[4];
$tab = " ";
echo $cognome . $tab . $squadra . $tab . $ruolo . $tab . $quotbase . "\n";
}
?>

Thanks a lot for any help
Davide

I put a debugger on my form and I found that actually my array is always void. My snippet worsk as if it doesn't load any data from the table... What should I do to load my data?

Thanks
Davide

Actually I solved it by working with a txt file, and saving the results in a csv file. All done by custom code.

Thanks to all
Davide

Hi Davide,

Good to hear that you got this working. I did look at your code but it was such a mix of Joomla! and custom code that it wasn't easy to follow.

In a similar case where I needed to randomise an array last week I downloaded the data and then used the php shuffle() method to shuffle it. That worked neatly.

Bob

That is my final code, and I found that the simpliest way to do my work.

<?php
$text = file_get_contents('includes/quotazioni.txt'); /*recupera i dati del file */
$textArray = explode("\n", $text); /* crea un array delle righe */

foreach ($textArray as $n => $val) {
$ncas = array_rand($textArray); /* genera casuale */
$ris[$n] = $textArray[$ncas]; /* lo mette in un array */
}

/* prepara i dati per la scrittura in un file di testo */
$filename = "includes/quot_base.csv";
$handle = fopen($filename, "w"); /* apre il file in scrittura */ 

/* crea un array con tutte le righe risultanti */
foreach ($ris as $n => $val) {
echo $ris[$n] . "<br />";	
$data[] = $ris[$n] . "\n"; /* aggiunge ogni riga dell'array */
$target = $data[$n];
fwrite($handle, $target);
}
fclose($handle);

?>