CODEWITHSUNDEEP
phpmysqlsql
DigitalFirefly
DigitalFirefly

Reputation: 99

Issue with duplicate data in array and SQL

I have this function that reads the contents of an array, and outputs data form a mysql database into a table. However, when there are duplicated in the array, it only lists that information once. In the code below, it will only give me three table rows, 1, 100 and 140. It skips over the second instance of 1. How would I fix that to output the data into the table regardless of there being duplicates?

$con = mysql_connect('localhost', 'root', 'password');
$table = "cwr";
$player1pod1 = array(1, 1, 100, 140);

function generatelistplayer1 ($player1pod1, $con, $table){
    $sql = "SELECT * FROM $table WHERE id IN (".implode(',',$player1pod1).")";
    $myData = mysql_query($sql, $con);

    echo "<table border='1' cellpadding='2'> ";
        while($row = mysql_fetch_array( $myData )) {
            echo "<tr>";
            echo '<td>' . $row['id'] . '</td>';
            echo '<td>' . $row['cardname'] . '</td>';
            echo '<td>' . $row['rarity'] . '</td>';
        } 
    echo "</table>";

Desired output for the table is :

I'm getting :

Upvotes: 1

Views: 221

Answers (3)

Elad
Elad

Reputation: 84

You can print the result as many times as you need in a loop.

Notice its not a loop on the query. Query to db will be done only once, and I do not recommend a solution which do multiple queries or query same data more than once.

If order is not important i.e array(1, 100, 1, 140) = is good as = array(1, 1, 100, 140) , we can use array_count_values($player1pod1) and print each result as much as needed:

$con = mysql_connect('localhost', 'root', 'password');
$table = "cwr";
$player1pod1 = array(1, 1, 100, 140);

function generatelistplayer1 ($player1pod1, $con, $table)
{

    $counts = array_count_values($player1pod1);
    $sql = "SELECT * FROM $table WHERE id IN (" . implode(',', $player1pod1) . ")";
    $myData = mysql_query($sql, $con);

    echo "<table border='1' cellpadding='2'> ";
    while ($row = mysql_fetch_array($myData)) {

        for ($i=0;$i<count($counts[$row['id']]);$i++) {
            echo "<tr>";
            echo '<td>' . $row['id'] . '</td>';
            echo '<td>' . $row['cardname'] . '</td>';
            echo '<td>' . $row['rarity'] . '</td>';
        }
    }
    echo "</table>";
}

In case that the order is do important we can first collect data from the databse sql, and then to print it one by one, with "print_rows" function in my example. In this solution we don't need to count the values but we use some RAM to hold the sql results:

function generatelistplayer1 ($player1pod1, $con, $table)
{

    $sql = "SELECT * FROM $table WHERE id IN (" . implode(',', $player1pod1) . ")";
    $myData = mysql_query($sql, $con);

    //extract data
    $sql_data = array();
    while ($row = mysql_fetch_array($myData)) {
        $sql_data[$row['id']] = array( 'cardname'=> $row['cardname'],'rarity'=> $row['rarity']);
    }

    //print data
    print_rows($player1pod1,$sql_data);

}


function print_rows($player1pod1, $mysql_data)
{
    echo "<table border='1' cellpadding='2'> ";

    for($i=0;$i<count($player1pod1);$i++) {
        echo "<tr>";
        echo '<td>' . $player1pod1[$i] . '</td>';
        echo '<td>' . $mysql_data[$player1pod1[$i]['cardname']] . '</td>';
        echo '<td>' . $mysql_data[$player1pod1[$i]['rarity']] . '</td>';
    }

    echo "</table>";   
}

Upvotes: 0

aynber
aynber

Reputation: 23011

It's only going to give you 3 rows, because it's only going to give you unique rows. You can instead use a UNION query if you want actual duplicates.

"SELECT * FROM $table WHERE id = 1 UNION ALL SELECT * FROM $table WHERE id IN (".implode(',',$player1pod1).")";

If you want to use the array, you can create a loop:

$query = [];
foreach($player1pod1 as $pod) {
     $query[] = "SELECT * FROM $table WHERE id = $pod";
}

$sql = implode(" UNION ALL ",$query);

Upvotes: 1

Karoly Horvath
Karoly Horvath

Reputation: 96258

Fetch all the results first (into an array, key is the id), then based on your original list, display each result.

Upvotes: 0

Related Questions