Database into PHP variable: Making progress, please, help me improve.

[gemini181]

Hi,

• I'm explaining this the best I can, based on what I currently know. Please, take a moment to give advice.

This code queries the local database and updates a remote database.
Whatever major flaws you may find:
1) It does actually work
2) I'm willing to spend huge amounts of time learning to improve
3) The reason why the local MySQL connection is left open "for so long", is because I need to learn a better way to turn the first query result into a "usable variable."

<?php

// edited out connection to local db, 
//  please, assume successful localhost connection

$result = mysql_query("SELECT username, score FROM mybb_games_champions WHERE `title` = 'Pacman'");

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

 // the next line is not needed, used only for testing...
    printf("Username: %s  <br />Score: %s", $row['username'], $row['score']);  
 
 
 // converting $row['score'] into a variable which will work in a query
$score = $row['score'];

 // starting remote connection
$hostname2 = "67.xx.xxx.xxx";
$username2 = 'remote_connect';
$password2 = 'knotpass';
$dbid2 = 'remote_connect';
$link2=mysql_connect($hostname2, $username2, $password2);
mysql_select_db($dbid2) or die ("Unable to connect to MySQL");

$query="UPDATE `admin_db8`.`pac1` SET `high_score` = $score WHERE `pac1`.`key` =5 LIMIT 1";
mysql_query($query);

mysql_close($link2);

}

mysql_close($link);

?>

When I try to echo (or use) for example

$row['username']

outside of the while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {....
}

The variable seems to no longer be set.
~~~~~~~~~~~

While I know I still have a lot to learn this "code sample" , does actually work. Instead of general advice (or a link to a well-known tutorial site), would someone be so kind as to teach me (an exact code example, please) a better way to:

a) retrieve small amounts of data (ie. a single row)
b) easily turn the result into a 'usable' php variable, which remains set after this pair of

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {....
} 

{} brackets (or something similar ends)

Thank you very much.

[djjjozsi]

1. question:

$sql="select afield from table WHERE id=1";
$res=mysql_query($sql);
$onefield=mysql_result($res , 0); 

2. question:

b) easily turn the result into a 'usable' php variable

its a shorten version if you're using: mysql_ fetch_ assoc()

[Shrapnel_N5]

When I try to echo (or use) for example
The variable seems to no longer be set.

Try again.
No one unsets your variable and it should be set. Last one, of course.

a) retrieve small amounts of data (ie. a single row)
b) easily turn the result into a 'usable' php variable, which remains set after this pair of

it is ok. you can use this while construct. and you will have your variable. if you want to shorten this call, you can set up a function.
Also, define "usable". As a matter of fact, any PHP variable is usable as well as any other.

but code is terrible, yeah

If you want to have a number of rows in one variable, you ought to be pointed to a link to a well-known tutorial site where you can learn arrays.

djjjozsi. for 1) he asked a row, not scalar

[SpacePhoenix]

The purpose of your script is to get the high scores from a table in one database and update a second database with the current high scores, right?

<?php

// Connect to first database $db1
// Connect to second database $db2


$current_high_scores = $result = mysql_query("SELECT username, score FROM mybb_games_champions WHERE `title` = 'Pacman'",$db1);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $username=$row['username'];
    $score=$row['score'];
    
    $query="UPDATE `admin_db8`.`pac1` SET `high_score` = $score WHERE `pac1`.`key` =5 LIMIT 1";
    
    $result = mysql_query($query,$db2);
    if (!$result) {
        die('Highscore updated Failed: ' . mysql_error());
    }
}
?>

Is it supposed to just fetch and update a single high score for a single game?

[gemini181]

1. question:

$sql="select afield from table WHERE id=1";
$res=mysql_query($sql);
$onefield=mysql_result($res , 0); 

2. question:
its a shorten version if you're using: mysql_ fetch_ assoc()

Yes, really simple yet, in this case, exactly what I should have remembered, thanks.

Try again.
No one unsets your variable and it should be set. Last one, of course.

it is ok. you can use this while construct. and you will have your variable. if you want to shorten this call, you can set up a function.
Also, define "usable". As a matter of fact, any PHP variable is usable as well as any other.

but code is terrible, yeah

If you want to have a number of rows in one variable, you ought to be pointed to a link to a well-known tutorial site where you can learn arrays.

djjjozsi. for 1) he asked a row, not scalar

Arrays are hugely important, and I'm working to learn them (or learning to work with them )

The purpose of your script is to get the high scores from a table in one database and update a second database with the current high scores, right?

<?php

// Connect to first database $db1
// Connect to second database $db2


$current_high_scores = $result = mysql_query("SELECT username, score FROM mybb_games_champions WHERE `title` = 'Pacman'",$db1);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $username=$row['username'];
    $score=$row['score'];
    
    $query="UPDATE `admin_db8`.`pac1` SET `high_score` = $score WHERE `pac1`.`key` =5 LIMIT 1";
    
    $result = mysql_query($query,$db2);
    if (!$result) {
        die('Highscore updated Failed: ' . mysql_error());
    }
}
?>

Is it supposed to just fetch and update a single high score for a single game?

Yes, correct...
I will try this within an hour, thank you also, a whole bunch.


// Very helpful, thanks everyone.