asked on

Correct syntax for htmlspecialchars(), ENT_QUOTES inside Values ($data);

I need to ensure punctuation marks are converted to HTML characters so I can run a one off script (an importer) using data in a spreadsheet.

The code is below. I want all $data[] variables punctuation to be converted to html codes so that punctuation marks don't cause errors in my script and so they display correctly on my website when called from the database.

I have used this in the past: htmlspecialchars($data[1]), ENT_QUOTES
but I don't know the syntax for this particular format of code.

Thank you!

$import="INSERT into venue(name,address1,address2,telephone,email,website) values('$data[0]','$data[1]','$data[2]','$data[5]','$data[6]','$data[7]')

Open in new window

SysTurn

Try using mysql_real_escape_string function: example:

$query = sprintf( "INSERT into venue(name,address1,address2,telephone,email,website) values('%s','%s','%s','%s','%s','%s')",
                  mysql_real_escape_string($data[0),
                  mysql_real_escape_string($data[1),
                  mysql_real_escape_string($data[2),
                  mysql_real_escape_string($data[5),
                  mysql_real_escape_string($data[6),
                  mysql_real_escape_string($data[7) );

Open in new window

IBMKenobi

ASKER

Hi, sorry, I should have posted the whole code. I have inserted your new code as below with this error:
Parse error: syntax error, unexpected T_STRING in URL/importer.php on line 24
(line 24 is WHERE NOT EXIST)

while (($data = fgetcsv($file, ",")) !== FALSE) {
      $import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website) values('%s','%s','%s','%s','%s','%s')",
mysql_real_escape_string($data[0]),
mysql_real_escape_string($data[1]),
mysql_real_escape_string($data[2]),
mysql_real_escape_string($data[5]),
mysql_real_escape_string($data[6]),
mysql_real_escape_string($data[7]) )

      WHERE NOT EXIST (
      SELECT name FROM venue
      WHERE
       name = '$data[0]'
       AND address1 = '$data[1]'
       AND address2 = '$data[2]'
       AND telephone = '$data[5]'
       AND email = '$data[6]'
       AND website = '$data[7]'
      )
";

Beverley Portlock

The correct syntax is shown at http://www.php.net/htmlspecialchars but it basically is

htmlspecialchars($data[1] , ENT_QUOTES );

rather than

htmlspecialchars($data[1]), ENT_QUOTES

In any case it is used for displaying things on the screen. For inserting into the database then mysql_real_escape_string is needed (see above althought the examples given are missing a closing ] on each line - $data[0] rather than $data[0

Beverley Portlock

@IBMKenobi - looks like we've been doing some simultaneous posting ;-)

SysTurn

    while (($data = fgetcsv($file, ",")) !== FALSE) {
      $import = sprintf( "INSERT into venue(name,address1,address2,telephone,email,website)
                            values('%s','%s','%s','%s','%s','%s') WHERE NOT EXIST (
                                SELECT name FROM venue WHERE name = '%s' AND address1 = '%s' AND address2 = '%s'
                                    AND telephone = '%s' AND email = '%s' AND website = '%s'
                            )",
                        mysql_real_escape_string($data[0]),
                        mysql_real_escape_string($data[1]),
                        mysql_real_escape_string($data[2]),
                        mysql_real_escape_string($data[5]),
                        mysql_real_escape_string($data[6]),
                        mysql_real_escape_string($data[7]),
                        mysql_real_escape_string($data[0]),
                        mysql_real_escape_string($data[1]),
                        mysql_real_escape_string($data[2]),
                        mysql_real_escape_string($data[5]),
                        mysql_real_escape_string($data[6]),
                        mysql_real_escape_string($data[7]) )

       //Execute Query $import
       //
       //
       
     }//End-While

Open in new window

IBMKenobi

ASKER

Having no luck here:

Parse error: syntax error, unexpected T_IF in URL\importer.php on line 35
Line 35: if ( @mysql_query($import) && (mysql_affected_rows() > 0)) {

I suspect this is an error from the earlier code, i.e. line 35 works fine, as the script works as long as there's no punctuation marks in the data.

Thanks.

while (($data = fgetcsv($file, ",")) !== FALSE) {
	$import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website) 
						values('%s','%s','%s','%s','%s','%s') 
WHERE NOT EXIST (
SELECT name FROM venue 
WHERE
name = '$s'
AND address1 = '%s'
AND address2 = '%s'
AND telephone = '%s'
AND email = '%s'
AND website = '%s')",
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]) )

if ( @mysql_query($import) && (mysql_affected_rows() > 0)) {

Open in new window

SysTurn

just add ; at the end of line 33 in your code

mysql_real_escape_string($data[7]) );

SysTurn

also note, line 22 you are using $s whicj is wrong, you should use %s

name = '$s'
should be:
name = '%s'

IBMKenobi

ASKER

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in URL\htdocs\cms\importer.php on line 40

Thanks again for your patience, I've raised the points to reflect this hopefully.

SysTurn

mmm, what's on line 40 ? :)

mostly, you forget a semicolon ; on line 39

IBMKenobi

ASKER

The rest of the script works when lines 15-35 were the original code (as long as the data being received had no punctuation) so I don't think the error is really on line 40 but I have posted the entire script for handiness.

<?PHP include 'dbconnection.php'; ?>

<?PHP
echo ('Opening File...<br />');
$file = fopen("gfaddresslist1.csv","r") or exit("Unable to open file!");

while (($data = fgetcsv($file, ",")) !== FALSE) {
	$import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website) 
						values('%s','%s','%s','%s','%s','%s') 
							WHERE NOT EXIST (
							SELECT name FROM venue 
							WHERE
	 						name = '%s'
	 						AND address1 = '%s'
	 						AND address2 = '%s'
	 						AND telephone = '%s'
	 						AND email = '%s'
	 						AND website = '%s'
							)",
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]) );

if ( @mysql_query($import) && (mysql_affected_rows() > 0)) {

		echo ('<span class="resultgood">Venue Added Successfully!</span><br />');
	}
		else {
			echo ( '<span class="resultbad">Error submitting venue:' . mysql_error().die()'<br /></span>' );
		}	
	$venueID = mysql_insert_id(); //contains last ID created by AUTO_INCREMENT in venue table

		$sql_venuetype = "INSERT IGNORE INTO venuetypelookup
							SET venueID=$venueID, venuetypeID=$data[4]";
				$ok1 = @mysql_query($sql_venuetype);
			if ($ok1) {
				echo ('<span class="resultgood">Venue Added to venuetypelookup Successfully!</span><br />');
			} else {
				echo ('Error inserting venueID and venuetypeID into venuetypelookup: '.mysql_error());
			}
		$sql_countylookup = "INSERT INTO countylookup 
							SET venueID=$venueID, countyID=$data[3]";
				if ( @mysql_query($sql_countylookup) ) {
				echo ('<span class="resultgood">Venue Added to countylookup Successfully!</span><br />');
			} else {
			echo ( '<span class="resultbad">Error submitting countylookup:' . mysql_error().'<br /></span>' );
			}
		$sql_ratinglookup = "INSERT INTO ratinglookup 
							SET venueID=$venueID";
				if ( @mysql_query($sql_ratinglookup) ) {
				echo ('<span class="resultgood">Venue Added to ratinglookup Successfully!</span><br />');
			} else {
			echo ( '<span class="resultbad">Error submitting to ratinglookup:' . mysql_error().'<br /></span>' );
			}
		$sql_paid = "INSERT INTO paid 
					SET venueID=$venueID, paid='0'";
				if ( @mysql_query($sql_paid) ) {
				echo ('<span class="resultgood">Venue Added to paid Successfully!</span><br />');
			} else {
			echo ( '<span class="resultbad">Error submitting to paid:' . mysql_error().'<br /></span>' );
			}
	mysql_query($import) or die(mysql_error());
}

