Problems building a CREATE TABLE query...

minorgod

I've been pulling my hair out over this for 2 days....

I use the following code to construct a query to create several new tables. The code gives me an SQL error, but when I paste the failed query into phpMyAdmin, it executes perfectly every time. I've tried using addslashes and everything else I can think of and nothing seems to help....

//include user authentication file
require_once("auth.inc.php");

//include config file which contains all config info
//including db_connection info. Since we're using
//PEAR DB functions, we've enabled  a nice callback
//function to spit out detailed errors...

require_once("config.inc.php");

//get the user-suppplied prefix for the new tables we will be creating
$db_prefix=$_GET['database_prefix'];

$query="CREATE TABLE IF NOT EXISTS ".$db_prefix."answers(
  answer_id int(11) NOT NULL auto_increment,
  question_id int(11) NOT NULL default '0',
  answer_text text NOT NULL,
  PRIMARY KEY (answer_id)
) TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS ".$db_prefix."chapters(
  chapter_id int(11) NOT NULL auto_increment,
  chapter_number smallint(6) NOT NULL default '0',
  chapter_title varchar(255) NOT NULL default '',
  course_id smallint(6) NOT NULL default '0',
  PRIMARY KEY (chapter_id)
) TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS ".$db_prefix."pages(
  page_id int(11) NOT NULL auto_increment,
  chapter_id int(11) NOT NULL default '0',
  page_title varchar(255) NOT NULL default '',
  page_number int(11) NOT NULL default '0',
  test_id int(11) NOT NULL default '0',
  page_content text NOT NULL,
  PRIMARY KEY (page_id)
) TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS ".$db_prefix."question_types(
  question_type_id smallint(6) NOT NULL auto_increment,
  description varchar(255) NOT NULL default '',
  PRIMARY KEY (question_type_id)
) TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS ".$db_prefix."questions(
  question_id int(11) NOT NULL auto_increment,
  correct_answer_id int(11) NOT NULL default '0',
  rationale_page_ids tinytext NOT NULL,
  question_type_id int(11) NOT NULL default '0',
  question mediumtext NOT NULL,
  PRIMARY KEY (question_id)
) TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS ".$db_prefix."students(
  student_id int(11) NOT NULL auto_increment,
  login tinytext NOT NULL,
  status char(2) NOT NULL default '',
  session_data text NOT NULL,
  PRIMARY KEY (student_id)
) TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS ".$db_prefix."tests(
  test_id int(11) NOT NULL auto_increment,
  test_question_ids text NOT NULL,
  test_type_id smallint(6) NOT NULL default '0',
  test_time_limit smallint(6) NOT NULL default '0',
  PRIMARY KEY (test_id)
) TYPE=MyISAM;";


$result = $dbh->query($query);


$query="INSERT INTO courses (course_name,course_url,course_start,course_prerequisites,productid,db_prefix) VALUES('".$_GET['course_name']."','".$_GET['course_url']."','".$_GET['course_start']."','".$course_prerequisites."','".$_GET['productid']."','".$db_prefix."');";

$result = $dbh->query($query);

The above code produces the following error:

DB Error: syntax error CREATE TABLE IF NOT EXISTS test6_answers( answer_id int(11) NOT NULL auto_increment, question_id int(11) NOT NULL default '0', answer_text text NOT NULL, PRIMARY KEY (answer_id) ); CREATE TABLE IF NOT EXISTS test6_chapters( chapter_id int(11) NOT NULL auto_increment, chapter_number smallint(6) NOT NULL default '0', chapter_title varchar(255) NOT NULL default '', course_id smallint(6) NOT NULL default '0', PRIMARY KEY (chapter_id) ); CREATE TABLE IF NOT EXISTS test6_pages( page_id int(11) NOT NULL auto_increment, chapter_id int(11) NOT NULL default '0', page_title varchar(255) NOT NULL default '', page_number int(11) NOT NULL default '0', test_id int(11) NOT NULL default '0', page_content text NOT NULL, PRIMARY KEY (page_id) ); CREATE TABLE IF NOT EXISTS test6_question_types( question_type_id smallint(6) NOT NULL auto_increment, description varchar(255) NOT NULL default '', PRIMARY KEY (question_type_id) ) TYPE=MyISAM; CREATE TABLE IF NOT EXISTS test6_questions( question_id int(11) NOT NULL auto_increment, correct_answer_id int(11) NOT NULL default '0', rationale_page_ids tinytext NOT NULL, question_type_id int(11) NOT NULL default '0', question mediumtext NOT NULL, PRIMARY KEY (question_id) ) TYPE=MyISAM; CREATE TABLE IF NOT EXISTS test6_students( student_id int(11) NOT NULL auto_increment, login tinytext NOT NULL, status char(2) NOT NULL default '', session_data text NOT NULL, PRIMARY KEY (student_id) ) TYPE=MyISAM; CREATE TABLE IF NOT EXISTS test6_tests( test_id int(11) NOT NULL auto_increment, test_question_ids text NOT NULL, test_type_id smallint(6) NOT NULL default '0', test_time_limit smallint(6) NOT NULL default '0', PRIMARY KEY (test_id) ) TYPE=MyISAM; [nativecode=1064 ** You have an error in your SQL syntax near '; CREATE TABLE IF NOT EXISTS test6_chapters( chapter_id int(11) NOT NULL auto' at line 6]

