Split column via query to insert extract into another column. Not working

Question

working forever trying to resolve my script to extract the middle five characters from column "part_no" and insert the data into the "core" column (varchar). The format is always "000-00000-00".

I run this script as a part of my whole script to create a new record.

The script has no errors, but does not insert into cartons_current.

Very frustrated.

Any help would be appreciated.

The record is created but the core column is blank.

enter image description here

 <?
 $host     = "XXXXXXX";
 $username = "XXXXXXX";
 $password = "XXXXXXX";
 $db_name  = "XXXXXXX";
 $tbl_name = "cartons_current";

 mysql_connect("$host", "$username", "$password") or die("cannot connect");
 mysql_select_db("$db_name") or die("cannot select DB");

 date_default_timezone_set('America/Los_Angeles');
 $date = date("m/d/y g:i A");
 $order = "INSERT INTO cartons_current (orig_time, type, part_no, description, artwork, min, max, qty)
        VALUES
        ('$date','$_POST[type]', '$_POST[part_no]', '$_POST[description]',       '$_POST[artwork]', '$_POST[min]', '$_POST[max]', '$_POST[qty]')";




 $result = mysql_query($order);

 $query2 = "select part_no from cartons_current";
 $sel = mysql_query($query2);
 while($row = mysql_fetch_row($sel)) {
 $core_digits = explode('-',$row[0]);
 $core =$core_digits[1];
 $query3 = "insert  into cartons_current(core) values('$core')";
 $sel2 = mysql_query($query3);
 }



 if ($result) {


 $part_no = $_REQUEST['part_no'] ;

 header("location: inv_fc_new_success.php?part_no=" . urlencode($part_no));
 }
 else {
  header("location: inv_fc_new_fail.php");
 }
 ?>

