Update XML file after MySQL database requests via PHP

Question

I have a client front-end that reads and prints XML. I have an admin front-end that reads and writes to a MySQL database via PHP & MySQLi script. Currently the XML and MySQL database are not bind. How can I update or rewrite the XML file after each time the MySQL database is manipulated? Below is my 'create.php' file that creates new SQL records within my 'ajax_demo' table.

    include 'libs/db_connect.php';
    include 'toXml.php';

    try{
    $query = "INSERT INTO ajax_demo SET firstname = ?, lastname = ?";

    $stmt = $con->prepare($query);
    $stmt->bindParam(1, $_POST['firstName']);
    $stmt->bindParam(2, $_POST['lastName']);

    if($stmt->execute()){
        echo "Person was created.";
    }else{
        echo "Unable to create person.";
    }
    }

    catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
    }

Here is the file 'toXml.php' which I'm trying to bind:

    $myFile = "data.xml";
    $fh = fopen($myFile, 'w') or die("can't open file");
    $data_txt .= '<?xml version="1.0" encoding="utf-8"?>';
    $data_txt .= '<pages>';

    $query = mysql_query("SELECT * FROM ajax_demo");
    while($values_query = mysql_fetch_assoc($query))
    {
        $data_txt .= '<item>';
        $data_txt .= '<firstName>' .$values_query['firstName']. '</firstName>';
        $data_txt .= '<lastName>' .$values_query['lastName']. '</lastName>';
        $data_txt .= '</item>';
    }
    $data_txt .= '</pages>';
    fwrite($fh, $data_txt);
    fclose($fh);

But I can't figure out how to bind the two scripts. Can someone please help me bind these two scripts so they coordinate with each other? Thanks.

EDIT - 1/9/14 7:54PM

NEW SOLUTION:

used another php file called 'read.php'. I made the XML write script within it instead.

<?php
include 'libs/db_connect.php';
$query = "SELECT id, firstName, lastName, age FROM ajax_demo ORDER BY id asc";
$stmt = $con->prepare( $query );
$stmt->execute();
$num = $stmt->rowCount();
if($num>0){
    echo "<table id='tfhover' class='tftable' border='1'>";
    $myFile = "data.xml";
    $fh = fopen($myFile, 'w') or die("Can't open XML file.");
    $data_txt .= '<?xml version="1.0" encoding="utf-8"?>';
    $data_txt .= '<pages>';
        echo "<tr>";
            echo "<th>Firstname</th>";
            echo "<th>Lastname</th>";
            echo "<th>Age</th>";
            echo "<th style='text-align:center;'>Action</th>";
        echo "</tr>";
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            extract($row);              
            echo "<tr>";
            $data_txt .= '<person>';
                echo "<td>{$firstName}</td>";
                $data_txt .= "<title>{$firstName}</title>";
                echo "<td>{$lastName}</td>";
                    $data_txt .= "<lastName>{$lastName}</lastName>";
            echo "</tr>";
            $data_txt .= '</person>';
        }
    echo "</table>";//end table
    $data_txt .= '</pages>';
    fwrite($fh, $data_txt);
    fclose($fh);
  }
  else{
    echo "<div class='noneFound'>No records found.</div>";
  }
  ?>

wrap the functionality of thecreate.php script in a function, include the file in your other script, then newfunction() to call the code. — Marc B
– Marc B, Commented Jan 9, 2014 at 17:21
Could I just have both scripts within one file? If so how? Thanks! — Jim22150
– Jim22150, Commented Jan 9, 2014 at 17:23
You can put the update of xml in the if statment of INSERT query of the other file. — maurelio79
– maurelio79, Commented Jan 9, 2014 at 17:23
sure. a simple include('create.php') would work just as well, except that you might overwrite your previous DB connections, since create.php includes the db libs unconditionally. — Marc B
– Marc B, Commented Jan 9, 2014 at 17:25
Actually, @marc I like your idea because I'll be using the function more than once. Can you please show me how to wrap this and make the call? Thanks again. — Jim22150
– Jim22150, Commented Jan 9, 2014 at 17:40

maurelio79 · Accepted Answer · 2014-01-09 22:01:13Z

I add another answer because maybe it can be long :-)

Ok, if you do in this way, you just include toXml.php at beginning of create.php and in this way the first thing executed is the content in toXml.php. This is not good, because you need to execute first create.php and then toXml.php If you include a file php in another file, it will be executed immediatly, unless the content of the file that you include is a function. SO my suggest is create the file toXml.php in this way:

