SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Read from database problem.

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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the error message tells you exactly where to look -- 2009

    try putting backticks around the table name -- `2009`
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    rename the table as t2009 and try again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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:

    Code:
    $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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please run the following in your own test database --
    Code:
    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 --
    Code:
    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 --
    Code:
    INSERT INTO `937` VALUES ( 1,'a' ) , ( 2,'b' );
    works, right?

    and we can select, too --
    Code:
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    please run the following in your own test database --
    Code:
    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 --
    Code:
    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 --
    Code:
    INSERT INTO `937` VALUES ( 1,'a' ) , ( 2,'b' );
    works, right?

    and we can select, too --
    Code:
    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

  8. #8
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,191
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    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
    PHP Code:
    $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.

  9. #9
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    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
    PHP Code:
    $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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    do they have phpmyadmin?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  14. #14
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,191
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    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.

  15. #15
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    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.

    Code:
    $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

  16. #16
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,191
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    The problem may have to do with the quotes. PHP does not parse variables inside single quotes, but treats them as literal strings. eg.
    PHP Code:
    $word 'buggaboo';
    echo 
    "$word";// outputs buggaboo
    echo '$word';// outputs $word 
    Try something like
    PHP Code:
    $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';


  17. #17
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    The problem may have to do with the quotes. PHP does not parse variables inside single quotes, but treats them as literal strings. eg.
    PHP Code:
    $word 'buggaboo';
    echo 
    "$word";// outputs buggaboo
    echo '$word';// outputs $word 
    Try something like
    PHP Code:
    $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

  18. #18
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,191
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    It could be that $usertable was not defined (no value assigned yet) when you referenced it. Or maybe the wrong datatype? i.e.
    PHP Code:
    $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); 

  19. #19
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    It could be that $usertable was not defined (no value assigned yet) when you referenced it. Or maybe the wrong datatype? i.e.
    PHP Code:
    $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

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by lkeeney View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  22. #22
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,191
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    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.

    PHP Code:
    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>'); 

  23. #23
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    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.

    PHP Code:
    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

  24. #24
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  25. #25
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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]
    MySQL v5.1.58
    PHP v5.3.6


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •