Hello, i have function that need to connect to same table 2 times and this check is happening in while loop to know show person or not
function rw3($conn, $worker, $from, $to, $inc, $count){
$result = mysqli_query($conn, "
SELECT person
FROM car_bussy
WHERE person = '".$worker."'
AND (date BETWEEN '".$from."' AND '".$to."')
AND incident_id='".$inc."'
AND count_id!='".$count."'");
$row = mysqli_fetch_array($result);
$result2 = mysqli_query($conn,"
SELECT person
FROM car_bussy
WHERE person = '".$worker."'
AND date BETWEEN '".$from."' AND '".$to."'
AND (incident_id!='".$inc."' || incident_id='".$inc."' AND count_id!='".$count."')");
$row2 = mysqli_fetch_array($result2);
if (mysqli_num_rows($result2)>0){
$person_works='w';
}else{
$person_works=null;
}
if(($person_works=='') || ($row['person']!=$row2['person'])){
return 'y';
}else{
return 'n';
}
}
There are 500 persons in each dropdown (3 total dropdowns) and it takes more than 2 seconds to load but if i remove this check it works fast!
I have all good with indexes! Is there anything i can do? I do not see a possible way to merge querys or something
The only difference between the first and second queries, if I read correctly, is that the second one will also return rows where incident_id != $inc. So you might be able to save time by just running the second query and scanning the results.
Your code seems to presume that the first query will always return at least one row, because you don’t check what’s in $row prior to using it for comparison towards the end. If that’s correct, you could just get the row-count from the second query and subtract one from it, which should give the same result.
Do you call this function individually for the 500-odd people in each drop-down? If you do, I suspect what @Dormilich was suggesting was that a prepared statement would speed this up a little. You’d need to alter the structure of the code a little, so perhaps your calling code would prepare the statement, then pass a pointer to it into this function to execute it with the varying values.
ETA: How does the second condition in this if statement ever do anything?
Your function returns a boolean (well, it should). The starting point is to figure out for which data filter the function should return true and for which it should return false.
