I am new to php & MySQL. I have a db that contains two tables called person (property owner) and property. There is a one-to-many relationship between the tables. For instance, a person can own many properties.
I am using php to retrieve data from the db (I have already established a connection with the db).
What I am trying to achieve is this: if a query is carried out for a person's name - (whether it is fname or lname) - then a list of all the properties owned by this person will be displayed in my search results form. The output will be shown as lname, fname, property number, property road, property zipcode.
However, with this code I am only getting two results output per property owner name. :
$request = mysql_query("SELECT person.fname, person.lname, property.number, property.road, property.zipcode,
FROM person p
INNER JOIN property py
ON p.id = py.p_id
WHERE p.fname LIKE '%$search%'
OR p.lname LIKE '%$search%'
ORDER BY p.lname");
$number = mysql_num_rows($request);
if ($number == 0){
$result .= 'No results'.' '.$search;
} else {
$propertyinfo= array();
$count = 0;
$real = false;
$real_within_array = 0;
while ($nrow = mysql_fetch_array($query)){
$lname = $nrow['lname'];
$real = false;
for ($l = 0; $l < count($propertyinfo); $l++)
{
if ($propertyinfo[$l][0] == $lname)
{
$real = true;
$real_within_array = $l;
break;
}
}
if ($real)
{
$fname = $nrow['fname'];
$lname = $nrow['lname'];
$number = $nrow['number'];
$road = $nrow['road'];
$zipcode = $nrow['zipcode'];
$propertyinfo[$real_within_array][1][1] = $fname;
$propertyinfo[$real_within_array][2][1] = $lname;
$propertyinfo[$real_within_array][3][1] = $number;
$propertyinfo[$real_within_array][4][1] = $road;
$propertyinfo[$real_within_array][5][1] = $zipcode;
}
else
{
// Get all the data from the row.
$fname = $nrow['fname'];
$lname = $nrow['lname'];
$number = $nrow['number'];
$road = $nrow['road'];
$zipcode = $nrow['zipcode'];
$propertyinfo[$real_within_array][1] = $fname;
$propertyinfo[$real_within_array][2]= $lname;
$propertyinfo[$real_within_array][3][0] = $number;
$propertyinfo[$real_within_array][4][0] = $road;
$propertyinfo[$real_within_array][5][0] = $zipcode;
$count++;
}
}
Example: Adam Smith owns 4 properties, but I will only get a result of two of his properties instead of all 4 in a list.
Please help. I have tried my very best to sort this out but I seem to have stumbled upon a difficult problem.
Your help is much appreciated. Thank you in advance.