MYSQLI REPLACE() function while inserting multiple rows

[funbinod]

i was trying to insert rows from one table to another and edit values before inserting. for that i did the following 

INSERT INTO projectra (projid, nermid, name, code, per, unitper, description, materials, manerials, macerials) SELECT '$projid', fullid, name, code, per, unitper, description,
REPLACE (materials, SUBSTRING_INDEX(materials, ';', -1), 
	(
		(SELECT (lurate) FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))
		+
		(
			(SELECT uweight FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))
			*
			(SELECT rate FROM transport WHERE name=(CONCAT((SELECT ctype FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1)), '-BLACK TOPPED')) AND cid='$cid' AND active='Y')
			*
			(SELECT SUBSTRING_INDEX((SELECT IF ((SELECT itype FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))='L', Ldistance, Fdistance) FROM projects WHERE fullid='$projid'), ';', 1))
		)
		+
		(
			(SELECT uweight FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))
			*
			(SELECT rate FROM transport WHERE name=(CONCAT((SELECT ctype FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1)), '-GRAVELL')) AND cid='$cid' AND active='Y')
			*
			(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT IF ((SELECT itype FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))='L', Ldistance, Fdistance) FROM projects WHERE fullid='$projid'), ';', 2), ';', -1))
		)
		+
		(
			(SELECT uweight FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))
			*
			(SELECT rate FROM transport WHERE name=(CONCAT((SELECT ctype FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1)), '-EARTHEN')) AND cid='$cid' AND active='Y')
			*
			(SELECT SUBSTRING_INDEX((SELECT IF ((SELECT itype FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))='L', Ldistance, Fdistance) FROM projects WHERE fullid='$projid'), ';', -1))
		)
	)
),
REPLACE (manerials, SUBSTRING_INDEX(manerials, ';', -1), '0'),
REPLACE (macerials, SUBSTRING_INDEX(macerials, ';', -1), '0') FROM nerms WHERE fullid='$nid'");

this works very good - calculates and replaces the exact point as expected. but the problem is it calculates only one random row and lefts other to be NULL.

for example, if there are 4 rows that meet the condition, all the 4 rows are copied but only one row contain the expected data and others are left to be NULL.

i've submitted 2 screenshots of phpmyadmin showing 2 tables - first one is from which i am coping data (there you can see data on cells) and another is of next table after the copying data (there you can see NULL on cells).

 

thanks in advance...

 

[justsomeguy]

In that big calculation, if any of those columns are null I think the result is going to be null.

[funbinod]

24 minutes ago, justsomeguy said:

In that big calculation, if any of those columns are null I think the result is going to be null.

thank u for the reply.

but if my explanation above was unclear i again explain - columns with data also converts to NULL except one row. you can check those two screenshots.

[justsomeguy]

I read what you posted, but I don't know which columns are in that screenshot, there are no headers.  I don't know which tables they are either.  I don't know what values are in items, transport, projects, and nerms.

You're selecting data from multiple tables and using math operations on them, addition and multiplication.  What I am saying if that if any of those values you use in addition or multiplication are null, the answer is null.

This is what I mean:

1 + 2 + null + 5 + 9 = null
	2 * 5 * null * 3 = null

[funbinod]

5 hours ago, justsomeguy said:

I read what you posted, but I don't know which columns are in that screenshot, there are no headers.  I don't know which tables they are either.  I don't know what values are in items, transport, projects, and nerms.

thank u for the reply. and sorry for the undefined table screenshots.

here i'm again posting the screenshot with some markings.

my purpose of this practice is to change the underlined part of the data before copying and the calculation (math) is for that purpose.

i'm copying the bordered 4 rows and the math should be applied for all the rows. but it inserted all the rows but only one row with data and rest NULL.

 

and one more thing.

i tried to shorten the code and found some interesting results.

the following code inserted all the rows with data

INSERT INTO projectra (projid, nermid, name, code, per, unitper, description, materials, manerials, macerials) SELECT '$projid', fullid, name, code, per, unitper, description,
REPLACE (materials, SUBSTRING_INDEX(materials, ';', -1), 
	(
		(SELECT (lurate) FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))
	)
),
REPLACE (manerials, SUBSTRING_INDEX(manerials, ';', -1), '0'),
REPLACE (macerials, SUBSTRING_INDEX(macerials, ';', -1), '0') FROM nerms WHERE fullid='$nid'");

and the following code inserted 2 rows with data and 2 rows NULL

INSERT INTO projectra (projid, nermid, name, code, per, unitper, description, materials, manerials, macerials) SELECT '$projid', fullid, name, code, per, unitper, description,
REPLACE (materials, SUBSTRING_INDEX(materials, ';', -1), 
	(
		(SELECT (lurate) FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))
		+
		(
			(SELECT uweight FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))
			*
			(SELECT rate FROM transport WHERE name=(CONCAT((SELECT ctype FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1)), '-BLACK TOPPED')) AND cid='$cid' AND active='Y')
			*
			(SELECT SUBSTRING_INDEX((SELECT IF ((SELECT itype FROM items WHERE fullid=SUBSTRING_INDEX(materials, ';', 1))='L', Ldistance, Fdistance) FROM projects WHERE fullid='$projid'), ';', 1))
		)
	)
),
REPLACE (manerials, SUBSTRING_INDEX(manerials, ';', -1), '0'),
REPLACE (macerials, SUBSTRING_INDEX(macerials, ';', -1), '0') FROM nerms WHERE fullid='$nid'");

the second part of the addition could be any one among the three on the main code.

 

thank u again.

[funbinod]

7 hours ago, justsomeguy said:

 

1 + 2 + null + 5 + 9 = null
	2 * 5 * null * 3 = null

 

wow! this solved the problem. i checked for all the tables and found some columns be empty that led to this problem. i corrected them and found that the above code is working properly. thank u for the idea.