PHP - in_array, loops, and if statements

Question

I have a database for scheduling field techs. Each field tech has his own unique login id that can see only his own specific jobs. I have recently added the function to add multiple techs to a single ticket. I am currently running the following loop. Each tech can currently see each job he is assigned on alone. When I start to assign multiple tech is where it gets tricky. Let's say I have 3 techs total: Mickey, Dustin, and Michael.

If I assign 1 job as a multi tech job for Michael and Dustin it appears fine in both techs lists. If I then assign another job for Mickey and Dustin. The previous job I assigned as a multi tech for Dustin disappears from his list but still shows on Michaels.

I am assuming something is getting caught up in the loop but I am having trouble figuring it out. Im a noob. Need Help.

$gettechresult = mysql_query("SELECT tech_id2, tech_id FROM jobs WHERE ((status = '2')) ORDER by $listsort;",$link);

while ($p_row =mysql_fetch_array ($gettechresult) ) {
    $tech_id2 = $p_row[tech_id2];
    $tech_id = $p_row[tech_id];
    $fids = explode(",",$tech_id2);
    $fids2 = explode(",",$tech_id);

    if (in_array($l_tech_id, $fids)) 
    {
        $techteam = 'TRUE';
    }
    else{
        $techteam = 'False';
    }
    /// Used to make sure the query is not returning False Values        
    echo "$techteam,";

    if ($grp_all == "1")
    { 
        $jobresult = mysql_query("SELECT * FROM jobs WHERE ((status = '2')) ORDER by $listsort;",$link); 
    }
    elseif ((in_array($l_tech_id, $fids))) 
    { 
        $jobresult = mysql_query("SELECT * FROM jobs WHERE ((tech_id2 ='$l_tech_id' AND status = '2' || tech_id2 IN ('".$fids."') AND status = '2')) ORDER by $listsort;",$link); 
    }
}

Here are pictures of my current resultsThis is an image of the scheduled jobs currently in the database

Image of Field tech Dustin's Scheduled List - $l_tech_id = 16

The only job that is not reporting back correctly is the one where Mickey and Dustin are Multiple Techs. Dustin's is the only one that isn't reporting one job. $tech_id2 is the field that captures this info as an array.

You should really be using a Relational table lookup for this, not just more columns on your Jobs table and subsequent queries. Something like `job_assignment` with a `jobID` and a `techID` - then you can `select ... from jobs inner join job_assignment on jobs.id = job_assignment.jobID where ...`. Especially if your DB is on a separate server (it should be...) you're offloading additional process/looping time in your PHP app by having the Sql server do simple joins. — Scott Byers, Jun 16 '17 at 17:41
This is a 10 year old system created by another developer we are trying to move forward at this point with working with what we have. Please don't respond to questions unless you can offer an answer. — Grisham Designs, Jun 16 '17 at 18:20
Sorry some of the items are globals established earlier in the code $l_tech_id = Login Tech Id $grp_all == "1" means admin — Grisham Designs, Jun 16 '17 at 18:35
Can we see what your tables looks like and what you are doing to update these multi-tech jobs? Also, what these current queries yield? — nerdlyist, Jun 16 '17 at 18:38
**Hint**: For debugging try to use something like `die(var_dump($fids))` before `if` statement to see what you really have. — SaidbakR, Jun 16 '17 at 18:55
that returned the following array(1) { [0]=> string(1) "8" } @nerdlyist — Grisham Designs, Jun 16 '17 at 19:22
I would very strongly suggest that you do not store data like `#,#` in a column and more appropriately normalize that. As for your current issue I am not following how this works there are too many unknowns. — nerdlyist, Jun 16 '17 at 19:37
I don't understand the difference between the fields `tech_id` and `tech_id2`. If you are going to denormalise your table (which others have rightfully identified as a bad idea), then why not keep that limited to one column listing the techs, instead of heaving to deal with 2 columns? — trincot, Jun 16 '17 at 19:43
This system was initially a single tech system. We used the tech_id field. As of about a month ago we added the tech_id2 and created a way to schedule 2 techs to each ticket. Now we are wanting to expand that capability by adding an unlimited number of techs to each ticket. We having a multi select html form sending POST data to make the array that goes inside the tech_id2 column. — Grisham Designs, Jun 16 '17 at 19:51
So you are now ignoring `tech_id` and always using `tech_id2`? Do I understand that correctly? — trincot, Jun 16 '17 at 19:52
Yes that is correct, if I can get this idea to function correctly tech_id would no longer be needed. — Grisham Designs, Jun 16 '17 at 19:57

score 0 · Accepted Answer · answered Jun 16 '17 at 20:00

The SQL where condition tech_id2 ='$l_tech_id' will be false as soon as tech_id2 has a combination of 2 or more values, and the tested value is not the first in the list.

Instead I would suggest using the MySql find_in_set function:

where find_in_set(?, tech_id2) and status = 2

Once you have that set up, you can remove the corresponding processing logic from PHP.

If you go for this tech_id2 solution, then drop the use of tech_id, or it would become a mess as to what really is meant by each reference.

NB: I put a ? in the proposed SQL on purpose, since you really should be using prepared statements to avoid SQL injection. The ? represents the $l_tech_id value.

Also, consider moving away from mysql_ functions, as they have been deprecated for years now, and are no longer supported in PHP 7.x.

PHP - in_array, loops, and if statements

1 Answers1