Page 1 of 1

PHP 5 and inserting to a table, 2 don't work, 1 does work???

Posted: Tue Jul 25, 2006 11:43 am
by levelfourdesigns
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


These first two functions do not work...  I have PHP 5 and MySQL 5 running...

Code: Select all

function insertOrder($ClientID,$Total)
		//inserts a new order into the system with total and shipping information
	{
		$ShipMethod = "Standard Shipping";
		$OrderStatus = "Order Started";
		$result = mysql_query("Insert into orders values('', '".$ClientID."', '".$Total."', '".$ShipMethod."', NOW(),'".$OrderStatus."')");
		if(!mysql_error()) return mysql_insert_id($this->conn);
		else return "error";
	
	}
	
	function insertDetails($OrderID,$Cart)
		//inserts the order details into the many-many relationship table
	{

		for ($i=0; $i<sizeof($Cart); $i++)
		$result = mysql_query("INSERT INTO details(OrderID, ProductID, Quantity) values('".$OrderID."', '".$Cart[$i]['productid']."', '".$Cart[$i]['quantity']."')");				
		if(!mysql_error()) return $OrderID;
		else return "error";	
	
	}

This function is on the same page and does work correctly...

Code: Select all

function insertMailingList($email)
		//insert into the mailing list
	{
		$result = mysql_query("Insert into mailinglist values('".$email."')");
		if(!mysql_error()) return $email;
		else return "error";
	}
what gives on the first two? something simple? By the way, all three work under my test environment using PHP 4.3 and MySQL 4, but under the new PHP 5 and MySQL 5, the first two error out.

JOsh Jones


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Jul 25, 2006 2:51 pm
by RobertGonzalez
Have you tried running the queries from phpMyAdmin (or your preferred MySQL Admin Utility) or the command line? I would start there just to make sure the queries are working. You may want to also consider running the $result through mysql_affected_rows also to make sure there was a change to the data that was INSERTED/UPDATED.

Posted: Tue Jul 25, 2006 4:12 pm
by jamiel
PPut your SQL into a variable and var_dump or print_r the output and make sure the query is what you expect. Side note, why return 'error' ? It wont help you at all in debugging. ouput the error or log it somewhere.

Replies

Posted: Tue Jul 25, 2006 4:20 pm
by levelfourdesigns
1. I have to return error, or really anything because these are being used as Flash Remoting Services through AMFphp and I'm using that as my response, which I can then take and do something with in Flash, it really does't matter what I return.

2. As for the services, I can check them through the Flash Service Browser provided by AMFPHP. I am not very good with PHP at all, so I really have parts and pieced these together. Not really knowing how each really does things.

3. If there is a correct way to rewrite this so that it works in both PHP 5 and PHP 4, that would be great... but something seems amis if my third "insert" works and the others do not only in PHP 5. must be something little I was hoping an expert could point out.

Josh Jones

Posted: Tue Jul 25, 2006 4:24 pm
by jamiel
var_dump the SQL and the mysql_error() and I assure you that you will spot your error in at least one of them.

I'm an idiot

Posted: Tue Jul 25, 2006 6:34 pm
by levelfourdesigns
so would the return code look like this?

Code: Select all

else return mysql_error();
forgive me for being an idiot...

ok, got this error

Posted: Tue Jul 25, 2006 7:45 pm
by levelfourdesigns

Code: Select all

Out of range value adjusted for column 'OrderID' at row 1
when I did "else return mysql_error();"

so I am assuming there is something wrong with how I present my various variables in the insert? Is there another, or proper, syntax to use for inserting those variables?

thanks in advance...

Josh

Posted: Tue Jul 25, 2006 11:34 pm
by RobertGonzalez
On an update/insert, this error usually means that you are trying to add data of one type into a field of another type. Look at your table tow datatypes and see what they are as compared to what is getting put in them.

Posted: Wed Jul 26, 2006 4:18 pm
by levelfourdesigns
ok, sounds reasonable..

here are my data types for my insertOrder()

OrderID = Int(11)
ClientID = Int(11)
Total = float(7,2)
ShipMethod = varchar(255)
OrderDate = TimeStamp(14)
OrderStatus = varchar(50)

hope this helps?

Posted: Wed Jul 26, 2006 11:01 pm
by RobertGonzalez
Is orderid an autoincrement field? Primary Key? If it is not autoincrement, then passing it a null value ('') makes it choke if you have the field set to NOT NULL. Even moreso if it is a PK.

Posted: Thu Jul 27, 2006 9:32 am
by levelfourdesigns
sorry, I should have stated that. yes, orderID is a Primary Key and it is autoincrement...

does the problem exist because of how I put those fields in the insert?

'".OrderID."'

does it have to have the periods and single and double quotes?

Josh

Posted: Thu Jul 27, 2006 10:20 am
by RobertGonzalez
Try your queries like this...

Code: Select all

<?php
$sql = "Insert into orders values('', '$ClientID', '$Total', '$ShipMethod', NOW(),'$OrderStatus')";
if (!$result = mysql_query($sql))
{
    return 'Error in statement ' . $sql . ': ' . mysql_error();
}
else
{
    return mysql_insert_id($this->conn);
}
?>
It might make it a little easier to read through the code and locate potential error in the SQL strings. And no, concatenating shouldn't cause any of the error unless the concatenating is done wrong.

Posted: Thu Jul 27, 2006 1:03 pm
by levelfourdesigns
Error in statement Insert into orders values('', '2', '22', 'Standard Shipping', NOW(),'Order Started'): Out of range value adjusted for column 'OrderID' at row 1


I tried this new code and still get this error.

I am beginning to think that either MySQL 5 or PHP 5 doesn't like to insert the '' Null value? But I need the Database to simply autoincrement that field on a per order basis.

OrderID is setup as a "Not Null", "Primary Key", and "AutoIncrement" field. All works under the MySQL 4 and PHP 4 test system though.

I appreciate the help here...

Josh

Posted: Thu Jul 27, 2006 1:13 pm
by RobertGonzalez
Take this exact query and try it in phpMyAdmin:

Code: Select all

Insert into orders values('', '2', '22', 'Standard Shipping', NOW(),'Order Started');
if that fails, try this:

Code: Select all

INSERT INTO `orders` (`OrderID`, `ClientID`, `Total`, `ShipMethod`, `OrderDate`, `OrderStatus`) VALUES ('', 2, 22, 'Standard Shipping', NOW(),'Order Started');

Posted: Thu Jul 27, 2006 3:30 pm
by levelfourdesigns
tried both, both returned the same error? arrrrgh... and yet my other inserts work...

After further searching, it is appearing to be a MySQL 5 bug that is currently involved with MySQL strict mode and not the PHP code we are working on... Apparently we can turn off this strict mode in my my.ini file and it solved my issue...

sorry for all the confusion, but cripes, what an ordeal for something so simple...

by the way, thanks for the insert code, it's much cleaner looking than my original one.

Josh