Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jul 2018
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Parse XML in SQL PDO

    Hey all

    I think the title is the correct question. Am new to php.

    I have an old flash game that I'm converting to Unity3d. In the meantime the php that worked with the game 2 years ago no longer does so hence converting that to PDO.

    I'm stuck with retrieving the scores and username from the leaderboard. I don't know how to parse the XML. I've read a few tutorials but they are an actual XML file not for reading the data from flash.

    This was the old code in prepared statements...

    Code:
    $username = isset($_POST['username']) ? $_POST['username'] : ""; 
    $userscore = isset($_POST['userscore']) ? $_POST['userscore'] : "";
    
    
    if(isset($_POST['lbscores'])){
    
    
    	 if ($s = mysqli_prepare($connect, "SELECT `userscore`, `username` FROM `{$nameTable}` ORDER by `userscore`=? DESC LIMIT 0 , 1000 ")) {          
             mysqli_stmt_bind_param($s, "s", $userscore);
             mysqli_stmt_execute($s);
             mysqli_stmt_bind_result($s, $userscore, $username);
             $num_rows = 0;
             while (mysqli_stmt_fetch($s)) {
                $num_rows++;
             }
    
             if ($num_rows > 0) {
    	          header('Content-Type: text/xml');
                      echo "<?xml version='1.0'?>";
                      echo "<".$nameTable.">";
                      echo "<player>";
                      echo "<username>".$username."</username>";                                                                      
                      echo "<userscore>".(int)$userscore."</userscore>";
                      echo "</player>";
                      echo "</".$nameTable.">";
    				  }
                   mysqli_stmt_close($s);
    
    }
    }
    and this is what I've got so far in PDO (copied from some forum)... not far at all... and it doesn't work.

    Code:
    if(isset($_POST['lbscores'])){
    
        $username = isset($_POST['username']) ? $_POST['username'] : "";
        $userscore = isset($_POST['userscore']) ? $_POST['userscore'] : "";
    	 
        $sql = "SELECT * FROM table ORDER by userscore = :userscore DESC LIMIT 0 , 1000 ";           
        $stmt = $pdo->prepare($sql);
        $xml = array('<?xml version="1.0" encoding="UTF-8"?><v4desk>');
        
        $stmt->bindValue(':username', $username);
        $stmt->bindValue(':userscore', $userscore);
    	$result = $stmt->execute();
    
    	if ($result > 0) {  
            while ($data = $stmt->fetch()) {
    	$xml[] = '<username="'.$data['username'].'" userscore="'.$data["userscore"].'" />'; 
    }
    $xml[] = '</v4desk>';
    header('Content-Type:text/xml');
    
    }
    }
    Any pointers as always greatly appreciated.

    Cheers
    Last edited by vinyl-junkie; Jul 26th, 2018 at 04:03 AM. Reason: added code tags

  2. #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,271
    Thanks
    3
    Thanked 556 Times in 541 Posts
    There's technically nothing in the 1st code that would keep it from working under the latest php version. If it isn't working, you would need to find out why, because the problem will likely keep the converted code from working too.

    A good reason to convert code to use the php PDO extension would be if you want to simplify it. To execute an existing mysqli prepared query using PDO only involves a few steps, after making the database connection using the php PDO extension of course -

    1) The ? place-holder works for PDO, so you can just use the same sql syntax. Forming the sql query in a php variable is a good idea, since you can echo it to see what it is (more important when dynamically building sql statements) and it separates the sql syntax from the php statements.

    2) Call the PDO prepare() method.

    3) Call the PDOStatement execute([...]) method, with an array consisting of the input data.

    4) For SELECT queries, if you fetch the data into an appropriately named php variable, this will separate the database specific code, that knows how to query for and retrieve the data, from the presentation code, that knows how to produce the output from the data (if your unity based system needs some other data format, such as json, you will only need to change the presentation code. All the rest of the code will be untouched.) For a query that will at most match one row, use the fetch() method. For a query that can match a set of data (zero or more rows), use the fetchAll() method.

    5) Test and/or use the php variable from step #4 to produce the output. If the variable is empty, the query didn't match any row(s). For a query that matches a single row, just references the elements in the fetched array. For a query that matches a set of data, use a foreach(){} loop to iterate over the row(s) of fetched data.

    You are not parsing xml, you are producing it. Assuming the xml in the first code is correct, your task would be to produce the same xml with the converted code.

    Next, a question. What is the query supposed to match? The first code isn't using the username in the query, even though it is binding the value, which will end up producing an error, and it is ordering the data so that data with a matching userscore value is first, then it is fetching all the data but only using the last row.
    Last edited by CFMaBiSmAd; Jul 26th, 2018 at 03:39 AM.
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  3. #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,271
    Thanks
    3
    Thanked 556 Times in 541 Posts
    Also, you need to apply htmlentities() to all data values when they are being output, so that nothing in them can break the xml syntax or cause cross site scripting.
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  4. #4
    Senior Coder deathshadow's Avatar
    Join Date
    Feb 2016
    Location
    Keene, NH
    Posts
    3,505
    Thanks
    4
    Thanked 503 Times in 491 Posts
    Biggest problem I see is you're binding a parameter that doesn't even exist... :username doesn't even exist in your query, so that bindValue should be bombing.

    Also not sure why you'd be building your XML as an array, or sending the header after constructing it instead of header FIRST then just doing a echo... and there's NO reason to be screwing around with some of those extra 'variables for nothing' since that just ups your memory footprint.

    Code:
    if (isset($_POST['lbscores'])) {
    	$stmt = $pdo->prepare('
    		SELECT *
    		FROM table
    		ORDER by userscore = ? DESC
    		LIMIT 0 , 1000
    	');
    	header('Content-Type:text/xml');
    	echo '<?xml version="1.0" encoding="UTF-8"?>
    	<v4desk>';
    	if ($stmt->execute([
    		isset($_POST['userscore']) ? $_POST['userscore'] : ''
    	])) while ($data = $stmt->fetch()) echo '
    		<username="', $data['username'], '" userscore="', $data["userscore"], '" />'; 
    	echo '
    	</v4desk>';
    }
    Last edited by deathshadow; Jul 26th, 2018 at 12:07 PM.
    “There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies and the other way is to make it so complicated that there are no obvious deficiencies.” – C.A.R. Hoare, The 1980 ACM Turing Award Lecture
    http://www.cutcodedown.com

  5. #5
    New Coder
    Join Date
    Jul 2018
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    deathshadow your code didn't work (neither did the registration code either in my last post). I appreciate your help but how many times to I have to say I'm new at this? I'm taking a bit of this and a bit of that and learning as I do. It's no good asking me why I've done this or that b/c to be honest I don't know!

    However, what I've got thus far... finally generating xml output... is this... but it's only returning one result... the last entry in the database. This is for a leaderboard that is meant to display the highest scorer along with their name in descending order...

    Code:
    <?php
    
    session_start();
    
    require 'db2.php';
    
    $nameTable = 'v4desk';
    
    $username = isset($_POST['username']) ? $_POST['username'] : "";
    $userscore = isset($_POST['userscore']) ? $_POST['userscore'] : "";
    
    header('Content-Type:text/xml');
    	
    $sql = "SELECT username, userscore FROM $nameTable ORDER BY userscore=? DESC LIMIT 0 , 1000 ";  
    
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(1, $userscore, PDO::PARAM_INT);
    
    echo '<'.'?xml version="1.0" encoding="utf-8"?'.'>';
    echo '<'.$nameTable.'>';
    
    $stmt->execute();
    			
    $data = $stmt->fetch(PDO::FETCH_ASSOC);
                while($data) {     
                 echo '<username>'.$data['username'].'</username>';
    			echo '<userscore>'.$data['userscore'].'</userscore>';
    			}     		 
    		
     echo '</'.$nameTable.'>';		
    		
    ?>
    Last edited by vinyl-junkie; Jul 29th, 2018 at 07:38 AM. Reason: added code tags


 

Tags for this Thread

Posting Permissions

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