SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 25
Thread: Read from database problem.
-
Nov 22, 2008, 15:26 #1
- 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 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
-
Nov 22, 2008, 16:09 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
the error message tells you exactly where to look -- 2009
try putting backticks around the table name -- `2009`
-
Nov 22, 2008, 18:02 #3
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Nov 22, 2008, 18:25 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
rename the table as t2009 and try again
-
Nov 22, 2008, 21:29 #5
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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"; }
I wonder if there is something on the server that is blocking me from accessing the data in my table.
Lawrence
-
Nov 22, 2008, 22:04 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
please run the following in your own test database --
Code:CREATE TABLE 937 ( i INTEGER , x VARCHAR(9) );
why? because a table name consisting of numerics needs to be escaped
now try this --Code:CREATE TABLE `937` ( i INTEGER , x VARCHAR(9) );
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' );
and we can select, too --Code:SELECT x,i FROM `937`;
which is why i suggested thqat you rename it to t2009, because then you don't need the backtick nonsense all da time
-
Nov 22, 2008, 22:26 #7
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Nov 22, 2008, 22:49 #8
- Join Date
- Jul 2005
- Location
- West Springfield, Massachusetts
- Posts
- 17,290
- Mentioned
- 198 Post(s)
- Tagged
- 3 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`";
Big Change Coming Soon - if you want your PMs save them now!
What you need to do to prepare for our migration to Discourse
A New SitePoint Forum Experience: Our Move to Discourse
-
Nov 22, 2008, 23:02 #9
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Nov 23, 2008, 02:02 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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)
-
Nov 23, 2008, 22:32 #11
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Nov 24, 2008, 04:37 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
do they have phpmyadmin?
-
Nov 24, 2008, 11:42 #13
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Nov 24, 2008, 12:31 #14
- Join Date
- Jul 2005
- Location
- West Springfield, Massachusetts
- Posts
- 17,290
- Mentioned
- 198 Post(s)
- Tagged
- 3 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.
Big Change Coming Soon - if you want your PMs save them now!
What you need to do to prepare for our migration to Discourse
A New SitePoint Forum Experience: Our Move to Discourse
-
Nov 24, 2008, 16:51 #15
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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; } }
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
-
Nov 24, 2008, 19:07 #16
- Join Date
- Jul 2005
- Location
- West Springfield, Massachusetts
- Posts
- 17,290
- Mentioned
- 198 Post(s)
- Tagged
- 3 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
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';
}
Big Change Coming Soon - if you want your PMs save them now!
What you need to do to prepare for our migration to Discourse
A New SitePoint Forum Experience: Our Move to Discourse
-
Nov 24, 2008, 21:56 #17
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Nov 24, 2008, 22:19 #18
- Join Date
- Jul 2005
- Location
- West Springfield, Massachusetts
- Posts
- 17,290
- Mentioned
- 198 Post(s)
- Tagged
- 3 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);
Big Change Coming Soon - if you want your PMs save them now!
What you need to do to prepare for our migration to Discourse
A New SitePoint Forum Experience: Our Move to Discourse
-
Nov 25, 2008, 19:20 #19
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Nov 25, 2008, 19:29 #20
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Nov 25, 2008, 22:29 #21
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Nov 26, 2008, 00:18 #22
- Join Date
- Jul 2005
- Location
- West Springfield, Massachusetts
- Posts
- 17,290
- Mentioned
- 198 Post(s)
- Tagged
- 3 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>');
Big Change Coming Soon - if you want your PMs save them now!
What you need to do to prepare for our migration to Discourse
A New SitePoint Forum Experience: Our Move to Discourse
-
Nov 26, 2008, 00:26 #23
- Join Date
- Nov 2005
- Location
- Southern Nevada, USA
- Posts
- 150
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Nov 26, 2008, 00:48 #24
- 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
-
Nov 26, 2008, 10:46 #25
- Join Date
- Dec 2005
- Posts
- 982
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Bookmarks