Works in PhpMyadmin, but not when executed through php

[soapdude]

Hello, I am having a terrible problem with this php/mysql script. When I echo the query and insert it via PHPMyAdmin, it works:

INSERT INTO users VALUES ('testaccount', 'b732f2ac717306feb520f5e7c7ef544b', '0', '0', 'Howard', 'Krause', '[e-mail]', '555-555-5555', 'Owner', 'test', 1294501405); INSERT INTO companies (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Address`, `City`, `State`, `Country`, `Zip`, `Phone`, `Description`, `Overview`, `Keywords`, `Email`, `Hire`, `Volunteer`, `Invest`, `Live`) VALUES ('Test Company','www.testcompany.com','facebook.com/test','http://','Test Addy','Campbell','OH','usa', '44405','330-555-5555','Testing it out','Testing it Out','test, whitesnake, rocks','[e-mail]','1','','','1');

However when I execute it through PHP (here's the code I'm using):

 $q = "INSERT INTO ".TBL_USERS." VALUES ('$username', '$password', '0', '0', '$fname', '$lname', '$email', '$phone', '$position', '$howiheard', $time); INSERT INTO companies (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Address`, `City`, `State`, `Country`, `Zip`, `Phone`, `Description`, `Overview`, `Keywords`, `Email`, `Hire`, `Volunteer`, `Invest`, `Live`) VALUES ('$company_name','$web','$facebook','$twitter','$company_address','$company_city','$company_state','$company_country',
'$company_zip','$company_phone','$description','$overview','$keywords','$company_email','$hire','$volunteer','$invest','1');";
 
 echo $q . '<br /><br />';
 if (!mysql_query($q, $this->connection))
 {
 die('Error: ' . mysql_error());
 }

It fails with syntax error:

INSERT INTO users VALUES ('testaccount', 'b732f2ac717306feb520f5e7c7ef544b', '0', '0', 'Howard', 'Krause', '[e-mail]', '555-555-5555', 'Owner', 'test', 1294501405); INSERT INTO companies (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Address`, `City`, `State`, `Country`, `Zip`, `Phone`, `Description`, `Overview`, `Keywords`, `Email`, `Hire`, `Volunteer`, `Invest`, `Live`) VALUES ('Test Company','www.testcompany.com','facebook.com/test','http://','Test Addy','Campbell','OH','usa', '44405','330-555-5555','Testing it out','Testing it Out','test, whitesnake, rocks','[e-mail]','1','','','1');

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 'INSERT INTO companies (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Ad' at line 1

Any ideas? Thanks!

[Matthew1980]

hi there soapdude,

Welcome to WebmasterWorld!

Firstly you need to debug the string to screen that your building, so put this and run it to see if the query is being built as your expecting, then copy that from the browser into mysqladmin and then you can work out the kinks:-

echo $q = "INSERT INTO ".TBL_USERS." VALUES ('$username', '$password', '0', '0', '$fname', '$lname', '$email', '$phone', '$position', '$howiheard', $time); INSERT INTO companies (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Address`, `City`, `State`, `Country`, `Zip`, `Phone`, `Description`, `Overview`, `Keywords`, `Email`, `Hire`, `Volunteer`, `Invest`, `Live`) VALUES ('$company_name','$web','$facebook','$twitter','$company_address','$company_city','$company_state','$company_country',
'$company_zip','$company_phone','$description','$overview','$keywords','$company_email','$hire','$volunteer','$invest','1');";

Also there are e couple of things in the query that needn't be there, ie, the ; within the string, php will include this when it executes the query..

Try this amended version of your to see if it functions:-

$q = "INSERT INTO `".TBL_USERS."` VALUES ('".$username."', '".$password."', 0, 0, '".$fname."', '".$lname."', '".$email."', '".$phone."', '".$position."', '".$howiheard."', '".$time."') INSERT INTO `companies` (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Address`, `City`, `State`, `Country`, `Zip`, `Phone`, `Description`, `Overview`, `Keywords`, `Email`, `Hire`, `Volunteer`, `Invest`, `Live`) VALUES ('".$company_name."','".$web."','".$facebook."','".$twitter."','".$company_address."','".$company_city."','".$company_state."','".$company_country."',
'".$company_zip."','".$company_phone."','".$description."','".$overview."','".$keywords."','".$company_email."','".$hire."','".$volunteer."','".$invest."',1)";

Try echoing out either of those, then exit; directly after so that you can JUST see the string.

Hope that helps,

Cheers,
MRb

[soapdude]

Thanks for the quick reply! When I run:

echo $q = "INSERT INTO ".TBL_USERS." VALUES ('$username', '$password', '0', '0', '$fname', '$lname', '$email', '$phone', '$position', '$howiheard', $time); INSERT INTO companies (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Address`, `City`, `State`, `Country`, `Zip`, `Phone`, `Description`, `Overview`, `Keywords`, `Email`, `Hire`, `Volunteer`, `Invest`, `Live`) VALUES ('$company_name','$web','$facebook','$twitter','$company_address','$company_city','$company_state','$company_country', 
'$company_zip','$company_phone','$description','$overview','$keywords','$company_email','$hire','$volunteer','$invest','1');"; 

I receive:

