www.webdeveloper.com
Results 1 to 7 of 7

Thread: Remove subset of data

  1. #1
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88

    Remove subset of data

    Team,

    I have a script that uploads a CSV file into a MySQL database. In this CSV file is a column of two sets of numbers. Ex: 48 / 54.

    There are times when this set of numbers is 06 / 54 and interpreted by the spreadsheet creating the CSV as a date (06/01/54).

    I need a way to determine of this field of data is set up like a date, and if so to remove the middle set (/01) or (01/) so my end result is 06 / 54

    This line is for TxRxMBPS, line_of_data[4]

    Code below.

    Code:
    while (($line_of_data = fgetcsv($file_handle, 1000, ",")) !== FALSE) {
        $line_import_query="
            INSERT into bridges (
              MAC,
              DeviceName,
              SignalNoiseDBM,
              Distance,
              TxRxMbps,
              CCQ,
              ConnectionTime,
              LastIP,
              ComputerName,
              ExpectedRig
            ) VALUES (
              '".mysql_real_escape_string($line_of_data[0])."',
              '".mysql_real_escape_string($line_of_data[1])."',
              '".mysql_real_escape_string($line_of_data[2])."',
              '".mysql_real_escape_string($line_of_data[3])."',
              '".mysql_real_escape_string($line_of_data[4])."',
              '".mysql_real_escape_string($line_of_data[5])."',
              '".mysql_real_escape_string($line_of_data[6])."',
              '".mysql_real_escape_string($line_of_data[7])."',
              '".mysql_real_escape_string($compname)."',
              '".mysql_real_escape_string($ExpectedRig)."'
            )
            ";
        mysql_query($line_import_query) or die(mysql_error());
    }
    I appreciate your help

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    Sounds like a job for preg_replace().
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    Thank you, NogDog. I'm reading up on it now, but all the examples are using static text. I will need this to be a variable (the value of the column) and it will have to determine the number of characters, and execute only if there are exactly 8 characters.

    I'll keep reading, and would appreciate if you could elaborate a bit on the best way to do this.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    The quick-and-dirty solution would be to change this line (assuming it's a field that might have this issue)...
    PHP Code:
    mysql_real_escape_string($line_of_data[5]) 
    ...to something like...
    PHP Code:
    mysql_real_escape_string(preg_replace('#\b(\d+)/\d+/(\d+)\b#' "$1 / $2"$line_of_data[5])) 
    Now your job is to figure out how the heck that regular expression works -- assuming I got it right.

    PS: There probably is a SQL-base solution using regexes in the query itself.

    PPS: Prepared statements in either PDO or MySQLi could both clean the code up a bit and make it work at least somewhat more efficiently.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    Thank you again. I'll take a look at regexes. Never heard of that. BTW - I read the quote in your signature line each time I see your posts. It makes me push myself a little further each time I am working through a challenge.

  6. #6
    Join Date
    Mar 2014
    Location
    Corpus Christi, TX
    Posts
    88
    Using your example, I am getting the following error:

    Parse error: syntax error, unexpected '"$1 / $2"' (T_CONSTANT_ENCAPSED_STRING) in C:\xampp2\htdocs\htdocs\Speedtest\executimpcsv.php on line 47

    Line 47 reads as you suggested:

    Code:
    '".mysql_real_escape_string(preg_replace('#\b(\d+)/\d+/(\d+)\b#' "$1 / $2", $line_of_data[5]))."',
    Any thoughts?

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,326
    missing a comma between the first and second params (i.e. between the single-quoted string and the double-quoted string).
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles