[RESOLVED] I am getting a SQL syntax error using ' please help :p

[zrocker]

Alright guys, i need help on this also... I have set up a program to take prayer request but when someone places a ' in the field it messes up the whole MySQL query.... what is the solotion to this issue?

this is the error i get...................
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 '0', prayer='testing something thomas - it's me'' at line 1

This is the actual code.............
if ("add_record" == $HTTP_POST_VARS["submit"]) {

$about_spaces = $HTTP_POST_VARS[prayer];
$about_spaces = nl2br($about_spaces);
$sql1 = "INSERT INTO prayer SET name='$HTTP_POST_VARS[name]',
viewable='$HTTP_POST_VARS[viewable]',
prayer='$about_spaces'";
$result=mysql_query($sql1)or die ("".mysql_error());

[djjjozsi]

<?php

/**
 *
 *
 *
 *
 */
	if ($_POST["submit"] == "add_record")
	{

	if(empty($_POST['name']))
	{
		$failes.="name(<i>1.</i>) field must have been filled!<br>"; //Y
		$was_failed=1;
	}


	if(empty($_POST['viewable']))
	{
		$failes.="viewable(<i>2.</i>) field must have been filled!<br>"; //Y
		$was_failed=1;
	}

	if(empty($_POST['prayer']))
	{
		$failes.="prayer(<i>3.</i>) field must have been filled!<br>"; //Y
		$was_failed=1;
	}

	if($was_failed==0)
	{
		include_once("connect.php");
            /* mysql_real_escape_string protect your database from the special characters */

		$name = mysql_real_escape_string($_POST['name']);
		$viewable = mysql_real_escape_string($_POST['viewable']);
		$prayer = mysql_real_escape_string($_POST['prayer']);

            /* And learn the mysql INSERT syntax ...    */
		$query = "INSERT INTO prayer (`name`, `viewable`, `prayer`)
VALUES ('$name','$viewable','$prayer')";
		$results = mysql_query($query) or die(mysql_error());
		}
		else
		print $failes;
	}

?>

[zrocker]

Thank you for helping me with the special character solution but you added something to my form that I needed also but i have spent all day trying to mod it to do this... can you help....

I want for the form to pop up... If name is blank it will change the value of prayer for the prayer and on the name it will have it in red stating over it you must fill in name before submitting. If name is filled out but prayer is left alone I want it to do the other way... Where i am getting hung up at is if both fields are left blank I want for it to say please fill in this NAME and PRAYER section of the form before submitting....

Is there a way to say...

if(empty($POST['prayer']) AND $POST['name']))

just wondering... thank you for your help...

[djjjozsi]

you might use if conditions, and isset function

<?php

if ($_POST["submit"] == "add_record")
{
/*   extract makes the POST values into separated variable*/
	extract($_POST);  

if(isset($prayer) AND empty($name))    
	{
/*  if the user fills the prayer but the name is missing,
lets make the prayer's value into the name's value  */

	$name=$prayer;
	}

	if(empty($name) AND empty($prayer))
	$failes="You must fill the forms!";
}

?>

[zrocker]

Thank you for the time you are taking out by answering my questions.... I learned a few things from you. Before i read this and after 7 hours of tossing my brain around... I finally arrived at a working program for the form submitting... but I just think there has to be a cleaner code.... Without putting much thought into it... how could this become less code? I used $bothunfilled and gave it a value of 1 if both fields are empty and if not it bypasses that and checks the other fields one on one... but after reading your above answer... just wondered what you thought... sorry for the messy code 🙂 ... probably explains why it took me 7 hours to get the answer 😃

