php and mysql query results displayed only if using limit

Question

In an attempt to learn more php, mysql & json, I downloaded a demo database from: http://www.mysqltutorial.org/wp-content/uploads/downloads/2013/05/mysqlsampledatabase1.zip.

I want to display all customers to echo them out in html table format with edit and delete buttons.

My SQL statement is

"SELECT * FROM customers"

It returns no results and no errors. However, if I change the SQL statement to

"SELECT * FROM customers LIMIT 11"

The first 11 of 122 rows are returned. Using a LIMIT of 1-11 will return results while anything 12 or more returns noting.

Can anyone shed some light on this for me please?

UPDATE: Code for assistance.

<?php
 $servername = "localhost";
 $username = "root";
 $password = "root";
 $dbname = "classicmodels";
 $dbh = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password) or die(mysql_error());
 $sql = 'SELECT * FROM customers LIMIT 12' or die(mysql_error());
 $stmt = $dbh->prepare($sql);
 $stmt->execute();
 $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
 echo json_encode($result);
    die();
?>

Thank you in advance,

Bg

What do you mean by `LIMIT 1-11`? Do you want all customers or page two? — tadman, Jan 29 '15 at 19:07
For comparison: "SELECT * FROM customers" - no results "SELECT * FROM customers LIMIT #" - results are returned if # is 11 or less "SELECT * FROM customers LIMIT #" - no results if # is 12 or greater — BGill, Jan 29 '15 at 19:11
That really doesn't make any sense, that's not how it's supposed to work. `LIMIT 10` will return anywhere between 0 and 10 results depending on how much data is in your database, it just *limits* the rows returned. I don't know why you're seeing no rows when you have insufficient records. Are you sure this query works correctly independent of PDO? Do you have errors in your code that prevent it from working correctly? You'll want to carefully inspect what's happening between each step of your process. — tadman, Jan 29 '15 at 19:41

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

The problem is not in your SQL code. If

SELECT * FROM customers LIMIT 11

works correctly, so will

SELECT * FROM customers LIMIT 12

Therefore, there is probably an error in your php code. Either it doesn't handle the 12th record retrieved correctly, or it cannot handle the 12+ limit for some other reason. Could you expand the question with the relevant php code?

Edit

To correctly display special characters, such as the å in Luleå (your 12th record), you must match the character set in the database settings, database connection and output.

For example, if your database collation is UTF-8, make your first query in your script SET NAMES 'utf8'. Then, before printing the json, send a header to the browser (I'm assuming you're outputting to a browser): header('Content-Type: text/json; charset=utf-8');

By configuring your database correctly, the SET NAMES becomes obsolete.

Some helpful guides I found explain this in more detail:

edited May 23 '17 at 12:28

Community

1
1

answered Jan 29 '15 at 19:13

TacoV

424
1
5
17

`code`prepare($sql); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); echo json_encode($result); die(); ?>`code` – BGill Jan 29 '15 at 19:15
The code is simple enough, I see. However, the 12th record contains an obscure character (Luleå), and you have no explicit character set settings. I'm guessing that is the problem and this is a character set issue. I'll edit some advice in the answer. – TacoV Jan 29 '15 at 19:20
Using the LIMIT in the MySql statement I have been going line by line through the database and replacing the special characters. This has allowed the returned data to grow up to 22 rows returned so far, verifying TacoV's answer. – BGill Jan 29 '15 at 20:28

score 0 · Answer 2 · answered Jan 29 '15 at 19:05

0

Since you are selecting everything from the table, you may want to change your syntax to:
SELECT * FROM customers (rather then @)
LIMIT x
Where x is your number, this should allow for numbers beyond 12.

answered Jan 29 '15 at 19:05

Nick Gable

21
1
4

I'm sorry, the @ was a typo. – BGill Jan 29 '15 at 19:08
For comparison: "SELECT * FROM customers" - no results "SELECT * FROM customers LIMIT #" - results are returned if # is 11 or less "SELECT * FROM customers LIMIT #" - no results if # is 12 or greater – BGill Jan 29 '15 at 19:11

php and mysql query results displayed only if using limit

2 Answers2

Edit