I am very new to php/mysql and have inherited serveral sites that use both -extensively. I am giving my self a crash course and moving along well...until now. I have been working on this issue for about a week (solid) and have decided to finally ask...someone - I haven't been able to find anything online anywhere that is specfic enough to my problem. My hosting company offers PHP 4.0 & 5.0 however I currently have 4.0 enabled as I am skeptical to use 5.0 since my webage(s) are older and I don't need any major breaks until I learn more. The previous designer wasn't an advocate of WC3 validation and was not attention-to-detail oriented, so to me, most of the site feels like a house-of-cards. To the issue....

This query *works PEFECTLY in the MySQLAdmin interface:

SELECT r.id, r.type, r.name, r.street, r.city, r.state, r.zip, r.phone, r.accounttype, bl.relation, bl.username AS business_listing_username, url.url, url.username AS url_username, c.optionvalue, c.name AS category_name
FROM restaurant AS r
LEFT OUTER JOIN business_listing AS bl ON r.id = bl.relation
LEFT OUTER JOIN url_mapping AS url ON bl.username = url.username
RIGHT JOIN category AS c ON r.type = c.optionvalue
WHERE r.city LIKE 'citynamehere'
ORDER BY `r`.`name` ASC

This *works PEFECTLY in PHP on the webpage:

"SELECT r.id, r.type, r.name, r.street, r.city, r.state, r.zip, r.phone, r.accounttype, bl.relation, bl.username AS business_listing_username, url.url, url.username AS url_username, c.optionvalue, c.name AS category_name
FROM restaurant AS r
LEFT OUTER JOIN business_listing AS bl ON r.id = bl.relation
LEFT OUTER JOIN url_mapping AS url ON bl.username = url.username
RIGHT JOIN category AS c ON r.type = c.optionvalue
WHERE r.city LIKE 'citynamehere'";

Once I add the ORDER BY to the PHP on the webpage IN ANY FORM, regardlesss of backticks, alias names, or any other combination I have tried, the website displays NOTHING (yes my text is a different color than the background). There are no results, no errors, just a blank. I am ready to lose my mind. Other PHP scripting on the page has been removed, just in case there was a conflict somewhere, but no other unrelated scripting is contained in my <?php tags.

I have NEVER posted on a forum before so I don't really know what other information may be required to solve this problem, other than the PHP query WORKS until I add the ORDER BY. What the query is doing is rather complicated in my mind, but does exactly what I am asking it to do.

Any help would be greatly appreciated - I fear I will never grasp this stuff.

Thanks!
JB

*Works PEFECTLY = ALL data prints out as anticipated.

When you say there are no errors, did you check mysql_error()? PHP won't print MySQL errors by default.

Try printing the result of mysql_error() after executing the sql query and see what you get.

Also it sometimes smart to put the query into a variable and echo it

Sometimes you will miss stupid things like a $ or something that changes the actual text.

Anderswc & sjenkins,
Thank you for the response. When I check mysql_error() this is returned.

"The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay"

This error was being returned earlier in my testing of the JOIN's in the MySQLAdmin interface. However, I was able to write the query to "not-do-this." In the interface when I click on the r.name column heading, the virtual table is sorted accordingly and ADDS the ORDER BY statement to the query in the SQL code box.

Why would it function correctly there -and then error on the actual page?

Okay,
Going to try and back up a bit and ask this question a different way...

Here's the query with a bit of explanation and it all works fine.

$query="SELECT
r.id, <-match #1
bl.relation, <-match #1

bl.username AS business_listing_username, <-match #2
url.username AS url_username, <- match #2

url.url, <-this is what I am really after in the 1st & 2nd following LEFT OUTER JOINs, but there isn't a connect from r.* to url.* which is why I must "go-through" the bl.* table to get there...hence 2 LEFT OUTER JOIN's because I don't want to exclude the NULL's. I need them all, but some restaurants (r) don't have url's.

r.type,
r.name,
r.street,
r.city,
r.state,
r.zip,
r.phone,
r.accounttype

