PHP/MySQL Help

[hank9481]

I am having some trouble with PHP/MySQL and need some help if possible. I am creating tables, one as large as 13 mb, but am under the impression that this is what PHP is used for - to pull large amounts of data. Nonetheless, I continually get timeouts and errors when accessing my database via the panel and phpmyadmin. Certain databases won't even fully load, and I am constantly having to load from a backup. These problems did not start until last week when Dreamhost did some things to the panel.

 

Anywho, here is my code they said was problematic. I'm definitely no PHP expert so can someone help me out?

 

SELECT P.player_id, P.last_name, P.first_name, T.team_id, T.abbr, P.organization_id, POS.pos_id, POS.pos, P.position, RS.mlb_service_days, RS.player_id, P.league_id, PS.vorp, PS.split_id, PS.player_id, PS.league_id, BS.vorp, BS.split_id, BS.player_id, BS.league_id FROM players P, teams T, position POS, players_roster_status RS, players_career_pitching_stats PS, players_career_batting_stats BS WHERE P.league_id=100 AND P.player_id=RS.player_id AND P.organization_id=T.team_id AND P.position=POS.pos_id AND RS.mlb_service_days>0 AND RS.mlb_service_days<344 AND ((PS.split_id=1 AND PS.vorp>5 AND PS.player_id=P.player_id AND PS.league_id=100) || (BS.split_id=1 AND BS.vorp>10 AND BS.player_id=P.player_id AND BS.league_id=100)) GROUP BY P.player_id ORDER BY P.last_name DESC LIMIT 18;

[MasterACE14]

more then likely a problem with your host then, not the mysql.

[PugJr]

Oh, Dreamhost, huh? Well, atleast you still get unlimited HD and BW space right? Probably your best option right now is the following:

 

 

1.) Pester dreamhost about fixing it.

2.) Get a new host.

3.) Wait.

 

Take your pick!

[hank9481]

Oh, Dreamhost, huh? Well, atleast you still get unlimited HD and BW space right? Probably your best option right now is the following:

 

 

1.) Pester dreamhost about fixing it.

2.) Get a new host.

3.) Wait.

 

Take your pick!

 

See, that's what I thought too. I've pestered them to death over the last 5 days, it takes multiple days to get any kind of response, and when they do, they say it's my fault. They said that once the queries begin running, it begins to cause a problem and chews up their space i.e. that my query was too large. This just seems odd to me.

 

I'd be willing to share my entire code with someone via email or whatever, if perhaps someone would be willing to look it over and give me a definitive "it's not your fault" or a "this is what you could do to improve it" sort of thing. I very much want to get this working, and I hate to dump Dreamhost if this is actually my problem, but it just doesn't seem to be the case, at least to me.

 

Basically, what they are telling me is that you get unlimited everything, up until a certain extent. Am I missing something?

[MasterACE14]

Basically, what they are telling me is that you get unlimited everything, up until a certain extent. Am I missing something?

 

lol, that is a hot topic indeed, I did see a good analogy for it earlier today, I'll see if I can't dig it up.

 

EDIT: found it!

 

If the topic of unlimited bandwidth was a topic for Mythbusters, it would be myth busted!

 

Well, consider this. Imagine a hotel with an infinite number of rooms that are all occupied, i.e. the room number range is N1 (i.e. all positive integers). We'll use n to denote the current room number of an individual. Now someone comes into the hotel asking for a room, so the hotel manager rings a bell and asks all the other guests to move to room number n+1, shifting all of them one room upwards. This makes room number 1 available. Now a party of infinite people wants a room. The hotel manager rings the bell once more and asks everybody to move to room number 2n. This leaves all the odd room numbers free, and there are an infinite number of odd, positive integers. So even though he had used all of his infinite number of rooms, he can still make room for an infinite amount of new guests, and again, and again.

 

This is Hilbert's Paradox of the Hotel for you. It's supposed to illustrate that having or reaching infinity is not possible using a technique called reductio ad absurdum. We first assume it is possible, but it turns out that if it is, then reality contradicts that, so the initial assumption must be false, which makes the opposite case (that reaching infinity is not possible) true. It should be easily understandable for everybody, probably even DreamHost's support/sales reps (though I didn't try).

[hank9481]

Ok, so I'd like someone to recommend a better host. It is apparent to me that I am not the problem, unless someone wants to take a peak at my code and double-check me. But, someone help me out and recommend someone to host my stuff that provides quality and reliability!

[hank9481]

I've done some research and think most folks consider BlueHost and InMotion to be pretty highly regarded. Does anyone have any info on either one or potentially another host you would recommend?

[Maq]

I've done some research and think most folks consider BlueHost and InMotion to be pretty highly regarded. Does anyone have any info on either one or potentially another host you would recommend?

 

This may be helpful:

 

http://www.phpfreaks.com/forums/index.php/topic,117475.0.html

[hank9481]

I am now told my problem is a LEFT JOIN, or lack thereof. Can someone please help me identify just what that is?

 

<? $result = mysql_query("SELECT P.player_id, P.last_name, P.first_name, B.hr, B.t, B.d, B.h, B.pa, B.bb, B.hp, B.ab, B.rbi, B.r, B.player_id, B.league_id, B.team_id, B.split_id, B.vorp, T.team_id, T.abbr, P.organization_id, POS.pos_id, POS.pos, P.position FROM players P, players_career_batting_stats B, teams T, position POS WHERE B.year=".$curYear." AND B.team_id<17 AND B.team_id!=12 AND B.league_id=100 AND B.split_id=1 AND B.player_id=P.player_id AND P.organization_id=T.team_id AND P.position=POS.pos_id AND B.pa>(162*3.1) GROUP BY B.player_id ORDER BY B.vorp DESC LIMIT 18") or die(mysql_error());;

 

while ($row = @mysql_fetch_array($result,MYSQL_ASSOC)) {

 

$tb=$row{'hr'}*4 + $row{'t'}*3 + $row{'d'}*2+ ($row{'h'} - ($row{'d'} + $row{'t'} + $row{'hr'}));

 

$obp=($row{'h'} + $row{'bb'} + $row{'hp'}) / $row{'pa'};

 

$avg=$row{'h'} / $row{'ab'};

 

$slg=$tb / $row{'ab'};

 

$ops=$obp + $slg;

 

$pos=$row{'pos'};

 

$first=$row{'first_name'};

 

$last=$row{'last_name'};

 

$team=$row{'abbr'};

 

$hr=$row{'hr'};

 

$rbi=$row{'rbi'};

 

$runs=$row{'r'};

 

print "<option value='".$first." ".$last."'>".$pos." ".$first." ".$last.", ".$team." (".substr($avg,0,5)." Avg, ".$hr." HR, ".$rbi." RBI, ".$runs." Runs, ".substr($ops,0,5)." OPS)</option>";

 

}

 

?>