9 Replies - 3478 Views - Last Post: 05 July 2011 - 08:25 AM Rate Topic: -----

#1 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 926
  • View blog
  • Posts: 3,205
  • Joined: 19-January 10

Multiple queries at once?

Posted 04 July 2011 - 05:25 PM

here's my script:
$mysqli->query(file_get_contents("../data/etc/dump.txt")) or die($mysqli->error);



Dump in pretty large and impossible to read, but I know it's valid because I tried it in phpMyAdmin. It contains stuff like creating tables and inserting stuff into those tables, but I'm getting an error. The error is on the line right after my first query.


tl;dr multiple queries in one works in phpMyAdmin but not through mysqli. Any way around it without having to break each query up?

Is This A Good Question/Topic? 0
  • +

Replies To: Multiple queries at once?

#2 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: Multiple queries at once?

Posted 04 July 2011 - 05:28 PM

It should be working if it works in phpMyAdmin.

I would ask to see an example, but it's "impossible to read."

Did you switch databases? Like try it with phpMyAdmin with MySQL, and then use an Oracle Database instead (e.x.)?

And, if not, why would you have to run it again? You ran the queries in phpMyAdmin, so the changes should be set in place already. :P
Was This Post Helpful? 1
  • +
  • -

#3 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 926
  • View blog
  • Posts: 3,205
  • Joined: 19-January 10

Re: Multiple queries at once?

Posted 04 July 2011 - 05:41 PM

View Postmaniacalsounds, on 04 July 2011 - 06:28 PM, said:

It should be working if it works in phpMyAdmin.

I would ask to see an example, but it's "impossible to read."

Did you switch databases? Like try it with phpMyAdmin with MySQL, and then use an Oracle Database instead (e.x.)?

And, if not, why would you have to run it again? You ran the queries in phpMyAdmin, so the changes should be set in place already. :P


Here's dump.txt:

