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
– tadman, Commented Jan 29, 2015 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
– BGill, Commented Jan 29, 2015 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
– tadman, Commented Jan 29, 2015 at 19:41

Community · Accepted Answer · 2017-05-23 12:28:31Z

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, 2017 at 12:28

CommunityBot

11 silver badge

answered Jan 29, 2015 at 19:13

TacoV

4741 gold badge6 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BGill Over a year ago

code<?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(); ?>code

TacoV Over a year ago

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.

BGill Over a year ago

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.

Nick Gable · Accepted Answer · 2015-01-29 19:05:05Z

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, 2015 at 19:05

Nick Gable

211 silver badge4 bronze badges

2 Comments

BGill Over a year ago

I'm sorry, the @ was a typo.

BGill Over a year ago

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

Collectives™ on Stack Overflow

php and mysql query results displayed only if using limit

2 Answers 2

Edit

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related