How to get first_name and email from tl_formdata_details

[mhenke]

I'm trying to get an array with first_name, email from tl_formdata_details.

This table is not a regular table, Contao stores values in a way I have not seen before.

Then again, I'm not a top developer : )

I have tried all sorts of joins, and am even resorting to ugly nested queries, but still didn't figure it out.

This will list the values but it's ugly and it's still not in an array:

$query = "SELECT tl_formdata.id 

FROM tl_formdata ORDER BY tl_formdata.id";  

	$result = mysql_query($query);

while($row = mysql_fetch_array($result)){
	
	$id = $row[0];	
	
	$query1 = "SELECT value FROM tl_formdata_details WHERE ff_name = 'firstname' AND pid = ".$id.""; 
	$query2 = "SELECT value FROM tl_formdata_details WHERE ff_name = 'email' AND pid = ".$id.""; 

	$result1 = mysql_query($query1);	
	$result2 = mysql_query($query2); 	
	
while($row1 = mysql_fetch_row($result1)){
	
	$id1 = $row1[0];	
	
echo $id1.' - ';
	} 
	
	while($row2 = mysql_fetch_row($result2)){
	
	$id2 = $row2[0];	
	
echo $id2.'</br>';
	} 	

	}

Does anyone know how to solve this?

I have added a screen of the MyPHPAdmin table.

[punkstjimmy]

With the way it's stored it's going to be difficult to get the data out of the database as an associative array. It might be better to get the data out of the database, then create the array with PHP.

One possible way to do it is like this:

<?php 

// Get all the rows
$query = 'SELECT ff_name, value, pid FROM tl_formdata_details';

$result = mysql_query($query);

// Loop through the result
while($row = mysql_fetch_array($result))
{
      // If it's a name field assign the value to the 'firstname' key of our array
      if($row['ff_name'] == 'firstname')
      {
             // Use the PID as the main array key so that the right username and email are grouped together
             $arrData[ $row['pid'] ]['firstname'] = $row['value'];
      }

      // If it's an email field assign it to the email key of the array
      if($row['ff_name'] == 'email')
      {
              // Use the PID as the main array key so that the right username and email are grouped together
              $arrData[ $row['pid'] ]['email'] = $row['value'];
      }

}

?>

$arrData data is now an associative array that looks something like this:

array(
     [5] => array('firstname' => 'Jim', 'email' => 'jim@email.com'),
     [3] => array('firstname' => 'Bill', 'email' => 'billy@email.com'),
     [8] => array('firstname' => 'Sally', 'email' => 'sally@email.com')
);

The number being the PID. Also if you're doing this inside of Contao you'd be much better off using the Database class (http://api.contao.org/v3/classes/Contao.Database.html) to run your query than mysql functions.

Maybe someone knows how to do this directly with SQL, but this is how I'd try it.