Prasing with a single quotation

[windriderme]

I'm working on a script to take info from a text file and read it into a MySQL database using PHP. Currently I have most every thing working but I am comming up against one rather odd wall. Since I have no control over how the data in the textfile is prased (getting it from outside source), my code stops running when it runs into single quotations (') that are part of the actual data (ie: Cox's Creek and 10'X12'). I can not think of how to let the code know that it may encounter this info and it should be treated as part of the data instead of the code. I guess I should menchion I am using an array to read everything. A snippit of my code is below. Thanks for any help in advance.

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

[span style=\"color:#0000BB\"]<?php

$filename [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#DD0000\"]\"http://londonkentucky.netvantedge.com/MLSDATARES.txt\"[/span][span style=\"color:#007700\"];

[/span][span style=\"color:#0000BB\"]$fd [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]fopen[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$filename[/span][span style=\"color:#007700\"],[/span][span style=\"color:#DD0000\"]\"r\"[/span][span style=\"color:#007700\"]) or die ([/span][span style=\"color:#DD0000\"]\"Could not read the file.\"[/span][span style=\"color:#007700\"]);

while (![/span][span style=\"color:#0000BB\"]feof[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$fd[/span][span style=\"color:#007700\"])) {

    [/span][span style=\"color:#0000BB\"]$str [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]fgets[/span][span style=\"color:#007700\"]([/span][span style=\"color:#0000BB\"]$fd[/span][span style=\"color:#007700\"],[/span][span style=\"color:#0000BB\"]4096[/span][span style=\"color:#007700\"]);

    [/span][span style=\"color:#0000BB\"]$line [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]explode[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"\t\"[/span][span style=\"color:#007700\"], [/span][span style=\"color:#0000BB\"]$str[/span][span style=\"color:#007700\"]);

        if ([/span][span style=\"color:#0000BB\"]$x [/span][span style=\"color:#007700\"]== [/span][span style=\"color:#0000BB\"]0[/span][span style=\"color:#007700\"]) {

            [/span][span style=\"color:#0000BB\"]$create_table [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"CREATE TABLE `MLSdata` (

                `\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]67[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` INT NOT NULL,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]0[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]2[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,

                `\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]3[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]1[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]92[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,

                `\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]13[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]50[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]52[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,

                `\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]54[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]90[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]14[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,

                `\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]51[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]53[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]55[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,

                `\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]91[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]60[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` VARCHAR( 10 ) NOT NULL,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]61[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` DECIMAL,

                `\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]76[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` DECIMAL,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]77[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` VARCHAR( 3 ) NOT NULL,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]65[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` DECIMAL,

                `\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]66[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` BIGINT,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]5[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` DECIMAL,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]6[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` VARCHAR( 10 ),

                `\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]9[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` TINYINT NOT NULL,`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]84[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` VARCHAR( 10 ),`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]85[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` SMALLINT,

                `\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]86[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` VARCHAR( 255 ),`\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]17[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"` VARCHAR( 250 )

                );\"[/span][span style=\"color:#007700\"]) or die ([/span][span style=\"color:#DD0000\"]\"Could not create table\"[/span][span style=\"color:#007700\"]);

            echo [/span][span style=\"color:#DD0000\"]\"Table created<br>\"[/span][span style=\"color:#007700\"];

        } else {

            [/span][span style=\"color:#0000BB\"]$insert_info [/span][span style=\"color:#007700\"]= [/span][span style=\"color:#0000BB\"]mysql_query[/span][span style=\"color:#007700\"]([/span][span style=\"color:#DD0000\"]\"INSERT INTO `MLSdata`

                VALUES (

                \'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]67[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]0[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]2[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]3[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]1[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]92[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',

                \'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]13[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]50[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]52[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]54[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]90[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]14[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',

                \'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]51[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]53[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]55[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]91[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]60[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]61[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',

                \'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]76[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]77[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]65[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]66[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]5[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]6[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',

                \'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]9[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]84[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]85[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]86[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\',\'\"[/span][span style=\"color:#007700\"].[/span][span style=\"color:#0000BB\"]$line[/span][span style=\"color:#007700\"][[/span][span style=\"color:#0000BB\"]17[/span][span style=\"color:#007700\"]].[/span][span style=\"color:#DD0000\"]\"\'

                );\"[/span][span style=\"color:#007700\"]) or die([/span][span style=\"color:#DD0000\"]\"Could not insert information\"[/span][span style=\"color:#007700\"]);

            echo [/span][span style=\"color:#DD0000\"]\"Row $x created<br>\"[/span][span style=\"color:#007700\"];

        }

[/span][span style=\"color:#FF8000\"]//finish going through file line by line to get specific fields

        

//increment line read

    [/span][span style=\"color:#0000BB\"]$x[/span][span style=\"color:#007700\"]++;

    }

[/span][span style=\"color:#0000BB\"]?>

[/span]

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

[ryanlwh]

use mysql_real_escape_string or addslashes when you get the line before exploding

 $line = mysql_real_escape_string($line);

[windriderme]

I tried your suggestion of mysql_real_escape_string and got the error message:

Fatal error: Call to undefined function: mysql_real_escape_string() in /home/httpd/vhosts/netvantedge.com/subdomains/londonkentucky/httpdocs/index.php on line 26

 

for addslashes I got:

Could not create table

 

This tells me that the phrasing of the array element ( `".$line[67]."` ) is somehow incorrect when using addslashes. Do you know why this would be? Also I was doing more research and found this in the php manual:

An example use of addslashes() is when you're entering data into a database. For example, to insert the name O'reilly into a database, you will need to escape it. Most databases do this with a \ which would mean O\'reilly. This would only be to get the data into the database, the extra \ will not be inserted. Having the PHP directive  magic_quotes_sybase set to on will mean ' is instead escaped with another '.

 

The PHP directive magic_quotes_gpc is on by default, and it essentially runs addslashes() on all GET, POST, and COOKIE data. Do not use addslashes() on strings that have already been escaped with magic_quotes_gpc as you'll then do double escaping. The function get_magic_quotes_gpc() may come in handy for checking this.

I know I am not using get, post, or cookie data but how can I tell if array data is being escaped? or how can I set it so array data is escaped automatically as well?

[ryanlwh]

is the table already in the database when you try the script?

[windriderme]

I have code that drops the table first.

is the table already in the database when you try the script?

316403[/snapback]

 

[windriderme]

Ok I found and tried something different.

[!--PHP-Head--][div class=\'phptop\']PHP[/div][div class=\'phpmain\'][!--PHP-EHead--]

$line = addcslashes($line,\"\'\");

[/span][!--PHP-Foot--][/div][!--PHP-EFoot--]

This allows the script to input the info into the db but only inputs 0 values. Can not think why this is but I guess it is back to the drawing board.

[ryanlwh]

try to echo the query and see what happens.