possible duplicate of [Trying to extract 5 characters from a column when adding record mysql via php](http://stackoverflow.com/questions/10973494/trying-to-extract-5-characters-from-a-column-when-adding-record-mysql-via-php) — Aaron W., Jun 11 '12 at 18:09
I assume you mean it does not insert into `cartons_current`? — Jeremy Holovacs, Jun 11 '12 at 18:10
Before you go any further with this code, plug the gaping-wide-open SQL injection vulnerabilities in there. You're just begging to have your server pwn3d. — Marc B, Jun 11 '12 at 18:35

Zane Bien · Accepted Answer · 2012-06-11T19:30:13.377

2

If you're trying to insert values into that column for already existing rows, you need to use UPDATE. You can do it in one query:

UPDATE cartons_current
SET core = SUBSTR(part_no, 5, 5)

Edit: Okay, you're trying to insert a new record and you need the extracted core value:

$core = substr($_POST['part_no'], 5, 5);
$type = mysql_real_escape_string($_POST['type']);
$part_no = mysql_real_escape_string($_POST['part_no']);
$description = mysql_real_escape_string($_POST['description']);
$artwork = mysql_real_escape_string($_POST['artwork']);
$min = mysql_real_escape_string($_POST['min']);
$max = mysql_real_escape_string($_POST['max']);
$qty = mysql_real_escape_string($_POST['qty']);
$order = "
    INSERT INTO cartons_current
    (
        orig_time,
        type,
        part_no,
        description,
        artwork,
        min,
        max,
        qty,
        core
    )
    VALUES
    (
        '$date',
        '$type',
        '$part_no',
        '$description',
        '$artwork',
        $min,
        $max,
        $qty,
        '$core'
    )";

$result = mysql_query($order);

Always escape your input if you're using the deprecated mysql_* functions... but much better yet, use PDO and prepared statements to interact with your database.

edited Jun 11 '12 at 19:30

answered Jun 11 '12 at 18:20

Zane Bien

22,685
6
45
57

I'm running this script at the time the client creates a new record with a new part number (part_no) – Erik Jun 11 '12 at 18:24
This is all client side. I updated the question with the whole script. This one really got me stumped. – Erik Jun 11 '12 at 18:24
In PHP, you can use `$core = substr($_POST['part_no'], 5, 5)` and insert it in the same query you use to insert the row. Also, the way you have your script set up makes it wide open to SQL injection attack. Consider using [PDO](http://php.net/manual/en/book.pdo.php) and its support for [prepared statements](http://www.php.net/manual/en/pdo.prepared-statements.php). – Zane Bien Jun 11 '12 at 18:26
Without a where that will update all the rows with the same value! – somnath Jun 11 '12 at 18:34
@somnath, no it won't. It will update the column with the substr of each row's respective part_no value. – Zane Bien Jun 11 '12 at 18:35
I thought initially that to be from PHP code sending a MySQL code in a while. Good one! However, he seems to be after creating a new row instead! – somnath Jun 11 '12 at 18:38
I am creating a new row. Nothing seems to be working. Should I be checking null on any of these fields? – Erik Jun 11 '12 at 18:40
You should be validating (is the input empty, is it not a number when it's supposed to be, etc. etc.) and [escaping](http://php.net/manual/en/function.mysql-real-escape-string.php) all values before you execute the query (i.e. if the inputs didn't pass all the tests, don't bother to execute the query and display an error message instead). Where is the $date variable coming from? – Zane Bien Jun 11 '12 at 18:45
Should I be creating the part_no first then in a 2nd process do the core script? or can I do them together? – Erik Jun 11 '12 at 18:49
@Zane Thank u. I uploaded a pic of the structure for you. Now I'm thinking maybe there is a problem in the structure. – Erik Jun 11 '12 at 19:05
I see the picture, thank you. You should make fields that you don't necessarily need to insert values into a nullable field, where its allowed to be empty. You will know when you see the field containing a "Yes" under the NULL column in your structure pic. Can you also provide some example input values (like where is the $date variable coming from, etc.). Make sure none of your input values exceed the character limit you set in your VARCHAR types. – Zane Bien Jun 11 '12 at 19:09
I updated the script with everything. I appreciate your help, but core is still showing up empty. I'm gonna give up on this one I think. Dosen't make sense. – Erik Jun 11 '12 at 19:13
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/12408/discussion-between-zane-bien-and-erik) – Zane Bien Jun 11 '12 at 19:18

score 0 · Answer 2 · answered Jun 11 '12 at 18:09

0

Try explode(). split() is deprecated.

$query2 = "select part_no from cartons_current";
$sel = mysql_query($query2);
while($row = mysql_fetch_row($sel)) {
 $core_digits = explode('-',$row[0]);
 $core =$core_digits[1];
 $query3 = "insert  into cartons_current(core) values('$core')";
 $sel2 = mysql_query($query3);
}

answered Jun 11 '12 at 18:09

somnath

1,337
1
9
13

I tried your suggestion, but I still am not seeing anything in the core column. Again, The script does create a new record but the core column is still left blank. The column is varchar at 20 limit. Can't figure it out. I updated the question with the whole script. – Erik Jun 11 '12 at 18:21
Should I move this query above query1? – Erik Jun 11 '12 at 18:25
Can you check for not null for any of the other columns in the table? You are `inserting` into `core` only but it could be some other column is not null and as such the insert is failing. Try to call mysql_error() and check if anything comes up. http://php.net/manual/en/function.mysql-error.php – somnath Jun 11 '12 at 18:36
The null box is not checked for any of these fields. – Erik Jun 11 '12 at 18:41
I'm trying to create a new row and extract from "part_no" to insert into "core". Appreciate your follow through. Really stumped on this one. – Erik Jun 11 '12 at 18:42
Should I be creating the part_no first then in a 2nd process do the core script? or can I do them together? – Erik Jun 11 '12 at 18:49
I uploaded a pic of the structure for you. No I'm thinking maybe there is a problem in the structure. – Erik Jun 11 '12 at 19:06
@Erik all the columns are NOT NULL meaning they cant be left blank. So in your insert command you have to mention data for each of the other columns in the table. The other way round is to tick on NULL for each column which will allow you to enter the row (as in your script) in the table. – somnath Jun 12 '12 at 04:10

Split column via query to insert extract into another column. Not working

2 Answers2