PHP Array search

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Eowyne
Forum Newbie
Posts: 6
Joined: Sat Sep 13, 2008 6:36 pm
Location: Netherlands, The

PHP Array search

Post by Eowyne »

Hi there,
First of all, i'd like to excuse if this question has already been answered, but I don't know keywords to search for it.. (it's a though question in my head, maybe it isn't though at all.. i don't know..)

Anyway, this is my problem:

I have 2 tables, let's call them `ToDo` and `Done`.
Bot tables have more than 10k values, of course, `ToDo` has more values than `Done`, but let's keep it at 10k.

Now, to query and requery those tables time after time to solve my troubles, seem like a (too much) immense data load on my database, So I've decided to query them just once, and drop the data inside an Array, using the following PHP Code:

Code: Select all

<?php
function QueryIntoArray($query){
    settype($retval,"array"); 
    $result=MySQL_Query($query);   
    if(!$result){
        print "Query Failed";
    }        
    for($i=0;$i<MySQL_Num_Rows($result);$i++){
    for($j=0;$j<MySQL_Num_Fields($result);$j++){
            $retval[$i][MySQL_Field_Name($result,$j)] = MySQL_Result($result,$i,MySQL_Field_Name($result,$j));
    }//end inner loop
  }//end outer loop
return $retval;
}//end function
?>
 
This should Query the tables and put the values inside an array.

Now my problem:
I have a field, called `Tag`, this field can differ from both tables

e.g. Table `ToDo` has the following values:
100
133
910
9320
14
10

Table `Done` has this values:
100
133
20
14
10

Now, I wish to create a function which checks both arrays, to find the differences and outputs this:

100
133
14
10

910
9320

20

Color meanings: Green = In Both Tables, Blue = Only in `ToDo` Table, Red = Only in `Done` table..

I hope I make sense till now..
I know of the function In_Array($needle,$haystack), but if I use a For Loop

Code: Select all

 
for($i=0;$i<count($X);$i++){
    for($c=0;$c<count($Y);$c++){
        if(in_array($Y[$i]["tag"],$X["tag"],false)){
            echo "true <br />";
        }else{
            echo "false <br />";
        }
    }
}
 
In the first For loop, I'm looping trough $X (array of `ToDo`),
Then, in the second, I loop trough $Y (array of `Done`)
to see if the tag at which I am on $X, is available in $Y, and the other way around..

but: it gives me nothing at all, so I must do something wrong.. right?

Can anyone point me into a direction.. or at least tell me what I'm doing wrong?
and please, don't mind asking for a better description of my problem.. I've tried my best now, but it might be non-suited..

Yt, Eowyne
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: PHP Array search

Post by Stryks »

Well ... so you have array $x and array $y.

To find out what is the same in both ...

Code: Select all

$in_both = array_intersect($x, $y);
To find what is in $x that isn't in $y ...

Code: Select all

$in_x = array_diff($x, $y);
... and vise versa ...

Code: Select all

$in_y = array_diff($y, $x);
That should achieve what you want .... however ... I kind of get the feeling that this could be achieved much more easily.

As an example ... should it not be possible to just add a new column to the 'todo' table called 'done'? Then just mark the item as done. To pull a todo list you get the ones without a marked 'done' field. To pull a done list, do the opposite; pull a list where done is true.

That way you only have one list.

Of course, this assumes that there is a parity between the two tables - so that if all tasks were completed right now, both tables would hold the exact same number of rows.

You should be able to work up a query to populate the 'done' status to bring it all up to date, and then just slightly alter your logic to use this single table approach.

Of course ... that's easy for me to say ... I dont know the specifics of the project and I don't have to do the work. :)

Anyhow .. food for thought.

Cheers
User avatar
Eowyne
Forum Newbie
Posts: 6
Joined: Sat Sep 13, 2008 6:36 pm
Location: Netherlands, The

Re: PHP Array search

Post by Eowyne »

Indeed, it would be much easier to create a new column based on the `done` list, and set that column to 0's and 1's if (not)done..
There's just one problem:
The two databases are a 'snapshot' of 4 months working in Excel, and here comes the worst part: it's done by hand, meaning that it's full of errors... Tags are miscalled, missed, or just done double.
My employer has asked me to review both sheets (the `ToDo` sheet is permanent, no changes there, so it should contain all the tags I need to have in the `Done` sheet) and to find out which tags have, haven't or are (mis)used.
To do this, I've setup a MySQL database, in which the two sheets now reside as two tables.

