mysql_query(UPDATE) Help Please!

[jalexsmith]

Hi All....

 

Got a problem on my hands.....

 

I need to update a variable in my database, or rather, the user needs to be able to do it after they click a submit button.

 

I've got it most of the way there, but I'm not sure why it's not working. All the values are POST'ing properly, and I'm not getting any errors on this page, but it's not updating the database. It's all working except for the new pard I just added which is the WHERE part. I need it to update the variable where username = $config['pageowner'].

 

Here's my code. One note: many of the values are variables. For example, $config['table'] equals the table in the database.

 

<?php

require '../config_edit.php';

$text = mysql_real_escape_string( addslashes( nl2br($_POST['value'])) );

mysql_query("UPDATE ". $config[ 'table' ]. " SET ". $config['columns'][ $_POST['column'] ]. " WHERE username = ". $config['pageowner'] ." = '". $text. "'"); // Update the text

?>

 

Any ideas on how to make that work? There's just something I'm missing because it's not updating the database. It was working before I added the WHERE part.

 

Thanks in advance for the help!

Alex

[Pikachu2000]

See if you get any errors with this.

 

require '../config_edit.php';

$text = mysql_real_escape_string( addslashes( nl2br($_POST['value'])) );

$query = "UPDATE ". $config[ 'table' ]. " SET ". $config['columns'][ $_POST['column'] ]. " WHERE username = ". $config['pageowner'] ." = '". $text. "'"
$result = mysql_query($query) or die( 'Query: ' . $query . '<br>Produced error: ' . mysql_error() . '<br>'); // Update the text

?>

[jalexsmith]

Thanks for the reply!

 

I get the following error unfortunately:

 

Parse error: syntax error, unexpected T_VARIABLE in /......................................................./users/sp/save.php on line 8

 

Ideas? Thanks!

[Pikachu2000]

The code I posted above parses without error. Did you paste it or type it in?

[PFMaBiSmAd]

The $query = "..."; line is missing the ;

[jalexsmith]

That worked.

 

I get this back from it though:

 

Query: UPDATE users SET WHERE username = $pageowner = ''

Produced error: 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 'WHERE username = $pageowner = ''' at line 1

[Pikachu2000]

I'm surprised Aptana didn't pick up the missing semicolon on that line.

 

Anyhow, none of the variables in the query have values, and the string literal '$pageowner' is echoing in there, possibly as the value of $config['pageowner']. Where are you defining the variables?

[jalexsmith]

I'm defining the variables in a page called config_edit.php which is included in the other page. Here's the code:

 

<?php

$config = array();

$config['host'] = '**********.secureserver.net'; // Your host (default: localhost)
$config['db_user'] = '********'; // Your database username
$config['db_pass'] = '********'; // Your database password
$config['db_name'] = '********'; // Your database name
$config['table'] = "users"; // The main table of your text in the database
$config['columns'] = array( "user_interests", "user_activities" );  // The columns (If more than one, order by the order of your divs) - starting from 0
$config['pageowner'] = '$pageowner';
$config['username'] = 'username';

$c = mysql_connect($config['host'], $config['db_user'], $config['db_pass']) or die( mysql_error() );
mysql_select_db($config['db_name'], $c) or die( mysql_error() );

?>

 

[Pikachu2000]

In the config file, you need to unquote $pageowner. That isn't the only problem here though. The query string is malformed even if the variables all have proper values. Currently, if all values are present it would look like this, which is syntactically incorrect:

 

UPDATE table SET field WHERE username = name = text

 

Correct syntax is

UPDATE table SET field = value WHERE some_field = some_value

 

If you can explain how this query is intended to work, and what values go where, someone can probably help get it fixed up for you.

[jalexsmith]

Thanks for responding again!

 

Here's how this is supposed to work.....

 

This is a small part of a user management system where each user gets their own profile. On that profile, each user has a few fields like "Favorite _______" and "Activities: ______" and so on. When the user is logged in and on their own profile page, a little button that says "Edit" is present next to each field. When the user clicks that edit button, a jQuery event handler steps in and displays a text box and allows the user to modify their favorite ______. When the user clicks the "Save" button after they're done editing their stuff, jQuery uses AJAX to POST the data from that field and the column (the part that tells the script which field on the page the user is editing) to Save.php. Save.php is then supposed to update that field in the database for that user. So, if I'm user jalexsmith and I update my "Favorite Food" field to be "sushi", then save.php should update the favorite food part of the database where it finds my username with the word sushi.

 

Does that make sense?

 

Save.php looks like this:

<?php
require '../config_edit.php';

$text = mysql_real_escape_string( addslashes( nl2br($_POST['value'])) );

$query = "UPDATE ". $config[ 'table' ]. " SET ". $config['columns'][ $_POST['column'] ]. " WHERE username = ". $config['pageowner'] ." = '". $text. "'";
$result = mysql_query($query) or die( 'Query: ' . $query . '<br>Produced error: ' . mysql_error() . '<br>'); // Update the text

?>

 

but currently returns this:

 

Query: UPDATE users SET WHERE username = $pageowner = ''

Produced error: 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 'WHERE username = $pageowner = ''' at line 1

 

Thanks to all of you for the help! I'm looking forward to finally getting this to work!

[jalexsmith]

I tried messing with it to get it into this format:

UPDATE table SET field = value WHERE some_field = some_value

but I wasn't able to get it right.

 

Would somebody mind telling me how to format this:

$query = "UPDATE ". $config[ 'table' ]. " SET ". $config['columns'][ $_POST['column'] ]. " WHERE username = ". $config['pageowner'] ." = '". $text. "'";

properly?

$text is the value of what it's putting in the database when the user clicks the "Save" button as described in my last reply.

 

Thanks!!

[Pikachu2000]

I think this is what you mean. Give it a try and see if it works.

 

$query = "UPDATE `{$config['table']}` SET `{$config['columns'][$_POST['column']]}` = '$text' WHERE `username` = '{$config['pageowner']}'";

[jalexsmith]

@Pikachu2000:

 

That gives me the following error when I go directly to save.php (the page where the code you suggested is).

Query: UPDATE `users` SET `` = '' WHERE `username` = ''
Produced error: Unknown column '' in 'field list'

 

If I try editing the field and letting it do it's thing, I don't get an empty response from save.php after POST. But it doesn't work.

 

I'll attach two screenshots of the database. The first one is a list of the fields. The second one is a view of the 'user_activities' field. See the 13 rows that are all the word "That"? I want to change only one row at a time based on the username.

 

 

 

[attachment deleted by admin]

[jalexsmith]

I got it working!

 

What was it you may ask? This was missing:

session_start();

 

Oh the joys of code.

 

[Pikachu2000]

Yet we had no idea your code even used sessions. Kind of hard to diagnose the problem without all of the information.