通过PHP添加记录MySQL时尝试从列中提取5个字符 [英] Trying to extract 5 characters from a column when adding record mysql via php

问题描述

我的part_no列具有以下格式:000-00000-00用于所有记录.

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

创建记录时，我需要从part_no中提取五个中间字符并将其放在core列中.

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);
}

推荐答案

是的，脚本没有错误.

我认为问题出在您的SQL上，导致您无法插入新行，尤其是在表结构上.也许您定义了不带AUTO_INCREMENT的PRIMARY KEY，定义了不是core键的INDEX或UNIQUE键，或者还有一些其他键没有默认值.请记住，如果不定义所有必填字段，则无法插入行.

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.

您的脚本将选择所有part_no，并且对于每个part_no，您都将在同一表中插入新行，因此可能存在问题.

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.

我认为您想要的是更新每个结果以添加它们的core值，您可以使用UPDATE作为以下代码来做到这一点:

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'] . '\'');
}

我添加了getValue函数以转义发布的数据以防止SQL注入.

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

这篇关于通过PHP添加记录MySQL时尝试从列中提取5个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！