[RESOLVED] Nested Query problem

krotkruton

I'm not sure if I'm doing this incorrectly or if its a problem with my MySQL version which is 3.23.58. Since I can't seem to find any documentation on versions less than 4.0, I can't quite figure out this problem (if anyone knows of a link to older documentation, it would be appreciated as well).

To avoid lots of clutter, you'll just have to trust me that all these tables and values exist and that the nested SELECT query would return values that are 'id's... althought I'm not sure that that is the proper way to implement such a thing... because all the values stored as 'user' are just 'id' values from the userprofiles table.
I try to run this query:
$query_profiles = "SELECT * FROM userprofiles WHERE id = (SELECT user FROM friendspending WHERE friend = '$userid')"

Which crashes my page and displays the error:
You have an error in your SQL syntax near 'SELECT user FROM friendspending WHERE friend = '15')' at line 1

So the first question is, is it possible to do nested queries with 3.23 or do I have to get my host to upgrade. If I do need an upgrade, how ridiculous is it that my host is resistant to upgrading? I asked them before and was told "there is nothing we can do."

Any input is appreciated

superwormy

MySQL 3.23 does not support subselects. In fact, I don't think 4.x does either. 5.0 however, does support subselects.

If you're going with a cheapy host, it doesn't surprise me at all that they won't upgrade just for you. Get another host or deal with it. It shouldn't be hard to rewrite your scripts to avoid the subselects.

krotkruton

Thanks. I'm a little surprised because this host is pretty major, but what can ya do.

And it should be really easy to rewrite my scripts because I haven't written them yet. I just happened to see this subquery thing and thought it would work really well for my particular problem so I thought I'd try it out.

Could you provide an example of how to do it without subqueries? Or an example of anything that could be done with subqueries?

In my particular case, I have a relationship table of ID's that is a friends list, where each ID in the friends list is a primary key in my userprofiles table. Partially because I'm having a mind block right now but mainly because I don't know any better, I can't see any way to get the first and last names of an ID from my profile DB without doing a new SELECT for each result from the SELECT where a certain ID is in the friend's table.
...hope that was clear

Thanks again

-Edit

So then I got thinking about my host some more. Is 3.23 really outdated? I mean, is it like using MS Word 95 to do data processing or a 4 year old DreamWeaver to do stuff on the web so that you can still do what you need to do, but if you are serious about this stuff, you should really update your software.
I'm kinda trying to get a grasp of whether or not I should push them to update or if 3.23 is really a pretty good stable version of MySQL that I should pretty much be happy with, even though it isn't the best.

krotkruton

Wow, just happened on this while looking for an answer for someone else...
So this

$query_profiles = "SELECT * FROM `userprofiles` WHERE `id` = (SELECT `user` FROM `friendspending` WHERE `friend` = '$userid')"

is essentially the same as

$query_profiles = "SELECT * FROM `userprofiles`,`friendspending` WHERE userprofiles.id = friendspending.user AND friendspending.friend = '$userid'"

I had never realized how to do that...

superwormy

A lot of hosts use 3.23 still. Many hosts find its not worth the hassle to upgrade because it can break people's existing scripts / its a lot of work to upgrade the servers and make sure they dont' cause any problems in doing so.

Glad you figured it out with joins. You could also make two calls on mysql_query() and accomplish the same thing.