[SOLVED] Array + MySql

[Aeolus]

So here's what I'm trying to do.

 

1) Get all users from a table, where they have natural scores recorded.

2) Add random number on top of each natural score

3) Combine the natural scores into one final score

4) Order and rate (starting with 1) users based off the final score

5) Insert into an event table the rating, score, and username

 

Here's what I have so far...

 

<?php require_once("inc/config.php"); 
include("inc/header.php"); 

    $connection = mysql_connect($dbHost,$dbUser,$dbPass);
    if (!$connection) { die("Database connection failed: " . mysql_error());}
    $db_select = mysql_select_db($dbName,$connection);
        if (!$db_select) { die("Database selection failed: " . mysql_error()); }


    // requesting
    $result = @mysql_query("SELECT * FROM users");
    if (!$result) {
     exit('<p>Error performing query: ' . mysql_error() . '</p>');
    }

while ($row = mysql_fetch_array($result)) {

/* here is where I think I need to add the random score, but then it adds the same score to each user,
and I want each user to have their own randomized number */

//I have plugged 4 in for now, but would like the next three lines to read + $random instead of + 4

$score_d = $row ['nat_d'] + 4;
$score_j = $row ['nat_j'] + 4;
$score_c = $row ['nat_c'] + 4;
$score_final = $score_d + $score_j + $score_c;
    
$assocArray[$row['name']] = $score_final;

arsort($assocArray, SORT_NUMERIC);
$i = 1;
};

foreach ($assocArray as $key => $value) {
    // request
    $result = @mysql_query("SELECT * FROM users WHERE name='$key'");
    if (!$result) {
     exit('<p>Error performing query: ' . mysql_error() . '</p>');
    }
    
    while ($row = mysql_fetch_array($result)) {
        $score_d = $row ['nat_d'] + 4;
        $score_j = $row ['nat_j'] + 4;
        $score_c = $row ['nat_c'] + 4;

        echo $i . ' <b>' . $row ['name'] . '</b> | ';
        printf("%05.2f", $score_d); echo ' | ';
        printf("%05.2f", $score_c); echo ' | ';
        printf("%02.0f", $score_j); echo ' | ';
        echo $value . ' |<br><br>';

/*$sql = "insert into event (rank, score, user) VALUES ('$i', '$value', '$key')";
$query = mysql_query($sql);
*/

        $i++;
    };
};


    
include("inc/footer.php"); ?>

 

 

[Jessica]

Thanks for posting the code, but please tell us exactly where you are stuck, not just your goal. Not many people will want to read all the code and try to debug it without knowing why you are stuck.

 

What is the problem you have now?

[Aeolus]

Whoops sorry

 

So where I'm stuck is (I put it in the code) when I add the random numbers in before I execute the array for ranking, it assigns the same random number set to all of the users.

 

If I put it in inside the array, it messes up the score - since the final score is calculated based on several natural scores added with the random numbers.

 

Does that make any more sense?

 

There isn't necessarily a bug in the script, I'm not getting any errors or anything, but it's just not doing what I want it to :/

[roopurt18]

Here are some general comments on your code, I'll come back later with something else.

 

<?php 
require_once("inc/config.php"); 
include("inc/header.php"); 

//
// die() IS JUST ABOUT THE WORST WAY TO HANDLE ERRORS, ESPECIALLY WHEN
// COMBINED WITH mysql_error() OR OTHER MESSAGES LIKE YOU HAVE THERE.
// WHEN YOUR SCRIPT DIES, IT'S GOING TO DUMP INFORMATION TO THE BROWSER THAT
// WILL MAKE YOUR SCRIPT VULNERABLE TO ATTACKERS.
// INSTEAD, YOU SHOULD USE set_error_handler() TO REGISTER YOUR OWN ERROR
// FUNCTION.  WITHIN YOUR ERROR FUNCTION, LOG THE ERRORS TO A FILE SILENTLY AND
// KILL THE SCRIPT IF NECESSARY.  WHEN YOU NEED TO CAUSE AN ERROR, YOU CAN USE
// SOMETHING LIKE:
//
// if( !$connection ) { trigger_error( "Failed Connection: " . mysql_error() ); }
//
$connection = mysql_connect($dbHost,$dbUser,$dbPass);
if (!$connection) { die("Database connection failed: " . mysql_error()); }

$db_select = mysql_select_db($dbName,$connection);
if (!$db_select) { die("Database selection failed: " . mysql_error()); }


// requesting
$result = @mysql_query("SELECT * FROM users");
if (!$result) {
    //
    // THIS IS JUST AS BAD AS DIE()
    //
    exit('<p>Error performing query: ' . mysql_error() . '</p>');
}

