Determining if records DON'T exist in table

[showman13]

For some reason, every time I try to submit a question I get the error message that the code snippet in my post is formatted incorrectly..

I don't even have a code snippet.

is it just me or is it something that happens to others?

[showman13]

OK, let me set up the scenario...

I have a position table that contains  1 record for each position in a matrix.

the primary key is the 'pos_id' which I generate based on what the referring position number is. 
    -3 available positions under each pos-
-this has turned out to be a terrible idea, but it is what it is and I know I need to change it soon-

The purpose of this was to create one large matrix that contains many small personal matrices within it. That way the members can fill their smaller personal matrices, and the company can fill the holes that can be left periodically...

Anyway...  that isn't what I need to ask about (not yet anyway)

every small matrix has the top position and then 3 positions under it and then the 3 positions under each of them, which makes up the pay line of 9 positions.

Determining when the top position cycled was easy when the pay line filled from left to right, 
but it doesn't necessarilly happen that way with what I've designed.  
So the 9th payline position could be filled before any of the 1st through the 6th...

What I need is a way to determine if any of the 9 positions don't exist yet, by virtue of there not being a record in the position table with that pos_id...

I can determine the pos_id of each of the 9 positions using a function I wrote that give me the next 3 under any position,  but what I need to know is the most efficient way to determine if any of those 9 are non-existant at that point.

If I determine the 9 pos_ids and have them in an array, I'm thinking that a query could be written that would look for any of those pos_ids in the array that can't be found.

Two questions here...  
One - how would that query need to be structured? 
Two - whether there would be a more efficient means of doing this?

So, if I have a pay position of 100000002,
  the array would be 

array  100000014, 100000015, 100000016, 100000017, 100000018, 100000019, 100000020, 100000021, 100000022

and if a query finds that one of those pos_ids doesn't exist in the table, then there wouldn't be a cycle, but if they are all there, then a cycle occurs...

This has turned out to be very lengthy, 
but I hope that someone will have taken the time to read it and can give me a little direction.

thanks in advance for your creative responses...

Douglas

[simplypixie]

I am very confused by your post and am thinking that what I am about to reply is incorrect as seems too simple, but here we go anyway just in case:

SELECT * FROM table_name WHERE pos_id NOT IN (SELECT pos_id FROM position_table)

[showman13]

This is basically what I've come up with... and seems to work, but I wonder if there isn't a much more efficient means of accomplishing the same thing.
I have a functioin called three_under that calculates numerically the 3 positions directly under any given pos_id...

so I start with the top position, put it in the array as element 0.
Then call the function which puts the 3 pos_ids into elements 1, 2, and 3
then using the values in 1, 2, and 3 call the function again for each of them, loading the resulting pos_ids in elements 4 through 12.
Like this

    $count=4;
// use $upline_pos_id with the Next Three function to get array of position IDs
// fill array then traverse until finding a number that doesn't exist
// this will be the new position number
        $mat_pos[]=$upline_pos_id;
        for ($pos_tk=0; $pos_tk<$count; $pos_tk++) {
            $three_under=NextThreeFunc($mat_pos[$pos_tk]);
            $mat_pos[]=$three_under[0];
            $mat_pos[]=$three_under[1];
            $mat_pos[]=$three_under[2];
        }

So now I have the array with 13 pos_ids in it the first being the top position, so I need to traverse the array of pos_ids starting with element 1 to see which of them doesn't exist in the table, and that would be the pos_id to be placed in...

//$mat_pos now has 12 elements holding all potential pos_id numbers
// need to determine which pos_id doesn't have a record in it yet...
// do a query of each rec with the pos_ids in the array
// first one with no results returned is the next available position
        $pos_tk=1;
        while ($pos_tk<=($count*3)){// iterate through all array elements
            $pos_ck="SELECT * from ".$tbl_name." where pos_id = '$mat_pos[$pos_tk]'";
            $result_pos_ck=mysql_query($pos_ck);
            if(mysql_num_rows($result_pos_ck)==0){
                // this is the first available position
                $new_pos_id=$mat_pos[$pos_tk]; // New Position ID assigned
                 break;
            }
            ++$pos_tk;
        }

So now new_pos_id contains the new pos_id
and I do an INSERT into that position table with all the pertinent information required.

Seems to me that there would be an easier / most efficient way to do this...

Any suggestions?

[showman13]

I can't believe that with all the talent there is in this room, that nobody has any suggestions or at least comments on the code that I included, whether it makes sens or not, or if there is a better way to accomplish the task.

[diafol]

I looked at your post and couldn't make head nor tail of it. That's why I didn't reply. If I can't understand it, I can't advise as to whether there's a better way to do it. The fact that it's confusing the hell out of me, shouldn't be taken that the post is nonsense - I'm not the sharpest tool in the box!

