Import and export data in databases to XML files

This class is meant to import and export data stored in SQL databases to XML files using the ADODB library.

It provides a function to export the data of SQL query result set to XML file.

It also provides another function that parses XML files that define the data of fields to be inserted as new rows of a table also specified in the XML files.

You can download these files (the class and the examples) from phpclass.org or here (8kb)

Included in the package are:

class.ADODB_XML.php Class Class mother

 class.xml.php    --    Class    -- Auxiliary class

 connection.php --  Aux. --  connection for ADOdb

 DB_TO_XML.php --  Example --  This example saves the data of the consultation in an file XML

 user.sql  --  Data  --  Script SQL

 usersxml.xml --  Data  --  XML for test

 XML_TO_DB.php --  Example --  This example saves the content of file XML in the table "users" of the Data base

 

Read More

How to Join Two Separate Databases in PHP Using Join Query

This is a common problem faced by many PHP/MySQL Developers.Sometimes you need to retreive data from 2 different databases and save them in a file.But the main problem here is that Config file points to only one database.So how do you join the two databases with your query.The below given PHP Code just does that for you.It shows you, how you can write a query to Join 2 separate database and fetch the required information.

<?php include "/home/db1/connections.php";?>
<?php
$db = "DATABASE1 NAME"; // name of the db
$host = $_SERVER[secret_db_host]; // db host name
$user = "DATABASE1 USERNAME"; // db username
$password = "DATABASE1 PASSWORD"; // db password 
$object= new mysql($host, $user, $password, $db);
$object->connect();
$object->select();
$sql = "SELECT email, first_name, last_name FROM em_subscriber ORDER BY first_name";
$object->query($sql);
while($object_res = $object->fetchObject())
 {
 $emails[]=$object_res->email;
 $contents.=$object_res->first_name." ";
 $contents.=$object_res->last_name." ";
 $contents.=$object_res->email." ";
 $contents.=$answer." ";
 }


$db = "DATABASE2 NAME"; // name of the db
$host = $_SERVER[secret_db_host]; // db host name
$user = "DATABASE2 USERNAME"; // db username
$password = "DATABASE2 PASSWORD"; // db password 
$object= new mysql($host, $user, $password, $db);
$object->connect();
$object->select();
$sql = "SELECT firstname,
 lastname,
 emailaddress,
 date_of_birth,
 postcode,
 new_bookings.checkin_date AS booking_date,
 new_bookings.id AS b_id
FROM customers
 LEFT OUTER JOIN new_bookings
 ON new_bookings.customer_id = customers.customer_id
GROUP BY customers.customer_id
ORDER BY lastname";
$object->query($sql);
while($object_res = $object->fetchObject())
 {
 if (!in_array($object_res->emailaddress, $emails))
 {
 $contents.=$object_res->firstname." ";
 $contents.=$object_res->lastname." ";
 $contents.=$object_res->emailaddress." ";
 $contents.=$object_res->postcode." ";
 $contents.=$object_res->date_of_birth." ";
 $contents.=$object_res->booking_date." ";
 $contents.=$answer." ";
 $not_found++;
 echo "<span style="color:green">".$object_res->emailaddress." Not Found In Database</span><br />"; 
 }
 else
 {
 $found++;
 echo "<span style="color:red">".$object_res->emailaddress." Found In Database</span><br />"; 
 }
 }
 
 
$contents = strip_tags($contents); // remove html and php tags etc.
//header(Content-type: application/ms-excel);
//header("Content-Disposition: attachment; filename=both.xls");
//print $contents;
echo $found."Found<br />";
echo $not_found."Not Found<br />";
?>

Note that the above given PHP code is tried and tested.So you can use it straightaway.Just replace the Database Name, Username,Password and SELECT Queries (represented in BLUE color) as required.

Like this Stuff! Subscribe to our blog and we will email you working solutions of all common problems faced by a PHP Developer. 

Read More