0

I'm using a MySQL database on localhost and I'm trying to upload some world data. When I run the code below it creates the forms but then they don't get filled.

I tested the file reading. It was able to echo out the name, region, lat, and lng just fine.

<?php

require_once 'connect.ini.php';

$file_names = scandir('Countries');

foreach ($file_names as &$file_name)
    if ($file_name != '.' && $file_name != '..') {

        $file_path = '/Applications/XAMPP/xamppfiles/htdocs/series/dbsetup/Countries/'.$file_name;
        $file_handle = fopen("$file_path", "r");

        $sql = $sql = "CREATE TABLE `countries1`.`$file_name` (`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(20) NOT NULL, `region` VARCHAR(20) NOT NULL, `latitude` FLOAT(15) NOT NULL, `longitude` FLOAT(15) NOT NULL) ENGINE = MyISAM;";
        mysql_query($sql);

        while(!feof($file_handle)) {
            $explode = explode(", ", fgets($file_handle));
            $name = $explode[0];
            $region = $explode[1];
            $latitude = $explode[2];
            $longitude = $explode[3];
            $sql = "INSERT INTO `countries1`.`$file_name` (`id`, `name`, `region`, `latitude`, `longitude`) VALUES (NULL, \'$name\', \'$region\', \'$latitude\', \'$longitude\');";
            mysql_query($sql);
        }
    }
?>

Below is my connect.ini.php file although I'm pretty sure it's working because it is creating the forms.

<?php
$conn_error='Could not connect.';

$mysql_host='localhost';
$mysql_user='root';
$mysql_pass='';

$mysql_db='a_database';


if(!(@mysql_connect($mysql_host,$mysql_user,$mysql_pass)&&@mysql_select_db($mysql_db)))
    die($conn_error);
?>
Josh
  • 107
  • 2
  • 9
  • 1
    You create the table dynamically from your script?!? DONT DO THAT! – luk2302 Jun 15 '13 at 11:47
  • 2
    Side note : please use `mysqli` or `PDO` instead of `mysql`. – Kevin Jun 15 '13 at 11:47
  • 1
    @luk2302 I think he tries to automate the process because he wants create many tables using a `foreach` loop. – Kevin Jun 15 '13 at 11:49
  • Yeah, many tables that are all the exact same with only a different name, thats kind of usefull... – luk2302 Jun 15 '13 at 11:51
  • @luk2302 If I understand it, OP is just trying to create a database once, in a development environment. I don't think this is supposed to be a user accessible script. – Adi Bradfield Jun 15 '13 at 11:55
  • 1
    okay, thats possible.... why do you escape some characters here `" ... VALUES (NULL, \'$name\', \'$region\', \'$latitude\', \'$longitude\');"`? – luk2302 Jun 15 '13 at 11:57
  • @luk2302 that was the problem :) – Kevin Jun 15 '13 at 12:01
  • I'm pretty new to databases, what's wrong with mysql? Why shouldn't it be used? – Josh Jun 15 '13 at 12:13
  • There's nothing wrong with mysql. The problem is with the functions that start with `mysql_`. They are deprecated as of now and will be removed in the future by php. So, switch to the `mysql improved` (`mysqli_`) or PHP Data Objects(`PDO`). Have a look at this http://stackoverflow.com/questions/12097245/php-mysql-v-mysqli-v-pdo – Kevin Jun 15 '13 at 12:20

1 Answers1

0

Well, you should not escape single quotes when the variable are inside double quotes. So change the insert query to:

$sql = "INSERT INTO `countries1`.`abc` (`id`, `name`, `region`, `latitude`, `longitude`) VALUES (NULL, '$name', '$region', '$latitude', '$longitude');";

You also don't need the trailing semicolon ; inside the double quotes if you are using mysql functions to execute your query.

Kevin
  • 6,539
  • 5
  • 44
  • 54
  • 1
    Haha I've been using PHP and MySQL for a couple of years, and I never even knew that you could insert variables into a string that way! I've been doing the incredible tedious `"My ".$var." Statement"` – Adi Bradfield Jun 15 '13 at 12:05
  • @AdiBradfield lol! I learnt this from one such expert like you in SO. :) – Kevin Jun 15 '13 at 12:07
  • Didn't think of that, I guess I'm too reliant on my IDE highlighting. Worked like a charm. Thank you. – Josh Jun 15 '13 at 12:07
  • @KevinPaladin It makes me wonder what other little PHP tricks I'm missing out on! Can't believe I've never seen that in any tutorials when I was first learning! – Adi Bradfield Jun 15 '13 at 12:11
  • @AdiBradfield exactly! I'm sure you will learn a lot more like this, while you read other people's code here in SO. – Kevin Jun 15 '13 at 12:16