sql error error staring me in the face......i just cant see it - fresh eyes needed please :)

[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

[eluser]Chad Fulton[/eluser]
Hello

The problem is in constructing your WHERE statement: the "tid = #" gets truncated to "tid = ".

The reason this happens is because you have the following code, which you use to strip out the " OR":

Code:

$q = substr($q, 0, -4)

Since you're only removing a " OR", which is 3 characters, you want:

Code:

$q = substr($q, 0, -3)

[eluser]kiedis[/eluser]
^this and besides you missing a space before the WHERE clause

SELECT p.* FROM posts p INNER JOIN tagsRel r ON(r.postId=p.id) INNER JOIN tags t ON(t.id=r.tagId) :exclaim: WHERE ( t.id = :exclaim: ) GROUP BY p.id LIMIT 0, 10

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 (';
[...]
}

[eluser]Unknown[/eluser]
Thanks guys

that done the trick
i knew it was just starring right at me........great help and as always fresh eyes often help with the simple things.

if your in the U.S, please enjoy your holiday