13 Replies Latest reply on May 4, 2017 11:14 AM by beverly

    PHP get field, is there a way to strip comma from results?

    Tom_Droz

      Hi

      I have a php script that grabs data from fields in a hosted FileMaker solution.  Part of the code reads:

       

      mysql_real_escape_string($inventory_row->getField('VEHICLES::Price
      Asking'))

       

      The problem is the field has values with a comma ie "10,000"  I want the results to be just the number no comma ie "10000"

       

      Is there a way to do that within the above PHP code?

       

      The data is from many databases so changing it there isn't my 1st option.  The data is scripted to be inserted in to a MYSQL database and it needs to be a number without a comma.  Any ideas on if the code above can be tweaked to accomplish this?

       

      Thanks!

       

      Tom

        • 1. Re: PHP get field, is there a way to strip comma from results?
          CarlSchwarz

          Use the PHP string functions.  Here's a string replace function you could use on your result

          PHP str_replace() Function

          • 2. Re: PHP get field, is there a way to strip comma from results?
            beverly

            Carl, you beat me to the answer and the w3schools is a great PHP resource I use often!

            here is another:

            beverly

            • 3. Re: PHP get field, is there a way to strip comma from results?
              Tom_Droz

              Thanks Carl

               

              I'm understand the concept, but not sure how that would be inserted in to the formula

               

              str_replace(",","","'VEHICLES::Price Asking'")  so....

               

              mysql_real_escape_string($inventory_row->getField('VEHICLES::Price Asking'))

               

              should be?

               

              str_replace(",","","mysql_real_escape_string($inventory_row->getField('VEHICLES::Price Asking')")

              • 4. Re: PHP get field, is there a way to strip comma from results?
                CarlSchwarz

                That looks right on the face of it.  Or you could break it up into 2 lines by first assigning the sql result to a variable and then using the str_replace function on the variable.  If you break it up into 2 lines then you can use the echo statement debug each line.

                • 5. Re: PHP get field, is there a way to strip comma from results?
                  Tom_Droz

                  Thanks Carl for your time looking at this.

                   

                  This is a 1 of a long list, when I made that change it failed.

                   

                  here is a snippet of the code around it ....

                   

                  . mysql_real_escape_string($inventory_row->getField('VEHICLES::Photo_Count')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Price Asking')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Status')) .

                  • 6. Re: PHP get field, is there a way to strip comma from results?
                    CarlSchwarz

                    That is one long line of code that is hard to debug.

                    I suggest you break it apart, e.g.:

                    $photoCount = mysql_real_escape_string($inventory_row->getField('VEHICLES::Photo_Count'));

                    $priceAsking = mysql_real_escape_string($inventory_row->getField('VEHICLES::Price Asking'));

                     

                    etc.

                    Then you can do the following to debug:

                    echo $priceAsking;

                    $priceAsking = str_replace(",","",$priceAsking);

                    echo $priceAsking;

                    etc. and debug.

                    Comment out lines when the page breaks to find the line that is breaking the page.

                    I am guessing that you didn't originally write this php page?

                    • 7. Re: PHP get field, is there a way to strip comma from results?
                      CarlSchwarz

                      Tom_Droz wrote:

                       

                       

                       

                      str_replace(",","","mysql_real_escape_string($inventory_row->getField('VEHICLES::Price Asking')")

                      I just noticed that you have quotes around your sql statement, they shouldn't be there.

                      • 8. Re: PHP get field, is there a way to strip comma from results?
                        Tom_Droz

                        Carl the code is a 50 times that length, that is just a snippet.  It is in production so I cant mess with it.

                         

                        Thanks for the time and thoughts!

                         

                        Tom

                        • 9. Re: PHP get field, is there a way to strip comma from results?
                          mikebeargie

                          First, don't use:

                          mysql_real_escape_string()

                          That's designed to strip user entry before being used as a username/password/write action to MySQL for preventing attacks. Since you are PULLING data here, it's not necessary.

                           

                          Secondly, if the result has a comma STORED as part of the value, that means it's being returned as a string instead of an integer. If you want to work with that value as an integer, you need to convert it. Otherwise you're just window dressing instead of solving the problem.

                           

                          You can convert explicitly by type casting. EG:

                          $val = $inventory_row->getField('VEHICLES::Price Asking');

                          $num = (float)$val;

                           

                          Otherwise, the above recommendations are similar to using filemaker's substitute() function, but it sounds like you would may be better off using a filter function instead, which would be:

                          filter_var($inventory_row->getField('VEHICLES::Price Asking'), FILTER_SANITIZE_NUMBER_FLOAT);

                           

                          filter_var is a lot better to use for sanitizing or validating types of values that preg_replace() or str_replace() is.

                          Here's a reference doc so you can read all of the parameters:

                          PHP 5 Filter Functions

                          1 of 1 people found this helpful
                          • 10. Re: PHP get field, is there a way to strip comma from results?
                            Tom_Droz

                            Thanks I tried:

                            str_replace(",","", mysql_real_escape_string($inventory_row->getField('VEHICLES::Price Asking'))

                             

                            Still no go

                            • 11. Re: PHP get field, is there a way to strip comma from results?
                              Tom_Droz

                              Thanks Mike

                              A little over my head as I didn't write the code.

                              Just as FYI for you or anyone else interested:  The code both pulls values from FM then put them in to MySQL database.  Here is the entire code, not sure if it matters:  What it does is pulls my data (I sell a vertical solution so have many database with identical structures) insert them in to a MySQL database which then has the customers website that works off that data.  Example site Home Eli's Autos 399 Empire Blvd Brooklyn NY 11225 7184849822

                               

                              <?php
                              error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
                              ini_set("display_errors", false);

                              date_default_timezone_set('America/New_York');

                              require_once('FileMaker.php');
                              require_once('libraries/mail/class.phpmailer.php');
                              require_once('libraries/mail/class.smtp.php');
                              include('libraries/functions.php');
                              include('connection.php');
                              include('dealerList.php');

                              $dblink = mysql_connect($vHostName,$vUserName,$vPassword); //Arguments = host, username, password
                              mysql_select_db($vDBName);

                              $body = "";

                              foreach ($arrDatabase as $vDatabase) {
                              echo 'Loading ' . $vDatabase . ' (Starting @ ' . date('m/d/y H:i:s') . ')';
                              $body .= 'Loading ' . $vDatabase . ' (Starting @ ' . date('m/d/y H:i:s') . ')';

                              //FileMaker Connection Info and Tools Library
                              $ShiftPoint = new FileMaker($vDatabase, $hostname_ShiftPoint, $username_ShiftPoint, $password_ShiftPoint);
                              //FileMaker "Query"
                              $inventory_find = $ShiftPoint->newFindCommand('Scripted_WebData'); //Scripted_WebData is tablename in filemaker
                              $inventory_findCriterions = array('VEHICLES::__kp_VehicleID'=>'*',);
                              foreach($inventory_findCriterions as $key=>$value) {
                                $inventory_find->AddFindCriterion($key,$value);
                              }
                              $inventory_result = $inventory_find->execute();

                              if($inventory_result->code == NULL){
                                $vQuery = "DELETE FROM inventory WHERE DBID='" . $vDatabase . "'";
                                mysql_query($vQuery);

                                $vCount = 0;
                                foreach($inventory_result->getRecords() as $inventory_row){
                                 //
                                 $vQuery = "INSERT INTO inventory (DBID, kf_WebData_ID, image_array, MySql_Website_Data, Price_Display, Price_Display_Feeds, Random_Number, Special_Count, Status, BaseTable_SignInInformation_File_Name, vehicle_FEATURES_Details, vehicle_FEATURES_Extra_Features_Final, vehicle_FEATURES_Mileage_EPA_City, vehicle_FEATURES_Mileage_EPA_Highway, vehicle_FEATURES_Virtual_Tour_Link, vehicle_FEATURES_Web_Custom_Link, vehicles_web_data_WEB_BLOG_Staff_Total, vehicles_web_data_WEB_BLOG_Testimonial_Total, VEHICLES___kp_Stock_Number, VEHICLES___kp_VehicleID, VEHICLES_Body_Style, VEHICLES_Doors, VEHICLES_Drive_Type, VEHICLES_Engine_Description, VEHICLES_Exterior_Color, VEHICLES_Fuel_Type, VEHICLES_Interior_Color, VEHICLES_Location_Name, VEHICLES_Location_Number, VEHICLES_Make, VEHICLES_Mileage_Current, VEHICLES_Model, VEHICLES_Photo_Count, VEHICLES_Price_Asking, VEHICLES_Status, VEHICLES_Transmission, VEHICLES_Trim, VEHICLES_Vin_Number, VEHICLES_Web_Publish, VEHICLES_Web_Publish_AutoTrader, VEHICLES_Web_Publish_Cars, VEHICLES_Web_Publish_CarsForSale, VEHICLES_Web_Publish_LemonFree, VEHICLES_Web_Publish_OVE, VEHICLES_Web_Publish_Special, VEHICLES_Year, VEHICLES_Year_Make_Model_Body_Style, Vehilces_X_Global_Storage_Email_Address, Vehilces_X_Global_Storage_Office_Phone_Number, Vehilces_X_Global_Storage_Office_Web_Address, Vehilces_X_Global_Storage_Office_Web_Address_Navigation, Vehilces_X_Global_Storage_Web_AboutUs, Vehilces_X_Global_Storage_Web_Body_Style, Vehilces_X_Global_Storage_Web_Body_Style_Count, Vehilces_X_Global_Storage_Web_Body_Style_Count_Show, Vehilces_X_Global_Storage_Web_Body_Style_Show, Vehilces_X_Global_Storage_Web_Custom_Title, Vehilces_X_Global_Storage_Web_Email, Vehilces_X_Global_Storage_Web_Facebook_Like, Vehilces_X_Global_Storage_Web_Financing_Link, Vehilces_X_Global_Storage_Web_Financing_Link_Mobile, Vehilces_X_Global_Storage_Web_Financing_Link_Vehicle, Vehilces_X_Global_Storage_Web_General_Notes, Vehilces_X_Global_Storage_Web_Google_Analytics, Vehilces_X_Global_Storage_Web_Hours_Friday, Vehilces_X_Global_Storage_Web_Hours_Monday, Vehilces_X_Global_Storage_Web_Hours_Saturday, Vehilces_X_Global_Storage_Web_Hours_Sunday, Vehilces_X_Global_Storage_Web_Hours_Thursday, Vehilces_X_Global_Storage_Web_Hours_Tuesday, Vehilces_X_Global_Storage_Web_Hours_Wednesday, Vehilces_X_Global_Storage_Web_Loan_Calculator, Vehilces_X_Global_Storage_Web_Map, Vehilces_X_Global_Storage_Web_Map_Mobile, Vehilces_X_Global_Storage_Web_Meta_Description, Vehilces_X_Global_Storage_Web_Meta_Keywords, Vehilces_X_Global_Storage_Web_Multi_Office, Vehilces_X_Global_Storage_Web_Office_Name_Additional, Vehilces_X_Global_Storage_Web_Office_Name_Calculation, Vehilces_X_Global_Storage_Web_Phone, Vehilces_X_Global_Storage_Web_Phone_Txt_Messages, Vehilces_X_Global_Storage_Web_Site_Link, Vehilces_X_Global_Storage_Web_Title_Additional, Vehicles_Days_On_Market) VALUES ('" . $vDatabase . "',
                                 '" . mysql_real_escape_string($inventory_row->getField('_kf_WebData_ID')) . "', '" . mysql_real_escape_string($inventory_row->getField('image_array')) . "', '" . mysql_real_escape_string($inventory_row->getField('MySql_Website_Data')) . "', '" . mysql_real_escape_string($inventory_row->getField('Price_Display')) . "', '" . mysql_real_escape_string($inventory_row->getField('Price_Display_Feeds')) . "', '" . mysql_real_escape_string($inventory_row->getField('Random_Number')) . "', '" . mysql_real_escape_string($inventory_row->getField('Special_Count')) . "', '" . mysql_real_escape_string($inventory_row->getField('Status')) . "', '" . mysql_real_escape_string($inventory_row->getField('BaseTable_SignInInformation::File Name')) . "', '" . mysql_real_escape_string($inventory_row->getField('vehicle_FEATURES::Details')) . "', '" . mysql_real_escape_string($inventory_row->getField('vehicle_FEATURES::Extra Features Final')) . "', '" . mysql_real_escape_string($inventory_row->getField('vehicle_FEATURES::Mileage EPA City')) . "', '" . mysql_real_escape_string($inventory_row->getField('vehicle_FEATURES::Mileage EPA Highway')) . "', '" . mysql_real_escape_string($inventory_row->getField('vehicle_FEATURES::Virtual Tour Link')) . "', '" . mysql_real_escape_string($inventory_row->getField('vehicle_FEATURES::Web_Custom_Link')) . "', '" . mysql_real_escape_string($inventory_row->getField('vehicles_web_data_WEB_BLOG::Staff_Total')) . "', '" . mysql_real_escape_string($inventory_row->getField('vehicles_web_data_WEB_BLOG::Testimonial_Total')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::__kp_Stock Number')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::__kp_VehicleID')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Body Style')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Doors')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Drive Type')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Engine Description')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Exterior Color')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Fuel Type')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Interior Color')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Location_Name')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Location_Number')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Make')) . "', '" . str_replace(',','',mysql_real_escape_string($inventory_row->getField('VEHICLES::Mileage Current'))) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Model')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Photo_Count')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Price Asking')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Status')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Transmission')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Trim')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Vin Number')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Web_Publish')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Web_Publish_AutoTrader')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Web_Publish_Cars')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Web_Publish_CarsForSale')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Web_Publish_LemonFree')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Web_Publish_OVE')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Web_Publish_Special')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Year')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Year Make Model Body Style')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Email Address')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Office Phone Number')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Office Web Address')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Office_Web_Address_Navigation')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_AboutUs')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Body_Style')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Body_Style_Count')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Body_Style_Count_Show')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Body_Style_Show')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Custom_Title')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Email')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Facebook_Like')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Financing_Link')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Financing_Link_Mobile')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Financing_Link_Vehicle')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_General_Notes')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Google_Analytics')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Hours_Friday')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Hours_Monday')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Hours_Saturday')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Hours_Sunday')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Hours_Thursday')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Hours_Tuesday')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Hours_Wednesday')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Loan_Calculator')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Map')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Map_Mobile')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Meta_Description')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Meta_Keywords')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Multi_Office')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Office_Name_Additional')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Office_Name_Calculation')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Phone')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Phone_Txt_Messages')) . "', '"
                              . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Site_Link')) . "', '" . mysql_real_escape_string($inventory_row->getField('Vehilces_X_Global Storage::Web_Title_Additional')) . "', '" . mysql_real_escape_string($inventory_row->getField('VEHICLES::Days On Market Active')) . "')";
                                if (!empty($inventory_row)) mysql_query($vQuery);
                                 //mysql_query($vQuery) or die($vQuery . "<BR>Invalid query: " . " L:" . __LINE__ . " - " .mysql_error());
                                 echo '.';
                                 $vCount = $vCount + 1;
                                }
                                echo  'Done - ' . $vCount . ' Records (Ending @ ' . date('m/d/y H:i:s') . ')'."\n\n";
                                $body .= ' Done - ' . $vCount . ' Records (Ending @ ' . date('m/d/y H:i:s') . ')'."\n\n";
                              }elseif($inventory_result->code == 401){
                                echo $inventory_result->message . '(' . $inventory_result->code . ')';
                                $body .= $inventory_result->message . '(' . $inventory_result->code . ')';

                                $vQuery = "DELETE FROM inventory WHERE DBID='" . $vDatabase . "'";
                                mysql_query($vQuery);

                                echo PHP_EOL . 'Done - 0 Records (Ending @ ' . date('m/d/y H:i:s') . ')' . PHP_EOL;
                                $body .= PHP_EOL . ' Done - 0 Records (Ending @ ' . date('m/d/y H:i:s') . ')' . PHP_EOL;
                              }else{
                                echo PHP_EOL . 'ERROR '.$inventory_result->message . '(' . $inventory_result->code . ')'.' '. $vDatabase .' - 0 Records (Ending @ ' . date('m/d/y H:i:s') . ')' . PHP_EOL;
                                $body .= PHP_EOL . ' ERROR '.$inventory_result->message . '(' . $inventory_result->code . ')'.' '. $vDatabase .' - 0 Records (Ending @ ' . date('m/d/y H:i:s') . ')' . PHP_EOL;
                              }

                              }
                              mysql_close($dblink);
                              if (strpos($body,'ERROR') !== false) {
                                  $subject = 'ERROR';
                              } else {
                              $subject = 'GOOD';
                              }
                              $result = sendMail ('me@aol.com','Tom Droz',$body,$subject); //send mail

                              ?>

                              • 12. Re: PHP get field, is there a way to strip comma from results?
                                mikebeargie

                                The code is deprecated since PDO is the preferred method (has been for more than a few years) for writing statements that will interact with MySQL. If you’re not sure what you’re doing an experienced developer could probably redo it for you in a very short time.

                                • 13. Re: PHP get field, is there a way to strip comma from results?
                                  beverly

                                  for those who don't know:

                                  "PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP"

                                   

                                  beverly