SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast Adelante's Avatar
    Join Date
    Sep 2006
    Location
    Queensland Australia
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble setting uo an UPDATE query in PHP/MYSQL

    I'm taking my first steps in MySQL and having a lot of fun till I tried to set up an update script. I've read the query structure and seen a few examples but haven't been able to get it to work. The MySQL version is 5.1. here's the query code that have at present:

    Code:
    $sql = "UPDATE $table SET 
    	date='$date', 
        	heading='$heading',
        	image='$image',
        	excerpt='$excerpt',
        	content='$content',
        	status='$status'
        	WHERE 'index'=$id";
    //perform edit query
     mysql_select_db($database_dsSTC, $dsSTC);
     mysql_query($sql);
    I don't get any erros but the record doesn't update either. I hope someone can spot my error. Many thanks in advance.

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Adelante View Post
    I'm taking my first steps in MySQL and having a lot of fun till I tried to set up an update script. I've read the query structure and seen a few examples but haven't been able to get it to work. The MySQL version is 5.1. here's the query code that have at present:

    Code:
    $sql = "UPDATE $table SET 
        date='$date', 
            heading='$heading',
            image='$image',
            excerpt='$excerpt',
            content='$content',
            status='$status'
            WHERE 'index'=$id";
    //perform edit query
     mysql_select_db($database_dsSTC, $dsSTC);
     mysql_query($sql);
    I don't get any erros but the record doesn't update either. I hope someone can spot my error. Many thanks in advance.
    You've got a couple of ' in the wrong place, it should be:

    Code SQL:
    $sql = "
        UPDATE
            '$table'
        SET
            date='$date'
            , heading='$heading'
            , image='$image'
            , excerpt='$excerpt'
            , content='$content'
            , status='$status'
        WHERE
            index='$id'";

    When passing a query to the MySQL extension all php variables need to have ' around them.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try taking away the single quotes from 'index' in your where clause.
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SpacePhoenix
    When passing a query to the MySQL extension all php variables need to have ' around them.
    Only strings need to be quoted.

    Replace the quotes around index with back ticks.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    You've got a couple of ' in the wrong place, it should be:

    Code SQL:
    $sql = "
        UPDATE
            '$table' 
         ...
         "
    When passing a query to the MySQL extension all php variables need to have ' around them.
    but shirley, not the table name
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Only strings need to be quoted.

    Replace the quotes around index with back ticks.
    Every example that I've ever seen has always had the php variables surrounded my ' when in a query. Even the examples in the php manual (php.net) are like that.

    @Adelante

    r937 brought up a good point, why is the table name a variable? Should it not be the same table involved every time that particular query is run?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SitePoint Enthusiast Adelante's Avatar
    Join Date
    Sep 2006
    Location
    Queensland Australia
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks everyone for your help so far. I've tried the suggestion from Space Phoenix but it does the same thing, no error from the query but no update either. It has me stummped.

    I had the script echo the query and have pasted it in PHP myAdmin, the query works fine so I'm wondering if there is something else wrong. The script processes a form where a new record can be created or edited, the form has two html area wysiwyg editors plus a few text fields. The new record side of things works fine.

    This is the whole script as it currently stands:

    Code:
    <?php
    // CHECKS FIRST TO SEE IF MAGIC QUOTES IS INSTALLED
    function myAddSlashes( $string ) {
    		if (get_magic_quotes_gpc()==1) {
    			return ( $string );
    		} else {
    			return ( addslashes ( $string ) );
    		}
    }
    require_once('../Connections/dsSTC.php'); 
    //set variables 
    $id = $_POST[id];
    $table = $_POST[table];
    $edit = $_POST[edit];
    $date = $_POST[date];
    //Add slashes if needed
    $heading = myAddSlashes($_POST[heading]);
    $image = myAddSlashes($_POST[image_filename]);
    $excerpt = myAddSlashes($_POST[elm1]);
    $content = myAddSlashes($_POST[elm2]);
    $status = $_POST[status];
    //Construct mySQL query
    if ($edit == 'y') {
    $sql = "UPDATE $table SET `date` = '$date', `heading` = '$heading', `image` = '$image', `excerpt` = '$excerpt', `content` = '$content', `status` = '$status' WHERE `index` = '$id'";
    	echo $sql;
    } else {
    //New record query
    $sql="INSERT INTO $table (date, heading, image, excerpt, content, status) VALUES ('$date', '$heading', '$image', '$excerpt', '$content', '$status')";
    //perform query
    mysql_select_db($database_dsSTC, $dsSTC);
    if (mysql_query($sql))
    { 
    //worked like a charm
    	echo '<p>Record from table: '.$table.'&nbsp;&nbsp; with record Index No: '.$id.' Added/Updated </p>';
    	} else {
    //somthing stuffed up
    		exit( '<p>Error adding/editing record!: '.
    			mysql_error() . '</p>'); 
    	}
    }
    
    //Go back to index			
    echo'<a href="index.php">Return</a>';
    ?>
    I must be missing somthing?

    Hi Rudy, congrats on your fine book, I purchased one as soon as they came out, it's inspired me to start playing with mySQL (and getting less sleep).

  8. #8
    SitePoint Enthusiast Adelante's Avatar
    Join Date
    Sep 2006
    Location
    Queensland Australia
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 brought up a good point, why is the table name a variable? Should it not be the same table involved every time that particular query is run?
    I missed your post while I was posting myself. The $table variable was there from a re-used deleting module, it was used across tables. I've removed it and entered the table name. Sadly no difference.

    I'm very new to MySQL, I've seen different query and connection styles and wonder what the difference is or which are the preferred ones.

    In Kevin Yanks 3rd edition database driven website book he uses @mysql_query, in the new book he uses !mysqli_query. I've been using just mysql_query because that's what Dreamweaver uses in Dynamic content. Any opinions on which is the best way to go? I'm in the early stages of habit geneation and I want it to be a good one.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Every example that I've ever seen has always had the php variables surrounded my ' when in a query. Even the examples in the php manual (php.net) are like that.
    that doesn't make it the right thing to do

    try it with a database that enforces datatype consistency, and you will get a syntax error

    my point wasn't that the table name was a variable, but that it shouldn't be in quotes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    In your latest code, if $edit == 'y', you never execute a query. You place the query string in a variable, $sql. You echo $sql. But you never call mysql_query() to send that query to the database.

    The "//perform query" part is all in the "else" branch.

  11. #11
    SitePoint Enthusiast Adelante's Avatar
    Join Date
    Sep 2006
    Location
    Queensland Australia
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dan you're a legend, you wouldn't believe how long I've stared at that code for. Goes to show what a fresh set of eyes can do.

    Thanks again


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •