PHP - SQL查询过滤器+

Question

I am working on a php script that will get the required information and display it in an xml. For some reason my brin isn't work and I don't know how to do the query. The database has two tables that I want to pull information from. Both tables have one thing in common which is 'id_member'. Themes table has a bunch of junk in it - so it has 4 coulms - 'id_member', 'id_theme', 'varible', and 'value'. There are two items in 'varible' that I want to filter (show them and not the rest) "cust_armaus" and "cust_armamo" then value will show the value of course. So in the table Themes it will have id_member listed more than once to show the different varibles. This is what I got that isn't working for me:

    <?php
header('Content-Type: text/xml');
$username="database_username";
$password="database_password";
$database="database_name";
mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

echo "<?xml version=\"1.0\"?>
".
     "<!DOCTYPE squad SYSTEM \"squad.dtd\">
".
     "<?xml-stylesheet href=\"squad.xsl?\" type=\"text/xsl\"?>
";
?>
<squad nick="Team Tag">
    <name>Team Name</name>
    <email>[e-mail]</email>
    <web>http://www.team.com</web>
    <picture>teamlogo.paa</picture>
    <title>This is the Team motto</title>
<?php

// smf_themes -> id_member = 
// smf_themes -> variable for cust_armaus & cust_usermo
// smf_themes -> value <- get for cust_armaus & cust_usermo
// smf_members -> real_name = profile_name & name
// smf_members -> email_address = email - NO

$memberSQL = mysql_query("SELECT id_member AS id, real_name FROM smf_members");
$member = mysql_fetch_array($memberSQL);
$armausSQL = mysql_query("SELECT id_member, variable, value AS arma_id FROM smf_themes WHERE id_member='$member[id]' AND variable='cust_armaus'");
$armaid = mysql_fetch_array($armausSQL);
$armamoSQL = mysql_query("SELECT id_member, variable, value AS motto FROM smf_themes WHERE id_member='$member[id]' AND variable='cust_usermo'");
$armamo = mysql_fetch_array($armamoSQL);

  $num=mysql_numrows($member, $armaid, $armamo);
  mysql_close();
  $i=0;

  while ($i < $num) {
   $profile_id = mysql_result($armaid,$i,"value");
   $profile_name = mysql_result($member,$i,"real_name");
   $profile_remark = mysql_result($armamo,$i,"value");
   $profile_username = mysql_result($member,$i,"real_name");
   //$profile_email = mysql_result($result,$i,"email");
   //$profile_icq = mysql_result($result,$i,"icq");

   echo "<member id=\"$profile_id\" nick=\"$profile_name\">" .
        "<name>$profile_name</name>".
        "<email>$profile_email</email>".
        "<icq>$profile_icq</icq>".
        "<remark>$profile_remark</remark>".
    "</member>
";

   $i++;
  }
?>
</squad>

Answer 1

The mysql interface is deprecated. New development should use either mysqli or PDO.

Assuming you only one one value of the cust_armaus and cust_usermo rows for each smf_members, you could use correlated subqueries in the SELECT list, and return just a single query that returns a single row for each row in smf_members. Something like this:

  SELECT m.id_member AS id
       , m.real_name
       , (SELECT a.value
            FROM smf_themes a
           WHERE a.id_member = m.id_member
             AND a.variable = 'cust_armaus'
           ORDER BY a.value LIMIT 1
         ) AS cust_armaus
       , (SELECT u.value
            FROM smf_themes u
           WHERE u.id_member = m.id_member
             AND u.variable = 'cust_usermo'
           ORDER BY u.value LIMIT 1
         ) AS cust_usermo 
    FROM smf_members m  
   ORDER BY m.id_member 

If the (id_member,variable) tuple is guaranteed to be unique in smf_themes, you could use a simpler outer join:

  SELECT m.id_member AS id
       , m.real_name
       , a.value AS cust_armaus 
       , u.value AS cust_usermo 
    FROM smf_members m
    LEFT  
    JOIN smf_themes a 
      ON a.member_id = m.member_id AND a.variable = 'cust_aramus'
    LEFT  
    JOIN smf_themes u 
      ON u.member_id = m.member_id AND u.variable = 'cust_usermo'
   ORDER BY m.id_member 

With either of those queries, you could use code something like this:

  $sql = "SELECT ...";
  $rs = $mysqli->query($sql)
  if (!$rs) {
    // handle error
    echo 'query failed: (' . $mysqli->errno . ') ' . $mysqli->error;
    die;
  }
  // loop through rows returned
  while ( $row = $rs->fetch_assoc() ) {
    echo '<member id="' . htmlspecialchars($row['id']) . '"'
       . ' nick="' . htmlspecialchars($row['real_name']) . '">'
       . '<cust_aramus>' . htmlspecialchars($row['cust_aramus']) . '</cust_aramus>'
       . '<cust_usermo>' . htmlspecialchars($row['cust_usermo']) . '</cust_usermo>' ;
  }