fetchRow

I am programming a function to transfer data from one database to another. I am using pear (and mysql is database).

The problem is this:
I don't know the fields are before they are transferred. So before I can run a SQL UPDATE query I must get the fields from another table.

I thought about a nested query at first but wasn't sure how to formulate it so I was trying a workaround:

// MySQL specific - change if another database - CONCAT_WS
$mysql="SELECT CONCAT_WS( 'X', sid, gid, qid ) FROM survey_data_questions WHERE sid='".$surveyid."'";

$result = $db_object->query($mysql);

$counter = 0;
$sqld = "SELECT";
while ($row = $result->fetchRow(DB_FETCHMODE_ORDERED)) {
$counter = $counter++;
$field = $row->$counter;
$sqld = $sqld." ".$field;
}

---
Once I have all the fields in select query (yes, I know I still need to put in commas as separators), I wanted to use the same/similar string to update the table with the actual data. (e.g. $sql = "UPDATE ".$newtable." SET ".$field1." = ".$field1data.", " ...) except generated dynamically.

I don't seem to be able to get the DB_FETCHMODE_ORDERED working correctly with a counter incrementing it but don't know how to get the data out of that array any other way.

Any suggestions would be greatly appreciated.

Additionally, if there is a better way to do it rather than having all these separate queries (nested), that would save me more time and reduce server resources.

Thank you!

After consulting some reference materials I found the correct syntax for getting the information out of the $row array:

$row[$counter]

My counter, however is still not incrementing. (have even changed it to $counter = $counter + 1;) but I still only have the first record. (where $counter=0)

Any thoughts?

First - have you considered using tableInfo() to get your column names?

As for a better way - maybe use mysqldump and mysqlimport - See http://dev.mysql.com/doc/refman/5.0/en/upgrading-to-arch.html

why use PHP if the DB itself can be used to do it.

please post your new code for futher help on that line.

Thank you for the information about the tableinfo function. I may use it in the future. (although I think I'll have to massage the data a little...)

I was able to use the following code to view the table information:

$info = $db_object->tableInfo($surveydatatable);
print_r($info);

---
Regarding the "why" for what I'm doing. I don't want to get married to any specific technology. (that's why I'm using pear in the first place.) You can see I already use $mysql variables in my code for non-portable sections. (specific to mysql) Moreover from the pear website:

tableInfo() is not portable because not all drivers have this method, many DBMS's are unable to determine table names from query results and the metadata returned by each database system varies dramatically.

Only fbsql and mysql provide full information from queries.

---

Regardless I was wrong in one thing - (I had the table fields listed in another table) Here is the code I plan to use to extract the field info... I suppose the $counter variable I was using was reserved or something. The "$i" seems to work quite well.

$result = $db_object->query($mysql);

$i = 0;
while ($row = $result->fetchRow(DB_FETCHMODE_ORDERED)) {
$sqld = $sqld.$row[$i].", ";
$i = $i++;
}

echo $sqld;

Basically I loop through the results and append them to a string. Then I can use the string to pull the data and insert the data into table number 2.

It seems my solution didn't work because table I was querying (a list of fields in another table) did not have all the fields. I reverted back to the tableinfo function and it worked. (Still dependent on MySQL, however) Anyway I have the function below if anyone wants to find the field name of a table before querying from that table. (Use whatever db connect method you wish)

You may wish to know why if a simple "SELECT * ..." would suffice later. With this loop, you can weed out the fields you don't want to transfer or even format them however you wish. (In the loop just put an if != $variable statement) This really helps if you don't know what fields you want to transfer exactly but you know how they should be formatted.

function gettablefields($dbname,$table){
// Get field names

require('admin/dbaccess.php'); // partial database connect script.
$datasource = $datasource."/".$dbname;
$db_object = DB::connect($datasource, TRUE);
if(DB::isError($db_object)) {
die($db_object->getMessage());
}

$info = $db_object->tableInfo($table);

foreach($info as $fieldname) {
$sqld = $sqld.", ".$fieldname['name'];
}
}
return $sqld;
}


Hopefully I haven't confused people more than I helped!

Duane

Another question...

I now have a $sql string (SELECT a, b, c, etc. FROM ...)

I pass the string to a function and want to put the results in another string, delimited by commas. I was trying to use the same type of format as before but this time I have no luck. I don't really have enough experience with the pear technology to get it going. Can someone tell me what the code should be?

function a($sql){

$result2 = $db_object->query($sql);
$i = 0;

while ($row = $result2->fetchRow(DB_FETCHMODE_ORDERED)) {
echo $row[$i];
cleanfordb($row[$i]);
$sqld1 = $sqld1.", '".$row[$i]."'";
$i = $i + 1;
}
return $sqld1; //This should be the result, delimited by commas.
}

Thanks for any help someone can provide.