Parse rows of data from string and insert into database

Question

I am new to PHP apologize if this is an unworldly question. I am receiving a packet of data with dynamic length on the tcp/ip socket. The packet looks like this:

Palace1,radio,location1,location2,location3,location4,GSMId:Palace2,radio,location1,location2,location3,location4,GSMId:Palace3,radio,location1,location2,location3,location4,GSMId

You can see after the GSMId I have a colon to separate one reports. The length of the packet could be anything.

My task is that I want to chop this packet after every colon (:) and want to save each report in Database.

Right now what I am doing to chop each packet is:

$string = "Palace1,radio,location1,location2,location3,location4,GSMId:Palace2,radio,location1,location2,location3,location4,GSMId:Palace3,radio,location1,location2,location3,location4,GSMId";

$countString = substr_count($string, ":");

$NumberOfReports = $countString + 1;

echo $NumberOfReports."\n";
echo $countString."\n";

$chopPacket = explode(':' , $string);

foreach($chopPacket as $value)
{
    $Report = $value;
    echo $Report."\n";
    writeToDataBase($Report);
}

DataBAse Code :

function writeToDataBase($Report)
{
    date_default_timezone_set("Europe/London");
    $date = date('Y-m-d H:i:s');
    $counter = 0;

    $DecodingData = explode("," , $Report);

    if ($DecodingData > 0) {
        $username = "user";
        $password = "password";
        $host     = "localhost";
    
        $connector = @mysql_connect($host, $username, $password) or die("Unable to connect");
        $selected = @mysql_select_db("gsmdb", $connector) or die("Unable to connect");

        $importSQL = "INSERT INTO gsmclient_test      VALUES('".$counter."','".$DecodingData[0]."','".$DecodingData[1]."','".$DecodingData[2]."','".$DecodingData[3]."','".$DecodingData[4]."', '".$DecodingData[5]."','".$DecodingData[6]."','".$date."')";
        mysql_query($importSQL) or die(mysql_error());
        mysql_close($connector);
    }
}

The code above is only saving the first report in database.

where is your database code? in general your code is correct, you must do `INSERT` into `foreach` loop. — mitkosoft, Mar 22 '16 at 11:06
Please have look at the code. I am using separate function for database in another file. — Crazy Engineer, Mar 22 '16 at 11:12
in general is OK, just do `if (count($DecodingData) > 0)` in your function as this is an array after `explode()`. one more suggestion - start using mysqli_* functions instead of mysql_* — mitkosoft, Mar 22 '16 at 11:15
and there is a missing closing bracket in your function - you must close both IF statement and function body, — mitkosoft, Mar 22 '16 at 11:18
one more thing - what is the purpose of `$counter` variable? every time when you call the function, the value will be 0, so you will try to insert every record with this 0. if this is a unique rowID in your database, make it autoincremental and remove it from SQL query OR make it global variable in your PHP script. most probably this is the problem — mitkosoft, Mar 22 '16 at 11:21
O dear.. how the hell did it work.. @mitkosoft you just saved my day.. it worked.. Thanks for your help. — Crazy Engineer, Mar 22 '16 at 11:24
the $counter I am using is useless I should have removed it I am using auto-increment for unique id — Crazy Engineer, Mar 22 '16 at 11:25
The list of things you should remove begins with your database code. `mysql_*` functions are not present in the current version of PHP and were deprecated more than 5 years ago. Just stop using them. — miken32, Mar 23 '16 at 20:23

score 0 · Answer 1 · answered Jan 05 '21 at 10:47

You will need to perform 2 separate e plosions to prepare the data.

Use a single, prepared statement to ensure that your query is stable and secure.

Here is a similar mysqli technique as a comparison.

I don't like the look of that $counter. Your database should have the row identifier as an autoincremented primary key and the date column should have a default value of CURRENT_TIMESTAMP so that that column doesn't need to be declared during insert queries.

I don't know what your column names are, so I cannot add them to my sql.

We shouldn't see mysql_ functions anymore; use mysqli_ or pdo functions.

Code:

$string = "Palace1,radio,location1,location2,location3,location4,GSMId:Palace2,radio,location1,location2,location3,location4,GSMId:Palace3,radio,location1,location2,location3,location4,GSMId";

$stmt = $mysqli->prepare("INSERT INTO gsmclient_test VALUES(?,?,?,?,?,?)";
$stmt->bind_param('ssssss', $pal, $rad, $loc1, $loc2, $loc3, $loc4);

foreach (preg_split('/,GSMId:?/', $string, -1, PREG_SPLIT_NO_EMPTY) as $rowString) {
    [$pal, $rad, $loc1, $loc2, $loc3, $loc4] = explode(',', $rowString, 6);
    $stmt->execute();
}

Parse rows of data from string and insert into database

1 Answers1