[RESOLVED] Having problems updating

[Drabin]

Here is my current code:

<?php
require('qs_connection.php');
require('qs_functions.php');
require('../config.php');

## Performing a query to determine the epic counts based on item type

$query="SELECT COUNT( fab_mc.epics ) epics,
mc_drop_garr.`id` gid
FROM `fab_mc` , `mc_drop_garr` 
WHERE fab_mc.epics = mc_drop_garr.item
GROUP BY mc_drop_garr.item, mc_drop_garr.`id` ASC";

$result=mysql_query($query) or die(mysql_error());
$num=mysql_numrows($result);

$i=0;

while ($i < $num) {
    $drops=mysql_result($result,$i,"epics");
    $id=mysql_result($result, $i, "gid");

$sql="UPDATE `mc_drop_garr`, `fab_mc` SET 
`drop`='$drops'
WHERE fab_mc.epics=mc_drop_garr.item";

if ($sql_debug_mode==1) { echo "<BR>SQL: $sql<BR>"; }
$result = mysql_query($sql) or die(mysql_error());
$i++;
}
    if ($result == 1) {
    die ('<center>Garr dropped totals have been updated.</center>');
    } else {
    echo "Error with SQL and updating..";
    }

?>

Here's what "should" be happening:

The search query finds all of the data from fab_mc.epics that equals the same name as mc_drop_garr.item and counts those up and groups them. It displays similar to:

1 -- 5
2 -- 2
3 -- 1
4 -- 6
5 -- 4
6 -- 3
etc. up to ID 13

The left hand column is the ID.
The right hand column are the grouped "dropped" counts.

I want to update the drop counts into mc_drop_garr.drop where they correspond to the ID.

However, I keep getting an invalid result resource for the following two lines:

    $drops=mysql_result($result,$i,"epics");
    $id=mysql_result($result, $i, "gid");

Can anyone let me know what I'm doing wrong and what I should be altering with my code to update these values?

Thanks!

[bogu]

Look in here, I changed your code a little ...

<?php 
require('qs_connection.php'); 
require('qs_functions.php'); 
require('../config.php'); 

## Performing a query to determine the epic counts based on item type 

$query="SELECT COUNT( fab_mc.epics ) as epics, 
mc_drop_garr.`id` as gid 
FROM `fab_mc` , `mc_drop_garr` 
WHERE fab_mc.epics = mc_drop_garr.item 
GROUP BY mc_drop_garr.item, mc_drop_garr.`id` ASC"; 

$result=mysql_query($query) or die(mysql_error()); 
while ($rez = mysql_fetch_assoc($result)) { 
    $sql="UPDATE `mc_drop_garr`, `fab_mc` SET 
    `drop`='{$rez['epics']}' 
    WHERE fab_mc.epics=mc_drop_garr.item"; 
    mysql_query($sql) or die(mysql_error());
    if (mysql_affected_rows()) {
    	echo "UPDATE made successfully!";
    } else {
    	echo "No update has been made!";
    }
}
?>

[Drabin]

Hi Bogu:

I tried the code and received:

UPDATE made successfully!UPDATE made successfully!UPDATE made successfully!No update has been made!UPDATE made successfully!No update has been made!UPDATE made successfully!UPDATE made successfully!No update has been made!UPDATE made successfully!UPDATE made successfully!No update has been made!

When I checked the data, it updated everything with the number 5, which is the first line. So, while it at least is doing something now, it is not quite there yet.

I appreciate the assistance. Any ideas?

[Drabin]

I put a break after the segments to see what it did a little easier and got:

UPDATE made successfully!
UPDATE made successfully!
UPDATE made successfully!
No update has been made!
UPDATE made successfully!
No update has been made!
UPDATE made successfully!
UPDATE made successfully!
No update has been made!
UPDATE made successfully!
UPDATE made successfully!
No update has been made!

I checked the tables and there is one of 13 rows that should not be affected so it did look at all 12 rows. It is supposed to look at 12 rows not 13 and your code did do that. It looks like it just updated the affected rows with the first returned value from the query.

Is it because it's not reading from an array? Or, is that even necessary? The code you gave I feel is close though. I just need it to return specific values.

When I use this bit of code:

<?php

require('qs_connection.php');
require('qs_functions.php');
require('../config.php');

## Performing a query to determine the epic counts based on item type

$query="SELECT COUNT( fab_mc.epics ) epics,
mc_drop_garr.`id` gid
FROM `fab_mc` , `mc_drop_garr` 
WHERE fab_mc.epics = mc_drop_garr.item
GROUP BY mc_drop_garr.item, mc_drop_garr.`id` ASC";

$result=mysql_query($query) or die(mysql_error());
$num=mysql_numrows($result);

while($row = mysql_fetch_array($result)){
	echo $row['gid']. " - ". $row['epics'];
	echo "<br />";
}
?>

It returns:

11 - 2
6 - 5
1 - 2
2 - 2
12 - 3
7 - 3
10 - 4
4 - 2
8 - 2
3 - 4
13 - 5
9 - 5

The left value is the id or "gid" and the right value is the "epics" which get recorded into the drop field on the mc_garr_drop table. You'll also notice that it skipped the record for id number 5 because it contains no matching data.

[bogu]

Sry, I didn't look that close to the script, u need to change your where condition from the update statement ...

WHERE fab_mc.epics =mc_drop_garr.item AND mc_drop_garr.item = {$row['gid']}";

U tell to mysql to update rows that have identical ID and the ID equal $row['gid']

[Drabin]

Okay Bogu,

Thanks to your help I figured out what you were trying to do and I corrected it myself based off all the great feedback you gave. Here's the final code and yes it works!

<?php 
require('qs_connection.php'); 
require('qs_functions.php'); 
require('../config.php'); 

## Performing a query to determine the epic counts based on item type 

$query="SELECT COUNT( fab_mc.epics ) as epics, 
mc_drop_garr.`id` as gid 
FROM `fab_mc` , `mc_drop_garr` 
WHERE fab_mc.epics = mc_drop_garr.item 
GROUP BY mc_drop_garr.item, mc_drop_garr.`id` ASC"; 

$result=mysql_query($query) or die(mysql_error()); 
while ($rez = mysql_fetch_assoc($result)) { 
    $sql="UPDATE `mc_drop_garr`, `fab_mc` SET 
    `drop`='{$rez['epics']}' 
    WHERE fab_mc.epics =mc_drop_garr.item AND mc_drop_garr.`id` = {$rez['gid']}";  

    mysql_query($sql) or die(mysql_error()); 
    if (mysql_affected_rows()) { 
        echo "UPDATE made successfully!"; 
    } else { 
        echo "No update has been made!"; 
    } 
} 
?> 

I had to change the where line to read:

WHERE fab_mc.epics =mc_drop_garr.item AND mc_drop_garr.`id` = {$rez['gid']}";

[bogu]

I'm glad that I could help.

Have fun coding. 😃