php in clause not working for prepared statement

Question

I am trying to make a mysql query with a prepared statement including an "IN" clause. This is the statement:

$stmt1 = $c->db->prepare("SELECT ITEM_URL,VIRALITY FROM RSSINGEST WHERE userid=(?) AND ITEM_URL IN (SELECT url FROM person_url WHERE person=(?) AND userid=(?))");
$stmt1->bind_param("sss", $userid,$name,$userid);

However I always get the error: "Call to a member function bind_param()..." which occurs when the prepared statement is wrong.

Basically I am trying to find all urls from one user who also has those urls in another table. In this other table called "person_url" every entry has also a "person". I want only those with a special "person" in it, as you can see in the statement.

Maybe somebody can tell me if there is a other way to do this or where the error is?

EDIT 1: userid is a string, they name can be misleading.

EDIT 2: Create Statements for both tables:

  CREATE TABLE IF NOT EXISTS `rssingest` (
  `item_id` varchar(500) CHARACTER SET latin1 NOT NULL,
  `feed_url` varchar(512) CHARACTER SET latin1 DEFAULT NULL,
  `item_title` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `item_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `item_url` varchar(512) CHARACTER SET latin1 NOT NULL,
  `item_category_id` int(11) DEFAULT NULL,
  `fetch_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `fb_share` int(11) DEFAULT NULL,
  `fb_like` int(11) DEFAULT NULL,
  `fb_comment` int(11) DEFAULT NULL,
  `tw_count` int(11) DEFAULT NULL,
  `pinterest` int(11) DEFAULT NULL,
  `linkedin` int(11) DEFAULT NULL,
  `googleplus` int(11) DEFAULT NULL,
  `stumbleupon` int(11) DEFAULT NULL,
  `virality` int(11) DEFAULT NULL,
  `userID` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `people_url` (
  `person` varchar(100) CHARACTER SET latin1 NOT NULL,
  `url` varchar(500) CHARACTER SET latin1 NOT NULL,
  `virality` int(20) NOT NULL,
  `insertdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `userID` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

EDIT 3: Full Error

Fatal error: Call to a member function bind_param() on a non-object in C:\xampp\htdocs\monitorUser\websiteBuilder.php on line 163

EDIT 4: Tunnel vision at its finest. I have written person_url in the statement instead of people_url. Thanks everybody.

The prepare failed...because the table in your code does not match the MySQL structure — MonkeyZeus, Oct 19 '15 at 15:07
When in doubt, echo and output as many errors as the language will allow — MonkeyZeus, Oct 19 '15 at 15:08
Don't listen to @Alex because I have never seen that syntax before. You are basically telling MySQL to evaluate your strings like math. This is sure to cause headaches later on. Actually listen to only half of what Alex said. Just do `SELECT ITEM_URL,VIRALITY FROM RSSINGEST WHERE userid=? AND ITEM_URL IN (SELECT url FROM person_url WHERE person=? AND userid=?)`. Since you are preparing your statements, the escaping and everything is being taken care of on the DB-side — MonkeyZeus, Oct 19 '15 at 15:10
@chris85 you are right! .I just reread doc http://php.net/manual/en/mysqli-stmt.bind-param.php so there is no need even for quotes so just `?` — Alex, Oct 19 '15 at 15:50

score 0 · Answer 1 · answered Dec 24 '15 at 03:07

0

The query in $stmt1 should be

$stmt1 = $c->db->prepare(
  "SELECT item_url, virality 
  FROM rssingest
  WHERE userid=(?)
  AND item_url IN (SELECT url FROM people_url WHERE person=(?) AND userid=(?))"
);

answered Dec 24 '15 at 03:07

wogsland

9,106
19
57
93

php in clause not working for prepared statement

1 Answers1