Solved

SQL is not retrieving the form data on certain fields.

Posted on 2007-03-22
29
198 Views
Last Modified: 2007-03-23
Not sure why, but my sql statement is not retrieving what my form is sending out.  I have triple checked to make sure the variables are correct.  I'm not sure if it means anything, but the two that are not being retrieved are longtext strings in phpMyAdmin.

                   $strProviderservice = $_REQUEST['strProviderservice'];
            $strCompanyname = $_REQUEST['strCompanyname'];
            $strOwner = $_REQUEST['strOwner'];
            $strAddress = $_REQUEST['strAddress'];
            $strTown = $_REQUEST['strTown'];
            $strZipcode = $_REQUEST['strZipcode'];
            $strPhone = $_REQUEST['strPhone'];
            $str2ndphone = $_REQUEST['str2ndphone'];
            $strMobile = $_REQUEST['strMobile'];
            $strPager = $_REQUEST['strPager'];
            $strFax = $_REQUEST['strFax'];
            $strEmail = $_REQUEST['strEmail'];
            $strWebsite = $_REQUEST['strWebsite'];
            $strInbusiness_since = $_REQUEST['strInbusiness_since'];
            $strLicense = $_REQUEST['strLicense'];
            $strInsured = $_REQUEST['strInsured'];
            $strBonded = $_REQUEST['strBonded'];
            $strHours = $_REQUEST['strHours'];
            $str24houremerg = $_REQUEST['str24houremerg'];
            $strServicesoffered = $_REQUEST['strServicesoffered'];
            $strOtherservices = $_REQUEST['strOtherservices'];
            $strServicearea = $_REQUEST['strServicearea'];
            $strFreeestimate = $_REQUEST['strFreeestimate'];
            $strWorkguaranteed = $_REQUEST['strWorkguaranteed'];
            $strProvidertagline = $_REQUEST['strProvidertagline'];
            $strAd_size = $_REQUEST['strAd_size'];

$SQL = "INSERT INTO tblAdspace VALUES"
     . "("
     . "strProviderservice, strCompanyname, strOwner, strAddress, strTown, "
     . "strZipcode, strPhone, str2ndphone, "
     . "strMobile, strPager, strFax, "
     . "strEmail, strWebsite, strLicense, "
     . "strBonded, strHours, str24houremerg, strServicesoffered, "
     . "strOtherservices, strServicearea, strFreeestimate, strWorkguaranteed, strProvidertagline, strAd_size"
     . ") "
     . "VALUES("
     . "'" . $strProviderservice . "',"
     . "'" . $strCompanyname . "',"
     . "'" . $strOwner . "',"
     . "'" . $strAddress . "',"
     . "'" . $strTown . "',"
     . "'" . $strZipcode . "',"
     . "'" . $strPhone . "',"
     . "'" . $str2ndphone . "',"
     . "'" . $strMobile . "',"
     . "'" . $strPager . "',"
     . "'" . $strFax . "',"
     . "'" . $strEmail . "',"
     . "'" . $strWebsite . "',"
     . "'" . $strLicense . "',"
     . "'" . $strBonded . "',"
     . "'" . $strHours . "',"
     . "'" . $str24houremerg . "',"
     . "'" . str_replace("'", "''", $strOtherservices) . "',"
       . "'" . str_replace("'", "''", $strServicesoffered) . "', "
     . "'" . $strServicearea . "',"
     . "'" . $strFreeestimate . "',"
     . "'" . $strWorkguaranteed . "',"
     . "'" . str_replace("'", "''", $strProvidertagline) . "',"
     . "'" . $strAd_size . "'"
     . ") ";
      
       echo $SQL;
       die;
0
Comment
Question by:pingeyeg
  • 15
  • 12
  • 2
29 Comments
 
LVL 1

Author Comment

by:pingeyeg
ID: 18775699
This is kind of bits and pieces of the whole page.  Didn't think you would need the entire page.
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 18776269
Have you tried changing the attribute type to VARCHAR() just to see if it is the data type that is causing an issue ?

Just out of curiosity, why are you concatinating all your attribute values into your DB ?
0
 
LVL 5

Expert Comment

by:Oscurochu
ID: 18776606
try doing this:
<?
print "<pre>";
print $SQL;
print "</pre>";
?>

at the end of your page and see if the variables are being passed correctly.

if they aren't, try this:

before assigning variables that use the $_REQUEST variable, assign the $_REQUEST variable yourself like this:

$_REQUEST = ( isset($_REQEST) && !empty($_REQUEST) ) ? $_REQUEST : array_merge($_GET, $_POST);
0
 
LVL 5

Expert Comment

by:Oscurochu
ID: 18776638
with further studying of your code, i've noticed something else. your sql is formatted incorrectly, and the syntax is wrong (line 1, char 24-29). this should be correct now:

$SQL = "INSERT INTO tblAdspace
     . "("
     . "strProviderservice, strCompanyname, strOwner, strAddress, strTown, "
     . "strZipcode, strPhone, str2ndphone, "
     . "strMobile, strPager, strFax, "
     . "strEmail, strWebsite, strLicense, "
     . "strBonded, strHours, str24houremerg, strServicesoffered, "
     . "strOtherservices, strServicearea, strFreeestimate, strWorkguaranteed, strProvidertagline, strAd_size"
     . ") "
     . "VALUES("
     . "'" . $strProviderservice . "',"
     . "'" . $strCompanyname . "',"
     . "'" . $strOwner . "',"
     . "'" . $strAddress . "',"
     . "'" . $strTown . "',"
     . "'" . $strZipcode . "',"
     . "'" . $strPhone . "',"
     . "'" . $str2ndphone . "',"
     . "'" . $strMobile . "',"
     . "'" . $strPager . "',"
     . "'" . $strFax . "',"
     . "'" . $strEmail . "',"
     . "'" . $strWebsite . "',"
     . "'" . $strLicense . "',"
     . "'" . $strBonded . "',"
     . "'" . $strHours . "',"
     . "'" . $str24houremerg . "',"
     . "'" . str_replace("'", "''", $strOtherservices) . "',"
       . "'" . str_replace("'", "''", $strServicesoffered) . "', "
     . "'" . $strServicearea . "',"
     . "'" . $strFreeestimate . "',"
     . "'" . $strWorkguaranteed . "',"
     . "'" . str_replace("'", "''", $strProvidertagline) . "',"
     . "'" . $strAd_size . "'"
     . ") ";
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18779259
Oscurochu, with your code I am getting a parse error:  Line  . "("
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18779289
Ok, Oscurochu, I found what was missing in your code, but it still isn't working.  I don't think the problem lies in the sql statement, I think the problem is in the $_REQUEST portion of the page, because most of the values are being brought in, only two aren't.  The two that aren't are formatted in phpMyAdmin as Longtext.
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18779292
Tchuki, what do you mean by using the VARCHAR() ?
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 18779320
Why are you concatinating your vars into the DB ?

What are the column attribute names in your tblAdspace ?
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18779340
I was told it would be easier to read the code towards troubleshooting.
0
 
LVL 14

Assisted Solution

by:Tchuki
Tchuki earned 500 total points
ID: 18779438
In your DB, change the data type of the longtext attributes to VARCHAR()


<?PHP

$strProviderservice = $_POST['strProviderservice'];
$strCompanyname = $_POST['strCompanyname'];
$strOwner = $_POST['strOwner'];
$strAddress = $_POST['strAddress'];
$strTown = $_POST['strTown'];
$strZipcode = $_POST['strZipcode'];
$strPhone = $_POST['strPhone'];
$str2ndphone = $_POST['str2ndphone'];
$strMobile = $_POST['strMobile'];
$strPager = $_POST['strPager'];
$strFax = $_POST['strFax'];
$strEmail = $_POST['strEmail'];
$strWebsite = $_POST['strWebsite'];
$strInbusiness_since = $_POST['strInbusiness_since'];
$strLicense = $_POST['strLicense'];
$strInsured = $_POST['strInsured'];
$strBonded = $_POST['strBonded'];
$strHours = $_POST['strHours'];
$str24houremerg = $_POST['str24houremerg'];
$strServicesoffered = $_POST['strServicesoffered'];
$strOtherservices = $_POST['strOtherservices'];
$strServicearea = $_POST['strServicearea'];
$strFreeestimate = $_POST['strFreeestimate'];
$strWorkguaranteed = $_POST['strWorkguaranteed'];
$strProvidertagline = $_POST['strProvidertagline'];
$strAd_size = $_POST['strAd_size'];

