Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

PHP MySQL truncating input

Hey,

I'm using the following PHP to add items to a MySQL database installed on Linux RHEL5, INtel, 64. The FIRST item ($value) added to the "barcode" column adds correctly and then all the remaining items truncate the last character.

When items are added manually (via an SQL insert command at the mysql> prompt) they all add correctly.

The data being added is fed from an HTML page using a form with a textarea box. Multiple "barcodes" are entered in this textarea with carriage returns between them.

Thanks!
**** Here is the PHP script **** (see below for example of output)
<html>
 <head>
  <title>PHP Test</title>
 </head>
 <body>
   <?php

        //Grab variables from prev page
        $tapes = explode("\r", $_POST['tapelist']);
        $container = $_POST['container'];
        $containerexpdate = $_POST['expires'];

        // Make a MySQL Connection
         mysql_connect("localhost", "root","") or die(mysql_error());
         mysql_select_db("tapestorage") or die(mysql_error());

        // Insert a row of information into the table
        foreach ($tapes as &$value) {
                echo "Writing $value <br />";
                mysql_query("INSERT INTO tapes
                  VALUES ('$value','$containerexpdate','$container')") or print(mysql_error());
                print "<br />----<br />";
        }
   ?>
 </body>
</html>

*** Here is what a "Select * from tapes" produces (The last character "3" is missing from all the "barcode" column entries)
mysql> select * from tapes;
+----------+------------+----------+
| barcode  | expdate    | location |
+----------+------------+----------+
| SJ8023L3 | 2011-05-15 | 12345678 |
|
SJ8023L | 2011-05-15 | 12345678 |
|
SJ5693L | 2011-05-15 | 12345678 |
|
SJ7761L | 2011-05-15 | 12345678 |
|
SJ5648L | 2011-05-15 | 12345678 |
|
SJ9165L | 2011-05-15 | 12345678 |
|
SJ5319L | 2011-05-15 | 12345678 |
|
SJ2884L | 2011-05-15 | 12345678 |
|
SJ6666L | 2011-05-15 | 12345678 |
|
SJ8436L | 2011-05-15 | 12345678 |
+----------+------------+----------+
10 rows in set (0.00 sec)

mysql>

0
Mike R.
Asked:
Mike R.
  • 8
  • 6
1 Solution
 
Ray PaseurCommented:
MySQL will truncate variable strings silently if the column definition is not wide enough for the data.  Please post the CREATE TABLE statements, thanks.
0
 
Ray PaseurCommented:
Also you might want to change this:

foreach ($tapes as &$value) {

to this:

foreach ($tapes as $value) {
0
 
cfEngineersCommented:
try this
comment out this line
  //mysql_query("INSERT INTO tapes VALUES ('$value','$containerexpdate','$container')") or print(mysql_error());

and insert this one
echo "INSERT INTO tapes VALUES ('".$value."','".$containerexpdate."','".$container."');

paste the output
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Mike R.Author Commented:
P.S. In the script, an echo statement presents the variable "$value" to the screen before writing (echo "Writing $value <br />";) and the "$value" variable is correct. The issue is in the writing.

Also, I notice on output, the leading " | " is missing. I realize this is just a marker to make the text output pretty...but it is only missing on the values with the truncated last character. Manually entered valuse have the correct " | " showing. Could this be related?
0
 
Ray PaseurCommented:
Try this... Use var_dump($_POST) to see if you are getting the expected values into the script.

Also, you might want to learn about this function before hackers learn about your web site.  Just a thought...
http://php.net/manual/en/function.mysql-real-escape-string.php
0
 
Mike R.Author Commented:
Wow. Thanks for all the fast input!

@cfEngineers: The line...
echo "INSERT INTO tapes VALUES ('".$value."','".$containerexpdate."','".$container."');
...failed completely (script would not run. Is the "echo" supposed to be there? Is the statement supposed to somehow be integrated into a "mysql_query" statement somehow?

@Ray_Paseur:
Changing "&$values" to "$values" unfortunately had no effect. (Script ran, but with same error)
The column is an 8 character varchar. This should be enough since the first PHP, and all manually added, barcodes are stored correctly.


mysql> show columns from tapes
    -> ;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| barcode  | varchar(8) | NO   | PRI | NULL    |       |
| expdate  | date       | YES  |     | NULL    |       |
| location | varchar(8) | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Thanks again!


0
 
Ray PaseurCommented:
OK, let's see the var_dump($_POST) output.  If you echo "<pre>" before var_dump() it will be easier to read.
0
 
Mike R.Author Commented:
P.s.
@cfEngineers:
Changing the mysql insert to (added periods)...

 mysql_query("INSERT INTO tapes VALUES ('.$value.','.$containerexpdate.','.$container.')") or print(mysql_error());

... simply added the periods as text to the variable. So the values truncates worse (appeared as ".SJ1234." having the effect of truncating the last TWO characters :-)
0
 
Mike R.Author Commented:
@Ray_Paseur:

Here is the var_dump output. Sorry its messy. I couldn;t quite get the <pre> to work :D

array(4) { ["container"]=> string(8) "12345678" ["expires"]=> string(9) "2020-5-15" ["tapelist"]=> string(90) "SJ8023L3 SJ5693L3 SJ7761L3 SJ5648L3 SJ9165L3 SJ5319L3 SJ2884L3 SJ6666L3 SJ8436L3 " ["Submit"]=> string(6) "Submit" }
0
 
Mike R.Author Commented:
Here's the normal output of the script. It appears the "$value" var is correctly holding the entire string...
----
Writing SJ5693L3

----
Writing SJ7761L3

----
Writing SJ5648L3

----
Writing SJ9165L3

----
Writing SJ5319L3

----
Writing SJ2884L3

----
Writing SJ6666L3

----
Writing SJ8436L3

----
0
 
Ray PaseurCommented:
You might try using trim() on the variables.  I am not sure whether MySQL will do that automatically or not.  However the best thing might be to post the form script and the action script as they are right now.  Please use the code snippet to do that.  I will give you a test script back with some built-in diagnostics.
0
 
Mike R.Author Commented:
Hey,

I tried adding a "trim()"...to no avail (same issue). Here;s the snippets...
 
<html>
 <head>
  <title>PHP Test</title>
 </head>
 <body>
   <?php

        //Grab variables from prev page
        $tapes = explode("\r", $_POST['tapelist']);
        $container = $_POST['container'];
        $containerexpdate = $_POST['expires'];

        // Make a MySQL Connection
         mysql_connect("localhost", "root","") or die(mysql_error());
         mysql_select_db("tapestorage") or die(mysql_error());

        // Insert a row of information into the table
        foreach ($tapes as $utvalue) {
                echo "Writing $utvalue <br />";
                $value = trim($utvalue);
                $value = $utvalue;
                echo "Writing $value <br />";
                mysql_query("INSERT INTO tapes
                  VALUES ('$value','$containerexpdate','$container')") or print(mysql_error());
                print "<br />----<br />";
        }
   ?>
 </body>
</html>

Open in new window

<html>
<head>
<title>My Variables HTML Form</title>
</head>
<body>
<h4>My HTML Form</h4>

<form name="myform" method="post" action="parse.php">
Enter Container: <input type="text" name="container"><br>
Enter Container expiration date(yyyy-mm-dd): <input type="text" name="expires"><br>



Scan tapes here: <textarea cols="40" rows="5" input type="text" name="tapelist"></textarea><br>


<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>

Open in new window

0
 
Mike R.Author Commented:
Sh|^. I'm a moron. I inadvertently had a line UNDOING the trim after I did.

When I removed that...the trim worked. They;re all entering correctly now.

Sorry about that but THANKS for solving the problem!!

The answer was trim()!
0
 
Mike R.Author Commented:
Awesome help and fast!!!
0
 
Ray PaseurCommented:
Thanks for the points!  Best, ~Ray
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now