working with UPDATE

[woodsonoversoul]

Hello all. I'm tryign to update some records (for the first time) with;

//update DB 
    $sql = mysql_query("UPDATE users SET favorite_songs = '$userFavSongs' WHERE username = '$username'");

			//check to see if the query went through
			if (!mysql_query($sql, $con)){
  					die('Error: ' . mysql_error());
          }

 

But the DB never updates. Anyone see any problems with the syntax? Whole script is:

<?php

//turn on error reporting
ini_set('display_errors', 'On');
error_reporting(E_ALL | E_STRICT);
header('Content-Type: text/xml');

//pull variables
//need to do some error checking here
$username = ($_GET['username']);
$songId = ($_GET['songId']);
$incORdec = ($_GET['incORdec']);

//connect with database
$con = mysql_connect("localhost","root","");
if(!$con){
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("musicneverstopped", $con);
//end connecting to database

//declare placeholders

$result = mysql_query("SELECT favorite_songs FROM users WHERE username = '$username'");

//error check - see if this is safe to remove later
if(!$result){
  die(mysql_error());
  }

//check if any results were returned
if(mysql_num_rows($result) > 0){
  
  while($row = mysql_fetch_array($result)){
    //check if the current songs artist is in the artist array
    $userFavSongs = $row['favorite_songs'];
    }//end while
    $userFavSongsArray = $userFavSongs->getElementsByTagName('song');
    
    if($incORdec == 'inc'){
      $userFavSongs = $userFavSongs . "<song>". $songId . "</song>";
    }
    else{
      foreach($userFavSongsArray as $a){
        if($a == $songId){
          $userFavSongsArray =- $a;
        }//end if
      }//end foreach
      foreach($userFavSongsArray as $b){
          $userFavSongs =+ "<song>" . $b . "</song>";
        }//end foreach
    }//end else   
      
     //update DB 
    $sql = mysql_query("UPDATE users SET favorite_songs = '$userFavSongs' WHERE username = '$username'");


			//check to see if the query went through
			if (!mysql_query($sql, $con)){
  					die('Error: ' . mysql_error());
          }
              
  }//end if
  	  
//////////////////////////////////////////

//close database connection
mysql_close($con);//close mysql connection

    
?>

[Maq]

Try putting your query in a string and echo it to ensure it contains the proper values:

 

    $sql = "UPDATE users SET favorite_songs = '$userFavSongs' WHERE username = '$username'";
    echo $sql;
    $result = mysql_query("UPDATE users SET favorite_songs = '$userFavSongs' WHERE username = '$username'");
    if (!mysql_query($result, $con)){
        die('Error: ' . mysql_error());
    }

[woodsonoversoul]

This is a script called from javascript, ie:

xmlHttp.open("GET", "php/setUserFavSongs.php?" + params, true);

Where would I expect to see the echo? I don't see it on the main page...

[Maq]

Wherever that line of AJAX is called.

[PFMaBiSmAd]

There are two mysql_query() statements. The second one is going to fail because it is trying to use the TRUE/FALSE returned by the first one as the query string for the second one.

 

If you are making a first attempt at an UPDATE query, I recommend forgoing the Ajax for now. Get the basics working first.

[woodsonoversoul]

I get the following errors when accessing the page directly (location + setUserFavSongs.php?username=danwoods&songId=tdb2009-01-29s2s02&incORdec=inc as the address)

 

 

Warning: Cannot modify header information - headers already sent by (output started at /opt/lampp/htdocs/musicneverstopped/php/setUserFavSongs.php:2) in /opt/lampp/htdocs/musicneverstopped/php/setUserFavSongs.php on line 7

 

Fatal error: Call to a member function getElementsByTagName() on a non-object in /opt/lampp/htdocs/musicneverstopped/php/setUserFavSongs.php on line 45

 

 

How would I clear the value of mysql_query()?

[woodsonoversoul]

and no echo statement...

[Maq]

The 'header error' is probably being caused by output before the header call.  You cannot have any output before a header redirect.  Move that function to be the first line directly after your opening PHP tag (read more here - HEADER ERRORS).

 

As for the second error, I'm not sure what you're trying to do.  I don't even see where you instantiate the object.

 

How would I clear the value of mysql_query()?

 

You don't have to clear the value just don't call 'mysql_query()' twice.  You should assign a variable to the function call so you can check to check if the return value is a resource (success) or FALSE (obviously, failure).  Something like this:

 

    $sql = "UPDATE users SET favorite_songs = '$userFavSongs' WHERE username = '$username'";
    echo $sql;
    $result = mysql_query("UPDATE users SET favorite_songs = '$userFavSongs' WHERE username = '$username'");
    if (!$result){
        die('Error: ' . mysql_error());
    }

[woodsonoversoul]

moving the header function didn't remove the warning, just put it at a different line number.

 

What I'm trying to do is pull the xml stored in the table 'users' and the field 'favorite_songs'. Then either add a song to that xml or take one away.

 

Could I put the queries in different functions in the script, would that help? Do I really need to write a whole different script to handle a single query and a few lines of code? Surely there are times when developers need to do multiple queries in a single function...

[woodsonoversoul]

commenting out those two lines still leaves errors. Does my header look right?

 

New error from commenting out error producing lines:

1Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

[fenway]

Echo the query.