PHP MYSQL Configuring a Update Statement

Question

I have the following if statement that works fine for an insert but I cannot seem to get it to only update the table with items that are not inserted:

Insert:

        for($i=0; $i<count($fields['Occupation']); $i++) {
            $id = $fields['Occupation'][$i];

           CMS::insertQuery("INSERT INTO {table} SET canid=?, categoryid=?", array($emailCheck['id'], $id));
        }
            echo 'found update';

I have tried the following update line with no luck.

CMS::updateQuery("UPDATE {refocus_candidate_category} SET canid=?, categoryid=? WHERE canid=? AND categoryid=?", array($emailCheck['id'], $id));

Full Statement:

$catParams = array_merge(array($emailCheck['id']), $fields['Occupation']);
$catPlaceholders = '?'.str_repeat(',?',count($fields['Occupation'])-1);

$catCheck = CMS::selectQuery("SELECT * FROM {table} WHERE canid=? AND categoryid IN (".$catPlaceholders.")", $catParams);

if($catCheck != FALSE)
{
    for($i=0; $i<count($fields['Occupation']); $i++) {
        $id = $fields['Occupation'][$i];

        CMS::updateQuery("UPDATE {table} SET canid=?, categoryid=? WHERE canid=? AND categoryid=?", array($emailCheck['id'], $id));
    }
        echo 'found update'l
}else{
    for($i=0; $i<count($fields['Occupation']); $i++) {
        $id = $fields['Occupation'][$i];

        CMS::insertQuery("INSERT INTO {table} SET canid=?, categoryid=?", array($emailCheck['id'], $id));
    }
    echo 'else insert';
}

You have four parameters in your update query but you are providing only two values..? — heretolearn
– heretolearn, Commented Sep 21, 2012 at 21:36

Alexander Larikov · Accepted Answer · 2012-09-21 21:30:12Z

1

My guess you need something like following code:

CMS::updateQuery("UPDATE {refocus_candidate_category} SET canid=?,
                  categoryid=? WHERE canid=? AND categoryid=?",
                  array($emailCheck['id'], $id, $emailCheck['id'], $id)
                );

Basically you need as many array elements as ? in your UPDATE statement

answered Sep 21, 2012 at 21:30

Alexander Larikov

2,33915 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jess McKenzie Over a year ago

Thanks alot, but currently it will not insert any new selected options - keep the current inserted items but update with any new selected items. How could I make it do this

qwertymk · Accepted Answer · 2012-09-21 21:29:24Z

0

Seems like you're two array items short:

CMS::updateQuery("UPDATE {refocus_candidate_category} SET canid=?, categoryid=? WHERE canid=? AND categoryid=?", array(
    emailCheck['id'], $id, ,'canid', 'cat_id number here');
);

answered Sep 21, 2012 at 21:29

qwertymk

35.6k31 gold badges127 silver badges184 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL Configuring a Update Statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related