Íf, however, I use a 'Done' column in the ToDo table, I will miss the wrong inputted tags, and also miss the double, triple or quadruple tags.. that's why I have PHP to compare those values for me and color-mark them, to create a visual representation of the (manual) errors.

So, If I use array_intersect and array_diff, will I still need to loop trough both the arrays in an Outside and a Inside loop, or can this be done in just one loop (Looping Y, and check the values)?

Thnx in advance, Eowyne
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: PHP Array search

Post by Stryks »

Once you have the arrays, you wont have to loop at all. Just pass the arrays to the three lines as per my previous post, and $in_both, $in_x, and $in_y should hold all the info you need.

Once you get this data sorted, what is the plan? To set up a front end to manage the data, or just to find out what needs to be fixed in the spreadsheet? *shudder*
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: PHP Array search

Post by Stryks »

Oh ... I just took a look at how you're building your arrays. This approach isn't going to work directly on multi-dimensional arrays.

Can you post the table structures for me? There'll be a way to get what we need, but at the moment I can't visualise your tables or your arrays.

Cheers
User avatar
Eowyne
Forum Newbie
Posts: 6
Joined: Sat Sep 13, 2008 6:36 pm
Location: Netherlands, The

Re: PHP Array search

Post by Eowyne »

Sure, sorry for my tad-late reply, but I've got to sleep once in a while ;)

Here's the tablestructure:

`Todo`:
LocID - Integer
Location - VarChar
Tag - VarChar
Desktop - VarChar
Docking - Bool
ActualUserID - VarChar
Floor - VarChar
Chamber - VarChar

(ps. there are more fields, but I don't use those)

`Done`
Asset - VarChar (e.g. D009123456)
Serial - VarChar (e.g. CZC98B2AA2)
Tag - VarChar
Location - VarChar
Desktop - VarChar
Docking - Bool

To give you an Idea of the values in each field:

`ToDo`

||1||New York, 67th street 420||2920||DC7800||0||PJones08||5||402||
||2||Amsterdam, Nieuwe Markt 1||Spare||DC7700||1||KApple||1||NULL||

`Done`
||D009123456||CZC920DK01||Spare||New York, 67th Street 420||DC7800||0|| <-- Which is wrong, this should be a different tag
||D009024900||CZC911PSZ2|| 2001||Bruxelles, Marie NareLaan 1||DC7800||1||

I hope this makes sense in the way I put it.. anyway, you might get the picture here;

As you can see, I have to check the Tag in `Todo` against the Tag in `Done` in order to find out if it has been (mis)used, or hasn't been used...
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: PHP Array search

Post by Stryks »

Sorry ... I'm running very low on time to come back and look at this.

For now, I'll just suggest an alternative to your current QueryToArray function.

Code: Select all

<?php
function QueryIntoArray($query){
    $result = MySQL_Query($query);  
    if(!$result) {
        print "Query Failed";
    }        
 
   if(mysql_num_rows($result) > 0) {
      while ($row = mysql_fetch_assoc($result)) $retval[] = $row;
      return $retval;   
   } else return array();     
}
I'm thinking that it may be best to let mysql do the work here, although it will take possibly 3 queries to pull the data. And keep in mind that you are currently using two anyhow. But unless someone else can chip in the SQL for this approach, it'll have to wait because I don't have time right now to work it out.

The PHP comparison is still another option, but unfortunately it is a little more complex than I had first thought. I didn't have in mind that you'd be sorting this across two entire muti-dimensional datasets.

Anyhow ... good luck, and when I get a chance tomorrow sometime I'll check back in and see what I can think of.

Cheers
User avatar
Eowyne
Forum Newbie
Posts: 6
Joined: Sat Sep 13, 2008 6:36 pm
Location: Netherlands, The

Re: PHP Array search

Post by Eowyne »

Hey, thanks for the function, It looks like it works (at least.. I don't get [s]much[/s] Any errors :'))
Anyway, I'm not as good in the Array functions as I am with Strings (read: I Suck at arrays).

I now have two foreach loops;

Code: Select all

 
    foreach($Y as $key => $value){
        foreach($value as $key2 => $value2){
            echo "Key: $key2; Value: $value2 ||\n";
        }
        echo("<hr>");
    }
 
This actually gives me all the items in each array value; however, this gives me ALL data.
I Do need All data, but don't wish it to display..

Lets say, I only wish to show Location and Tag, how do I get to that?
After that, I'd wish to check if The Tag Values are shown in $X, combined with the location, it should be giving me a Yes, or No.. simple as that.. (sounds like some kind of IF statement to me..(array_intersect) maybe?)

