Trying to extract 5 characters from a column when adding record mysql via php

Question

My part_no column has the following format: 000-00000-00 for all records.

I need to extract the five middle characters from part_no and place it in the core column when I create the record.

I can't get my script to work.

I'm not getting any errors. Just not working.

$order = "INSERT INTO cartons_added (add_time, type, part_no, add_type, add_qty, add_ref, add_by, add_notes)
        VALUES
        ('$date',
        '$_POST[type]', 
        '$_POST[part_no]', 
        '$_POST[add_type]', 
        '$_POST[add_qty]', 
        '$_POST[add_ref]', 
        '$_POST[add_by]', 
        '$_POST[add_notes]')";

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

Your code is vulnerable to SQL Injection. [Read this asap](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) — Mike B, Jun 11 '12 at 00:43
Please tell us the structure of your `cartons_current` table. — Okonomiyaki3000, Jun 11 '12 at 00:44

score 1 · Answer 1 · answered Jun 11 '12 at 00:48

You can update your cartons_current table based on your cartons_added table with something like:

INSERT INTO cartons_current(core) 
SELECT SUBSTR(part_no, 5, 5) FROM cartons_added

You will probably want to limit that with a WHERE clause or maybe deal with what happens when this value is already in cartons_current (use either INSERT IGNORE or ON DUPLICATE KEY UPDATE)

score 0 · Answer 2 · answered Jun 11 '12 at 00:42

Try removing this

$res = mysql_result($sel);

And change your while to reference the main query resource

while($row = mysql_fetch_row($sel)) {

I don't understand your logic with your tables though. You're inserting data into the cartons_added table but then you're selecting from cartons_current?

Also, split is deprecated as of PHP 5.3.0

score 0 · Answer 3 · answered Jun 11 '12 at 00:43

0

You said five middle "characters", so I'd add quotes around your variable like so:

$query3 = "insert  into cartons_current(core) values('$core')";

(Also, there's only about a gazillion answers on SO about SQL injection, and using pdo)

answered Jun 11 '12 at 00:43

GDP

8,109
6
45
82

score 0 · Answer 4 · answered Jun 11 '12 at 00:45

0

INSERT INTO cartons_current(core) 
SELECT 
  substr(part_no,position('-' IN part_no)+1,position('-' IN substr(part_no,position('-' IN part_no)+1))-1)
FROM cartons_added;

answered Jun 11 '12 at 00:45

Eugen Rieck

64,175
10
70
92

This is pretty good. Although an ugly query, it accounts for the possibility that the format of `part_no` may change slightly. – Okonomiyaki3000 Jun 11 '12 at 00:57
The query gets slightly less ugly if you remember, that MySQL will calculate each function oly once per row, i.e. `substr(part_no,position('-' IN part_no)` will be calculated only once, although it is used 3 times – Eugen Rieck Jun 11 '12 at 01:17
I mean it's not so easy on the eyes (this can't be helped, it's SQL) not that it would give bad performance. I'm not criticizing, this is actually a very good way to do it. – Okonomiyaki3000 Jun 11 '12 at 01:30

Fong-Wan Chau · Accepted Answer · 2012-06-11T18:31:47.593

You are right, the script has no error.

I think the problem is on your SQL that made you can't insert a new row, specifically on the table structure. Maybe you defined a PRIMARY KEY without AUTO_INCREMENT, defined a INDEX or UNIQUE key that is not the core key or there have some other key that did not have default value. Remember that you can't insert a row without defining all required field.

You script is selecting all part_no and for every part_no you are inserting a new row in the same table, so maybe there is the problem.

I think what you want is update every result to add they core value, you can do that with UPDATE as this code:

function getValue($value) {
  return "'" . trim(mysql_real_escape_string($value)) . "'";
}

mysql_query('INSERT INTO `cartons_added` (`add_time`, `type`, `part_no`, `add_type`, `add_qty`, `add_ref`, `add_by`, `add_notes`)
             VALUES (' . 
              getValue($date) . ', ' .
              getValue($_POST[type]) . ', ' .
              getValue($_POST[part_no]) . ', ' .
              getValue($_POST[add_type]) . ', ' .
              getValue($_POST[add_qty]) . ', ' .
              getValue($_POST[add_ref]) . ', ' .
              getValue($_POST[add_by]) . ', ' .
              getValue($_POST[add_notes]) . 
             ')');

$partNoQuery = mysql_query('SELECT `part_no` FROM `cartons_current`');

while($partNoResult = mysql_fetch_assoc($partNoQuery)) {
    list($prefix, $core, $suffix) = explode('-', $partNoResult['part_no']);
    mysql_query('UPDATE cartons_current SET `core` = \'' . $core . '\' WHERE `part_no` = \'' . $partNoResult['part_no'] . '\'');
}

I added getValue function to escape posted data to prevent SQL injection.

Trying to extract 5 characters from a column when adding record mysql via php

5 Answers5

Linked