Combining Output of Multiple foreach loops php

Question

This Code, is to Select Online users and their user mode from an SQL database, add their status prefix (~, &, @, %, +) before their username in the php output.

<<  SQL Connection Data Goes here  >>

//Half Mod/Half Op

$hmodsinlistsql = mysql_query("SELECT user.nick FROM `ison` INNER JOIN `user` on
`ison`.`nickid` = `user`.`nickid` INNER JOIN `chan` ON `ison`.`chanid` =
`chan`.`chanid` WHERE chan.channel = '#Channel' AND user.nick NOT LIKE
'%bot%' AND user.nick NOT LIKE '%Serv%' and user.realname NOT LIKE
'%bot%' AND user.realname NOT LIKE '%Serv' AND user.nick NOT LIKE
'%STAT%' AND user.realname NOT LIKE '%EVA%APB%' AND ison.mode_lh = 'Y' AND ison.mode_lo = 'N'
AND user.away = 'N'");

//Voiced/Temp Mod

$tempmodsinlistsql = mysql_query("SELECT user.nick FROM `ison` INNER JOIN `user` on
`ison`.`nickid` = `user`.`nickid` INNER JOIN `chan` ON `ison`.`chanid` =
`chan`.`chanid` WHERE chan.channel = '#Channel' AND user.nick NOT LIKE
'%bot%' AND user.nick NOT LIKE '%Serv%' and user.realname NOT LIKE
'%bot%' AND user.realname NOT LIKE '%Serv' AND user.nick NOT LIKE
'%STAT%' AND user.realname NOT LIKE '%EVA%APB%' AND ison.mode_lv = 'Y'
AND user.away = 'N'");

while ($row = mysql_fetch_assoc($hmodsinlistsql)) {
 $rows[] = $row;
}

foreach($rows as $row) {

 echo '<tr><td>%' . $row[nick] .', </td></tr>';
}

Above Code gives me this:

%hmod2, %hmod3, %hmod4, %hmod1, %hmod5,

Desired Output:

%hmod2, %hmod3, %hmod4, %hmod1, %hmod5, +tmod1, +tmod2, +tmod3

My issue is that i can get the hmod results fine, however i'm not sure how to have multiple foreach loops on separate queries, and then merge the results into a single output string as desired above.

if anyone is able to help with this, that would be great.

You never merge the two search results, and you have only one loop that echoes the names. — Lorenz Meyer, Jul 17 '14 at 04:54

simeg · Accepted Answer · 2014-07-17T15:44:50.003

Put this:

while ($row = mysql_fetch_assoc($hmodsinlistsql)) {
 $rows[] = $row;
}

in between your querys and declare the array before the loops. Like this:

$rows = []; // Declaring an array correctly before trying to fill it

$hmodsinlistsql = mysql_query("SELECT user.nick FROM `ison` INNER JOIN `user` on
`ison`.`nickid` = `user`.`nickid` INNER JOIN `chan` ON `ison`.`chanid` =
`chan`.`chanid` WHERE chan.channel = '#Channel' AND user.nick NOT LIKE
'%bot%' AND user.nick NOT LIKE '%Serv%' and user.realname NOT LIKE
'%bot%' AND user.realname NOT LIKE '%Serv' AND user.nick NOT LIKE
'%STAT%' AND user.realname NOT LIKE '%EVA%APB%' AND ison.mode_lh = 'Y' AND ison.mode_lo = 'N'
AND user.away = 'N'");

while ($row = mysql_fetch_assoc($hmodsinlistsql)) {
 array_push($rows, $row); // Push the value to the array
}

//Voiced/Temp Mod

$tempmodsinlistsql = mysql_query("SELECT user.nick FROM `ison` INNER JOIN `user` on
`ison`.`nickid` = `user`.`nickid` INNER JOIN `chan` ON `ison`.`chanid` =
`chan`.`chanid` WHERE chan.channel = '#Channel' AND user.nick NOT LIKE
'%bot%' AND user.nick NOT LIKE '%Serv%' and user.realname NOT LIKE
'%bot%' AND user.realname NOT LIKE '%Serv' AND user.nick NOT LIKE
'%STAT%' AND user.realname NOT LIKE '%EVA%APB%' AND ison.mode_lv = 'Y'
AND user.away = 'N'");

while ($row = mysql_fetch_assoc($hmodsinlistsql)) {
 array_push($rows, $row); // Push the value to the array
}

foreach($rows as $row) {

 echo '<tr><td>%' . $row['nick'] .', </td></tr>'; 
}

So I do that and I am returned a blank page. Here is my full code excluding sql connection: http://pastebin.com/ZnYXZR9G — entropy, Jul 17 '14 at 05:08
I mistakenly did not declare the array correctly in my last answer, sorry. I fixed that and I also changed `$row[nick]` to `$row['nick']`. See my edit and if solved - please mark my answer as solved. If not - give me a shout and I'll give you more help to solve this :-) Also you should check out this http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — simeg, Jul 17 '14 at 15:43
This worked as expected after declaring the array properly! Thanks so much!! — entropy, Jul 18 '14 at 05:46

score 0 · Answer 2 · answered Jul 17 '14 at 04:58

I haven't tested it, but try this. You really need to stop using the deprecated mysql extension. Not only will you have more functionality, but you will have more security. And as you can see, if you use the procedural style syntax is nearly identical.

$connect = mysqli_connect("localhost", "user", "pw", "db");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

//Half Mod/Half Op

$query = "SELECT user.nick FROM `ison` INNER JOIN `user` on
`ison`.`nickid` = `user`.`nickid` INNER JOIN `chan` ON `ison`.`chanid` =
`chan`.`chanid` WHERE chan.channel = '#Channel' AND user.nick NOT LIKE
'%bot%' AND user.nick NOT LIKE '%Serv%' and user.realname NOT LIKE
'%bot%' AND user.realname NOT LIKE '%Serv' AND user.nick NOT LIKE
'%STAT%' AND user.realname NOT LIKE '%EVA%APB%' AND ison.mode_lh = 'Y' AND ison.mode_lo = 'N'
AND user.away = 'N'";

//Voiced/Temp Mod

$query .= "SELECT user.nick FROM `ison` INNER JOIN `user` on
`ison`.`nickid` = `user`.`nickid` INNER JOIN `chan` ON `ison`.`chanid` =
`chan`.`chanid` WHERE chan.channel = '#Channel' AND user.nick NOT LIKE
'%bot%' AND user.nick NOT LIKE '%Serv%' and user.realname NOT LIKE
'%bot%' AND user.realname NOT LIKE '%Serv' AND user.nick NOT LIKE
'%STAT%' AND user.realname NOT LIKE '%EVA%APB%' AND ison.mode_lv = 'Y'
AND user.away = 'N'";

/* execute multi query */
if (mysqli_multi_query($connect, $query)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($connect)) {
            while ($row = mysqli_fetch_row($result)) {
                $rows[] = $row;
            }
        foreach($rows as $row) {
            echo '<tr><td>%' . $row[nick] .', </td></tr>';
            }
            mysqli_free_result($result);
        }
    }
}

/* close connection */
mysqli_close($connect);

Combining Output of Multiple foreach loops php

2 Answers2