Learn VBA & Macros in 1 Week!

PHP - Collecting .xml Data To Input Into Database

Full Excel VBA Course - Beginner to Expert

Collecting .xml Data To Input Into Database	View Content

At the moment I collect data from an .XML file and it gets inserted into a database and it works great however the way in which the .xml files work has changed and I need to update my script to accommodate, before everything was processed by 1x .xml file and i collected data, now its done via 2x .xml files. So here is my current script;

<?php 
// INCLUDE DB CONNECTION FILE
include("includes/connect.php");
 
// CHANGE THE VALUES HERE
include("includes/config.php");

// URL FOR XML DATA
$url = "https://api.eveonline.com/corp/StarbaseList.xml.aspx?keyID=".$keyID."&vCode=".$vCode."";

// RUN XML DATA READY FOR INSERT
$xml = simplexml_load_file($url);

// Loop Through Names
$insertValues = array();
$modifiedTS = date('Y-m-d h:i:s');
foreach ($xml->result->rowset[0] as $value)
{
        //Prepare the values
        $itemID = $value['itemID'];
        $typeID = mysql_real_escape_string($value['typeID']);
        $locationID = $value['locationID'];
        $moonID = $value['moonID'];
        $state = mysql_real_escape_string($value['state']);
        $stateTimestamp = mysql_real_escape_string($value['stateTimestamp']);
        $onlineTimestamp  = $value['onlineTimestamp'];
        $standingOwnerID = $value['standingOwnerID'];

//Create and run ONE INSERT statement (with UPDATE clause)
    $insert = "INSERT INTO `ecmt_poslist` (itemID,typeID,locationID,moonID,state,stateTimestamp,onlineTimestamp,standingOwnerID,last_modified) VALUES('$itemID','$typeID','$locationID','$moonID','$state','$stateTimestamp','$onlineTimestamp','$standingOwnerID','$modifiedTS') ON DUPLICATE KEY UPDATE 
state='$state',
stateTimestamp='$stateTimestamp',
onlineTimestamp='$onlineTimestamp',
last_modified = '$modifiedTS'";
						
mysql_query($insert) or die(mysql_error());


//ERROR CHECKING OPTION ONLY!
//echo $insert;
//echo "<br><br>";

};
//UPDATE last time this script ran and insert timestamp into Database 
$timeNow = date('Y-m-d H:i:s', strtotime('-1 hour'));
$insertTime = "UPDATE `ecmt_API` SET time=1, time='$timeNow'";
mysql_query($insertTime) or die(mysql_error());

//Run query to delete records that were not just inserted/updated
$delete = "DELETE FROM `ecmt_poslist` WHERE last_modified < '$modifiedTS'";
mysql_query($delete) or die(mysql_error());

?>

The main problem is that the 'email' row is displayed properly while its while expression is not nested and alone(meaning the other data is omitted or commented out), but either nested or neighbored to the first while loop, it is displayed horizontally and the other data ('validity', 'valid_from', 'valid_to') is not displayed.'

Can someone help me on this, I guess the problem lies in the while loop?
Here is part of the HTML code:

<thead>
 <tr>
 <th data-column-id="id" data-type="numeric">ID</th>
 <th data-column-id="email">Subscriber's Email</th>
 <th data-column-id="validity">Validity</th>
<th data-column-id="valid_from">Valid From</th>
 <th data-column-id="valid_to">Valid To</th>
</tr>
</thead>

Here is part of the PHP code:

    <?php
    while($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        echo '
            <tr>
                <td>'.$row["id"].'</td>
        ';
        while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
        echo '
                <td>'.$row1["email"].'</td>
        ';
        }
        if($row["validity"] == 1) {
            echo '<td>'.$row["validity"].' month</td>';
        }else{
            echo '<td>'.$row["validity"].' months</td>';
        }
        echo '  <td>'.$row["valid_from"].'</td>
                <td>'.$row["valid_to"].'</td>
        </tr>';
    }
    ?>

P.S. Please know that I have purposely omitted the connection part and the methods in the class that are used in this code to keep the spot of the problem as clean as possible. If you need me to rephrase the question, please ask.

Thank you.

Lookup Data And Modifying Data Into Database Help Needed

Similar Tutorials

View Content

Hi, I've currently started to modify a chat script of mine to output a moderation panel but the moderation page seems empty(blank) every time I load it. What im trying to do is to take the ID part in my URL via the $_GET and look it up in my database table in the column named id, then select that specific row to be able to retrieve the StringyChat_ip and place it into another table to ban the IP and the second thing im trying to do is to be able to delete the specific row from my table. My Http link look something like http://imagecrab.fre.../ban.php?id=159 and my ban.php page where I want to lookup the 159 part and do the banning etc looks like

