Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL Table Results to Process Another Table Results

    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

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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).

  • #3
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 '='

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •