Jump to content
Larry Ullman's Book Forums

A Query Within A Query


Recommended Posts

I am working on a site which uses the site modularization from Chapter 2 (thanks Stuart for the help with that). The problem I am having is that I need to run a second query within the "result set usage" of a prior query. What I mean is that I will run the first query, and then depending upon a boolean value from a field in the result set, I will run a second query. The problem is that once the second query is finished, I need to continue using the result set from the first query to construct a navigation menu in the footer.php file.

Here is pseudocode to illustrate:

1)  The first query is ran
2)  Some of the result set of the 1st query is used to generate page content
3)  Within a conditional (which checks a boolean result from the first query) a second query is ran
4)  The results of the second query are displayed
5)  The results of the first query are then used to build the nav. section

 

Specifically, within the navigation section I'm using:

if ($rows == 1)
{
...display link
}

That creates a huge conundrum since the $rows variable for the first query will be destroyed upon execution of the second query!

I imagine that I can put the result set of the first query (including a flag variable for "if ($rows==1)") into variables, then I can access them from anywhere in the page. Or, can I store the result set from the first query into a different variable than the result set of the second query? It just seems like there has to be a more efficient way!

 

Has anyone ran into this situation before? What would be the best way to get around the problem?

 

Thank you very much in advance for any help or advice,

 

Matt

Link to comment
Share on other sites

The easiest way would be to save the query in an array. That is what I do when I need a result set more than one time.

 

$query = "SELECT ....";
mysqli_real_escape_string($connect, $query);
$result = mysqli_query($connect, $query);

// create empty array and fill it...
$result_array = array();

while($fetch = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
   $result_array[] = $fetch; // fill array
}

/* USE RESULTS */

foreach ($result_array as &$value) {
  // use me
}

 

Some questions though:

- Can't you build the navigation before the second result set and display it later?

- Could you create one query out of two? Remember that num_rows() is also possible to do with MYSQL-functions like COUNT(*) as num or the likes.

Link to comment
Share on other sites

Thanks Larry and Antonio,

 

Basically, I am using the first query to populate a user profile page (first name, last name, pic, etc...), so it will return only one record each time.

The second query is based on a boolean field in the the result set of the first query. It also will return one record. The results of that record will be used before moving on to the nav. section, which will use a row count check from the first query to determine whether a link should be created or not.

 

I talked about this with HartleySan and he suggested creating a view of all tables involved and then performing one query. However, I think views are only appropriate where you need to perform multiple queries on a certain set of data and display it. They create a host of issues when you edit the underlying tables as well. I think that might be overkill for this situation.

 

I did come up with a "workable" solution, which was to store both queries in different result sets ($result1 and $result2). However, I tend not to like "workable" solutions as they are almost always also "half-ass" solutions!

 

The easiest way would be to save the query in an array.

Thanks Antonio, but wouldn't this be a more involved way of doing the same thing I did above with the two result set variables?

- Could you create one query out of two? Remember that num_rows() is also possible to do with MYSQL-functions like COUNT(*) as num or the likes.

Do you mean so I can iterate through the data? Each query will only return one record. Sorry for not being clear about that above!

 

Thanks guys,

 

Matt

Link to comment
Share on other sites

Matt, to clarify what I said, I said that the primary goal should be to reduce your two queries to one, and a view would potentially accomplish that (but I can't guarantee that, as I have never actually created a view).

 

However, if you're populating the nav area based on a flag value of sorts (for example, checking whether the user is viewing their own profile), I would think that that is sufficient enough to properly populate the nav area without a second query.

 

For example, if a user is not viewing their own profile, load certain links into the nav area, and if they are viewing their own profile, that add some additional links to the nav area.

 

Is a second query really necessary?

Link to comment
Share on other sites

However, if you're populating the nav area based on a flag value of sorts (for example, checking whether the user is viewing their own profile), I would think that that is sufficient enough to properly populate the nav area without a second query.

I'm not populating the nav. area with "a second query". I'm using a value from the first. (see above)

For example, if a user is not viewing their own profile, load certain links into the nav area, and if they are viewing their own profile, that add some additional links to the nav area.

I'm already doing that!

 

HartleySan,

 

I appreciate your trying to help, but I feel that things are getting a bit off target and I am not really in the frame of mind to start explaining myself. The problem is very straight forward.

 

Perhaps I should have explained it better, and I do apologize. What the first query is for is simply to grab the name of a person's page (this is for the gallery site I have been working on), the name of the file used for their logo, their first name, last name, and a boolean value. All of that is used in the header of the page and the boolean value is used in a conditional in the nav. menu.

 

The second query is used for the content on each page. The reason I can't use a view, or a single query, is because the query is different from page to page (for the person's profile, to display an image gallery, to display videos, etc...).

 

I just wanted to know what the "best practice" was for calling another query in the midst of using the result set from another query. I know the answer is probably very simple, and there are certainly many solutions, but I'm sure this situation is very common and I just wanted to know if there was good way of handling it.

 

Thanks,

 

Matt

 

@Antonio - I misunderstood what you were trying to tell me before. In this particular there's only one record returned, so I wouldn't even need an array, but your solution is perfect for more than one record and I will keep it in mind in the future! Thanks!

Link to comment
Share on other sites

Isn't the content of a page linked to the header info for that page? I would think that all that info would either be in the same table (depending on the complexity of the content) or very easy to connect across two or more tables using one or more joins.

 

Am I wrong?

 

And sorry, Matt, but what you are trying to accomplish was not clear to me in your original post, which is why I misunderstood the situation. Sorry.

Link to comment
Share on other sites

Jon (HartlySan)... good friend of mine... I'm banging my head against the desk here!

 

Isn't the content of a page linked to the header info for that page?

Only if you mean the "profile" page, which is the first one users see when they go to a gallery. That data could certainly all be pulled in the same query. However, you of anyone should know the layout of this, and when it comes to displaying all the images in a user's gallery, or videos, or a form to add images or videos, then entirely different queries are going to have to be used. 分た?

 

Here is a more in depth illustration of the problem.

 

Query to populate header:

 

$q = "SELECT galleries.gallery_id, galleries.user_id, galleries.gallery_name, galleries.logo, galleries.status FROM galleries WHERE galleries.directory_name='$dir'";
$r = mysqli_query ($dbc, $q);

The returned data is then used in the header.

 

In the "content" section of the page I would run a query like:

$q = "SELECT images.user_id, images.title, images.temp_name FROM images LEFT OUTER JOIN galleries ON galleries.user_id=images.id WHERE images.gallery_id='$gallery_id'";
$r = mysqli_query ($dbc, $q);

Now, in the nav. section, which is in the footer, I use the "status" field from the first query to set up a link.

 

Do you see the problem now? The two queries are vastly different in the information they are trying to gather, so a single query would be out of the question.

 

This isn't a difficult problem, by any means, but I just want to know how others handle it. I am starting to conclude that using 2 different variable names for each result set might be the only option.

 

Larry, Antonio, Stuart, what do you think?

Link to comment
Share on other sites

"分た?"

 

You can't honestly use incorrect Japanese like that and not expect me to comment, can you?

 

Anyway, you may want others to comment, but I'm still going to chime in.

 

I'm still not sure what you want to accomplish. I am assuming that on a given page, you're making one query for the info about the gallery and owner of that gallery, and another for the actual content (i.e., links to images to be displayed, etc.).

 

However, I still don't see why one join wouldn't accomplish this task. What is wrong with joining the galleries table and the images table where the session ID (or whatever) is equal to the user ID in the database? Is it really that hard?

 

If that's not a viable solution, please explain why, because otherwise, I think you're the one that is wrong.

 

If I am misunderstanding something, and you can clearly explain that, I will admit defeat, but until then, I am still confused as to why one join won't work.

Link to comment
Share on other sites

I'm probably missing something, but wouldn't it be enough to create a $gallery_status variable from the results of your first query, and then use its value for the creation of the navigation menu?

 

Sorry if I missed the point entirely! ;-)

Link to comment
Share on other sites

Josee, that's exactly what I thought the first time as well, but after having talked to Matt more in private, I *think* the situation is as follows:

 

There are galleries on a website, and each user can make their own gallery as well as view other people's galleries. At the top of the gallery is the basic info about the gallery. Below that are the images for that gallery, and below that is the nav section. The nav section is the same in all galleries, except for when you're logged in and viewing your own gallery, in which case, extra links are added for editing the gallery, etc.

 

Anyway, I think he has all the gallery info (minus the image info) in one table, and the image info in another table.

 

With that said, I think I am still missing something, as I proposed a simple join to get all the necessary info in one query. This seems reasonable, as the common factor is the user ID of the gallery being viewed.

 

However, Matt does not seem satisfied with my solution, so I am likely still misunderstanding something.

 

If you re-read Matt's posts, maybe you can glean something that I missed. Thanks.

Link to comment
Share on other sites

"分た?"

 

You can't honestly use incorrect Japanese like that and not expect me to comment, can you?

Jon,

Sorry to offend your Japanese sensitivities, but I don't know how to get small 'つ' on this keyboard!

 

However, I still don't see why one join wouldn't accomplish this task. What is wrong with joining the galleries table and the images table where the session ID (or whatever) is equal to the user ID in the database? Is it really that hard?

What are you, Accenture Consulting? Nowhere in my original post did I ask for you to reevaluate my query structure or redesign my site. Believe me, with an IT degree, 4+ years of PHP and MySQL experience under my belt, and having read 3 of Larry's books from cover to cover, I don' think I need you to tell me how many queries I should have on a page, nor how to rewrite them! If I do, you'll be the first one I ask (after Larry, Stuart, and Antonio, of course)!

If that's not a viable solution, please explain why, because otherwise, I think you're the one that is wrong.

Dude, doesn't someone in the Ajax forum need help?

 

Thank you very much Josee! That might just be the best option for my situation!

 

Matt

Link to comment
Share on other sites

Matt, you need to chill out.

 

I'm not even going to get into an argument. All I'm trying to do is help, and you continue to insult me while neither clarifying what it is you want to do nor explaining to me (nicely) why my join solution is not appropriate.

 

Like I said, if I'm wrong, tbat's fine, but as it stands, I really can't tell, because you refuse to explain anything.

Link to comment
Share on other sites

you continue to insult me

Really? I take the opposite to be true!

 

The offending line was: "If that's not a viable solution, please explain why, because otherwise, I think you're the one that is wrong."

 

You may talk to the new people in this forum that way (which I've repeatedly told you not to do), but you will not talk to me that way in a public forum! Why does everything have to be a debate with you? You sound like a 5 year old who is trying to prove to himself, and everyone else, that he is the smarter one in the class! Personally, I really don't care! I was simply asking a question and was trying to get an answer to it, but since you have managed to drag this thread down the toilet I guess it should end here!

 

As far as not explaining the problem well enough, I have tried to do so; not once, not twice, but three times! Josee seems to have understood it!

 

I want to apologize to Larry and everyone else for letting things get this far.

Link to comment
Share on other sites

Hey, guys. Relax. :)

 

Without knowing anyone of you, I can only assume Jon made suggestions to help you, Matt. You are obviously a smart guy, so why take this personally? Think you just lost each other here. No harm done, huh?

 

Going back on topic now. I actually tried to comment on this some days ago, but I could not fully picture what you wanted. With that in mind, I concluded myself that this is not a "one-big-query"-thing. The data seems related, but not how you are going to display the data. That is what I concluded with, but as I said, I tried waiting for someone smarter to comment.

 

In my experience, you join multiple tables/queries because it will make your life easier. I could, however, not think of any easy way to display this query as ONE without MORE logic.

 

Easy understandable code is often good code. If this code makes sense to you, Matt, it's probably a good way to do it.

 

If this, however, feels "wrong" in any way, I would have double-checked my table structures. I don't completely understand the use of this boolean-field. Anyway. Complete this script, and come back to it later if it needs improvement. It feels like you are feed up with this task. No need to bang your head against the wall over small things. :)

 

Just a little encouragement, Matt. You're a way better programmer than I am. I've seen a lot of your brilliant solutions when you've helped people out. You're really good.

Link to comment
Share on other sites

I want to apologize to anyone that came into this thread to read it, and got caught up in that quarrel between Matt and I.

 

Him and I are good friends, so quite often, I don't mind being a bit more abbrasive with him than with other users on the forum, but this time around, we were both failing to understand each other, and as a result, got rather rude with each other in a public forum, which was inappropriate.

 

The good news is that we have since spoken on the phone, and everything is okay now. Also, we were able to discuss the issue like gentlemen, and after much discussion, it would seem that there are multiple possible solutions to his issue.

 

Being Matt's site, he will of course make the final decision on what to do, at which point, I imagine he'll come back on here to report his findings.

 

Again, I apologize for the behavior on both of our parts.

  • Upvote 2
Link to comment
Share on other sites

Thank you, HartleySan, for your apology and this post. I very much appreciate it as I did not care for how this thread went.

 

And, I will add, that there are almost always multiple ways to solve a problem. And it's not always the case that some of those solutions are clearly and universally better than others.

  • Upvote 1
Link to comment
Share on other sites

  • 4 weeks later...

Hello and sorry for the late reply!

 

Jon and I did indeed "kiss" and makeup ;) We have a tendency to get at each other sometimes, and I was in a bit of a hurry trying to get a solution when I wrote this post, so my apologies again for being over abrasive with him.

 

@Antonio - Thanks for the help and the kind words (I get pretty unsure of myself sometimes and it's probably not a good thing)! What I ended up doing was creating a separate query as I had originally thought about. I called the result set from query one $result_1 and the result set from query two $result_2. Then I could use the data in them anywhere I wanted. I know it was a confusing situation and I apologize for that. I think the confusion comes because the site is modular, so the same page is being called each time with different content being loaded depending on which links were clicked. Also, there is additional content for a user who is logged in and viewing their page. Basically that second query will only be called when a user has that boolean variable set as true (it's a flag set by the admin. to approve the user so he can post content). I hope that doesn't sound more confusing than it already does!

 

Thanks again,

 

Matt

Link to comment
Share on other sites

 Share

×
×
  • Create New...