PHP - Mysql Insert - Problems With Quotes ('")

Hi All,

I have a html form that I can submit using php, which then enters information into a database. This works fine when the text is just standard text - for example "the cat is on the mat". But if I put in "the cat's on the mat" - then the speach mark throws an error up!

Is there any way around this?

Thanks
Matt

An insert function is not inserting.  I keep getting "Could not store data, please try again."

After some probing, it seems that "$result = $conn->query($user_degree_insert_query); " is where the problem is.  Variables before it seem to pass testing.  I can't figure out why this line is messing thins up though.

Any idea?  Thank you in advance for your help!

The "//$degree_id = mysql_fetch_array($degree_id_query, MYSQL_BOTH) ;" is commented out because it was giving me trouble, i replaced it with the line above that


                $conn = db_connect();

$arraycount = count($degree_Array);

//loop through the categories chosen and add them each into member-category
for($i =0; $i < $arraycount; $i++){ 
$degree_id_query = "SELECT degree_id FROM degree WHERE degree_type ='".$degree_Array[$i]."'";
$result = $conn->query($degree_id_query);

//place the id found into the array, which automatically cancatonates it.
$degree_id = $result->fetch_array();

//$degree_id = mysql_fetch_array($degree_id_query, MYSQL_BOTH) ;

$user_degree_insert_query = "INSERT INTO user-degree (
`user_id` ,
`degree_id`
)
VALUES (
'".$user_id."', '".$degree_id[0]."'
)";

$result = $conn->query($user_degree_insert_query); 

if($result) {
header("Location: survey2.php"); 
}else { 
echo "<p>Could not store data, please try again.</p>"; 
exit;

<?php

require_once('upper.php');

require_once('database.php');

echo $error_msg='';

if(isset($_POST['submit']))

{

$LoginId=mysqli_real_escape_string($dbc,trim($_POST['LoginId']));

$Password1=mysqli_real_escape_string($dbc,trim($_POST['Password1']));

$Password2=mysqli_real_escape_string($dbc,trim($_POST['Password2']));

$Name=mysqli_real_escape_string($dbc,trim($_POST['Name']));

$Age=mysqli_real_escape_string($dbc,trim($_POST['Age']));

$BloodGroup=mysqli_real_escape_string($dbc,trim($_POST['BloodGroup']));

if(!isset($_POST['Sex']))

{

echo 'Please enter Sex<br>';

}

else{

$Sex= mysqli_real_escape_string($dbc,trim($_POST['Sex']));

}

$Qualification=mysqli_real_escape_string($dbc,trim($_POST['Qualification']));

$ContactNumber=mysqli_real_escape_string($dbc,trim($_POST['ContactNumber']));

$Email=mysqli_real_escape_string($dbc,trim($_POST['Email']));

$Address=mysqli_real_escape_string($dbc,trim($_POST['Address']));

$AboutYourself=mysqli_real_escape_string($dbc,trim($_POST['AboutYourself']));

//$countCheck=count($_POST['checkbox']);

//echo $countCheck;

//$checkbox=$_POST['checkbox'];

//$countCheck=count($checkbox);

    if(empty($LoginId)){echo 'Please enter Login Id';}

    elseif(empty($Password1)){echo 'Please enter Password';}

    elseif(empty($Password2)){echo 'Please confirm Password';}

    elseif($Password1!==$Password2){echo 'Password didn\'t match';}

        elseif(empty($Name)){echo 'Please enter Name';}

        elseif(empty($Age)){echo 'Please enter Age';}

        elseif(!isset($_POST['Sex'])){}

        elseif(empty($Qualification)){echo 'Please enter Qualification';}

        elseif(empty($ContactNumber)){echo 'Please enter Contact Number';}

        elseif(empty($Email)){echo 'Please enter Email';}

        elseif(empty($Address)){echo 'Please enter Address';}

        elseif(empty($AboutYourself)){echo 'Please enter About Yourself';}

        elseif(!isset($_POST['checkbox'])){ echo 'You have to register at least one activity.';}

        else

        {

        require_once('database.php');

        $query="select * from registration where LoginId='$LoginId'";

        $result=mysqli_query($dbc,$query);

        if(mysqli_num_rows($result)==0)    

        {        

        $checkbox=$_POST['checkbox'];

        $countCheck=count($_POST['checkbox']);

        $reg_id=' ';

        for($i=0;$i<$countCheck;$i++)

        {

        $reg_id=$reg_id.$checkbox[$i].',';

        }

        $query="insert into registration (LoginId,Password,Name,Age,BloodGroup,Sex,Qualification,ContactNumber,Email,Address,AboutYourself,Activity)values ('$LoginId',SHA('$Password1'),'$Name','$Age','$BloodGroup','$Sex','$Qualification','$ContactNumber','$Email','$Address','$AboutYourself',',$reg_id')";

        $result=mysqli_query($dbc,$query) or die("Not Connect");

        

        echo ' Dear '.$Name.'.<br>Your Account is waiting for approval<br>';



        //header('Location: index.php');

        //    header('Location: Registration.php');

        }

        else

        {

        echo 'Dear '.$Name. ', <br> An account already exist with login-id<b> '.$LoginId.'</b> <br>Please try another login-id';

        }}    

}        

?>



<html>





<body>



<?php

echo $error_msg; ?>



<form action='<?php echo $_SERVER['PHP_SELF'];?>' method='post'>

<div class="registration_and_activity">



<table border="0" width="400">

<tr><td colspan="2">

<h3>New User?</h3></td></tr>

<tr><td width="120">

Enter Login id</td><td><input type='text' name='LoginId' value='<?php if(!empty($LoginId))echo $LoginId;?>' /></td></tr>

<tr><td>

Enter Password</td><td><input type='password' name='Password1' value='<?php if(!empty($Password1))echo $Password1;?>' /></td></tr>

<tr><td>

Confirm Password</td><td><input type='password' name='Password2' value='<?php if(!empty($Password2))echo $Password2;?>' /></td></tr>

<tr><td>

Enter Name</td> <td><input type='text' name='Name' value='<?php if(!empty($Name))echo $Name;?>' /></td></tr>

<tr><td>

Enter Age</td><HEAD>

   <SCRIPT language=Javascript>

      

      function isNumberKey(evt)

      {

         var charCode = (evt.which) ? evt.which : event.keyCode

         if (charCode > 31 && (charCode < 48 || charCode > 57))

            return false;



         return true;

      }

      

      

     

   </SCRIPT>

   </HEAD>

<td><INPUT onkeypress='return isNumberKey(event)' type='text' name='Age' value='<?php if(!empty($Age))echo $Age;?>'/></td></tr>



<tr><td>

Enter Blood</td><td><input type='text' name='BloodGroup'value='<?php if(!empty($BloodGroup))echo $BloodGroup;?>' /></td></tr>



<tr><td>

Enter Sex</td><td><input type='radio' name='Sex' style='width:16px; border:0;' 'value='Male' />Male   <input type='radio' name='Sex' style='width:16px; border:0;' 'value='Female' />Female</td></tr>



<tr><td>

Enter Qualification</td><td><input type='text' name='Qualification' value='<?php if(!empty($Qualification))echo $Qualification;?>' /></td></tr>



<tr><td>

Enter Contact Number </td><td><input onkeypress='return isNumberKey(event)'type='text' name='ContactNumber' value='<?php if(!empty($ContactNumber))echo $ContactNumber;?>' /></td></tr>



<tr><td>

Enter Email</td><td><input type='text' name='Email' value='<?php if(!empty($Email))echo $Email;?>' /></td></tr>



<tr><td>

Enter Address</td><td><input type='text' name='Address' value='<?php if(!empty($Address))echo $Address;?>' /></td></tr>





<tr><td>

About Yourself :</td></tr>

<tr><td colspan="2"><textarea rows='10' cols='40' name='AboutYourself' value='<?php if(!empty($AboutYourself))echo $AboutYourself;?>' /></textarea></td></tr>

<tr><td>



<?php echo"

<tr><td colspan='2'><b>Select fields for which you want to register</b></td></tr>";



require_once('database.php');

$query="select * from activity";

$result=mysqli_query($dbc,$query);

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

$Title=$row['Title'];

$ActivityId=$row['ActivityId'];

echo "<tr><td>$Title</td>";

//echo $ActivityId;

[B]echo "<td><input type='checkbox' name='checkbox[]' value=$Title [/B]style='width:14px; text-align:right;'/></td></tr>";//value=$ActivityId tells ActivityId variable extracts with name="checkbox"

echo "<br/>";

}

echo "<tr><td colspan='2' align='center'><input type='submit' value='Register' name='submit' style='background:url(./images/button_img2.png) no-repeat 10px 0px; width:100px; padding:3px 0 10px 0; color:#FEFBC4; border:0;'/></td></tr><br>";

    

echo " </td></tr></table>



</div>



</form>

</body>

</html>";

require_once('lower.php');

?>

In above code when $Title="Fire" or any single word, it inserts in database properly but when $Title="Terrist Attack" or any data more than one word it inserts only the first word "Terrist".
I want to insert the whole "Terrist Attack " as it is in database.........
What i should do??????????/
Plzzzzzzzzzzz help Anyone????????????

When I add a ' or " quotes in a textarea I get a sql error when it tries to insert the record.    I was told to use mysqli_real_escape_string but that didn't work.    Here's my code -

   $blog= mysqli_real_escape_string($con, $_POST['blog']);
   

        $blog= $_POST['message'];
            
        $sql = "SELECT * FROM table WHERE `message` = '{$message}'";
        
        $result = mysql_query($sql);
        
        if ( mysql_num_rows ( $result ) > 0 )
        {
                        
            $error = "Message Exists.";
        }
        
        else
        {
        
         $error = "This message does not exist. Insert it!!!";
        
         $sql="INSERT INTO table (message) VALUES ('$_POST[message])";
        
        
        }
        
        if (!mysqli_query($con,$sql))
         {
         die('Error: ' . mysqli_error($con));
         }
        
        mysqli_close($con)

Edited by barkly, 26 October 2014 - 09:31 PM.

Quotation marks are confusing me.   What do you guys use when it comes to quotation marks?   In HTML attributes and throughout the bodies of my web pages, I use the HTML entity (&quot;). For example:

<a href="" title="Read &quot;Article Name&quot;">

<p>In his new book, he says: &quot;This is a quote.&quot;</p>

I thought that this is the best practice.   However, today, I read that it's perfectly safe to use straight quotes (") in the body, and that I should use the HTML entity only in HTML attributes.   Is that correct?   But what if I want to use curly quotes in the body instead of straight quotes? Should I always use the HTML entities for curly quotes (&ldquo; and &rdquo;), or can I also safely use the characters (“”)?   I heard that straight quotes are safe in all browsers, even if you don't specify the character set of your web pages, but that curly quotes are only safe if you specify the character set or if you use the HTML entities.   Is that true?   And what about the <q> tag? Apparently, it's compatible with all browsers but they treat it differently.
Edited by Fluoresce, 30 August 2014 - 07:37 AM.

The fancy-looking quotes won't insert into my DB, so I'm trying to convert them to %93 & %94 or normal quotes.    Nothing I've tried works.   Code:   $fancy=" “test” ";
$fixed=htmlentities($old, ENT_QUOTES);   echo "fancy: $fancy<br>"; echo "fixed: $fixed";

Results:

fancy: “test”
fixed:   I want $fixed to be %93test%94, or even "test" would work. 
Edited by rwmaho, 18 October 2014 - 01:28 PM.

Hello everyone!  This is my first post.  I am very new to php and mysql and coding in general.  It has not been made 100% clear to me as to when I should use single quotes, double quotes, and {}.  From what I gather you use single quotes for literal interpretation...  so if you put something like a variable in single quotes and echoed it, it would literally echo it as it is written and not the value of the variable.  in double quotes, I gather that it will echo the value of the variable.  as for {} I am unclear as to when to use the curly brackets for a variable.  I am assuming if you had a statement in single quotes and you put a variable in curly brackets you would get the value of the variable?
Edited by LazerOrca, 25 November 2014 - 11:20 PM.

Can anyone tell me why this is not INSERTing? My array data is coming out just fine.. I've tried everything I can think of and cannot get anything to insert.. Ahhhh!

<?php
  $query = "SELECT RegionID, City FROM geo_cities WHERE RegionID='135'";
  $results = mysqli_query($cxn, $query);
  $row_cnt = mysqli_num_rows($results);
  echo $row_cnt . " Total Records in Query.<br /><br />";
  if (mysqli_num_rows($results)) {
      while ($row = mysqli_fetch_array($results)) {
          $insert_city_query = "INSERT INTO all_illinois SET state_id=$row[RegionID], city_name=$row[City] WHERE id = null" or mysqli_error();
          $insert = mysqli_query($cxn, $insert_city_query);
          if (!$insert) {
              echo "INSERT is NOT working!";
              exit();
          }
          
          echo $row['City'] . "<br />";
          
          echo "<pre>";
          echo print_r($row);
          echo "</pre>";
      }
      //while ($rows = mysqli_fetch_array($results))
      
      } //if (mysqli_num_rows($results))
      
      else {
          echo "No results to get!";
      }
?>

Here is my all_illinois INSERT table structu

CREATE TABLE IF NOT EXISTS `all_illinois` (
  `state_id` varchar(255) NOT NULL,
  `city_name` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is my source table geo_cities structu

CREATE TABLE IF NOT EXISTS `1` (
  `CityId` varchar(255) NOT NULL,
  `CountryID` varchar(255) NOT NULL,
  `RegionID` varchar(255) NOT NULL,
  `City` varchar(255) NOT NULL,
  `Latitude` varchar(255) NOT NULL,
  `Longitude` varchar(255) NOT NULL,
  `TimeZone` varchar(255) NOT NULL,
  `DmaId` varchar(255) NOT NULL,
  `Code` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I don't think I've asked this before have I??  can someone give me an internet KB that gives me all scenarios that warrant using singles or doubles?  as in, wrapping values, variables, and why I need to do either, and when, etc, etc....?

thanks

Edited March 29 by ajetrumpet

I'm just doing a tutorial, learning PHP/mySQL for the first time, and have been having problems getting PHP to talk to mySQL, so to speak.

The tutorial is from he http://www.youtube.com/watch?v=Gei4AYkpAi8&feature=player_detailpage#t=276s

Here is the code:

        <?php
       
$link = mysql_connect('http://192.168.0.8:3307', 'ddd1600', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('test', $link);
if (!$db_selected) {
    die ('Could not connect: ' . mysql_error());
}else{
    echo "It worked!";
}

        $sql =
"INSERT INTO `test`.`Table2` (
`ID` ,
`name` ,
`content`
)
VALUES (
NULL ,  'apples',  'are grown by Australians.');"
       
       ; ?>


My server is on my private network, by the way.

Thanks!

PS:
If it helps, here is the error message from Chrome browser or any other browser:
Warning: mysql_connect() [function.mysql-connect]: php_network_getaddresses: getaddrinfo failed: No such host is known. in C:\xampp\htdocs\PhpProject1\index.php on line 4

Warning: mysql_connect() [function.mysql-connect]: [2002] php_network_getaddresses: getaddrinfo failed: No such host is known. (trying to connect via tcp://http:3306) in C:\xampp\htdocs\PhpProject1\index.php on line 4

Warning: mysql_connect() [function.mysql-connect]: php_network_getaddresses: getaddrinfo failed: No such host is known. in C:\xampp\htdocs\PhpProject1\index.php on line 4
Could not connect: php_network_getaddresses: getaddrinfo failed: No such host is known.

In most cases, yes, so don't bother asking. Instead, asking how is a much better alternative.

What I'm trying to accomplish is your average session login w/ a cookie-based "remember me" feature.

I can log in fine, I can set cookies, I can access those cookies, and I can use that data to log in just as if the user had logged in manually.

The problem is logging out. But the bigger problem is figuring out why.

You see, it works perfect. It logs in and it log out, so long as I am outputting data to the page. I was outputting a session variable and the cookies I set to make sure they were all working right; and they were. But then as soon as I disable those echoes, all of a sudden it won't log out anymore.

So then I turn them on to see what the data says and BAM, I'm logged out. I log back in fine, I log back out fine, so I turn em off again.

I log in fine. I can't log out. I try multiple times. I close my browser and open a new one. Still logged in. I try a few more times, still logged in. I turn the output back on, load the page again and I'm logged out.

So... WTF? (my code to follow)

Can someone please help me with an array problem i can not figure out.

I need the array to be numbered from 1 to how ever many fields that are needed in the form and have a mysql field name and the title of the field also in the array.

1, "mysql_field_name", "Title of form field"
2, "", ""

and so on

then the form will be shown based on the array.

I have the following draft code which I am working with.

any suggestions on how i may do this array ?

Code: [Select]
<?php
$options = array(
'1'=> array('fieldtext'=>'option1', 'mysqlfield'=>'option1'),
'2'=> array('fieldtext'=>'option2', 'mysqlfield'=>'option2'),
'3'=> array('fieldtext'=>'option3', 'mysqlfield'=>'option3'),
'4'=> array('fieldtext'=>'option4', 'mysqlfield'=>'option4'),
);


// $options = array(1 => "option1", "option2", "option3", "option4");

// the line above works but i want to include the name of the mysql field as well.

$userid = 1;

?>
<div style="align: center; margin: 12px; font-family:Tahoma;">
<br><br><?php
if ($_POST['Update'] != "Update") { // check if form submitted yet, if not get data from mysql.
$res = db_query("SELECT * FROM `users` WHERE `userid` = '" . $userid . "'");
foreach($options as $key => $value) { $_POST[$key] = mysql_result($res, 0, $value); }
$ok_to_update = "no";
} elseif ($_POST['Update'] == "Update") { // check if form submitted yet, if so get POST data.
// error checking
// foreach($options as $key => $value) {
// $_POST[$i] = "";
// }
$ok_to_update = "yes";
}

if ($_POST['Update'] == "Update" && $ok_to_update == "yes") {
// $res = db_query("INSERT INTO `users` () VALUES ()"); // add user details to database.
?><p><br><br><br>Thank you for updating</p><?php
} else {
?><form name="form1" method="post" action="">
<?php foreach($options as $key => $value) {
?><p><?php echo($value); ?>:&nbsp;<input type="text" name="<?php echo($key); ?>" value="<?php echo($_POST[$key]);?>"></p>
<?php } ?>
<input name="Update" type="submit" value="Update">
</form>
<?php } ?>
</div>

A problem has arisen which puzzles me. I have forms which save data to MySql and retrieve it, showing it as the default data in the form. Naturally I escape any quotes before sending it to the database and remove the slashes when I retrieve it. But the form HTML code shows the data like this value="$variable" which is fine when only single quotes are used in the data but causes a problem when the user uses double quotes. So data of John \"Jack\" Smith would be output as value="John "Jack" Smith" with obvious problems. If I use value='...' then that would cause problems with single quotes.

I haven't seen the answer in any of my books. The only things I can think of is changing all double quotes to single before saving to DB or converting them with htmlspecialcharacters so they are no longer actual quotes.