[RESOLVED] Using foreach to create sql statement

[Desbrina]

$insert = "INSERT INTO trade_ids (tradeid, going, cardid) VALUES ":
		foreach($_POST['give'] as $give) {
			if($give != 0) {
				$insert .= "(".$tid.", 'r', ".$give.")"; // Cards going to the receiver
			}
		}
		foreach($_POST['want'] as $want) {
			if($want != 0) {
				$insert .= "(".$tid.", 's', ".$want.")"; // Cards going to the sender
			}
		}

I'm not sure if this is the easiest way, if its not then can someone suggest an easier way?

What i'm trying to do is create an sql statement using a foreach loop. The part i'm having trouble with is the commas between each insert. if i put the comma before the bracket, i get a problem where the statement ends up

INSERT INTO trade_ids (tradeid, going, cardid) VALUES , (".$tid.", 'r', ".$give."), (".$tid.", 's', ".$want.")

if its after, then i get a similar problem, where its after the last one

INSERT INTO trade_ids (tradeid, going, cardid) VALUES  (".$tid.", 'r', ".$give."), (".$tid.", 's', ".$want."), 

is there a way of finding out either if its the first one, then missing of the 1st comma, or if its the last one missing off the last comma?

[laserlight]

This could be one option:

$values = array();
foreach ($_POST['give'] as $give) {
    if ($give != 0) {
        $values[] = "(" . $tid . ", 'r', " . $give . ")"; // Cards going to the receiver
    }
}

foreach ($_POST['want'] as $want) {
    if ($want != 0) {
        $values[] = "(" . $tid . ", 's', " . $want . ")"; // Cards going to the sender
    }
}

if (!empty($values)) {
    $insert = "INSERT INTO trade_ids (tradeid, going, cardid) VALUES " . implode(',', $values);
    // ...
} else {
    // No input.
}

Of course, the incoming variables should be escaped properly. If you use prepared statements instead then perhaps you could just execute a series of statements by rebinding them, wrapping the whole lot in a single transaction.

[Desbrina]

thanks, works perfectly. incoming variables will be escaped. i learnt that the hard way.

[laserlight]

You're welcome 🙂
Remember to mark this thread as resolved using the thread tools.