<?
include("admin_code_header.php");


if ($_POST["DeletePost"]) {
    $id = $_POST['id'];
    $query = "DELETE FROM ".$dbTable." WHERE id='".$id."'"; 
    mysql_query($query);
    echo "ID removed from system: ".$id;
  }


if ($_POST["BanIP"]) {
    $IP_To_Add = $_POST["ip"];
    
      $sql = "INSERT INTO ".$IPBanTable." (ip) VALUES (\"$IP_To_Add\")";
      $result = mysql_query($sql);
  }
$result = mysql_query("SELECT * FROM ".$dbTable." WHERE id='".$id."'",$db);

  while ($myrow = mysql_fetch_array($result)) {
    $msg = $myrow["StringyChat_message"];
$idm = $myrow["id"];



?>
<html>
<form name="form<? echo $myrow["id"];?>" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

<input name="DeletePost" type="submit" id="DeletePost" value="Delete">

<input name="BanIP" type="submit" id="BanIP" value="Ban <? echo $myrow["StringyChat_ip"];?>">
    </form>
</html>
<?
}
?>

$users = array();

$users_data = $db -> prepare('SELECT id, username FROM users');

$users_data -> execute();

while($fetched = $users_data->fetch()) {

$users[$fetchedt['id']] = array (

'id' => $fetched['id'],

'username' => $fetched['name']

);

}

echo json_encode($leaders);

i get

{"1":{"id":1,"username":"jeremia"},"2":{"id":2,"username":"Ernest"}}

Edited March 24 by mahenda

Seriuos Problem With Collecting Result In Recursive Function

Similar Tutorials

View Content

Hi there, hope one of you can help me with a problem im just having.

Ok, lets start with the explanation what i want to to:
I'd like to collect headlines from a html site, and get the test results in an array, so that the array structure represents the dom-levels of the site.

Small Example:
<h1>test1.1<h1/>
<h2>test2.1</h2>
<h2>test2.2</h2>
<h3>test3</h3>
<h1>test1.2<h1/>
Shoul end in a array structure like:

Code: [Select]
array('level 1' => array (
'sibble1' => array (
'headline' => 'test1.1',
'level2' => array(
'sibble1' => array (
'headline' => 'test2.1',
'level3' => array(), // empty data, needs to be processed anyway to find gaps, to maybe a h4 headline would be existing
),
'sibble2' => array (
'headline' => 'test2.2',
'level3' => array (
'sibble1' => array (
'headline' => 'test3.1',
'level4' => array(), // empty data, needs to be processed anyway to find gaps, to maybe a h4 headline would be existing
),
),
),
),
),
'sibble2' => array (
'headline' => 'test1.1',
'level2' => array(),
),
),
);

So i hope out of this example you can see what i want to do. level represents the healdine level 1-9, sibblin is as name for the childs on the headline level.

Ok, so to extract the herefore needed data out of the html, i build a class with a recursive function, that filters the html by a regex from one headline to the next, first iteratin all childs, if there are no more childs i go tho ne nextsibbling element.

as an running example code look he

Code: [Select]
<?php

class Application_Model_DomParser {

    const PREG_MATCH_ONE_ELEMENT_TO_MUCH_LENGTH = 4;

    private $slicedFields = array();

    public function __construct() {
    }

    public function sliceFirstSectionToDataFields($level = 1, $haystack) {

        preg_match_all("#(.*?)<h$level>(.*?)</h$level>(.*?)(<h$level>|$)#s", $haystack, $data);

        // prepare the chunkData
        $pageText = '';
        if (isset($data[1][0])) {
            $pageText = $data[1][0];
        }
        $headline = '';
        if (isset($data[2][0])) {
            $headline = $data[2][0];
        }
        $dataToProcessNextLevel ='';
        if (isset($data[3][0])) {
            $dataToProcessNextLevel = $data[3][0];
        }
        // @todo dirty warnings compression, search why warning occures
        @$posOfNextChild = strlen($data[0][0]) - self::PREG_MATCH_ONE_ELEMENT_TO_MUCH_LENGTH;

// from here goes the debug...
echo $headline ."::" .strlen($dataToProcessNextLevel). "<br>";

        if (strlen($dataToProcessNextLevel) <= self::PREG_MATCH_ONE_ELEMENT_TO_MUCH_LENGTH) {
            if ($level < 9) {
                $dataToProcessNextLevel = $haystack;
            }
            else {
                return;
            }
        }
        //recursive check for next level in $dataToProcessNextLevel
        $nextLevel = $level + 1;
        $this->sliceFirstSectionToDataFields($nextLevel, $dataToProcessNextLevel);

        $haystack = substr($haystack, $posOfNextChild);
        // slized To The End
        if (self::PREG_MATCH_ONE_ELEMENT_TO_MUCH_LENGTH == strlen($haystack)) {
//            die("slized To The End");
            return;
        }

        // recursive check for other childs in actuall level...
        $this->sliceFirstSectionToDataFields($level, $haystack);
    }
}

