MySql not responding to query
-
Hi, I'm new to MySql and am just trying to get communicating with the database. Regrettably, perhaps, the database is set up on Godaddy, which has no support for MySql. The good news is that I was able to create the database on the server (shared hosting) and have been able to open a connection to it. Referring to the code here:
When the php code is called it prints out 'Connection established' and does not print 'Could not change into database'.
The bad news is that it does not seem to be responding to any queries.
Basically I can put garbage into any of the $sql statements here and MySql never seems to object. In fact I think it is ignoring everything.Basically I'm trying to get it to create a table in the database...but for now that's not even important, I'm really just trying to figure out why the system isn't even "trying" to understand the $sql statements I'm feeding it.
Is there some bit of syntax I'm not getting? But even if so, why isn't it throwing up any error messages?
In the code below, I can change, say TABLE to TABLE893hg, and no error message results.
$sql = "DROP TABLE IF EXISTS `users`";
$mysql_result=mysql_query( $sql, $connection );
echo('$mysql_result ');$sql = "CREATE TABLE `users`
(
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20 ) NOT NULL,\`address\` VARCHAR(30) unsigned NOT NULL, PRIMARY KEY ( \`id\` ) )";mysql_query( $sql, $connection );
$f_name="John Smith";
$f_address="BurlingtonVT";
$sql="INSERT INTO users (name, address)
values
('$f_name','$f_address')";echo($sql);
Maybe some of the parentheses aren't placed correctly or some other problem, but if it doesn't even tell me there is something wrong it's pretty hard to start finding the problem;)
Thanks for any insight.
Jeff
-
Hi, I'm new to MySql and am just trying to get communicating with the database. Regrettably, perhaps, the database is set up on Godaddy, which has no support for MySql. The good news is that I was able to create the database on the server (shared hosting) and have been able to open a connection to it. Referring to the code here:
When the php code is called it prints out 'Connection established' and does not print 'Could not change into database'.
The bad news is that it does not seem to be responding to any queries.
Basically I can put garbage into any of the $sql statements here and MySql never seems to object. In fact I think it is ignoring everything.Basically I'm trying to get it to create a table in the database...but for now that's not even important, I'm really just trying to figure out why the system isn't even "trying" to understand the $sql statements I'm feeding it.
Is there some bit of syntax I'm not getting? But even if so, why isn't it throwing up any error messages?
In the code below, I can change, say TABLE to TABLE893hg, and no error message results.
$sql = "DROP TABLE IF EXISTS `users`";
$mysql_result=mysql_query( $sql, $connection );
echo('$mysql_result ');$sql = "CREATE TABLE `users`
(
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20 ) NOT NULL,\`address\` VARCHAR(30) unsigned NOT NULL, PRIMARY KEY ( \`id\` ) )";mysql_query( $sql, $connection );
$f_name="John Smith";
$f_address="BurlingtonVT";
$sql="INSERT INTO users (name, address)
values
('$f_name','$f_address')";echo($sql);
Maybe some of the parentheses aren't placed correctly or some other problem, but if it doesn't even tell me there is something wrong it's pretty hard to start finding the problem;)
Thanks for any insight.
Jeff
Hi Jeff, more remarks: 1. add error checking (I am repeating myself, but I insist you add it at the start, so it can help you while developing the code); this should solve "In the code below, I can change, say TABLE to TABLE893hg, and no error message results." 2. you seem to be using some kind of backquotes? how about straight quotes ('aha') 3. maybe start out with a simpler table, just one VARCHAR, no attributes 4. your snippet does not insert anything, the final sql_query is missing :)
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
-
Hi, I'm new to MySql and am just trying to get communicating with the database. Regrettably, perhaps, the database is set up on Godaddy, which has no support for MySql. The good news is that I was able to create the database on the server (shared hosting) and have been able to open a connection to it. Referring to the code here:
When the php code is called it prints out 'Connection established' and does not print 'Could not change into database'.
The bad news is that it does not seem to be responding to any queries.
Basically I can put garbage into any of the $sql statements here and MySql never seems to object. In fact I think it is ignoring everything.Basically I'm trying to get it to create a table in the database...but for now that's not even important, I'm really just trying to figure out why the system isn't even "trying" to understand the $sql statements I'm feeding it.
Is there some bit of syntax I'm not getting? But even if so, why isn't it throwing up any error messages?
In the code below, I can change, say TABLE to TABLE893hg, and no error message results.
$sql = "DROP TABLE IF EXISTS `users`";
$mysql_result=mysql_query( $sql, $connection );
echo('$mysql_result ');$sql = "CREATE TABLE `users`
(
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20 ) NOT NULL,\`address\` VARCHAR(30) unsigned NOT NULL, PRIMARY KEY ( \`id\` ) )";mysql_query( $sql, $connection );
$f_name="John Smith";
$f_address="BurlingtonVT";
$sql="INSERT INTO users (name, address)
values
('$f_name','$f_address')";echo($sql);
Maybe some of the parentheses aren't placed correctly or some other problem, but if it doesn't even tell me there is something wrong it's pretty hard to start finding the problem;)
Thanks for any insight.
Jeff
I've reprinted Luc's response here from the GDb forum. ______________________________________________________________ Hi Jeff, three suggestions: 1. CodeProject has a separate forum for MySQL; 2. I take it you are aware MySQL isn't SQL, the query language is somewhat different (so start every Google search with "MySQL") 3. there is a function that generates a textual description of the last problem. I recommend you test for success after every MySQL function call, and print the result of that function whenever a failure occurs. Here is an example (using PHP, where . is the string concatenation operator): $this->;Connection=mysql_connect($host, $username, $password); if (!$this->;Connection) { die("DB Error: Could not connect to $host: ".mysql_error()); } That should help you in understanding what is wrong where. ;) ___________________________________________________________________ Thanks for your response. 1)Has been addressed as you can see. 2)Yes,I'm aware they are different. 3)Okay I am getting connection information. Using, for example, this code:
$connection=mysql_connect($hostname, $username, $password) or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db($dbname) or die(mysql_error());
echo "Connected to Database";This does give back hints as to what went wrong if there is a failure in the connection. But the connections all seem to be fine. I'm wondering if is there something similar to the 'die' statement for queries. To my knowledge you can't use 'die' in that way. Right now the queries are clearly failing (in fact they can be garbage) yet I'm getting no feedback from MySql. Thanks, Jeff
-
Hi Jeff, more remarks: 1. add error checking (I am repeating myself, but I insist you add it at the start, so it can help you while developing the code); this should solve "In the code below, I can change, say TABLE to TABLE893hg, and no error message results." 2. you seem to be using some kind of backquotes? how about straight quotes ('aha') 3. maybe start out with a simpler table, just one VARCHAR, no attributes 4. your snippet does not insert anything, the final sql_query is missing :)
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
Hi, Well I think there is something odd happening on the server side. Just for the hell of it I tried putting the query directly into the statement like this:
mysql_query
(
"CREATE a;idsofvTABLE 'users'
('name' VARCHAR(20))",
$connection
);Rather than loading it into a string variable first. Lo and behold it generated a much anticipated error! Then I removed the garbage and no error occurred. I tried adding and removing the garbage several times and it gave expected behavior every time. Very exciting. But then it went back to the bad old ways and failed to give an error message upon garbage addition. There have been other instances of odd server behavior. Obviously people successfully run MySql databases from Godaddy so somehow they are working around this weirdness. I just have to figure out how. Jeff
-
Hi, Well I think there is something odd happening on the server side. Just for the hell of it I tried putting the query directly into the statement like this:
mysql_query
(
"CREATE a;idsofvTABLE 'users'
('name' VARCHAR(20))",
$connection
);Rather than loading it into a string variable first. Lo and behold it generated a much anticipated error! Then I removed the garbage and no error occurred. I tried adding and removing the garbage several times and it gave expected behavior every time. Very exciting. But then it went back to the bad old ways and failed to give an error message upon garbage addition. There have been other instances of odd server behavior. Obviously people successfully run MySql databases from Godaddy so somehow they are working around this weirdness. I just have to figure out how. Jeff
Hi Jeff, your snippets surprised me in having multi-line string literals. I've never seen that before, but it seems to work on my local XAMPP. Maybe Godaddy's PHP does not understand it outside parentheses (just guessing here). I tend to concatenate strings, like so:
$sql="fdfdfdfddds";
$sql.="323434344324";
$sql.="323434344324";
$this->Results=mysql_query($query);
if (!$this->Results) die("DB Error in ReadQuery ($query)".mysql_error());BTW: the above snippet shows mysql_query also returns something you can use to check for errors. You could find out which version of PHP (and MySQL) Godaddy is running; when at it, also try to get hold of PHPMyAdmin, which is what I use to manually set up databases and tables, and inspect their content. FWIW: I'm using PHP 5.2.10 and MySQL 5.0.67 :)
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
-
Hi Jeff, your snippets surprised me in having multi-line string literals. I've never seen that before, but it seems to work on my local XAMPP. Maybe Godaddy's PHP does not understand it outside parentheses (just guessing here). I tend to concatenate strings, like so:
$sql="fdfdfdfddds";
$sql.="323434344324";
$sql.="323434344324";
$this->Results=mysql_query($query);
if (!$this->Results) die("DB Error in ReadQuery ($query)".mysql_error());BTW: the above snippet shows mysql_query also returns something you can use to check for errors. You could find out which version of PHP (and MySQL) Godaddy is running; when at it, also try to get hold of PHPMyAdmin, which is what I use to manually set up databases and tables, and inspect their content. FWIW: I'm using PHP 5.2.10 and MySQL 5.0.67 :)
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
Quick update, I continued working on this all today. I now have quite a bit up and running. Basically I just tried different code pieces I saw throughout the internet. Some worked and kept building on those. This is just intended to do the most rudimentary operations and does no useful work. For example the names entered into the table are hard coded -- of no practical use. And of course the conditionals redundantly test positive and negative. But the important thing is what it that it actually interacts with a database through php: Opens a connection Opens a database Deletes any existing table Creates a table Writes two rows of data to the table Prints out the first and last names from the two rows Closes the connection. (Actually it is only the last operation that I have not confirmed is working, but everything else definitely is.)
"; mysql_select_db($dbname) or die(mysql_error()); echo "Connected to Database "; $sql = "DROP TABLE IF EXISTS Persons "; $success=mysql_query($sql); if(!$success){echo" Table not deleted";} if($success){echo "Table deleted";} echo " "; $sql = "CREATE TABLE Persons ( FirstName varchar(15), LastName varchar(15), Age int )"; $success=mysql_query($sql); if(!$success){echo" Table not created";} if($success){echo "Table created";} echo " "; $success=mysql_query("INSERT INTO Persons (FirstName, LastName, Age) VALUES ('John', 'Peters', '18')"); if(!$success){echo" John Peters not added";} if($success){echo "John Peters added";} echo " "; $success=mysql_query("INSERT INTO Persons (FirstName, LastName, Age) VALUES ('Mark', 'Waters', '62')"); if(!$success){echo" Mark Waters not added";} if($success){echo "Mark Waters added";} echo " "; $result = mysql_query("SELECT * FROM Persons") or die(mysql_error()); echo "Made it through Select From"; echo " >"; echo "Printing contents of array"; echo " "; while($row = mysql_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo " ; } mysql_close($connection); if (!$connection) { echo "Connection closed"; } ?> -
Quick update, I continued working on this all today. I now have quite a bit up and running. Basically I just tried different code pieces I saw throughout the internet. Some worked and kept building on those. This is just intended to do the most rudimentary operations and does no useful work. For example the names entered into the table are hard coded -- of no practical use. And of course the conditionals redundantly test positive and negative. But the important thing is what it that it actually interacts with a database through php: Opens a connection Opens a database Deletes any existing table Creates a table Writes two rows of data to the table Prints out the first and last names from the two rows Closes the connection. (Actually it is only the last operation that I have not confirmed is working, but everything else definitely is.)
"; mysql_select_db($dbname) or die(mysql_error()); echo "Connected to Database "; $sql = "DROP TABLE IF EXISTS Persons "; $success=mysql_query($sql); if(!$success){echo" Table not deleted";} if($success){echo "Table deleted";} echo " "; $sql = "CREATE TABLE Persons ( FirstName varchar(15), LastName varchar(15), Age int )"; $success=mysql_query($sql); if(!$success){echo" Table not created";} if($success){echo "Table created";} echo " "; $success=mysql_query("INSERT INTO Persons (FirstName, LastName, Age) VALUES ('John', 'Peters', '18')"); if(!$success){echo" John Peters not added";} if($success){echo "John Peters added";} echo " "; $success=mysql_query("INSERT INTO Persons (FirstName, LastName, Age) VALUES ('Mark', 'Waters', '62')"); if(!$success){echo" Mark Waters not added";} if($success){echo "Mark Waters added";} echo " "; $result = mysql_query("SELECT * FROM Persons") or die(mysql_error()); echo "Made it through Select From"; echo " >"; echo "Printing contents of array"; echo " "; while($row = mysql_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo " ; } mysql_close($connection); if (!$connection) { echo "Connection closed"; } ?>Hi, For some reason this text system ate the "br"s. I think I was supposed to use the <> s listed at the top of this text box. Sorry. Jeff
-
Quick update, I continued working on this all today. I now have quite a bit up and running. Basically I just tried different code pieces I saw throughout the internet. Some worked and kept building on those. This is just intended to do the most rudimentary operations and does no useful work. For example the names entered into the table are hard coded -- of no practical use. And of course the conditionals redundantly test positive and negative. But the important thing is what it that it actually interacts with a database through php: Opens a connection Opens a database Deletes any existing table Creates a table Writes two rows of data to the table Prints out the first and last names from the two rows Closes the connection. (Actually it is only the last operation that I have not confirmed is working, but everything else definitely is.)
"; mysql_select_db($dbname) or die(mysql_error()); echo "Connected to Database "; $sql = "DROP TABLE IF EXISTS Persons "; $success=mysql_query($sql); if(!$success){echo" Table not deleted";} if($success){echo "Table deleted";} echo " "; $sql = "CREATE TABLE Persons ( FirstName varchar(15), LastName varchar(15), Age int )"; $success=mysql_query($sql); if(!$success){echo" Table not created";} if($success){echo "Table created";} echo " "; $success=mysql_query("INSERT INTO Persons (FirstName, LastName, Age) VALUES ('John', 'Peters', '18')"); if(!$success){echo" John Peters not added";} if($success){echo "John Peters added";} echo " "; $success=mysql_query("INSERT INTO Persons (FirstName, LastName, Age) VALUES ('Mark', 'Waters', '62')"); if(!$success){echo" Mark Waters not added";} if($success){echo "Mark Waters added";} echo " "; $result = mysql_query("SELECT * FROM Persons") or die(mysql_error()); echo "Made it through Select From"; echo " >"; echo "Printing contents of array"; echo " "; while($row = mysql_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo " ; } mysql_close($connection); if (!$connection) { echo "Connection closed"; } ?>Hi Jeff, Good, I see you got it working then. Some more remarks: - you can still use mysql_error() on all kinds of failing mysql_xxx calls; - I think you shouldn't be using quotes for integer literals (as in age '62'); in MS SQL it would be an error, in MySQL I don't know; - your very last if isn't truthful, it basically confirms the connection once got opened; however mysql_close returns a bool; and furthermore closing often isn't necessary, see the documentation[^]. And one suggestion: PHP, with all its typelessness and freedom, is a dangerous language; however it does offer classes. So I recommend you create your own little database class, which takes care of pesky details, and offers a higher-level API to your business layer. That is what I did. I also included conditional tracing, setting a tracelevel outputs a variable amount of trace information, that goes right to the HTML output (assuming it is all part of a web server app). Stuffing the database class in a separate file also achieves separation of concerns, e.g. your account information is required on one location only. :)
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
modified on Friday, September 18, 2009 6:56 PM
-
Hi Jeff, Good, I see you got it working then. Some more remarks: - you can still use mysql_error() on all kinds of failing mysql_xxx calls; - I think you shouldn't be using quotes for integer literals (as in age '62'); in MS SQL it would be an error, in MySQL I don't know; - your very last if isn't truthful, it basically confirms the connection once got opened; however mysql_close returns a bool; and furthermore closing often isn't necessary, see the documentation[^]. And one suggestion: PHP, with all its typelessness and freedom, is a dangerous language; however it does offer classes. So I recommend you create your own little database class, which takes care of pesky details, and offers a higher-level API to your business layer. That is what I did. I also included conditional tracing, setting a tracelevel outputs a variable amount of trace information, that goes right to the HTML output (assuming it is all part of a web server app). Stuffing the database class in a separate file also achieves separation of concerns, e.g. your account information is required on one location only. :)
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
modified on Friday, September 18, 2009 6:56 PM
Hi, I will definitely look into classes in php as this will simplify a lot of the mechanics. Going back to a previous point about strings, basically I haven't seen that the return/enter key has any effect on processing. It seems to view it the same way whether it is line separated or not. Maybe on some systems it does but I haven't seen any evidence of it on this server. Thanks, Jeff