but, before showing the results, I'd have to check if the Values are double or exist even more than two times. ÁND it occurs that Tags SHOULD be Double or Triple, so it shouldn't mark those values..

Is this impossible... or is there a way to get to it?

Thnx in advance, Eowyne
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: PHP Array search

Post by Stryks »

Eowyne wrote:Lets say, I only wish to show Location and Tag, how do I get to that?
I'm assuming you'd mean something like this ...

Code: Select all

foreach($Y as $y_row) {
   echo "Location: {$y_row['Location']}; Value: {$y_row['Tag']} ||\n";
}
... depending you the format you want. You get the idea though.
Eowyne wrote:but, before showing the results, I'd have to check if the Values are double or exist even more than two times. ÁND it occurs that Tags SHOULD be Double or Triple, so it shouldn't mark those values..
I'm not too sure what you mean by this actually. Where would they occur 2 or more times? The same tag in the $X or $Y dataset numerous times? Or a single tag in $X ocurrs in $Y numerous times?

"What exactly are you wanting to output?" might be a better question to ask. Are you just wanting to show matches between $X and $Y, except where there is more than one match? Or only where there is more than one?

It's just that we're juggling two arrays of around 10,00 rows each, and if you only actually need to use Location and Tag, you should rewrite your queries to pull just that data. It'll speed up the queries and save a lot of useless info being shuttled around in arrays.

If you could give description of what you actually want output, and what data is needed to achieve this, that might help point you in the right direction a bit more.

Cheers
User avatar
Eowyne
Forum Newbie
Posts: 6
Joined: Sat Sep 13, 2008 6:36 pm
Location: Netherlands, The

Re: PHP Array search

Post by Eowyne »

Ok, So I Have a query (2 actually); One which pulls all the Locations and Tags from `Todo` ($X), one which pulls the Asset, Tag and Location from the `done` table ($Y).

I'd like to check if ALL the Tags per location in $Y show up in $X. After that, I wish to check if there are values in $X Which don't show up in $Y (Missed tags) and after that, I'd like to check if there are values in $Y which don't show up in $X (Misnamed tags).

However, Tags can be used multiple times, so if the tag 'Supply' shows up 3x in $Y, then it shouldn't hit on a single occurrence of 'Supply' in $X...

When I've done that, I wish to display each outcome ($X <=> $Y, $X <= $Y, and $Y=>$X) in a table
So I guess i'd have to make an array for each possibility.. and add values to that array as soon as I start checking them?


[overview]
From table Todo:
Location, Tag

Code: Select all

 
SELECT `Location`, `Tag` FROM `todo` ORDER BY `Location`, CAST(`tag` AS UNSIGNED)
 
From table Done:
Asset, Location, Tag

Code: Select all

 
SELECT `Asset`,`Location`,`Tag` FROM `Done` WHERE `Location` ='".$_GET['location']."'
 
From Table Done, I'd only use Location and Tag to find equals, Asset will be displayed when I'm done with it, so it should be inside the query.
As you can see in my Query, I Do select all from `todo`, while selecting just one location from `Done`.

This is to give the user an ouput like

