[SOLVED] Attaching variables to a database positioning calculation :S

[Mutley]

Sorry about the title, can't explain it in 1 sentence, so please read, hehe...

 

 

I have 16 database fields:

 

p1_id | p2_id | p3_id | p4_id | p5_id | p6_id | p7_id | p8_id

pts_1 | pts_2 | pts_3 | pts_4 | pts_5 | pts_6 | pts_7 | pts_8

 

 

How it works is, the *_id fields contain the users ID number. The pts_* field contains a numeric score, the number after pts_ corrisponds to the user field (so the number in pts_5 belongs to p5_id).

 

What I wish to do is determine 1st, 2nd, 3rd.... 8th positions, depending on the highest number (highest number = 1st, lowest = 8th) then give them a variable, which I need for later on in my script to send messages to each user telling them where they have come etc.

 

As an example, this is how I've done it for just 2 users:

 

<?php
	if($p1_pts > $p2_pts) {
	$1st = $p1_id;
	$2nd = $p2_id;
	} else {
	$1nd = $p2_id;
	$2nd = $p1_id;
	}
?>

 

I need to do it for 2 users, 4, 6 and 8 users seperatly, the above is okay for 2 users but my mind is boggled for any more users!

 

I hope that makes sense, lol! Very thankful for any help.

[per1os]

That database design needs some serious work.

 

Why not have a separate table with a date or a round id associated with it.

 

IE:

 

create table round (
   roundid int(11) NOT NULL auto_increment,
   roundtitle varchar(50) NOT NULL,
   Primary Key (roundid)
);

create table user_rounds (
   user_round_id INT(11) NOT NULL auto_increment,
   roundid INT(11) NOT NULL,
   userid INT(11) NOT NULL,
   Priamry Key (user_round_id)
);

create table round_results (
   round_results_id INT(11) NOT NULL auto_increment,
   place tinyint(1) NOT NULL,
   primary key(round_results_id)
);

create table user_round_results (
   user_round_results_id INT(11) NOT NULL auto_increment,
   user_round_id INT(11) NOT NULL,
   round_results INT(11) NOT NULL,
   Primary Key (user_round_results_id)
);

 

That would be the ideal way to do it =) Normalization is wonderful. Anyhow, I wouldn't want to do it in separate columns inefficient, just thought I would throw this thought out there.

 

<?php
/*
Takes in an array by reference and re-orders the array to be sorted by points.
*/
function re_order(&$array) {
$rows = array();
$rows = $array;
$array = array();
$i=1;
$sort_ar = array();
foreach ($rows as $old_key => $points) {

	foreach ($points as $key => $val) {
		if ($key == 'p' . $i . '_pts') {
			$sort_ar[$old_key] = $val;
		}
	}
	$i++;
}

arsort($sort_ar); // should sort by points
$i=0;
foreach ($sort_ar as $oldkey => $sorted) {
	$array[$i++] = $rows[$oldkey];
}

return true;
}
$my_array = array(0 => array("p1_pts" => 10, "p1_id" => "test1"), 1 => array("p2_pts" => 16, "p2_id" => "test2"), 2 => array("p3_pts" => 15, "p3_id" => "test3"), 3 => array("p4_pts" => 5, "p4_id" => "test4"));
re_order($my_array);

echo '<pre>', print_r($my_array);
?>

 

There is one way to do it for any amount of users. The new array is ordered correctly just look through the array with www.php.net/foreach to get the first second etc.

 

Anyhow I would look into a database redesign.

[Mutley]

I was dreading to hear database re-design, lol! I just re-did loads of files with that database from an even worse one.

 

So is that code for my current database design, sorry?

[per1os]

I was dreading to hear database re-design, lol! I just re-did loads of files with that database from an even worse one.

 

So is that code for my current database design, sorry?

 

Yes it is.

[Mutley]

I get this when executed:

 

Array