but if you paste this query from the error message into phpMyAdmin, it executes fine. Someone please tell me why. Please. I'm begging here. Really

[deleted]

THe PHP database interface cannot execute more than once query at a time for semi-obvious security reasons, and frighteningly obious error-checking reasons.

Try splitting the query up into seperate create-statements and execute them one at a time.

minorgod

Thanks. That did the trick. I'm still trying to get the hang of using PEAR DB objects. The documentation sucks. Serious lack of good examples. Can you recommend any good FAQ's or forums? I really hate the PEAR mailing list. Way too hard to find stuff.

[deleted]

I wouldn'yt recommend using PEAR at all... but then again, I woulsd also never recommend using mysql.

PEAR is meant to make the database operations transparent so you can change databases without having to change all your code.

If you don't need this feature, you can also write your won database functions using just PHP. That way you can define your own interface, and make it so that you understand what's happening.

minorgod

Again, I'd just like to know the proper way to free a friggin result object using PEAR DB, I'm not looking for a treatise on the shortcomings of MySQL as that's already been covered at length by many other people who think they need stored procedures to build a dynamic site that might get 1000 hits a day if they're lucky.

I've got no idea why you or anyone else wouldn't want to use MySQL for such purposes, but regardless, I do in fact need a database abstraction layer so I don't have to recode my entire application for all those "superior" (ie: commercial) databases. I'm not particularly interested in writing my own abstraction layer (which would be more tedious than difficult), so I've settled on PEAR since it's installed by default with every PHP install and is "supported" by a large community of developers. For the most part, it seems to be working great. I can query the database and fetch an entire result set into a variable or object with two lines of code, which is nice. It seems relatively fast. My main gripe is the weak, weak, whack documentation.

If there's another abstraction layer that is more widely supported (besides ADODB which is too heavy to lift), I'd love to hear about it. I've looked at 4 or 5 different libraries, but most of them are only supported by a handful of users which means they are not likely to be updated with new database options, and each one has its own set of functions that aren't very well standardized, so it's not like I can try a bunch of them and just swop them out if I don't like them.

Any thoughts? Any suggestions for a better abstraction layer? Any clue how to properly free a PEAR DB result object?

[deleted]

"Again, I'd just like to know the proper way to free a friggin result object using PEAR DB,"

uhm.. rtfm?

http://pear.php.net/manual/en/core.db.free.php

(ps: google works!)

"many other people who think they need stored procedures to build a dynamic site that might get 1000 hits a day if they're lucky. "

You don't have a clue do you? 🙂

PS: No need to reply, because this is only going to end in a mysql vs real-databases war, and you know I'll win...

minorgod

I'm already very familiar with the PEAR documentation and Google. I could care less about your opinions; I was looking for a very specific answer to a very specific question about using PEAR with MySQL when the documented methods produced errors. You chose to ignore this an instead to answer with...

I wouldn'yt recommend using PEAR at all... but then again, I woulsd also never recommend using mysql.

Whatever. You're right, I don't have a clue. Fortunately I have yet to need one, but I'm sure as soon as I do, you'll be the first to tell me I'm using the absolute worst clue I could have possibly chosen to get the job done and that REAL clue-havers use your particular brand of clue, which, if I spent the next 6 months learning about, would solve every problem I might ever have.

Still, thanks for answering the first question in this thread. You obviously aren't stupid, at least not about SQL and despite how delicious it would be to get into a flame war with someone who may or may not be able to spell the word "wouldn't", I will instead offer my thanks for your time and request no more of it.🙂

PS: No need to reply, because this is only going to end in a battle of wits, and I think we both know...