<?PHP
require("dbconn.php");

 if ("add_record" == $HTTP_POST_VARS["submit"]) {

      if(empty($_POST['name']) and empty($_POST['prayer'])) {
          $bothunfilled=1;

		 $failes.="<span class=\"style7\">................</span>
	   <span class=\"style5\">PLEASE FILL OUT THE NAME & PRAYER SECTION....</span><br />
	   <span class=\"style5\"><strong>Name:</strong></span>
   <label>
   <input name=\"name\" type=\"text\" id=\"name\" size=\"55\" />
   </label>
      <br />
      <a href=\"../prayer.php\">
      <input name=\"submit\" type=\"hidden\" id=\"submit\" value=\"add_record\" />
      </a>

<br />
<span class=\"style5\"><strong>Prayer Request:</strong></span></ br>
<label>
<textarea name=\"prayer\" cols=\"48\" rows=\"5\" id=\"prayer\"></textarea>
</label>
<br />
__________________________________________________________<br />
Private:
<label>
<input name=\"viewable\" type=\"radio\" value=\"0\" checked=\"checked\" />
</label>
|| Public:
<label>
<input name=\"viewable\" type=\"radio\" value=\"1\" />
</label>
|| <span class=\"style1\">................................................</span>
<label>
<input type=\"submit\" name=\"Submit\" value=\"Submit\" />
</label>"; //Y
$was_failed=1;
}
if ("1" == "$bothunfilled") {
if($was_failed==0)
{
include_once("connect.php");

	$name = mysql_real_escape_string($_POST['name']); 
    $prayer = mysql_real_escape_string($_POST['prayer']); 

    $sql1 = "INSERT INTO prayer SET name='$name',
	viewable='$HTTP_POST_VARS[viewable]',
	prayer='$prayer'";
	$result=mysql_query($sql1)or die ("".mysql_error());
	} 

    else {
    print $failes; 
	} 
 }

 if ("1" !== "$bothunfilled") { 

 if(empty($_POST['name'])) 
    {

   $failes1.="<span class=\"style7\">................</span>
	   <span class=\"style5\">PLEASE FILL OUT THE NAME SECTION....</span><br />
	   <span class=\"style5\"><strong>Name:</strong></span>
   <label>
   <input name=\"name\" type=\"text\" id=\"name\" size=\"55\" />
   </label>
      <br />
      <a href=\"../prayer.php\">
      <input name=\"submit\" type=\"hidden\" id=\"submit\" value=\"add_record\" />
      </a>

<br />
Prayer Request:</ br>
<label>
<textarea name=\"prayer\" cols=\"48\" rows=\"5\" id=\"prayer\">$HTTP_POST_VARS[prayer]</textarea>
</label>
<br />
__________________________________________________________<br />
Private:
<label>
<input name=\"viewable\" type=\"radio\" value=\"0\" checked=\"checked\" />
</label>
|| Public:
<label>
<input name=\"viewable\" type=\"radio\" value=\"1\" />
</label>
|| <span class=\"style1\">................................................</span>
<label>
<input type=\"submit\" name=\"Submit\" value=\"Submit\" />
</label>"; //Y
$was_failed1=1;
}

	if(empty($_POST['prayer'])) 
    { 
        $failes1.="<span class=\"style7\">................</span>
	   <span class=\"style5\">PLEASE FILL OUT THE PRAYER SECTION....</span><br />
	   Name:
   <label>
   <input name=\"name\" type=\"text\" value=\"$HTTP_POST_VARS[name]\" id=\"name\" size=\"55\" />
   </label>
      <br />
      <a href=\"../prayer.php\">
      <input name=\"submit\" type=\"hidden\" id=\"submit\" value=\"add_record\" />
      </a>

<br />
<span class=\"style5\"><strong>Prayer Request:</strong></span></ br>
<label>
<textarea name=\"prayer\" cols=\"48\" rows=\"5\" id=\"prayer\"></textarea>
</label>
<br />
__________________________________________________________<br />
Private:
<label>
<input name=\"viewable\" type=\"radio\" value=\"0\" checked=\"checked\" />
</label>
|| Public:
<label>
<input name=\"viewable\" type=\"radio\" value=\"1\" />
</label>
|| <span class=\"style1\">................................................</span>
<label>
<input type=\"submit\" name=\"Submit\" value=\"Submit\" />
</label>"; //Y
$was_failed1=1;

    }

		if($was_failed1==0) 
    { 
        include_once("connect.php"); 

	$name = mysql_real_escape_string($_POST['name']); 
    $prayer = mysql_real_escape_string($_POST['prayer']); 

    $sql1 = "INSERT INTO prayer SET name='$name',
	viewable='$HTTP_POST_VARS[viewable]',
	prayer='$prayer'";
	$result=mysql_query($sql1)or die ("".mysql_error());

	echo"<br />
       <br />
       <br />
       <br />
       <span class=\"style7\">................</span>
	   <span class=\"style5\">Your Prayer Request Was Submited....</span><br />
       <span class=\"style7\">..................................</span>$HTTP_POST_VARS[name] <span class=\"style6\">God bless you greatly!</span>";
	 }  
    else {
    print $failes1; 
	}

}
}else{

    echo"Name:
   <label>
   <input name=\"name\" type=\"text\" id=\"name\" size=\"55\" />
   </label>
      <br />
      <a href=\"../prayer.php\">
      <input name=\"submit\" type=\"hidden\" id=\"submit\" value=\"add_record\" />
      </a>

<br />
Prayer Request:<br />
<label>
<textarea name=\"prayer\" cols=\"48\" rows=\"5\" id=\"prayer\"></textarea>
</label>
<br />
__________________________________________________________<br />
Private:
<label>
<input name=\"viewable\" type=\"radio\" value=\"0\" checked=\"checked\" />
</label>
|| Public:
<label>
<input name=\"viewable\" type=\"radio\" value=\"1\" />
</label>
|| <span class=\"style1\">................................................</span>
<label>
<input type=\"submit\" name=\"Submit\" value=\"Submit\" />
</label>";
}
?>

