[Phpwm] mysql in() lists

[David Long]

Phil Beynon wrote:
> Is there a more efficient way of making in() list sql other than
> concatenating them together?
>   

Without sample data I'm not sure I've got this quite right, but you can 
do this in a single SQL query, even in MySQL 4.0.

You're looking for user_names from Type_B_Image_Tracking that match 
'%DUMMY_IMAGE.jpg' but do not already appear in the imageasset table, 
correct? If so, here's the sample datasets I used: (apologies for the 
ASCII art, this should be viewed in a fixed-width font)

mysql> SELECT * FROM Type_B_Image_Tracking;
+-------------------+
| user_name         |
+-------------------+
| 1_DUMMY_IMAGE.jpg |
| 2_DUMMY_IMAGE.jpg |
| 3_DUMMY_IMAGE.jpg |
+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM imageasset;
+-------------------+
| user_name         |
+-------------------+
| 1_DUMMY_IMAGE.jpg |
| 2_DUMMY_IMAGE.jpg |
| 4_DUMMY_IMAGE.jpg |
+-------------------+
3 rows in set (0.00 sec)

This query returns just 3_DUMMY_IMAGE.jpg:

mysql> SELECT Type_B_Image_Tracking.user_name
    -> FROM Type_B_Image_Tracking
    -> LEFT JOIN imageasset ON Type_B_Image_Tracking.user_name = 
imageasset.user_name
    -> WHERE Type_B_Image_Tracking.user_name LIKE '%DUMMY_IMAGE.jpg'
    -> AND imageasset.user_name IS NULL;
+-------------------+
| user_name         |
+-------------------+
| 3_DUMMY_IMAGE.jpg |
+-------------------+
1 row in set (0.00 sec)

The LEFT JOIN and NULL trick is useful to find rows in one table that 
don't exist in another without using subselects. By switching to SELECT 
* and removing the "IS NULL" part, we can see more clearly how the query 
works:

mysql> SELECT *
    -> FROM Type_B_Image_Tracking
    -> LEFT JOIN imageasset ON Type_B_Image_Tracking.user_name = 
imageasset.user_name
    -> WHERE Type_B_Image_Tracking.user_name LIKE '%DUMMY_IMAGE.jpg';
+-------------------+-------------------+
| user_name         | user_name         |
+-------------------+-------------------+
| 1_DUMMY_IMAGE.jpg | 1_DUMMY_IMAGE.jpg |
| 2_DUMMY_IMAGE.jpg | 2_DUMMY_IMAGE.jpg |
| 3_DUMMY_IMAGE.jpg | NULL              |
+-------------------+-------------------+
3 rows in set (0.00 sec)

The query takes all rows from the Type_B_Image_Tracking that match the 
given filename and tries to find a matching row in imageasset. If there 
is no match, NULL is used instead.

In cases like this, it's often more readable and more efficient to let 
the database do all the work in one go, rather than generating dynamic 
queries from data that already exists in the database.

Cheers,

Dave