Unknown column 'xxxxxx' in 'field list'

Question

I know there are a lot of topics on this, and I've looked at them all, and they don't help me. My table name is correct, no spaces or anything out of the ordinary. I've checked 100 times and checked 100 more. I'll post both bits of my code, and hopefully someone can help.

I get this error when I try to use the submit button: Error updating odds: Unknown column 'homeOdds' in 'field list'

POST:

if ($_POST['action'] == 'Update') {
foreach($_POST['game'] as $game) {

    $homeScore = ((strlen($game['homeScore']) > 0) ? $game['homeScore'] : 'NULL');
    $homeOdds = (str_replace("\xBD", ".5", $homeScore));
    $visitorScore = ((strlen($game['visitorScore']) > 0) ? $game['visitorScore'] : 'NULL');
    $visitorOdds = (str_replace("\xBD", ".5", $visitorScore));
    $sql = "update " . $db_prefix . "schedule ";
    $sql .= "set homeOdds = '" . $homeOdds . "', visitorOdds = '" . $visitorOdds . "' ";
    $sql .= "where gameID = " . $game['gameID'];
    mysql_query($sql) or die('Error updating odds: ' . mysql_error());
}
header('Location: index.php');
}

Table/Form & Update button:

<form id="scoresForm" name="scoresForm" action="odds.php" method="post">
<input type="hidden" name="week" value="<?php echo $week; ?>" />
<?php
$sql = "select s.*, ht.city, ht.team, ht.displayName, vt.city, vt.team, vt.displayName ";
$sql .= "from " . $db_prefix . "schedule s ";
$sql .= "inner join " . $db_prefix . "teams ht on s.homeID = ht.teamID ";
$sql .= "inner join " . $db_prefix . "teams vt on s.visitorID = vt.teamID ";
$sql .= "where weekNum = " . $week . " ";
$sql .= "order by gameTimeEastern";
$query = mysql_query($sql);
if (mysql_num_rows($query) > 0) {
echo '<table cellpadding="4" cellspacing="0" class="table1">' . "\n";
echo '  <tr><th colspan="6" align="left">Week ' . $week . '</th></tr>' . "\n";
$i = 0;
while ($result = mysql_fetch_array($query)) {
    $homeTeam = new team($result['homeID']);
    $visitorTeam = new team($result['visitorID']);
    $rowclass = (($i % 2 == 0) ? ' class="altrow"' : '');
    echo '      <tr' . $rowclass . '>' . "\n";
    echo '          <td><input type="hidden" name="game[' . $result['gameID'] . '][gameID]" value="' . $result['gameID'] . '" />' . date('D n/j g:i a', strtotime($result['gameTimeEastern'])) . ' ET</td>' . "\n";
    echo '          <td align="right"><input type="hidden" name="gameID[' . strtolower($visitorTeam->team) . ']" value="' . $result['gameID'] . '" />' . $visitorTeam->teamName . '</td>' . "\n";
    echo '          <td><input type="text" name="game[' . $result['gameID'] . '][visitorScore]" id="game[' . $result['gameID'] . '][visitorScore]" value="' . $result['visitorOdds'] . '" size="3" /></td>' . "\n";
    echo '          <td align="right"><input type="hidden" name="gameID[' . strtolower($homeTeam->team) . ']" value="' . $result['gameID'] . '" />at ' . $homeTeam->teamName . '</td>' . "\n";
    echo '          <td><input type="text" name="game[' . $result['gameID'] . '][homeScore]" id="game[' . $result['gameID'] . '][homeScore]" value="' . $result['homeOdds'] . '" size="3" /></td>' . "\n";
    echo '      </tr>' . "\n";
    $i++;
}
echo '</table>' . "\n";
}
?>
<br><input type="submit" name="action" value="Update" />
</form>

Any help is appreciated.

your php/html code is irrelevant. the db has clearly told you that there's no field `homeodds` in a table named `odds`. And you're vulnerable to [sql injection attacks](http://bobby-tables.com), so it's probably a good thing nothing is working right now. You need to show us `var_dump($sql)` — Marc B, Aug 10 '15 at 18:52
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Aug 10 '15 at 18:53
I'm in the process of converting... I just have a lot to convert. This is a small add on that I'm trying to get working the way I know how for the time being. I know it's depreciated, and I know the risks. Marc B, the table name is not `odds` where are you seeing that? — Mark Jones, Aug 10 '15 at 18:56
Is there a column named 'homeOdds' in the result of your query..? Surely its that simple? `var_dump($result)` inside your loop... — MaggsWeb, Aug 10 '15 at 19:05

score 0 · Accepted Answer · answered Aug 10 '15 at 19:10

0

For debugging this, echo (or var_dump) the dynamically generated SQL contained in the $sql variable, before you submit it to the database.

Then take that statement to another client to test it.

MySQL is telling you that the table schedule which you are referencing doesn't contain a column named homeOdds.

We don't see the contents of all the variables that are being incorporated into the SQL text. (The code appears to be vulnerable to SQL Injection.

answered Aug 10 '15 at 19:10

spencer7593

106,611
15
112
140

Ok, I think I found some issues and I also did a var_dump and it doesn't look right... I took out the ' that were wrapping the " in `$sql .= "set homeOdds = '" . $homeOdds . "', visitorOdds = '" . $visitorOdds . "' ";` and the var_dump says this: `string(69) "update nflp_scheduleset homeOdds = 0, visitorOdds = 0where gameID = 1" Error updating odds: 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 '= 0, visitorOdds = 0where gameID = 1' at line 1` – Mark Jones Aug 10 '15 at 19:18
I added some spaces to fix the string, and it looks right now, but it still thinks the column doesn't exist. I am so confused... the columns definitely exist, there are no spaces or dead space. I have no clue. I know this code is vulnerable but I would like to get it working as is for now. – Mark Jones Aug 10 '15 at 19:20
how about doing a `show create table nflp_schedule;` and letting another pair of eyes look at the table... – BK435 Aug 10 '15 at 19:25
As a side note, whenever I have to dynamically generate a SQL statement, I generally put the required spaces at the *beginning* of what I'm appending. `$sql .= " set mycol = ...";` `$sql .= " where ...";`. (The query doesn't need a trailing space... what I need is a space before what I'm appending.) – spencer7593 Aug 10 '15 at 19:46
I finally got it. Thank you. The table was there, but for some reason I removed the table, removed the unnecessary `'`'s, added spaces before SET and WHERE and recreated the tables, and all is working now. – Mark Jones Aug 10 '15 at 19:48

Unknown column 'xxxxxx' in 'field list'

1 Answers1