Problem inserting into database

sn0wy · #1 (**permalink**) 02-03-07, 02:00 PM

Hey Guys,

Trying to make a script where i just fill a textarea in and sends the text into a database, but the problem i have is when there is any ' in the text i got the error

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 're firmly established at the very heart of British life, providing the freshest ' at line 1

but if i dont use ' it goes in fine? Can anyone help?? my text is below

PHP Code:


			
<?PHP

include ("includes/db.php");



$id2 = $_POST['id2'];



$free_text = $_POST['free_text'];

$awards_text = $_POST['awards_text'];









$table_name = "baccount";

      

          $connection = @mysql_connect($dbserver, $dbuser, $dbpass) or die ("Couldn't Connect to database");

          $db = @mysql_select_db($dbname, $connection) or die(mysql_error());

          $sql = "UPDATE $table_name set free_text= '$free_text', awards_text= '$awards_text' WHERE id = '$id2'";

          $result = @mysql_query($sql,$connection) or die(mysql_error()); 

        

          ?>

Can someone have a look at my code and see what i need to do?

Thank you in advanced

grafman · #2 (**permalink**) 02-03-07, 09:11 PM

Do you have an apostrophe or single quote of some sort in your text? If you do then you need to use addslashes otherwise sql complains.

My guess is that you have something like "they're" right there and the string is trying to close at the apostrophe rather than at the end of the string where you expect it to.

sn0wy · #3 (**permalink**) 02-04-07, 06:20 AM

Yea thats the problem i am having, But because its a textarea and want users to enter ' and any other special characters, what do i do?

Nico · #4 (**permalink**) 02-04-07, 07:05 AM

PHP Code:


			


$free_text = mysql_real_escape_string($free_text);

http://us2.php.net/mysql_real_escape_string

sn0wy · #5 (**permalink**) 02-04-07, 07:14 AM

Is this correct?

PHP Code:


			
<?PHP

include ("includes/db.php");



$id2 = $_POST['id2'];



$free_text = $_POST['free_text'] ;



$free_text = mysql_real_escape_string($free_text);  



$awards_text = $_POST['awards_text'];



$table_name = "baccount";

      

          $connection = @mysql_connect($dbserver, $dbuser, $dbpass) or die ("Couldn't Connect to database");

          $db = @mysql_select_db($dbname, $connection) or die(mysql_error());

          $sql = "UPDATE $table_name set free_text= '$free_text', awards_text= '$awards_text' WHERE id = '$id2'";

          $result = @mysql_query($sql,$connection) or die(mysql_error()); 

        

          ?>

Nico · #6 (**permalink**) 02-04-07, 07:27 AM

Half. Use mysql_real_escape_string() on all user defined variables you're going to insert.

And you can do it directly like this.

PHP Code:


			
$free_text = mysql_real_escape_string($_POST['free_text']);

$awards_text = mysql_real_escape_string($_POST['awards_text']);

And if $id2 is supposed to be a numeric value, I'd use intval() on it.

PHP Code:


			
$id2 = intval($_POST['id2']);

www.php.net/intval

stormshadow · #7 (**permalink**) 02-04-07, 11:12 AM

sometimes in my code, the output changes but sometimes this wont word:

PHP Code:


			
 $sql = "UPDATE $table_name set free_text= '$free_text', awards_text= '$awards_text' WHERE id = '$id2'";

and you need to do this:

PHP Code:


			
 $sql = "UPDATE `$table_name` set free_text= '$free_text', awards_text= '$awards_text' WHERE id = '$id2'";

but like i said my output usually varies

HoTDaWg · #8 (**permalink**) 02-04-07, 02:14 PM

could you export your mysql table for us?

sn0wy · #9 (**permalink**) 02-05-07, 02:11 PM

Ok for some reason this is not inserting into a database

PHP Code:


			
<?PHP

include ("includes/db.php");



$id2 = $_POST['id2'];



$free_text = mysql_real_escape_string($_POST['free_text']); 

$awards_text = mysql_real_escape_string($_POST['awards_text']);  



$table_name = "baccount";

      

          $connection = @mysql_connect($dbserver, $dbuser, $dbpass) or die ("Couldn't Connect to database");

          $db = @mysql_select_db($dbname, $connection) or die(mysql_error());

          $sql = "UPDATE $table_name set free_text= '$free_text', awards_text= '$awards_text' WHERE id = '$id2'";

          $result = @mysql_query($sql,$connection) or die(mysql_error()); 

        

          ?>

          

          <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>foodplanet infonet</title>

<link href="includes/default.css" rel="stylesheet" type="text/css" />

</head><body>

<div id="extra">Classifications    (<?php echo ''.$id2.''; ?>)</div><div id="extra1">

Classification update completed<br />

<?php echo ''.$free_text.''; ?>

<a href="Javascript: self.close()">Close Window</a>

</div>   

</div>

</body>

</html>

The error i get is this

PHP Warning: mysql_real_escape_string(): A link to the server could not be established in /home/f/o/foodplanet/public_html/info/extratext2.php on line 7

sn0wy · #10 (**permalink**) 02-05-07, 02:11 PM

if you need it this is the page with the form in

PHP Code:


			
<?php 



include ("includes/db.php");



$id = $_GET['id'];



$process = $_POST['process'];





        $table_name2 = "baccount";

        $connection = @mysql_connect($localhost, $dbuser, $dbpass) or die ("Couldn't Connect to database");

        $db = @mysql_select_db($dbname, $connection) or die("Couldn't select database");

        $sql = "select * from $table_name2 WHERE id = '$id'";

        $result = @mysql_query($sql,$connection) or die ("Couldn't execute query");



        while ($row = mysql_fetch_array($result)) {   

        

        

                        

                $awards_text = $row["awards_text"];

                $free_text = $row["free_text"];

                

                }

                

                ?>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>foodplanet infonet</title>

<link href="includes/default.css" rel="stylesheet" type="text/css" />

</head>

<script> 

self.resizeTo(700,600);

self.moveTo(700,350);

</script>

<body>

<div id="extra">Classifications    (<?php echo ''.$id.''; ?>)</div>

<div id="extra1"><form id="form1" name="form1" method="post" action="extratext2.php">

  <table width="100%" border="0">

    <tr>

      <td><strong>More Information</strong></td>

      </tr>

    <tr>

      <td><label>

        <textarea name="free_text" cols="60" rows="5" wrap="physical" id="free_text"><?php echo ''.$free_text.''; ?>

</textarea>

      </label></td>

      </tr>

    <tr>

      <td><strong>Award Text </strong></td>

    </tr>

    <tr>

      <td><label>

        <textarea name="awards_text" cols="60" rows="5" wrap="physical" id="awards_text"><?php echo ''.$awards_text.''; ?>

</textarea>

      </label></td>

    </tr>

    <tr>

      <td><label>

        <div align="center">

          <input type="submit" name="Submit" value="Save" />

          <input name="id2" type="hidden" id="id2" value="<?php echo ''.$id.'';?>" />

          <input name="process" type="hidden" id="process" value="1" />

</div>

      </label></td>

    </tr>

  </table>

</form><br /><a href="Javascript: self.close()">Close Window</a>

</div>

</body>

</html>