Need Some Help Figuring Out Arrays

OldGrim · October 28, 2021

I am trying to help a friend with his football pool system. First an image and then the explanation:

The numbers table holds random numbers, 10 under NFC and 10 under AFC. The numbers are generated after all 100 squares are filled in the squares table and are applied to the squares table as shown in the middle image. The Matrix table is for the 100 squares holding the square number, buyer name and the numbers matrix that I am trying to build. As you can see the NFC numbers are applied to all 100 squares repeating every 10 rows. The AFC numbers are the crux of the matter here. For every 10 rows the AFC number is the same and then advances to the next AFC number. I've never tried to explain anything like this before and I hope it makes sense. I'm trying to figure out how to construct the arrays(array) needed to insert these numbers into the matrix table for later processing to determine the winners.

Barand · October 28, 2021

Something like this?

$nfc = range(0,9);
$afc = range(0,9);
shuffle($nfc);
shuffle($afc);

echo '<pre>Numbers table<br>';
echo "| NFC | AFC |<br>";
for ($i=0; $i<10; $i++)  {
    printf( '|  %d  |  %d  |<br>', $nfc[$i], $afc[$i]);
}

echo '<hr>Matrix table<br>';
for ($i=0; $i<100; $i++) {
    $a = $afc[intdiv($i, 10)];
    $n = $nfc[$i%10];
    printf('|  %02d  | %d, %d<br>', $i, $n, $a);
}

OldGrim · October 29, 2021

Ummm... not quite. You see once the users buy all 100 squares the code we already have in place generates the random numbers table. That is working fine. Then from that point forward the numbers table and the squares table are static as the numbers are fixed/added to the squares table. As the squares are bought my matrix table adds the users name to the square(s) bought. That is fine and works great. I need to add code (array/arrays) to add those numbers to the matrix table. Looking at the images it should be obvious that row one which is squares 00 thru 09 have an AFC number of 6 (repeated for all 10 squares) and NFC numbers from the numbers table. The second row which is squares 10 thru 19 would have an AFC number of 0 for those 10 squares and the NFC numbers would be just a repeat of the previous NFC numbers. Each block of 10 squares 00 thru 99 all have the same set of AFC numbers (from the numbers table) with the NFC number varying according to the numbers table. I was hoping my image was clear enough.

Barand · October 29, 2021

OK - hard-coding your $afc and $nfc arrays instead of randomly generating them

$afc = [6,0,9,4,2,3,8,5,7,1];
$nfc = [2,9,1,4,8,7,0,3,5,6];
//$nfc = range(0,9);
//$afc = range(0,9);
//shuffle($nfc);
//shuffle($afc);

echo '<pre>Numbers table<br>';
echo "| NFC | AFC |<br>";
for ($i=0; $i<10; $i++)  {
    printf( '|  %d  |  %d  |<br>', $nfc[$i], $afc[$i]);
}

echo '<hr>Matrix table<br>';
for ($i=0; $i<100; $i++) {
    $a = $afc[intdiv($i, 10)];
    $n = $nfc[$i%10];
    printf('|  %02d  | %d, %d<br>', $i, $n, $a);
}

gives this,

Numbers table
| NFC | AFC |
|  2  |  6  |
|  9  |  0  |
|  1  |  9  |
|  4  |  4  |
|  8  |  2  |
|  7  |  3  |
|  0  |  8  |
|  3  |  5  |
|  5  |  7  |
|  6  |  1  |

