Query returns data along with data from another table

Question

I'm quite experiences in programming in PHP, and for a little side project I'm making I want to get some information from the database

I made my own sql handler, with a singleton so the connection doesn't get overflowed.

This is my query function:

<?php    
public static function query($q){
        $a = '';
        $b = '';
        self::$queries[] = $q;
        if(strpos($q, 'INSERT') !== false){
            if(!mysql_query($q)){
                self::$err[] = mysql_error()."<br />Query: ".$q;
                return false;
            }
            return true;
        } elseif(strpos($q, 'SELECT') !== false){
            echo $q;
            if(!$res = mysql_query($q)){
                self::$err[] = mysql_error()."<br />Query: ".$q;
            return false;
            } else {
                if(mysql_num_rows($res) > 1){

                    while($r = mysql_fetch_assoc($res)){
                        foreach($r as $key => $val){
                            self::$results[$r['id']][$key] = $val;
                        }
                    }
                    return self::$results;
                } else {
                    $res = mysql_fetch_assoc($res);
                    foreach($res as $key => $val){
                            self::$results[$res['id']][$key] = $val;
                    }
                    return self::$results;
                }
            }
        } elseif(strpos($q, 'UPDATE') !== false){
            if(!mysql_query($q)){
                self::$err[] = mysql_error()."<br />Query: ".$q;
            return false;
            }
            return true;
        } elseif(strpos($q, 'DELETE') !== false){
            if(!mysql_query($q)){
                self::$err[] = mysql_error()."<br />Query: ".$q;
            return false;
            }
            return true;
        }
    }
?>

Now I want to get some data from a table that I created(Table contains 2 foreign keys) Table:

    CREATE TABLE IF NOT EXISTS `loc_npc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loc_id` int(11) NOT NULL,
  `npc_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `loc_id` (`loc_id`),
  KEY `npc_id` (`npc_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

ALTER TABLE `loc_npc`
  ADD CONSTRAINT `loc_npc_ibfk_2` FOREIGN KEY (`npc_id`) REFERENCES `npcs` (`id`),
  ADD CONSTRAINT `loc_npc_ibfk_1` FOREIGN KEY (`loc_id`) REFERENCES `locations` (`id`);

The problem when I run this query SELECT id, loc_id, npc_id FROM loc_npc WHERE loc_id = 9 Is that it somehow returns data from another table, aswel as the correct data.

The query is ran in another handler:

public function getLocationNpc($id){
    //return $this->sql->query("SELECT e.`name`, e.`desc`, e.`id` FROM npcs e LEFT JOIN(loc_npc i CROSS JOIN locations l) ON (e.id = i.npc_id AND i.loc_id = l.id) WHERE i.loc_id = $id");
    $npcH = new npcHandler();
    $npcId = $this->sql->query("SELECT id, loc_id, npc_id FROM loc_npc WHERE loc_id = $id");
    echo '<pre>'.print_r($npcId, true).'</pre>';
    foreach($npcId as $nId){
        $npcArr[] = $npcH->getNpc($nId['npc_id']);
    }
    return $npcArr;
}

I tested the queries in phpmyadmin and it just returns the correct data, unlike the query executed through my handler.

I'm pretty sure it's something small and stupid, but I just can't seem to find it.

Welcome to Stack Overflow! [Please, don't use `mysql_*` functions in new code](http://stackoverflow.com/q/12859942). They are no longer maintained and the deprecation process has begun, see the [red box](http://php.net/mysql-connect). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli); [this article](http://php.net/mysqlinfo.api.choosing) will help you decide which. If you choose PDO, [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). — vascowhite, Dec 03 '12 at 21:44
Although I've been meaning to learn PDO, I haven't really had time to take up a tutorial. But what I'm trying to do should still be possible with mysql_ functions, right? I'm not planning on using this for a long time, it's just to test some stuff. — candanz, Dec 03 '12 at 21:51
You're wasting time learning mysql_* functions. Prepared statements are not complicated. I'm sorry, but I don't give help for mysql_* functions. — vascowhite, Dec 03 '12 at 21:54
Oh I'm not learning mysql_* functions, I know alot of them already, I've been PHPing since the I was 11-12, which is 8-7 years ago, I'm just curious why it's including data from another table, even when I'm not calling the table at all. Never had this problem occur — candanz, Dec 03 '12 at 22:01

score 0 · Accepted Answer · answered Dec 03 '12 at 23:33

0

You don't appear to be clearing the contents of self::$results anywhere - are you getting results from a previously run query you've run?

answered Dec 03 '12 at 23:33

John C

8,223
2
36
47

Oh my god, this fixxed it. Thanks! I'm an idiot... Haha – candanz Dec 03 '12 at 23:45

Query returns data along with data from another table

1 Answers1