Read from database problem.

[lkeeney]

I am new to mysql programming. I have set up a database with a table called "2009". I have 2 rows with about 16 fields. I have data stored in these two rows.

I have some code that tells me this table exists. Below that code I have this code:

$sql = "SELECT * FROM 2009";
$result = mysql_query($sql, $conn) or die(mysql_error());

I get this error: "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 '2009'". The error is in the second line because when I comment it out, I don't get the error.

I have a number of books on MySQL, and I have tried many different ways to retrieve data from the data base, but I am constantly getting errors. The above code is simple code that produces this error.

What am I doing wrong?

I will appreciate any help I can get. I have spent all morning trying to get something to work.

Lawrence

[r937]

the error message tells you exactly where to look -- 2009

try putting backticks around the table name -- `2009`

[lkeeney]

the error message tells you exactly where to look -- 2009

try putting backticks around the table name -- `2009`

Thanks for the tip.

I did this, but I get the same error.

Lawrence

[r937]

rename the table as t2009 and try again

[lkeeney]

rename the table as t2009 and try again

With this change I get an error that says this table does not exist.

However, I have this code ahead of the previous code and it indicates that the table does exist:

$table_found = false;
    $result = mysql_query("SHOW TABLES");

    while($row = mysql_fetch_array($result))
        {
        if($row[0] == $dbtable)
            {
                $table_found = true;
            break;
            }
        }

        if($table_found) {
            echo "Table named $dbtable does exist.<br>";
        } else {
            echo "Table does not exist";
        }

The response from this code is: "Table named 2009 does exist."

I wonder if there is something on the server that is blocking me from accessing the data in my table.

Lawrence

[r937]

please run the following in your own test database --

CREATE TABLE 937
( i INTEGER
, x VARCHAR(9)
);

that should give you an error

why? because a table name consisting of numerics needs to be escaped

now try this --

CREATE TABLE `937`
( i INTEGER
, x VARCHAR(9)
);

that should work

did you notice a difference? the table name is escaped by backticks

okay, now let's insert some rows --

INSERT INTO `937` VALUES ( 1,'a' ) , ( 2,'b' );

works, right?

and we can select, too --

SELECT x,i FROM `937`;

now, once you have run these, and confirmed that everything works, please go back to your code and remember that if your table name is numeric, you must always escape it

which is why i suggested thqat you rename it to t2009, because then you don't need the backtick nonsense all da time

[lkeeney]

please run the following in your own test database --

CREATE TABLE 937
( i INTEGER
, x VARCHAR(9)
);

that should give you an error

why? because a table name consisting of numerics needs to be escaped

now try this --

CREATE TABLE `937`
( i INTEGER
, x VARCHAR(9)
);

that should work

did you notice a difference? the table name is escaped by backticks

okay, now let's insert some rows --

INSERT INTO `937` VALUES ( 1,'a' ) , ( 2,'b' );

works, right?

and we can select, too --

SELECT x,i FROM `937`;

now, once you have run these, and confirmed that everything works, please go back to your code and remember that if your table name is numeric, you must always escape it

which is why i suggested thqat you rename it to t2009, because then you don't need the backtick nonsense all da time

I just got off the phone with technical support at my hosting service. They had a standard php file they asked me to run. This didn't work either. In my second phone call to them, they said the database was set up for PHP5 which won't run the normal php files without doing something. I did not understand what he was talking about. In any case, he is having the database set up for PHP 4 instead. He said my database would be available tomorrow as PHP 4 and should run the code I have been trying to use.

I sure wish I knew what I was doing. At my age (72) learning PHP and MySQL is not as easy as it would have been 30 years ago.

Lawrence

[Mittineague]

I don't think it's easy at first for anyone, irregardless of age. As you go, things that seemed baffling at first become second nature and new challenges take their place.

There are some differences between the PHP versions, but bear in mind that you are dealing primarily with an MySQL server. PHP interacts with it, but except for various functions that may or may not be available the query syntax in general is the same. At least for simple queries like

$sql = "SELECT * FROM `2009`"; 

Error messages are strange creatures at first too, but once you know what they're saying, you'll know what they're saying. You're probably thinking something like "@!^*!?!", but after a while you'll see what I mean.

[lkeeney]

I don't think it's easy at first for anyone, irregardless of age. As you go, things that seemed baffling at first become second nature and new challenges take their place.

