Change table style when mysql result matches

[Jurtz]

Hello,

I have the following table which is created out of a foreach loop. The variables are coming from an extern API array. I´ve made a screenshot for better understanding. 

This the table:

foreach($data2['wow_accounts']['0']['characters'] as $key => $item) {
    echo '<tr>';
    echo '<td>';
    echo  $item['firstname'];
    echo '</td>';
    echo '<td>';
    echo  $item['lastname'];
    echo '</td>';
    echo '<td>';
    echo  '<button class="btnSelect">Select</button>';
    echo '</td>';
    echo '</tr>';
    }
    echo '</table>';

I also have a mysql table with some stored values. I also made a screenshot.

I want now, if the php foreach loop finds a match with the mysql column "lastname" this specific row should get the <tr class="my-additional-class">.

In this example the first row "Malygos" should have the <tr class="my-additional-class">.

I tried the following, but with no success. Could you help me please?

 foreach($data2['wow_accounts']['0']['characters'] as $key => $item) {
 $result = mysqli_query("SELECT lastname FROM mytable");

         if($result == $item['lastname']) {
         $class_string = '';
         } else {
        $class_string = ' class="my-additional-class"';
    }
        echo '<tr>';
        echo '<td>';
        echo  $item['firstname'];
        echo '</td>';
        echo '<td>';
        echo  $item['lastname'];
        echo '</td>';
        echo '<td>';
        echo  '<button class="btnSelect">Select</button>';
        echo '</td>';
        echo '</tr>';
}
        echo '</table>';

 

Edited September 18, 2020 by Jurtz

[requinix]

Putting a database query into a loop is almost always a bad idea.

Do one query ahead of time that looks up every record, then stuffs it into an array. You can use the firstname + lastname (that being the only unique (?) piece of information you can work with) as the array key, and for fun the entire row of data as the array value.
Then your foreach loop checks that array for a match.

[Barand]

5 hours ago, Jurtz said:

$result = mysqli_query("SELECT lastname FROM mytable");

Also note that the above code does not put the lastname into the $result  variable.

[Jurtz]

Yes, firstname and lastname are the only uniquie ones.

I improved the query now and played it outside of the foreach loop. But still don´t work. My try:

 

$link = mysqli_connect("localhost", "xxx", "xx", "xx");

$query = "SELECT firstname, lastname FROM mytable";

if ($result = mysqli_query($link, $query)) {
     while ($row = mysqli_fetch_assoc($result)) {

echo '<table class="formatHTML5" id="myTable">';
foreach($data2['wow_accounts']['0']['characters'] as $key => $item) {

  if($row["firstname"] == $item['name'] && row["lastname"] == $item['lastname'] ) {
         $class_string = '';
         } else {
        $class_string = ' class="my-additional-class"';
    }

    echo '<tr>';
    echo '<td>';
    echo  $item['firstname'];
    echo '</td>';
    echo '<td>';
    echo  $item['lastname'];
    echo '</td>';
    echo '<td>';
    echo  '<button class="btnSelect">Select</button>';
    echo '</td>';
    echo '</tr>';
    }
    echo '</table>';

 }
}

 

 

 

 

Edited September 18, 2020 by Jurtz

[requinix]

Check what I said again. Your query on the database (1) needs to set up an array that holds all the records in it so you can look in there for a match easily, and therefore (2) it has to happen before the foreach.

[Jurtz]

 

So I set the query now before the foreach like that.

    $connection = mysqli_connect("localhost", "xx", "xx", "xx") or die("Error " . mysqli_error());
    
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
    
       $sql = mysqli_query($connection, "SELECT * FROM mytable");
    
       while($row = mysqli_fetch_array($sql)) {
       $names[] = $row['firstname'];
    }

Quote

Then your foreach loop checks that array for a match.

Here I need some assistant. where is the mistake. Basically I have both arrays included now.

foreach($data2['wow_accounts']['0']['characters'] as $key => $item) {

  if($row["firstname"] == $item['name'] && row["lastname"] == $item['lastname'] ) {
         $class_string = '';
         } else {
        $class_string = ' class="my-additional-class"';
    }

    echo '<tr>';
    echo '<td>';
    echo  $item['firstname'];
    echo '</td>';
    echo '<td>';
    echo  $item['lastname'];
    echo '</td>';
    echo '<td>';
    echo  '<button class="btnSelect">Select</button>';
    echo '</td>';
    echo '</tr>';
    }
    echo '</table>';

 }
}

 

 

[requinix]

3 hours ago, Jurtz said:

So I set the query now before the foreach like that.

All you have in that array is the firstname. That's not unique. You need the first and last names.

Also, don't add them as array values. When it comes time to look up a value in there with in_array(), PHP will have to scan potentially the whole array to find a match. A faster method is to use array keys; put the whole $row as the value.

 

3 hours ago, Jurtz said:

Here I need some assistant. where is the mistake. Basically I have both arrays included now.

Well, for starters, I don't see anything trying to use $names in there.

Since you're going with the approach I said before about having the first and last names be the array key, you can use isset() to tell if there is an entry according to the name parts from $item.