Problem when inserting data to DB

[erezvol]

Hello
I tried to insert data into the DB

Here is a screenshot of error message

Here is the report wuth the uploaded data:

Here is the table structure:

And here is the result un the DB:

And here is the code where the error is ( the $stmt->…):

$db->beginTransaction();

$stmt = $db->prepare('INSERT INTO `data`
			(username, ticket, o_time, type, size, item, o_price, s_l, t_p, c_time, c_price, profit)
			VALUES
			(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');

	
for($i=0;$i<$count;$i++){

    $stmt->execute($line[$i]);
}//End for loop
$db->commit();

And this is the code where the data is created:

if((isset($o_time) && $keywords[4]<$o_time) || !isset($o_time)===true){
			//Insert row content into array.
			
				//Insert relevant data into array

				$ticket =$keywords[2];
				$o_time = $keywords[4];
				$type = $keywords[6];
			    $c_time = $o_time;

				$line[$count] = array($username, $ticket, $o_time, $type, NULL, NULL, NULL, NULL, NULL, $c_time, NULL, NULL );
				$count++;

How do I calculate and define the size of the float number?
The code stopped where the profit is -100000.00

What is the solution ?

[m_hutley]

@erezvol

The code stopped where the profit is -100000.00

Are you sure?
What’s the profit on the row AFTER that on your input?

EDIT: Additional; the code you have listed here does not fill in anything for the Profit column. Where does THAT get set?

[erezvol]

Hello
I tried to insert data into the DB

Here is a screenshot of error message

Here is the report:

Here is the table structure:

And here is the result:

And here is the code where the error is

$db->beginTransaction();

$stmt = $db->prepare('INSERT INTO `data`
			(username, ticket, o_time, type, size, item, o_price, s_l, t_p, c_time, c_price, profit)
			VALUES
			(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');

	
for($i=0;$i<$count;$i++){

    $stmt->execute($line[$i]);
}//End for loop
$db->commit();

And this is the code where the data is created:

if((isset($o_time) && $keywords[4]<$o_time) || !isset($o_time)===true){
			//Insert row content into array.
			
				//Insert relevant data into array

				$ticket =$keywords[2];
				$o_time = $keywords[4];
				$type = $keywords[6];
			    $c_time = $o_time;

				$line[$count] = array($username, $ticket, $o_time, $type, NULL, NULL, NULL, NULL, NULL, $c_time, NULL, NULL );
				$count++;

How do I calculate and define the size of the float number?
The code stopped where the profit is -100000.00

What is the solution ?

[droopsnoot]

Where is the code that attempts to write the profit column? On the face of it, a value of -100000.00 should fit inside your column specification as it is only ten digits. If you extend it to (11,2), does the problem go away?

Is FLOAT an appropriate column type? Would DECIMAL perhaps be more suitable?

[m_hutley]

Well, you have successfully copy and pasted the same post that i’ve already read without giving any new data. So congrats on sounding like a robot and not actually helping us solve your problem by answering the questions that were asked of you.

My suspicion is twofold.

Either:

1. This is the first row of data you’re trying to insert, and you’re sending profit as a string instead of a number, which will generate the ‘truncation’ warning as it type-coerces String to Float.
2. The error is on the data line AFTER the line where you try to insert a profit of -100000, and you’re instead trying to insert -100000000.

[erezvol]

Thanks for your replies,
I was’nt sure if this is a DB issue or PHP code issue so I posted on both. Now obviously I was wring.

I don’t insert the data manually
The report comes in html file.
I wrote a code which “reads” the reportt, extracts the relevant data and inserts it into the DB
Here is the code which reads the report:

do{ //start do-while loop
   
	//Get content of current upper  row
	$row = strstr($new_homepage, '</tr', true );

	//Remove content of current row from $new_homepage
	$new_homepage = str_replace($row.'</tr>', "", $new_homepage);

	//Check if the row includes a balance or credit order
	if ((strpos($row, 'balance') == true) || (strpos($row, 'credit') == true)){ //If Balance


			//Insert row content into array.
			$keywords = preg_split("#\<(.*?)\>#", $row);

    //IF $o_time EXISTS IT MEANS THIS ROW IS FULL AND HAS RELEVANT DATA
	if((isset($o_time) && $keywords[4]<$o_time) || !isset($o_time)===true){
			//Insert row content into array.
			
				//Insert relevant data into array

				$ticket =$keywords[2];
				$o_time = $keywords[4];
				$type = $keywords[6];
			    $c_time = $o_time;

				$line[$count] = array($username, $ticket, $o_time, $type, NULL, NULL, NULL, NULL, NULL, $c_time, NULL, NULL );
				$count++;
				
	} //End If Balance
    }else
	{

	//Check if the row includes a buy / sell order
	if ((strpos($row, 'buy') == true || strpos($row, 'sell') == true)){ //If Buy / Sell

		//CHECK WEATHER ORDER WAS CANCELLED OR DELETED
		if((strpos($row, 'cancelled ') == false || strpos($row, 'delete') == false)){//If 2

			//Insert row content into array.
			$keywords = preg_split("#\<(.*?)\>#", $row);

			//IF $keywords[28] EXISTS IT MEANS THIS ROW IS FULL AND HAS RELEVANT DATA
			if(isset($keywords[28])){//If 3
			
				//Insert relevant data into array

				$ticket =$keywords[2];
				$o_time = $keywords[4];
				$type = $keywords[6];
				$size = $keywords[8];
				$item = substr($keywords[10], 0,  -1);
				$o_price = $keywords[12]; 
				$s_l = $keywords[14];
				$t_p = $keywords[16];
				$c_time = $keywords[18];
				$c_price = $keywords[20];
				$profit = $keywords[28];

				$line[$count] = array($username, $ticket, $o_time, $type, $size, $item, $o_price, $s_l, $t_p, $c_time, $c_price, $profit);

				$count++;
				 
				
			
			}else{//End If 3
			
			continue;
			
			}

		}else{//END If 2
		
		continue;
		
		}

	}else{//END If Buy / Sell
	
		continue;
	}
	}

     
/////     iF Closed P/L  IS REACHED - NO MORE TRADES AVAILABLE     //////

}while (strpos($row, 'Closed P/L') != true); //End do-while loop

The first part searches for deposit q withrdawal

the second part deals with trades
It worked well for one report
Now I test the code with another report and it doesn’t work.
Here is the beginning of the first report:

And here is the begining of the second:

Chngeing data type to decimal Resulted with the following error:

[m_hutley]

Looks to me like your database engine doesnt like spaces as thousands separators.

Let’s try stripping the spaces out, and running your numbers through as actual numbers:

$profit = floatval(str_replace(" ","",$keywords[28]));