0

I have the following code:

    $sql = "SELECT table.field, table2.field2 FROM
                table, table2";
    $stmt = $this->db->prepare($sql);
    $stmt->execute();

    $x = $stmt->fetchAll(PDO::FETCH_ASSOC);

I know it is not enough information, but I do not understand why the code executes only if I comment the fetchAll line. When that line executes, I do not get anything, just blank browser, no errors or anything. In firebug the response is blank... What can it be??

Problematic
  • 17,567
  • 10
  • 73
  • 85
luqita
  • 4,008
  • 13
  • 60
  • 93
  • What do you expect to see in browser? Check if error reporting turned on in php.ini. Also, are you handling MySQL errors? – rubayeet Aug 25 '11 at 19:16

2 Answers2

6

You are doing a Cartesian product of table and table2. This can be a HUGE amount of data.

For each row in table, you are getting all the rows in table2. So if table has 100 lines, and table2 has 500 lines, your query will try to fetch 50.000 lines.

You'll either want a JOIN:

SELECT table.field, table2.field2 
FROM table 
JOIN table2 
ON table.field3 = table2.field3

Or a UNION

SELECT table.field AS FIELDNAME FROM table
UNION ALL
SELECT table2.field2 AS FIELDNAME FROM table2

If a Cartesian product is really what you want, try adding a LIMIT and see if that works. Then later increase or remove the limit

SELECT table.field, table2.field2 
FROM table, table2 LIMIT 10

Depending on what your tables contain (you indeed did not give enough info).

Konerak
  • 39,272
  • 12
  • 98
  • 118
  • it is a lot of data :p why would that be a problem? – luqita Aug 25 '11 at 19:17
  • 1
    @luqita: because PHP has fairly hefty memory overhead for storing even the smallest/simplest of data. You're basically trying to load the entire contents of your two tables, SQUARED, into memory. – Marc B Aug 25 '11 at 19:20
  • Try getting smaller data first. If that works, you are hitting certain imposed limits. Maybe your PHP script has a maximum execution time set, or you're running out of memory, or your request timed out... – Konerak Aug 25 '11 at 19:21
  • PHP tends to have a limit on memory and time useage (per default, it is somewhat in the thirty seconds area), which you can change in the php.ini. That is almost every single time a bad idea. You should try to improve your code before even thinking about that. – ty812 Aug 25 '11 at 19:21
  • Try executing the SQL query in another (My)SQL client (commandline mysql, phpmyadmin, MySQL Workbench, Toad, ...) and see the results... – Konerak Aug 25 '11 at 19:29
  • I changed it to using joins but the same thing is happening. By the way, why does this only happen when using fetchAll? If I don't use fetchAll the code just goes through, which maybe means the SQL is not bad? – luqita Aug 25 '11 at 19:30
  • 1
    It's legal SQL, it just generates a very large result set. MySQL can do that all right, but PHP can't fetch and hold a result that large. – Bill Karwin Aug 25 '11 at 19:36
  • @luqita: that is interesting. Can update your question and post the code that does work? Also, `SHOW CREATE TABLE` and `SHOW CREATE TABLE2` would be nice, as well as the `EXPLAIN SELECT ...` of your select :) – Konerak Aug 25 '11 at 19:36
0

For catching errors you should enabled error_reporting and display_errors. I think the cause of this behavior is that you get 'allowed memory limit'. Maybe you get too many rows from db.

Try to add ini_set('memory_limit', '256M') and check working.

Andrej
  • 7,474
  • 1
  • 19
  • 21