Matrix table
|  00  | 2, 6
|  01  | 9, 6
|  02  | 1, 6
|  03  | 4, 6
|  04  | 8, 6
|  05  | 7, 6
|  06  | 0, 6
|  07  | 3, 6
|  08  | 5, 6
|  09  | 6, 6
|  10  | 2, 0
|  11  | 9, 0
|  12  | 1, 0
|  13  | 4, 0
|  14  | 8, 0
|  15  | 7, 0
|  16  | 0, 0
|  17  | 3, 0
|  18  | 5, 0
|  19  | 6, 0
|  20  | 2, 9
|  21  | 9, 9
|  22  | 1, 9
|  23  | 4, 9
|  24  | 8, 9
|  25  | 7, 9
|  26  | 0, 9
|  27  | 3, 9
|  28  | 5, 9
|  29  | 6, 9
|  30  | 2, 4
|  31  | 9, 4
|  32  | 1, 4
|  33  | 4, 4
|  34  | 8, 4
|  35  | 7, 4
|  36  | 0, 4
|  37  | 3, 4
|  38  | 5, 4
|  39  | 6, 4
|  40  | 2, 2
|  41  | 9, 2
|  42  | 1, 2
|  43  | 4, 2
|  44  | 8, 2
|  45  | 7, 2
|  46  | 0, 2
|  47  | 3, 2
|  48  | 5, 2
|  49  | 6, 2
|  50  | 2, 3
|  51  | 9, 3
|  52  | 1, 3
|  53  | 4, 3
|  54  | 8, 3
|  55  | 7, 3
|  56  | 0, 3
|  57  | 3, 3
|  58  | 5, 3
|  59  | 6, 3
|  60  | 2, 8
|  61  | 9, 8
|  62  | 1, 8
|  63  | 4, 8
|  64  | 8, 8
|  65  | 7, 8
|  66  | 0, 8
|  67  | 3, 8
|  68  | 5, 8
|  69  | 6, 8
|  70  | 2, 5
|  71  | 9, 5
|  72  | 1, 5
|  73  | 4, 5
|  74  | 8, 5
|  75  | 7, 5
|  76  | 0, 5
|  77  | 3, 5
|  78  | 5, 5
|  79  | 6, 5
|  80  | 2, 7
|  81  | 9, 7
|  82  | 1, 7
|  83  | 4, 7
|  84  | 8, 7
|  85  | 7, 7
|  86  | 0, 7
|  87  | 3, 7
|  88  | 5, 7
|  89  | 6, 7
|  90  | 2, 1
|  91  | 9, 1
|  92  | 1, 1
|  93  | 4, 1
|  94  | 8, 1
|  95  | 7, 1
|  96  | 0, 1
|  97  | 3, 1
|  98  | 5, 1
|  99  | 6, 1

so which bit didn't I get? Did you even try running the code and looking at the output

Psycho · October 29, 2021

Based on the request, I see that Barand's code works as required. However, I would do one thing differently. The lines to determine the afc and nfc positions in the loop are perfectly valid, but the calculations are not intuitively apparent. While maybe not as efficient, I prefer code that is more obvious, such as

echo '<hr>Matrix table<br>';
for ($afcPos=0; $afcPos<10; $afcPos++) {
    for ($nfcPos=0; $nfcPos<10; $nfcPos++) {
    $coord = $afcPos . $nfcPos;
    $afcVal = $afc[$afcPos];
    $nfcVal = $nfc[$nfcPos];
    printf('|  %02d  | %d, %d<br>', $coord, $nfcVal, $afcVal);
    }
}

Although you state

Quote

I'm trying to figure out how to construct the arrays(array) needed to insert these numbers into the matrix table for later processing to determine the winners.

If this is going into a database, you should just need a table that defines each player and the coordinates of the position(s) they selected and a second table that defines the randomly generated numbers and their positions. You do not need to go back and update the player records with the number associated with their positions.

OldGrim · October 29, 2021

OK thanks for more input Psycho. I see the need here for further explanation and some code from the actual scripts. People can buy from 1 to 10 squares at a time. Here is the code that writes the info into the squares table and my added code to save the buyers name(s) into the squares table and my added squares_matrix table. This is not the entire script but the relevant parts.

