Newbie: exporting data, trying to fix broken while..
Posted: Thu Jul 24, 2003 5:49 pm
Hi,
Apologies if these are stupid questions. I'm a newbie to PHP. I'm trying to output data from a MySQL db into a specific format so that the data can be imported into another program.
The problems I'm encountering and would like guidance/help:
1. I need to lookup a text label for one piece of data that comes out as a number. So I retrieve the integer 15 then look up in another table what 15 translates to in English and print that value, not the 15.
2. The code I've hacked together generates 455 records in phpMyAdmin but only 1 record repeated 455 times in the browser. Oddly, each of the 455 entries has the correct category number (but not the correct text label) per #1 above.
3. I'm also trying to use str_replace() to swap bits of data as they're found in the data dump but not all instances are swapped even though visual inspection shows they should have converted.
Here's the code, if someone is interested to help me sort this out. Thank you very much!
Tim
<?php
include("pm_inc.php"); //Contains db connection values
function swap_pm_url($text) {
global $clean;
$clean = str_replace("[/url]", "</a>", $text);
$clean = str_replace("[url=", "<a href=\"", $clean);
$clean = str_replace("]", ">", $clean);
echo $clean;
}
//Need to pull all entries from weblog
//Need to get and translate category information
//Set variables here
global $weblog, $author, $dte, $title, $body, $extbody, $catnum, $catquery, $catsql;
$db = new DB(); //DB() is set in the include file above
$weblog = "weblog"; //Set to appropriate pM weblog name
$author = "Jane Doe"; //Set to appropriate MT author name
//Get all entries from defined $weblog
$sql = "select t_stamp,title,body,custom1,category
from $db_weblog
where weblog = '$weblog'";
$query = new DB_query($db, $sql);
//Set up and assign values in an array
$query->db_fetch_array();
$dte = date("m/d/Y H:i:s", $query->row['t_stamp']);
$title = htmlspecialchars($query->row['title']);
$body = htmlspecialchars($query->row['body']);
$extbody = htmlspecialchars($query->row['custom1']); //Select 2nd pM field to include
$catnum = $query->row['category'];
//Generate results of the array
while($query->db_fetch_array())
{
//Get category name and assign $category that name
$catsql = "select category
from pm_categories
where id = '$catnum'";
$catquery = new DB_query($db, $catsql);
$catquery->db_fetch_array();
$category = $query->row['category'];
//Build and add each weblog entry in MT format
$export = "--------\n";
$export = $export . "TITLE: $title\n";
$export = $export . "AUTHOR: $author\n";
$export = $export . "DATE: $dte\n";
$export = $export . "PRIMARY CATEGORY: $category\n";
$export = $export . "-----\n";
$export = $export . "BODY:\n";
$export = $export . "$body\n";
$export = $export . "-----\n";
$export = $export . "EXTENDED BODY:\n";
$export = $export . "$extbody\n";
$export = $export . "-----\n";
$export = $export . "--------\n";
swap_pm_url($export);
echo $export;
$logfile = @fopen('export.html', 'a');
@fputs($logfile, "$export");
@fclose($logfile);
unset($catquery);
unset($catsql);
}
unset($query);
unset($sql);
?>
Apologies if these are stupid questions. I'm a newbie to PHP. I'm trying to output data from a MySQL db into a specific format so that the data can be imported into another program.
The problems I'm encountering and would like guidance/help:
1. I need to lookup a text label for one piece of data that comes out as a number. So I retrieve the integer 15 then look up in another table what 15 translates to in English and print that value, not the 15.
2. The code I've hacked together generates 455 records in phpMyAdmin but only 1 record repeated 455 times in the browser. Oddly, each of the 455 entries has the correct category number (but not the correct text label) per #1 above.
3. I'm also trying to use str_replace() to swap bits of data as they're found in the data dump but not all instances are swapped even though visual inspection shows they should have converted.
Here's the code, if someone is interested to help me sort this out. Thank you very much!
Tim
<?php
include("pm_inc.php"); //Contains db connection values
function swap_pm_url($text) {
global $clean;
$clean = str_replace("[/url]", "</a>", $text);
$clean = str_replace("[url=", "<a href=\"", $clean);
$clean = str_replace("]", ">", $clean);
echo $clean;
}
//Need to pull all entries from weblog
//Need to get and translate category information
//Set variables here
global $weblog, $author, $dte, $title, $body, $extbody, $catnum, $catquery, $catsql;
$db = new DB(); //DB() is set in the include file above
$weblog = "weblog"; //Set to appropriate pM weblog name
$author = "Jane Doe"; //Set to appropriate MT author name
//Get all entries from defined $weblog
$sql = "select t_stamp,title,body,custom1,category
from $db_weblog
where weblog = '$weblog'";
$query = new DB_query($db, $sql);
//Set up and assign values in an array
$query->db_fetch_array();
$dte = date("m/d/Y H:i:s", $query->row['t_stamp']);
$title = htmlspecialchars($query->row['title']);
$body = htmlspecialchars($query->row['body']);
$extbody = htmlspecialchars($query->row['custom1']); //Select 2nd pM field to include
$catnum = $query->row['category'];
//Generate results of the array
while($query->db_fetch_array())
{
//Get category name and assign $category that name
$catsql = "select category
from pm_categories
where id = '$catnum'";
$catquery = new DB_query($db, $catsql);
$catquery->db_fetch_array();
$category = $query->row['category'];
//Build and add each weblog entry in MT format
$export = "--------\n";
$export = $export . "TITLE: $title\n";
$export = $export . "AUTHOR: $author\n";
$export = $export . "DATE: $dte\n";
$export = $export . "PRIMARY CATEGORY: $category\n";
$export = $export . "-----\n";
$export = $export . "BODY:\n";
$export = $export . "$body\n";
$export = $export . "-----\n";
$export = $export . "EXTENDED BODY:\n";
$export = $export . "$extbody\n";
$export = $export . "-----\n";
$export = $export . "--------\n";
swap_pm_url($export);
echo $export;
$logfile = @fopen('export.html', 'a');
@fputs($logfile, "$export");
@fclose($logfile);
unset($catquery);
unset($catsql);
}
unset($query);
unset($sql);
?>