MySQL error "Unknown column "sorting" in "where clause" very very odd

Question

So im trying to make a script that fetches all dates from my Database, and then adds 1 day to them, in the dd/mm/yy format, error trubleshooting worked fine, and my script works fine, other then that it cant upload to my SQL database....

ERROR: Unknown column "sorting" in 'where clause'

here it is:

<?      
$usernamedb = "goldacco"; // connect da jeg skal bruge det senere
$passworddb = "26102610asd";
$database = "goldacco_fb";
$server = "127.0.0.1";

$db_handle = mysql_connect($server, $usernamedb, $passworddb);
$db_found = mysql_select_db($database, $db_handle);

$i=0;
$f=0;

   $sql_sort = "SELECT sorting FROM feedback WHERE approved='1' ORDER BY sorting ASC"; // get lowest sorting
   $result_sort = mysql_query($sql_sort);
   $sorting = mysql_fetch_assoc($result_sort);

   $sorting[$f] = $sorting['sorting']; // 405



   $sql = "SELECT date FROM feedback WHERE approved='1' ORDER BY sorting ASC"; // get date
   $result_date = mysql_query($sql);

  while ($row = mysql_fetch_assoc($result_date) OR die(mysql_error())) { // værdigen jeg har gemt

       $olddate[$i] = $row['date'];

   $new_date[$i] = substr($olddate[$i], 0, -6); //  day

   $new_date[$i] = str_replace("/", null, $new_date[$i]); // fjern /

   $new_month[$i] = substr($olddate[$i], 2, -3); // month

   $new_month[$i] = str_replace("/", null, $new_month[$i]); // fjern /

   $new_year[$i] = substr($olddate[$i], 5-0); // år

   $new_year[$i] = str_replace("/", null, $new_year[$i]); // fjern /



   $new_date[$i] = $new_date[$i]+1;

  if ($new_date[$i] == 32) { // check om det er den 31, hvis ja, sæt til 0
    $new_date[$i] = 1;
    $new_month[$i] = $new_month[$i] +1;
  }

  if ($new_month[$i] == 13) { // check om det er den 12, hvis ja, sæt til 0
     $new_month[$i] = 1;
     $new_year[$i] = $new_month[$i] +1;
  }


  $new_date[$i] = sprintf("%02s", $new_date[$i]);
  $new_month[$i] = sprintf("%02s", $new_month[$i]);


  $new_total[$i] = $new_date[$i] . "/" . $new_month[$i] . "/" . $new_year[$i];

  $sql_into = "UPDATE feedback SET date=`$new_total` WHERE sorting=`$sorting[$f]`";
  $result_date = mysql_query($sql_into);

  $f++;
  $i++;
   }

?>

Have you tried to print out the sql_into, in order to see what are you actually trying to update. Try to manually create this SQL with hard-coded values and execute it - to see if otherwise works. — Riho, Feb 08 '13 at 09:35
Please do not vandalize your old questions. While they may no longer be important to you, they may be important to others who come across them. — ceejayoz, Feb 09 '20 at 02:47

score 3 · Answer 1 · edited May 23 '17 at 10:24

3

Don't use backtick around string literals. It should be single quotes. Backticks are for identifiers.

UPDATE feedback 
SET    date = '$new_total' 
WHERE  sorting='$sorting[$f]'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 10:24

Community

1
1

answered Feb 08 '13 at 01:22

John Woo

258,903
69
498
492

They are coming from the inside and have been cleaned – Feb 08 '13 at 01:23
@JohnDoe then it should be fine `:D` – John Woo Feb 08 '13 at 01:24
anyway, the problem is on line 35, the "While" statement =) I think it has something to do with me defining "sorting" as an array and then using "ORDER BY 'sorting" to fetch new data – Feb 08 '13 at 01:25

MySQL error "Unknown column "sorting" in "where clause" very very odd

1 Answers1