1

I am getting two different types of errors from a single line and it took me long to find out where the error was. This is an interesting behavior php shows while concatenating MySql query statements using bacticks for seperating strings.

$query='SELECT c.name as category_name, p.id, p.category_id, p.title, p.body, p.author, p.created_at FROM'.$this->table. ' p LEFT JOIN categories c ON p.category_id=c.id ORDER BY p.created_at DESC'; 

Notice: No space between FROM and backtick. The error follows is


Fatal error: Uncaught PDOException: SQLSTATE[ 42000 ]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'p LEFT JOIN categories c ON p.category_id=c.id ORDER BY p.crea' at line 2 in C:\xampp\htdocs\php_REST_myblog\models\Post.php: 31 Stack trace:
#0 C:\xampp\htdocs\php_REST_myblog\models\Post.php(31): PDOStatement->execute()
#1 C:\xampp\htdocs\php_REST_myblog\api\posts\read.php(18): Post->read()
#2 {main } thrown in C:\xampp\htdocs\php_REST_myblog\models\Post.php on line 31

The next variation is

 $query='SELECT c.name as category_name, p.id, p.category_id, p.title, p.body, p.author, p.created_at FROM '.$this->table. 'p LEFT JOIN categories c ON p.category_id=c.id ORDER BY p.created_at DESC'; 

Notice no space between backtick and p. DB name is myblog and table name is posts.The error follows is


Fatal error: Uncaught PDOException: SQLSTATE[ 42S02 ]: Base table or view not found: 1146 Table 'myblog.postsp' doesn't exist in C:\xampp\htdocs\php_REST_myblog\models\Post.php: 31 Stack trace:
#0 C:\xampp\htdocs\php_REST_myblog\models\Post.php(31): PDOStatement->execute()
#1 C:\xampp\htdocs\php_REST_myblog\api\posts\read.php(18): Post->read()
#2 {main } thrown in C:\xampp\htdocs\php_REST_myblog\models\Post.php on line
31

The correct code is below with two extra spaces.

$query='SELECT c.name as category_name, p.id, p.category_id, p.title, p.body, p.author, p.created_at FROM '.$this->table. ' p LEFT JOIN categories c ON p.category_id=c.id ORDER BY p.created_at DESC'; 

What is going wrong?

1
  • 1
    The first two are invalid sql statements and the third is the correct one - that's why it works. First basically generates SELECT ... FROMposts... (which is "near 'p LEFT JOIN categories..."), second generates SELECT ... FROM postsp... - and that table doesn't exist Commented May 27, 2020 at 19:18

1 Answer 1

2

Your database and table name will be added from $this->table and 'p' is alias for it. They need to be separated from each other by space, just like database name and FROM need space between them.

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.