if (feof($file)) echo ('End of file.'); 
fclose($file);
?>

Open in new window

SysTurn

line 32 in the previous code, invalid use of die()

try:

else {
die( '<span class="resultbad">Error submitting venue:' . mysql_error() . '<br /></span>' );
}

IBMKenobi

ASKER

I forgot about that, I left it in to stop the script going any further and entering venue data without the main venue going in correctly.

Result:
Opening File...

Warning: sprintf() [function.sprintf]: Too few arguments in URL\importer.php on line 33
Error submitting venue:Query was empty

SysTurn

Note: some arguments passed to sprintf are missing, I didn't notice that in your code :)

while (($data = fgetcsv($file, ",")) !== FALSE) {
	$import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website) 
						values('%s','%s','%s','%s','%s','%s') 
							WHERE NOT EXIST (
							SELECT name FROM venue 
							WHERE
	 						name = '%s'
	 						AND address1 = '%s'
	 						AND address2 = '%s'
	 						AND telephone = '%s'
	 						AND email = '%s'
	 						AND website = '%s'
							)",
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]),
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]) );

Open in new window

IBMKenobi

ASKER

Error submitting venue: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 'WHERE NOT EXIST ( SELECT name FROM venue WHERE name = ' at line 3

Ray Paseur

@IBMKenobi: Are you sure you don't want to hire a professional developer to give you a hand with this? Parse errors are pretty basic issues in PHP, and SQL syntax errors are pretty basic issues in MySQL. A pro should be able to get your app up and running in minutes.

