PHP/ MySQL Query: Rows Not Being Inserted

Question

I have a large SQL file I'm trying to insert into the database. It wouldn't work via phpMyAdmin since it was so large, so I created a custom script:

<?php

ini_set('max_execution_time', 0);

$file = file_get_contents('./mySQLfile.sql');

//var_dump($file); die();

$conn = new mysqli('localhost', 'myUserName', 'myPassword', 'myDatabase');

$q = $file;

if ($result = $conn->query($q))
{
    echo '<font color="red">'.$conn->error.'</font>';
    echo '<br />';
}

?>

The file var_dump()'s properly. The connection to the database is created (I've tested it by returning values from the database). However, when I run the code, no data is inserted into the database. The beginning of the SQL file is as follows:

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 05, 2012 at 04:01 PM
-- Server version: 5.5.16
-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `myDatabase`
--

-- --------------------------------------------------------

--
-- Table structure for table `content_field_image_cache`
--

CREATE TABLE IF NOT EXISTS `content_field_image_cache` (
  `vid` int(10) unsigned NOT NULL DEFAULT '0',
  `nid` int(10) unsigned NOT NULL DEFAULT '0',
  `delta` int(10) unsigned NOT NULL DEFAULT '0',
  `field_image_cache_fid` int(11) DEFAULT NULL,
  `field_image_cache_list` tinyint(4) DEFAULT NULL,
  `field_image_cache_data` text,
  PRIMARY KEY (`vid`,`delta`),
  KEY `nid` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `content_field_image_cache`
--

INSERT INTO `content_field_image_cache` (`vid`, `nid`, `delta`, `field_image_cache_fid`, `field_image_cache_list`, `field_image_cache_data`) VALUES
(1000, 1000, 0, 1000, 1, NULL),
(1001, 1001, 0, 1001, 1, NULL),
(1002, 1002, 0, 1002, 1, NULL),

Why won't these values get inserted?

Thanks.

Run unbuffered queries instead. Because statements DELETE, INSERT, UPDATE do not return value — Yang
– Yang, Commented Dec 6, 2012 at 14:43

Marc B · Accepted Answer · 2012-12-06 14:43:12Z

2

PHP's mysql driver (mysql/mysqli/pdo) doesn't allow multiple queries in a single call as a security measure. You cannot feed an entire dump into a single query call like that.

You'd be better off simply doing something like

exec("mysql -u xxx -p < dump.sql");

instead.

answered Dec 6, 2012 at 14:43

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1477388 Over a year ago

I have googled but am not sure of the syntax here. Can you put it in context of "myUsername," "myPassword," "myDatabase?" Also, is there another way to do this if the exec command is not available?

user1477388 Over a year ago

Thanks, but I don't have command line access, this is a shared server. Can I use the help command via PHP script in my web browser? If so, how?

user1477388 Over a year ago

Nevermind, I found the syntax here dev.mysql.com/doc/refman/5.5/en/mysql.html

user1477388 Over a year ago

It still is not working and it's not giving me any error. What can I do to see why it's not working? I already added ini_set('error_reporting', E_ALL); to the top of the script.

Marc B Over a year ago

mysql may not be in the path of the shell that exec is using. try a fullblown /usr/bin/mysql-type path, adjusting to match your system's setup, of course.

|

Collectives™ on Stack Overflow

PHP/ MySQL Query: Rows Not Being Inserted

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related