Ask a Question Register Login

PHP Extract Codes From TextArea and Pass to Mysql SELECT IN Query

350

I have the following data which gets submitted in a text area:

new myProduct('', 'bbc_609'),
new myProduct('', '35857'),

What I am aiming to do is extract the codes from the above and pass just the codes to a Mysql Select IN query.

I can successfully get the codes out no problem with the following:

$text = trim($_POST['newfeatured']);
$textAr = explode("\n", $text);
$textAr = array_filter($textAr, 'trim'); // remove any extra \r characters left behind


foreach ($textAr as $line) {
  $g = preg_match("/new myProduct\('', '(.*?)'\)/i", $line, $match);
  $br = $match[1];
  echo "<br>";              
} 
// returns bbc_6093
// returns 5857

However, if I try:

var_dump($br)

... following is returned: 

string(7) "bbc_609"

Or if I try to implode and echo, I get nothing back:

$gr = implode(", ", $br);
echo $gr;
//nothing returns

Therefore, given the above, I will not be able to pass the variable to the Mysql statement:

select product.productid, product.name, product.brand, price.code from product inner join price on product.productid=price.productid 
WHERE product.productid IN('".implode("','",$br)."')"; 
//if I pass in $br in this case.

I have also tried without the foreach loop:

if(isset($_POST['newfeatured'])) {
$g = preg_match_all("/new featuredProduct\('', '(.*?)'\)/i",$_POST['newfeatured'], $match); 
    $g = $match[1];

    $codes = implode(", ", $g); //so now we need to be able to pass $codes to the Mysql correctly.
    //var_dump($codes); returns the data in the array fine.
}

And when I try to pass $codes to the mysql select IN query, no data is returned?

Any help appreciated.

Cheers

145

Answer

Solution:

First about the pattern:

  • You don't need any capture groups just use\K to restart the fullstring match.
  • I'd use'[^']*' on the first/empty single quoted component of your input string just in case some text does fill that position.

About your query:

  • It is unsafe. You must not directly insert user submitted data into a query for security purposes.
  • Here prepared statements with? placeholders are used.
  • Because the number of placeholders/params-to-be-bound are variable, the convolution ofcall_user_func_array() is required.
  • I have also implementedbind_result() to aid in the processing of the resultset.

Untested Code:

$_POST['newfeatured']="new myProduct('', 'bbc_609'),
new myProduct('', '35857'),";

if(preg_match_all("/new (?:my|featured)Product\('[^']*', '\K[^']*/",$_POST['newfeatured'],$prd_ids)){
    $params=$prd_ids[0];  // the fullstring matches
    $count=count($params);  // number of fullstring matches
    $csph=implode(',',array_fill(0,$count,'?'));  // comma-separated placeholders

    $query="SELECT A.productid, A.name, A.brand, B.code
            FROM product A
            INNER JOIN price B ON A.productid=B.productid
            WHERE A.productid IN ($csph);";

    $stmt=$mysqli->prepare($query);  // for security reasons

    array_unshift($params,str_repeat('s',$count));  // prepend the type values string
    $ref=[];  // add references
    foreach($params as $i=>$v){
        $ref[$i]=&$params[$i];  // pass by reference as required/advised by the manual
    }
    call_user_func_array([$stmt,'bind_param'],$ref);    

    $stmt->execute();
    $stmt->bind_result($id,$name,$brand,$code);
    while($stmt->fetch()){
        echo "Whatever you want to do with the results: $id, $name, $brand, $code\n";
    }
    $stmt->close();
}else{
    echo "bonk";
}

People are also looking for solutions to the problem: php - How to recursively walk tree with SimpleXMLIterator?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.