Page 1 of 1

Updating a comma seperated keyword string

Posted: Thu Apr 10, 2008 6:04 am
by ben_sws
~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


Hello all,

I have a 'pages' table and each row contains the information for a page. Each page has relevant keywords associated to it, so I have a column within 'pages' which lists these keywords for each page. The keywords are comma seperated, like this:
Cost Saving, Efficiency, Reporting

What I'm struggling to achieve is the feature of editing which keywords are selected for a page. I have used the same form element from the 'add page' form, and I have successfully 'selected' the keywords which feature within the page's keywords field. This is how:
Note: A list of all keywords is kept within a seperate table.

Code: Select all

<?php $size_query = "SELECT COUNT(word) FROM keywords" ;
    $size_set = mysql_query($size_query, $connection);
    $size_data = mysql_fetch_array($size_set) ; ?>
    <label><strong>Relevant Keyword(s)</strong> (Hold CTRL + Left Click for multiple)<br />
    <select size="<?php echo $size_data[0] ?>" name="keyword_ids[]" tabindex="3" multiple="multiple">
      <?php
        $keyword_query = "SELECT *
                            FROM keywords
                            ORDER BY word ASC";
        $keyword_set = mysql_query($keyword_query, $connection);
        confirm_query($keyword_set);
        while ($keyword_data = mysql_fetch_array($keyword_set)) {
            if (strpos($pg_data['keyword_ids'], $keyword_data['word'])) {
                echo "<option value=\"{$keyword_data["word"]}\" selected=\"selected\"";
                echo ">{$keyword_data["word"]}</option>"; }
            else {
                echo "<option value=\"{$keyword_data["word"]}\"";
                echo ">{$keyword_data["word"]}</option>"; }
                }
      ?>
        </select></label>
So this renders a multi-select box, with all keywords (from the 'keywords' table) as an option. If the keyword is featured within the selected page's keyword field, it selects the option.

Where I'm failing is the update. This is how I try to update the page:

Code: Select all

$keyword_ids = mysql_prep($_POST['keyword_ids']);
$query = "UPDATE pages SET 
                    keyword_ids = \"{$keywords}\"
            WHERE id = {$id}";
            $result = mysql_query($query);
            // test to see if the update occurred
            if (mysql_affected_rows() == 1) {
                // Success!
                $edit = "<strong>The page was successfully updated.</strong>" . "<br /><br />";
            } else {
                $edit = "<strong>The page could not be updated.</strong>";
                $edit .= "<br />" . mysql_error();
        }   }
It doesn't update the keywords, in fact it wipes the field completely.

The following php error is logged:
[10-Apr-2008 12:01:31] PHP Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\wamp\www\bdam\includes\functions.php on line 8

'mysql_prep' is a function I picked up from an amazing tutorial by kevin Skoglund, lynda.com:

Code: Select all

   function mysql_prep( $value ) {
        $magic_quotes_active = get_magic_quotes_gpc();
        $new_enough_php = function_exists( "mysql_real_escape_string" ); // i.e. PHP >= v4.3.0
        if( $new_enough_php ) { // PHP v4.3.0 or higher
            // undo any magic quote effects so mysql_real_escape_string can do the work
            if( $magic_quotes_active ) { $value = stripslashes( $value ); }
            $value = mysql_real_escape_string( $value );
        } else { // before PHP v4.3.0
            // if magic quotes aren't already on then add slashes manually
            if( !$magic_quotes_active ) { $value = addslashes( $value ); }
            // if magic quotes are active, then the slashes already exist
        }
        return $value;
    }
 
        function confirm_query($result_set) {
        if (!$result_set) {
            die("Database query failed: " . mysql_error());
        }
        }
Help!

Many thanks,

Ben


~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Re: Updating a comma seperated keyword string

Posted: Thu Apr 10, 2008 9:52 am
by EverLearning
ben_sws wrote:[10-Apr-2008 12:01:31] PHP Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\wamp\www\bdam\includes\functions.php on line 8
Error above says it all :)

You need to convert keywords array to comma delimited string before you pass it to your mysql_prep() function, like this:

Code: Select all

$keywords = join(', ', $_POST['keyword_ids']);
$keywords  = mysql_prep($keywords);
 

Re: Updating a comma seperated keyword string

Posted: Fri Apr 11, 2008 4:49 am
by ben_sws
Many thanks, that's cracked it!

MODS: Sorry about the improper use of code tag.

Thanks,

Ben