Column not found in field list! arrggghhhh

[cold1too]

I recently add two new columns (month & year) to a table in our test database for a client. I wrote some PHP code to query the new columns and everything worked fine. I then made the exact same changes to our production database which uses the phpMyAdmin web interface at our hosting provider. It clearly shows the two new columns I added to the table in question and I could successfully query on the new columns from phpMyAdmin. When I posted PHP page with the updated query I received the following error,

Warning: Supplied argument is not a valid MySQL result resource in: php.php/line blah blah blah

Upon further investigation I discovered that the error was caused by "no column named 'year' in field list. I tried flushing the table and also had the administrator restart the MySQL server, all to no avail. I just can't figure out why the new columns are not recognized in the SQL query. Like I said, the same query worked fine on our test box after I had added the two new columns to the table.

Has anybody else ever encountered this?

 

[r937]

year is a reserved word

do you have backticks around it in the query?


rudy

 

[cold1too]

Thanks, I thought your suggestion might do the trick, so I changed the column names to 'pub_year' and 'pub_month' and the page still will not return the new column names. It's like the data is being cached or something.

 

[hos2]

can you perhaps post the query ??

 

[cold1too]

The following code works perfectly on our test box, but not on the live box. The page doesn't recognize the new 'pub_year' column even though it is there and can be queried on from phpMyAdmin.

$sql1 = "SELECT * FROM stories WHERE page = 'legEX Archive' AND published IS NOT NULL ORDER BY published DESC";

$sql2 = "SELECT pub_year FROM stories WHERE page = 'legEX Archive' AND published IS NOT NULL ORDER BY pub_year DESC";


// ****** This code organizes stories by year ******

$years = mysql_query($sql2, $conn) or die(mysql_error().'<br> SQL:'.$sql);

while($year = mysql_fetch_array($years))
{
print &quot;<br><b>&quot; . stripslashes($year[pub_year]) . &quot;</b><br>\n&quot;;

$links = mysql_query($sql1, $conn) or die(mysql_error().'<br> SQL:'.$sql);

while($story = mysql_fetch_array($links))
{

if($story[pub_year] == $year[pub_year])
{
if (($story[link] != '') && ($story[link] != NULL))
{
print &quot;<a href=\&quot;&quot; . $story[link] . &quot;\&quot; target=\&quot;_blank\&quot;>&quot; . stripslashes($story[headline]) . &quot;</a><br>\n&quot;;
}
else
{
print &quot;<a href=\&quot;page.php?page=&quot; . $story[id] . &quot;\&quot;>&quot; . stripslashes($story[headline]) . &quot;</a><br>\n&quot;;
}
}
}
}

 

[hos2]

you still use year as a variable name. perhaps it's flipping on that ??

 

[cold1too]

Yeah, I tried that. I used the following code to print out all the column names for the 'stories' table and sure enough the 'pub_year' column' is not showing up, even though it's there. I gotta feeling it's something on the hosting provider's end. It just doesn't make sense. It's like there's another instance of the database.

// ****** This code prints out all the field names of the stories table ******
$fields = mysql_list_fields(&quot;insight&quot;, &quot;stories&quot;, $conn);
$columns = mysql_num_fields($fields);

for ($i = 0; $i < $columns; $i++) {
echo mysql_field_name($fields, $i) . &quot;<br>\n&quot;;
}

 

[hos2]

perhaps you are still refering to the wrong connection file ?????

 

[cold1too]

No, that was one of the first things I checked

 

[ingresman]

Can you get a list of the tables column defintion from the db, perhaps by a php script ?

 

[tshot]

<b>ingresman</b>he (<b>cold1too</b>) has laready done it

$fields = mysql_list_fields("insight", "stories", $conn);
$columns = mysql_num_fields($fields);

for ($i = 0; $i < $columns; $i++) {
     echo mysql_field_name($fields, $i) . &quot;<br>\n&quot;;
}

why don't use mysql client and connect direclty and use DESC or SHOW COLUMNS. it will rempove all doubts
if possible create another test table on production with following filelds including all the fields of proble table




----------------
ur feedback is a very welcome desire

 

[ingresman]

what I would like to see is the actual table defintion, if he can connect to the host and issue mysql so much the better but I sometimes like to run diagnostics with the actual connection in error, for example he might connect to the correct machine and get the results he wants from a show table. But if the bug is in the script and it isn't in fact connecting to the correct db you will see the problem.