[SOLVED] Cant find the error

[erdomester]

Hi,

I have this simple php code. I stopped using Ms Access but was lazy to create all the tables again so I converted the Access tables to Mysql. This created a "bus" directory with all the tables. Now I am trying to turn my code to be suitable for MySQL, but encountered this problem. I checked several tutorials but I seem to be blind. What is it giving me the error:

Error in query: SELECT * FROM 86. 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 '86' at line 1

<?php 
$conn = mysql_connect("localhost","root","password");
if (!$conn)
  {
     die('Could not connect: ' . mysql_error());
  }

$db="bus";
mysql_select_db("bus", $conn) or die ("Unable to select database!");
$query = "SELECT * FROM 86";
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
if (mysql_num_rows($result) > 0) 
{
     while($row = mysql_fetch_array($result)) 
     {
        echo $row['minutes'] . " " . $row['stops'];
      }
  }
mysql_free_result($result);


?>

 

[premiso]

Is 86 the tablename?

 

If not this is how the query should be formed:

 

$query = "SELECT * FROM table_name WHERE column = 86";

 

Replace the table_name and the column with their respected values. If it is suppose to be 86 for the table name try using backticks around it:

 

$query = "SELECT * FROM `86`";

[erdomester]

It is the tablename (number of bus). I've tried this already.

Maybe it has something to do with the conversion. Maybe it went wrong and I should create all tables. But what to do if I have 300 tables? Or let's suppose I have 10 tables in the db, and with the GUI Access it's easier and faster to create databases then I want to convert them...

 

[Ken2k7]

If you have 300 tables, that's a problem. You should look into DB normalization.

 

What is 86?

[erdomester]

86 is the name of the table, is that so difficult?

I tried to make a query in mysql but i get the same message....weird.

[Ken2k7]

$query = "SELECT * FROM `86`";

Copy and paste that in.

[Mchl]

I think it might not be a valid name for table, although I can not find any reference to that.

 

[edit]

 

Seems it is valid after all

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

 

One could even call a table `;`

 

 

Anyway: as mentioned above, having a separate table for each bus line is probably not the best idea.

[Ken2k7]

I think it might not be a valid name for table, although I can not find any reference to that.

I once named my table 1 (the number one). Hehe... it worked for me, though it was only for testing and I was curious. You just need backticks.

[erdomester]

I think I have found the solution. However, it is valid in Access, making a number to be a name of a table in SQL is a mistake. I put a "B" to the beginning of the name of the tables in Access (e.g. 86 -> B86 and so on), converted them to MySQL and now it works. Thanks for reading and I hope this will help for many people.

Bye,

erdomester