[leatherback]

Hi Rocker,

Have a look at this shortened version. I have not tested it, and it will as such have some typos and stuff. But it should give you an idea of how to handle these lengthy processes:

<?PHP
require("dbconn.php");

$name = '';
$prayer = '';
$viewable = 0;

if ($_POST["submit"] == "add_record") 
  {
  // Form was submitted. verify input
  $fail = false;
  $submit = true;
  $error = '';
  if($_POST['viewable'] == 1)
    {
	$view = 1;
	$viewcheck0 = '';
	$viewcheck1 = 'selected';
	}
  else
    {
	$view = 0;
	$viewcheck0 = 'selected';
	$viewcheck1 = '';
	}

  if(empty($_POST['name'])) 
    {
	$error .= "You need to submit a name";
	$fail = true;
	}
  else
    {
    $name = mysql_real_scape_string($_POST['name']);
    }

  if(empty($_POST['prayer']))
    {
	$error .= "Please fill out the prayer";
	$fail = true;
	}
  else
    {
    $prayer = mysql_real_scape_string($_POST['prayer']);
	}
  }
else
  {
  // Form was not submitted
  $fail   = true;
  $submit = false;
  }

// Create the form
$insertform="
<span class=\"style5\"><strong>Name:</strong></span>
<label>
<input name=\"name\" type=\"text\" id=\"name\" value=\"".$name."\" size=\"55\" />
</label>
<br />
<a href=\"../prayer.php\">
<input name=\"submit\" type=\"hidden\" id=\"submit\" value=\"add_record\" />
</a>

<br />
<span class=\"style5\"><strong>Prayer Request:</strong></span></ br>
<label>
<textarea name=\"prayer\" cols=\"48\" rows=\"5\" id=\"prayer\">".$prayer."</textarea>
</label>
<br />
__________________________________________________________<br />
Private:
<label>
<input name=\"viewable\" type=\"radio\" value=\"0\"  ".$viewcheck0." />
</label>
|| Public:
<label>
<input name=\"viewable\" type=\"radio\" ".$viewcheck1." value=\"1\" />
</label>
|| <span class=\"style1\">................................................</span>
<label>
<input type=\"submit\" name=\"Submit\" value=\"Submit\" />
</label>"; //Y



// --------------------------------- Handling input  -----------------------------------
if($submit && $fail == false)
  {
  // insert the data
  include_once("connect.php");

  $name = mysql_real_escape_string($_POST['name']);
  $prayer = mysql_real_escape_string($_POST['prayer']);

  $sql1 = "INSERT INTO prayer 
             (name, viewable, prayer) values
			 ('".$name."','".$viewable."','".$prayer."')";
  $result=mysql_query($sql1);
  if(!$result)
    {
	$fail= true;
	$error = "We could not insert the data";
	}
  }

if($fail)
  {
  echo "<h1>Some errors occured:</h1>".$error;
  echo $insertform;
  }
else
  {
  if($submit)
    {
    echo 'Thank you for insertng your info';
	}
  else
    {
	echo "Welcome to the prayer site. Please fill out theform below.";
    echo $insertform;
	}
  }

?>

[zrocker]

Thank you for helping me out on this.... looks a lot more cleaner then what I was doing... Thanks guys for helping me understand coding better...