Print Multiple Ids column data from one to other table in mysqli

[jasmine381]

hello i inserted data in to table A and get IDs from table A to table B in catid column like 1, 2, 3... so i want to print IDs values as per multiple ids on same row ,Here is my table and final output table

[upl-image-preview url=https://board.phpbuilder.com/assets/files/2020-01-14/1578989836-736965-tensess.png]

<?php
$sql = "SELECT t.tense_name, c.tenid, c.cat_name FROM tenses t, tensecategory c WHERE t.pktenseid=c.tenid";
$getcat = mysqli_query($conn, $sql);
$tenseinfo = [];
while ($row = mysqli_fetch_assoc($getcat)) {
if (!isset($tenseinfo[$row['tenid']])) {
$tenseinfo[$row['tenid']] = ['cat_name' => $row['cat_name'], 'tenses' => [$row['tense_name']]];
} else {
$tenseinfo[$row['tenid']]['tenses'][] = $row['tense_name'];
}
}
//var_dump($tenseinfo);
echo '<table>';
foreach ($tenseinfo as $catid => $data) {
echo '<tr><td>' . $catid . '</td>';
echo '<td>' . $data['cat_name'] . '</td>';
$tensesimpl = implode(', ', $data['tenses']);
echo '<td>' . $tensesimpl . '</td></tr>';
}
echo '</table>';
?>

[Weedpacket]

So... what is the problem you're having? I'm having trouble understanding what you mean by "i want to print IDs values as per multiple ids on same row". If the output you're getting is not what you want, then what do you want? What should it look like?

Do you mean you want those pktenseid values instead of the tense_name ones? Because that would just be a matter of selecting and using those instead.

[laserlight]

It looks like tenid really has multi-valued fields that consists of comma-separated ids. Don't do this. Rather, introduce a third table: each entry in this third table would have at least two columns: pktenseid and pkcategoryid. The combination of these two columns represent a tense in a category. This allows this third table to be used as a join table, hence you can more efficiently select say, all the tenses belonging to a particular category.

[dalecosp]

While I would tend to agree in most respects with laserlight about the comma-separated fields, I can see where for the sake of the operator UX, without a more complex GUI, it might be easier for the operator to use the comma-separated fields (therefore revealing a common programming issue: namely that we must write complex systems in order for them to appear simple for the end-user).

I can make your data work with this code. I note that the output "ID" appears to be arbitrary, so I created $x. You can create an array to act as a "lookup table" in order to get the names substituted into the "Selected Tenses" column.

$conn = mysqli_connect("localhost", "root", "", "test");

// get tense_names into an array

$tense_names = [];
$sql         = "select * from tenses;";
$q           = $conn->query($sql); // alternate, "OO" way for querying DB

while ($row = $q->fetch_assoc()) {

    $tense_names[$row['pktenseid']] = $row['tense_name'];
}

// get the data

$sql       = "SELECT t.tense_name, c.tenid, c.cat_name FROM tenses t, tensecategory c WHERE t.pktenseid=c.tenid";
$getcat    = mysqli_query($conn, $sql);  // this is also "okay"
$tenseinfo = [];

while ($row = mysqli_fetch_assoc($getcat)) {

    if (!isset($tenseinfo[$row['tenid']])) {

        $tenseinfo[$row['tenid']] = ['cat_name' => $row['cat_name'], 'tenses' => [$row['tense_name']]];

    } else {

        $tenseinfo[$row['tenid']]['tenses'][] = $row['tense_name'];
    }
}

//echo header row
echo '<table><tr><th>ID</th><th>Category Name</th><th>Selected Tenses</th></tr>\n';

$x = 1; //arbitrary counter?

// main loop, creation of the table data
foreach ($tenseinfo as $catid => $data) {

    echo '<tr><td>' . $x . '</td>';
    echo '<td>' . $data['cat_name'] . '</td>';

    $tense_nums = explode(',', $catid);  // separate your comma-values
    $string     = '';

    // look-up the tenses in our $tense_names array and concat them to our string
    foreach ($tense_nums as $lookup) {

       $string .= $tense_names[$lookup] . ", ";
    }

    echo '<td>' . rtrim($string, ",") . '</td></tr>';

    $x++;
}
echo '</table>';

[Weedpacket]

dalecosp

At which point you find yourself thinking that a DBMS that supports array types might be a nice thing to have

Select
	cat_name As "Category Name",
	array_to_string(Array(Select tense_name From tenses
						  Where pktenseid = Any(tenid)
						  Order By pktenseid), ', ') As "Selected Tenses"
From tensecategory
Order By pkcategoryid

[dalecosp]

@Weedpacket DBMS that supports array types

I suppose so, yes; do they? Do you have experience with Oracle GeoRaster or something of that ilk, or am I so far off-base with this question that I might as well keep my trap shut 😉 ?

[Weedpacket]

PostgreSQL, which weirdly doesn't include any analogue of array_map() to turn an array of values into a different array of different values (like, tenid values into tense_name values).

But then they note that

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

In an array, sequence order and repeated values are both significant ({1,2,3} is different from {1,3,2} and {1,1,2,3}); storing it as a table would mean an additional "position" column. If they're not significant, then they recommend using another table.