Combine two SQL Tables with a where Variable

Question

Can't seem to get it right. I am trying to join two tables in my form and the WHERE is a variable taken from the URL (www.websiste.com?reference=38)

Both tables have the same commonkey (ProgramCode)

Table 1: programs: id_program | ProgramName | ReleaseDate | ProgramCode

Table 2: ProgramGenre: id_program_genre | ProgramCode | id_genre

I got this but its not working

$sql_select = "SELECT * FROM program
                        INNER JOIN ProgramGenre
                            ON program.ProgramCode = ProgramGenre.ProgramCode               
                    WHERE ProgramCode='$_GET[reference]'";

What am i doing wrong here?

How is it not working? Edit your question and provide more details. — Gordon Linoff, May 29 '14 at 13:04
You might need to specify the table name in your `where` clause, depending on your SQL engine, eg `WHERE program.ProgramCode='$_GET[reference]'";`. But please read up on [SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) - that's dangerous code — Hobo, May 29 '14 at 13:05
Thanks @Hobo. Changing the line for the 'where' fixed it. Working perfectly. Thanks a lot — Ewald Bos, May 29 '14 at 13:14
No problem - but please don't ignore my comment about SQL injection. A malicious user could pass a dodgy value and do things like deleting your data or dropping your database. — Hobo, May 29 '14 at 13:17
Yes was looking into that before as well, now i am using PDO — Ewald Bos, May 29 '14 at 13:24

score 0 · Accepted Answer · edited Sep 04 '14 at 03:48

Try using

$sql_select="SELECT * from programs p,ProgramGenre g where p.ProgramCode=g.ProgramCode and p.ProgramCode='".$_REQUEST['reference']."'";

or you can use

$sql_select = "SELECT * FROM programs
                        INNER JOIN ProgramGenre
                            ON programs.ProgramCode = ProgramGenre.ProgramCode               
                    WHERE ProgramCode='".$_GET['reference']."'";

score 0 · Answer 2 · answered Jun 04 '14 at 05:03

SQL doesn't know which column ProgramCode refers to.

Assuming a parametrized query,

$sql_select = "SELECT * FROM program
                    INNER JOIN ProgramGenre
                        ON program.ProgramCode = ProgramGenre.ProgramCode               
                WHERE program.ProgramCode=?";

Combine two SQL Tables with a where Variable

2 Answers2