0

I have to tables.

The first one (members) contains my customers id|name|email|key

The second (types) contains listings customer subscribes to id|customer_id|type|active

What I would like to do is to list all members that subscribed to a type of list. I can do this with 2 sql:s, but I guess there must be a better and faster way using som kind if JOIN maby and besides I get the wrong ORDER for my customers doing my way. I want ORDER by name.

<?
$type ='555';
$sql = mysql_query(" SELECT * FROM types WHERE type='$type' && active='1' ");
while($a = mysql_fetch_array($sql))
{
  $sql2 = mysql_query(" SELECT * FROM members WHERE id='{$a['customer_id']}' ");    
  while($b = mysql_fetch_array($sql2))
  {
    echo 'Name: '.$b['name'].'<br>';
  }
}
?>
Andy Lester
  • 91,102
  • 13
  • 100
  • 152
user3214817
  • 91
  • 5

3 Answers3

2
 mysql_query(" SELECT * FROM types INNER JOIN members ON types.customer_id = members.id WHERE type='$type' AND active='1' ORDER by members.name ASC");

This should do the trick for you

Chitowns24
  • 950
  • 5
  • 16
2
SELECT * FROM members 
JOIN types ON members.id = types.customer_id
WHERE types.type = ? AND types.active = '1'
ORDER BY members.name
AaronM
  • 2,339
  • 2
  • 17
  • 18
  • Thank you. I do'nt get the correct result but your code puts me the correct way so I have to trix arround a little bit :) – user3214817 Mar 03 '14 at 20:50
  • Sorry not a PHP dev, but you had a SQL question. I did miss the t.active in the original PHP code. – AaronM Mar 03 '14 at 21:02
  • Also, I refused to type the $type into the query as that is the creation of a security whole and will enable a SQL injection attack on your website if type comes from a url parameter. You should always use parametrized queries, using PHP is no excuse, I have written enough PHP to know that you can pass parameters, even if nobody does. That is why PHP is one of the largest exploited languages to date. Non-Parameterized queries also break the query cache and reduce performance. – AaronM Mar 03 '14 at 21:05
  • ok, but that's not the problem though :) ... And when asked the question I did a kind of translate from my correct (swedish table names) to make the "ask" more easy to read ... I guess I missed something when I put all togheter in my sctip that looks lie this: :) SELECT * FROM lunchmottagare JOIN lunchmottagartyp ON lunchmottagare.id = lunchmottagartyp.lunchmottagar_id WHERE lunchmottagartyp.typ = '1' && restaurang_kundnummer='{$_REQUEST['kundnummer']}' ORDER BY lunchmottagare.fornamn – user3214817 Mar 03 '14 at 21:08
  • Glad it is working for you now. I would really recommend replacing this restaurang_kundnummer='{$_REQUEST['kundnummer']}' with a parameterized query. As it stands someone could delete your database by simply passing kundnummer=';DROP+TABLE+lunchmottagare;', something along those lines. – AaronM Mar 03 '14 at 21:25
  • Yes, I'll do that thank you ... but my sql does not work as I would like :) ... It does not give me all the rows I now there is. I tryed the oposit and started with SELECT FROM lunchmottagartyp but I only get errors :) .. I have to try again tomorrow – user3214817 Mar 03 '14 at 21:37
  • Rows will be excluded that do not exist in both tables. If you change the JOIN to be LEFT OUTER JOIN, then it will give you all the rows from both tables with nulls in the fields for the other table when there is not a match. I do not think this is what you want, but it can be helpful in debugging the issue. The order of the JOINs really does not matter, but should not generate an error. – AaronM Mar 03 '14 at 22:49
  • Thank you, but changing to LEFT OUTER JOIN gives me the same result. I get 4 rows, but I now there's 21 rows – user3214817 Mar 04 '14 at 06:39
  • It works now .. my misstake. Thought I sent kundnummer as kundnummer but it was only "k". Thank you! – user3214817 Mar 04 '14 at 09:51
1

perhaps this:

SELECT m.name, m.email 
    FROM members m left join types t 
    ON m.id=t.customer_id 
    WHERE t.type='$type' and t.active='1';
deanosaur
  • 621
  • 3
  • 5