[PHP] mysql query (LEFT JOIN and LIKE)

software development

#1

Hello, I’m pretty new at this stuff but what I’d like to do is search two tables that are joined with a LEFT JOIN. The second table is used to store an array.

$sql = "SELECT study.*, sclass.* FROM study LEFT JOIN sclass ON (study.studyID = sclass.studyID) WHERE study.Stitle LIKE '%$search%' OR study.Speaker LIKE '%$search%' OR study.Location LIKE '%$search%' OR study.Description LIKE '%$search%' OR sclass.Ctitle LIKE '%$search%'";

This works pretty well for me but when a sclass.Ctitle matches then it only gives me that entry in the array. What I would like is when one Ctitle matches that it returns the entire array that corresponds to that studyID. Is there any way to do this? Thanks!


#2

I’m not sure I completely understand what you mean, so let me ask a couple questions to help me try to figure it out. When you talk about it returning an “entire array,” what do you mean by that, exactly? Are you saying that it doesn’t return any of the study.* information like it should? Or are you saying that you want it to return all of the sclass entries that are attached to the study entry that the particular sclass.Ctitle belongs to?

If you’re talking about the former, then this query should do that, and we’ll need a bit more information as to what’s going wrong in order to help. In particular, what does the query actually return? Can you give us a representative sample?

If you mean the latter, then the most straightforward thing to do is use two queries. Use the query you’ve given to simply generate a list of studyID results that match your criteria and then use a second query to actually retrieve the information associated with the studyIDs.

The only other option I can think of for the latter, though I haven’t tested it and won’t guarantee that it actually does what I think it will, is to change the SELECT to a SELECT DISTINCT study.*, sclass2.* and add JOIN sclass sclass2 ON (sclass.studyID = sclass2.studyID) to your query. By joining the sclass table back on itself, you get a list of all classes with the same studyID for each class which matches your WHERE clause, including the class which matched initially. Making the query DISTINCT stops you from getting a lot of duplicate results.

If you meant still something different, please try to describe a little bit more what you’re after.

Hope this helps!