//continue only if the square is available
if ($record['DATE'] == "0000-00-00 00:00:00") {


//check for required fields
for ($i=1;$i<=$sqTotal;$i++) {
if (($sqSelect[$i] >= 00 OR $sqSelect[$i] < 100) AND $name != '' AND $email != '') {

	$query="UPDATE VNSB21_squares SET NAME='".$name."', EMAIL='".$email."', NOTES='".$notes."', DATE='".$date."', CONFIRM='".$confirm."' WHERE SQUARE='".$sqSelect[$i]."' LIMIT 1";
	$result = mysqli_query($connection, $query);
	if (!$result) {
		echo mysqli_error();
		echo "<BR>Sorry, Technical problem occurred... your selection was not added.<br><br> Email this problem to <a href=\"mailto:".$ADMIN_EMAIL."\">".$ADMIN_EMAIL."</a>";
		exit;
	}
} else {
	echo "<p align='center'><br/><font color='#ff0000', size='3'>Required fields are missing!</font><br/><br/><a href='javascript:onClick=history.go(-1);'>Back</a></p>";
	exit;
}
}

// update my matrix table with buyers name(s)
for ($i=1;$i<=$sqTotal;$i++) {
if (($sqSelect[$i] >= 00 OR $sqSelect[$i] < 100) AND $name != '') {

	$query="UPDATE VNSB21_squares_matrix SET NAME='".$name."' WHERE SQUARE='".$sqSelect[$i]."' LIMIT 1";
	$result = mysqli_query($connection, $query);
	if (!$result) {
		echo mysqli_error();
		echo "<BR>Sorry, Technical problem occurred... your selection was not added.";
		exit;
	}
} else {
	echo "";
	exit;
}
}
// end update matrix table

This gets executed several times by the administrator until all the squares are filled. Only then can the numbers table be generated by the following code. At the end is my attempt to add the numbers to the matrix table. It actually writes numbers but not the correct ones so I know I'm not doing it right.

//show table for review
		echo "<table width=\"95%\" border=\"1\" cellspacing=\"1\" cellpadding=\"5\" style=\"font-family: Verdana,Ariel; font-size: 10px\">
		  <tr>
			<td style=\"border-top: none; border-left: none\">&nbsp;</td>";
		
			for ($i=1; $i<=10; $i++) {
				echo "<td align=\"center\">".$NFC_TEAM."&nbsp;<font size=\"3\" color=\"blue\"><strong>".$NFC[$i]."</strong></font></td>";
			}
		echo "
		  </tr>
		<tr>";
		
		$query="SELECT * FROM VNSB21_squares ORDER BY SQUARE";
		$result = mysqli_query($connection, $query);
		if (!$result) {
			echo mysqli_error($connection);
			exit;
		}
		$cnt_row = 0;
		$i=0;
		while ($record = mysqli_fetch_assoc($result)) {	
			if ($cnt_row==0) {
		$i++;
			echo "<td align='center'> $AFC_TEAM<br/><font size='3' color=\"red\"><strong>".$AFC[$i]."</strong></font></td>";
			}
			if ($record['NAME'] == "AVAILABLE") { 
				echo "<td width='10%' title='only $".$BET."'><a href=\"signup.php?square=".$record['SQUARE']."\">".stripslashes($record['NAME'])."<br/>".$record['SQUARE']."</a></td>";
			} else if ($record['NAME']!="AVAILABLE" && $record['CONFIRM']==1) {
				echo "<td width='10%' bgcolor='#99ff66' align='center' title=\"".$record['NOTES']."\"><strong>".stripslashes($record['NAME'])."</strong><br/>Confirmed</td>";
			} else {
				echo "<td width='10%' bgcolor='#ff9966' align='center' title=\"".$record['NOTES']."\"><strong>".stripslashes($record['NAME'])."</strong><br/>Pending</td>";
			}
			
		$cnt_row++;
			if ($cnt_row==10) {
				$cnt_row=0;
				echo "</tr><tr>";
			}
		}
		echo "</table>";	
	} else {
	?>

	<form action="" method="post">
	<input type="submit" name="randomnumber" value="Random" title="Auto select numbers randomly"></input>
	</form>

	<?php
	}

	// save to database
	if (isset($RANDOM)) {
		for ($n=1; $n<=10; $n++) {
			$query="INSERT INTO VNSB21_numbers (NFC, AFC) VALUES ('".$NFC[$n]."','".$AFC[$n]."')";
			$result = mysqli_query($connection, $query);
			if (!$result) {
				echo mysqli_error($connection);
				echo "<p>PROBLEM WRITING TO NUMBERS TABLE!</p>";
				exit;
			}
// My new squares_matrix table number entries
		for ($i=0; $i<100; $i++) {
    		$a = $AFC[intdiv($i, 10)];
    		$b = $NFC[$i%10];
				$query="UPDATE VNSB21_squares_matrix SET NFC='".$b."',AFC='".$a."'";
				$result = mysqli_query($connection, $query);
				if (!$result) {
					echo mysqli_error($connection);
					echo "<p>PROBLEM WRITING TO MATRIX TABLE!</p>";
				exit;
			}
		}
// end squares_matrix number entries

Edited October 29, 2021 by OldGrim
spell correction

OldGrim · October 30, 2021

OK I'm going to try one more time.

// save to database
	if (isset($RANDOM)) {
		for ($n=1; $n<=10; $n++) {
			$query="INSERT INTO VNSB21_numbers (NFC, AFC) VALUES ('".$NFC[$n]."','".$AFC[$n]."')";
			$result = mysqli_query($connection, $query);
			if (!$result) {
				echo mysqli_error($connection);
				echo "<p>PROBLEM WRITING TO NUMBERS TABLE!</p>";
				exit;
			}
	}
// My new squares_matrix table number entries
for ($i=0; $i<100; $i++) {
    $a = $AFC[intdiv($i, 10)];
    $n = $NFC[$i%10];

			$query="UPDATE VNSB21_squares_matrix SET NFC='".$n."',AFC='".$a."'";
			$result = mysqli_query($connection, $query);
			if (!$result) {
				echo mysqli_error($connection);
				echo "<p>PROBLEM WRITING TO MATRIX TABLE!</p>";
				exit;
				}
			}
		}
// end squares_matrix number entries

The first code sequence correctly generates the random numbers table. But the second code sequence writes 4, 0 into the NFC, AFC fields in all 100 records of the squares_matrix table. I don't understand how to code the for loop and further how to actually do the update table to get the desired results.

Barand · October 30, 2021

When you do an UPDATE query, the changes are applied to all records in the table unless you specify which record(s) is to be updated. I am guessing that the last pair of numbers in your two arrays are NFC 4, AFC 0

Your query needs a WHERE clause adding

$query = $connection->prepare("UPDATE VNSB21_squares_matrix 
                                  SET NFC = ?
                                    , AFC = ?
                               WHERE square = ?
                               ");
$query->bind_param('iii', $n, $a, $i);

for ($i=0; $i<100; $i++) {
    $a = $AFC[intdiv($i, 10)];
    $n = $NFC[$i%10];

    $query->execute();
}

OldGrim · October 30, 2021

Well it certainly is different now; but still not correct: This is the numbers table and then the squares_matrix table.

SQUARE	NAME	NFC	AFC 	
00 	ChuckY 	NULL 	NULL
01 	ChuckY 	3 	NULL
02 	ChuckY 	8 	NULL
03 	ChuckY 	5 	NULL
04 	ChuckY 	7 	NULL
05 	ChuckY 	6 	NULL
06 	Roger S 	4 	NULL
07 	FredB 	9 	NULL
08 	FredB 	1 	NULL
09 	FredB 	2 	NULL
10 	ChuckY 	NULL 	3
11 	Jimmy G 	3 	3
12 	Jimmy G 	8 	3
13 	Jimmy G 	5 	3
14 	Jimmy G 	7 	3
15 	Jimmy G 	6 	3
16 	FredB 	4 	3
17 	Roger S 	9 	3
18 	Roger S 	1 	3
19 	FredB 	2 	3
20 	ChuckY 	NULL 	6
21 	JamesB 	3 	6
22 	JamesB 	8 	6
23 	JamesB 	5 	6
24 	JamesB 	7 	6
25 	Jimmy G 	6 	6
26 	Roger S 	4 	6
27 	FredB 	9 	6
28 	FredB 	1 	6
29 	Roger S 	2 	6
30 	ChuckY 	NULL 	5
31 	Jimmy G 	3 	5
32 	Jimmy G 	8 	5
33 	Jimmy G 	5 	5
34 	Jimmy G 	7 	5
35 	FredB 	6 	5
36 	PepperJ 	4 	5
37 	PepperJ 	9 	5
38 	PepperJ 	1 	5
39 	PepperJ 	2 	5
40 	ChuckY 	NULL 	9
41 	JamesB 	3 	9
42 	JamesB 	8 	9
43 	JamesB 	5 	9
44 	JamesB 	7 	9
45 	FredB 	6 	9
46 	AliceK 	4 	9
47 	PepperJ 	9 	9
48 	PepperJ 	1 	9
49 	PepperJ 	2 	9
50 	JackB 	NULL 	7
51 	JackB 	3 	7
52 	SuzieQ 	8 	7
53 	SuzieQ 	5 	7
54 	Roger S 	7 	7
55 	FredB 	6 	7
56 	AliceK 	4 	7
57 	PepperJ 	9 	7
58 	PepperJ 	1 	7
59 	PepperJ 	2 	7
60 	JackB 	NULL 	8
61 	JackB 	3 	8
62 	AliceK 	8 	8
63 	AliceK 	5 	8
64 	Roger S 	7 	8
65 	JamesB 	6 	8
66 	JamesB 	4 	8
67 	SuzieQ 	9 	8
68 	SuzieQ 	1 	8
69 	SuzieQ 	2 	8
70 	JackB 	NULL 	4
71 	JackB 	3 	4
72 	AliceK 	8 	4
73 	AliceK 	5 	4
74 	Roger S 	7 	4
75 	SuzieQ 	6 	4
76 	SuzieQ 	4 	4
77 	SuzieQ 	9 	4
78 	SuzieQ 	1 	4
79 	SuzieQ 	2 	4
80 	JackB 	NULL 	2
81 	JackB 	3 	2
82 	AliceK 	8 	2
83 	AliceK 	5 	2
84 	Roger S 	7 	2
85 	Sean P 	6 	2
86 	Sean P 	4 	2
87 	Sean P 	9 	2
88 	Sean P 	1 	2
89 	Sean P 	2 	2
90 	JackB 	NULL 	0
91 	JackB 	3 	0
92 	AliceK 	8 	0
93 	AliceK 	5 	0
94 	Roger S 	7 	0
95 	Sean P 	6 	0
96 	Sean P 	4 	0
97 	Sean P 	9 	0
98 	Sean P 	1 	0
99 	Sean P 	2 	0

Copied directly from phpMyAdmin.

This is what it should be. Only the first 20 squares:

SQUARE	NAME	NFC	AFC 	
00 	ChuckY 	3 	3
01 	ChuckY 	8 	3
02 	ChuckY 	5 	3
03 	ChuckY 	7 	3
04 	ChuckY 	6 	3
05 	ChuckY 	4 	3
06 	Roger S 	9 	3
07 	FredB 	                1 	3
08 	FredB 	                2 	3
09 	FredB 	                0 	3
10 	ChuckY 	3 	6
11 	Jimmy G 	8 	6
12 	Jimmy G 	5 	6
13 	Jimmy G 	7 	6
14 	Jimmy G 	6 	6
15 	Jimmy G 	4 	6
16 	FredB 	                9 	6
17 	Roger S 	1 	6
18 	Roger S 	2 	6
19 	FredB 	                0 	6

Edited October 30, 2021 by OldGrim

OldGrim · October 30, 2021

Here is the url if you care to see the live system:

https://www.shade-treecoder.net/sb2/index.php

Barand · October 30, 2021

I don't know what your matrix table looks like but when I create one

CREATE TABLE `squares_matrix` (
  `square` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `nfc` int(11) DEFAULT NULL,
  `afc` int(11) DEFAULT NULL,
  PRIMARY KEY (`square`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

... generate 100 rows, then run that same code I get

mysql> select * from squares_matrix;
+--------+------+------+------+       +--------+------+------+------+       +--------+------+------+------+
| square | name | nfc  | afc  |       | square | name | nfc  | afc  |       | square | name | nfc  | afc  |
+--------+------+------+------+       +--------+------+------+------+       +--------+------+------+------+
|      0 | A    |    3 |    3 |       |     35 | AJ   |    4 |    9 |       |     69 | BR   |    0 |    4 |
|      1 | B    |    8 |    3 |       |     36 | AK   |    9 |    9 |       |     70 | BS   |    3 |    2 |
|      2 | C    |    5 |    3 |       |     37 | AL   |    1 |    9 |       |     71 | BT   |    8 |    2 |
|      3 | D    |    7 |    3 |       |     38 | AM   |    2 |    9 |       |     72 | BU   |    5 |    2 |
|      4 | E    |    6 |    3 |       |     39 | AN   |    0 |    9 |       |     73 | BV   |    7 |    2 |
|      5 | F    |    4 |    3 |       |     40 | AO   |    3 |    7 |       |     74 | BW   |    6 |    2 |
|      6 | G    |    9 |    3 |       |     41 | AP   |    8 |    7 |       |     75 | BX   |    4 |    2 |
|      7 | H    |    1 |    3 |       |     42 | AQ   |    5 |    7 |       |     76 | BY   |    9 |    2 |
|      8 | I    |    2 |    3 |       |     43 | AR   |    7 |    7 |       |     77 | BZ   |    1 |    2 |
|      9 | J    |    0 |    3 |       |     44 | AS   |    6 |    7 |       |     78 | CA   |    2 |    2 |
|     10 | K    |    3 |    6 |       |     45 | AT   |    4 |    7 |       |     79 | CB   |    0 |    2 |
|     11 | L    |    8 |    6 |       |     46 | AU   |    9 |    7 |       |     80 | CC   |    3 |    0 |
|     12 | M    |    5 |    6 |       |     47 | AV   |    1 |    7 |       |     81 | CD   |    8 |    0 |
|     13 | N    |    7 |    6 |       |     48 | AW   |    2 |    7 |       |     82 | CE   |    5 |    0 |
|     14 | O    |    6 |    6 |       |     49 | AX   |    0 |    7 |       |     83 | CF   |    7 |    0 |
|     15 | P    |    4 |    6 |       |     50 | AY   |    3 |    8 |       |     84 | CG   |    6 |    0 |
|     16 | Q    |    9 |    6 |       |     51 | AZ   |    8 |    8 |       |     85 | CH   |    4 |    0 |
|     17 | R    |    1 |    6 |       |     52 | BA   |    5 |    8 |       |     86 | CI   |    9 |    0 |
|     18 | S    |    2 |    6 |       |     53 | BB   |    7 |    8 |       |     87 | CJ   |    1 |    0 |
|     19 | T    |    0 |    6 |       |     54 | BC   |    6 |    8 |       |     88 | CK   |    2 |    0 |
|     20 | U    |    3 |    5 |       |     55 | BD   |    4 |    8 |       |     89 | CL   |    0 |    0 |
|     21 | V    |    8 |    5 |       |     56 | BE   |    9 |    8 |       |     90 | CM   |    3 |    1 |
|     22 | W    |    5 |    5 |       |     57 | BF   |    1 |    8 |       |     91 | CN   |    8 |    1 |
|     23 | X    |    7 |    5 |       |     58 | BG   |    2 |    8 |       |     92 | CO   |    5 |    1 |
|     24 | Y    |    6 |    5 |       |     59 | BH   |    0 |    8 |       |     93 | CP   |    7 |    1 |
|     25 | Z    |    4 |    5 |       |     60 | BI   |    3 |    4 |       |     94 | CQ   |    6 |    1 |
|     26 | AA   |    9 |    5 |       |     61 | BJ   |    8 |    4 |       |     95 | CR   |    4 |    1 |
|     27 | AB   |    1 |    5 |       |     62 | BK   |    5 |    4 |       |     96 | CS   |    9 |    1 |
|     28 | AC   |    2 |    5 |       |     63 | BL   |    7 |    4 |       |     97 | CT   |    1 |    1 |
|     29 | AD   |    0 |    5 |       |     64 | BM   |    6 |    4 |       |     98 | CU   |    2 |    1 |
|     30 | AE   |    3 |    9 |       |     65 | BN   |    4 |    4 |       |     99 | CV   |    0 |    1 |
|     31 | AF   |    8 |    9 |       |     66 | BO   |    9 |    4 |       +--------+------+------+------+
|     32 | AG   |    5 |    9 |       |     67 | BP   |    1 |    4 |
|     33 | AH   |    7 |    9 |       |     68 | BQ   |    2 |    4 |
|     34 | AI   |    6 |    9 |       +--------+------+------+------+
+--------+------+------+------+

OldGrim · October 31, 2021

This is my table creation and the first 20 rows of data. Too long to show all 100 rows.

CREATE TABLE `VNSB21_squares_matrix` (
  `SQUARE` varchar(2) NOT NULL DEFAULT '',
  `NAME` varchar(25) NOT NULL DEFAULT 'AVAILABLE',
  `NFC` tinyint(1) UNSIGNED DEFAULT '0',
  `AFC` tinyint(1) UNSIGNED DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='SB Squares Matrix';

--
-- Dumping data for table `VNSB21_squares_matrix`
--

INSERT INTO `VNSB21_squares_matrix` (`SQUARE`, `NAME`, `NFC`, `AFC`) VALUES
('00', 'ChuckY', 0, 0),
('01', 'ChuckY', 0, 0),
('02', 'ChuckY', 0, 0),
('03', 'ChuckY', 0, 0),
('04', 'ChuckY', 0, 0),
('05', 'ChuckY', 0, 0),
('06', 'Roger S', 0, 0),
('07', 'FredB', 0, 0),
('08', 'FredB', 0, 0),
('09', 'FredB', 0, 0),
('10', 'ChuckY', 0, 0),
('11', 'Jimmy G', 0, 0),
('12', 'Jimmy G', 0, 0),
('13', 'Jimmy G', 0, 0),
('14', 'Jimmy G', 0, 0),
('15', 'Jimmy G', 0, 0),
('16', 'FredB', 0, 0),
('17', 'Roger S', 0, 0),
('18', 'Roger S', 0, 0),
('19', 'FredB', 0, 0),
etc etc etc to 99

Barand · October 31, 2021

I used your "create table" this time then ran exactly the same code again

$AFC = [3,6,5,9,7,8,4,2,0,1];
$NFC = [3,8,5,7,6,4,9,1,2,0];

$query = $db->prepare("UPDATE VNSB21_squares_matrix 
                          SET nfc = ?
                            , afc = ?
                       WHERE square = ?
                       ");
$query->bind_param('iii', $n, $a, $i);

for ($i=0; $i<100; $i++) {
    $a = $AFC[intdiv($i, 10)];
    $n = $NFC[$i%10];

    $query->execute();
}

mysql> select * from vnsb21_squares_matrix;
+--------+------+------+------+          +--------+------+------+------+           +--------+------+------+------+
| SQUARE | NAME | NFC  | AFC  |          | SQUARE | NAME | NFC  | AFC  |           | SQUARE | NAME | NFC  | AFC  |
+--------+------+------+------+          +--------+------+------+------+           +--------+------+------+------+
| 00     | A    |    3 |    3 |          | 34     | AI   |    6 |    9 |           | 68     | BQ   |    2 |    4 |
| 01     | B    |    8 |    3 |          | 35     | AJ   |    4 |    9 |           | 69     | BR   |    0 |    4 |
| 02     | C    |    5 |    3 |          | 36     | AK   |    9 |    9 |           | 70     | BS   |    3 |    2 |
| 03     | D    |    7 |    3 |          | 37     | AL   |    1 |    9 |           | 71     | BT   |    8 |    2 |
| 04     | E    |    6 |    3 |          | 38     | AM   |    2 |    9 |           | 72     | BU   |    5 |    2 |
| 05     | F    |    4 |    3 |          | 39     | AN   |    0 |    9 |           | 73     | BV   |    7 |    2 |
| 06     | G    |    9 |    3 |          | 40     | AO   |    3 |    7 |           | 74     | BW   |    6 |    2 |
| 07     | H    |    1 |    3 |          | 41     | AP   |    8 |    7 |           | 75     | BX   |    4 |    2 |
| 08     | I    |    2 |    3 |          | 42     | AQ   |    5 |    7 |           | 76     | BY   |    9 |    2 |
| 09     | J    |    0 |    3 |          | 43     | AR   |    7 |    7 |           | 77     | BZ   |    1 |    2 |
| 10     | K    |    3 |    6 |          | 44     | AS   |    6 |    7 |           | 78     | CA   |    2 |    2 |
| 11     | L    |    8 |    6 |          | 45     | AT   |    4 |    7 |           | 79     | CB   |    0 |    2 |
| 12     | M    |    5 |    6 |          | 46     | AU   |    9 |    7 |           | 80     | CC   |    3 |    0 |
| 13     | N    |    7 |    6 |          | 47     | AV   |    1 |    7 |           | 81     | CD   |    8 |    0 |
| 14     | O    |    6 |    6 |          | 48     | AW   |    2 |    7 |           | 82     | CE   |    5 |    0 |
| 15     | P    |    4 |    6 |          | 49     | AX   |    0 |    7 |           | 83     | CF   |    7 |    0 |
| 16     | Q    |    9 |    6 |          | 50     | AY   |    3 |    8 |           | 84     | CG   |    6 |    0 |
| 17     | R    |    1 |    6 |          | 51     | AZ   |    8 |    8 |           | 85     | CH   |    4 |    0 |
| 18     | S    |    2 |    6 |          | 52     | BA   |    5 |    8 |           | 86     | CI   |    9 |    0 |
| 19     | T    |    0 |    6 |          | 53     | BB   |    7 |    8 |           | 87     | CJ   |    1 |    0 |
| 20     | U    |    3 |    5 |          | 54     | BC   |    6 |    8 |           | 88     | CK   |    2 |    0 |
| 21     | V    |    8 |    5 |          | 55     | BD   |    4 |    8 |           | 89     | CL   |    0 |    0 |
| 22     | W    |    5 |    5 |          | 56     | BE   |    9 |    8 |           | 90     | CM   |    3 |    1 |
| 23     | X    |    7 |    5 |          | 57     | BF   |    1 |    8 |           | 91     | CN   |    8 |    1 |
| 24     | Y    |    6 |    5 |          | 58     | BG   |    2 |    8 |           | 92     | CO   |    5 |    1 |
| 25     | Z    |    4 |    5 |          | 59     | BH   |    0 |    8 |           | 93     | CP   |    7 |    1 |
| 26     | AA   |    9 |    5 |          | 60     | BI   |    3 |    4 |           | 94     | CQ   |    6 |    1 |
| 27     | AB   |    1 |    5 |          | 61     | BJ   |    8 |    4 |           | 95     | CR   |    4 |    1 |
| 28     | AC   |    2 |    5 |          | 62     | BK   |    5 |    4 |           | 96     | CS   |    9 |    1 |
| 29     | AD   |    0 |    5 |          | 63     | BL   |    7 |    4 |           | 97     | CT   |    1 |    1 |
| 30     | AE   |    3 |    9 |          | 64     | BM   |    6 |    4 |           | 98     | CU   |    2 |    1 |
| 31     | AF   |    8 |    9 |          | 65     | BN   |    4 |    4 |           | 99     | CV   |    0 |    1 |
| 32     | AG   |    5 |    9 |          | 66     | BO   |    9 |    4 |           +--------+------+------+------+
| 33     | AH   |    7 |    9 |          | 67     | BP   |    1 |    4 |
+--------+------+------+------+          +--------+------+------+------+

and I still couldn't get your messed up results.

However, just in case your local set up isn't as tolerant as mine when it comes to matching "00" with 0, then try

$AFC = [3,6,5,9,7,8,4,2,0,1];
$NFC = [3,8,5,7,6,4,9,1,2,0];

$query = $db->prepare("UPDATE VNSB21_squares_matrix 
                          SET nfc = ?
                            , afc = ?
                       WHERE square = ?
                       ");
$query->bind_param('iis', $n, $a, $s);

for ($i=0; $i<100; $i++) {
    $a = $AFC[intdiv($i, 10)];
    $n = $NFC[$i%10];
    $s = sprintf('%02d', $i);
    
    $query->execute();
}

PS - What is in your $AFC and $NFC arrays?

Edited October 31, 2021 by Barand

Barand · October 31, 2021

It looks like your $AFC/$NFC array keys are not 0-based, as they need to be, so $AFC[0] and $NFC[0] do not exist. (It should be throwing out messages to that effect - are you reporting your errors?)

Try

$AFC = array_values($AFC);
$NFC = array_values($NFC);

just before the loop to update the matrix table.

OldGrim · October 31, 2021

OK I'll try it Barand. But I'm just about ready to give it up for now. I even went so far as to convert my tables to use InnoDB engine and added indexes but everything went South on me in a hurry. I have another db set up with all original files so I'll experiment with that one and try your suggestion. Thanks for all your time and help Barand I really appreciate it.

OldGrim · October 31, 2021

Well HALLELUJAH !!!!!!!!! That works perfectly Barand. Thank you so much; I am very grateful. Now I can continue to write the other script to determine the winners. Of course all of this won't take place live until the actual Super Bowl.

Sign In

Need Some Help Figuring Out Arrays

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information