i have added the group by which seems to work wonders!
it is a tad odd tho, it seems to return all the queries twice, even the once that are only in the data once...
i added a count and splice to remove the extra data.. assuming it will always do this and want cause an error else where.
any ideas why i get a repetition?
Code: Select all
$q="SELECT DISTINCT schedule.schedule_id, schedule.date, schedule.start_time, schedule.end_time,buildings.lon,buildings.lat,buildings.name,buildings.building_id,events.event_id,events.organisation_id, events.name AS event_name, buildings.name AS building_name FROM buildings INNER JOIN (events INNER JOIN schedule ON events.event_id = schedule.event_id) ON buildings.building_id = schedule.building_id WHERE ((events.user_id='$user_id')OR (events.organisation_id IN('$orglist3')) )AND schedule.date>=curdate() GROUP BY buildings.building_id";
$r= mysqli_query($dbc, $q); // run the query
$num_rows = mysqli_num_rows($r);
while($row = mysqli_fetch_array($r))
{
$thelist[$i]['building_id'] = $row["building_id"];
$thelist[$i]['building_name'] = $row["building_name"];
$thelist[$i]['building_lat'] = $row["lat"];
$thelist[$i]['building_lon'] = $row["lon"];
$i++;
}
$countit=count($thelist);
//half the array
$countit = $countit/2;
array_splice($thelist, $countit);
sort($thelist);
print_r($thelist);
// i hope it ALWAYS returns all the buildings twice. it works for yr login and mine.
Testing was only returned once by the query before..
Code: Select all
[color=#8040BF]Array ( [] => Array ( [building_id] => 72 [building_name] => ownership [building_lat] => 51.2192561 [building_lon] => 1.4000388 ) [1] => Array ( [building_id] => 46 [building_name] => Testing Two [building_lat] => 51.2233922 [building_lon] => 1.3856348 ) [2] => Array ( [building_id] => 45 [building_name] => Testing [building_lat] => 51.2231481 [building_lon] => 1.4022725 [/color])[color=#804000] [3] => Array ( [building_id] => 45 [building_name] => Testing [building_lat] => 51.2231481 [building_lon] => 1.4022725 ) [4] => Array ( [building_id] => 46 [building_name] => Testing Two [building_lat] => 51.2233922 [building_lon] => 1.3856348 ) [5] => Array ( [building_id] => 72 [building_name] => ownership [building_lat] => 51.2192561 [building_lon] => 1.4000388 ) ) [/color]