If you want to post the COMPLETE code set as it exists now, I'll be glad to take a look. Regards, ~Ray

SysTurn

The following will only work on mysql 5

$import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website)
                        select '%s','%s','%s','%s','%s','%s' from dual
                            WHERE NOT EXIST (
                            SELECT name FROM venue
                            WHERE
                             name = '%s'
                             AND address1 = '%s'
                             AND address2 = '%s'
                             AND telephone = '%s'
                             AND email = '%s'
                             AND website = '%s'
                            )",
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]),
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]) );

Open in new window

IBMKenobi

ASKER

Hi Ray,

I would be happy to hire a pro programmer to complete this site as I've built the rest of the site and there are other issues needing fixed. This section of code was created for me in EE so I don't want to go messing with it and end up making things worse at the moment though. I also pay for my subscription to EE so I'm hoping to get these final issues sorted here.

Thanks,
Mike

IBMKenobi

ASKER

My server has PHP Version 5.2.6

SysTurn

I meant the MySQL server version not the PHP version.

Conditional inserts using the above syntax requires MySQL server v5

IBMKenobi

ASKER

Hi, sorry, I've just checked with my host and it's MySQL 5. Thanks.

ASKER CERTIFIED SOLUTION

SysTurn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

Well, without your data base it may be hard for us to test, but like I said, "If you want to post the COMPLETE code set as it exists now,..."

However it looks like SysTurn is making good progress, too.

IBMKenobi

ASKER

Hi SysTurn,

Thanks again for your patience so far. This still isn't working unfortunately.
I've rewritten it to try a 'simpler' format. i.e. noob

