single and double quotes in php & sql

[thminco]

I'm trying to take this input string from an html form....  5' 10"

 

1) Put it into an sql table (this part is working)

 

2) Take it out of first sql table using a php script and put it into a different sql table (this part is not working)

 

Here is what I have code wise...

 

The html form creates the variable "height" and assigns an integer for the chosen height:

<select name="height" id="height2" size="1">

      <option value="70">5' 10"</option>

 

The form processing script takes the variable integer and creates a variable for display as 5' 10"

$height = $_POST['height'];

IF ($height == "70") {$displayheight = "5\' 10\"";}

 

The same script  then "inserts" string into first table as 5' 10"

 

When the second script tries to use the string, I get hung up because of the single and double quotes in the string.  I'm using this in a while loop to extract the string from an array...

 

$height=mysql_result($result,$i,"Height");

 

Do I need to "escape" the variable at this point?  Or do I have to store it in the first table with backslashes escaping both the single and double quotes?  OR?

 

As always, any help is greatly appreciated!!

 

 

 

[Psycho]

You should be using the Database specific escaping function instead of creating your own escaping. In this case mysql_real_escape_string(). Then use that on all data used in queries that could contain problematic data. Here is a better solution for the first process

 

$height = (int)$_POST['height'];
$feet = floor($height / 12);
$inch = $height % 12;
$heightSQL = mysql_real_escape_string("{$feet}' {$inch}\"");
//Note: still need to escape the double quote because
//the string is defined in double quotes

 

Now, as for putting the same value into another table, if you want to extract the value first and then insert it again you would want to use mysql_real_escape_string() again. But, that's not necessary. There shouldn't be a need to insert the same value into different tables if the table structure is set up appropriately. And, if you really need to insert the value again, just do it directly in a query instead of extracting the value into PHP.

Note that the double quote still needed to be escaped because it is defined inside a double-quote. Bu

[thminco]

I am trying to take this string... 5' 10"

 

And insert it into an sql table as this...  5\' 10\"

 

when I create the following variable, it gets inserted into the sgl table without the backslashes.

$displayheight = "5\' 10\"";

 

I tried to figure this out using the php manual (It says use a double backslash for a "literal" backslash...whatever that is?)

[Christian F.]

Why would you store the backslashes in the database?

[thminco]

It's a long story...basically I'm a rookie and this is the easiest solution I can come up with

[thminco]

OK..here is the part of the code I have where I am trying to take a string from an sql table that contains the single and double quote and put it into another table.  How do I escape this variable ($displayheight) so it will work?

 

$query="SELECT * FROM ttmmodeltemp

WHERE `processed` = 'No'

AND `verified` = 'Yes'";

 

$result=mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query);

 

$num=mysql_num_rows($result);

 

$i=0;

 

while ($i < $num) {

 

$displayheight=mysql_result($result,$i,"Display height");

 

$sql="INSERT INTO ttmmodels (`Display height`)

VALUES ('$displayheight')";

 

$i++;

 

}

 

[jazzman1]

I've never tried this, but.... it could be something like this:

$query = sprintf("INSERT INTO `tbl_name` SET `name` = '%s'",
mysql_real_escape_string('5\\'.'\''.'10\\'.'"'));
$result = mysql_query($query);

[PFMaBiSmAd]

If your data is escaped properly (only once), the \ characters are NOT inserted into the database table. Only the literal data will be in the database table.

[thminco]

HHHHMMM that looks very similar to what I tried...I created the variable like this

 

$displayheight = "5\\"."\'"." 10\\"."\"";

 

The insert just puts it in as 5' 10"

[thminco]

PFM  I am trying to get the backslashes into the table!

[jazzman1]

HHHHMMM that looks very similar to what I tried...I created the variable like this

 

$displayheight = "5\\"."\'"." 10\\"."\"";

 

The insert just puts it in as 5' 10"

The result of my query above is :

5\'10\"

 

To get the same result your variable could be:

$displayheight = '5\\'.'\''.'10\\'.'"'; 

[PFMaBiSmAd]

PFM  I am trying to get the backslashes into the table!

 

Why do you think you want the \ characters in the actual database table? That requires more storage and if you ever want to search for a value, you must take into account the extra \ characters in the data. You will also need to strip the extra \ characters if you ever display the values in a browser or if you need to perform any math operation on the values.

[thminco]

OK thanks...I will play with it some more, but what is the '%s' doing in the query?

[thminco]

PFM if I manually put in the backslashes in the `ttmmodeltemp` table, then my code seamlessly moves 5\' 10\" into the permanent table and stores it correctly as 5' 10", ready to display later.  Thus, I am trying to eliminate this manual correction and have the temp table string stored as 5\' 10\".  I'm sure there is an easier way, but it's beyond me.

[thminco]

Here is what i have moving the string from temp table to permanent table...

 

$query="SELECT * FROM ttmmodeltemp

WHERE `processed` = 'No'

AND `verified` = 'Yes'";

 

$result=mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query);

 

$num=mysql_num_rows($result);

 

$i=0;

 

while ($i < $num) {

 

$displayheight=mysql_result($result,$i,"Display height");

 

$sql="INSERT INTO ttmmodels (`Display height`)

VALUES ('$displayheight')";

 

$i++;

 

}

 

[Barand]

WHy don't you just

 

$query = "INSERT INTO ttmmodels (`Display height`)
SELECT `Display height` FROM ttmmodeltemp
WHERE `processed` = 'No'
AND `verified` = 'Yes'";

[Barand]

Why don't just

 

$query = "INSERT INTO ttmmodels (`Display height`)
SELECT `Display height` FROM ttmmodeltemp
WHERE `processed` = 'No'
AND `verified` = 'Yes'";

 

then you can close this post and stop double posting the same problem and wasting our time,

 

[Christian F.]

If I were you I'd do away with the temp table, remove all unneeded complexity, and just make things as simple as possible. You're just making things overly complex, as far as I can see it, and thus creating a major headache for yourself.

 

To enable transactional behaviour in a script, if you have multiple SQL queries and would like to roll everything back if one of the steps fail, then you should be looking at just that: Transactions.

On the other hand, if you're saving temporary data in the database to tidy you over different page views, then you should be looking at sessions in PHP to save that data.

Lastly, if you want to have a "save before publish" feature, then add a flag (column) to the table that tells whether or not the data is to be considered "finalized".

 

PS: You're not moving the data in the script above, you're copying it. Having data duplicated in a database over two (or more) tables is a sure sign of something not being as it should be.

[hakimserwa]

why dont you do it the easy way all these people are warning you of the same thing but you still insist. save  the dat in diferent table and call it out then backslash it in you script other than doing it wrong. we are here to learn and expand our understanding. use it with respect

[hakimserwa]

take a look at the implode() function it might help you.