[SOLVED] MySQL won't update

[timmah1]

I'm trying to change the values of the field "default" in my database, but it won't update.

Here is the code
[code]
<a href=myPhotosD.php?default=yes&file=$myrow[file]>Make Default Picture</a>[/code]

and here is the script that should update the DB
[code]
<?php
session_start();
if(!session_is_registered(username)){
header("location:login.html");
}
/***** database connection start *****/
// MySQL Settings
      $MySqlHostname = "localhost";
      $MySqlUsername = "xx";
      $MySqlPassword = "xx";
      $MySqlDatabase = "xx";

      /* make connection to database */
      /* If no connection made, display error Message */
         
      $dblink=MYSQL_CONNECT($MySqlHostname, $MySqlUsername, $MySqlPassword) OR DIE("Unable to connect to database");     

      /* Select the database name to be used or else print error message if unsuccessful*/
      @mysql_select_db("$MySqlDatabase") or die( "Unable to select database");
/***** database connection end ******/

$query="update photos set default='$default' WHERE file='$file'";
$result = MYSQL_QUERY($query);
?>
[/code]

any ideas?
thanks

[trq]

What does....

[code=php:0]
$result = MYSQL_QUERY($query) or die(mysql_error());
[/code]

produce?

[timmah1]

It produced an error
[code]
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 'default='yes' WHERE file='545607209.gif'' at line 1
[/code]

[trq]

Where do you define $default and $file?

[timmah1]

here's the code where it's defined
[code]
echo "<table width=100% border=0 align=center>";

//set 3 to 4 of you want 4 columns. Set it to 5 if you want 5, etc
$numcols = 3; // how many columns to display
$numcolsprinted = 0; // no of columns so far

// get the results to be displayed
$query = "SELECT * FROM photos WHERE username = '$username'";
$mysql_result = mysql_query($query);


// get each row
while($myrow = mysql_fetch_assoc($mysql_result))
{

//get data - eg,
$file = $myrow['file'];
$default = $myrow['default'];
$username = $myrow['username'];
$comments = $myrow['comments'];

if ($numcolsprinted == $numcols) {
print "</tr>\n<tr>\n";
$numcolsprinted = 0;
}

// output row from database
echo "<td align=center valign=top><img src=\"/$username/$file\" width=\"100\" border=\"1\"><br>
Default <b>$myrow[default]</b><br>
<a href=myPhotosD.php?default=yes&file=$myrow[file]&username=$myrow[username]>Make Default Picture</a><br>
<a href=share.php?default=no&file=$myrow[file]>Undefault Picture</a></td>\n";

// bump up row counter
$numcolsprinted++;

} // end while loop

$colstobalance = $numcols - $numcolsprinted;
for ($i=1; $i<=$colstobalance; $i++) {

}
print "<TD></TD></table>\n";
[/code]

[trq]

Is that somehow included into the other script?

[corbin]

Try

$query="UPDATE `photos` SET `default` = '{$default}' WHERE `file` = '{$file}'";
$result = mysql_query($query) or die(mysql_error());

I doubt it'll work, but its worth a try... Anyways tell me what it says the error is if it doesnt work...

And ummm if file and default are being pulled from the DB, whats the point in updating them to the same values?

[timmah1]

I have a code that displays pictures, which is this one.
It also gives you the choice of making a certain picture your default, and you should be able to change it
[code]
echo "<table width=100% border=0 align=center>";

//set 3 to 4 of you want 4 columns. Set it to 5 if you want 5, etc
$numcols = 3; // how many columns to display
$numcolsprinted = 0; // no of columns so far

// get the results to be displayed
$query = "SELECT * FROM photos WHERE username = '$username'";
$mysql_result = mysql_query($query);


// get each row
while($myrow = mysql_fetch_assoc($mysql_result))
{

//get data - eg,
$file = $myrow['file'];
$default = $myrow['default'];
$username = $myrow['username'];
$comments = $myrow['comments'];

if ($numcolsprinted == $numcols) {
print "</tr>\n<tr>\n";
$numcolsprinted = 0;
}

// output row from database
echo "<td align=center valign=top><img src=\"/$username/$file\" width=\"100\" border=\"1\"><br>
Default <b>$myrow[default]</b><br>
<a href=myPhotosD.php?default=yes&file=$myrow[file]>Make Default Picture</a><br>
<a href=myPhotosD.php?default=no&file=$myrow[file]>Undefault Picture</a></td>\n";

// bump up row counter
$numcolsprinted++;

} // end while loop

$colstobalance = $numcols - $numcolsprinted;
for ($i=1; $i<=$colstobalance; $i++) {

}
print "<TD></TD></table>\n";[/code]

Then here is the script which is suppose to update the file into the database
[code]
session_start();
if(!session_is_registered(username)){
header("location:login.html");
}
/***** database connection start *****/
// MySQL Settings
      $MySqlHostname = "localhost";
      $MySqlUsername = "xx";
      $MySqlPassword = "xx";
      $MySqlDatabase = "xx;

      /* make connection to database */
      /* If no connection made, display error Message */
         
      $dblink=MYSQL_CONNECT($MySqlHostname, $MySqlUsername, $MySqlPassword) OR DIE("Unable to connect to database");     

      /* Select the database name to be used or else print error message if unsuccessful*/
      @mysql_select_db("$MySqlDatabase") or die( "Unable to select database");
/***** database connection end ******/


$query="update photos set default='$default' WHERE file='$file'";
$result = MYSQL_QUERY($query);
[/code]

[trq]

Backticks are not required and neither are {} unless your dealing with complex variables (arrays).

The problem is that $default and $file are empty I assume.

[timmah1]

their not being updated with the same values,
one link updates default to 'yes'
one link updates default to 'no'


no, the values are not empty

[timmah1]

Is the field I have named "default" a problem with MySQL?

If I change that field name to something different, would it work?

[trq]

$default and $file are not defined in your second script. They are contained within the $_GET array. Use...

[code=php:0]
$query="update photos set default='{$_GET['default']}' WHERE file='{$_GET['file']}'";
[/code]

Whatever tutorial or book it is your reading as well out of date.

[corbin]

Hehe sorry, didnt read the entire script when I posted that bout changing it to the same value...

And you should consider checking the $_GET variables, and checking the owner of the file when you update or someone could use someone else's pic...

[timmah1]

SOLVED
Thanks for the help
I changed the field name to something other than 'default', and it works fine

I will change to $_GET, not sure the book is out of date, I'm the one out-of-date :)

Thanks for everything, works now!!

[trq]

Oh... well, my bad. If you put backticks around `default` you can use it even if it is a reserved word.

[timmah1]

I was not aware of that, thanks again for the info, I'm going to start doing more reading to get myself up to date