Having trouble with an update

Drabin

Here's my code

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

## Performing a query to find all the ingredients of the ZG loot system, including the 
## event days where the names match up for both tables
$query = "
SELECT mc_drop_garr.item item, COUNT( fab_mc.epics ) epics, mc_drop_garr.`drop` drops
FROM `fab_mc` , `mc_drop_garr` 
WHERE fab_mc.epics = mc_drop_garr.item
GROUP BY mc_drop_garr.item";

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

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

$sql = "UPDATE mc_drop_garr SET 
 `drop`='$drops'";
	if ($sql_debug_mode==1) { echo "<BR>SQL: $sql<BR>"; }
$result = mysql_query($sql);
	if ($result == 1) {
	die ('<center>Garr dropped totals have been updated.</center>');
	} else {
	echo "Error with SQL and updating..";
	}
?>

I'm receiving the following error:

Parse error: parse error, unexpected $ in garrupdate1.php on line 30

Line 30 is ?>

Any ideas what I'm doing wrong here?

Thanks,

Installer

You don't have a closing curly brace for your while loop.

Drabin

Yeah, I just found it - but I have another issue

Here's the corrected code by the way:

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

## Performing a query to find all the ingredients of the ZG loot system, including the 
## event days where the names match up for both tables
$query = "
SELECT mc_drop_garr.item item, COUNT( fab_mc.epics ) epics, mc_drop_garr.`drop` drops
FROM `fab_mc` , `mc_drop_garr` 
WHERE fab_mc.epics = mc_drop_garr.item
GROUP BY mc_drop_garr.item";

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

$i=0;
while ($i < $num) 
{

$drops=mysql_result($result,$i,"epics");
$sql = "UPDATE `mc_drop_garr` SET `drop`= '$drops'";

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

It's updating all of the fields with "2" instead of updating them with the correct numbers. The query I uses is sorting the numbers correctly, but maybe I need to load them in an array and then update properly.

Any help here?

Drabin

So, it's only updating 1result out of the 13 that are returned from the select query.

Installer

And I'll bet that's the last result. Since, without a "WHERE" the "UPDATE" will change every row. So it's being changed 13 times, and when it finishes the last one is the one that remains.

Drabin

I get an invalid mysql resource if I use:

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

Drabin

Okay,

I'm getting closer. Here's what I have now:

<?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 mc_drop_garr.item item, COUNT( fab_mc.epics ) epics, mc_drop_garr.`drop` drops
FROM `fab_mc` , `mc_drop_garr` 
WHERE fab_mc.epics = mc_drop_garr.item
GROUP BY mc_drop_garr.item";

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

## Running a while loop to pull the query results and update the table
## drop field with the individual row counts of the epic field from the
## fab_mc table
$i=0;
while ($i < $num) 
{

$drops=mysql_result($result,$i,"epics");
$sql = "UPDATE `mc_drop_garr` 
SET `drop`='$drops'
WHERE `id`='$i'";

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

I'm receiving:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in garrupdate1.php on line 24

Crowly

Try this, i presume that both $drops and $i are number variables, and the database fields are as well. When you do ex SET something = '2', you set 2 as text and not a number, SET something = 2 its a number:

 $sql = sprintf("UPDATE `mc_drop_garr` 
    SET `drop`=%d 
    WHERE `id`=%d",$drops,$i);

Example 2 on this page might be useful: http://www.zend.com/manual/function.mysql-query.php

Also i think you can drop the ` in your SQL commands, i dont use them and it works for me. So you could to this:

 $sql = sprintf("UPDATE mc_drop_garr
    SET drop=%d 
    WHERE id=%d",$drops,$i);

Installer

The backticks are needed around MySQL reserved words, such as DROP (or drop).

tiburblium

This is a bit offtopic but I was wondering if Garr has dropped bindings of the windseeker for you guys yet? And if so how many times over how many runs?