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

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.

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

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

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.

$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.

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;
?>

Final set of fixes for the time being.

<?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;
?>

I'll stop now.

Honest!

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.  

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.

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

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??

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.