while (($data = fgetcsv($file, ",")) !== FALSE) {
$data0 = mysql_real_escape_string($data[0]);
$data1 = mysql_real_escape_string($data[1]);
$data2 = mysql_real_escape_string($data[2]);
$data5 = mysql_real_escape_string($data[5]);
$data6 = mysql_real_escape_string($data[6]);
$data7 = mysql_real_escape_string($data[7]);

$import = "INSERT INTO venue(name,address1,address2,telephone,email,website)
VALUES ($data0,$data1,$data2,$data5$,$data6,$data7)";

This is the data from the CSV file: (the empty line is an empty field in that particular address)
Richy's Bar & Bistro

                 Clonakilty
                 353 (0) 23 21852
                 richysbarandbistro@eircom.net
                 www.richysbarandbistro.com

The error displayed is below:
Error submitting venue: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 '\'s Bar & Bistro,,Clonakilty,353 (0) 23 21852$,richysbarandbistro@eircom.net ,ww' at line 2

Ray Paseur

You need quotes around the $data values like this...

$import = "INSERT INTO venue(name,address1)
VALUES ('$data0','$data1')";

IBMKenobi

ASKER

We're soooo close.
The script now works, entering the correct data into the form with all punctuation converted to HTML codes.

The only error is the venue is entered twice. The other fields are entered once based on the first venue ID created by mysql_insert_id() which is correct.

The venue receives two identical rows however. I can delete them manually but when I run the full CSV file there are a couple of hundred entries.

The venue names aren't unique but I'm assuming the telephone numbers are unique, .e.g. a chain of food outlets.

Ray Paseur

Let me try this again...

"If you want to post the COMPLETE code set as it exists now,..."

The reason I ask you for that is because I am just an expert, not a mind reader, and I cannot see your logic errors without seeing your code.

Also, UNIQUE is a term of art in data base processing. An attempt to insert a duplicate value into a UNIQUE column causes MySQL to throw error #1062. You can test for this in mysql_errno() and recognize it.

IBMKenobi

ASKER

Hi Ray,

I've re-attached the complete code but including the new while statement.

Thanks,
Mike

CSV file sample contents: black line is empty address1 field

Richy's Bar & Bistro

Clonakilty
353 (0) 23 21852
richysbarandbistro@eircom.net
www.richysbarandbistro.com

<?PHP include 'dbconnection.php'; ?>

<?PHP
echo ('Opening File...<br />');
$file = fopen("gfaddresslist1.csv","r") or exit("Unable to open file!");

while (($data = fgetcsv($file, ",")) !== FALSE) {
	$data0 = mysql_real_escape_string($data[0]);
	$data1 = mysql_real_escape_string($data[1]);
	$data2 = mysql_real_escape_string($data[2]);
	$data5 = mysql_real_escape_string($data[5]);
	$data6 = mysql_real_escape_string($data[6]);
	$data7 = mysql_real_escape_string($data[7]);
	
	echo ($data0.'<br />
			'.$data1.'<br />
			'.$data2.'<br />
			'.$data5.'<br />
			'.$data6.'<br />
			'.$data7.'<br />
			<br /><br />');
	
    $import = "INSERT INTO venue(name,address1,address2,telephone,email,website)
                        VALUES ('$data0','$data1','$data2','$data5','$data6','$data7')";

if ( @mysql_query($import) ) {

        echo ('<span class="resultgood">Venue Added Successfully!</span><br />');
    }
        else {
            die ( '<span class="resultbad">Error submitting venue:' . mysql_error() . '<br /></span>' );
        }
    $venueID = mysql_insert_id(); //contains last ID created by AUTO_INCREMENT in venue table

        $sql_venuetype = "INSERT IGNORE INTO venuetypelookup
                            SET venueID=$venueID, venuetypeID=$data[4]";
                $ok1 = @mysql_query($sql_venuetype);
            if ($ok1) {
                echo ('<span class="resultgood">Venue Added to venuetypelookup Successfully!</span><br />');
            } else {
                echo ('Error inserting venueID and venuetypeID into venuetypelookup: '.mysql_error());
            }
        $sql_countylookup = "INSERT INTO countylookup
                            SET venueID=$venueID, countyID=$data[3]";
                if ( @mysql_query($sql_countylookup) ) {
                echo ('<span class="resultgood">Venue Added to countylookup Successfully!</span><br />');
            } else {
            echo ( '<span class="resultbad">Error submitting countylookup:' . mysql_error().'<br /></span>' );
            }
        $sql_ratinglookup = "INSERT INTO ratinglookup
                            SET venueID=$venueID";
                if ( @mysql_query($sql_ratinglookup) ) {
                echo ('<span class="resultgood">Venue Added to ratinglookup Successfully!</span><br />');
            } else {
            echo ( '<span class="resultbad">Error submitting to ratinglookup:' . mysql_error().'<br /></span>' );
            }
        $sql_paid = "INSERT INTO paid
                    SET venueID=$venueID, paid='0'";
                if ( @mysql_query($sql_paid) ) {
                echo ('<span class="resultgood">Venue Added to paid Successfully!</span><br />');
            } else {
            echo ( '<span class="resultbad">Error submitting to paid:' . mysql_error().'<br /></span>' );
            }
    mysql_query($import) or die(mysql_error());
}

if (feof($file)) echo ('End of file.');
fclose($file);
?>

Open in new window

Ray Paseur

Thanks - I'll have a look. Can you please post the test data set that shows the error when I run that script?

IBMKenobi

ASKER

Hi Ray,

CSV file attached.
Just for better points allocation, am I better opening a new question ref double data entry?
That way I can give SysTurn points for his patience and replies and give yourself more points for sorting the double data entry?

Thanks,
Mike

gfaddresslist1.csv

Ray Paseur

Looks like you run the $import query twice. Lines 26 and 64.

This question was about HTMLSpecialChars() and that has been well answered, so I will sign off on this now. Just be sure to use that function before you echo any of the data out of your data base and into the browser output stream.

Best regards, ~Ray

IBMKenobi

ASKER

Thanks very much for your patience and time and to the other experts who all contributed great info.

Ray Paseur

@IBMKenobi: You can split the points if you think that more than one of the Experts helped you. Just a thought for going forward. As I look at this thread, it looks to me like BPortlock answered your posted question first and best.