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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
levelfourdesigns
Forum Newbie
Posts: 8
Joined: Tue Jul 25, 2006 11:38 am

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

Post 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]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
levelfourdesigns
Forum Newbie
Posts: 8
Joined: Tue Jul 25, 2006 11:38 am

Replies

Post 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
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
levelfourdesigns
Forum Newbie
Posts: 8
Joined: Tue Jul 25, 2006 11:38 am

I'm an idiot

Post by levelfourdesigns »

so would the return code look like this?

Code: Select all

else return mysql_error();
forgive me for being an idiot...
levelfourdesigns
Forum Newbie
Posts: 8
Joined: Tue Jul 25, 2006 11:38 am

ok, got this error

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
levelfourdesigns
Forum Newbie
Posts: 8
Joined: Tue Jul 25, 2006 11:38 am

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
levelfourdesigns
Forum Newbie
Posts: 8
Joined: Tue Jul 25, 2006 11:38 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
levelfourdesigns
Forum Newbie
Posts: 8
Joined: Tue Jul 25, 2006 11:38 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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');
levelfourdesigns
Forum Newbie
Posts: 8
Joined: Tue Jul 25, 2006 11:38 am

Post 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
Post Reply