FROM restaurant AS r

LEFT OUTER JOIN business_listing AS bl ON r.id = bl.relation
LEFT OUTER JOIN url_mapping AS url ON bl.username = url.username

WHERE `r`.`city` LIKE 'somecityname'

ORDER BY type ASC, name ASC";

NEXT....

1. I need get one more thing from another table and that is the table "category AS c"
2. The information I need is c.name
3. The connect to the restaurant table is r.type = c.optionvalue

I HAD added this to my select list as: c.name, c.optionvalue. Then tried to RIGHT OUTER JOIN them as you can see above. This c.name is completely independent of the other 2 tables bl and url, but I still need the data returned. As you can see by my previous post, even though the query works in the myphpadmin interface and when ran produces a table exactly as I want, when I add the code to the webpage WITH the ORDER BY statement the "OMG this is too huge" error appears.
How should I write the 3rd JOIN to avoid the duplications which are causing the error? I appreciate the help.

And what is the result if you try this:

select
  r.id, 
  bl.relation, 
  bl.username as business_listing_username, 
  url.username as url_username, 
  url.url, 
  r.type,
  r.name,
  r.street,
  r.city,
  r.state,
  r.zip,
  r.phone,
  r.accounttype,
  c.name as catname
from restaurant r
  inner join category c
    on r.type = c.optionvalue
  left outer join business_listing as bl 
    on r.id = bl.relation
  left outer join url_mapping as url 
    on bl.username = url.username
where r.city like 'cityName'
order by r.name desc, r.type desc

Now, try to run this directly on the db, and give us back the exact error, if there is one.
If one of the joins have a missing jointure, then you will get what we call in the dba world a *cardinal product".
Like in ghostbusters, it's as bad as crossing the streams (usually, but it can come handy in a few situations).

A cardinal product means that every rows from your left table are matched with every rows of your right table.
Which can lead to millions of rows as a query result when you thought than a few hundred will result.
Add an "order by" on top of a cardinal product, and you have 90% of chances to crash the server because he allocate all the memory to (try to) order the millions of rows.

The problem is, only you can tell us if we are missing something.
Without seeing the full schema, and a subset of datas to test it, we are left blind.
And the error "OMG this is too huge" is really not a useful description of the error.

The query you provided works in the MySqlAdmin interface perfectly (returns the correct data WITHOUT error)...as do the ones I have previously provided.
Once I place the PHP code on the site and load the page the same error occurs.

"The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay"

I do not understand WHY the query functions with the datbabase, but will not function on the webpage. I am using the exact same code.

I do not understand WHY the query functions with the datbabase, but will not function on the webpage. I am using the exact same code.

Probably because sqlAdmin programm issue the SET SQL_BIG_SELECTS=1 command when you launch it.
It's a separate statement, which you can execute at any time before you
execute your SELECT. The setting remains in effect during the current
database session between your PHP application and the database.

The easiest solution for you, is to issue this before sending your big query:

PHP Code:

mysql_query("SET OPTION SQL_BIG_SELECTS=1");
mysql_query("your big query here");
//parse the query results...
//...
//...
mysql_query("SET OPTION SQL_BIG_SELECTS=0"); 


On a side note, looking about this problem, and it's in fact a specific config that the DB administrator enabled.
By default, mysql don't fix a MAX_JOIN_SIZE value.
Probably a failsafe that has been added to avoid the server going down because a user did an error in a query and crashed the process.

I cannot thank everyone enough for their help and input.
Tripy, you're a GAWD. That worked perfectly - the php code on the webpage did not error and returned the results exactly as the database.
I placed this (as instructed):

mysql_query("SET OPTION SQL_BIG_SELECTS=1");

just before my $query="select...etc" and this:

mysql_query("SET OPTION SQL_BIG_SELECTS=0");

just before the last bracket in the function...essentially "turning-off" BIG SELECT. Awesome! Same number of results and in the proper order were displayed.