However, structures should be relatively simple IMO, and this looks pretty convoluted, so I assume from that, that it could be represented / modelled in a more efficient manner.

[jkon]

showman13 you asked two questions
• Two - whether there would be a more efficient means of doing this?
And you answered it your self
“-this has turned out to be a terrible idea, but it is what it is and I know I need to change it soon-”

Well I must admit that I will agree with you that this data model is a terrible idea. And as you are developing on it the more you will buried in logical problems. If you are going to keep it for a while then it really worth’s to develop it from the scratch again.

The first question
• One - how would that query need to be structured?
Well simplypixie gave you an answer. In fact NOT IN has some performance issues but if that solves your issue then we could share ideas about performance improvement. Please give an answer if that is at least a possible solution.

Diofol sayings “I am not the sharpest toll in the box” is accurate also for me … but take a moment to explain if simplepixie’s answer is working for you (and if not why) and I believe that we could help you create a temporary fix to that. (But should be a temporary fix … you should restructure your model)

[showman13]

I have to keep it at the moment and keep it functioning because it is on a live site. Once I am sure it is all running smooth, I will take the time to restructure the numbering system so it doesn't exceed the limitations of the BigInt...

I can't see how the suggestion from SimplyPixie will do anything like what I need to do, but will take another look... I guess for now I will just use what I have and not try to make it better.

thanks for your responses.

Douglas

[simplypixie]

showman13 - In your original post you wrote:

What I need is a way to determine if any of the 9 positions don't exist yet, by virtue of there not being a record in the position table with that pos_id...
And what I posted would do that in the simplest way.

[showman13]

OK, I'm sorry but I must be missing something in your response simplypixie...

Here is what you posted:

I am very confused by your post and am thinking that what I am about to reply is incorrect as seems too simple, but here we go anyway just in case:

    SELECT * FROM table_name WHERE pos_id NOT IN (SELECT pos_id FROM position_table)

And here is what I tried, which is what I understood it to say

SELECT * FROM table_name WHERE pos_id NOT IN (SELECT pos_id FROM table_name WHERE pos_id IN (100000014, 100000015, 100000016, 100000017, 100000018, 100000019, 100000020, 100000021, 100000022))

Because I know what the series of 9 pos_is numbers is that I want to check against.

But what I get back is 1279 records

So Clearly I am confused with how to utilize your suggested resolution.

[simplypixie]

I think we are both confused here. From how I read it you wanted to find all pos_id from one table (I don't know that table name as not provided so I have called it table1) that do not exist in the position_table, in which case your query would be (as I originally put)

SELECT * FROM table1 WHERE pos_id NOT IN (SELECT pos_id FROM position_table)

However, what you have written in the new query is something else and if you already have the numbers you want to check against, you can simply do

SELECT * FROM table1 WHERE pos_id NOT IN (100000014, 100000015, 100000016, 100000017, 100000018, 100000019, 100000020, 100000021, 100000022)

[showman13]

OK, I probably didn't explain myself very well initially... I apologize...

What I wanted basically was to find out how many pos_id numbers in a given series of 9 (payline from any matrix) were filled (had records in the position table).

The problem with doing the select of the pos_id that is NOT IN the listed 9 numbers is that it brings up the rest of the table, which is thousands of records

This is the solution that I came up with...
I start by knowing the pos_id that was just Inserted...

$pos_id=100016567; // just a test number

function CkPos($pos_id) {

    $to_cycle = 0; // if returned as 0 then no cycle occured 
    // numbering starts at 100 million and 1
    $pos_number=$pos_id-100000000;
     // Determine Receiving position that could potentially cycle
    $pay_pos=(round($pos_number/9)[phone]);

    // get pos_ids in the matrix (13 of them total starting with $pay_pos)
    $mat_pos[]=$pay_pos;
    for ($pos_tk=0; $pos_tk<4; $pos_tk++) {
        $get_three=NextThreeFunc($mat_pos[$pos_tk]);
        $mat_pos[]=$get_three[0];
        $mat_pos[]=$get_three[1];
        $mat_pos[]=$get_three[2];
    }

    // determine how many pos_id on payline (last 9) have records in table
    $sql="SELECT count(pos_id) FROM position_table WHERE ((pos_id >=".$mat_pos[4].") && (pos_id <=".$mat_pos[12]."))";
    $result=mysql_query($sql);
    $pos_ct=mysql_fetch_array($result);
    $number_positions=$pos_ct[0];

    if ($number_positions==9){// if all filled - cycle created
      $to_cycle =  $pay_pos;  
    }
return $to_cycle;// either $pay_pos or Zero
}

Hope that makes more sense...