Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

loading XML file into mysql database via jquery and php

Hey there,

So I am basically trying to create a small scale, specific to my business wep app / content management system, which will be used to manage information about my products.. The first step I need to figure out, is how I can upload an XML file containing a list of products to my MySql table. (this will primarily be product quantity and SKU information.

Im using Quickbooks point of sale and I have a program that extracts all my inventory data as an XML file, but basically, I cant track enough information in QBPOS or through my websites storefront provider (prostores), so I am trying first to update a MYSQL table containing basic product information, which will be overwritten by my XML file upload each day. (and then I will worry about the second table which will contain the actual product information additional details which will be linked to the first.)

Anyways - Onto the specifics.

My mysql table primary key = ProductSKU and the qty value is ProductQty

I will load the XML format all of my products are in below in the code section. But basically all I need is a simple form and a script that can connect to my mysql database, parse the XML file and update the mysql database with the loaded data.
<?xml version="1.0" encoding="UTF-8">
<items>
  <item>
    <ItemNumber>8543</ItemNumber>
    <OnHandStore01>2</OnHandStore01>
  </item>
  <item>
    <ItemNumber>8549</ItemNumber>
    <OnHandStore01>1</OnHandStore01>
  </item>
</items>

Open in new window

0
lexusgs430
Asked:
lexusgs430
  • 2
1 Solution
 
Ray PaseurCommented:
From the breadth of the question, this does not sound as much like a question with an answer, as a need for application development, and for that you should hire an application developer.

let's try to break it down a little bit...

jQuery - cannot see any use or need for it in this work.
XML - you can parse XML with the SimpleXML class in PHP.  I can show you how to do that.
MySQL - you can use the parsed strings from the SimpleXML object to update your data base.

Please read over the code snippet and post back with any specific questions.

Best regards and Happy New Year, ~Ray
<?php // RAY_temp_xml_example_119.php
error_reporting(E_ALL);
echo "<pre>\n";

// THE TEST DATA SAMPLE FROM EE - NOTE THE ADDITION OF A QUESTION MARK TO THE XML DECLARATION
$xml = <<<EOXML
<?xml version="1.0" encoding="UTF-8"?>
<items>
  <item>
    <ItemNumber>8543</ItemNumber>
    <OnHandStore01>2</OnHandStore01>
  </item>
  <item>
    <ItemNumber>8549</ItemNumber>
    <OnHandStore01>1</OnHandStore01>
  </item>
</items>
EOXML;

// CREATING AN OBJECT
$obj = SimpleXML_Load_String($xml);

// ITERATING OVER THE OBJECT
foreach ($obj->item as $item)
{
    // EXTRACTING THE OOP DATA IN STRING FORMAT
    $i = (string)$item->ItemNumber;
    $o = (string)$item->OnHandStore01;

    // CONSTRUCTING A QUERY
    $sql = "INSERT INTO myTable ( ItemNumber, OnHandStore01 ) VALUES ( `$i`, `$o` )";

    // VISUALIZE THE QUERY WE JUST CREATED
    var_dump($sql);
}

Open in new window

0
 
Ray PaseurCommented:
Continuing on the MySQL part of this application, you will need to connect to, select, and query a MySQL data base.  Here is my teaching sample of how to do a few of those things.

A good starter book on PHP and MySQL is available from SitePoint, here:
http://www.sitepoint.com/books/phpmysql4/

That book has been in my professional library for years.  It is now in its fourth printing.  Useful as a tutorial and a reference, I am sure it will help you get a good foundation in PHP and MySQL.  SitePoint also has books on how to use XML.

Please post back with any questions, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);

// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$safe_username = mysql_real_escape_string($username);




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://us2.php.net/manual/en/function.mysql-query.php
$sql = "INSERT INTO my_table (username) VALUES (\"$safe_username\")";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-insert-id.php
$id  = mysql_insert_id($db_connection);

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now