Sep 012008

Looks like this is becoming a habit: I’m learning programming stuff and getting frustrated at annoying things and when I solve them I want to share it with others so others don’t have to go through my frustrations.

This one is related to MySQL with PHP programming. Some general tips:

  • Development on your computer is a good idea. A great portable server is XAMPP and it comes with Apache, MySQL and phpMyAdmin. The windows version is here.
  • Use phpMyAdmin, it’s awesome.
  • For long queries, it’s very helpful to output them, so write and echo $query; and be happier.
  • After outputting the queries, if you’re having errors, copy-paste them into an SQL box at phpMyAdmin, it will give you slightly better error reporting.
  • Watch for commas, especially when putting in many fields. After outputting the query that’s going into the MySQL engine, you can see commas more easily.
  • The line numbers at the end of errors are stupid, ignore them. Look at the query code that says the problem is at. In general, error reporting in MySQL is crap.
  • Single commas are wonderful for use inside the double quotation marks of your actual PHP variable definition. If you use double quotations throughout, you would have to escape each one and that’s no fun.
  • sprintf is also great for generating formatted strings, in the example at the end, I use it to keep my code neater. Basically, the placeholders in the string that’s the first argument (%s) is replaced by the other arguments, in order.

Some misc. troubleshooting:

MySQL queries generate errors!

MySQL query entries need to be sanitized to prevent things like slashes or quotation marks from messing up the query. So, for that, use mysql_real_escape_string. This one is recommended over the mysql_escape_string function, so use the “real” one.

You 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 ‘desc, prog_title) VALUES(‘ABS201Y1’, ‘Intro Abor Studies’, ‘blah blah’, ‘Aborigi’ at line1”

You’ve done everything you can but you can’t figure it out. This one pissed me off like no tomorrow. Turns out, that “desc”, one of my field names is actually a function of MySQL. When I created the query to insert data into that field, I didn’t use quotation marks around it. So, in general, make sure you’ve got quotation marks around your field names, and try not to name your fields after functions.

Here’s an example of a pretty good query I stole from somewhere and used in my program:

//MySQL – insert everything into courses table
$query = “SELECT * FROM courses WHERE code='”.mysql_real_escape_string($courseCode).”‘;”;
$mysql_result = mysql_query ($query)
or die (“Error in query: $query. ” . mysql_error() .”\n”);

if (mysql_num_rows($mysql_result) == 0) {
$q_insert = sprintf(“INSERT INTO courses”.
“(‘code’, ‘name’, ‘descrip’, ‘prog_title’) VALUES(‘%s’, ‘%s’, ‘%s’, ‘%s’);”,
mysql_real_escape_string($courseCode), mysql_real_escape_string($courseName),
mysql_real_escape_string($courseDesc), mysql_real_escape_string($progTitle) );
echo “\n”.$q_insert . “\n”;
$mysql_result = mysql_query($q_insert)
or die (“Error in query: $query. ” . mysql_error() .”\n”);
echo “mysql courses rows: ” .mysql_affected_rows() . ” rows affected.”;


  One Response to “MySQL Troubleshooting”

  1. […] public links >> phpmyadmin MySQL Troubleshooting Saved by sagolla on Wed 08-10-2008 How to restore MySQL backup using phpMyAdmin Saved by […]

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>