I can not extract results using join table

Question

I have 2 tables

first table Actors it has

 ID      name_english           picture               link
 5       Daniela Bessia         4f8ab755ed.png        daniela-bessia
 6       Mohannad Alarjan       14f8dfsf55ed.png      mohannad-alarjan
 7       Lee Tae-Im             kxe3gj64.jpg          lee-tae-im

second table actors_content

id       content_id      actors_id
1        7               5
2        5               5
3        5               6
4        5               7

Now my query is request by drama id if the visitors visit drama id number 7 ( content_id ) it should coming search on second table actors_content and select all data has content_id = 7 after select i want to make join one by one to make while for actors data without duplicate to get actors details from Actors

public function dodisplayActorsbycontent($getid){
$query = $this->db->query("SELECT `actors`.`id` , `actors`.`name_english` , `actors`.`picture` , `actors`.`link` , `actors_content`.* FROM `actors_content`, `actors` WHERE `actors_content`.`content_id` = '".$getid."'");
$total = $this->db->resultcount($query);
if($total != 0){
while($this->db->fetchrow($query)){
$list[] = $this->db->_record;
}
return($list);
}else{
return(false);
}
}

Possible duplicate of [INNER JOIN ON vs WHERE clause](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) — Dharman, Jun 16 '19 at 10:18
Time to read a introduction to querying in SQL. PS When giving a relation(ship)/association or table (base or query result), say what a row in it states re the business situation in terms of its column values. Eg Fix/finish this: Return (id#1, name_english, ...) rows where FOR SOME a.*, ac.*, (a.id, a.name_english, ...) IN actors AND (ac.id, ac.actors_id, ...) IN actors_content AND id#1=a.id AND ... AND id#2=ac.id AND ... AND ac.content_id=7 AND a.ID=ac.actors_id. — philipxy, Jun 16 '19 at 12:01

score -1 · Accepted Answer · answered Jun 16 '19 at 10:09

-1

The join need an ON clause which you've missed:

SELECT 
  `actors`.`id` , `actors`.`name_english` , 
  `actors`.`picture` , `actors`.`link` , 
  `actors_content` . * 
FROM `actors` INNER JOIN `actors_content`
ON `actors_content`.`actors_id` = `actors`.`id`
WHERE `actors_content`.`content_id` = '".$getid."'
LIMIT 0, 30

answered Jun 16 '19 at 10:09

forpas

160,666
10
38
76

LIMIT usage without ORDER BY suggestion? The SQL injection is already be mentioned in question comments – Raymond Nijland Jun 16 '19 at 11:49
1

@RaymondNijland did not even notice it as it is not the problem here. – forpas Jun 16 '19 at 11:52
2

@mohannad add an ORDER BY.. clause before LIMIT. – forpas Jun 16 '19 at 11:52
I was taking "needs" to be about the language definition & you weren't. – philipxy Jun 16 '19 at 12:35

I can not extract results using join table

1 Answers1