mysql query error

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

mysql query error

Post 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?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: mysql query error

Post by s.dot »

With MySQL you can't send multiple queries in one go, however you can with MySQLi and mysqli_multi_query().
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Re: mysql query error

Post by SidewinderX »

Thank you!
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: mysql query error

Post 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.
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: mysql query error

Post 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]);
Post Reply