There are some differences between the PHP versions, but bear in mind that you are dealing primarily with an MySQL server. PHP interacts with it, but except for various functions that may or may not be available the query syntax in general is the same. At least for simple queries like

$sql = "SELECT * FROM `2009`"; 

Error messages are strange creatures at first too, but once you know what they're saying, you'll know what they're saying. You're probably thinking something like "@!^*!?!", but after a while you'll see what I mean.

I know you are right about this, it is just that I get so darned frustrated. I have a bunch of PHP & MySQL books I read, and I do a lot of searches on the web. If I can't find the answer there I turn to the forum. It just takes more time than I like to find answers to my simple problems.

Lawrence

[r937]

you do not need to use php to test the queries

you should be using a front end application -- the one i use is HeidiSQL, although there are others (notably the MySQL Query Browser, which you can get from the MySQL web site)

[lkeeney]

you do not need to use php to test the queries

you should be using a front end application -- the one i use is HeidiSQL, although there are others (notably the MySQL Query Browser, which you can get from the MySQL web site)

I do have a database management program, but godaddy.com does not allow access to their databases except from their own servers. Because of this, I am running the php files from my client's web site which is located on godaddy.com.

Lawrence

[r937]

do they have phpmyadmin?

[lkeeney]

do they have phpmyadmin?

I don't remember the name of it, but they do have a database management program in their user's control panel. This is what I used to set up a test database.

However, now I have to be able to read the data with my clients web site. The application I am designing is a reservation database. The potential customer will have the ability to check the database to see if there are any openings for a particular day. If there is an opening, they can then fill in a form with about 14 fields and make a reservation.

To make this application work I have to access the database data through php code.

If you are interested, you can see the page I am working on here:
http://www.lasvegasmodelexperience.com/reservations

Lawrence

[Mittineague]

I don't think asking about phpmyadmin (or whatever it is) was meant to suggest you work exclusively through it. But it would be worth exploring the interface a bit. True, it will allow you to modify and edit, but it can also give information about the database and tables, eg. field names, data types, etc. and can show row contents so you can double-check whether or not your PHP code is doing what you think it is. I used to use the phpmyadmin interface to do edits that would have required queries that were too confusing for me (at the time) to write using PHP and I learned a bit about query syntax from the "query is [the query as put together by phpadmin], do you want to perform this action?" prompts.

[lkeeney]

I don't think asking about phpmyadmin (or whatever it is) was meant to suggest you work exclusively through it. But it would be worth exploring the interface a bit. True, it will allow you to modify and edit, but it can also give information about the database and tables, eg. field names, data types, etc. and can show row contents so you can double-check whether or not your PHP code is doing what you think it is. I used to use the phpmyadmin interface to do edits that would have required queries that were too confusing for me (at the time) to write using PHP and I learned a bit about query syntax from the "query is [the query as put together by phpadmin], do you want to perform this action?" prompts.

OK, now I understand.

I designed two different tables, one of which only has 2 rows and 2 fields, and the one I am testing with. I am not using anything our of the ordinary, here is the code this is not working.

$query = 'SELECT * FROM $usertable';
$result = mysql_query($query);

if($result) {
    while($row = mysql_fetch_array($result)){
        $name = $row['$yourfield'];
        echo 'Name: '.$name;
    }
}

I have the correct information stored in the variables. This code gets as far as the if statement then quits. I checked this by placing a number of echo statements through the code.

This code was supplied by godaddy for accessing their databases. Their technical support people can't get it to work either, but no one seems to know why at this point.

I will do as you suggest and take a further look into their database management software.

Lawrence

[Mittineague]

The problem may have to do with the quotes. PHP does not parse variables inside single quotes, but treats them as literal strings. eg.

$word = 'buggaboo';
echo "$word";// outputs buggaboo
echo '$word';// outputs $word 

Try something like

$query = "SELECT * FROM $usertable";
$result = mysql_query($query);

