SQL注释与数组元素PHP

Question

This is my first question in this site hope that some one will help me over here.

I have a investor table with some fields and a project table with some files

Project table
---------------------
project_investor_id     1,26,29,30,39,48

data stored as , separated values So in my investor admin area i want show perticular project to show for logedin investor I am using this function.

public function list_all_projects_by_userid ($uid){


    $rs = mysql_query("select * from  project_table WHERE project_investor_id LIKE '$uid'  ORDER BY project_id DESC");    
    $i = 0;
    while ($row = mysql_fetch_assoc($rs)) {    
    $result[$i]['project_id'] = $row['project_id'];
    $result[$i]['project_investor_id'] = $row['project_investor_id'];
    $result[$i]['project_name'] = $row['project_name'];
    $result[$i]['project_location'] = $row['project_location'];
    $result[$i]['project_location'] = $row['project_location'];
    $result[$i]['project_phase'] = $row['project_phase'];
    $result[$i]['project_capital'] = $row['project_capital'];
    $result[$i]['project_notes'] = $row['project_notes'];
    $result[$i]['project_file'] = $row['project_file'];
    $i++;
    }
    return $result;
    }

But its not working with project have multiple investors. Please let me know how recode this?

Thank You

Answer 1

Normally, you should have another mapping table for such things. And use joins to do a search.

If you want to make your approach work, you should change your sql request like this:

 $rs = mysql_query("select * from  project_table WHERE project_investor_id LIKE '%$uid%'  ORDER BY project_id DESC"); 

See % symbols added before and after $uid. It should work fine with that.

Also, remember about sql injections! You must escape your input. with mysql_* you can do it like this:

 $rs = mysql_query("select * from  project_table WHERE project_investor_id LIKE '%".mysql_real_escape_string($uid) . "%'  ORDER BY project_id DESC"); 

Function to escape: mysql_real_escape_string. Also, read carefully about what is shown in warning section of linked page.

But still, you should better put a list of project investors into a different table. It should be something lie this:

Project table
-------------
pr_id    pr_name


project investors map table
----------------------------
pr_id         investor_id
 1              1
 1              26
 1              29

And then, your request will be like:

$rs = mysql_query("select * from  project_table as pt join project_investor_map  as pi on pi.pr_id = pi.investor_id WHERE pi.investor_id = ".mysql_real_escape_string($uid) . "  ORDER BY project_id DESC");

which will take all projects of some investor