[SOLVED] php/mysql error

[DnJ]

Trying to create a registry of sorts. Have a fairly large html form which I am trying to use with php and a mysql data base.

 

Getting an error!

 

Error in query: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')' at line 1

 

New to php so have no idea what this error refers to. Wrote this page by extracting snippets from different topics on php freaks.

 

<?php

// Receiving variables
$Couple =$_POST['Couple'];
$HisFirstName =$_POST['His-First-Name'];
$HerFirstName =$_POST['Her-First-Name'];
$HisLastName =$_POST['His-Last-Name'];
$HerLastName =$_POST['Her-Last-Name'];
$HisCity =$_POST['His-City'];
$HisProvinceState =$_POST['His-Province-State'];
$HisCountry =$_POST['His-Country'];
$HisPostalZip =$_POST['His-Postal-Zip'];
$HerCity = $_POST['Her-City'];
$HerProvinceState =$_POST['Her-Province-State'];
$HerCountry =$_POST['Her-Country'];
$HerPostalZip =$_POST['Her-Postal-Zip'];
$HisMainEmail =$_POST['His-Main-Email'];
$HisYahooEmail =$_POST['His-Yahoo-Email'];
$HisMSNEmail =$_POST['His-MSN-Email'];
$HerMainEmail =$_POST['Her-Main-Email'];
$HerYahooEmail =$_POST['Her-Yahoo-Email'];
$HerMSNEmail =$_POST['Her-MSN-Email'];
$HisYes =$_POST['His-Yes'];
$HisNo =$_POST['His-No'];
$HerYes =$_POST['Her-Yes'];
$HerNo =$_POST['Her-No'];
$HisPreferredContact =$_POST['His-Preferred-Contact'];
$HerPreferredContact =$_POST['Her-Preferred-Contact'];
$SingleFemale =$_POST['Single-Female'];
$SFFirstName =$_POST['SF-First-Name'];
$SFLastName =$_POST['SF-Last-Name'];
$SFCity =$_POST['SF-City'];
$SFProvinceState =$_POST['SF-Province-State'];
$SFCountry =$_POST['SF-Country'];
$SFPostalZip =$_POST['SF-Postal-Zip'];
$SFMainEmail =$_POST['SF-Main-Email'];
$SFYahooEmail =$_POST['SF-Yahoo-Email'];
$SFMSNEmail =$_POST['SF-MSN-Email'];
$SFYes =$_POST['SF-Yes'];
$SFNo =$_POST['SF-No'];
$SFPreferredContact =$_POST['SF-Preferred-Contact'];
$SingleMale =$_POST['Single-Male'];
$SMFirstName =$_POST['SM-First-Name'];
$SMLastName =$_POST['SM-Last-Name'];
$SMCity =$_POST['SM-City'];
$SMProvinceState =$_POST['SM-Province-State'];
$SMCountry =$_POST['SM-Country'];
$SMPostalZip =$_POST['SM-Postal-Zip'];
$SMMainEmail =$_POST['SM-Main-Email'];
$SMYahooEmail =$_POST['SM-Yahoo-Email'];
$SMMSNEmail =$_POST['SM-MSN-Email'];
$SMYes =$_POST['SM-Yes'];
$SMNo =$_POST['SM-No'];
$SMPreferredContact =$_POST['SM-Preferred-Contact'];
$OffPremiseClub =$_POST['Off-Premise-Club'];
$OffPremiseClubName =$_POST['Off-Premise-Club-Name'];
$OffPremiseClubCity =$_POST['Off-Premise-Club-City'];
$OffPremiseClubProvinceState =$_POST['Off-Premise-Club-Province-State'];
$OffPremiseClubCountry =$_POST['Off-Premise-Club-Country'];
$OffPremiseClubPostalZip =$_POST['Off-Premise-Club-Postal-Zip'];
$OffPremiseClubEmail =$_POST['Off-Premise-Club-Email'];
$OffPremiseClubWebsite =$_POST['Off-Premise-Club-Website'];
$OffPremiseClubComment =$_POST['Off-Premise-Club-Comment'];
$OnPremiseClub =$_POST['On-Premise-Club'];
$OnPremiseClubName =$_POST['On-Premise-Club-Name'];
$OnPremiseClubCity =$_POST['On-Premise-Club-City'];
$OnPremiseClubProvinceState =$_POST['On-Premise-Club-Province-State'];
$OnPremiseClubCountry =$_POST['On-Premise-Club-Country'];
$OnPremiseClubPostalZip =$_POST['On-Premise-Club-Postal-Zip'];
$OnPremiseClubEmail =$_POST['On-Premise-Club-Email'];
$OnPremiseClubWebsite =$_POST['On-Premise-Club-Website'];
$OnPremiseClubComment =$_POST['On-Premise-Club-Comment'];
$AdultTravel =$_POST['Adult-Travel'];
$AdultTravelName =$_POST['Adult-Travel-Name'];
$AdultTravelCity =$_POST['Adult-Travel-City'];
$AdultTravelProvinceState =$_POST['Adult-Travel-Province-State'];
$AdultTravelCountry =$_POST['Adult-Travel-Country'];
$AdultTravelPostalZip =$_POST['Adult-Travel-Postal-Zip'];
$AdultTravelEmail =$_POST['Adult-Travel-Email'];
$AdultTravelWebsite =$_POST['Adult-Travel-Website'];
$AdultTravelComment =$_POST['Adult-Travel-Comment'];
$ConventionsEvents =$_POST['Conventions-Events'];
$ConventionEventName =$_POST['Convention-Event-Name'];
$ConventionsEventsRecurringYes =$_POST['Conventions-Events-Recurring-Yes'];
$ConventionsEventsRecurringNo =$_POST['Conventions-Events-Recurring-No'];
$ConventionsEventsCity =$_POST['Conventions-Events-City'];
$ConventionsEventsProvinceState =$_POST['Conventions-Events-Province-State'];
$ConventionsEventsCountry =$_POST['Conventions-Events-Country'];
$ConventionsEventsPostalZip =$_POST['Conventions-Events-Postal-Zip'];
$ConventionsEventsEmail =$_POST['Conventions-Events-Email'];
$ConventionsEventsWebsite =$_POST['Conventions-Events-Website'];
$ConventionsEventsComment =$_POST['Conventions-Events-Comment'];
$PublicationListings =$_POST['Publication-Listings'];
$PublicationListingsName =$_POST['Publication-Listings-Name'];
$PublicationListingsYearly =$_POST['Publication-Listings-Yearly'];
$PublicationListingsSemiAnnual =$_POST['Publication-Listings-Semi-Annual'];
$PublicationListingsBiMonthly =$_POST['Publication-Listings-Bi-Monthly'];
$PublicationListingsMonthly =$_POST['Publication-Listings-Monthly'];
$PublicationListingsBiWeekly =$_POST['Publication-Listings-Bi-Weekly'];
$PublicationListingsWeekly =$_POST['Publication-Listings-Weekly'];
$PublicationListingsCity =$_POST['Publication-Listings-City'];
$PublicationListingsProvinceState =$_POST['Publication-Listings-Province-State'];
$PublicationListingsCountry =$_POST['Publication-Listings-Country'];
$PublicationListingsPostalZip =$_POST['Publication-Listings-Postal-Zip'];
$PublicationListingsEmail =$_POST['Publication-Listings-Email'];
$PublicationListingsWebsite =$_POST['Publication-Listings-Website'];
$PublicationListingsComment =$_POST['Publication-Listings-Comment'];
$Marketplace =$_POST['Marketplace'];
$MarketplaceName =$_POST['Marketplace-Name'];
$MarketplaceCity =$_POST['Marketplace-City'];
$MarketplaceProvinceState =$_POST['Marketplace-Province-State'];
$MarketplaceCountry =$_POST['Marketplace-Country'];
$MarketplacePostalZip =$_POST['Marketplace-Postal-Zip'];
$MarketplaceEmail =$_POST['Marketplace-Email'];
$MarketplaceWebsite =$_POST['Marketplace-Website'];
$MarketplaceComment =$_POST['Marketplace-Comment'];
$InternetServices =$_POST['Internet-Services'];
$InternetServicesName =$_POST['Internet-Services-Name'];
$InternetServicesCity =$_POST['Internet-Services-City'];
$InternetServicesProvinceState =$_POST['Internet-Services-Province-State'];
$InternetServicesCountry =$_POST['Internet-Services-Country'];
$InternetServicesPostalZip =$_POST['Internet-Services-Postal-Zip'];
$InternetServicesEmail =$_POST['Internet-Services-Email'];
$InternetServicesWebsite =$_POST['Internet-Services-Website'];
$InternetServicesComment =$_POST['Internet-Services-Comment'];

