Export My 68 Data?

[bgordon]

I have the opportunity to do a bulk import of my site data to another company's site. And once I get this working, I will be importing his stuff into my site.

Is there a way to build a query to pull together all the products, product images and product fields for each ad into one result that I could export from mysql admin to excel?

I would expect there to be many colums of data but each row would be all the data for each ad. I could sanitize it and export it for import on his end.

Does this make sense? Has anyone done this before?

Thanks gurus!

[suzkaw]

I have done some importing and exporting before and it really isn't all that fun.

What I normally do is create a query to get the main listing data. Then inside the while loop do any additional queries to get the images, extra fields, etc.

[Lhotch]

If you take a look at viewlisting.php you can see the queries that are made to retrieve all the data for a given ad.

[suzkaw]

Here is what I would probably do:

PHP Code:

<?php
require_once('includes/init.php');
require_once('includes/classes/kernel/Listings.php');
            
            $sSQL="SELECT " .
            "p.id, p.owner, p.title, p.featured, p.section, p.shortDescription, p.display, " .
            "p.description, p.price, p.dateadded, p.expiration, p.pBold, p.pHighlighted, " .
            "u.state, u.city, u.country,u.phone " .
            "FROM ".PREFIX."products AS p " .
                    "LEFT JOIN ".PREFIX."users AS u " .
                            "ON p.owner = u.id " .
                            "WHERE p.expiration > NOW() AND p.display = 'Y' ";
                            
    $result=$db->query($sSQL);
    while ($row=$result->fetch())
    {
        //image
        $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1";
        $iResult=$db->query($imageSQL);
        $irs=$iResult->fetch();
        $row['image']=htmlspecialchars($irs['image']);
        
        
        echo $row['id'].','.$row['title'].','.$row['description'].','.$row['image']."\n";
    }
?>

[Lhotch]

B.Gordon, Erics code is php code, its a tan alone php file that within it contains mysql queries.

With 68C there are many different database tables, the owner info is in one, the ad info in another, extra field data in another and another for images..

The query you use will depend solely on what data you are looking to export.

[bgordon]

I am trying to get the following (I think I started down the right path in my Select above but went astray in all the joins):

User ID
Username
First Name
Last Name
E-Mail Address
Telephone Number

Ad ID
Ad Category Name (so he can match this up with his categories which are slightly different)
Ad Title
Ad Price
Ad Description
Ad Extra Fields (make, model, year, color etc) ...all of them...
Photo name that is ranked highest or ranked 5 if only one photo

This stuff on a single row per ad would be exactly what I need... delimited with a tab (not comma since there are commas in the descriptions). It would be extra cool if I could strip HTML from the description when pulling it but I can do this sanitizing in excel if need be.

Care to take a crack at it? I am willing to donate to the cause! I REALLY need this soon to give to this guy so he can import my data.

[suzkaw]

Create a new php file in your sites and include this:

PHP Code:

<?php
require_once('includes/init.php');
require_once('includes/classes/kernel/Listings.php');
            
            $sSQL="SELECT " .
            "p.id, p.owner, p.title, p.featured, p.section, p.shortDescription, p.display, " .
            "p.description, p.price, p.dateadded, p.expiration, p.pBold, p.pHighlighted, " .
            "u.firstname, u.lastname, u.email, u.state, u.city, u.country,u.phone " .
            "FROM ".PREFIX."products AS p " .
                    "LEFT JOIN ".PREFIX."users AS u " .
                            "ON p.owner = u.id " .
                            "WHERE p.expiration > NOW() AND p.display = 'Y' ";
                            
    $result=$db->query($sSQL);
    while ($row=$result->fetch())
    {
        echo
            $row['id']."\t".
            $row['title']."\t".
            $row['price']."\t".
            $row['description']."\t".
            $row['owner']."\t".
            $row['firstname']."\t".
            $row['lastname']."\t".
            $row['email']."\t".
            $row['phone']."\t";
        
        //extra fields
        $sSQL2 = "SELECT f.fID,sValue FROM ".PREFIX."products_fields AS p, `".PREFIX."fields` AS f WHERE p.pID='".$view."' AND f.fSeen='Y' AND p.fID=f.fID ORDER BY f.fOrder ASC";
        $result2=$db->query($sSQL2);
        $i=0;
        while ($rs=$result2->fetch())
        {
            $sql="SELECT fID,fName,fType FROM `".PREFIX."fields` WHERE fID='".$rs['fID']."' AND fSeen='Y' ORDER BY fOrder ASC";
            $result3=$db->query($sql);
            $rs2=$result3->fetch();
            if(strpos($rs['sValue'], "|"))
            {
                $rs['sValue'] = explode("|", $rs['sValue']);
            }
            
            echo  $rs2['fName']. ": ". $rs['sValue']. "\t";
        }
        
        //image
        $imageSQL = "SELECT image FROM ".PREFIX."prodimages WHERE pid=".$row['id']." ORDER BY rank LIMIT 1";
        $iResult=$db->query($imageSQL);
        $irs=$iResult->fetch();
        $row['image']=htmlspecialchars($irs['image']);
        
        echo $row['image']."\n";
    }
?>

I haven't tested it but it should point you in the right direction. You will not be able to use one query to pull all of this out.

[bgordon]

Ok.. I got it working but it won't return NULL values from the extra fields. This means that my columns don't line up in excel where someone has not inserted a value into each and every field.

How can I get the query to return everything (nulls or empty included) for each record so my columns line up in excel?

extra 1 | extra 2 |||extra5||extra7
instead of

extra 1 | extra 2|extra 5 |extra7

You can see how this totally messes up an export.... we are almost there!!!