PHP/MySQL Help

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 DH 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;

Edited by hank9481 on 04/13/09 08:41 PM (server time).

The problem is your FROM clause. When you don't specify a JOIN type and simply name all the tables, you get a huge Cartesian product of all the matches, and then limit it with your WHERE clause. This means that the database has to deal with a ton of unnecessary data that you could limit out with some minor changes.

Try the modified FROM clause I suggested below. These are all INNER JOINs so you will not get results if there are no entries in the specified tables. You may need to go with LEFT JOINs, but that remains to be seen.

I guessed at the field name that would join RS to P. You'll need to change that if I guessed wrong.

Original FROM clause:
FROM players P
, teams T
, position POS
, players_roster_status RS
, players_career_pitching_stats PS
, players_career_batting_stats BS

Suggested FROM clause:
FROM players P
, JOIN teams T ON T.team_id = P.organization_id
, JOIN position POS ON POS.pos_id = P.position
, JOIN players_roster_status RS ON RS.player_id = P.player_id
, JOIN players_career_pitching_stats PS ON PS.player_id = P.player_id
, JOIN players_career_batting_stats BS ON BS.player_id = P.player_id

I tried to really simplify it, but I get no data appearing now regardless of whether I use JOIN, LEFT JOIN, or RIGHT JOIN. Help!

<? $result = mysql_query("SELECT P.player_id, P.last_name, P.first_name, P.team_id, P.position, T.team_id, T.abbr, POS.pos_id, POS.pos FROM players P, JOIN teams T ON T.team_id=P.organization_id, JOIN position POS ON POS.pos_id=P.position") or die(mysql_error());;
while ($row = @mysql_fetch_array($result,MYSQL_ASSOC)) {
print "<option value='".$row{'first_name'}." ".$row{'last_name'}."'>".$row{'pos'}." ".$row{'first_name'}." ".$row{'last_name'}.", ".$row{'abbr'}."</option>";
}
?>

In reply to:

---

I tried to really simplify it, but I get no data appearing now regardless of whether I use JOIN, LEFT JOIN, or RIGHT JOIN. Help!

---

First, it's important to know what you're trying to accomplish with the query. INNER, LEFT, and RIGHT joins serve very different purposes. Arbitrarily switching them will not help, and will often cause problems that are difficult to diagnose when your data set increases in size.

Personally I always try to run my queries through a tool like phpMyAdmin first to make sure they're correct. At this point you're adding a couple of variables that can skew your results. I strongly suggest you do that first. You can start with just the query of players, which definitely should work. Then join in one table at a time and make sure you're still getting results.

Just for clarification the "position" column in the players table is a numeric foreign key to the "position" table, right? You need to make sure the datatypes match up.

I notice that you have two semi-colons when calling the mysql_query function. I guess that you were doing some nesting and left the extra one in there. I don't know if it will cause any problems, but it's not necessary and is a bad habit to get into IMHO.

Instead of jumping right into building the HTML code, verify that you're not getting results. Echo out "mysql_num_rows($result)" to make sure the recordset is empty. Your snippet doesn't embed the option values within a "<select>" form element input. Many browsers won't display anything if you just have "<option>" tags. Check the source to see if it's being dumped out.

I'm happy to help you, but we've gotta take this a step at a time.