SELECT * FROM giving other result then SELECT 2010,2011 FROM

Question

when i try to get the values from some columns and use SELECT 2010,2011 FROM blablatable then i get only the table names(checked in a browser). When i use SELECT * FROM blablatable then i get (of course everything) but then the content of the year colums are displayed correct(checked in a browser). Now other columns in the same table are displayed correct, i use this php script to get the data into a datagrid in Livecode. Everything works OK except for the year columns. Then the datagrid is giving an error. I do not understand why it is giving the tables instead of giving the data. I allready use the same adjusted script in Livecode and that works ok. So tested some thing and either it is the php script or it is the database giving the trouble.

These are parts of the 2 php testscripts i use: _______First the script that only gives the table names__notgood___

$hostname_connLivecode = "localhost";
   $database_connLivecode = "blabla";
   $username_connLivecode = "blabla";
   $password_connLivecode = "blabla";
   $connLivecode = mysql_pconnect($hostname_connLivecode, $username_connLivecode, $password_connLivecode) or trigger_error(mysql_error(),E_USER_ERROR); 

//2. Perform database query

mysql_select_db($database_connLivecode, $connLivecode);
$query_rsUser = ("SELECT 2010,2011 FROM blabla_table ORDER BY id");

$rsUser = mysql_query($query_rsUser, $connLivecode) or die(mysql_error());
$row_rsUser = mysql_fetch_assoc($rsUser);
$totalRows_rsUser = mysql_num_rows($rsUser);

if ($totalRows_rsUser == 0) {
    echo "no data found";
} 


do {
  echo $row_rsUser["id"]."\t".$row_rsUser["2010"]."\t".$row_rsUser["2011"]."\n";
   } while ($row_rsUser = mysql_fetch_assoc($rsUser)); 

?>

---

____________Now the script that gives the correct info_____

$hostname_connLivecode = "localhost";
$database_connLivecode = "blabla";
$username_connLivecode = "blabla";
$password_connLivecode = "blabla";
$connLivecode = mysql_pconnect($hostname_connLivecode, $username_connLivecode, $password_connLivecode) or trigger_error(mysql_error(),E_USER_ERROR); 

//2. Perform database query

mysql_select_db($database_connLivecode, $connLivecode);
$query_rsUser = ("SELECT * FROM blabla_table ORDER BY id");

$rsUser = mysql_query($query_rsUser, $connLivecode) or die(mysql_error());
$row_rsUser = mysql_fetch_assoc($rsUser);
$totalRows_rsUser = mysql_num_rows($rsUser);

if ($totalRows_rsUser == 0) {
    echo "no data found";
} 


do {
  echo $row_rsUser["id"]."\t".$row_rsUser["2010"]."\t".$row_rsUser["2011"]."\n";
   } while ($row_rsUser = mysql_fetch_assoc($rsUser)); 

?>

---

Does anyone understand why SELECT * works ok? I cannot use this, i have to use the column names.

Any help will be appreciated.

Thanks.

Answer 1

This part of your code SELECT 2010,2011

From the manual http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

"Identifiers may begin with a digit but unless quoted may not consist solely of digits."

wrap those column names in ticks.

SELECT `2010`,`2011`

• Since you didn't post your error message.

Sidenote: Copy/paste it from this answer. Ticks and regular quotes look alike, but are not the same.

Example, which will fail:

SELECT '2010','2011'

since they are regular ' quotes and translated by MySQL as a literal string.

I see many questions where an OP will use code from an answer, and use regular quotes, rather than ticks, instead of copying/pasting from the answer's code itself.

---

Footnotes:

You should consider using mysqli with prepared statements, or PDO with prepared statements, they're much safer.

• mysql_ functions are now deprecated and will be removed from future PHP releases.

Reference(s):

• https://wiki.php.net/rfc/mysql_deprecation
• Why shouldn't I use mysql_* functions in PHP?