Jump to content

Check whether values from array exist in column...


Kristoff1875
Go to solution Solved by mac_gyver,

Recommended Posts

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.

Link to comment
Share on other sites

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 by dalecosp
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

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?!

Link to comment
Share on other sites

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')
*/
Link to comment
Share on other sites

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 by Kristoff1875
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.