How to join fields to their array of values?

mahershalalhashbaz37 · February 5, 2018, 7:10pm

The first while has all the field/column names and the 2nd while has the
values. But how can I join the fields so that they represent to their
array of values?

[code] $showTables = "SHOW COLUMNS FROM ".$dbTable11;
if($output = $conn->query($showTables)){
$fields = array();
//while($result = mysqli_fetch_assoc($output)):
while($row = $output->fetch(PDO::FETCH_ASSOC)){
$fields = $row[‘Field’];
}
}
echo ‘

’;

print_r($fields);

echo ‘

’;

$sql1 = "SELECT * FROM ".$dbTable11." WHERE book='".$getBook."' AND chapter='".$getC1."'";
$result1 = $conn->query($sql1);
while($row = $result1->fetch(PDO::FETCH_ASSOC)) {
    if($row['text_data']!=NULL){
        
        $id[] = $row['id'];
        $book[] = $row['book'];        
        $bookTitle[] = $row['book_title'];
        $recordType[] = $row['recordType'];        
        $chapter[] = $row['chapter'];
        $verse[] = $row['verse'];
        $bookSpoke[] = $row['book_spoke']; 
        $chapterSpoke[] = $row['chapter_spoke']; 
        $verseSpoke[] = $row['verse_spoke'];
        $textData[] = $row['text_data'];        
        $strongs[] = $row['strongs'];
        $transliteration[] = $row['transliteration'];
        $etymology[] = $row['etymology'];
        $etymStrongs[] = $row['etym_strongs'];
        $etymDesc[] = lcfirst($row['etym_desc']);
        //$hebWord[] = hebrevc($row['heb_word']);
        $hebWord[] = $row['heb_word'];
        $greek_word[] = $row['greek_word'];
        $added[] = $row['added'];
        $also[] = $row['also'];

/* $fields[0] = $row[‘id’];
$fields[1] = $row[‘book’];
$fields[2] = $row[‘book_title’];
$fields[3] = $row[‘recordType’];
$fields[4] = $row[‘chapter’];
$fields[5] = $row[‘verse’];
$fields[6] = $row[‘book_spoke’];
$fields[7] = $row[‘chapter_spoke’];
$fields[8] = $row[‘verse_spoke’];
$fields[9] = $row[‘text_data’];
$fields[10] = $row[‘strongs’];
$fields[11] = $row[‘transliteration’];
$fields[12] = $row[‘etymology’];
$fields[13] = $row[‘etym_strongs’];
$fields[14] = lcfirst($row[‘etym_desc’]);
//$hebWord = hebrevc($row[‘heb_word’]);
$fields[15] = $row[‘heb_word’];
$fields[16] = $row[‘greek_word’];
$fields[17] = $row[‘added’];
$fields[18] = $row[‘also’]; */
}
}

        $fields[0] => $id;
        $fields[1] => $book;        
        $fields[2] => $bookTitle;
        $fields[3] => $recordType;        
        $fields[4] => $chapter;
        $fields[5] => $verse;
        $fields[6] => $bookSpoke; 
        $fields[7] => $chapterSpoke; 
        $fields[8] => $verseSpoke;
        $fields[9] => $textData;        
        $fields[10] => $strongs;
        $fields[11] => $transliteration;
        $fields[12] => $etymology;
        $fields[13] => $etymStrongs;
        $fields[14] => etymDesc;
        $fields[15] => $hebWord;
        $fields[16] => $greek_word;
        $fields[17] => $added;
        $fields[18] => $also;

[/code]

droopsnoot · February 5, 2018, 7:38pm

Could you retrieve the rows using a numeric array instead of a named one? Use PDO::FETCH_NUM.

Can you explain in a bit more detail what you want to achieve?

mahershalalhashbaz37 · February 5, 2018, 10:47pm

I want to be able to lay the table trs and tds with a for loop:

    $string = "";
    $string .= "<tr>";
    for($i=0; $i<count($fields); $i++){
        $string .= "<th id=\"th_".$fields[$i]."\" style=\"background-color: #a9a9a9; border: 1px solid #7A1010; height: 118px; padding: 10px 10px 10px 10px; width: 169px;\" valign=\"top\">";
        $string .= $fields[$i];
        $string .= "</th>\n";
    }
    $string .= "</tr>";
    for($i=0;$i<count($fields[0]);$i++){
        $string .= "<tr>";
        $string .= "<td id=\"td_".$fields[$i]."_".$i."\" style=\"background-color: #a9a9a9; border: 1px solid #7A1010; height: 118px; padding: 10px 10px 10px 10px; width: 169px;\" valign=\"top\">";
        $string .= "Book = <br /><br />";
        $string .= "<input id=\"txt_".$fields[$i]."_".$i."\" name=\"".$fields[$i]."_".$i."\" type=\"text\" value=\"".$fields[$i]."\" />";
        $string .= "</td>";
    }

I want to do something similar to phpadmin with my own style.

mahershalalhashbaz37 · February 5, 2018, 11:40pm