DROP TABLE IF EXISTS `pointsBank`; CREATE TABLE IF NOT EXISTS `pointsBank` ( `id` int(11) NOT NULL auto_increment, `to` varchar(50) NOT NULL, `amount` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ; INSERT INTO `pointsBank` (`id`, `to`, `amount`) VALUES (45, 'drpepper', '100');DROP TABLE IF EXISTS `user`; CREATE TABLE IF NOT EXISTS `user` ( `id` int(10) NOT NULL auto_increment, `username` varchar(50) NOT NULL, `password` varchar(128) NOT NULL, `email` varchar(100) NOT NULL, `banned` varchar(3) NOT NULL default 'No', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; INSERT INTO `user` (`id`, `username`, `password`, `email`, `banned`) VALUES(1, 'awesome', '3bc59ca34df692694d7adb6fc85568c18cb2b3a60b042f08a7f0271d7c39e18a131ad5b9bb872a21d1e119abf8411f4aa47db912b98aa55d53cb8ef8f578fce8', 'email@mail.com', 'No'),(5, 'myuser', '58fef265268b8fc68aac5a7d05d9099ff97b63e98217d08c0d4a732c01925a650fb88369301f443baa13a6d75bc7fe56c73d1052d0169fb3d490c11e848c09fe', 'email@mail.com', 'No'),(6, 'drpepper', '3bc59ca34df692694d7adb6fc85568c18cb2b3a60b042f08a7f0271d7c39e18a131ad5b9bb872a21d1e119abf8411f4aa47db912b98aa55d53cb8ef8f578fce8', 'email@mail.com', 'No');


Works in PHPmyAdmin, not in PHP.
Was This Post Helpful? 0
  • +
  • -

#4 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: Multiple queries at once?

Posted 04 July 2011 - 05:45 PM

Ohh, I believe that you're going to have to seperate the queries, because you can't run multiple queries at once within the query() method. (Title makes sense now) Sorry, but it should be easy. They're seperated by semicolons.
Was This Post Helpful? 1
  • +
  • -

#5 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 926
  • View blog
  • Posts: 3,205
  • Joined: 19-January 10

Re: Multiple queries at once?

Posted 04 July 2011 - 05:49 PM

View Postmaniacalsounds, on 04 July 2011 - 06:45 PM, said:

Ohh, I believe that you're going to have to seperate the queries, because you can't run multiple queries at once within the query() method. (Title makes sense now) Sorry, but it should be easy. They're seperated by semicolons.



Crap, that sucks. It's gonna take a lot more then just separating. It's a part of a website backup tool I'm working on...

edit: actually... I might be able to explode by ; and execute in an array-style format.... I'll post back on how it works out.

This post has been edited by creativecoding: 04 July 2011 - 05:50 PM

Was This Post Helpful? 0
  • +
  • -

#6 creativecoding  Icon User is offline

  • Hash != Encryption
  • member icon


Reputation: 926
  • View blog
  • Posts: 3,205
  • Joined: 19-January 10

Re: Multiple queries at once?

Posted 04 July 2011 - 06:03 PM

Awesome! It works perfectly:

$file = file_get_contents("../data/etc/dump.txt");
		unlink("../data/etc/dump.txt");
		$file = explode(';', $file);
		$file = array_slice($file, 0, 6); // Empty key at the end, this is to remove it.
		foreach($file as $x => $q){
			$mysqli->query($q) or die("Failure on $x: " . $mysqli->error);
		}




And for debugging, this was the result:
Query 0:
DROP TABLE IF EXISTS `pointsBank`

Query 1:
CREATE TABLE IF NOT EXISTS `pointsBank` ( `id` int(11) NOT NULL auto_increment, `to` varchar(50) NOT NULL, `amount` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 

Query 2:
INSERT INTO `pointsBank` (`id`, `to`, `amount`) VALUES (45, 'drpepper', '100')

Query 3:
DROP TABLE IF EXISTS `user`

Query 4:
CREATE TABLE IF NOT EXISTS `user` ( `id` int(10) NOT NULL auto_increment, `username` varchar(50) NOT NULL, `password` varchar(128) NOT NULL, `email` varchar(100) NOT NULL, `banned` varchar(3) NOT NULL default 'No', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 

Query 5:
INSERT INTO `user` (`id`, `username`, `password`, `email`, `banned`) VALUES(1, 'awesome', '3bc59ca34df692694d7adb6fc85568c18cb2b3a60b042f08a7f0271d7c39e18a131ad5b9bb872a21d1e119abf8411f4aa47db912b98aa55d53cb8ef8f578fce8', 'email@mail.com', 'No'),(5, 'myuser', '58fef265268b8fc68aac5a7d05d9099ff97b63e98217d08c0d4a732c01925a650fb88369301f443baa13a6d75bc7fe56c73d1052d0169fb3d490c11e848c09fe', 'email@mail.com', 'No'),(6, 'drpepper', '3bc59ca34df692694d7adb6fc85568c18cb2b3a60b042f08a7f0271d7c39e18a131ad5b9bb872a21d1e119abf8411f4aa47db912b98aa55d53cb8ef8f578fce8', 'email@mail.com', 'No')



Works perfectly!
Was This Post Helpful? 0
  • +
  • -

#7 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: Multiple queries at once?

Posted 04 July 2011 - 08:30 PM

I'm glad it works! (And that is one way to seperate it, but you also could've done it by hand. I mean, it is only five queries, it's not that hard to seperate.) Anyway, glad to have helped. :)
Was This Post Helpful? 0
  • +
  • -

#8 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3515
  • View blog
  • Posts: 10,140
  • Joined: 08-June 10

Re: Multiple queries at once?

Posted 04 July 2011 - 10:55 PM

I never had problems running multiple Queries through PDO (though the last databases I tried it with were SQLite & PostgreSQL)
Was This Post Helpful? 0
  • +
  • -

#9 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: Multiple queries at once?

Posted 05 July 2011 - 01:47 AM

You can use mysqli function in PHP to run multi quires at once.
PHP & Mysql Multi Query
Was This Post Helpful? 1
  • +
  • -

#10 maniacalsounds  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 117
  • View blog
  • Posts: 472
  • Joined: 27-June 10

Re: Multiple queries at once?

Posted 05 July 2011 - 08:25 AM

Thanks noorahmad. I knew it wasn't really for me, but heyl=, i learned a new method. :)
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1