$htmlString = 'test<h1>headkline1.1</h1>
<p>test test</p>
<h2>headline 2.1</h2>test test
<h2>headline 2.2</h2>
<p>tes test test</p>
<h3>headline 3.1</h3>test
<h3>headline 3.2</h3>test
<h2>headline 2.3</h2>
<p> </p>
<p>test</p>
<h1>headline 1.2</h1>
<h2>headline 2.4</h2>
<p>11111111111112222222222222222222222</p>
<p> ewfwrefg upowmdg w3q09umq09wrt n3q089ty 3q0898943ty -98 41</p>
<h3>headline 3.3</h3>
<p>test</p>
<p>test</p>
<p>test</p>
<h1>1.3 testtest</h1>
<p>test</p>
<h3>head 3.4</h3>
<p>sadfsadfsadf asdfsda f sdaas saf saddas</p>
<h3>head 3.4</h3>
<h3>test 1.3</h3>
<p>test;</p>
';

$model = new Application_Model_DomParser();
$result = $model->sliceFirstSectionToDataFields(1, $this->htmlString);

So letting this piece of code run, you can se via the debug echo, every headline is found, even in the right order of its occurence.

My problem now is dont get it how to return the extracted values, and collect them to get a result as my above shown structure shows.

So i spent ours to solve this problem but didnt come to a result.

I know its a hard problem, and to help me takes time, cause its a complex situation.

Allthough i hope someone knows a anser. I need this problem solved, to get my mind rested!

Thanks, and greetings
Selma

Php And Mysql - Data Retrieval/input

Similar Tutorials

View Content

Please Help, Trying To Input Data Into Db Driving Me Crazy.

Similar Tutorials

View Content

Hi People.

I am trying to insert data from a form into my database.

Now I have the following code to connect to the DB to update a table so I know that I can connect to the DB ok

Code: [Select]
<?php
// this code I got from the new boston, PHP tutorial 25 in selecting a mysql db
// opens connection to mysql server
$dbc = mysql_connect('localhost', 'VinnyG', 'thepassword');
if (!$dbc) {
die("Not Connected:" . mysql_error ());
}
// select database
$db_selected = mysql_select_db ("sitename",$dbc);
if(!$db_selected)
{
die("can not connect:" . mysql_error ());

}
// testing code

$query="UPDATE users SET username = 'testing testing' WHERE user_id = '2'";
$result=mysql_query($query);

?>

Now here is the code from my form.

Code: [Select]
</head>

<body>
<?php
//include "connection_file.php"
//include "config01.php"

$username = "username";
$height_above = "height_above";
$mb_diff = "mb_diff";
$alternative = "alternative";

?>

<form name = 'form1' method = 'post' action='config01.php'>
<table width="700" border="1" cellspacing="5" cellpadding="5">
<caption>
Submit Your Airfield Details
</caption>
<tr>
<td width="100"> </td>
<td width="200">Your Name</td>
<td width="200"><input type='text' name='username' maxlength='30'></td>
<td width="100"> </td>
</tr>
<tr>
<td> </td>
<td>Height Above MSL</td>
<td><input type='text' name='height_above'maxlength= '30'></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td>Mb Difference</td>
<td><input type='text' name='mb_diff'maxlength='40'></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td>Alternative Airfield</td>
<td><input type='text' name='alternative' maxlength='30'></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td><input type='submit' name='submit' value='post' /></td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</form>
<?php
$sql01 = "INSERT INTO users SET user_id = '', username = '$username',height_above = '$height_above', mb_diff = $mb_diff, alternative = $alternative";
$result=mysql_query($sql01);
?>

</body>
</html>

here is the config01.php that the form refers to in the 'action' above.

Code: [Select]
<?php
$host = 'localhost';
$username = 'VinnyG';
$password = 'thepassword';
$db_name = 'sitename';

//connect to database
mysql_connect ("$host","$username","password")or die ("cannot connect to server");
mysql_select_db ("db_name") or die ("cannot select DB");

?>
Please could someone look at the above code and tell me where I'm going wrong. I can connect to the DB and update using the top script but I can't submit the form for some reason. I get a "cannot connect to server" message.

Please someone help. It's been driving me crazy for the past two days.

Regards
VinceG
http://www.microlightforum.com