Level 104
What are you doing?
Is this a Laravel project?
Jun 8, 2021
8
Level 6
How can I make an SQL INSERT with a conditional?
If I want to do:
$sql = "INSERT INTO u_spares ($sql_left) VALUES ($sql_right)";
but if only my variable does not equal the value of my table column already.
$sql_left = 'is_active, ';
$sql_left .= 'date_added, ';
$sql_left .= 'date_modified, ';
$sql_left .= 'client_id, ';
$sql_left .= 'league_id, ';
$sql_left .= 'player_id, ';
$sql_left .= 'game_id, ';
$sql_left .= 'team_id';
$sql_right = "'$is_active', ";
$sql_right .= "'$date_added', ";
$sql_right .= "'$date_modified', ";
$sql_right .= "'$client_id', ";
$sql_right .= "'$league_id', ";
$sql_right .= "'$player_id', ";
$sql_right .= "'$game_id', ";
$sql_right .= "'$team_id'";
So insert only if my value $game_id does not equal to the value of the game_id column?
Level 6
No just vanilla PHP, no framework. It's a CRUD Manager basically. For a sports league management system.
Level 104
PDO then?
So insert only if my value $game_id does not equal to the value of the game_id column?
This can be handled either with a read query for the given $game_id, or (if there is a UNIQUE index on the game_id column) by try/catch
Level 6
I'm using MYSQLi.
Would you mind giving a brief example of the syntax?
I basically need to INSERT on the condition that game_id and client_id are not both duplicates in any row. So if that combination of game_id and client_id exist, don't insert.
Level 6
Something like this?
$sql = "INSERT INTO u_spares ($sql_left) VALUES ($sql_right) WHERE NOT EXISTS (SELECT * FROM u_spares WHERE 'game_id' = $game_id AND 'client_id' = $client_id)";
Level 6
I think I found my problem: INSERT statements only support two syntaxes (uses values and uses a query) you can't combine them. Can anyone help me write my query then?
Level 5
do you need game_id to be unique? because you will need to make the game_id field unique in the DB then you could do INSERT IGNORE which will basically not do it if the game_id is already been used before like so:
$sql = "INSERT INGORE INTO u_spares ($sql_left) VALUES ($sql_right)";
Level 6
Doesn't insert ignore only work for duplicate primary keys? I need it for game_id and client_id. How would I write that?
Please or to participate in this conversation.