CODEWITHSUNDEEP
phpmysql
dougmiller
dougmiller

Reputation: 29

Trimming part of a returned field

I have this query on two tables.

    SELECT vcc.name AS item, vcc.prodid, vcc.quantity AS qty, UPPER( vcc.custom_message ) AS nickname, vfd1.value AS fullname, vfd2.value AS email, vcc.purchaseid, vcc.price, (
vcc.quantity * vcc.price
) AS sub
FROM vxu_4_wpsc_cart_contents AS vcc
INNER JOIN vxu_4_wpsc_submited_form_data AS vfd1 ON vcc.purchaseid = vfd1.log_id
AND vfd1.form_id =2
INNER JOIN vxu_4_wpsc_submited_form_data AS vfd2 ON vcc.purchaseid = vfd2.log_id
AND vfd2.form_id =9

Which returns this (image attached)result

Is it even possible to TRIM the 'size' detail (ie Small/Medium/XL etc) from the Item column, so that that potentially sensitive info is not displayed?

The php I'm using to display the query is this:

foreach ( $result as $print )   {
          echo '<tr>';
          echo '<td>' . $print->fullname .'</td>';
          echo '<td>' . $print->nickname .'</td>';
          echo '<td>' . $print->item .'</td>';
          echo '<td>' . $print->qty .'</td>';
          echo '<td>' . $print->email .'</td>';
          echo '<td>' . $print->price .'</td>';
          echo '<td>' . $print->sub .'</td>';
          echo '<td>' . $print->purchaseid .'</td>';
          echo '<td>' . $print->prodid .'</td>';
      echo '</tr>';

Upvotes: 1

Views: 36

Answers (2)

Jan Steinman
Jan Steinman

Reputation: 335

I don't know if you have control over the schema, but it seems to me that combining the size and item name is a denormalization, since size (as well as colour) is an attribute of the item, just as its name is.

So if I had control of the schema, I would split the size (and colour) out into separate columns. Then, it would be trivial to not include the size.

UPDATing the new columns could be done with Barmar's excellent preg_replace() suggestion.

Another alternative would be to use the MySQL fork, MariaDB 10, which has VIRTUAL columns. Then, you could define a new column whose value is derived from other columns, such as using MySQL string functions to extract just the name info.

Upvotes: 0

Barmar
Barmar

Reputation: 780724

You can remove that from the item column before printing it:

$item = preg_replace('/\b(?:Small|Medium|Large|XL|XXL)\b,? */', '', $print->item); // remove size
$item = str_replace(' ()', '', $item); // If there's nothing else in the parentheses, remove it entirely
echo '<td>' . $item . '</td>';

Upvotes: 1

Related Questions