$srtOtherservices = str_replace("'", "''", $strOtherservices);
$strServicesoffered = str_replace("'", "''", $strServicesoffered);
$strProvidertagline = str_replace("'", "''", $strProvidertagline);


$SQL = ("INSERT INTO tblAdspace
(strProviderservice, strCompanyname, strOwner, strAddress, strTown, strZipcode, strPhone, str2ndphone, strMobile, strPager, strFax, strEmail, strWebsite, strLicense, strBonded, strHours, str24houremerg, strServicesoffered, strOtherservices, strServicearea, strFreeestimate, strWorkguaranteed, strProvidertagline, strAd_size)
VALUES
('$strProviderservice', '$strCompanyname', '$strOwner', '$strAddress', '$strTown', '$strZipcode', '$strPhone', '$str2ndphone', '$strMobile', '$strPager', '$strFax, $strEmail', '$strWebsite', '$strLicense', '$strBonded', '$strHours', '$str24houremerg', '$strServicesoffered', '$strOtherservices', '$strServicearea', '$strFreeestimate', '$strWorkguaranteed', '$strProvidertagline', '$strAd_size')")
or die ("Could not insert values into DB tblAdspace: " . mysql_error());

        
?>
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18779506
I tried making an update with field that is a regular text value and it works, but the two that are longtext aren't working.
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 18779533
Have you tried changing the fields to VARCHAR and seeing if any information is inserted ?!
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18779550
Can you show me how to do that?  I don't know what VARCHAR is.
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 18779704
Go into phpMyAdmin, select your table and change the field type in there.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:pingeyeg
ID: 18779713
Ok, just out of curiosity, what would you change it to if the field needs to have 300 characters in it?
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 18779924
longtext should be the one you go for.

That said, try what I have suggested and see if the information is inserted.

We need to assertain if it is your PHP $_POST vars, SQL statement or DB itself that is having the issue.
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18779952
RIght now the field type is already longtext for those two fields.
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 18779999
Yes, try changing it to VARCHAR and see if any information is collected that way ...
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18780018
That didn't do the trick.
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 18780050
OK, which are the two fields that are not being inserted, what are their names ?
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18780089
strServicesoffered and strOtherservices
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 18780175
Does strProvidertagline insert correctly ?
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18781000
Apparently it does not.  Huh.
0
 
LVL 14

Accepted Solution

by:
Tchuki earned 500 total points
ID: 18781047
OK, think I see the issue now, its your str_replace function.

##--[ FIND ]--##

str_replace("'", "''", $strOtherservices);
str_replace("'", "''", $strServicesoffered);
str_replace("'", "''", $strProvidertagline);

##--[ REPLACE WITH ]--##

addslashes($strOtherservices);
addslashes($strServicesoffered);
addslashes($strProvidertagline);

Then try the script again.
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18781161
Still nothing.  What was that supposed to do anyway?
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18781175
Isn't that funtion supposed to work like that?  When people add text to these boxes they will most certainly be using apostrophes on certain word.  How will he addslashes function change that?
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 18781261
Nevermind, that did work.  I saw one other thing that I had extra that was conflicting with your statement.  It now works.  I don't know why, but now all of a sudden I can't change anything in phpMyAdmin without it kicking me out again.
0
 
LVL 14

Assisted Solution

by:Tchuki
Tchuki earned 500 total points
ID: 18781350
Symbols and special characters such as ' @ " $ need to be escaped before being inserted into a database.  The addslashes() function basicaly escapes the characters so that they can be read correctly by apps such as PHP and MySQL by placing a \ in front of them.


For example:

$str = "This strings' been escaped";

$strEscaped = addslashes($str);

echo $str; // output: This string\'s been escaped


When you retreive the data from the DB you will need to remove the slashes using stripslashes()

$sql = ("SELECT * FROM tblAdspace");

$output = stripslashes($sql);

echo $output;


Both addslashes() and stripslashes() were designed to handle this sort of situation and in general, when ever you are inserting into a DB, you should always implement these functions.
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 18781374
Woops

echo $str; // output: This string\'s been escaped

Should be:

echo $str; // output: This strings\' been escaped


Also with regards to you being kicked from phpMyAdmin, you probably need to refresh your cookie or something.  Try logging out, closing the browser completely and then going back to phpMyAdmin.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 35
ajax call to redirect to a different page with data from controller 7 48
<? versus <?php 5 37
Is this error or Notice in php error log? 6 32
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now