if($result) {
    while($row = mysql_fetch_array($result)){
        $name = $row["$yourfield"];
        echo 'Name: '.$name;//or concatenate your query like you did here
    }
else {
    echo 'No Result';
} 

[lkeeney]

The problem may have to do with the quotes. PHP does not parse variables inside single quotes, but treats them as literal strings. eg.

$word = 'buggaboo';
echo "$word";// outputs buggaboo
echo '$word';// outputs $word 

Try something like

$query = "SELECT * FROM $usertable";
$result = mysql_query($query);

if($result) {
    while($row = mysql_fetch_array($result)){
        $name = $row["$yourfield"];
        echo 'Name: '.$name;//or concatenate your query like you did here
    }
else {
    echo 'No Result';
} 

Thanks for pointing me in the right direction. I have been able to get it to work, but I don't understand why the original did not work.

The problem was here:

$usertable = 'test';
$query = "SELECT * FROM $usertable";

When I changed it to $query = "SELECT * FROM test"; it worked.

Lawrence

[Mittineague]

It could be that $usertable was not defined (no value assigned yet) when you referenced it. Or maybe the wrong datatype? i.e.

$usertable_int = 2006; // variable is an int
$usertable_str = '2006'; // variable is a string
$query = "SELECT * FROM $usertable_int"; // won't work
$query = "SELECT * FROM $usertable_str"; // might work
$query = "SELECT * FROM '" . $usertable_str . "'"; // should work
$result = mysql_query($query); 

[lkeeney]

It could be that $usertable was not defined (no value assigned yet) when you referenced it. Or maybe the wrong datatype? i.e.

$usertable_int = 2006; // variable is an int
$usertable_str = '2006'; // variable is a string
$query = "SELECT * FROM $usertable_int"; // won't work
$query = "SELECT * FROM $usertable_str"; // might work
$query = "SELECT * FROM '" . $usertable_str . "'"; // should work
$result = mysql_query($query); 

For the work I am doing, I don't need to have the table name in a variable. Today I created a web page that prints out the whole database.

Thanks for your help.

Lawrence

[r937]

For the work I am doing, I don't need to have the table name in a variable.

so what did your SELECT query end up looking like? do you still have a numeric table name?

[lkeeney]

so what did your SELECT query end up looking like? do you still have a numeric table name?

I ended up renaming the table "nine" rather than "2009".

Here is my SELECT query:
$sql='SELECT * FROM nine';

This works fine.

Right after I wrote that I don't need to have the table name in a variable I realized I do have to have a variable for the table name.

Can I in fact use a variable in the SELECT query? I tried it with the word nine in a variable, but that doesn't work. I then checked the 5 different books I have on the subject and none of them show a variable in the SELECT statement.

I must be missing something. After I got it working as shown above, I changed the word nine in the statement to a variable that contained the word nine, but it doesn't work.

I have my database set up with two tables, nine and ten for the years 2009 and 2010. I will need to access the appropriate table based on the date for which the customer wants to make a reservation.

Maybe I should ask this question in another post.

Lawrence

[Mittineague]

I am using a script that has several queries where I need to change the table from time to time. So instead of changing every query, I assign the table name to a variable and have the variable in the queries so I only need to make 1 change. eg.

include '../includes/db_config.php';
$connection = mysql_connect($host,$username,$password);
$select = mysql_select_db($database);
$table = 'wildflowers';

$query = "SELECT id, pagename, heading, content FROM $table ";
$query .= "WHERE id > 10 AND id < 100 ";
$query .= "ORDER BY id DESC ";
$result = mysql_query($query, $connection)
    or die ('<p>Query Error : ' .mysql_error() . '</p>'); 

[lkeeney]

I am using a script that has several queries where I need to change the table from time to time. So instead of changing every query, I assign the table name to a variable and have the variable in the queries so I only need to make 1 change. eg.

include '../includes/db_config.php';
$connection = mysql_connect($host,$username,$password);
$select = mysql_select_db($database);
$table = 'wildflowers';

$query = "SELECT id, pagename, heading, content FROM $table ";
$query .= "WHERE id > 10 AND id < 100 ";
$query .= "ORDER BY id DESC ";
$result = mysql_query($query, $connection)
    or die ('<p>Query Error : ' .mysql_error() . '</p>'); 

I am going to try this again in the morning. I spent the whole evening searching the web and looked at thousands of pieces of code, but never saw a variable following From in a Select query.

I don't know why it would not work for me before.

Lawrence

[lkeeney]

I just tried this and it did work.

$table=nine;
$query="SELECT * FROM $table";

I must have been doing something wrong before. I am surprised there are no references around showing using variables in this statement.

Lawrence

[BrandonK]

you should be using a front end application -- the one i use is HeidiSQL, although there are others (notably the MySQL Query Browser, which you can get from the MySQL web site)

Oh wow, that program is soooo much nicer than phpMyAdmin. Thanks for the tip!
[/thread hijack]