#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    53
    Rep Power
    16

    [HELP] PHP Script Only Updates MySQL Database If Value Is An Integer


    Here is the code used to create the database:
    PHP Code:
    $sql "
    CREATE TABLE "
    .$database_prefix."_preferences (
    Name CHAR(100) NOT NULL,
    Value TEXT NOT NULL);"
    ;
    $query mysql_query($sql);
    if (
    $query) {
    echo(
    "Preferences table created! (".$database_prefix."_preferences)<br>");
    } else {
    echo(
    "Error creating table \"".$database_prefix."_preferences\"<br>");
    }
    $sql 'INSERT INTO `newsletter_preferences` ( `Name` , `Value` ) VALUES ( \'header\', \'\' ); ';
    $query mysql_query($sql);
    if (
    $query) {
    echo(
    "Query successfully added to table! (".$database_prefix."_preferences)<br>");
    } else {
    echo(
    "Query not added to table. \"".$database_prefix."_preferences\"<br>");
    }
    $sql 'INSERT INTO `newsletter_preferences` ( `Name` , `Value` ) VALUES ( \'footer\', \'\' ); ';
    $query mysql_query($sql);
    if (
    $query) {
    echo(
    "Query successfully added to table! (".$database_prefix."_preferences)<br>");
    } else {
    echo(
    "Query not added to table. \"".$database_prefix."_preferences\"<br>");
    }
    $sql 'INSERT INTO `newsletter_preferences` ( `Name` , `Value` ) VALUES ( \'issue_number\', \'1\' ); ';
    $query mysql_query($sql);
    if (
    $query) {
    echo(
    "Query successfully added to table! (".$database_prefix."_preferences)<br>");
    } else {
    echo(
    "Query not added to table. \"".$database_prefix."_preferences\"<br>");
    }
    $sql 'INSERT INTO `newsletter_preferences` ( `Name` , `Value` ) VALUES ( \'from_name\', \'Name of Website\' ); ';
    $query mysql_query($sql);
    if (
    $query) {
    echo(
    "Query successfully added to table! (".$database_prefix."_preferences)<br>");
    } else {
    echo(
    "Query not added to table. \"".$database_prefix."_preferences\"<br>");
    }
    $sql 'INSERT INTO `newsletter_preferences` ( `Name` , `Value` ) VALUES ( \'from_email\', \'noreply@yoursite.com\' ); ';
    $query mysql_query($sql);
    if (
    $query) {
    echo(
    "Query successfully added to table! (".$database_prefix."_preferences)<br>");
    } else {
    echo(
    "Query not added to table. \"".$database_prefix."_preferences\"<br>");
    }
    $sql 'INSERT INTO `newsletter_preferences` ( `Name` , `Value` ) VALUES ( \'newsletter_subject\', \'Newsletter Subject\' ); ';
    $query mysql_query($sql);
    if (
    $query) {
    echo(
    "Query successfully added to table! (".$database_prefix."_preferences)<br>");
    } else {
    echo(
    "Query not added to table. \"".$database_prefix."_preferences\"<br>");
    }
    $sql 'INSERT INTO `newsletter_preferences` ( `Name` , `Value` ) VALUES ( \'admin_name\', \'Administrator Name\' ); ';
    $query mysql_query($sql);
    if (
    $query) {
    echo(
    "Query successfully added to table! (".$database_prefix."_preferences)<br>");
    } else {
    echo(
    "Query not added to table. \"".$database_prefix."_preferences\"<br>");
    }
    $sql 'INSERT INTO `newsletter_preferences` ( `Name` , `Value` ) VALUES ( \'admin_email\', \'you@yoursite.com\' ); ';
    $query mysql_query($sql);
    if (
    $query) {
    echo(
    "Query successfully added to table! (".$database_prefix."_preferences)<br>");
    } else {
    echo(
    "Query not added to table. \"".$database_prefix."_preferences\"<br>");
    }
    $sql 'INSERT INTO `newsletter_preferences` ( `Name` , `Value` ) VALUES ( \'auto_delete\', \'1\' ); ';
    $query mysql_query($sql);
    if (
    $query) {
    echo(
    "Query successfully added to table! (".$database_prefix."_preferences)<br>");
    } else {
    echo(
    "Query not added to table. \"".$database_prefix."_preferences\"<br>");

    And here is the code that updates this database.
    PHP Code:
    <?
    require_once("/home/leafs/public_html/newsletter/config.php");
    $dbcnx_newsletter mysql_connect($database_server,$database_username,$database_password);
    mysql_select_db($database_name);
    if (
    $_POST) {
    $name "issue_number";
    $value $_POST['issue_number'];
    $sql 'UPDATE newsletter_preferences SET Value = '.$value.' WHERE Name = \''.$name.'\'';
    $result mysql_query($sql);

    $name "from_name";
    $value $_POST['from_name'];
    $sql 'UPDATE newsletter_preferences SET Value = '.$value.' WHERE Name = \''.$name.'\'';
    $result mysql_query($sql);

    $name "from_email";
    $value $_POST['from_email'];
    $sql 'UPDATE newsletter_preferences SET Value = '.$value.' WHERE Name = \''.$name.'\'';
    $result mysql_query($sql);

    $name "newsletter_subject";
    $value $_POST['newsletter_subject'];
    $sql 'UPDATE newsletter_preferences SET Value = '.$value.' WHERE Name = \''.$name.'\'';
    $result mysql_query($sql);

    $name "admin_name";
    $value $_POST['admin_name'];
    $sql 'UPDATE newsletter_preferences SET Value = '.$value.' WHERE Name = \''.$name.'\'';
    $result mysql_query($sql);

    $name "admin_email";
    $value $_POST['admin_email'];
    $sql 'UPDATE newsletter_preferences SET Value = '.$value.' WHERE Name = \''.$name.'\'';
    $result mysql_query($sql);

    $name "auto_delete";
    $value $_POST['auto_delete'];
    $sql 'UPDATE newsletter_preferences SET Value = '.$value.' WHERE Name = \''.$name.'\'';
    $result mysql_query($sql);
    }

    $sql 'SELECT * FROM newsletter_preferences WHERE Name = \'issue_number\'';
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    $issue_number $row[Value];

    $sql 'SELECT * FROM newsletter_preferences WHERE Name = \'from_name\'';
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    $from_name $row[Value];

    $sql 'SELECT * FROM newsletter_preferences WHERE Name = \'from_email\'';
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    $from_email $row[Value];

    $sql 'SELECT * FROM newsletter_preferences WHERE Name = \'newsletter_subject\'';
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    $newsletter_subject $row[Value];

    $sql 'SELECT * FROM newsletter_preferences WHERE Name = \'admin_name\'';
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    $admin_name $row[Value];

    $sql 'SELECT * FROM newsletter_preferences WHERE Name = \'admin_email\'';
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    $admin_email $row[Value];

    $sql 'SELECT * FROM newsletter_preferences WHERE Name = \'auto_delete\'';
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    $auto_delete $row[Value];

    echo(
    "<form method=\"POST\">
    Issue Number:<br><input type=\"text\" name=\"issue_number\" size=\"20\" value=\""
    .$issue_number."\"><br><br>
    From Name:<br><input type=\"text\" name=\"from_name\" size=\"20\" value=\""
    .$from_name."\"><br><br>
    From Email:<br><input type=\"text\" name=\"from_email\" size=\"20\" value=\""
    .$from_email."\"><br><br>
    Newsletter Subject:<br><input type=\"text\" name=\"newsletter_subject\" size=\"20\" value=\""
    .$newsletter_subject."\"><br><br>
    Administrator Name:<br><input type=\"text\" name=\"admin_name\" size=\"20\" value=\""
    .$admin_name."\"><br><br>
    Administrator Email:<br><input type=\"text\" name=\"admin_email\" size=\"20\" value=\""
    .$admin_email."\"><br><br>
    Auto-Delete:<br><input type=\"text\" name=\"auto_delete\" size=\"20\" value=\""
    .$auto_delete."\"><br><br>
    <input type=\"submit\" value=\"Save\" name=\"save\">
    </form>"
    );
    mysql_close($dbcnx_newsletter);
    ?>
    I can't figure this out, but this update script I created doesn't update the values unless they are numbers.

    Any help would be greatly appreciated.
  2. #2
  3. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,349
    Rep Power
    65
    When you're inserting, updating or deleting info in a table and the filed is not a number, they must be encased in quotes. For example

    PHP Code:
    $sSQL "update table1 set textfield1='value1' where textfield2='value2'"
    If you try

    PHP Code:
    $sSQL "update table1 set textfield1=value1 where textfield2='value2'"
    and textfield1 is a text or varchar field, the update will not happen because this syntax will only work for a field that is a number type. Hope that helps.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Ireland
    Posts
    91
    Rep Power
    18
    And don't forget the dollar $ before the variable names

IMN logo majestic logo threadwatch logo seochat tools logo