//
// HERE YOU HAVE A LOOP THAT ITERATES ONCE FOR EACH USER, NOTHING WRONG WITH
// THAT.
while ($row = mysql_fetch_array($result)) {
    /* here is where I think I need to add the random score, but then it adds the same score to each user,
    and I want each user to have their own randomized number */
    //I have plugged 4 in for now, but would like the next three lines to read + $random instead of + 4
    $score_d = $row ['nat_d'] + 4;
    $score_j = $row ['nat_j'] + 4;
    $score_c = $row ['nat_c'] + 4;
    $score_final = $score_d + $score_j + $score_c;
    
    // YOU HAVE NOT DECLARED $assocArray. PHP IS SMART ENOUGH TO FIGURE OUT WHAT
    // YOU MEANT, BUT YOU SHOULD NOT RELY ON SUCH FUNCTIONALITY.
    $assocArray[$row['name']] = $score_final;
    
    // REMEMBER THAT WE ARE IN A LOOP AND EACH TIME THROUGH THE LOOP WE ARE
    // ADDING AN ITEM TO $assocArray.  IT IS A GREAT WASTE OF COMPUTING POWER
    // TO SORT SOMETHING WHEN WE KNOW IT IS AUTOMATICALLY GOING TO CHANGE THE
    // NEXT TIME WE COME THROUGH HERE.  THEREFORE THE arsort() CALL SHOULD COME
    // **AFTER** THE LOOP HAS FINISHED.
    arsort($assocArray, SORT_NUMERIC);
    
    //
    // THERE IS NO PURPSE TO SETTING THIS VARIABLE.  IT IS NOT USED WITHIN THIS
    // LOOP.  IT **IS** USED IN THE FOREACH() LOOP BELOW, IN WHICH CASE YOU SHOULD
    // MOVE THIS STATEMENT TO JUST BEFORE THE FOREACH() LOOP BEGINS.
    $i = 1;
};

// I CAN ONLY ASSUME HERE YOU ARE ATTEMPTING TO LOOP OVER THE RESULTS
// IN THE CORRECT ORDER TO INSERT THEM INTO THE FINAL TABLE.
// HOWEVER...
foreach ($assocArray as $key => $value) {
    // ARRRRGH!
    // YOU'VE ALREADY SELECTED ALL OF THE USERS ABOVE AND NOW YOU ARE GOING
    // TO SELECT EACH ONE AGAIN? ONE AT A TIME?  THERE'S NO NEED TO SLAM THE
    // DATABASE WITH THIS MANY QUERIES IF YOU'VE ALREADY SELECTED THEM ABOVE.
    $result = @mysql_query("SELECT * FROM users WHERE name='$key'");
    if (!$result) {
        exit('<p>Error performing query: ' . mysql_error() . '</p>');
    }
    
    
    while ($row = mysql_fetch_array($result)) {
        // ARRRRGH! x2
        // YOU'VE ALREADY CALCULATED THESE VALUES ABOVE -- WHY NOT JUST SAVE THEM
        // THE FIRST TIME YOU CALCULATE THEM?
        // ALSO, SINCE YOU WANT THESE TO BE REPLACED BY RANDOM NUMBERS, RECALCULATING
        // THEM HERE WOULD GIVE YOU DIFFERENT RESULTS SINCE RANDOM NUMBERS AREN'T
        // THE SAME TWICE IN A ROW (UNLESS YOU RESEED THE GENERATOR).
        $score_d = $row ['nat_d'] + 4;
        $score_j = $row ['nat_j'] + 4;
        $score_c = $row ['nat_c'] + 4;

        echo $i . ' <b>' . $row ['name'] . '</b> | ';
        printf("%05.2f", $score_d); echo ' | ';
        printf("%05.2f", $score_c); echo ' | ';
        printf("%02.0f", $score_j); echo ' | ';
        echo $value . ' |<br><br>';

/*$sql = "insert into event (rank, score, user) VALUES ('$i', '$value', '$key')";
$query = mysql_query($sql);
*/

        $i++;
    };
};


    
include("inc/footer.php"); ?>

[Aeolus]

I see where you're coming from, and I didn't plan on having the

 

       

$score_d = $row ['nat_d'] + 4;
        $score_j = $row ['nat_j'] + 4;
        $score_c = $row ['nat_c'] + 4;

 

in there twice once I figured out where it actually needed to be - I was just trying things back and forth trying to figure this out and left it in there again.

 

And how should I go about the second mysql query. Sorry, I'm an obvious beginner at php and I'm trying to learn bit by bit, so every piece of advice is appreciated.

 

[roopurt18]

If you have MySQL version 4.1 or higher, then this should get you there.

 

<?php
/**
* THE TEST TABLE I USED TO MAKE THIS
create table testjunkin (
  name varchar( 12 ),
  nat_d integer,
  nat_j integer,
  nat_c integer
);

insert into testjunkin ( name, nat_d, nat_j, nat_c, score_final ) values
  ( 'Fred', 8, 10, 12, null ),
  ( 'Bob', 9, 7, 11, null ),
  ( 'Alice', 11, 2, 4, null ); 
*/
require_once("inc/config.php"); 
include("inc/header.php"); 