(

    [0] => Array

        (

            [p2_pts] => 16

            [p2_id] => test2

        )

 

    [1] => Array

        (

            [p3_pts] => 15

            [p3_id] => test3

        )

 

    [2] => Array

        (

            [p1_pts] => 10

            [p1_id] => test1

        )

 

    [3] => Array

        (

            [p4_pts] => 5

            [p4_id] => test4

        )

 

)

1

 

My variables for testing:

 

$p1_id = '3';

$p2_id = '9';

$p3_id = '1';

$p4_id = '7';

$p5_id = '10';

$p6_id = '6';

$p7_id = '8';

$p8_id = '4';

$pts_1 = '12';

$pts_2 = '15';

$pts_3 = '16';

$pts_4 = '18.1';

$pts_5 = '18';

$pts_6 = '5';

$pts_7 = '24';

$pts_8 = '9';

[Mutley]

Thanks a lot for your help Frosty, I appreciate the time you spent producing that code. (I can't edit posts so just replying again ).

 

But the issue above is still existent.

[per1os]

What is the above issue? You never really stated a problem.

[Mutley]

Ah sorry, I wasn't looking at the code properly. I changed the array to how I want so it looks like this for 4 users:

 

$my_array = array(0 => array("p1_pts" => $pts_1, "p1_id" => "$p1_id"), 1 => array("p2_pts" => $pts_2, "p2_id" => "$p2_id"), 2 => array("p3_pts" => $pts_3, "p3_id" => "$p3_id"), 3 => array("p4_pts" => $pts_4, "p4_id" => "$p4_id"));
re_order($my_array);

 

(You can see I'm using the variables to fill in the data for user/points).

 

How would I do a foreach to then bind variables to the user id,

 

so $1st = arrayresult

$2nd = arrayresult

(In order how the array sorts)

[per1os]

Alright let's think about this. Why don't we house the position as an item in the array? Interesting...

 

$my_array = array(0 => array("pts" => 10, "user_id" => "test1", "pos" => 1), 1 => array("pts" => 16, "user_id" => "test2", "pos" => 2), 2 => array("pts" => 15, "user_id" => "test3", "pos" => 3), 3 => array("pts" => 5, "user_id" => "test4", "pos" => 4));

 

That way you know what position they were in, and their userid, with how many points they made.

 

With a re-order of the array we get them filtered by who won/lost right?

 

So with that logic, $my_array[0]['user_id']  will print to use the first positions user id.  $my_array[2]['pts']  would print to us third places points. $my_array[1]['pos'] will tell us what position 2nd place was in.

 

No need to assign them to variables, the array will do the trick without the hassle and for unlimited users.

[Mutley]

So, at the moment I have my Array looking like this...

 

$my_array = array(0 => array("p1_pts" => $pts_1, "p1_id" => "$p1_id", "pos" => 1), 1 => array("p2_pts" => $pts_2, "p2_id" => "$p2_id", "pos" => 2), 2 => array("p3_pts" => $pts_3, "p3_id" => "$p3_id", "pos" => 3), 3 => array("p4_pts" => $pts_4, "p4_id" => "$p4_id", "pos" => 4));
re_order($my_array);

 

Or is that wrong? Should the area saying "p1_id", "p2_id" etc, just be "user_id" and the same with the "p1_pts" should be "pts"?

 

I understand what you mean by the variables but I can't extract the data (just shows a "1" guessing the Array isn't right).

[Barand]

try

 

<?php
$sql = "SELECT p1_id , p2_id , p3_id , p4_id , p5_id , p6_id , p7_id , p8_id,
            pts_1 , pts_2 , pts_3 , pts_4 , pts_5 , pts_6 , pts_7 , pts_8
        FROM mytable";
$res = mysql_query($sql) or die (mysql_error()."<pre>$sql</pre>");
$row = mysql_fetch_row($res);

$id_array = array_slice ($row,0,;
$pts_array = array_slice ($row,8,;     

$results = array_combine ($id_array, $pts_array);

arsort ($results);
?>

[Barand]

Substituting your test values in $row above

 

<?php

$row = array (
    '3',
    '9',
    '1',
    '7',
    '10',
    '6',
    '8',
    '4',
    12,
    15,
    16,
    18.1,
    18,
    5,
    24,
    9
);

$id_array = array_slice ($row,0,;
$pts_array = array_slice ($row,8,;     

$results = array_combine ($id_array, $pts_array);

arsort ($results);

echo '<pre>', print_r($results, true), '</pre>';
?>

//gives -->

Array
(
    [8] => 24
    [7] => 18.1
    [10] => 18
    [1] => 16
    [9] => 15
    [3] => 12
    [4] => 9
    [6] => 5
)

[Mutley]

Thank-you Barand.

 

Now, to attach a variable (or extract the user_id depending on position) how do I do that?

 

I tried:

 

foreach ($results as $k => $v) {
    echo "[$k]<br />\n";
}

(won't show results if I remove square brackets [])

 

Which works but I get it like:

 

[8]

[7]

[10]

[1]

[9]

[3]

[4]

[6]

 

I need it to be like:

 

$1st = '8';

$2nd = '7';

$3rd = '10';

$4th = '1';

$5th = '9';

$6st = '3';

$7th = '4';

$8th = '6';

 

So, if I made another array to do the 1st/2nd/3rd etc, I can do something like:

 

    echo "$$place = $k<br />\n";

 

[Barand]

After the arsort($results line, put

 

<?php
$pos = 0;
foreach ($results as $id => $pts)
{
    echo ordSuffix(++$pos) . ' = ' . $id . '<br />';
}

function ordSuffix($n) {
    $str = "$n";
    $t = $n > 9 ? substr($str,-2,1) : 0;
    $u = substr($str,-1);
    if ($t==1) return $str . 'th';
    else switch ($u) {
        case 1: return $str . 'st';
        case 2: return $str . 'nd';
        case 3: return $str . 'rd';
        default: return $str . 'th';
    }
}

?>

 

gives-->[pre]

1st = 8

2nd = 7

3rd = 10

4th = 1

5th = 9

6th = 3

7th = 4

8th = 6

 

[/pre]

[Mutley]

If I add a "$" to that though, it lists them as text rather than creating them as variables.

 

I need to use the $1st, $2nd etc later on to message each user which position they came among other things.

 

Many thanks so far Barand!

[Barand]

You can't create variables whose name starts with a number.

 

Better to hold an array of positions for each id

 

<?php
arsort ($results);

$positions = array();

$pos = 0;
foreach ($results as $id => $pts)
{
    $position[$id] = ordSuffix(++$pos);
}


/**
* to email your clients
*/
foreach ($results as $id => $pts)
{
    $message = "Congratulations $id, you came {$position[$id]} with $pts points";
    echo $message, '<br/>';
}
?>

 

Of course, you'd need to convert ids to names

[sasa]

variable name can't start with number

name $1st is not regular

you can use $_1st or $position1st

[Mutley]

Still doesn't work sasa.

 

Thanks Barand but I really need them to be set as variables so I can use them for different things.

 

Here is my code:

 

<?php
$row = array (
    '3',
    '9',
    '1',
    '7',
    '10',
    '6',
    '8',
    '4',
    12,
    15,
    16,
    18.1,
    18,
    5,
    24,
    9
);


$id_array = array_slice ($row,0,;
$pts_array = array_slice ($row,8,;     

$results = array_combine ($id_array, $pts_array);

arsort ($results);

$pos = 0;
foreach ($results as $id => $pts)
{
    echo ' $_' . ordSuffix(++$pos) . ' = ' . $id . '<br />';
}

function ordSuffix($n) {
    $str = "$n";
    $t = $n > 9 ? substr($str,-2,1) : 0;
    $u = substr($str,-1);
    if ($t==1) return $str . 'th';
    else switch ($u) {
        case 1: return $str . 'st';
        case 2: return $str . 'nd';
        case 3: return $str . 'rd';
        default: return $str . 'th';
    }
}

echo '<pre>', print_r($results, true), '</pre>';

$test = $_1st + $_2nd;
echo $test;

?>

 

It displays:

 

$_1st = 8

$_2nd = 7

$_3rd = 10

$_4th = 1

$_5th = 9

$_6th = 3

$_7th = 4

$_8th = 6

 

Array

(

    [8] => 24

    [7] => 18.1

    [10] => 18

    [1] => 16

    [9] => 15

    [3] => 12

    [4] => 9

    [6] => 5

)

 

0

 

 

Using the variables like that sasa, still won't work but if they did that's exactly how I want it! Thanks a lot so far.

[sasa]

change

foreach ($results as $id => $pts)
{
    echo ' $_' . ordSuffix(++$pos) . ' = ' . $id . '<br />';
}

to

foreach ($results as $id => $pts)
{
    $name = ' $_' . ordSuffix(++$pos)
    echo  $name. ' = ' . $id . '<br />';
    $$name = $id; // or $$name = $pts;
}

[Mutley]

Displays:

 

$_1st = 8

$_2nd = 7

$_3rd = 10

$_4th = 1

$_5th = 9

$_6th = 3

$_7th = 4

$_8th = 6

1st:

 

Code snippet:

 

<?php

$pos = 0;
foreach ($results as $id => $pts)
{
    $name = ' $_' . ordSuffix(++$pos);
    echo  $name. ' = ' . $id . '<br />';
    $$name = $id; // or $$name = $pts;
}

function ordSuffix($n) {
    $str = "$n";
    $t = $n > 9 ? substr($str,-2,1) : 0;
    $u = substr($str,-1);
    if ($t==1) return $str . 'th';
    else switch ($u) {
        case 1: return $str . 'st';
        case 2: return $str . 'nd';
        case 3: return $str . 'rd';
        default: return $str . 'th';
    }
}

//echo '<pre>', print_r($results, true), '</pre>';

echo '1st:' . $_1st;

?>

 

Variables still broken.

[Barand]

 

Thanks Barand but I really need them to be set as variables so I can use them for different things.

 

 

 

What do you think the array is, if not variables?

[Mutley]

 

Thanks Barand but I really need them to be set as variables so I can use them for different things.

 

 

 

What do you think the array is, if not variables?

 

With the array though using FOREACH, I can only use it in those enclosed brackets can't I?

[Barand]

With variables you first have to construct the variable name (eg 'Position' . $id) before you can either store a value in it or reference it.

 

With an array all you need is

 

echo $position[$id];

[Mutley]

As an example, how would I get the users id for position 3rd?

 

Is that like doing $id['3rd'] sort of thing, with $position[$id] it's doing something like:- Find that position for this ID?

 

I've puzzled myself now on the whole array/variables. 

[per1os]

So with that logic, $my_array[0]['user_id']  will print to use the first positions user id.  $my_array[2]['pts']  would print to us third places points. $my_array[1]['pos'] will tell us what position 2nd place was in.

 

The array I provided you with the built code will be accessed like that 0 is not an ID, 0 is equal to first place. 1 is equal to second place, they are indexes in arrays. Understand ???

 

So if you want to find out who was in 2nd place, simply call $my_array[1]['user_id']  To get the second place points, it is $my_array[1]['pts']

 

It is very simple, no need to define variables man, arrays work just the same. Than using foreach logic you can print a table of what each place a person came in, or just manually write it out using the indexes.

 

As long as the data is formed as such

 

$my_array = array(0 => array("pts" => 10, "user_id" => "test1", "pos" => 1), 1 => array("pts" => 16, "user_id" => "test2", "pos" => 2), 2 => array("pts" => 15, "user_id" => "test3", "pos" => 3), 3 => array("pts" => 5, "user_id" => "test4", "pos" => 4));

 

There is no problem. Just use my re_order code with an array formed like that and you have your answers.