sql function error

[cactus]

Hi

I am trying to create a blog system for my website.

 

I have been following a tutorial and testing everything along the way. As part of the tutorial I have created the function get_posts which is giving me the error. As I have no data in my database at the moment I have tested this code in the sql section of phpmyadmin.

 

The error reads:

 

MySQL said: 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['posts'.'post_body'] AS 'preview',

                'posts','post_user' AS 'user',

                DAT' at line 4

 

(which isn't a very helpful error)

 

The function is:

function get_posts(){
$sql = "SELECT
			'posts','post_id' AS 'id',
			'posts','post_title' AS 'title',
			LEFT['posts'.'post_body','512'] AS 'preview',
			'posts','post_user' AS 'user',
			DATE_FORMAT['posts'.'post_date','%d/%m/%Y %H:%i:%s'] AS 'date',
			'comments'.'total_comments',
			DATE FORMAT['comments'.'last_comment','%d/%m/%Y %H:%i:%s'] AS 'last_comment'
		FROM 'posts'
		LEFT JOIN (
			SELECT
				'post_id';
				COUNT('comment_id') AS 'total_comments',
				MAX('comment_date') AS 'last_comment'
			FROM 'comments'
			GROUP BY 'post_id'
		) AS 'comments'
		ON 'posts'.'post_id' = 'comments'.'post_id'
		ORDER BY 'posts','post_date' DESC";

$posts = mysql_query($sql);

$rows = array();
}

 

I would really appreciate it if somebody could take a look and see if they can see the error.

 

Thanks a lot in advance

[gristoi]

try

LEFT('posts'.'post_body','512') AS 'preview',

 

instead of square brackets

[cactus]

I tried that as well and it still gave the same error. Any other ideas? Thanks

[gristoi]

can you post the table structure please

[PFMaBiSmAd]

it still gave the same error

 

It's unlikely the error is exactly the same, because the suggestion would have fixed the error you were getting at that point in the query. If you are getting the same type of error, but at a different point in the query, wouldn't it make sense to try using the same solution that you were just given?

[cactus]

The table is called 'posts'

 

its has post_id, post_title, post_body, post_user and post_date within it.

 

and @PFMaBiSmAd it does give the exact same error.

 

Thanks

[gristoi]

SELECT
			posts.post_id AS 'id',
			posts.post_title AS 'title',
			LEFT(posts.post_body,'512') AS 'preview',
			posts.post_user AS 'user',
			DATE_FORMAT(posts.post_date,'%d/%m/%Y %H:%i:%s') AS 'date',
			comments.total_comments,
			DATE_FORMAT(comments.last_comment,'%d/%m/%Y %H:%i:%s') AS 'last_comment'
		FROM posts
		LEFT JOIN (
			SELECT
				post_id,
				COUNT('comment_id') AS 'total_comments',
				MAX('comment_date') AS 'last_comment'
			FROM comments
			GROUP BY post_id
		) AS comments
		ON posts.post_id = comments.post_id
		ORDER BY 'posts','post_date' DESC;

[cactus]

Hi all,

 

I am trying to follow a tutorial on creating a blog system but keep getting the above error on line 41, the error message comes up about 100 times in a row which confuses me.

 

My section of code is:

 

function get_posts(){
$sql = "SELECT
			'posts'.'post_id' AS 'id',
			'posts'.'post_title' AS 'title',
			LEFT('posts'.'post_body','512') AS 'preview',
			'posts'.'post_user' AS 'user',
			DATE_FORMAT('posts'.'post_date','%d/%m/%Y %H:%i:%s') AS 'date',
			'comments'.'total_comments',
			DATE_FORMAT('comments'.'last_comment','%d/%m/%Y %H:%i:%s') AS 'last_comment'
		FROM 'posts'
		LEFT JOIN (
			SELECT
				'post_id';
				COUNT('comment_id') AS 'total_comments',
				MAX('comment_date') AS 'last_comment'
			FROM 'comments'
			GROUP BY 'post_id'
		) AS 'comments'
		ON 'posts'.'post_id' = 'comments'.'post_id'
		ORDER BY 'posts','post_date' DESC";

$posts = mysql_query($sql);

$rows = array();
while(($row = mysql_fetch_assoc($posts)) !== false){  //THIS IS LINE 41
	$rows[] = array(
		'id'				=> $row['id'],
		'title'				=> $row['title'],
		'preview'			=> $row['preview'],
		'user'				=> $row['user'],
		'date'				=> $row['date'],
		'total_comments'	=> ($row['total_comments'] === null) ? 0 : $row['total_comments'],
		'last_comment'		=> ($row['last_comment'] === null) ? 'never' : $row['last_comment']
	);
}
return $rows;
}

 

I really hope someone can help as I have no idea as this function in the tutorial worked.

Can someone help please?

 

Thanks in advance

[sasa]

change to

ORDER BY 'posts'.'post_date' DESC";

change comma to dot

[cactus]

Thanks I tried that but It's still giving me the same error any other ideas?

I really appreciate it.

[PFMaBiSmAd]

You already have an active thread from yesterday with this query. Some reason you didn't continue this in that thread and continue executing the query so that you are getting the msyql errors from it?

 

gristoi posted a more correct query in that thread that you didn't use, because he removed a lot, but not all of the single-quotes you have around the table, column, and alias names.

[cactus]

@PFMaBiSmAd what's your problem?!

 

I did try what @gristoi suggested yesterday but it didn't work, I opened a new thread as yes it might be the same code but it's a different error.

 

Maybe if you tried to help instead of just offering snide comments I might get somewhere with it!

[PFMaBiSmAd]

Wouldn't it be a good idea to continue making progress in an existing thread, rather than throw away the information gained in that thread?

 

It's also against the forum rules to start duplicate threads for the same issue and just because the error message changed, what you are trying to do didn't change.

[cactus]

I didn't throw away the information gained I tried the solution and it didn't work. I continued on through the tutorial and then got to this error. I am new to PHP so I have no idea if the 2 error messages are related hence why I started a new thread.

 

Do you have any help to offer?

[gristoi]

Hi, like I stated previously your have some syntax errors within your mysql query. firstly, if you are joining tables together in a query then you need to use the concatenation operator '.' to prepend the tablename to the field name. In your query you were using a comma to do this.

 

Secondly, you do not need to surround the fieldnames in the query with single quote marks, so posts.postid instead of 'posts'.'postid' is fine. You only need to use '' to surround the alias name of you fieldname -

posts.postid AS 'ID'

. There wil however be occasions when you do need to ensure there is not a conflict with reserved MYSQL words and for this you can surround the name with ``.

 

I did miss some of the commas on your query before but it should look like this:

SELECT
			posts.post_id AS 'id',
			posts.post_title AS 'title',
			LEFT(posts.post_body,'512') AS 'preview',
			posts.post_user AS 'user',
			DATE_FORMAT(posts.post_date,'%d/%m/%Y %H:%i:%s') AS 'date',
			comments.total_comments,
			DATE_FORMAT(comments.last_comment,'%d/%m/%Y %H:%i:%s') AS 'last_comment'
		FROM posts
		LEFT JOIN (
			SELECT
				post_id,
				COUNT('comment_id') AS 'total_comments',
				MAX('comment_date') AS 'last_comment'
			FROM comments
			GROUP BY post_id
		) AS comments
		ON posts.post_id = comments.post_id
		ORDER BY posts.post_date DESC;

 

A bit of advice with working with your queries: to check whether the query is syntactically correct ( and works ) I always directly run my query in my mysql workbench / phpmyadmin panel or sql command line on my dev environment to ensure that it is working properly. If it is then that allows me to look elswhere for what could be causing the issue. Tools such as mysql workbench allow you to write and test your queries and will highlight if there is a syntax error in your query, very useful.

 

The looping error you are getting is more than likely due to the syntax error in your query not returning a valid resource.

 

Hope this helps

[cactus]

Thanks gristoi

 

That solved that error however it is now giving me another error I was hoping you could help with:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 12

This is the code before the code I have previously posted:

<?php
//checks if the given post id is in the table
function valid_pid($pid){
$pid = (int)$pid;

$total = mysql_query("SELECT COUNT('post_id') FROM 'posts' WHERE 'post_id' = ($pid)");
$total = mysql_result($total, 0);

if ($total != 1){
	return false;
}else{
	return true;  //THIS IS LINE 12
}
}

 

Thank you again

[gristoi]

Change you query and remove the brackets around the pid and you need to alias the count:

"SELECT COUNT('post_id') AS 'postIdCount' FROM 'posts' WHERE 'post_id' = $pid"

 

the extra brackets in the query are terminating your script further on

[cactus]

Thank you for all your help

[gristoi]

always a pleasure