Comment by j on "How to handle choose all options in a select box on back-end?"

well add an extra mapping table :) n:m :)

CREATE TABLE user_selected_route 
(user_id INT(11), route_id INT(11), 
PRIMARY KEY (user_id, route_id), 
CONSTRAINT fk_my_user_key 
FOREIGN KEY (user_id) REFERENCES user(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_my_route_id
FOREIGN KEY (route_id) REFERENCES route(id)
ON DELETE CASCADE
ON UPDATE CASCADE);

something along the line of this :)

so if the user should have to be able to save multiple of these we would need another table between it.

The database shouldn't matter -- the QUERIES run on it should. You want all you trap it in your server-side language and omit the "WHERE" part (or at least the one relating to that match) in your query string.

Jason Knight he uses SQL that's why I offered a simple SQL example. he need an extra table to persist "all" possible routes. So I answered in terms of SQL I can write you the math equations for the behaviour it if you want ? Than it would'nt matter

Maybe I don't understand what you mean, could you please elaborate but please ;D ... no caps and no swear words ;D they amuse me but I'm trying to get the meaning not the phrases ;D

First off, apologies, wasn't replying directly to you -- confusing mess Hashnode is with TWO text entry boxes on every page; still adjusting. I'm used to just scrolling to the bottom and entering a reply.

But really I'm not sure I understood the OP's question or what you were doing with that massively complex create. Either I completely misunderstood what was being asked, or you guys are MASSIVELY overthinking this.

I think we need more clarification from Aqueel Aboobacker on just what it is he's doing with that select. Is that to select results from a query, or to store that value in the table.

Either way I'd be handling 0 as a special case in the QUERY, not the table.

If it's to query values (php example)

$baseQuery = '
        SELECT *
        FROM whatever
';
if ($_POST['routes'] == 0) $stmt = $db->prepare($baseQuery);
else {
    $stmt = $db->prepare($baseQuery . '
        WHERE routes = :routes
        OR routes = 0
    ');
    $stmt->bindParam(':routes', $_POST['routes']);
} 
$stmt->execute();

Then you just store the zero. "OR" being the magic part of the solution. Then you don't have to screw around with the table structure at all. Just treat index 0 as all.

Jason Knight Gg ah I see this makes more sense.

Well he was asking how to store it not how to select it, and to store it you either have to use a 1:n relation which is not present in the table structure or you store it as an n:m relation since every user can have n possible matches.

I personally don't see this as overly complex but I get your point that you actually could push this in the where clause with the select.

I agree some more clarification would be nice :)

Thank you for answering. You are right. It's not complex. I just want to know whether I'm doing it in the best way. May be I'm over thinking ☺️😇

Search Hashnode