SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2005
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Problem with array and fetching value

    Ok I am writing a script to "dump" my MySQL DB into XML format. i know there are programs out there that do this, but i have tried, and they do not work for what I need.

    So here is the problem. I can access the db, even retrieve all of the column names, types, etc. However, i am trying for the life of me to extract the row "data" and can not do it. I am using an array to go through the columns and print, but can't seem to find the solution for the value. Below you will see the code, as well as the results. Any help would be greatly appreciated:

    CODE
    ******************
    [<?php
    // database constants
    // make sure the information is correct
    define("DB_SERVER", "servername");
    define("DB_USER", "username");
    define("DB_PASS", "password");
    define("DB_NAME", "databasename");

    // connection to the database
    $dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS)
    or die("Unable to connect to MySQL");

    // select a database to work with
    $selected = mysql_select_db(DB_NAME, $dbhandle)
    or die("Could not select examples");

    // return all available tables
    $result_tbl = mysql_query( "SHOW TABLES FROM ".DB_NAME, $dbhandle );

    $tables = array();
    while ($row = mysql_fetch_row($result_tbl)) {
    $tables[] = $row[0];
    }

    $output = "<?xml version=\"1.0\" encoding=\"iso-8859-1\" ?>\n";
    $output .= "<schema>";

    // iterate over each table and return the fields for each table
    foreach ( $tables as $table ) {
    $output .= "<table name=\"$table\">";
    $result_fld = mysql_query( "SHOW FIELDS FROM ".$table, $dbhandle );

    while( $row1 = mysql_fetch_array($result_fld) ) {
    $output .= "<field name=\"$row1[0]\" type=\"$row1[1]\"";
    $output .= ($row1[3] == "PRI") ? " primary_key=\"yes\" />" : " />";
    }

    $output .= "</table>";
    }

    $output .= "</schema>";

    // tell the browser what kind of file is come in
    header("Content-type: text/xml");
    // print out XML that describes the schema
    echo $output;

    // close the connection
    mysql_close($dbhandle);
    ?>]


    AND THE RESULTS:
    **********************************

    <schema>

    <table name="abusereasons">
    <field name="abusereasonsid" type="int(11) unsigned" primary_key="yes"/>
    <field name="title" type="varchar(20)"/>
    <field name="description" type="text"/>
    <field name="emailsubject" type="text"/>
    <field name="emailbody" type="text"/>
    </table>

    <table name="anchor">
    <field name="anchorid" type="int(24) unsigned" primary_key="yes"/>
    <field name="tablename" type="enum('section','topic')"/>
    <field name="tableid" type="int(24) unsigned"/>
    <field name="storyid" type="int(24) unsigned"/>
    <field name="pagepos" type="tinyint(3) unsigned"/>
    </table>
    </schema>

  2. #2
    SitePoint Evangelist optl's Avatar
    Join Date
    Oct 2004
    Location
    Washington DC
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want the data you need to select it. All I see is you fetching the table and field names.
    Code:
    SELECT * FROM tablename
    Then fetch the results from the query with a loop and you will iterate through each row in the table.
    For the phrase "Bethesda home architect", my clients
    websites occupy 6 of the first 8 results
    on the 1st page of Google. My Secret SEO Strategy Revealed

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2005
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Opt...overlooked that aspect. I have corrected the code, however I am having another problem....this time with my nested while loops. I know I am overlooking something here again, and should be an easy fix...but for the life of me I can not get it....my eyes are going buggy. Anyway, the problem is that the inner loop is echoing the array values, but the outer is not..it echos the first array value and that is it.

    Here is the code
    **********************
    [// output basic header info and beginning of xml tags
    $output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
    $output .= "<Asset>";
    $output .= "<AssetType>";
    $output .= "File";

    // output basic header info and beginning of xml tags
    $result_fld = mysql_query( "Select * FROM table ORDER BY id", $dbhandle );
    $result_show = mysql_query( "SHOW FIELDS FROM table", $dbhandle );

    //count the number of records in the table
    $story_total_rows = mysql_num_rows($result_fld);

    //build the nested loop to pull in values for each field and structure the xml output
    for( $i=0; $i < $story_total_rows; $i++) {
    while( $row = mysql_fetch_row($result_fld) ) {
    while( $row1 = mysql_fetch_row($result_show) ) {
    $output .= "<Field>";
    $output .= "<Name>" .$row1[0];
    $output .= "</Name>";
    $output .= "<Values>";
    $output .= "<Value>" .$row[0] ."</Value>";
    $output .= "</Values>";
    $output .= "</Field>";
    }
    }
    }

    $output .= "</AssetType>";
    $output .= "</Asset>";

    // tell the browser what kind of file is come in
    header("Content-type: text/xml");
    // print out XML that describes the schema
    echo $output;]

    ********************************
    End of Code

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2005
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok..new problem...i got it so it is echoing the name and the field value, however, it is only doing this for the first array...that is it. This script is driving me up the wall. Can a fresh pair of eyes look at it and tell me why the output is only for the first field name and value. Thanks.

    Here is the Code Snippet
    *****************************

    [// output basic header info and beginning of xml tags
    $output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
    $output .= "<Asset>";
    $output .= "<AssetType>";
    $output .= "File";

    // output basic header info and beginning of xml tags
    $result_fld = mysql_query( "Select * FROM file ORDER BY fileid", $dbhandle );
    $result_show = mysql_query( "SHOW FIELDS FROM file", $dbhandle );


    //count the number of records in the table
    $story_total_rows = mysql_num_rows($result_fld);

    //build the loop to pull in values for each field
    for( $x=0; $x < $story_total_rows; $x++) {
    while( $row = mysql_fetch_row($result_fld) ) {
    $i=0;
    while( $row1 = mysql_fetch_array($result_show) ) {
    $output .= "<Field>";
    $output .= "<Name>" .$row1[0];
    $output .= "</Name>";
    $output .= "<Values>";
    $output .= "<Value>" .$row[$i] ."</Value>";
    $output .= "</Values>";
    $output .= "</Field>";
    $i++;
    }
    }
    }

    $output .= "</AssetType>";
    $output .= "</Asset>";

    // tell the browser what kind of file is come in
    header("Content-type: text/xml");
    // print out XML that describes the schema
    echo $output;

    // close the connection
    mysql_close($dbhandle);
    ?>
    ]

    *****************************
    End of Code
    Last edited by nmpku; Jul 6, 2007 at 10:44.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •