Explode, write, and then MYSQL UPdate

[davieboy]

Ok here goes

Ive got a shop, all my sizes are stored as
SIZE=STOCK
eg

3=1,5=3,6=3,7=2,8=1

Now im trying to update the stock, where a particular size is sold.

As in, i sell 1 of size 6, so i want to update 6=3, to 6=2 and leave the rest of the field intact.

Totally lost with how to do it, brain freeze

Any help would be much appreciated.

David

[Weedpacket]

The real solution (option A) would be to fix your database schema and then you wouldn't have to be doing this in the first place. Then it would be a straightforward UPDATE statement.

If you have to stick with using PHP to do the database's job for it then option B is .... well, you largely answered your own question: read the field, explode, make the changes, implode, and then update.

Or (option C) you could get fancy with the DBMS's string functions to locate substrings like ",6=" then the number after that (everything up to the next ",") would be the quantity associated with item six; onve you've got that you'd cast it as an integer, subtract one, cast it back into a string, concatenate it with the rest of the field's contents (everything up to and including the ",6=" and everything from the next "," on, but not anything in between because that's the quantity being updated), and the result of that would be the updated field value (of course I glossed over some edge conditions: like if the item is the first or the last one in the list then one of the commas will be missing (that can be fixed bymaking sure that the entire field begins and ends with a ","), or the possibility that the searched-for item ID isn't in the list....)

I'd go for option A, myself.

[davieboy]

Untill i can redo the database its going to have to be option b sadly

Can you advise at all on the best option using b

Dvaid

[Weedpacket]

1. Get the current value, and use explode(','...) to turn it into an array.

2. For each element of that array:

   1. use explode('=',...) to separate the id and the quantity

   2. if the ID is for an item you need to adjust the quantity for, then do so.

   3. Put the ID and quantity back together with a '=' in between.

   4. Replace the current element in the array with this updated element

3. Use implode(',', ...) to undo the explode.

4. Put the resulting string into the database.

[davieboy]

Think I getthe drift

will have a shot and post the results

thanks weedpacket 🙂

[davieboy]

ok ive got

$size22 = $size[0];
$filtered = explode(',',$size22);

print_r($filtered);
foreach($filtered as $key => $value){

$stockcheck = explode('=',$filtered);
print_r($stockcheck);
}

so far, but its not giving values in the foreach array

Array ( [0] => Me= 2 [1] => La= 2 ) Array ( [0] => Array ) Array ( [0] => Array )

the result should be
Me=2
La=2

[Weedpacket]

Check which variable you're exploding inside the loop. Bet you it shouldn't be $filtered 🙂

[davieboy]

lolz
epic failure to dave 😉

now getting

Array ( [0] => Me [1] => 2 )

Array ( [0] => La [1] => 2 )

which is progress.
Ive been looking at the array_search function to search the array, but not really having much luck. is it the right function to be looking for?

I do appologise, im a bit confuzzled at this.

[Weedpacket]

Building a new array, using the keys as keys:

$stockcheck=explode('=', $value);
$stock[$stockcheck[0]] = $stockcheck[1];

would make an array that's easier to search.

Incidentally, as long as this is working you'll never have a chance to redo the database... 🙂

[davieboy]

Thanks for that weedpacket, what would i then do to search that array?

David

[Weedpacket]

[man]isset/man

[Weedpacket]

If you want to increase Me by 1, then

$key='Me'; // Wherever that really comes from...
if(isset($array[$key]))
{
    // It's already there; just increase it by 1
    $array[$key]++;
}
else
{
    // Put it in since it's not there already
    $array[$key] = 1;
}

To turn that back into a (snort, hack, pptpt) comma-separated list to go in the array

$pairs = array(); // Please use better variable names than I am!
foreach($array as $key=>$value)
{
    $pairs[] = "$key=$value";
}
$string = join(",", $pairs);

[johanafm]

davieboy;10934229 wrote:

Untill i can redo the database its going to have to be option b sadly
Dvaid

When would that be? Each time you need to do anything at all with this information, you will end up doing so suboptimally. Also, every single time, you will have to spend more time developping work arounds for flawed db design than it would have taken to correct the issue in the first place. Do yourself a favor and correct the issue now.

Unless you charge them by the hour and they don't care 😉

[davieboy]

Stupid question then

Whats the best way to structure a DB for ashop with products and different sizes / colours for each product?

David

[johanafm]

CREATE TABLE product (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(),
other generic stuff specific for this product
)

CREATE TABLE size (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_id, INT UNSIGNED,
size VARCHAR(),
FOREIGN KEY (product_id) REFERENCES product(id)
)

CREATE TABLE color (
as above
)

CREATE TABLE stock (
product_id,
color_id,
size_id,
units INT UNSIGNED,
PRIMARY KEY(product_id, color_id, size_id)
)

If the price is dependt on the product alone, then price goes into the product table. If the price depends on the product's size, it goes into the size table.

// Using a prepared statement, your update would look like something like this
$qry = "UPDATE stock Set units = units - 1
WHERE product_id = ? AND color_id = ? AND size_id = ?";

// otherwise you might
$qry = sprintf("UPDATE stock Set units = units - 1
WHERE product_id = %d AND color_id = %d AND size_id = %d", $prod, $color, $size);

To get all products with sizes and colors, as well as the amount in stock

SELECT p.id, p.name, c.color, s.size, stock.units
FROM product AS p
INNER JOIN color AS c ON p.id = c.product_id
INNER JOIN size AS s ON p.id = s.product_id
INNER JOIN stock ON p.id = stock.product_id AND c.id = stock.color_id AND s.id = stock.size_id