Using OOP with PHP and MySQL, getting a weird error.

[sifuhall]

Hello everyone, I'm not sure what is causing this issue or what I can do about it.

 

I have a data class and one of the methods is query:

 

Code:

 

function query($Query_String) {
            $this->Query_ID = mysql_query($Query_String,$this->Link_ID);                                                    
            $this->Row = 0;
            $this->Errno = mysql_errno();
            $this->Error = mysql_error();                                    
            if (! $this->Query_ID) {
                $this->halt("Invalid SQL: " . $Query_String);
            }
            return $this->Query_ID;
        } 

 

 

This works great and I have used it many times.

 

I also have another class that uses the data class.

 

Only one method of this other class is having an issue with the data class.

 

Here is the relevant snippet:

 

Code:

 

// update skills, styles, and attributes if bcontinue == 1
        if ($bcontinue == 1) {
            // calculate remaining tp
            $mynewtp = $this->tp - $myspenttp;

            $dataf = new Data; // get db handle to db

            // update attributes
            $dataf->query("UPDATE fighters SET strength=$mystr, speed=$myspe, agility=$myagi, stamina=$mysta, endurance=$myend, flexibility=$myflx, tp=$mynewtp WHERE id=$this->id");

            // update styles
            for ($ic = 0; $ic < count($mystyles); $ic++) {
                if (round($mystyles[$ic]['rank'],2) <> round($mystyleinfo[($mystyles[$ic]['id'] - 1)]['rank'],2)) {
                    $dataf->query("UPDATE fighter_styles SET rank = " . round($mystyleinfo[($mystyles[$ic]['id'] - 1)]['rank'],2) . " WHERE id = " . $mystyles[$ic]['fighterstyleid'] . " and fighter_id = $this->id");
                }
                
                // update skills
                $myskills = $this->getSkills($mystyles[$ic]['id']);

                for ($ic2 = 0; $ic2 < count($myskills); $ic2++) {
                    $mynewskill = sanitize_int($skills["skill-" . $myskills[$ic2]['fighterskill_id']]);
                    if ($mynewskill <> $myskills[$ic2]['skill_value']) {
                        $dataf->query("UPDATE fighter_skills SET value = " . round($mynewskill,2) . " WHERE id = " . $myskills[$ic2]['fighterskill_id'] . " and fighter_id = $this->id");
                    }
                }
            }

            // reload stats
            $this->set_stats($this->id);
        } 

 

 

The issue is the inner for loop (for ($ic2 = 0; $ic2 < count($myskills); $ic2++) {)

 

When it executes the data query method ($dataf->query("UPDATE fighter_skills SET value = " . round($mynewskill,2) . " WHERE id = " . $myskills[$ic2]['fighterskill_id'] . " and fighter_id = $this->id")

 

it works for the first iteration of the loop, but every other iteration creates the invalid sql error from the data class.

 

However, if I copy and paste the sql that generates the invalid sql error into mysql it works with no issues so the sql syntax is correct.

 

Any ideas on what may be causing this to error out on any iteration of the inner for loop after the first?

 

Many thanks for the help.

[sifuhall]

Here is the results of the inner loop.

 

The first update statement was successful, the second was not.

 

 

UPDATE fighter_skills SET value = 3 WHERE id = 37 and fighter_id = 91

 

UPDATE fighter_skills SET value = 1 WHERE id = 55 and fighter_id = 91

 

Error: Database error - : Invalid SQL: UPDATE fighter_skills SET value = 1 WHERE id = 55 and fighter_id = 91 in /home/league/public_html/classes/class.Data.php on line187

[RMcLeod]

Instead of echoing out the query string, echo out the mysql error e.g.

 

<?php
...
if (! $this->Query_ID) {
  $this->halt($this->Error);
}
...
?>

 

This will make it a lot easier to debug