INSERT INTO users VALUES ('testaccount', 'b732f2ac717306feb520f5e7c7ef544b', '0', '0', 'Howard', 'Krause', '[e-mail]', '555-555-5555', 'Owner', 'test', 1294503043); INSERT INTO companies (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Address`, `City`, `State`, `Country`, `Zip`, `Phone`, `Description`, `Overview`, `Keywords`, `Email`, `Hire`, `Volunteer`, `Invest`, `Live`) VALUES ('Test Company','www.testcompany.com','facebook.com/test','http://','Test Addy','Campbell','OH','usa', '44405','330-555-5555','Testing it out','Testing it Out','test, whitesnake, rocks','[e-mail]','1','','','1');

I then post this into MySQLadmin and it passes with flying colors.. Everything is inserted. Now, when I return to the code and execute the script as you rewrote it:

$q = "INSERT INTO `".TBL_USERS."` VALUES ('".$username."', '".$password."', 0, 0, '".$fname."', '".$lname."', '".$email."', '".$phone."', '".$position."', '".$howiheard."', '".$time."') INSERT INTO `companies` (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Address`, `City`, `State`, `Country`, `Zip`, `Phone`, `Description`, `Overview`, `Keywords`, `Email`, `Hire`, `Volunteer`, `Invest`, `Live`) VALUES ('".$company_name."','".$web."','".$facebook."','".$twitter."','".$company_address."','".$company_city."','".$company_state."','".$company_country."', 
'".$company_zip."','".$company_phone."','".$description."','".$overview."','".$keywords."','".$company_email."','".$hire."','".$volunteer."','".$invest."',1)";
 if (!mysql_query($q, $this->connection))
 {
 die('Error: ' . mysql_error());
 }

I receive the same error:

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 'INSERT INTO companies (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Ad' at line 1

[soapdude]

Ok.. Now I can't get it to work through SQLMyAdmin.. I think that's a good thing haha.. here's what I'm echoing:

INSERT INTO `users` VALUES ('username', 'b732f2ac717306feb520f5e7c7ef544b', 0, 0, 'First', 'Last', '[e-mail]', 'person', 'Position', 'howiheard', '1294504865') INSERT INTO `companies` (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Address`, `City`, `State`, `Country`, `Zip`, `Phone`, `Description`, `Overview`, `Keywords`, `Email`, `Hire`, `Volunteer`, `Invest`) VALUES ('company','web','facebook','twitter','address','city','state','country', 'zip','contactcompany','Please provide 1-2 sentences that describe your company.','Please provide a descriptive, neutral, third-person overview of this company. Avoid marketing language. \r\n\r\nStart with one sentence that best describes what the company does. Then submit extra paragraphs to provide more depth.','ie: Soap, Organic, Healthy, Exfoliating','companyemail','1','1','1')

and the code:

 $q = "INSERT INTO `".TBL_USERS."` VALUES ('".$username."', '".$password."', 0, 0, '".$fname."', '".$lname."', '".$email."', '".$phone."', '".$position."', '".$howiheard."', '".$time."') INSERT INTO `companies` (`Company_Name`, `Web_Address`, `facebook`, `twitter`, `Address`, `City`, `State`, `Country`, `Zip`, `Phone`, `Description`, `Overview`, `Keywords`, `Email`, `Hire`, `Volunteer`, `Invest`) VALUES ('".$company_name."','".$web."','".$facebook."','".$twitter."','".$company_address."','".$company_city."','".$company_state."','".$company_country."', 
'".$company_zip."','".$company_phone."','".$description."','".$overview."','".$keywords."','".$company_email."','".$hire."','".$volunteer."','".$invest."')";

if (!mysql_query($q, $this->connection))
{
die('Error: ' . mysql_error());
}

any ideas? Thanks again!

[soapdude]

Ok so when I add a semi-colon after each INSERT string in the mysqladmin, the whole thing works, but when I add the semi-colon in te php script, it fails...

[soapdude]

I am so confused.. I believe this semi-colon thing has something to do with it but am at a complete loss...

[penders]

I think multiple SQL statements is your problem - so yes, in a way, the semicolon is your problem!

mysql_query() [uk2.php.net] does not support multiple statements. So you need to either split it up and make a call to mysql_query() for each SQL statement OR use the MySQL Improved Extension (MySQLi) [uk2.php.net] (preferred) and call mysqli::multi_query() [uk2.php.net] or mysqli_multi_query() instead - note the function parameters are reversed from mysql_query().

[soapdude]

Thanks penders! I have gotten this to work now separating into two mysql_query statements... i will mess around wit the MSQLi statement a bit tomorrow and update you on the progress..

[Matthew1980]

@soapdude

Sorry I wasn't able to respond to your posts, but I have been away this weekend, and my mobile only lets me post so many characters! So I have had to wait until I got back home to see the outcome!

Thanks to penders for sorting your issue out, you could have approached this in a few different ways, but personally I think that putting the entire statement into a loop would be about the best.

So long as all the user generated data that your pushing into your DB has been sanitised, you should be fine.

Happy coding and enjoy the rest of your project.

Cheers,
MRb

[penders]

Just a thought... if you had multiple SQL statements in a string, each separated by a semicolon and wished to run this through mysql_query(), then you could simply explode() on the ';' and step through the resulting array? Providing the ';' doesn't appear in your data!?