$connection = mysql_connect($dbHost,$dbUser,$dbPass);
if (!$connection) { die("Database connection failed: " . mysql_error());}
$db_select = mysql_select_db($dbName,$connection);
if (!$db_select) { die("Database selection failed: " . mysql_error()); }

// SETS UP A RANDOM VALUE BETWEEN 0 AND 10
$natModifierMin = 0;
$natModifierMax = 10;

// SETS A MYSQL VARIABLE
$qCounter = "
SET @MYCOUNTER=0;
";

// SELECTS, APPLIES RANDOM, ORDERS, RANKS, AND INSERTS ALL IN ONE
$qInsert = "
INSERT INTO `event` ( `rank`, `score`, `user` )
SELECT
    *, @MYCOUNTER:= @MYCOUNTER + 1 AS `rank`
FROM (
    SELECT
        `name`,
        `nat_d` + FLOOR( {$natModifierMin} + RAND() * ({$natModifierMax} - {$natModifierMin}))
            + `nat_j` + FLOOR( {$natModifierMin} + RAND() * ({$natModifierMax} - {$natModifierMin}))
            + `nat_c` + FLOOR( {$natModifierMin} + RAND() * ({$natModifierMax} - {$natModifierMin}))
            AS `score`
    FROM `users`
    WHERE 1=1
) AS `a`
WHERE 1=1
ORDER BY a.`score`
;
";

$success = false;
$reason = null;
do {
    if( !mysql_query( $qCounter ) ) {
        $reason = 'Counter...';
        break;
    }
    
    if( !mysql_query( $qInsert ) ) {
        $reason = 'Insert...Select';
        break;
    }
    
    $success = true;
} while( false );

echo '<p>';
if( $success === true ) {
    echo 'Success!';
}else{
    echo 'Failed at ' . $reason;
}
echo '</p>';

include("inc/footer.php"); 
?>

[roopurt18]

Sorry, I'm an obvious beginner at php and I'm trying to learn bit by bit, so every piece of advice is appreciated.

 

My solution might be waaaay over your head, so ask questions.

 

If you can walk away understanding it, then you'll have learned a lot in very short time. 

[Aeolus]

I'm getting these values in my event table..

rank          score        user

Test  3e+0  1

Test  3e+0  2

Test  3e+0  3

 

>> I'll try again, to double check

 

>> getting the same thing :/

[roopurt18]

Oops.

 

I had the columns for the INSERT and SELECT in the wrong order.  I must have brain damage.  Change that part to:

 

// SELECTS, APPLIES RANDOM, ORDERS, RANKS, AND INSERTS ALL IN ONE
$qInsert = "
INSERT INTO `event` ( `user`, `score`, `rank` )
SELECT
    a.`name`,
    a.`score`,
    @MYCOUNTER:= @MYCOUNTER + 1 AS `rank`
FROM (
    SELECT
        `name`,
        `nat_d` + FLOOR( {$natModifierMin} + RAND() * ({$natModifierMax} - {$natModifierMin}))
            + `nat_j` + FLOOR( {$natModifierMin} + RAND() * ({$natModifierMax} - {$natModifierMin}))
            + `nat_c` + FLOOR( {$natModifierMin} + RAND() * ({$natModifierMax} - {$natModifierMin}))
            AS `score`
    FROM `users`
    WHERE 1=1
) AS `a`
WHERE 1=1
ORDER BY a.`score`
;
";

[roopurt18]

Also, if you later need to know what the random numbers were, then this solution will have to be modified as it loses them.

[Aeolus]

I won't need to know what the randomized numbers are, but I'm still getting

 

2e+0

3e+0

4e+0

 

as "score" on the event table... the ranking seems to be working now

[roopurt18]

Run this in phpMyAdmin or other such tool to make sure the inner-most SELECT is working correctly:

 

SELECT
        `name`,
        `nat_d` + FLOOR( 0 + RAND() * (10 - 0))
            + `nat_j` + FLOOR( 0 + RAND() * (10 - 0))
            + `nat_c` + FLOOR( 0 + RAND() * (10 - 0))
            AS `score`
    FROM `users`
    WHERE 1=1

 

If I had to guess, you're column type for score is a float or double.  Those types of numbers are usually displayed in scientific notation, which is what you have there.

 

Where exactly are you seeing those values?  In phpMyAdmin?  In another script?

 

What do your CREATE TABLE statements look like?

[Aeolus]

Lol.. so I suck I had varchar instead of integer on the score - no idea why... part of me had to be difficult I suppose.

 

Voila! It seems to be working! woo hoo, thank you sooo much for your help!