Hi Smokey. I am starting a new thread as you suggested.
I finally have a script (listing 2) that extracts the data from both tables and writes it to an XML file. The problem now is that I have too much data as each time an image URL is written, it writes all the other information along with it. What I need is the information from Table 1 to be written once, then all the image URLs associated with that ID to be written afterwards, sequentially, in the same file. It looks like the only way I have found to extract the data from both tables makes it inevitable that it will lump everything together on each pass through the loop.
I have tried extracting each row individually (SELECT xxx,yyy,zzz,aaa,bbb,ccc ) (see listing 1) but the script exits at the “Data not found” die point.
Listing 1
[php]// connect to dbase
$linkID = mysql_connect($host, $user, $pass) or die(“Could not connect to host.”);
mysql_select_db($database, $linkID) or die(“Could not find database.”);
Listing 2
//select info from table
$query = “SELECT id, refresh_time, ref, price, available, locality, province, address, bedrooms, bathrooms, type, garage, extra_16, covered_area, area FROM jos_properties_products”;
// give back results
$resultID = mysql_query($query, $linkID) or die(“Data not found.”);
$num = mysql_num_rows($resultID);
[/php]
[php]<?php
// info for dbase connectiion
$host = “*********”;
$user = “*********”;
$pass = “*********”;
$database = “*********”;
// connect to dbase
$linkID = mysql_connect($host, $user, $pass) or die(“Could not connect to host.”);
mysql_select_db($database, $linkID) or die(“Could not find database.”);
//select info from table
$query = “SELECT * FROM jos_properties_products, jos_properties_images WHERE jos_properties_products.id = jos_properties_images.parent” or die (“Could not join tables”);
// give back results
$resultID = mysql_query($query, $linkID) or die(“Data not found.”);
$num = mysql_num_rows($resultID);
//output as XML
$xml_output = “<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n”;
$xml_output .= “\n”;
//create XML file
if ($num != 0) {
//set location of XML file
$file= fopen("./XML/kyerofeed.xml", “w”);
$xml_output ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\r\n";
$xml_output .="\r\n";
//while loop looking for results
while ($row = mysql_fetch_array($resultID)) {
//Make sure only published properties listed
//if ($row[“published”] == “1”) {
// if statement to look through results and returns ID info
if ($row[“id”]) {
//$xml_output .="\t[id = “” . $row[“id”] . “”]\r\n";
$xml_output .="\t\t" . $row[“id”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";//$xml_output .="\t<id =“0”>\r\n";
//$xml_output .="\t\r\n";
}
// returns date info
if ($row[“refresh_time”]) {
// $xml_output .="\t[refresh_time ="" . $row[“refresh_time”] . “”]\r\n";
$xml_output .="\t\t" . $row[“refresh_time”] . “\r\n”;//$xml_output .="\t</refresh_time>\r\n";
} else {
$xml_output .="\t\t0\r\n";//$xml_output .="\t<refresh_time =“0”>\r\n";
//$xml_output .="\t</refresh_time>\r\n";
}
// returns ref info
if ($row[“ref”]) {
// $xml_output .="\t[ref ="" . $row[“ref”] . “”]\r\n";
$xml_output .="\t\t" . $row[“ref”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t<ref =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}
// returns price info
if ($row[“price”]) {
//$xml_output .="\t[price ="" . $row[“price”] . “”]\r\n";
$xml_output .="\t\t" . $row[“price”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<price =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}
// returns sale info
if ($row[“available”]) {
// $xml_output .="\t[available ="" . $row[“available”] . “”]\r\n";
$xml_output .="\t\tSale\r\n";//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t[available =“Sale”]\r\n";
//$xml_output .="\t\r\n"; */
}
// returns town info
if ($row[“locality”]) {
//$xml_output .="\t[locality ="" . $row[“locality”] . “”]\r\n";
$xml_output .="\t\t" . $row[“locality”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t<locality =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}
// returns province info
if ($row[“province”]) {
//$xml_output .="\t[province ="" . $row[“province”] . “”]\r\n";
$xml_output .="\t\t" . $row[“province”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<province =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}
// returns location info
if ($row[“address”]) {
//$xml_output .="\t[address ="" . $row[“address”] . “”]\r\n";
$xml_output .="\t\t" . $row[“address”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<address =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}
// returns bedrooms info
if ($row[“bedrooms”]) {
// $xml_output .="\t[bedrooms ="" . $row[“bedrooms”] . “”]\r\n";
$xml_output .="\t\t" . $row[“bedrooms”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t<bedrooms =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}
// returns bathrooms info
if ($row[“bathrooms”]) {
// $xml_output .="\t[bathrooms ="" . $row[“bathrooms”] . “”]\r\n";
$xml_output .="\t\t" . $row[“bathrooms”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<bathrooms =“0”>\r\n";
//$xml_output .="\t\r\n";*/
}
// returns type info
if ($row[“type”]) {
//$xml_output .="\t[type ="" . $row[“type”] . “”]\r\n";
$xml_output .="\t\t" . $row[“type”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<type =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}
// returns garage info
if ($row[“garage”]) {
//$xml_output .="\t[garage ="" . $row[“garage”] . “”]\r\n";
$xml_output .="\t\t1\r\n";//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t<garage =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}
// returns pool info
if ($row[“extra_16”]) {
$xml_output .="\t[extra_16 ="" . $row[“extra_16”] . “”]\r\n";
// $xml_output .="\t\t" . $row[“extra_16”] . “\r\n”;//$xml_output .="\t</extra_16>\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<extra_16 =“0”>\r\n";
//$xml_output .="\t</extra_16>\r\n"; */
}
// returns built area info
if ($row[“covered_area”]) {
// $xml_output .="\t[covered_area ="" . $row[“covered_area”] . “”]\r\n";
$xml_output .="\t\t" . $row[“covered_area”] . “\r\n”;//$xml_output .="\t</covered_area>\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t<covered_area =“0”>\r\n";
//$xml_output .="\t</covered_area>\r\n"; */
}
// returns plot size info
if ($row[“area”]) {
//$xml_output .="\t[area ="" . $row[“area”] . “”]\r\n";
$xml_output .="\t\t" . $row[“area”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<area =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}
/*for ($count = 1; $count = 10; $count += 1) {
//read a picture file URL
while ($row[“parent”] = $[“id”]) {
//$xml_output .="\t[rout ="" . $row[“rout”] . “”]\r\n";
$xml_output .="\t\t" . $row[“rout”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n"; $xml_output .="\t<area =“0”>\r\n" or die (“Invalid code”);
//$xml_output .="\t\r\n";
}
}*/
//}
//echo $xml_output or die (“no output”);
}
$xml_output .="";
//write XML doc
fwrite($file, $xml_output);
//close XML doc
fclose($file);
//print results with hyperlink to link to results
echo “The Kyero XML feed has been updated.\r\n”;
//<a href=\XML"results.xml">View the XML.";
} else {
echo “No Records found”;
}
//print results
//echo $xml_output;
?>
[/php]
I really am pulling my hair out not that I have much left anyway :)), as I can’t understand why something that you can do with a very basic mailmerge and a spreadsheet for a data source should be so complex!
Basically, all I want to do is:
1 Query the database and return the rows indicated in listing 1 from table1, and 2 rows from table2, one of which contains the same id details as table 1. The only complication is that their may be up to 25 records per id in table 2, but I only want 10 of them.
-
Match up 1 instance of the data from table1 and match it to all the records corresponding to that id from table2, so you end up with …, not , , <table 1 fields for id1>,
-
Once all the records are matched up, write to an XML file (that bit works if it gets that far)
Any help, as always, helps you on your way to Paradise.
Dave