This doesn’t work either as the answer returns NULL:

    $string = "";
    $string .= "<tr>";
    for($i=0; $i<count($fields); $i++){
        $string .= "<th id=\"th_".$fields[$i]."\" style=\"background-color: #a9a9a9; border: 1px solid #7A1010; height: 118px; padding: 10px 10px 10px 10px; width: 169px;\" valign=\"top\">";
        $string .= $fields[$i];
        $string .= "</th>\n";
    }
    $string .= "</tr>";
    for($i=0;$i<count($fieldsArray);$i++){
        $string .= "<tr>";
        var_dump($fieldsArray["'".$fieldsArray[$i]."'"]);
        for($j=0;$j<count($fieldsArray["'".$fieldsArray[$i]."'"]);$j++){
            $string .= "<td id=\"td_".$fieldsArray[$i]."_".$i."\" style=\"background-color: #a9a9a9; border: 1px solid #7A1010; height: 118px; padding: 10px 10px 10px 10px; width: 169px;\" valign=\"top\">";
            $string .= $fieldsArray["'".$fieldsArray[$i]."'"]." = <br /><br />";
            $string .= "<input id=\"txt_".$fieldsArray[$i]."_".$i."\" name=\"".$fieldsArray[$i]."_".$i."\" type=\"text\" value=\"".$fieldsArray[$i][$j]."\" />";
            $string .= "</td>";
        }
        $string .= "</tr>";
    }

droopsnoot · February 6, 2018, 10:04am

I don’t know where $fieldsArray[] came from, it’s not in the code you posted so it’s difficult to figure out why that might be a problem.

I think what I would do is: run the query, retrieve the data into $row using PDO::FETCH_ASSOC as you have been doing, but for the first row on a screen page I would iterate through the array using a foreach() loop something like:

if ($first == true) { // we're on the first line of the new page
  echo "<tr>";
  foreach($row as $key=> $value) { 
    echo "<th>" . $key . "</th>"; // output the column names
    }
  echo "</tr>";
  $first = false; // only do this bit once
}

then draw the remainder of the table using just the data, so do the same foreach() loop but using the $value value instead.

I’m not 100% sure of the order that the data will come back in within each row, but I suspect it would be consistent. You could experiment with that.

mahershalalhashbaz37 · February 7, 2018, 10:18pm

THis is the definition:$fieldsArray = Array(0 => Array('id', $id), 1 => Array('book', $book), 2 => Array('book_title', $bookTitle), 3 => Array('recordType', $recordType), 4 => Array('chapter', $chapter), 5 => Array('verse', $verse), 6 => Array('book_spoke', $bookSpoke), 7 => Array('chapter_spoke', $chapterSpoke), 8 => Array('verse_spoke', $verseSpoke), 9 => Array('text_data', $textData), 10 => Array('strongs', $strongs), 11 => Array('transliteration', $transliteration), 12 => Array('etymology', $etymology), 13 => Array('etym_strongs', $etymStrongs), 14 => Array('etym_desc', $etymDesc), 15 => Array('heb_word', $hebWord), 16 => Array('greek_word', $greek_word), 17 => Array('added', $added), 18 => Array('also', $also));

John_Betong · February 8, 2018, 12:35am

Try this when displaying array contents:

echo '<pre>';
  print_r( $arrayName );
echo '</pre>';

Also try var_dump($arrayName);

Drummin · February 8, 2018, 1:45am

Would something like this work? Completely untested.

$showTables = "SHOW COLUMNS FROM ".$dbTable11;
    if($output = $conn->query($showTables)){
        $fields = array();
        //while($result = mysqli_fetch_assoc($output)):
        while($row = $output->fetch(PDO::FETCH_ASSOC)){
            $fields[] = $row['Field'];
        }
    } 
	/*
    echo '<pre>';
    print_r($fields);
    echo '</pre>';
	*/ 	   
$i=0;
	$data = array();
	$sql1 = "SELECT * FROM ".$dbTable11." WHERE book='".$getBook."' AND chapter='".$getC1."'";
	$result1 = $conn->query($sql1);
	while($row = $result1->fetch(PDO::FETCH_ASSOC)){
		if($row['text_data']!=NULL){ 
			$data[$i] = $row;
			$i++; 
		}
	}
	//////////////////////////////////// 
	$string = "";
	if(!empty($fields) && !empty($data)):
		$string .= '<table border=0>
		<tr>'."\r";
			foreach($fields as $f):
				$f = strtoupper(str_replace("_"," ",$f));
				$string .= '<th>'.$f.'</th>'."\r";
			endforeach;
			$string .= '</tr>'."\r";	
				
			foreach($data as $i => $arr):	 
				$string .= '<tr>'."\r";
				foreach($fields as $f):
					$string .= '<td>'.$data[$i][$f].'</td>'."\r";
				endforeach;	
				$string .= '</tr>'."\r";
			endforeach;	
		$string .= '</table>'."\r";
	endif;

Sorry if TAB looks very indented.

system · May 10, 2018, 8:46am

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Joining arrays PHP	2	278	February 27, 2011
Lists from tables - arrays? PHP	10	888	April 29, 2011
Field names in an array PHP	6	12292	October 8, 2014
FETCH an array with key-value pairs PHP	5	5655	April 17, 2015
Joining Tables fields display issues PHP	1	460	April 5, 2011

How to join fields to their array of values?

Related topics