Get an value from IN clause query

Question

I am trying to find a way to get a value from the IN clause in my query. The column I am looking for is "viewURL" in the table "contentAvailability".

$availableProvidersSQL = mysql_query("SELECT * FROM contentProviders 
                WHERE 
                code IN (SELECT providerCode FROM contentAvailability WHERE contentGuid = '".($row['guid'])."')
                ORDER BY firendlyName") or die(mysql_error());

                while($availableProvidersRow = mysql_fetch_array($availableProvidersSQL)) {

                        echo '<div class="col-lg-12 col-md-12 col-sm-6 col-xs-6" style="padding-bottom:7px;">
                        <figure>
                        <a href="#"><img src="'.$baseURL.'images/providers/'.$availableProvidersRow['code'].'/Logo.jpg" class="providerLink"></a><br />
                        </figure>
                        </div>';

                }

Any help would be greatly appreciated

The question is a tad unclear. Could you share your tables structures, some sample data, and the result you'd like to get for that sample? — Mureinik, Aug 06 '16 at 16:17
Please dont use [the `mysql_` database extension](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the `PDO` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) its really pretty easy — RiggsFolly, Aug 06 '16 at 16:20

Alex · Accepted Answer · 2016-08-06T16:47:02.370

If the column contentGuid is common to both contentAvailability and contentProviders, then why not use a JOIN?

SELECT 
    pv.*, 
    av.viewUrl 
 FROM contentAvailability av 
 JOIN contentProviders pv ON pv.contentGuid = av.contentGuid AND av.providerCode = pv.code 
WHERE av.contentGuid = $row['guid']
ORDER BY av.friendlyName

If contentGuid is NOT common to both tables, then you can do a JOIN like this:

SELECT 
    pv.*, 
    av.viewUrl 
 FROM contentAvailability av 
 JOIN contentProviders pv ON av.providerCode = pv.code    
WHERE av.contentGuid = $row['guid']
ORDER BY av.friendlyName

Please let me know if there's something I'm missing.

Edited to show case where contentGuid is not common to both tables.

score 0 · Answer 2 · edited May 23 '17 at 12:22

This query has got a little confused, this should work

$availableProvidersSQL = mysql_query(
           "SELECT * FROM contentProviders 
            WHERE 
            code IN (
                      SELECT providerCode 
                      FROM contentAvailability 
                      WHERE contentGuid = '{$row['guid']}'
                     )
            ORDER BY firendlyName") 
    or die(mysql_error());

Remember if you are using a double quoted string scalar variables are simply expanded to their value, but when using an array you either have to forget the quotes around the array occuranec like " $row[guid] " or use {} around the array like I have above.

But

Please dont use the mysql_ database extension, it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the PDO database extensions. Start here its really pretty easy

JOIN would be great, But I answered the questions as asked. OP is obviously learning, so I decided not to blast them with options and confuse. — RiggsFolly, Aug 06 '16 at 16:33
@Strawberry I imagined OP was on the chapter that was teaching them about the `IN()` clause, therefore an answer suggesting the use of JOIN would just be irrelevant — RiggsFolly, Aug 06 '16 at 16:38

Strawberry · Answer 3 · 2016-08-06T16:49:29.103

0

SELECT x.columns
     , x.I
     , x.actually
     , x.want
  FROM contentProviders x
  JOIN contentAvailability y
    ON y.providerCode = x.code       
 WHERE y.contentGuid = '{$row['guid']}'
 ORDER 
    BY x.firendlyName;

Note that 'firendly' isn't a word.

edited Aug 06 '16 at 16:49

answered Aug 06 '16 at 16:41

Strawberry

33,750
13
40
57

Get an value from IN clause query

3 Answers3