Check whether values from array exist in column...

[Kristoff1875]

Hi guys, before I get to the main part of my question, I first wanted to ask whether there is a maximum number of values you should store in an array. If for example you have 5000 boxes that people can choose from and eventually all 5000 boxes will be chosen, is it save to store all of the chosen boxes in an array? For example box1,box5,box7,box21 are already chosen, then someone chooses box2,box3,box4 and it adds to the same array, is that feasible with up to 5000 values in total? My thoughts on it were that it would probably be more feasible than giving each box it's own column!?

 

Secondly, assuming the above is ok, how would I go about ensuring nobody could choose a box already chosen? Basically, if somebody chooses box3 and box4, how can I check that these don't exist in the column before adding them so they don't duplicate?

 

Hope this makes some sense and isn't just gobblydeegook!

 

Finally, if somebody in the Users table chooses box3 and box4 from SaleID5, what would be the best way of storing what they've chosen in the User table to use the data when they're viewing what they've got?!

 

Many thanks in advance guys.

[dalecosp]

Are we talking user interface here?

This sentence:

 

 

 

how would I go about ensuring nobody could choose a box already chosen?

sounds a bit like that; and for web forms these days it's usually handled in Javascript (then double-checked in {PHP} after submission so it doesn't screw up the server).

As to maximum values to store in an array ... I suppose there's a working limit.  Do you have an alternate plan for storing these values?  The thing to do would be test both implementations to see if there was any performance hit/gain from one or more of the proposed storage solutions.

Edited December 19, 2013 by dalecosp

[Kristoff1875]

Nope, i'm talking more backend stuff. The idea is, depending on which SaleID is opened by the user, the page then calls all of the already taken boxes from the database and assigns them to the boxes on the site and as such says "This one is taken" and even if they try to choose it, it won't let them. What I am more meaning is as you say, double checking that they aren't somehow trying to add a box that someone else already has. Obviously that shouldn't be an issue if the UI works correctly, but just as a precaution!

[mac_gyver]

each piece of data, i.e. one box number, would be stored in one row in your database table. 5000 pieces of data = 5000 rows in the table holding that data. you can enforce uniqueness by assigning a unique key to the column holding the data. any attempt to insert or update the data to a value already in use would produce a query error.

 

the table would have id, box_number columns.

[Kristoff1875]

So if one person got 5 boxes, I'd have to insert each one as a unique row as:

 

UserID, SaleID, BoxID

 

Makes sense I suppose as I would then say something like:

SELECT * FROM BoxesTaken WHERE SaleID = XXXXX

To show all of the boxes that have been taken?

[Kristoff1875]

Could you guys point me in the direction for this by any chance?

 

I have the value of boxes selected being passed as:

 

box1,box2,box3

 

But no matter what I do, I can't seem to get this to insert to the database with a foreach statement. Here's what i've got:

$Selected = $_POST['Selected'];

$Selected1 = explode(',', $Selected);
$Selected2 = implode(',', $Selected);


$v1="'" . $db->real_escape_string('UserID') . "'";

foreach($Selected as $Selection){
	
    $sql = "INSERT INTO Selected (BoxID) VALUES ('$Selection')";

I've tried any mixture of these, including foreach($Selected1 as $Selection) and foreach($Selected 2 as $Selection) but don't seem to be getting anywhere. Anybody got any pointers for me please?!

[Barand]

here's an example

$Selected = $_POST['Selected']; // eg "Box1,Box2,Box3"

$Selected1 = explode(',', $Selected);

// assuming userid and sale id are numeric eg sample values
$userid = 123;
$saleid = 456;

$data = array();
foreach($Selected1 as $Selection){
    $data[] = sprintf ("(%d, %d, '%s')", intval($userid), intval($saleid), $db->real_escape_string($Selection));
}

$sql = "INSERT INTO Selected (UserID, SaleID, BoxID) VALUES \n" . join (',', $data);
echo $sql; 
/* outputs *** 
INSERT INTO Selected (UserID, SaleID, BoxID) VALUES 
(123, 456, 'Box1'),(123, 456, 'Box2'),(123, 456, 'Box3')
*/

[Kristoff1875]

Excellent, thank you!

[Kristoff1875]

Is there a way of making sure the row with the same BoxID doesn't exist in the database already? Would that be something to do with unique?

Edited December 22, 2013 by Kristoff1875

[Barand]

Yes, but it would depend on the rules for "exists already"

 

is it

• a box can only ever be selected once?
• several users can select the same box but cannot select same box more than once?
• users can select the same box more than once but only once per saleID?
• etc

[Kristoff1875]

The BoxID can only be selected once on each SaleID. I'm working on making the selection process disable on the front end, but want to double it up so if it does somehow happen, they definitely can't add it to the database and get an error.

[Barand]

Add a UNIQUE key on (SaleID, BoxID) to your table

[Kristoff1875]

Ah, so simple! Thanks. Is there a way to check for the:

 

Fatal error: Wrong SQL: INSERT INTO Selected ..... Error: Duplicate entry

 

 

Message and then use a custom error message? I'm searching google for this currently, just thought I'd ask here too.

Thanks again for your help!

 

------------
 

Ok, so it's this part:

if($db->query($sql) === false) {
  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $db->error, E_USER_ERROR);
} else {
 PRINT "Inserted"; 
}

Showing the error, so can I break this down in to different error pages?

Edited December 22, 2013 by Kristoff1875

[Barand]

You could check prior to insert like this

$Selected = $_POST['Selected']; // eg "Box1,Box2,Box3"

$Selected1 = explode(',', $Selected);
$saleid = 456;

//
// check for duplicates
//
$boxlist = join ("','", $Selected1);
$sql = "SELECT BoxID FROM Selected
        WHERE SaleID = $saleid AND BoxID IN ('$boxlist')";
$res = $db->query($sql);
if ($res->num_rows > 0) {
    // dupes found
    echo "SaleID: $saleid<br>";
    while ($row = $res->fetch_row()) {
        echo "Duplicated box: {$row[0]}<br>";
    }
}
else {
    //  OK to process inserts here
}

If you do try to insert a duplicate, the error number will be 1062 after attempting the insert

[mac_gyver]

a little bit off topic, but your $_POST['Selected']; // eg "Box1,Box2,Box3" value indicates your user interface requires someone to either type or copy/paste a list of choices or you have a bunch of code to produce that list from whatever is being selected by the user.

 

what you should have are checkboxes, using an array name for the form field, so that all the user needs to do is check the box(es) they want and submit the form. unavailable boxes would either not be shown at all or would be shown as disabled when you dynamically produce the form based on the possible choices and the choices already picked.

[Kristoff1875]

It's not actually check boxes, the values are from DIV's being set as selected when clicked. I also gave up with the extra errors and added a standard error message as they shouldn't be able to get to that part anyway.

Edited December 22, 2013 by Kristoff1875