inserting array into table (not a dupe!)

[inexion]

hey all,

i looked through all the arrays into table posts and no luck with my particular problem....

i know it has something to do with indexes (of which i have none assigned)....i have no mysql errors either but let me explain whats going on.

i have a standard table, 15 columns, varchar(x), myisam, latin1_swedish_ci.........
all i want to do is calculate some values from 2 of the columns and return the values back into either a new column in a new table, or a blank column in the same table that will line up with all the existing records etc...

whats happening:

the data is currently going back into the SAME table in a blank column - however the values don't line up with the rest of the records......for example, there are 179 rows right now, after i run the query it doubles to 358 rows.....the added 179 rows are all blank aside from the one column thats full of my calculated values.

so if at any time i add an index of any type (primary key, unique) i get the error:

Duplicate entry '' for key 1

here is my php thats generating my query:

$sql = "INSERT INTO test_targets (mutot)
VALUES ('{$test[$i]}')";
mysql_query($sql) or die("query failed".mysql_error());

test_targets = table
mutot = blank column in test_targets i need to fill
$test = array full of calculated values

thanks!

[illzz]

If you want to put data into an existing row, you will want to use an UPDATE instead of an INSERT.

Try this syntax instead, updating your WHERE clause as needed...

$sql = "UPDATE test_targets SET mutot='$test[$i]' WHERE id=$unique_id LIMIT 1";

[inexion]

solved.

used this instead:

$sql = "UPDATE targets SET mutot='{$test[$i]}' WHERE id=$i LIMIT $i";

thanks!

[inexion]

ohhh wait a second......

so this is strange, in reality i'm dealing with 179 records. the calculated values getting put back into the table are from an array so the count goes down to 178 (0th array element). i'm looking at the table now, and i see that 2 values at the very end, values 178 & 179 aren't getting inserted - but they are being calculated because i have an html output, and they show up there.....how can i account for the two rows not being calculated? thanks

[illzz]

You may want to look into MySQL's Limit syntax and make sure that you want a variable limit based on ID# (as you have it), rather than a static limit, such as 1.

Do you still have the Query exiting on error?

mysql_query($sql) or die("query failed".mysql_error());

What are the ID's for those 2 rows? Are they unique in that table?

[inexion]

ok lets see here, i'll try to give a better description.

here is my php code:

for($i = 0; $i < count($targets['mura']); $i++){
	$sql = "UPDATE targets SET mutot='".$targets['mutot'][$i]."' WHERE id=$i LIMIT $i ";
	mysql_query($sql) or die("query failed".mysql_error());
}
	echo $sql;

so, what exactly is happening is this:

I have 179 actual records in this table. the entire point of this was to do math on 2 columns in this table, and insert the results into another column (in the same table). so, i have an html output for my results and it prints everything to the screen......

so heres the strange part....it looks like everything in my table is shifted up 2 records, by that i mean record #1 (which doesnt get calculated either for some reason) has record #3's value and so on....and as a result the last 2 records in the database dont even get inserted back into their column.....no matter how I change the code its always shifted up like this by 2 records.

hopefully this is more helpful, let me know if i can give more info - thanks

[illzz]

From the sounds of it, this may be something that would be better placed inside of a database trigger. That is, assuming that you're using MySQL>=5.0.2 and there are going to be more records added to this table.

Try running a var_dump on $targets['mutot'] and see if anything looks odd in the output.

Also, you can echo out each query to make sure that it is formed correctly.

[inexion]

yeah after printing the queries...they come out like .....

$sql = "UPDATE targets SET mutot ='0' WHERE id=177 LIMIT 177";

so they are off by 2 records, but I don't understand why the iteration is losing 2 counts....and even if i make the for statement into an expression (i.e. $i = 0; $i < count($targets['mura'])+2; $i++) it still doesn't work.....and even just making a plain query and using literally the numbers 179 & 179 in the fields won't work =]

i'll look into triggers, thanks!

[illzz]

what does the var_dump say about the array? Is it possible that there are only 177 elements in $targets['mura']? You can also echo out the count of it:

echo count($targets['mura']);

[inexion]

i have it printing out in html heres everything:

size of array: 178
size of results: 178
query: UPDATE targets SET mutot='0' WHERE id=177 LIMIT 177

var_dump says 178 elements, starting on 0 ending 177....

** edit, sorry wrong numbers, should be right now

[illzz]

You say that there are 179 records in the table, yet there are only 178 elements in the array. Unless there are duplicate IDs in your table, you won't be able to update every row in the table.

What do you get if you were to run this in MySQL:

SELECT DISTINCT id FROM targets ORDER BY id;

How many rows are returned? What is the range (start and end)?

[inexion]

yeah i get 179 results from that query, 178 rows.....0-178

its so strange why things arent lining up here, i appreciate your help thus far though illzz thanks a bunch

[illzz]

One thing that might bring you closer is to change your < to <=...

for($i = 0; $i <= 178; $i++){

That will have $i equal to 0 through 178.

[Roger_Ramjet]

1. You don't need to use LIMIT: your WHERE clause specifies a unique ID so only 1 row will ever be updated.

2. [edit] Of course what I wrote here is complete crap. There is nothing wrong with the loop count, but FOREACH is still better IMHO.
   My excuse for the rubbish I wrote is that I'm working in VB with option base 1 all the time: and I always use foreach in php anyway[/edit]

3. Running the same query inside a loop is ALWAYS wrong. SQL will do it for you in 1 query or Stored Proc if you write it correctly.

eg (since I don't know your calculation I'm making up an example)

UPDATE targets SET mutot = ((col1 * col2) - (col1 + col2)) 

Now there are almost all of the mathematical and arithmetic operators and functions available in SQL as there are in PHP, so you don't need to extract the 2 columns and perform the calculations before returning the results - just do it in the query. Not only is this far less code to write, it is far more efficient when run. Each row will be updated with the value calculated from the columns in it so your original problem can not arise.

If you do find that you need to do it in a loop for other reasons then don't use

for($i = 0; $i < count($targets['mura']); $i++){

to iterate through the array, use FOREACH. That way you won't make the mistake of not processing every element because you logic is wrong. You can still run an $i++ counter, but you might just as well use the array index.

[inexion]

hey thanks roger,

i had tried this initially with putting it all into a query.....maybe you can look at it and tell me why it won't work.....

$sql = "UPDATE targets SET mutot = (SQRT(POW(mudec,2) + POW(mura,2)) * ((1+COS(2*RADIANS(DEC)))/2)))";

thanks for all the help, much appreciated!

[Roger_Ramjet]

Too many brackets - count them for yourself and you'll see. Surprised you did not get a syntax error message when you ran it.

[inexion]

sorry must've copied the wrong one, here it is....still not working and i broke it apart and everything

$sql = "UPDATE targets SET mutot = (SQRT(POW(mudec,2)+(POW(mura,2)*((1+COS(2*RADIANS(DEC)))/2))))";

when i remove the RADIANS(DEC) argument from out of the COS function it works....but i need all of one column to be in radians. its almost like COS() doesnt except anything but numbers.....

[inexion]

you know what it might be, is there a SQL function called DEC() ?? maybe I have to rename that column.......

**edit - that was the problem, there must be some function named DEC()....bleh

thanks!!

[Roger_Ramjet]

Familiarise yourself with Reserved Words and never use them to name anything.