Code: Select all

 
Asset || Location || Tag || Could Be ||
D009 || Haarlem || [color=#FF0000]201[/color] ||   501
D009 || Haarlem || [color=#40BF00]100[/color] || --
 
So, the Red color marking on '201' means that it doesn't exist at the actual location, but is used somehow.
The Green color marking on 100 means it both appears in Done as in Todo, so it's OK.

the 'could be' column is there to show values which are in `Todo` by that specific location, but isn't used in `Done` yet.
---> This is a Nice to Have however, if the CouldBe-column is kinda impossible to do, then i'd just wish to display an alternate table which displays all those values which are in `Todo` but not in `Done`, and otherwise
[/overview]

I hope this makes more sense..
User avatar
Eowyne
Forum Newbie
Posts: 6
Joined: Sat Sep 13, 2008 6:36 pm
Location: Netherlands, The

Re: PHP Array search

Post by Eowyne »

WoW WoW WoW!
I Think I got it to work...

Code: Select all

 
$X = QueryIntoArray("SELECT `Tag` FROM ToDo WHERE `Location` = '". $Location ."' AND Needed = 1 ORDER BY CAST(`Tag` AS UNSIGNED) ASC");
$Y = QueryIntoArray("SELECT `Tag` FROM Done WHERE `Location` = '".$Location."' ORDER BY CAST(`Tag` AS UNSIGNED) ASC");
echo("<b>Tags</b><br />
<table width='200px' cellpacing='0' style='font-size:10px;'>
        <thead>
            <tr>
                <th>Needed</th>
                <th>Done</th>
            </tr>
        </thead>
        <tbody>
        ");
        if(COUNT($X) >= COUNT($Y)){
            for($i=0;$i<=COUNT($X);$i++){
                if(in_array($X[$i],$Y)){
                    $XColor = "green";
                }else{
                    $XColor = "red";
                }
                if(in_array($Y[$i],$X)){
                    $YColor = "Green";
                }else{
                    $YColor = "Red";
                }
                print("<tr><td style='background-color:$XColor;color:white;font-weight:bold;'>".$X[$i]["Tag"]."</td>
                                     <td style='background-color:$YColor;color:white;font-weight:bold;'>".$Y[$i]['Tag']."</td></tr>");
            }
        }else{
            for($i=0;$i<=COUNT($Y);$i++){
                if(in_array($X[$i],$Y)){
                    $XColor = "green";
                }else{
                    $XColor = "red";
                }
                if(in_array($Y[$i],$X)){
                    $YColor = "Green";
                }else{
                    $YColor = "Red";
                }
                print("<tr><td style='background-color:$XColor;color:white;font-weight:bold;'>".$X[$i]["Tag"]."</td>
                                     <td style='background-color:$YColor;color:white;font-weight:bold;'>".$Y[$i]['Tag']."</td></tr>");
            }
        }
 
 
This outputs me:

Code: Select all

 
Needed || Done
[color=#008000]20[/color]  [color=#008000]20[/color]
[color=#008000]21[/color]  [color=#008000]21[/color]
[color=#FF0000]23[/color]  [color=#008000]24[/color]
[color=#008000]24[/color]  [color=#008000]27[/color]
[color=#008000]27[/color]  [color=#FF0000]28[/color]
[color=#008000]30[/color]  [color=#008000]30 [/color]
 
There is just one problem remaining;
I have Tag's called 'Supply' (numbers too).
These occur several times, but: They all get 'Hit!' because it occurs once in both tables, no matter that the first table has 29 ocurrences and the second 10..

now is there an array_count_values() function, but that only gives me a number, it doesn't let me color-mark them the way i can in the output above...
is there another solution to this? (and, if you think my code can be better, please provide me tips.. i'm a bit clumsy from time to time)

Thx, Eowyne


-----

PS: I Will be on vacation 'till Tuesday 23th september, please do not think that I'm not interested in this topic anymore, I am.!
Last edited by Eowyne on Tue Sep 16, 2008 3:40 pm, edited 1 time in total.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: PHP Array search

Post by Stryks »

This is actually a slightly different proposition to the original post, in that you aren't loading all the data, just the tags, and you're only doing it for a specific location.

Given that the data is simplified, you might be able to take a simpler route. I know I'm probably being dense here, but I still don't get the multiples issue. I'll just take a shot at building in something and see how it works out.

First, the array creation ...

Code: Select all

 
    $result = MySQL_Query($query);  
    if(!$result) print "Query Failed";
 
   if(mysql_num_rows($result) > 0) {
      while ($row = mysql_fetch_assoc($result)) {
         if(isset($X[$row['Tag']])) $X[$row['Tag']]++; else $X[$row['Tag']] = 1; 
   } else $X =  array();    
 
That would be done for each $X and $Y.

Now, that should give you an array of tags, along with how many instances were found in each table. So, you might be able to block out rows by checking the number of instances in $X as compared to $Y. As I said though ... I'm fuzzy on this so I cant really code anything up.

Then, as odd as this seems to me, you should be able to do something like ...

Code: Select all

 
$X_unique = array_diff_key($X, $Y);
$Y_unique = array_diff_key($Y, $X);
$XY = array_intersect_key($X, $Y);
 
That *should* give three lists. A list of items unique to X, items unique to Y, and items found in both X and Y. This should also leave your original $X and $Y intact, so as you iterate the lists, you should be able to call the number found in each list with ...

Code: Select all

foreach($X_unique as $tag) echo $X[$tag];
But yeah ... I don't really know if this is really what you're after. It seems like it is, but your working code snippet seems to be trying to present a unified list, as opposed to the three distinct lists as discussed previously. Or maybe it is ... I'm confused. :lol:

Anyhow .. take a look. Does this produce anything like what you wanted?

Cheers
myfriend
Forum Newbie
Posts: 2
Joined: Fri Sep 12, 2008 1:27 am

Re: PHP Array search

Post by myfriend »

You should be able to work up a query to populate the 'done' status to bring it all up to date, and then just slightly alter your logic to use this single table approach.
Post Reply