SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 Code:
    <?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($resultMYSQL_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
    Code:
    $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
    PHP Code:
    while ($row mysql_fetch_array($resultMYSQL_ASSOC)) {....

    {} brackets (or something similar ends)

    Thank you very much.

  2. #2
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1. question:

    PHP Code:
    $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()

  3. #3
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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($resultMYSQL_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?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by djjjozsi View Post
    1. question:

    PHP Code:
    $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.

    Quote Originally Posted by Shrapnel_N5 View Post
    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 )

    Quote Originally Posted by SpacePhoenix View Post
    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 Code:
    <?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($resultMYSQL_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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •