PHP/MYSQL: 2 similar codes, one fails

[MisterDood]

Back Story:
Hired to do PHP/MYSQL work on a race website.
Need to dump MYSQL database into Windoze spreadsheet (though that part works just fine)
This is what works:

 
$sql = "SELECT * FROM participants AS T1, race2011 AS T2 WHERE T1.id = T2.racerid";

    $result = mysql_query($sql);

    while($cols = mysql_fetch_assoc($result) ){

        if( !isset($columnLabels) ){

            foreach($cols AS $key => $value){

                $columnLabels[] = ucwords( str_replace("_", " ", $key) );
            }
            $row[] = $columnLabels;
        }
        $row[] = $cols;
    } 
  

This dumps a spreadsheet of 2011's racers. Now, I need a dump of all racers ever. When I try replacing $sql with "SELECT * FROM PARTICIPANTS", it doesn't work for an unknown reason. I know for a fact that it's not a server or Apache problem.

I @THINK@ that the search results aren't being stored to $row properly, but I have no idea. Can someone please help me? I've been working on this all day and can't manage to figure this one out. Thanks in advance.

[Cabhan]

"it doesn't work" is not very informative.

Does $result get populated? Does $cols work? Do the loops happen? Add print statements to the various parts of the code so you can see what the status of the variables are. And when you say "it doesn't work", what do you mean? $row doesn't get set correctly?

[elija]

In mysql tables are files so if this is running on a Linux server, PARTICIPANTS is not the same as participants. This is a common gotcha when developing on a Windows box and deploying to Linux!

[Cabhan]

SQL is generally case-insensitive. Here is an example from sqlite:

sqlite> INSERT INTO test.test VALUES ("alex");
sqlite> SELECT * FROM test.test;
alex
sqlite> SELECT * FROM test.TEST;
alex
sqlite> SELECT * FROM TEST.TEST;
alex

[elija]

SQL is generally case-insensitive. Here is an example from sqlite:

sqlite> INSERT INTO test.test VALUES ("alex");
sqlite> SELECT * FROM test.test;
alex
sqlite> SELECT * FROM test.TEST;
alex
sqlite> SELECT * FROM TEST.TEST;
alex

Agreed but in MySQL, MYISAM table names certainly are case sensitive if the underlying file system is case sensitive. A MYISAM table is stored as three files

TableName.frm which is the structure of the data
TableName.myd which is the actual data
TableName.myi which is your indexes.

To be honest, I can't remember if the engines like INNODB are are also case sensitive on the table names but with MySQL, it is safer to assume that it is!

[MisterDood]

Sorry for not being informative; I figured it would be kind of obvious since I've only been using PHP/MYSQL for about 4 days. I also had a really bad headache from this. Regardless, I'm going to run through this one more time, and post my test results:

 $sql = "SELECT * FROM participants"
$result = mysql_query($sql);
echo $result; 
  

That displays "Resource id #9". This is good so far.

         <...>
    while($cols = mysql_fetch_assoc($result) ){
    echo $cols;
    } 
  

This says "Array" a lot. Probably once for every row in the table (about 6,000).

     while($cols = mysql_fetch_assoc($result) ){
        if( !isset($columnLabels) ){
            foreach($cols AS $key => $value){
                $columnLabels = ucwords( str_replace("_", " ", $key) );
                echo $columnLabels;
            }
        }
    } 
  

Displays the name of each column name from the MYSQL database

When I replace $columnLabels in the line "$columnLabels = ucwords( str_replace("_", " ", $key) );" with $columnLabels[], it says Array once for each element in the array (which makes sense).

     while($cols = mysql_fetch_assoc($result) ){
        if( !isset($columnLabels) ){
            foreach($cols AS $key => $value){
                $columnLabels[] = ucwords( str_replace("_", " ", $key) );
            }
            $row[] = $columnLabels;
        }
    }
echo $row; 
  

This says "Array" once, which makes sense. echo var_dump($row); tells me that the array $row is holding a single array ($columnLabels). That array holds the column names from the MYSQL database (as expected).

     while($cols = mysql_fetch_assoc($result) ){
        if( !isset($columnLabels) ){
            foreach($cols AS $key => $value){
                $columnLabels[] = ucwords( str_replace("_", " ", $key) );
            }
            $row[] = $columnLabels;
        }
        $row[] = $cols;
    }
echo t;  // t doesn't need to be quoted 
  

It wont display t, but when I comment out the line "$row[]=$cols", it displays t. As far as I know, that means the line "$row[] = $cols;" is my problem. I can think of a few things this could mean, but I'm sure it's not a server issue since it worked before the switch.

Also, sorry for a long post, I tried my best to not repeat stuff. Is there a way to do a hide/show box or something?

[Kloschüssel]

You're probably wasting a lot of memory there. Try to limit the output data to a few records:

$sql = "SELECT * FROM participants LIMIT 10"

[MisterDood]

First of all, I need the entire table to be in the document. If you're saying I should loop the function you listed and append everything to my document, I'm not sure if that would work. I'd have to use this general idea:

 $len = /*Dynamic way to find last ID*/
$i;
while $i<$len{
 foreach($i AS $i){
  <file append function with row[$i] and file>
 }
}
generate_file(); 
  

Would that not waste a lot of memory?

[elija]

Instead of echo $row; use var_dump($row); and you will see the contents of the array. If you do it inside the loop, you will see a lot of data. It may shed some light.

[Kloschüssel]

I just wanted to point out that developing can be done with a small subset of the original data set. That should speed up some things during the development phase. Once your code works as you expect, you can still do optimizations if needed. That said, taking everything that comes from a database and storing it in memory is most often a bad idea. If the set becomes bigger and bigger, consider to use temporary files.