function toXml() { 
  $myFile = "data.xml";
  $fh = fopen($myFile, 'w') or die("can't open file");

  $data_txt .= '<?xml version="1.0" encoding="utf-8"?>';
  $data_txt .= '<pages>';

    $query = mysql_query("SELECT * FROM ajax_demo");
    while($values_query = mysql_fetch_assoc($query))
    {
        $data_txt .= '<item>';

        $data_txt .= '<firstName>' .$values_query['firstName']. '</firstName>';
        $data_txt .= '<lastName>' .$values_query['lastName']. '</lastName>';
        $data_txt .= '<age>' .$values_query['age']. '</age>';
        $data_txt .= '<hometown>' .$values_query['hometown']. '</hometown>';

        $data_txt .= '</item>';
    }
$data_txt .= '</pages>';

fwrite($fh, $data_txt);
fclose($fh);

}

After that you need to call the function when you need and you need the function after the INSERT query and if the query ends correctly, so you need to modify create.php in this way:

//include database connection
    include 'libs/db_connect.php';

    /////////////////////////////////
    //NEWLY ADDED 1/9/14 3:55PM
    include 'toXml.php';
    ////////////////////////////////



try{
    //write query
    $query = "INSERT INTO ajax_demo SET firstname = ?, lastname = ?, age = ?, hometown = ?";

    //prepare query for excecution
    $stmt = $con->prepare($query);

    //bind the parameters
    //this is the first question mark
    $stmt->bindParam(1, $_POST['firstName']);

    //this is the second question mark
    $stmt->bindParam(2, $_POST['lastName']);

    //this is the third question mark
    $stmt->bindParam(3, $_POST['age']);

    //this is the fourth question mark
    $stmt->bindParam(4, $_POST['hometown']);

    // Execute the query
    if($stmt->execute()){
       // If the query is executed correctly now it's the time to launch the function that create the xml
         toXml() 
        echo "Person was created.";
    }else{
        echo "Unable to create person.";
    }
}

//to handle error
catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
}
?>

Wow @maurello79 you are awesome. I feel really back, hopefully that didn't take you long, but I descovered that I was going about this all wrong, it would have probably helped if you saw my entire framework, but there is a 'read.php' file called pre and post SQL requests. I found a very simple solution by modifying it. I posted the script in my question.

maurelio79 · Accepted Answer · 2014-01-09 17:28:50Z

1

 update_xml() {
      $con = mysqli_connect('localhost','jamestar_user','passed','jamestar_ajax');
      $myFile = "rss.xml";
      $fh = fopen($myFile, 'w') or die("can't open file");

      $rss_txt .= '<?xml version="1.0" encoding="utf-8"?>';
      $rss_txt .= '<pages>';

      $query = mysql_query("SELECT * FROM ajax_demo");
      while($values_query = mysql_fetch_assoc($query))
         {
           $rss_txt .= '<item>';
           $rss_txt .= '<firstName>' .$values_query['firstName']. '</firstName>';
           $rss_txt .= '<lastName>' .$values_query['lastName']. '</lastName>';
           $rss_txt .= '<age>' .$values_query['age']. '</age>';
           $rss_txt .= '<hometown>' .$values_query['hometown']. '</hometown>';
           $rss_txt .= '<job>' .$values_query['job']. '</job>';
           $rss_txt .= '</item>';
         }
      $rss_txt .= '</pages>';

      fwrite($fh, $rss_txt);
      fclose($fh);
      mysqli_close($con);

}

//include database connection
    include 'libs/db_connect.php';

try{
    //write query
    $query = "INSERT INTO ajax_demo SET firstname = ?, lastname = ?, age = ?, hometown = ?";

    //prepare query for excecution
    $stmt = $con->prepare($query);

    //bind the parameters
    //this is the first question mark
    $stmt->bindParam(1, $_POST['firstName']);

    //this is the second question mark
    $stmt->bindParam(2, $_POST['lastName']);

    //this is the third question mark
    $stmt->bindParam(3, $_POST['age']);

    //this is the fourth question mark
    $stmt->bindParam(4, $_POST['hometown']);

    // Execute the query
    if($stmt->execute()){
        //if query is ok i update the xml
        update_xml();
        echo "Person was created.";
    }else{
        echo "Unable to create person.";
    }
}

//to handle error
catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
}
?>

answered Jan 9, 2014 at 17:28

maurelio79

2921 silver badge9 bronze badges

5 Comments

Jim22150 Over a year ago

Awesome @maurelio, thank you. However, I decided to go with marc's idea. How can I use two separate files? I'm new to php, classes and methods seem weird to me compared to my background with Java so please forgive me.

maurelio79 Over a year ago

You write the function update_xml() in another file and include that file in the main file

Jim22150 Over a year ago

I definitely tried that before making this post but the xml file was never created. Is there something wrong with my syntax?

maurelio79 Over a year ago

Post your new code: file with update_xml() function and the other where you include it, so i can see if everithing is ok.

Jim22150 Over a year ago

Okay I modified the first snippet by simply adding another include statement. I saved the second snippet as toXml.php. Both files are in the same directory. Seems to not be working still. Any clues?

Collectives™ on Stack Overflow

Update XML file after MySQL database requests via PHP

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related