-
Senior Member
php >> mySQL, not returning data as XML??
hey gang-
(more php problems apparently...LOL)
I am trying to do a simple query to the DB.. using a specific userID.. as well as return rows between a date range...
I tested the queries in phpMyAdmin.. and they worked fine.. when I threw them into a PHP script, to try and out put the data as XML using a while() loop..
I got 'nothing'...
I dont get any errors when I try to access the script directly through the browser (using WAMP Server to server it up)..
but I 'also' dont get any rows spit out (echo'd) to the screen either?
it would be a bit easier if I got an error to help guide me in my mistake...but I got nothing?
Looking at the source code of the page.. it seems the XML is 'there', structured correctly?
php code:
PHP Code:
<?php
$myDB = mysql_connect('localhost', 'root', '') or die('Connection failed!');
mysql_select_db('fac_paymentdb', $myDB);
$query = "SELECT * FROM payments WHERE collectorID = 'DMD' AND date(subDate) >='2012-01-01' AND date(subDate) <=
'2012-10-17'";
$result = mysql_query($query, $myDB);
$xml_output = "<?xml version=\"1.0\"?>\n";
$xml_output .= "<payments>\n";
while($row = mysql_fetch_assoc($result)){
$xml_output .= "\t<payment transID='" . $row['transID'] . "' subDate='" . $row['subDate'] . "' collectorID='" .
$row['collectorID'] . "' pFirst='" . $row['pFirst'] . "' pMiddle='" . $row['pMiddle'] . "' pLast='" . $row['pLast'] . "' pAddress='" . "' />\n";
}
$xml_output .= "</payments>";
echo ($xml_output);
?>
1.) Is this even WRONG?
2.) I seem to recall in the past seeing the XML data structure in the browser if I targeted the script directly?
(is this a situation of using echo() vs print() perhaps?)
thanks
-
read SimpleXMLElement in php.net
marlopax
-
Senior Member
thanks for the reply..
I dont really need (want) to build a XML object....
cant I just pass back an 'XML string' to FLASH?
Im also curious as to why nothing is being echo'd/printed to the screen??
thanks
-
Senior Member
been working on this still..
I find it very ODD that its not echo'ing out?
Is there a syntax error or something somewhere? (but it shows all the correct data in PAGE SOURCE?????????)
when looking at the page source code.. the last line closing the xml element </payments> is highlighted red?
not sure if this just a Firefox thing or what?
(right click on page...page source)
this is what is what I get:
Code:
COUNTER: 48
COUNTER: 50
COUNTER: 51
TOTAL COUNT: 3
<payments>
<payment transID='48' subDate='2012-08-15' collectorID='DMD' pFirst='q' pMiddle='q' pLast='q' pAddress='q' pCity='q' pState='WI' pZip='11' hPhone='1' wPhone='1' dFirst='q' dMiddle='q' dLast='q' maiden='q' clientNum='q' debtorNum='q' paymentType='check' paymentAmount='23.02' procDae='2012-10-17' bankName='q' checkNum='11' routingNum='11' bankAccountNum='22' ccNum='' securityNum='' expDate='0000-00-00' creditType='' accountType='' posted='0' postedDate='0000-00-00' />
<payment transID='50' subDate='2012-10-15' collectorID='DMD' pFirst='q' pMiddle='q' pLast='q' pAddress='q' pCity='q' pState='WI' pZip='11' hPhone='1' wPhone='1' dFirst='q' dMiddle='q' dLast='q' maiden='q' clientNum='q' debtorNum='q' paymentType='check' paymentAmount='1552.05' procDae='2012-12-16' bankName='q' checkNum='11' routingNum='11' bankAccountNum='22' ccNum='' securityNum='' expDate='0000-00-00' creditType='' accountType='' posted='0' postedDate='0000-00-00' />
<payment transID='51' subDate='2012-10-17' collectorID='DMD' pFirst='q' pMiddle='q' pLast='q' pAddress='q' pCity='q' pState='WI' pZip='11' hPhone='1' wPhone='1' dFirst='q' dMiddle='q' dLast='q' maiden='q' clientNum='q' debtorNum='q' paymentType='check' paymentAmount='12.65' procDae='2013-01-15' bankName='q' checkNum='11' routingNum='11' bankAccountNum='22' ccNum='' securityNum='' expDate='0000-00-00' creditType='' accountType='' posted='0' postedDate='0000-00-00' />
</payments>
PHP Code:
<?php
$counter = 0;
$myDB = mysql_connect('localhost', 'root', '') or die('Connection failed!');
mysql_select_db('fac_paymentdb', $myDB);
$query = "SELECT * FROM payments WHERE collectorID = 'DMD' AND date(subDate) >='2012-01-01' AND date(subDate) <= '2012-10-17'";
$result = mysql_query($query, $myDB);
/*$xml_output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";*/
/*$xml_output = "<?xml version=\"1.0\"?>\n";*/
$xml_output = "<payments>\n";
while($row = mysql_fetch_assoc($result)){
echo "COUNTER: ".$row['transID']."\n";
$xml_output .= "\t<payment transID='" . $row['transID'] . "' subDate='" . $row['subDate'] . "' collectorID='" . $row['collectorID'] . "' pFirst='" . $row['pFirst'] . "' pMiddle='" . $row['pMiddle'] . "' pLast='" . $row['pLast'] . "' pAddress='" . $row['pAddress'] . "' pCity='" . $row['pCity'] . "' pState='" . $row['pState'] . "' pZip='" . $row['pZip'] . "' hPhone='" . $row['hPhone'] . "' wPhone='" . $row['wPhone'] . "' dFirst='" . $row['dFirst'] . "' dMiddle='" . $row['dMiddle'] . "' dLast='" . $row['dLast'] . "' maiden='" . $row['maiden'] . "' clientNum='" . $row['clientNum'] . "' debtorNum='" . $row['debtorNum'] . "' paymentType='" . $row['paymentType'] . "' paymentAmount='" . $row['paymentAmount'] . "' procDae='" . $row['procDate'] . "' bankName='" . $row['bankName'] . "' checkNum='" . $row['checkNum'] . "' routingNum='" . $row['routingNum'] . "' bankAccountNum='" . $row['bankAccountNum'] . "' ccNum='" . $row['ccNum'] . "' securityNum='" . $row['securityNum'] . "' expDate='" . $row['expDate'] . "' creditType='" . $row['creditType'] . "' accountType='" . $row['accountType'] . "' posted='" . $row['posted'] . "' postedDate='" . $row['postedDate'] ."' />\n";
$counter++;
}
$xml_output .= "</payments>";
echo "TOTAL COUNT: $counter \n";
echo "$xml_output";
//echo "&xml_output=$xml_output";
?>
it queries fine..... seems to grab the correct (fake) data...
return said data in a 'somewhat' XML structured 'string'....
if I add another echo AFTER the xml_output attempt... it prints to screen.. but the XML_OUPUT var/string I am building will NOT print to screen?
and the </payments> element is still red?
anybody got any suggestions?
thanks
-
Hi,
to make sure it is not the browser fooling you, how about trying a different tool to capture the output? I am using curl quite often, and believe it is available for windows too
BTW: when I ran your script right away, I got a mysql error (of course, I do not have the database set up) plus an empty xml container
Code:
<?xml version="1.0"?>
<payments>
</payments>
Musicman
-
Senior Member
HI Musicman-
I am not familiar with the 'curl' option you suggest?
I seem to pulling/loading the data into FLASH just fine (using a sendAndLoad method)... but can NOT get 'anything' to print/echo to the screen/browser when looping through and building the xml_output string??
I look at source code.. and it is 'all' there.. formatted correctly..etc... (and again.. it does load into flash fine enough where I can spider through the 'xml' nodes)..
however when I look at this page source code.. the 'last' payments element/tag is RED?
(like a syntax error or something? but I see nothing along those lines)..
am I missing something? should this NOT be echo'd/printed to the screen for some PHP reason I am not understanding? lol
Thanks..
-w
-
Hi, curl is a program that is available from haxx.se. It is a command line tool that simply grabs something from the server (in this case your xml dataset) and saves it as a file.
So you can inspect the file and be sure that no browser logic changed things
Musicman
-
I dont get any errors when I try to access the script directly through the browser (using WAMP Server to server it up)..
but I 'also' dont get any rows spit out (echo'd) to the screen either?
it would be a bit easier if I got an error to help guide me in my mistake...but I got nothing?
**Links removed by Site Administrator so it doesn't look like you're spamming us. Please don't post them again.**
-
Hi there,
Quiet some time has passed and maybe the solution was found yet it did not look to me like it by looking at the last thread. So here a way of approaching it if anybody is still Interested to write / read XML with Flash / FLEX etc.
Code:
<?php
// init.php
// Script:
// Connecting to the MySQL DataBase Server & a DataBase.
/* ------------------------------------------------------------------------------------------------------------------ */
// MySQL DataBase Server Variables.
$mysql_host = "localhost";
$mysql_user = "root";
$mysql_pass = "";
// DataBase Variable.
$db = 'webflash_createcartdb';
// Connect to MySQL DataBase Server.
$con = @ mysql_connect("$mysql_host", "$mysql_user", "$mysql_pass")
or exit("Could not connect to MySQL DataBase Server! \n Select DB Error: " . @ mysql_error());
$con = @ mysql_select_db($db)
or exit( 'Can\'t select the Database is unavailable.' . @ mysql_error());
return $con;
/* ------------------------------------------------------------------------------------------------------------------ */
?>
READ & WRITE:
Code:
<?php
// custInfoDetails.php
/* ------------------------------------------------------------------------------------------------------------------ */
header('Content-Type: text/xml');
/* ------------------------------------------------------------------------------------------------------------------ */
include("../Conn/init.php");
/* ------------------------------------------------------------------------------------------------------------------ */
// MAIN TABLE.
/* ------------------------------------------------------------------------------------------------------------------ */
if (isset($_POST['lastName'])) {
$title = $_POST['title'];
$firstName = $_POST['firstName'];
$middleName = $_POST['middleName'];
$lastName = $_POST['lastName'];
$fullName = $_POST['fullName'];
$no = $_POST['no'];
$street1 = $_POST['street1'];
$street2 = $_POST['street2'];
$zip = $_POST['zip'];
$suburb = $_POST['suburb'];
$city = $_POST['city'];
$state = $_POST['state'];
$country = $_POST['country'];
$email = $_POST['email'];
$userName = $_POST['userName'];
$password = $_POST['password'];
$userNameCopy = mysql_real_escape_string($_POST["userNameCopy"]);
$sql = 'INSERT INTO ' . $userNameCopy . '
(title, firstName, middleName, lastName, fullName, no, street1, street2, zip, suburb, city,
state, country, email, userName, password, now)
VALUES
("' . $title . '", "' . $firstName . '", "' . $middleName . '", "' . $lastName . '", "' . $fullName . '",
"' . $no . '", "' . $street1 . '", "' . $street2 . '", "' . $zip . '", "' . $suburb . '",
"' . $city . '", "' . $state . '", "' . $country . '", "' . $email . '",
"' . $userName . '", "' . $password . '", NOW() )';
$result = @ mysql_query($sql);
if (!$result) { exit('Error performing the MAIN INSERT query.' . @ mysql_error());
}
}
/* ------------------------------------------------------------------------------------------------------------------ */
$sql = "SELECT custcartinfoID, title, firstName, middleName, lastName, fullName, no, street1, street2,
zip, suburb, city, state, country, email, userName, password, now FROM ' . $userNameCopy . ' ";
$result = @ mysql_query($sql);
if (!$result) {
$message = 'Invalid query: ' . @ mysql_errno() . " : " . @ mysql_error() . "\n";
$message .= 'Whole query: ' . $sql;
exit($message);
}
/* ------------------------------------------------------------------------------------------------------------------ */
// XML READOUT.
/* ------------------------------------------------------------------------------------------------------------------ */
$xml = new DomDocument('1.0', 'UTF-8');
$root = $xml->createElement('allMessages');
$root = $xml->appendChild($root);
$result = @ mysql_query($sql);
if ($result) {
if ( @ mysql_num_rows($result) > 0) {
while ($row = @ mysql_fetch_array($result)) {
$custcartinfoID = $row['custcartinfoID'];
$title = $row['title'];
$firstName = $row['firstName'];
$middleName = $row['middleName'];
$lastName = $row['lastName'];
$fullName = $row['fullName'];
$no = $row['no'];
$street1 = $row['street1'];
$street2 = $row['street2'];
$zip = $row['zip'];
$suburb = $row['suburb'];
$city = $row['city'];
$state = $row['state'];
$country = $row['country'];
$email = $row['email'];
$userName = $row['userName'];
$password = $row['password'];
$now = $row['now'];
// Message Element OPEN Node.
$itemElement = $xml->createElement('message');
$itemElement = $root->appendChild($itemElement);
// First Field.
$idElement = $xml->createElement('custcartinfoID', $custcartinfoID);
$idElement = $itemElement->appendChild($idElement);
$titleElement = $xml->createElement('title', $title);
$titleElement = $itemElement->appendChild($titleElement);
$firstNameElement = $xml->createElement('firstName', $firstName);
$firstNameElement = $itemElement->appendChild($firstNameElement);
$middleNameElement = $xml->createElement('middleName', $middleName);
$middleNameElement = $itemElement->appendChild($middleNameElement);
$lastNameElement = $xml->createElement('lastName', $lastName);
$lastNameElement = $itemElement->appendChild($lastNameElement);
$fullNameElement = $xml->createElement('fullName', $fullName);
$fullNameElement = $itemElement->appendChild($fullNameElement);
$noElement = $xml->createElement('no', $no);
$noElement = $itemElement->appendChild($noElement);
$street1Element = $xml->createElement('street1', $street1);
$street1Element = $itemElement->appendChild($street1Element);
$street2Element = $xml->createElement('street2', $street2);
$street2Element = $itemElement->appendChild($street2Element);
$zipElement = $xml->createElement('zip', $zip);
$zipElement = $itemElement->appendChild($zipElement);
$suburbElement = $xml->createElement('suburb', $suburb);
$suburbElement = $itemElement->appendChild($suburbElement);
$cityElement = $xml->createElement('city', $city);
$cityElement = $itemElement->appendChild($cityElement);
$stateElement = $xml->createElement('state', $state);
$stateElement = $itemElement->appendChild($stateElement);
$countryElement = $xml->createElement('country', $country);
$countryElement = $itemElement->appendChild($countryElement);
$emailElement = $xml->createElement('email', $email);
$emailElement = $itemElement->appendChild($emailElement);
$userNameElement = $xml->createElement('userName', $userName);
$userNameElement = $itemElement->appendChild($userNameElement);
$passwordElement = $xml->createElement('password', $password);
$passwordElement = $itemElement->appendChild($passwordElement);
// Last Field.
$nowElement = $xml->createElement('now', $now);
$nowElement = $itemElement->appendChild($nowElement);
}
}
// Message Element CLOSING Node.
else {
$messageElement = $xml->createElement('message','There are no posts.');
$messageElement = $root->appendChild($messageElement);
}
}
else {
$messageElement = $xml->createElement('message', @mysql_error());
$messageElement = $root->appendChild($messageElement);
}
// Return the XML Document.
echo $xml->saveXML();
/* ------------------------------------------------------------------------------------------------------------------ */
// CLOSE DATABSE.
// Close DataBase Server Connection!
@ mysql_close($con);
/* ------------------------------------------------------------------------------------------------------------------ */
?>
READ ONLY:
Code:
<?php
// select.php
/* ------------------------------------------------------------------------------------------------------------------ */
// First, this script uses the header() function to tell the web server that the return is going to be XML.
header('Content-Type: text/xml');
/* ------------------------------------------------------------------------------------------------------------------ */
// Connecting with MySQL DataBase Server & a DataBase.
include("init.php");
/* ------------------------------------------------------------------------------------------------------------------ */
// MAIN TABLE.
/* ------------------------------------------------------------------------------------------------------------------ */
// SELECT more than one Table !
/* $query="SELECT movie.movie_name, movietype.movietype_label FROM movie, movietype
WHERE movie.movie_type = movietype.movietype_id
AND movie.movie_year>1990
ORDER BY movie_type"; */
$sql = 'SELECT customerID, title, firstname, middlename, lastname, no, street1, street2, zip, suburb, city,
state, country, email, emailpriv, emailbus, url1, url2, note, now FROM customerdetails';
$result = @ mysql_query($sql);
if (!$result) {
$message = 'Invalid query: ' . @ mysql_errno() . " : " . @ mysql_error() . "\n";
$message .= 'Whole query: ' . $sql;
exit($message);
}
/* ------------------------------------------------------------------------------------------------------------------ */
// XML READOUT.
/* ------------------------------------------------------------------------------------------------------------------ */
$xml = new DomDocument('1.0', 'UTF-8');
$root = $xml->createElement('customerDetails');
$root = $xml->appendChild($root);
$result = @ mysql_query($sql);
if ($result) {
if ( @ mysql_num_rows($result) > 0) {
while ($row = @ mysql_fetch_array($result)) {
$customerID = $row['customerID'];
$title = $row['title'];
$firstname = $row['firstname'];
$middlename = $row['middlename'];
$lastname = $row['lastname'];
$no = $row['no'];
$street1 = $row['street1'];
$street2 = $row['street2'];
$zip = $row['zip'];
$suburb = $row['suburb'];
$city = $row['city'];
$state = $row['state'];
$country = $row['country'];
$email = $row['email'];
$emailpriv = $row['emailpriv'];
$emailbus = $row['emailbus'];
$url1 = $row['url1'];
$url2 = $row['url2'];
$note = $row['note'];
$now = $row['now'];
// Message Element OPEN Node.
$itemElement = $xml->createElement('information');
$itemElement = $root->appendChild($itemElement);
// First Field.
$idElement = $xml->createElement('customerID', $customerID);
$idElement = $itemElement->appendChild($idElement);
$titleElement = $xml->createElement('title', $title);
$titleElement = $itemElement->appendChild($titleElement);
$firstnameElement = $xml->createElement('firstname', $firstname);
$firstnameElement = $itemElement->appendChild($firstnameElement);
$middlenameElement = $xml->createElement('middlename', $middlename);
$middlenameElement = $itemElement->appendChild($middlenameElement);
$lastnameElement = $xml->createElement('lastname', $lastname);
$lastnameElement = $itemElement->appendChild($lastnameElement);
$noElement = $xml->createElement('no', $no);
$noElement = $itemElement->appendChild($noElement);
$street1Element = $xml->createElement('street1', $street1);
$street1Element = $itemElement->appendChild($street1Element);
$street2Element = $xml->createElement('street2', $street2);
$street2Element = $itemElement->appendChild($street2Element);
$zipElement = $xml->createElement('zip', $zip);
$zipElement = $itemElement->appendChild($zipElement);
$suburbElement = $xml->createElement('suburb', $suburb);
$suburbElement = $itemElement->appendChild($suburbElement);
$cityElement = $xml->createElement('city', $city);
$cityElement = $itemElement->appendChild($cityElement);
$stateElement = $xml->createElement('state', $state);
$stateElement = $itemElement->appendChild($stateElement);
$countryElement = $xml->createElement('country', $country);
$countryElement = $itemElement->appendChild($countryElement);
$emailElement = $xml->createElement('email', $email);
$emailElement = $itemElement->appendChild($emailElement);
$emailprivElement = $xml->createElement('emailpriv', $emailpriv);
$emailprivElement = $itemElement->appendChild($emailprivElement);
$emailbusElement = $xml->createElement('emailbus', $emailbus);
$emailbusElement = $itemElement->appendChild($emailbusElement);
$url1Element = $xml->createElement('url1', $url1);
$url1Element = $itemElement->appendChild($url1Element);
$url2Element = $xml->createElement('url2', $url2);
$url2Element = $itemElement->appendChild($url2Element);
$noteElement = $xml->createElement('note', $note);
$noteElement = $itemElement->appendChild($noteElement);
// Last Field.
$nowElement = $xml->createElement('now', $now);
$nowElement = $itemElement->appendChild($nowElement);
}
}
// Message Element CLOSING Node.
else {
$messageElement = $xml->createElement('information','There are no posts.');
$messageElement = $root>appendChild($messageElement);
}
}
else {
$messageElement = $xml->createElement('information', @mysql_error());
$messageElement = $root>appendChild($messageElement);
}
echo $xml->saveXML();
?>
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|