Page 1 of 1

Joining tables help needed please

Posted: Sat Mar 24, 2012 6:06 pm
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

Re: Joining tables help needed please

Posted: Sat Mar 24, 2012 8:09 pm
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".

Re: Joining tables help needed please

Posted: Sun Mar 25, 2012 3:21 am
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?

Re: Joining tables help needed please

Posted: Sun Mar 25, 2012 11:43 am
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.