mySQL query help please

[iain sherriff]

Hi.
I have a .php based query that was posted in a specific mod thread.

Code Select Expand

<?php
$con = mysql_connect("db server name","db username","db password");

mysql_select_db("db name", $con);

$result = mysql_query("SELECT * FROM smf_hcb_newsletter");

echo "<table border='1'>
<tr>
<th>Email</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['email'] . "</td>";
  echo"</tr>";
  }
echo "</table>";

mysql_close($con);

?>


This works fine and gives me a table containing the emails.
I want add to that the membergroup associated with the email and the members real name.

Can anyone give me this coding adapted to add the other two queries ?

Thanks

[margarett]

You need to INNER JOIN smf_members and smf_membergroups, then picking the relevant fields.
I can try to check this for you later. Should be easy enough, I think...

[iain sherriff]

i would be very thankful if you could try............................. 

[margarett]

This should work:

Code Select Expand

<?php

// Include the SSI file.
require(dirname(__FILE__) . '/SSI.php');

$request = $smcFunc['db_query']('', '
SELECT n.email, m.real_name, m.id_group, mg.group_name
FROM {db_prefix}hcb_newsletter AS n
LEFT JOIN {db_prefix}members AS m ON m.email_address = n.email
LEFT JOIN {db_prefix}membergroups AS mg ON mg.id_group = m.id_group',
array(
)
);

echo "<table border='1'>
<tr>
<th>Email</th>
<th>Real Name</th>
<th>Membergroup</th>
</tr>";

while ($row = $smcFunc['db_fetch_assoc']($request))
{
  echo "<tr>";
  echo "<td>" . $row['email'] . "</td>";
  echo "<td>" . (empty($row['real_name']) ? '' : $row['real_name']) . "</td>";
  echo "<td>" . (empty($row['group_name']) ? '' : $row['group_name']) . "</td>";
  echo"</tr>";
}
$smcFunc['db_free_result']($request);


?>

[iain sherriff]

Thanks Bruno    works !

[iain sherriff]

getting a couple of errors on the page with the table

Code Select Expand

Strict Standards: Only variables should be passed by reference in /home3/f1162343/public_html/*********.com/Sources/Subs-UltimateMenu.php on line 19

Notice: SSI.php was unable to load a session! This may cause problems with logout and other functions - please make sure SSI.php is included before *anything* else in all your scripts! in /home3/f1162343/public_html/**********.com/SSI.php on line 170

but nothing shows in the forum error list so I guess it wont affect anything ?

[tectonny]

Resolveu de primeira Bruno, valeu mesmo.