How to insert data in table from RETS search query?

Question

I am trying to insert data in a table. I am using a search query and then FetchRow in while condition to fetch the data in row and want to insert this row in a table directly. I have given the structure of table in this link.

The code for insert values in table in while condition is here:

 require_once (ABSPATH . 'wp-admin/includes/upgrade.php');
 $query = "(922=MIAMI), (131=2014-12-16+)";
    $search = $rets->SearchQuery("Property", $class, $query, array("SystemName" => 1, 'Limit' => 1 ));

           if ($rets->NumRows($search) > 0) 
            {
                        $fields_order = $rets->SearchGetFields($search);
                        $this_record = array();
                        while ($record = $rets->FetchRow($search)) 
                        {

                        foreach ($fields_order as $fo)
                        { 
                        echo $this_record[] = $record[$fo];
                        }

                         $comma_fields_order = implode(",", $fields_order);
                         $comma_record = implode(",", $this_record );

                        echo $dfsdf = "INSERT INTO rets_property_2 (".$comma_fields_order.") VALUES (" .$comma_record.")";

                        dbDelta($dfsdf); 

                        }

           }

If I echo the insert query this is :

INSERT INTO rets_property_2 (sysid,1,10,11,13,14,17,19,21,22,25,28,29,30,31,39,45,47,53,54,56,57,59,61,62,63,66,69,71,73,74,75,76,80,92,93,97,98,99,100,102,106,109,110,111,113,114,115,125,126,127,128,129,131,134,136,137,143,144,149,150,157,158,160,161,164,165,167,178,180,181,188,190,193,194,195,206,207,214,223,225,226,227,229,230,232,242,245,246,247,248,250,252,261,263,264,266,267,268,274,275,283,294,295,296,314,315,319,320,321,322,324,326,327,328,329,330,331,332,333,334,335,336,337,338,341,342,347,348,351,352,353,354,355,356,360,361,362,363,364,366,367,368,369,370,372,373,374,375,376,881,886,891,893,894,922,924,1088,1218,1223,1329,1333,1335,1337,1339,1358,1410,1462,1463,1464,1465,1473,1485,1486,1487,1488,1490) VALUES (305578689,Condo/Co-Op/Villa/Townhouse,33131,,,,3003,42,None,,4,,,,Entry Level,Miami Board of Realtors,0,,Cbs Construction,,,Electric Cooling,,Miami-Dade County,,,,ASIA CONDOMINIUM,,,,,,2008-06-12T14:19:47,5,2630,,,,,2,,1,Electric Heat,Condo,9,Yes,ASIA IS BRICKELL KEY ISLAND'S PREMIER LUXURY BLDG. W/ ONLY 123 UNITS, PRIVATE ELEVATORS, SPECTACULAR VIEWS AND MUCH MORE.,,,,,3813,2014-12-23T09:16:55,EWM 09,0648464,6000000,,Daysi Morey,,1,M1242705,,No,,Vacant,EWM Realty International,305-329-7600,,Maximum 20 Lbs,Yes,,0043,,Funding,Yes,5000000,T,EXOTIC LUXURY AND STRIKING DESIGN MAKES ASIA THE # 1 RESIDENCE FOR THE MOST DISCRIMINATING OWNER. SMART TECHNOLOGY, PRIVATE ELEVATORS, FLOOR TO CEILING WINDOWS, BREATHTAKING VIEWS OF BISCAYNE BAY, BAYSIDE, MIAMI RIVER AND MIAMI;S EXHUBERTANT SKYLINE. 12' CIELINGS,MARBLE BATHROOMS, GOURMET CHEF STYLE KITCHEN, LUXE BATH WITH FRENCH VANILLA IMPORTED MARBLE AND MUCH MORE MAKES THIS REMARKABLE RESIDENCE A SANCTUARY NESTLED IN BISCAYNE BAY. ALLOW 24 HOURS NOTICE FOR SHOWINGS.,,,6,6,,,0,,2014-01-24T10:02:22,Active-Available,BRICKELL KEY BLVD.,900,,,4663,Tax Reflects City & County Tax,New Construction,,,42,,0,2007,,None,Yes,Bay Front,2008,Under Construction,Bike/Jog Path,Elevator,0,No Approvals,,,ASIA CONDOMINIUM,,No,,,,Dishwasher,Dryer,Microwave,Electric Range,Refrigerator,Other Equipment/Appliances,Open Balcony,,30,Wood Floors,,Condominium,Elevator,Foyer Entry,Other Interior Features,0,All Amenities,Building Exterior,Cable Tv,1920,365,,,1,1 Assigned Space,2 Or More Spaces,,,0,Other Restrictions,,Elevator Secure,Garage Secured,Lobby Secured,,Condo 5+ Stories,34,Other,Condo,Corner Unit,High Rise,123,,Bay,,900 BRICKELL KEY BLVD. # 3003,,,,0,Miami,Florida,,,http://instatour.propertypanorama.com/instaview/mia/M1242705,2009-02-19T16:00:26,,,,ASIA CONDO UNIT 3003,1573.564,No HOPA,,,2389,No,No,No,No,900 BRICKELL KEY BLVD. 3003,Yes,)

But nothing is being inserted in the table. Where I am wrong? Is there any other method to insert the whole row directly given by fetchrow into the table?

Please show me right direction. I think the problem is with the data type, number and varchar. Then how can I format the insert query where data is coming from the array?

I know my insert query is wrong.

If I do try this

INSERT INTO  `rets_property_2` (  `sysid` ,  `1` ) 
VALUES ( 526252,  'dsfsdfsdf' )

this will work fine. But the question is How can I get the values from array if the value is string then 'somestringvalue' and if the value is integer then only integervalue and then wrapping them in a array?

Please insert the relevant part of your table definition in this question. 10 kB for a table ... ?! — Lorenz Meyer, Dec 27 '14 at 06:27
ok, will this problem be resolved after that? Is my code is ok? — Vijay Lal, Dec 27 '14 at 06:29
Your insert query really doesn't look like SQL ! Where do you have this from ? Did you look at least for 30 seconds at the doc ? I guess not !!! — Lorenz Meyer, Dec 27 '14 at 06:33
No this is not true , I am trying since last two days. But I am not trying on sql query, I was trying on fetchrow to array and expload in php. I saw the basic query of insert from sql docs. I dont have much knowledge about sql. So I have this problem. — Vijay Lal, Dec 27 '14 at 06:39
Where you find me wrong plese give a litte hint. if you are telling about the values in insert table, then this is actually my question how to get the values in '' brakets from array if it is a string ? — Vijay Lal, Dec 27 '14 at 06:43
A basic insert query is like "insert into table (column1, column2) values ( 'string1', 'string2');" — Lorenz Meyer, Dec 27 '14 at 07:43
There's something really strange with your table : How is it possible that there is a column with name `\`1\`` ? This really makes no sense at all. — Lorenz Meyer, Dec 29 '14 at 19:53
should I give you the code by which I created this table? Well I found the solution of my question. Everything is working now for me. I don't know why it is looking strange to you. Now I am working on update the table. Thanks for your strange answers. — Vijay Lal, Dec 30 '14 at 06:44

score 1 · Answer 1 · answered Dec 30 '14 at 11:38

$query = "(922=MIAMI), (131=2014-12-16+)";
    $search = $rets->SearchQuery("Property", $class, $query, array("SystemName" => 1, 'Limit' => 5 ));
     if ($rets->NumRows($search) > 0) {


                $fields_order = $rets->SearchGetFields($search);

                while ($record = $rets->FetchRow($search)) {

                    //var_dump($fields_order);
                        foreach ($fields_order as $fo) { 


                           if( is_numeric($record[$fo])){
                               $valuesd[] = $record[$fo];
                           }else{

                              $onlyconsonants = str_replace("'", "", $record[$fo]);
                               $valuesd[] =  "'".$onlyconsonants."'";
                           }

                        }


                    $comma_fields_order = "`".implode("`,`", $fields_order)."`";
                    $valuestring = implode(",", $valuesd );

                    $wpdb->query("INSERT INTO rets_property_2 (".$comma_fields_order.") VALUES (".$valuestring.")");

                        unset($valuesd);
                        $valuesd = array();




                } /* end while */

        } /* end if of number of row */

score 1 · Answer 2 · answered Jan 05 '15 at 00:28

1

I would suggest using DBName column names instead of SystemName for your database table column names.

I would also suggest using PDO to prepare/execute your statements.

The below example should help you along:

<?php
$sysid = '1234'
$property_1 = '1111 main'
$stmt = $db->prepare("INSERT INTO table_name(`sysid`, `property_1`) VALUES( ?, ?)");
$stmt->execute(array($sysid, $property_1));

answered Jan 05 '15 at 00:28

dj_goku

117
6

Were you able to fix your problem answer from my code example? – dj_goku Jan 14 '15 at 02:30
I didnot use your code. I was using my code below. And now I have upgraded to wordpress. And inserting data directly in wordpress posts using wp_insert_post. And I have another problem that the duplicate posts are being added , I have asked the question related to this here http://stackoverflow.com/questions/27812743/why-duplicate-posts-are-being-added-while-fetching-data-from-rets-server – Vijay Lal Jan 14 '15 at 04:55

How to insert data in table from RETS search query?

2 Answers2