////////////////////////////////////////  CONNECT TO MYSQL DB  ////////////////////
// OPEN CONNECTION --->
$connection=mysql_connect("localhost","xxx", "xxx") or die("Unable to connect!");

mysql_select_db("xxx") or die("Unable to select database!");

//  EXECUTE QUERY --->
$query="INSERT INTO swingerregistry (
                                     Couple,
                                     HisFirstName,
                                     HerFirstName,
                                     HisLastName,
                                     HerLastName,
                                     HisCity,
                                     HisProvinceState,
                                     HisCountry,
                                     HisPostalZip,
                                     HerCity,
                                     HerProvinceState,
                                     HerCountry,
                                     HerPostalZip,
                                     HisMainEmail,
                                     HisYahooEmail,
                                     HisMSNEmail,
                                     HerMainEmail,
                                     HerYahooEmail,
                                     HerMSNEmail,
                                     HisYes,
                                     HisNo,
                                     HerYes,
                                     HerNo,
                                     HisPreferredContact,
                                     HerPreferredContact,
                                     SingleFemale,
                                     SFFirstName,
                                     SFLastName,
                                     SFCity,
                                     SFProvinceState,
                                     SFCountry,
                                     SFPostalZip,
                                     SFMainEmail,
                                     SFYahooEmail,
                                     SFMSNEmail,
                                     SFYes,
                                     SFNo,
                                     SFPreferredContact,
                                     SingleMale,
                                     SMFirstName,
                                     SMLastName,
                                     SMCity,
                                     SMProvinceState,
                                     SMCountry,
                                     SMPostalZip,
                                     SMMainEmail,
                                     SMYahooEmail,
                                     SMMSNEmail,
                                     SMYes,
                                     SMNo,
                                     SMPreferredContact,
                                     OffPremiseClub,
                                     OffPremiseClubName,
                                     OffPremiseClubCity,
                                     OffPremiseClubProvinceState,
                                     OffPremiseClubCountry,
                                     OffPremiseClubPostalZip,
                                     OffPremiseClubEmail,
                                     OffPremiseClubWebsite,
                                     OffPremiseClubComment,
                                     OnPremiseClub,
                                     OnPremiseClubName,
                                     OnPremiseClubCity,
                                     OnPremiseClubProvinceState,
                                     OnPremiseClubCountry,
                                     OnPremiseClubPostalZip,
                                     OnPremiseClubEmail,
                                     OnPremiseClubWebsite,
                                     OnPremiseClubComment,
                                     AdultTravel,
                                     AdultTravelName,
                                     AdultTravelCity,
                                     AdultTravelProvinceState,
                                     AdultTravelCountry,
                                     AdultTravelPostalZip,
                                     AdultTravelEmail,
                                     AdultTravelWebsite,
                                     AdultTravelComment,
                                     ConventionsEvents,
                                     ConventionEventName,
                                     ConventionsEventsRecurringYes,
                                     ConventionsEventsRecurringNo,
                                     ConventionsEventsCity,
                                     ConventionsEventsProvinceState,
                                     ConventionsEventsCountry,
                                     ConventionsEventsPostalZip,
                                     ConventionsEventsEmail,
                                     ConventionsEventsWebsite,
                                     ConventionsEventsComment,
                                     PublicationListings,
                                     PublicationListingsName,
                                     PublicationListingsYearly,
                                     PublicationListingsSemiAnnual,
                                     PublicationListingsBiMonthly,
                                     PublicationListingsMonthly,
                                     PublicationListingsBiWeekly,
                                     PublicationListingsWeekly,
                                     PublicationListingsCity,
                                     PublicationListingsProvinceState,
                                     PublicationListingsCountry,
                                     PublicationListingsPostalZip,
                                     PublicationListingsEmail,
                                     PublicationListingsWebsite,
                                     PublicationListingsComment,
                                     Marketplace,
                                     MarketplaceName,
                                     MarketplaceCity,
                                     MarketplaceProvinceState,
                                     MarketplaceCountry,
                                     MarketplacePostalZip,
                                     MarketplaceEmail,
                                     MarketplaceWebsite,
                                     MarketplaceComment,
                                     InternetServices,
                                     InternetServicesName,
                                     InternetServicesCity,
                                     InternetServicesProvinceState,
                                     InternetServicesCountry,
                                     InternetServicesPostalZip,
                                     InternetServicesEmail,
                                     InternetServicesWebsite,
                                     InternetServicesComment)
        VALUES(
               '".$Couple."',
               '".$His-First-Name."',
               '".$Her-First-Name."',
               '".$His-Last-Name."',
               '".$Her-Last-Name."',
               '".$His-City."',
               '".$His-Province-State."',
               '".$His-Country."',
               '".$His-Postal-Zip."',
               '".$Her-City."',
               '".$Her-Province-State."',
               '".$Her-Country."',
               '".$Her-Postal-Zip."',
               '".$His-Main-Email."',
               '".$His-Yahoo-Email."',
               '".$His-MSN-Email."',
               '".$Her-Main-Email."',
               '".$Her-Yahoo-Email."',
               '".$Her-MSN-Email."',
               '".$His-Yes."',
               '".$His-No."',
               '".$Her-Yes."',
               '".$Her-No."',
               '".$His-Preferred-Contact."',
               '".$Her-Preferred-Contact."',
               '".$Single-Female."',
               '".$SF-First-Name."',
               '".$SF-Last-Name."',
               '".$SF-City."',
               '".$SF-Province-State."',
               '".$SF-Country."',
               '".$SF-Postal-Zip."',
               '".$SF-Main-Email."',
               '".$SF-Yahoo-Email."',
               '".$SF-MSN-Email."',
               '".$SF-Yes."',
               '".$SF-No."',
               '".$SF-Preferred-Contact."',
               '".$Single-Male."',
               '".$SM-First-Name."',
               '".$SM-Last-Name."',
               '".$SM-City."',
               '".$SM-Province-State."',
               '".$SM-Country."',
               '".$SM-Postal-Zip."',
               '".$SM-Main-Email."',
               '".$SM-Yahoo-Email."',
               '".$SM-MSN-Email."',
               '".$SM-Yes."',
               '".$SM-No."',
               '".$SM-Preferred-Contact."',
               '".$Off-Premise-Club."',
               '".$Off-Premise-Club-Name."',
               '".$Off-Premise-Club-City."',
               '".$Off-Premise-Club-Province-State."',
               '".$Off-Premise-Club-Country."',
               '".$Off-Premise-Club-Postal-Zip."',
               '".$Off-Premise-Club-Email."',
               '".$Off-Premise-Club-Website."',
               '".$Off-Premise-Club-Comment."',
               '".$On-Premise-Club."',
               '".$On-Premise-Club-Name."',
               '".$On-Premise-Club-City."',
               '".$On-Premise-Club-Province-State."',
               '".$On-Premise-Club-Country."',
               '".$On-Premise-Club-Postal-Zip."',
               '".$On-Premise-Club-Email."',
               '".$On-Premise-Club-Website."',
               '".$On-Premise-Club-Comment."',
               '".$Adult-Travel."',
               '".$Adult-Travel-Name."',
               '".$Adult-Travel-City."',
               '".$Adult-Travel-Province-State."',
               '".$Adult-Travel-Country."',
               '".$Adult-Travel-Postal-Zip."',
               '".$Adult-Travel-Email."',
               '".$Adult-Travel-Website."',
               '".$Adult-Travel-Comment."',
               '".$Conventions-Events."',
               '".$Convention-Event-Name."',
               '".$Conventions-Events-Recurring-Yes."',
               '".$Conventions-Events-Recurring-No."',
               '".$Conventions-Events-City."',
               '".$Conventions-Events-Province-State."',
               '".$Conventions-Events-Country."',
               '".$Conventions-Events-Postal-Zip."',
               '".$Conventions-Events-Email."',
               '".$Conventions-Events-Website."',
               '".$Conventions-Events-Comment."',
               '".$Publication-Listings."',
               '".$Publication-Listings-Name."',
               '".$Publication-Listings-Yearly."',
               '".$Publication-Listings-Semi-Annual."',
               '".$Publication-Listings-Bi-Monthly."',
               '".$Publication-Listings-Monthly."',
               '".$Publication-Listings-Bi-Weekly."',
               '".$Publication-Listings-Weekly."',
               '".$Publication-Listings-City."',
               '".$Publication-Listings-Province-State."',
               '".$Publication-Listings-Country."',
               '".$Publication-Listings-Postal-Zip."',
               '".$Publication-Listings-Email."',
               '".$Publication-Listings-Website."',
               '".$Publication-Listings-Comment."',
               '".$Marketplace."',
               '".$Marketplace-Name."',
               '".$Marketplace-City."',
               '".$Marketplace-Province-State."',
               '".$Marketplace-Country."',
               '".$Marketplace-Postal-Zip."',
               '".$Marketplace-Email."',
               '".$Marketplace-Website."',
               '".$Marketplace-Comment."',
               '".$Internet-Services."',
               '".$Internet-Services-Name."',
               '".$Internet-Services-City."',
               '".$Internet-Services-Province-State."',
               '".$Internet-Services-Country."',
               '".$Internet-Services-Postal-Zip."',
               '".$Internet-Services-Email."',
               '".$Internet-Services-Website."',
               '".$Internet-Services-Comment."')";
