[eluser]Unknown[/eluser]
hi all,
i have this error starring me in the face but i just cant see it
so maybe some fresh eyes are needed
keep in mind i normally use .net (dont laugh ok)
the dilemma:
i am migrating an application over to a new server as a favor
the error
Code:
Error Number: 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 ') GROUP BY p.id LIMIT 0, 10' at line 10
SELECT p.* FROM posts p INNER JOIN tagsRel r ON(r.postId=p.id) INNER JOIN tags t ON(t.id=r.tagId)WHERE ( t.id = ) GROUP BY p.id LIMIT 0, 10
original server:
MySql 5.1.3.6 or 5.3.0 (just cant remember)
new server :
MySql 5.0.85-community-log
the code:
Code:
<?php
class Mposts extends Model {
function get ( $iId ) {
$query = $this->db->query ( 'SELECT * FROM posts WHERE id = ' . qstr ( ( int ) $iId ) );
return ( $query->num_rows () == 1 ) ? $query->row () : FALSE;
}
function getRelated ( $oTags ) {
$q = 'SELECT
p.*
FROM
posts p
INNER JOIN
tagsRel r
ON(r.postId=p.id)
INNER JOIN
tags t
ON(t.id=r.tagId)';
if ( $oTags != FALSE ) {
$q .= 'WHERE (';
foreach ( $oTags as $oTag ) {
$q .= ' t.id = ' . qstr ( ( int ) $oTag->id ) . ' OR';
}
$q = substr ( $q, 0, - 4 );
$q .= ')';
}
$q .= ' GROUP BY p.id LIMIT 0, 10';
$query = $this->db->query ( $q );
return ( $query->num_rows () ) ? $query->result () : FALSE;
}
}
the MySql:
Code:
CREATE TABLE `posts` (
`id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
`dateAdded` int(10) NOT NULL,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`content` text COLLATE utf8_unicode_ci NOT NULL,
`url` tinytext COLLATE utf8_unicode_ci NOT NULL,
`uniqueId` char(32) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniqueId` (`uniqueId`),
KEY `dateAdded` (`dateAdded`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `tags` (
`id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
`tag` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique` (`tag`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `tagsRel` (
`postId` bigint(10) unsigned NOT NULL,
`tagId` bigint(10) unsigned NOT NULL,
KEY `fk_tagsRel_tags` (`tagId`),
KEY `fk_tagsRel_posts` (`postId`),
CONSTRAINT `fk_tagsRel_profiles1` FOREIGN KEY (`postId`) REFERENCES `posts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_tagsRel_tags` FOREIGN KEY (`tagId`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
any help would be great
thanks all