?
Solved

PHP script inserts data into table but fields are not getting inserted into correct fields and some are blank..what to do??

Posted on 2006-03-28
17
Medium Priority
?
311 Views
Last Modified: 2013-12-12
I have a PHP script that is inserting data into my 'rentals' table however the data is not going into the correct fields.  I will post code and rentals table structure so you can see them both.  One specific question I have is what does the '%s mean?? is it just a generic variable name.

  //add property
$insertSQL = sprintf("INSERT INTO rentals
                        (`mid`,`countyID`,`garage`,`levels`,`sellerTitle`,`sellerName`,`sellerPhone`,
                        `Notes`, `sellerEmail`,`price`, `expires`, `showCities`, cid, title, pdate, deposit,  
                          `availdate`, `leaseterm`, sold, description, address,  city,
                         `state`, zip,  featured, active, bed, bath, subdiv, schoold, sqft, pets, petdeposit,
                          smoking, `lat`, `long`)
              VALUES
                    (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, `%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
             GetSQLValueString($_POST['mid'],"int"),
             GetSQLValueString($_POST['countyID'],"int"),
             GetSQLValueString($_POST['testimonial'], "text"),  
             GetSQLValueString($_POST['garage'], "text"),
             GetSQLValueString($_POST['levels'], "text"),
             GetSQLValueString($_POST['sellerTitle'],"text"),
             GetSQLValueString($_POST['sellerName'], "text"),
             GetSQLValueString($_POST['sellerPhone'],"text"),
             GetSQLValueString($_POST['Notes'],"text"),
             GetSQLValueString($_POST['sellerEmail'],"text"),
             GetSQLValueString($_POST['price'], "double"),
             GetSQLValueString($_POST['cat'], "int"),
                       GetSQLValueString($_POST['title'], "text"),
                       GetSQLValueString(date("Y-m-d"), "date"),
             GetSQLValueString($_POST['deposit'],"text"),
             GetSQLValueString($_POST['availdate'], "text"),
             GetSQLValueString($_POST['leaseterm'], "text"),
                       GetSQLValueString($_POST['Sold'], "text"),
                       GetSQLValueString($_POST['description'], "text"),
                       GetSQLValueString($_POST['address'], "text"),
                       GetSQLValueString($_POST['city'], "text"),
                       GetSQLValueString($_POST['state'], "text"),
                       GetSQLValueString($_POST['zip'], "text"),         
                       GetSQLValueString($_POST['featured'], "text"),
                       GetSQLValueString("Yes", "text"),
                       GetSQLValueString($_POST['bed'], "text"),
                       GetSQLValueString($_POST['bath'], "text"),
                GetSQLValueString($_POST['year'], "int"),
                       GetSQLValueString($_POST['subdiv'], "text"),
                       GetSQLValueString($_POST['schoold'], "text"),
                       GetSQLValueString($_POST['sqft'], "double"),
             GetSQLValueString($_POST['pets'], "text"),
             GetSQLValueString($_POST['petdeposit'], "text"),
             GetSQLValueString($_POST['smoking'], "text"),
                       GetSQLValueString($address['lat'], "text"),
                       GetSQLValueString($address['long'], "text"));

Here is the 'rentals' table structure.

TABLE `rentals` (
  `id` bigint(20) NOT NULL auto_increment,
  `cid` int(11) default NULL,
  `title` varchar(50) default NULL,
  `price` decimal(6,2) default NULL,
  `pdate` date default NULL,
  `sold` varchar(4) default NULL,
  `description` text,
  `address` varchar(50) default NULL,
  `city` varchar(45) default NULL,
  `state` varchar(45) default NULL,
  `zip` varchar(50) default NULL,
  `featured` varchar(4) default NULL,
  `active` varchar(5) default NULL,
  `hits` int(11) default NULL,
  `bed` varchar(10) default NULL,
  `bath` varchar(10) default NULL,
  `subdiv` varchar(60) default NULL,
  `schoold` varchar(60) default NULL,
  `sqft` double default NULL,
  `lat` varchar(40) default NULL,
  `long` varchar(40) default NULL,
  `showCities` text NOT NULL,
  `sellerTitle` varchar(100) default NULL,
  `sellerName` varchar(100) default NULL,
  `sellerPhone` varchar(100) default NULL,
  `sellerEmail` varchar(100) default NULL,
  `mid` int(11) default NULL,
  `Notes` text,
  `expires` date default NULL,
  `garage` varchar(4) default NULL,
  `levels` varchar(100) default NULL,
  `countyID` int(11) default NULL,
  `availdate` varchar(12) default NULL,
  `deposit` decimal(6,2) default NULL,
  `pets` varchar(15) default NULL,
  `petdeposit` decimal(6,2) default NULL,
  `leaseterm` varchar(10) default NULL,
  `smoking` varchar(4) default NULL,

should I re-arrange the order so that they are all in sink??  I have tried changing the number of '%s' before the DATE_ADD(NOW(), INTERVAL ".$credit['days']." DAY), '".$citySTR."',  but that just screws it up more.  thanks for taking a look!!
0
Comment
Question by:mnoel76
  • 9
  • 5
  • 3
17 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16319008
Before I look further, is that a typo in the %s line?

You have a backtick in it...

(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, `%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

Try

(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",


Oh.

Can you also try adding an

echo $insertSQL;

after the this and see what the value is. You may see something a little more obvious.



The %s means a string. Depending upon the output of the GetSQLValueString, it may or may not have quotes around it. I would be expecting the quotes.

This is why the additional echo statement is required.

0
 

Author Comment

by:mnoel76
ID: 16325740
RQuadling

thaks for your reply...I have been away from the computer.  I will give it a try.

Oh and the `%s, is due to some code that didn't get copied in.  this is what it should look like.

VALUES
  (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, DATE_ADD(NOW(), INTERVAL ".$credit['days']." DAY),
                     '".$citySTR."', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",

I would think the echo statement will make things a lot easier
0
 

Author Comment

by:mnoel76
ID: 16326607
Rquaudling,

everything works except for the expires and showcities.  this is the codeI have now.

$insertSQL = sprintf("INSERT INTO rentals
                        (`mid`,`countyID`,`garage`,`levels`,`sellerTitle`,`sellerName`,`sellerPhone`,
                        `Notes`, `sellerEmail`,`price`, `expires`, `showCities`, cid, title, pdate, deposit,  
                          `availdate`, `leaseterm`, leased, description, address,  city,
                         `state`, zip,  featured, active, bed, bath, subdiv, schoold, sqft, pets, petdeposit,
                          smoking, `lat`, `long`)
              VALUES
                    (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, DATE_ADD(NOW(), INTERVAL ".$credit['days']." DAY),
                     '".$citySTR."', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                                 GetSQLValueString($_POST['mid'],"int"),
                                 GetSQLValueString($_POST['countyID'],"int"),  
                                    GetSQLValueString($_POST['garage'], "text"),
                                 GetSQLValueString($_POST['levels'], "text"),
                                 GetSQLValueString($_POST['sellerTitle'],"text"),
                                 GetSQLValueString($_POST['sellerName'], "text"),
                                 GetSQLValueString($_POST['sellerPhone'],"text"),
                                 GetSQLValueString($_POST['Notes'],"text"),
                                 GetSQLValueString($_POST['sellerEmail'],"text"),
                                 GetSQLValueString($_POST['price'], "double"),
                                 GetSQLValueString($_POST['cat'], "int"),
                       GetSQLValueString($_POST['title'], "text"),
                       GetSQLValueString(date("Y-m-d"), "date"),
                   GetSQLValueString($_POST['deposit'],"text"),
                   GetSQLValueString($_POST['availdate'], "text"),
                     GetSQLValueString($_POST['leaseterm'], "text"),
                       GetSQLValueString($_POST['leased'], "text"),
                       GetSQLValueString($_POST['description'], "text"),
                       GetSQLValueString($_POST['address'], "text"),
                       GetSQLValueString($_POST['city'], "text"),
                       GetSQLValueString($_POST['state'], "text"),
                       GetSQLValueString($_POST['zip'], "text"),         
                       GetSQLValueString($_POST['featured'], "text"),
                       GetSQLValueString("Yes", "text"),
                       GetSQLValueString($_POST['bed'], "text"),
                       GetSQLValueString($_POST['bath'], "text"),
                       GetSQLValueString($_POST['subdiv'], "text"),
                       GetSQLValueString($_POST['schoold'], "text"),
                       GetSQLValueString($_POST['sqft'], "double"),
                   GetSQLValueString($_POST['pets'], "text"),
                   GetSQLValueString($_POST['petdeposit'], "text"),
                   GetSQLValueString($_POST['smoking'], "text"),
                       GetSQLValueString($address['lat'], "text"),
                       GetSQLValueString($address['long'], "text"));                              
                       $newID = mysql_insert_id();

for some reason the showcities is grabing the expire field data.  How can I fix this??  I have tried moveing some things around and it but it just changes the values.  this is what the echo statement produces

NSERT INTO rentals (`mid`,`countyID`,`garage`,`levels`,`sellerTitle`,`sellerName`,`sellerPhone`, `Notes`, `sellerEmail`,`price`, `expires`, `showCities`, cid, title, pdate, deposit, `availdate`, `leaseterm`, leased, description, address, city, `state`, zip, featured, active, bed, bath, subdiv, schoold, sqft, pets, petdeposit, smoking, `lat`, `long`) VALUES (3, 1, 'Yes', 'levels', 'Company name', 'Contact Name', 'Contact Phone', ' personal notes', 'Contact email', '0', 1, DATE_ADD(NOW(), INTERVAL 30 DAY), '', 'Property Title', '2006-03-29', 'deposit', 'date avail', 'lease term', NULL, 'description', 'Address', 'Bennett', 'CO', '80202', 'No', 'Yes', 'bed', 'bath', 'subdivision', 'school', '0', 'No Pets', 'pdeposit', 'Yes', NULL, NULL)

any ides please let me know!! thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:Rob_Jeffrey
ID: 16329386
What value do you have for $citySTR?  If it is returning '' in the query - that means it is empty at that point.
Could you add a echo $citySTR; before the echo $query - to see what's in there?
I don't know about the dateadd without testing it - but I think that it only works on the date - not a timestamp lke NOW() returns.
try:

DATE_ADD( CURDATE(), INTERVAL " . $credit['days'] . " DAY)

Other than that - it looks fine.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16330483
$insertSQL = sprintf
      (
"INSERT INTO
      rentals
            (
            mid,
            countyID,
            garage,
            levels,
            sellerTitle,
            sellerName,
            sellerPhone,
            Notes,
            sellerEmail,
            price,
            expires,
            showCities,
            cid,
            title,
            pdate,
            deposit,
            availdate,
            leaseterm,
            leased,
            description,
            address,
            city,
            state,
            zip,
            featured,
            active,
            bed,
            bath,
            subdiv,
            schoold,
            sqft,
            pets,
            petdeposit,
            smoking,
            lat,
            long
            )
      VALUES
            (
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            DATE_ADD(NOW(), INTERVAL {$credit['days']} DAY),
            {$citySTR},
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s,
            %s
            )",
      GetSQLValueString($_POST['mid'],"int"),
      GetSQLValueString($_POST['countyID'],"int"),
      GetSQLValueString($_POST['garage'], "text"),
      GetSQLValueString($_POST['levels'], "text"),
      GetSQLValueString($_POST['sellerTitle'],"text"),
      GetSQLValueString($_POST['sellerName'], "text"),
      GetSQLValueString($_POST['sellerPhone'],"text"),
      GetSQLValueString($_POST['Notes'],"text"),
      GetSQLValueString($_POST['sellerEmail'],"text"),
      GetSQLValueString($_POST['price'], "double"),
      GetSQLValueString($_POST['cat'], "int"),
      GetSQLValueString($_POST['title'], "text"),
      GetSQLValueString(date("Y-m-d"), "date"),
      GetSQLValueString($_POST['deposit'],"text"),
      GetSQLValueString($_POST['availdate'], "text"),
      GetSQLValueString($_POST['leaseterm'], "text"),
      GetSQLValueString($_POST['leased'], "text"),
      GetSQLValueString($_POST['description'], "text"),
      GetSQLValueString($_POST['address'], "text"),
      GetSQLValueString($_POST['city'], "text"),
      GetSQLValueString($_POST['state'], "text"),
      GetSQLValueString($_POST['zip'], "text"),
      GetSQLValueString($_POST['featured'], "text"),
      GetSQLValueString("Yes", "text"),
      GetSQLValueString($_POST['bed'], "text"),
      GetSQLValueString($_POST['bath'], "text"),
      GetSQLValueString($_POST['subdiv'], "text"),
      GetSQLValueString($_POST['schoold'], "text"),
      GetSQLValueString($_POST['sqft'], "double"),
      GetSQLValueString($_POST['pets'], "text"),
      GetSQLValueString($_POST['petdeposit'], "text"),
      GetSQLValueString($_POST['smoking'], "text"),
      GetSQLValueString($address['lat'], "text"),
      GetSQLValueString($address['long'], "text")
      );

Personally, I would consider writing a small function to construct this statement as it is too big.

Maybe ...

<?php
$as_SQL_parts = array
      (
      'columns' => '',
      'values' => '',
      );

function add_to_SQL(array &$as_SQL_parts, $s_column, $s_value)
      {
      $as_SQL_parts['columns'] .= "`$s_column`,";
      $as_SQL_parts['values'] .= "$s_value,";
      }

add_to_SQL($as_SQL_parts, 'active', GetSQLValueString('Yes', 'text'));
add_to_SQL($as_SQL_parts, 'address', GetSQLValueString($_POST['address'], 'text'));
add_to_SQL($as_SQL_parts, 'availdate', GetSQLValueString($_POST['availdate'], 'text'));
add_to_SQL($as_SQL_parts, 'bath', GetSQLValueString($_POST['bath'], 'text'));
add_to_SQL($as_SQL_parts, 'bed', GetSQLValueString($_POST['bed'], 'text'));
add_to_SQL($as_SQL_parts, 'cid', GetSQLValueString($_POST['cat'], 'int'));
add_to_SQL($as_SQL_parts, 'city', GetSQLValueString($_POST['city'], 'text'));
add_to_SQL($as_SQL_parts, 'countyID', GetSQLValueString($_POST['countyID'],'int'));
add_to_SQL($as_SQL_parts, 'deposit', GetSQLValueString($_POST['deposit'],'text'));
add_to_SQL($as_SQL_parts, 'description', GetSQLValueString($_POST['description'], 'text'));
add_to_SQL($as_SQL_parts, 'expires,', "DATE_ADD(NOW(), INTERVAL {$credit['days']} DAY)");
add_to_SQL($as_SQL_parts, 'featured', GetSQLValueString($_POST['featured'], 'text'));
add_to_SQL($as_SQL_parts, 'garage', GetSQLValueString($_POST['garage'], 'text'));
add_to_SQL($as_SQL_parts, 'lat', GetSQLValueString($address['lat'], 'text'));
add_to_SQL($as_SQL_parts, 'leased', GetSQLValueString($_POST['leased'], 'text'));
add_to_SQL($as_SQL_parts, 'leaseterm', GetSQLValueString($_POST['leaseterm'], 'text'));
add_to_SQL($as_SQL_parts, 'levels', GetSQLValueString($_POST['levels'], 'text'));
add_to_SQL($as_SQL_parts, 'long', GetSQLValueString($address['long'], 'text'));
add_to_SQL($as_SQL_parts, 'mid', GetSQLValueString($_POST['mid'],'int'));
add_to_SQL($as_SQL_parts, 'Notes', GetSQLValueString($_POST['Notes'],'text'));
add_to_SQL($as_SQL_parts, 'pdate', GetSQLValueString(date('Y-m-d'), 'date'));
add_to_SQL($as_SQL_parts, 'petdeposit', GetSQLValueString($_POST['petdeposit'], 'text'));
add_to_SQL($as_SQL_parts, 'pets', GetSQLValueString($_POST['pets'], 'text'));
add_to_SQL($as_SQL_parts, 'price', GetSQLValueString($_POST['price'], 'double'));
add_to_SQL($as_SQL_parts, 'schoold', GetSQLValueString($_POST['schoold'], 'text'));
add_to_SQL($as_SQL_parts, 'sellerEmail', GetSQLValueString($_POST['sellerEmail'],'text'));
add_to_SQL($as_SQL_parts, 'sellerName', GetSQLValueString($_POST['sellerName'], 'text'));
add_to_SQL($as_SQL_parts, 'sellerPhone', GetSQLValueString($_POST['sellerPhone'],'text'));
add_to_SQL($as_SQL_parts, 'sellerTitle', GetSQLValueString($_POST['sellerTitle'],'text'));
add_to_SQL($as_SQL_parts, 'showCities,', $citySTR);
add_to_SQL($as_SQL_parts, 'smoking', GetSQLValueString($_POST['smoking'], 'text'));
add_to_SQL($as_SQL_parts, 'sqft', GetSQLValueString($_POST['sqft'], 'double'));
add_to_SQL($as_SQL_parts, 'state', GetSQLValueString($_POST['state'], 'text'));
add_to_SQL($as_SQL_parts, 'subdiv', GetSQLValueString($_POST['subdiv'], 'text'));
add_to_SQL($as_SQL_parts, 'title', GetSQLValueString($_POST['title'], 'text'));
add_to_SQL($as_SQL_parts, 'zip', GetSQLValueString($_POST['zip'], 'text'));

rtrim($as_SQL_parts['columns'], ',');
rtrim($as_SQL_parts['values'], ',');


$insertSQL = "INSERT INTO rentals ({$as_SQL_parts['columns']}) VALUES ({$as_SQL_parts['values']})";

?>

I would probably go even further and define the a population array structure...

<?php
$as_SQL_parts = array
      (
      'columns' => '',
      'values' => '',
      );

function add_to_SQL(array &$as_SQL_parts, $s_column, $s_value)
      {
      $as_SQL_parts['columns'] .= "`$s_column`,";
      $as_SQL_parts['values'] .= "$s_value,";
      }

$as_SQL_parts_definition = array
      (
      'text' => array
            (
            'address' => NULL,
            'availdate' => NULL,
            'bath' => NULL,
            'bed' => NULL,
            'city' => NULL,
            'deposit' => NULL,
            'description' => NULL,
            'featured' => NULL,
            'garage' => NULL,
            'lat' => $address['lat'],
            'leased' => NULL,
            'leaseterm' => NULL,
            'levels' => NULL,
            'long' => $address['long'],
            'Notes' => NULL,
            'petdeposit' => NULL,
            'pets' => NULL,
            'school' => NULL,
            'sellerEmail' => NULL,
            'sellerName' => NULL,
            'sellerPhone' => NULL,
            'sellerTitle' => NULL,
            'smoking' => NULL,
            'state' => NULL,
            'subdiv' => NULL,
            'title' => NULL,
            'zip' => NULL,
            ),
      'int'      => array
            (
            'cid' => 'cat',
            'countyid' => NULL,
            'mid' => NULL,
            ),
      'date' => array
            (
            'pdate' => date('Y-m-d'),
            ),
      'double' => array
            (
            'price' => NULL,
            'sqft' => NULL,
            ),
      'literal' => array
            (
            'active' => 'Yes',
            'expires', "DATE_ADD(NOW(), INTERVAL {$credit['days']} DAY)",
            'showCities' => $citySTR,
            ),
      );

foreach($as_SQL_parts_definition as $s_SQL_type => $as_SQL_columns)
      {
      foreach($as_SQL_columns as $s_SQL_column => $s_value_override)
            {
            switch($s_SQL_type)
                  {
                  case 'literal' :
                        add_to_SQL($as_SQL_parts, $as_SQL_colum, $s_value_override);
                        break;
                  case 'text' :
                  case 'int' :
                  case 'double' :
                  case 'date' :
                        if (!is_null($s_value_override))
                              {
                              $s_new_value = GetSQLValueString($s_value_override, $s_SQL_type);
                              }
                        else
                              {
                              $s_new_value = GetSQLValueString($_POST[$s_SQL_column], $s_SQL_type);
                              }
                        add_to_SQL($as_SQL_parts, $as_SQL_colum, $s_new_value);
                        break;
                  default :
                  }
            }
      }

rtrim($as_SQL_parts['columns'], ',');
rtrim($as_SQL_parts['values'], ',');
$insertSQL = "INSERT INTO rentals ({$as_SQL_parts['columns']}) VALUES ({$as_SQL_parts['values']})";

?>

NOTE: This is untested code.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16330525
Some testing and typos.

<?php
$as_SQL_parts = array
      (
      'columns' => '',
      'values' => '',
      );

function add_to_SQL(array &$as_SQL_parts, $s_column, $s_value)
      {
      $as_SQL_parts['columns'] .= "`$s_column`,";
      $as_SQL_parts['values'] .= "$s_value,";
      }

$as_SQL_parts_definition = array
      (
      'text' => array
            (
            'address' => NULL,
            'availdate' => NULL,
            'bath' => NULL,
            'bed' => NULL,
            'city' => NULL,
            'deposit' => NULL,
            'description' => NULL,
            'featured' => NULL,
            'garage' => NULL,
            'lat' => $address['lat'],
            'leased' => NULL,
            'leaseterm' => NULL,
            'levels' => NULL,
            'long' => $address['long'],
            'Notes' => NULL,
            'petdeposit' => NULL,
            'pets' => NULL,
            'school' => NULL,
            'sellerEmail' => NULL,
            'sellerName' => NULL,
            'sellerPhone' => NULL,
            'sellerTitle' => NULL,
            'smoking' => NULL,
            'state' => NULL,
            'subdiv' => NULL,
            'title' => NULL,
            'zip' => NULL,
            ),
      'int'      => array
            (
            'cid' => 'cat',
            'countyid' => NULL,
            'mid' => NULL,
            ),
      'date' => array
            (
            'pdate' => date('Y-m-d'),
            ),
      'double' => array
            (
            'price' => NULL,
            'sqft' => NULL,
            ),
      'literal' => array
            (
            'active' => 'Yes',
            'expires' => "DATE_ADD(NOW(), INTERVAL {$credit['days']} DAY)",
            'showCities' => $citySTR,
            ),
      );

foreach($as_SQL_parts_definition as $s_SQL_type => $as_SQL_columns)
      {
      foreach($as_SQL_columns as $s_SQL_column => $s_value_override)
            {
            switch($s_SQL_type)
                  {
                  case 'literal' :
                        add_to_SQL($as_SQL_parts, $s_SQL_column, GetSQLValueString($s_value_override, 'text'));
                        break;
                  case 'text' :
                  case 'int' :
                  case 'double' :
                  case 'date' :
                        if (!is_null($s_value_override))
                              {
                              $s_new_value = GetSQLValueString($s_value_override, $s_SQL_type);
                              }
                        else
                              {
                              $s_new_value = GetSQLValueString($_POST[$s_SQL_column], $s_SQL_type);
                              }
                        add_to_SQL($as_SQL_parts, $s_SQL_column, $s_new_value);
                        break;
                  default :
                  }
            }
      }

$as_SQL_parts['columns'] = rtrim($as_SQL_parts['columns'], ',');
$as_SQL_parts['values'] = rtrim($as_SQL_parts['values'], ',');
$insertSQL = "INSERT INTO rentals ({$as_SQL_parts['columns']}) VALUES ({$as_SQL_parts['values']})";
echo $insertSQL;
?>
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16330530
Final set of fixes for the time being.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16330531
<?php
$as_SQL_parts = array
      (
      'columns' => '',
      'values' => '',
      );

function add_to_SQL(array &$as_SQL_parts, $s_column, $s_value)
      {
      $as_SQL_parts['columns'] .= "`$s_column`,";
      $as_SQL_parts['values'] .= "$s_value,";
      }

$as_SQL_parts_definition = array
      (
      'text' => array
            (
            'active' => 'Yes',
            'address' => NULL,
            'availdate' => NULL,
            'bath' => NULL,
            'bed' => NULL,
            'city' => NULL,
            'deposit' => NULL,
            'description' => NULL,
            'expires' => "DATE_ADD(NOW(), INTERVAL {$credit['days']} DAY)",
            'featured' => NULL,
            'garage' => NULL,
            'lat' => $address['lat'],
            'leased' => NULL,
            'leaseterm' => NULL,
            'levels' => NULL,
            'long' => $address['long'],
            'Notes' => NULL,
            'petdeposit' => NULL,
            'pets' => NULL,
            'school' => NULL,
            'sellerEmail' => NULL,
            'sellerName' => NULL,
            'sellerPhone' => NULL,
            'sellerTitle' => NULL,
            'showCities' => $citySTR,
            'smoking' => NULL,
            'state' => NULL,
            'subdiv' => NULL,
            'title' => NULL,
            'zip' => NULL,
            ),
      'int'      => array
            (
            'cid' => $_POST['cat'],
            'countyid' => NULL,
            'mid' => NULL,
            ),
      'date' => array
            (
            'pdate' => date('Y-m-d'),
            ),
      'double' => array
            (
            'price' => NULL,
            'sqft' => NULL,
            ),
      );

foreach($as_SQL_parts_definition as $s_SQL_type => $as_SQL_columns)
      {
      foreach($as_SQL_columns as $s_SQL_column => $s_value_override)
            {
            switch($s_SQL_type)
                  {
                  case 'text' :
                  case 'int' :
                  case 'double' :
                  case 'date' :
                        if (!is_null($s_value_override))
                              {
                              $s_new_value = GetSQLValueString($s_value_override, $s_SQL_type);
                              }
                        else
                              {
                              $s_new_value = GetSQLValueString($_POST[$s_SQL_column], $s_SQL_type);
                              }
                        add_to_SQL($as_SQL_parts, $s_SQL_column, $s_new_value);
                        break;
                  default :
                  }
            }
      }

$as_SQL_parts['columns'] = rtrim($as_SQL_parts['columns'], ',');
$as_SQL_parts['values'] = rtrim($as_SQL_parts['values'], ',');
$insertSQL = "INSERT INTO rentals ({$as_SQL_parts['columns']}) VALUES ({$as_SQL_parts['values']})";
echo $insertSQL;
?>
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 1200 total points
ID: 16330549
Now with some comments and the final bit of refactoring.

<?php
// Define the main parts of the SQL statement, column names and values.
$as_SQL_parts = array
      (
      'columns' => '',
      'values' => '',
      );

// Define how the columns to be inserted into the SQL statement are typed and any hard-coded overrides.
$as_SQL_parts_definition = array
      (
      'text' => array
            (
            'active' => 'Yes',
            'address' => NULL,
            'availdate' => NULL,
            'bath' => NULL,
            'bed' => NULL,
            'city' => NULL,
            'deposit' => NULL,
            'description' => NULL,
            'expires' => "DATE_ADD(NOW(), INTERVAL {$credit['days']} DAY)",
            'featured' => NULL,
            'garage' => NULL,
            'lat' => $address['lat'],
            'leased' => NULL,
            'leaseterm' => NULL,
            'levels' => NULL,
            'long' => $address['long'],
            'Notes' => NULL,
            'petdeposit' => NULL,
            'pets' => NULL,
            'school' => NULL,
            'sellerEmail' => NULL,
            'sellerName' => NULL,
            'sellerPhone' => NULL,
            'sellerTitle' => NULL,
            'showCities' => $citySTR,
            'smoking' => NULL,
            'state' => NULL,
            'subdiv' => NULL,
            'title' => NULL,
            'zip' => NULL,
            ),
      'int'      => array
            (
            'cid' => $_POST['cat'],
            'countyid' => NULL,
            'mid' => NULL,
            ),
      'date' => array
            (
            'pdate' => date('Y-m-d'),
            ),
      'double' => array
            (
            'price' => NULL,
            'sqft' => NULL,
            ),
      );

// Process each column type.
foreach($as_SQL_parts_definition as $s_SQL_type => $as_SQL_columns)
      {
      // Process each column for the current column type.
      foreach($as_SQL_columns as $s_SQL_column => $s_value_override)
            {
            // Only handle specific types as others MAY require additional logic.
            switch($s_SQL_type)
                  {
                  case 'text' :
                  case 'int' :
                  case 'double' :
                  case 'date' :
                        // Is the override value defined.
                        if (!is_null($s_value_override))
                              {
                              // Convert the override value into an SQL compatible string.
                              $s_new_value = GetSQLValueString($s_value_override, $s_SQL_type);
                              }
                        else
                              {
                              // Convert the $_POST value into an SQL compatible string.
                              $s_new_value = GetSQLValueString($_POST[$s_SQL_column], $s_SQL_type);
                              }
                        // Add the current column to the SQL columns.
                        $as_SQL_parts['columns'] .= "`$s_column`,";

                        // Add the value to the SQL values.
                        $as_SQL_parts['values'] .= "$s_value,";
                        break;
                  default :
                  }
            }
      }

// Remove trailing , from the SQL parts.
foreach($as_SQL_parts as $s_key => &$s_value)
      {
      $s_value = rtrim($s_value, ',');
      }

// Build the SQL statement.
$insertSQL = "INSERT INTO rentals ({$as_SQL_parts['columns']}) VALUES ({$as_SQL_parts['values']})";

// Echo the SQL statement.
echo $insertSQL;
?>
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16330551
I'll stop now.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16330554
Honest!
0
 

Author Comment

by:mnoel76
ID: 16333356
Rob_jefferey

I tried to echo sitySTR;  but nothing displayed...dont know what that is about.

Rquadling

WOW

thanks for the numerous reponses.  I need to take some time and get into the code.  THanks much for the numerous responses.  I will get back to you a little later.  
0
 
LVL 9

Expert Comment

by:Rob_Jeffrey
ID: 16333556
mnoel76, if the variable contains something different than what you expect you will have to track that down.
One of the things I hate about PHP is that it doesn't require you to register variables.  
If you misspell a variable then it just starts a new one without any warning or error.

RQuadling, the reason the query is structured like that is because mnoel76 is using Dreamweaver to handle the database connection and form update.  To alter the code that drastically will drop the functionality completely from Dreamweaver.
It's generated code.
0
 

Author Comment

by:mnoel76
ID: 16333685
Rob_Jefferey

I will have to look to make sure all the spellings are correct.

Although I am using Dreamweaver to test on my local server I do have a hosting plan for the website.  Do you still think that by altering the code it would cause me more problems??  

I will be away from the computer for a little while but will repond to any reply on mmy return.

thanks
0
 
LVL 9

Assisted Solution

by:Rob_Jeffrey
Rob_Jeffrey earned 300 total points
ID: 16333743
Oh no  - definately not.
I havn't tested RQuadling's code - but it looks solid.
I just know from experience that if you modify the code generated by Dreamweaver than the interface for modifying data binding and application databases within Dreamweaver gets completely trashed.

I have had trouble in the past with changing the code manually and then trying to change a field's databind and it trashed the page I was working on.

There is no problem with using the code RQuadling wrote if you don't mind loosing the GUI in Dreamweaver for future data control in that page.
0
 

Author Comment

by:mnoel76
ID: 16337496
RQualding  thanks for all the effort.

I currently am gettin gthe following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VALUES (`` ,`` ,`` ,`` ,`` ,`` ,`` ,`` ,`` ,`` ,`` ,`` ,`` ,``

I know ther are no values display but when I try with data in fields I get same error.  and ideas here??
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16340425
What line number? Can you tell us what the $insertSQL value is? Better yet, can you show the full script again.

It seems like the data source is NOT being picked up. Hence all the blank values. I am worried about the reverse quotes in the VALUES. Normally these would be normal quotes ' not `




I've never used Dreamweaver. I also don't use Frontpage. I use EditPadPro.

But then again, my layout skills are just some DIVs with CSS positioning generated in code. So, no actual HTML pages.


0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question