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.

Not related but you shouldn't use mysql extension. It's deprecated. Use mysqli or pdo instead — A.D., Jun 11 '15 at 17:30
Have you tried $query_rsUser = ("SELECT '2010' ,'2011' FROM blabla_table ORDER BY id"); ? — Sari Rahal, Jun 11 '15 at 17:34
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, Jun 11 '15 at 17:35

score 3 · Accepted Answer · edited May 23 '17 at 12:30

3

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):

edited May 23 '17 at 12:30

Community

1
1

answered Jun 11 '15 at 17:31

Funk Forty Niner

74,450
15
68
141

I did a test and changed the 2010 column to 20_10, then with the same script (changing the 2010 to 20_10 accordingly) it does work. @Fred the datagrid error is : An error has occured in the behavior for the column template. Chunk: can't find background. I followed the info on this site: http://learninglivecode.blogspot.nl/2015/04/desperately-seeking-middleware.html which is good to use to solve the puzzle to use PHP scripts to access the database and get the info into Livecode. But maybe there are some better solutions than this. Thank you all, i'm going to read every answer again! – JeeJee Studio Jun 11 '15 at 19:03
@JeeJeeStudio You're welcome. But do try using the ticks as outlined in my answer. – Funk Forty Niner Jun 11 '15 at 19:21
I did Fred and it works ! Found it on the keyboard left from 1. Great tip! Also going to check into some of the other suggestions not to use MYSQL commands bu pdo. Maybe in time i can change it piece by piece. Thanks a lot for your help. – JeeJee Studio Jun 11 '15 at 19:26
Ok did, Thanks Fred all the best to you too :) – JeeJee Studio Jun 11 '15 at 19:43
@JeeJeeStudio You're very much welcome, thanks and all the best to you as well, *cheers!* – Funk Forty Niner Jun 11 '15 at 19:46

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

1 Answers1