mySQL query in PHP only takes 7 columns

Question

How come my mySQL query will only take 7 columns? Every time I attempt to add an 8th column name, the page breaks. Example:

Does NOT work:

            $result = mysqli_query($conn, "SELECT name, fibre, sugar, img, kcal, carbs, protein, fat FROM table");
        $names = array();
        $fields_num = mysqli_num_fields($result);

        if(mysqli_num_rows($result) > 0) {
          while($row = mysqli_fetch_assoc($result)) {
            $names[] = $row;
          }
        } else {
          echo "0 results";
        }

Does Work: See 'fat' removed from query

            $result = mysqli_query($conn, "SELECT name, fibre, sugar, img, kcal, carbs, protein FROM table");
        $names = array();
        $fields_num = mysqli_num_fields($result);

        if(mysqli_num_rows($result) > 0) {
          while($row = mysqli_fetch_assoc($result)) {
            $names[] = $row;
          }
        } else {
          echo "0 results";
        }

I have also tried with different column names from the table, but it doesn't matter, they still don't work. Yes, the columns are all spelled right.

Is there any way I can query more then 7 column names? ALSO: using mysqli_query($conn, "SELECT * FROM table"); Does not work either!?!? What is going on here??

I didn't get it. I see `fat`column in both of snippet codes... — bcesars, Apr 08 '15 at 17:23
Also, if you are seeing blank pages, check php_error_log or add `error_reporting(E_ALL); ini_set('display_errors', 1);` at the beggining of you file. — bcesars, Apr 08 '15 at 17:25
Try integrating this function somehow: http://php.net/manual/en/mysqli.error.php — Mike, Apr 08 '15 at 17:27
Add `or die(mysqli_error($conn))` to `mysqli_query()` see what that says. — Funk Forty Niner, Apr 08 '15 at 17:27
When OP said _the page breaks_, I guess that 'page' doesn't work... 'Its broken'... lol — bcesars, Apr 08 '15 at 17:30
@bcesars Saying "the page breaks" or "it's broken" does not help anyone determine *what* is broken. — Mike, Apr 08 '15 at 17:32
♫ *When the levee breaks* ♫ @bcesars now that's a good "break" ;-) Google that. — Funk Forty Niner, Apr 08 '15 at 17:35
I know @Mike. that's why I'm laughing. It's too broad saying that something _is broken_ out of the blue and now I suggested to add error in that file. — bcesars, Apr 08 '15 at 17:36
You still haven't told us what "the page breaks" means. [Did you do this?](http://stackoverflow.com/questions/29521241/mysql-query-in-php-only-takes-7-columns#comment47197441_29521241) or [this?](http://php.net/manual/en/function.error-reporting.php) just like [this person wrote](http://stackoverflow.com/questions/29521241/mysql-query-in-php-only-takes-7-columns#comment47197343_29521241) — Funk Forty Niner, Apr 08 '15 at 17:40
@bcesars my apologies, I accidently copied the wrong code. It is updated now. Will try `error_reporting`, thanks. Yes, blank pages is what I was referring to, sorry for using very vague words. — Alex McLean, Apr 08 '15 at 17:43
Blank pages means syntax errors without error reporting set or displayed. — Funk Forty Niner, Apr 08 '15 at 17:44
I don't understand why I would get a syntax error if all I do is add an additional column name to my query... — Alex McLean, Apr 08 '15 at 17:47
I don't know... I thought that's what you meant with the blank pages... I added bcesars code: `error_reporting(E_ALL); ` and `ini_set('display_errors', 1);` to the very beginning of my file inside the php tags and still, nothing comes up on the blank page... — Alex McLean, Apr 08 '15 at 17:51
@McLean25 You could also check your php's error logs. The default with Apache on Linux is usually `/var/log/apache2/error.log`. — Mike, Apr 08 '15 at 17:53
Ok... here's a very very wild guess as to what could be going on, but I doubt it. First, see if your column name doesn't accidentally contain a space or encoded character. Try to rename the column to say... `fat_x`. If it works, then my "wild guess" is that, in computer code language; FAT has a special meaning "FIle Allocation Table". Again, a very *wild* guess. — Funk Forty Niner, Apr 08 '15 at 17:54
@Mike thanks, finally found the error logs and it told me what the problem is. `[08-Apr-2015 17:49:14 UTC] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 5 bytes) in `. Can't believe this is happening as I am trying to display very small content, like 9 text fields for 10 items... — Alex McLean, Apr 08 '15 at 17:59
@Fred-ii- If `fat` had a special meaning in MySQL it would probably appear in the [reserved words list](https://dev.mysql.com/doc/refman/5.7/en/reserved-words.html). — Mike, Apr 08 '15 at 17:59
@McLean25 Add that to the question and I'll remove my downvote — Mike, Apr 08 '15 at 18:01
@Mike I know. That's why I said *but I doubt it* and *a very wild guess*. ;-) stranger things have happened. — Funk Forty Niner, Apr 08 '15 at 18:01
`ini_set('memory_limit','16M');` or use a higher number. See also http://www.ewhathow.com/2013/09/how-to-increase-mysql-memory-limit/ and http://stackoverflow.com/q/12104185/ — Funk Forty Niner, Apr 08 '15 at 18:02
@Mike and Fred -ii- : Thanks guys for your help. Sorry for the confusion in the question, just started with PHP over the weekend. — Alex McLean, Apr 08 '15 at 18:05
@Fred-ii- It looks like the memory limit is already set to 128MB. I doubt increasing it would fix the problem. It's probably some sort of memory leak, like an infinite loop. — Mike, Apr 08 '15 at 18:07
@Mike Maybe there's something in the two links that will help the OP. — Funk Forty Niner, Apr 08 '15 at 18:08
@Mike that's what I was thinking as well. I'll just have to go over my code again. I'm sure I have an inefficient loop somewhere. — Alex McLean, Apr 08 '15 at 18:09
@Fred-ii- yes problem solved, somewhat. I now know what I have to fix in my code. — Alex McLean, Apr 08 '15 at 18:09
Great. You can post your own answer if you want; Stack lets you do that. I'm glad this matter was finally resolved, *cheers* — Funk Forty Niner, Apr 08 '15 at 18:10
Plus, it could be that if your table isn't indexed, it could be working too hard, in turn taking up more memory. — Funk Forty Niner, Apr 08 '15 at 18:12
@Fred-ii- Reading about 'Properly indexed' tables now. Although I think the majority of the problem was that my table had thousands of rows. I set `LIMIT 10` and that solved my problem for now. — Alex McLean, Apr 08 '15 at 18:20

Alex McLean · Answer 1 · 2015-04-08T18:21:37.177

1

Was trying to pull too much data from the table:

Found in PHP error logs: [08-Apr-2015 17:49:14 UTC] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 5 bytes).

Code had to be looked over again to find inefficiencies in loops.

Also added LIMIT 10 to the end of the query to reduce table workload.

edited Apr 08 '15 at 18:21

answered Apr 08 '15 at 18:15

Alex McLean

2,524
5
30
53

1

*"@Fred-ii- Reading about 'Properly indexed' tables now. Although I think the majority of the problem was that my table had thousands of rows. I set LIMIT 10 and that solved my problem for now."* - There's "always" a solution. Just needed to get right "formulae" ;-) – Funk Forty Niner Apr 08 '15 at 18:25
1

Plus, you could also use pagination, if that's part of the *ultimate goal* along with the LIMIT. – Funk Forty Niner Apr 08 '15 at 18:28

mySQL query in PHP only takes 7 columns

1 Answers1