Page 1 of 1

mysql query error

Posted: Wed Jan 16, 2008 4:51 pm
by SidewinderX
Not sure whats going on with the PHP tags right now, so I hope feyd doesn't yell at me for using code tags instead. 8)

So I created a database with tables in phpMyAdmin and exported the SQL. Then took that SQL and pasted it into my code so the script creates the tables automatically. The code is below:

Code: Select all

<?php
$connection = mysql_connect("localhost", "root", "******") or die("Error mysql_connect(): " . mysql_error());
$db = mysql_select_db("test") or die("Error mysql_select_db(): " . mysql_error());
$tables ="CREATE TABLE IF NOT EXISTS `administrator` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(15) NOT NULL,
  `password` varchar(15) NOT NULL,
  `email` varchar(25) NOT NULL,
  `lastlogin` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
CREATE TABLE IF NOT EXISTS `files` (
  `id` int(11) NOT NULL auto_increment,
  `filename` varchar(50) NOT NULL,
  `size` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
CREATE TABLE IF NOT EXISTS `history` (
  `id` int(11) NOT NULL auto_increment,
  `filename` varchar(50) NOT NULL,
  `created` varchar(50) NOT NULL,
  `lastedit` varchar(50) NOT NULL,
  `action` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
 
$query = mysql_query($tables) or die("Error mysql_query(): " . mysql_error()); 
 
?>
But I get this error:
Error mysql_query(): 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 '; CREATE TABLE IF NOT EXISTS `files` ( `id` int(11) NOT NULL auto_incremen' at line 8
However, If I make each table creation its own query, it works fine. The code below produces no error:

Code: Select all

<?php
$connection = mysql_connect("localhost", "root", "vertrigo") or die("Error mysql_connect(): " . mysql_error());
$db = mysql_select_db("test") or die("Error mysql_select_db(): " . mysql_error());
$tables1 ="CREATE TABLE IF NOT EXISTS `administrator` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(15) NOT NULL,
  `password` varchar(15) NOT NULL,
  `email` varchar(25) NOT NULL,
  `lastlogin` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
 
 
 
$tables2 = "CREATE TABLE IF NOT EXISTS `files` (
  `id` int(11) NOT NULL auto_increment,
  `filename` varchar(50) NOT NULL,
  `size` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
 
$tables3 = "CREATE TABLE IF NOT EXISTS `history` (
  `id` int(11) NOT NULL auto_increment,
  `filename` varchar(50) NOT NULL,
  `created` varchar(50) NOT NULL,
  `lastedit` varchar(50) NOT NULL,
  `action` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
 
$query = mysql_query($tables1) or die("Error mysql_query(): " . mysql_error()); 
$query = mysql_query($tables2) or die("Error mysql_query(): " . mysql_error()); 
$query = mysql_query($tables3) or die("Error mysql_query(): " . mysql_error()); 
?>
Any ideas as to why the first code is throwing that error?

Re: mysql query error

Posted: Wed Jan 16, 2008 5:47 pm
by s.dot
With MySQL you can't send multiple queries in one go, however you can with MySQLi and mysqli_multi_query().

Re: mysql query error

Posted: Wed Jan 16, 2008 6:40 pm
by SidewinderX
Thank you!

Re: mysql query error

Posted: Thu Jan 17, 2008 12:42 am
by Mordred
@SidewinderX:
scottayy wrote:With MySQL you can't send multiple queries in one go, however you can with MySQLi and mysqli_multi_query().
Then again, just don't use these. As you already found out writing your code to use separate queries is not any harder. While this is safe with static queries such as these, for dynamic queries the security risk greatly outweighs the usability. I would make it a habbit not to use the multiquery function even when it's safe.

Btw, it looks like you're planning on keeping the user passwords in plaintext. This is not wise.

Re: mysql query error

Posted: Thu Jan 17, 2008 8:35 am
by Sindarin
Btw, it looks like you're planning on keeping the user passwords in plaintext. This is not wise.
I agree,
you can use md5:

Code: Select all

$db_password = md5($_POST[password]);