//////----->
$result=mysql_query($query) or die("Error in query:".mysql_error());
//if ($result)
    //echo mysql_affected_rows()." row inserted into the database effectively.";

//  CLOSE CONNECTION --->
mysql_close($connection);

?>

[Jessica]

Holy Batman, that is an insane table.

Change to:

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

So you can make sure the values are as you expect.

[trq]

Change this and show us the output.

 

$result=mysql_query($query) or die("Error in query:".mysql_error()."<br />$query");

 

PS: You really ought to clean those $_POST variables before trying to use them, its very insecure to let your users post straight into the database. Take a look at mysql_real_escape_string.

[DnJ]

changed to:

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

 

Results

 

Query: 0'), 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 '0')' at line 1

[AndyB]

You can't use the dash/hyphen (-) in php variable names, it's interpreted as a negation sign.  You have correctly abstracted your POSTed variables without the dashes .... but you left all the wrong variable names in your mysql query string

 

Change the variable names in the VALUES section of your query to match the values abstracted from the $_POST array, i.e. remove all the minus signs.

[ataria]

I have never seen a bigger table in my life!

Dear lord hope you have you a great host who can handle all taht.

[Jessica]

Good catch Andy!

 

A table that big is REALLY a bad idea.

[DnJ]

Andy thanks will fix the table and try it again.

 

And do so more reading and try an downsize the table.

 

Also read about - real_escape_string

 

From what I can see an escape string makes use of a user/password from a user table wasn't requiring a log-in was open registration for anyone. Bad idea I am guessing

 

<?php
// Quote variable to make safe
function quote_smart($value)
{
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not a number or a numeric string
   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
   OR die(mysql_error());

// Make a safe query
$query = sprintf("SELECT * FROM users WHERE user=%s AND password=%s",
           quote_smart($_POST['username']),
           quote_smart($_POST['password']));

mysql_query($query);
?> 

[Jessica]

mysql_real_escape_string helps prevent SQL injection, it has nothing to do with users. What you have is an example of how it would be used. It should be used on any user input.

[DnJ]

Removed all the '-' and no more errrors. Tried submitting a trial from the form and 1 record shows up.

 

Thanks for the help.

 

Now onto reading and figuring out how to compact the table.