MySQL Table Results to Process Another Table Results

[amie]

Below you will find my code, it's not it all in it's entirety, but shows what's important. I have some custom functions that are being used just to note out. What I have my code doing is searching a MySQL table and displaying all of the results based on the current session user id. Then I loop through those results and for each one I search another table. This works perfectly, but I just want to see if there is a better way to do this without having to loop and search a table for every result row.

Code: http://pastie.org/5191376

[Fou-Lu]

Never query in a loop. That stacks (potentially) hundreds of queries for a single run.
Join it.

PHP Code:

$sQry = "SELECT p.shop_id, p.distributor_id, q.id, q.service, q.start_date, q.end_date
FROM participation p
INNER JOIN qpons q ON q.id = p.qpon_id
WHERE p.shop_id = '{$session->user_id}'"; 


Loop it and go. You'll need to set up any ordering and use PHP to do some magic if you need to change the visual layout (all the qpons per shop_id, distributor_id, etc).

[amie]

Looks like it would work but it then returns this:

Database query failed: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

[Fou-Lu]

Either change the database so it matches the collation across the board, or try to issue the COLLATE() SQL function to force it to match on the ON clause. I'd suggest modifying the db to match the collation and then adding a foreign key constraint assuming you have an innodb engine type and are using mysql.