Joining tables help needed please

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mp3lab
Forum Newbie
Posts: 3
Joined: Wed Jul 20, 2011 9:05 am

Joining tables help needed please

Post by mp3lab »

Hi
As I'm new to PHP/MySQL
I need a little help concerning joining 4 tables in SQL query.
I have 4 tables
first is asort with 4+ columns
id catid pid tipid ...

id is primary key and catid, pid and tipid are foreign keys from tables kategorija, proizvod and tip

I want to make a query to join this 4 tables and find for example all rows with pid=4
I tryed with

Code: Select all

$q1 = "SELECT * FROM asort LEFT JOIN (kategorija, proizvod, tip)
                 ON (kategorija.catid=asort.catid AND proizvod.pid=asort.pid AND tip.tipid=asort.tipid) 
				 where tipid='$_GET[tipid]' order by cijena as
c";

but I got
"Column 'tipid' in where clause is ambiguous"

Thank you for reading this especially if you are able to help me out on this.
Bye
DVAL
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Joining tables help needed please

Post by califdon »

That means is that "tipid" is in more than one table, so you must specify which table. In your case, tipid is in both asort and tip. Probably you want to use the one in asort, so just change your where clause to:

Code: Select all

where asort.tipid='$_GET[tipid]' order by cijena asc
Apart from that, you are risking a "SQL injection" security exploit by using the raw $_GET value directly in your SQL statement. That's strongly advised against. You should first assign the $_GET value to a variable and use one of the several PHP functions that are available to "cleanse" any input to protect your database. Google "sql injection".
mp3lab
Forum Newbie
Posts: 3
Joined: Wed Jul 20, 2011 9:05 am

Re: Joining tables help needed please

Post by mp3lab »

Hi
Thank you for your quick reply :D
I tried with

Code: Select all

where asort.tipid='$_GET[tipid]' order by cijena asc
and it works fine

concerning "SQL injection"
I have function

Code: Select all

function check($x=null) {
  if(!isset($x)) return null;
  else if(is_string($x)) return mysql_real_escape_string($x);
  else if(is_array($x)) {
    foreach($x as $k=>$v) {
      $k2=mysql_real_escape_string($k);
      if($k!=$k2) unset($x[$k]);
      $x[$k2]=check($v);
    }
    return $x;
  }
}
and prior to this query I have
$_GET=check($_GET)
Can I "cleanse" input on this way?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Joining tables help needed please

Post by califdon »

That looks reasonable to me (I'm not an expert on security), although I haven't seen it done that way before. The most common method is just to assign all $_GET and/or $_POST variables to simple variables right at the beginning, using the mysql_real_escape_string() function, then just use those variables later in the script. However, I can see some advantages to subjecting the entire $_GET array variable to "cleansing" like that.
Post Reply