Jump to content
MakeWebGames

Error Help Please!!!!


chicka
 Share

Recommended Posts

When players click on a users profile this is what they get.. I have not touched the files in a while so its nothing I did.. can someone please explain what this means and a fix if possible.. thanks in advance!!

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

Query was SELECT u.*,us.*,c.*,g.*,f.* FROM users u LEFT JOIN userstats us ON u.userid=us.userid LEFT JOIN cities c ON u.location=c.cityid LEFT JOIN gangs g ON g.gangID=u.gang LEFT JOIN fedjail f ON f.fed_userid=u.userid WHERE u.userid=9233

Link to comment
Share on other sites

Well basically it tells you all.

When running that query, too many joins are done and therefore could lead to a slow down / memory usage. For that MySQL doesn't allow it by default. Yet either you rewrite your query to make it work with smaller joins or... you need to modify (if possible) the my.cfg file to set the SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=#

Both of those changes might not be possible on a shared host and are quiet surely a dead road as it will make slow queries at the end.

Best solution would be to rewrite the query.

Link to comment
Share on other sites

Did you install game properly? And if you did, maybe you changed mySQL username/password after that?(I've no idea giving 1st idea what "shoot" in my head)

Yes I installed my game properly about 3 years ago. No I didn't change any usernames or passwords.

a_bertrand thanks for the info.. I appreciate it

Link to comment
Share on other sites

I don't know why you are querying for so much unwanted data.

Let's make it a little more simpler:

You're queries:

    (SELECT u.* FROM `users` WHERE u.userid = 1)
   (SELECT us.* FROM `userstats` WHERE us.userid = 1)
   (SELECT c.* FROM `cities` WHERE u.location = c.cityid)
   (SELECT g.* FROM `gangs` WHERE g.gangID=u.gang)
   (SELECT f.* FROM `fedjail` WHERE f.fed_userid=u.userid)

 

If you really need all the data, or don't know what to expect, I'd suggest these queries seperately:

SELECT     u.*, g.*, c.*
 FROM 
   `users` u 
 LEFT JOIN
   gangs g
 ON
   g.gangID = u.gang
 LEFT JOIN 
   cities c 
 ON 
   u.location = c.cityid
 WHERE 
   u.userid = 1

 

And:

SELECT    f.*, us.*
FROM
   fedjail f 
 LEFT JOIN
   userstats us 
 ON 
   fed_userid = us.userid 
WHERE 
  f.fed_userid= 1

 

Quickly tested, and seems to work, apart from the obvious that no userstats would should if no fedjail record is present for that specific ID.

Though, it's still not optimized in any way.

Edited by Spudinski
Link to comment
Share on other sites

$q=$db->query("SELECT u.*,us.*,c.*,g.*,f.* FROM users u LEFT JOIN userstats us ON u.userid=us.userid LEFT JOIN cities c ON u.location=c.cityid LEFT JOIN gangs g ON g.gangID=u.gang LEFT JOIN fedjail f ON f.fed_userid=u.userid WHERE u.userid={$_GET['u']}");

if($db->num_rows($q) == 0)
{
print "Sorry, we could not find a user with that ID, check your source.";
}

 

Thats the query that is giving me a hard time all of a sudden. I tried the above code but didn't work.. If someone could lend a hand I would appreciate it

Link to comment
Share on other sites

What you can do is make 5 different queries selecting the needed fields.

Then fetch the array of each of them then merge the arrays.

 

$id=abs((int) $_GET['u']);
$q1="SELECT * FROM `users` WHERE `userid`=$id";
$q2=select next table blah blah;
...
$q5=select last table blah bla;

//run queries $q1=$db->query($q1) ... to $q5

//fetch each row and pump into array $q1rows[]=$db->fetch_row($q1)

$userdata=array_merge($q1rows,$q2rows,$q3rows,$q4rows,$q5rows);
Edited by bluegman991
Link to comment
Share on other sites

What you can do is make 5 different queries selecting the needed fields.

Then fetch the array of each of them then merge the arrays.

 

$id=abs((int) $_GET['u']);
$q1="SELECT * FROM `users` WHERE `userid`=$id";
$q2=select next table blah blah;
...
$q5=select last table blah bla;

//run queries $q1=$db->query($q1) ... to $q5

//fetch each row and pump into array $q1rows[]=$db->fetch_row($q1)

$userdata=array_merge($q1rows,$q2rows,$q3rows,$q4rows,$q5rows);

If I may might a suggestion:

5 queries would be slower than the original. The JOIN method can still be kept, but it's usage can just be lowered upon.

Two, maybe three queries should suffice.

I would also like to point the OP into the right direction for learning about this:

Ref 1: http://dev.mysql.com/doc/refman/5.6/en/join.html

Ref 2: http://dev.mysql.com/doc/refman/5.6/en/union.html

Red 3: http://dev.mysql.com/doc/refman/5.6/en/select.html

Link to comment
Share on other sites

If I may might a suggestion:

5 queries would be slower than the original. The JOIN method can still be kept, but it's usage can just be lowered upon.

Two, maybe three queries should suffice.

I would also like to point the OP into the right direction for learning about this:

Ref 1: http://dev.mysql.com/doc/refman/5.6/en/join.html

Ref 2: http://dev.mysql.com/doc/refman/5.6/en/union.html

Red 3: http://dev.mysql.com/doc/refman/5.6/en/select.html

Thanks spudinski for the links. i'll look them over and see what I can come up with.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...