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
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
If I understood you right, your main problem is your datababse-design.
You should not save multiple investors comma-seperated, instead you should build a n:m-relation (if investors can invest into many projects and many projects can have many investors) from projects to investors (with a "join-table"). That way, such querys would be pretty easy done.
Check out articles referencing "Database normalization" to find out more about good database design. After that, joins and sub-queries will help you to find the right way of doing this.
And in addition, check out articles about sql-injections (if you can't be absolute sure $uid is a number)