MySQL query row count

[adam525]

I have a problem getting the number of rows from a mysql query.  I'm working with code that was written by someone else and there are multiple queries inside a while loop.  Here is the code...

 

__SNIP__

 

      if ($x == TRANSSOL){

 

          $closefile = TRUE;

          $data = fgetcsv($myFile, 2048, $delimiter);

          $codate = convert_date($data[$fieldArray[1][1]]);

          $udprofit1="SELECT TRANSSOL.mid, TRANSSOL.period, SUM(profit) AS tsprofit, SUM(comm) AS tscomm FROM TRANSSOL WHERE TRANSSOL.period = '{$codate}' GROUP BY TRANSSOL.mid";

          $sql="SELECT PROFIT.ReportDate, PROFIT.mid FROM PROFIT, TRANSSOL WHERE PROFIT.ReportDate = '{$codate}' AND PROFIT.mid = TRANSSOL.mid GROUP BY PROFIT.mid";

                                $sqlpft = mysql_query($sql);

                                $sqldte = mysql_fetch_array($sqlpft);

                                $sqltsdte = $sqldte[0];

                                $sqltsmid = $sqldte[1];

 

                                $udprofit2 = mysql_query($udprofit1);

                                        while ($udrow = mysql_fetch_array($udprofit2)){

                                            $tsmid = $udrow[0];

                                            $tsdte = $udrow[1];

                                            $profit = $udrow['ccprofit'];

                                            $comm = $udrow['cccomm'];

                                                                //$udprofit = mysql_query("INSERT INTO PROFIT (mid,ImportDate,ReportDate) VALUES ('{$tsmid}','{$now}','{$codate}')");

                                mysql_query("INSERT INTO PROFIT (mid, ReportDate) SELECT '{$tsmid}', '{$tsdte}' FROM dual

                                                        WHERE not exists (select * from PROFIT where PROFIT.mid = '{$tsmid}' and PROFIT.ReportDate = '{$tsdte}')");

 

 

                                                        $gresult = mysql_query("UPDATE PROFIT SET PROFIT.ccprofit = '{$profit}' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}' ");

                                                        $gresult1 = mysql_query("UPDATE PROFIT SET PROFIT.cccomm = '{$comm}' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}'");

//ADAM

//$updatetstype = mysql_query("UPDATE PROFIT SET PROFIT.type = 'CC' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}'");

$updatetstype = mysql_query("UPDATE PROFIT SET PROFIT.type = 'TS' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}'");

                                          }

            //mysql_query("DELETE FROM PROFIT WHERE mid = '{$sqltsmid}' AND ReportDate = '{$codate}' LIMIT 1");

 

        $tsresultprofit = mysql_query("UPDATE PROFIT,MERCHANT SET PROFIT.mname = MERCHANT.mname WHERE PROFIT.mid = MERCHANT.mid AND PROFIT.ReportDate = '{$codate}'");

 

}

 

__SNIP__

 

I want to return the number of records updated from this query...

 

$updatetstype = mysql_query("UPDATE PROFIT SET PROFIT.type = 'TS' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}'");

 

and I've tried every way that I can imagine.

 

If I use mysql_affected_rows under that query, like so...

 

$updatetstype = mysql_query("UPDATE PROFIT SET PROFIT.type = 'TS' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}'");

$tempval = mysql_affected_rows();

 

I get that it's not a valid mysql resource 20 times (which is the number of rows that I'm looking for).

 

I've also tried counting the number of elements in the array and I get 20 1's returned. 

 

What would be the best way to get the number of records from that query?

 

TIA,

 

Adam

[revraz]

Dont you need a resource in it?

 

$tempval = mysql_affected_rows($updatetstype);

[The Little Guy]

if you want the number of rows from a select statement:

 

$nRows = mysql_num_rows($sql); // Your sql query

[adam525]

I get the same thing with $tempval = mysql_affected_rows($updatetstype);

[adam525]

To the little guy:

 

This is an update statement though and mysql_affected_rows is not working. 

 

If I use this code :

 

$updatetstype = mysql_query("UPDATE PROFIT SET PROFIT.type = 'TS' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}'");

$tempval = mysql_affected_rows($updatetstype);

echo $tempval;

 

I get :

 

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /var/www/csv_importer.php on line 2254

 

20 times.  I get that 20 times (I know) because I have the echo inside the while loop, but I still dont know why mysql_affected_rows isn't working.

 

I would even settle for being able to count the number of elements in the returned array and that's not working.

 

 

 

 

[revraz]

If you echo $updatetstype after the query, does it produce a Resource ID?

[revraz]

$updatetstype = mysql_query("UPDATE PROFIT SET PROFIT.type = 'TS' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}'");

 

printf("Records Updated: %d\n", mysql_affected_rows());

 

[adam525]

revraz:

 

Here's the output I get using

 

printf("Records Updated: %d\n", mysql_affected_rows());

echo $updatetstype;

 

Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1Records Updated: 1 1

[adam525]

If I could just count sum the elements of that array returned, I think that would work, but I've tried count() and it does the same thing (just returns 20 1's)...

[revraz]

Sounds like you have it in a loop then.  Can you move it outside the loop?

[adam525]

I would then need to pass the mysql_affected_rows function the resource id like...

 

$updatetstype = mysql_query("UPDATE PROFIT SET PROFIT.type = 'TS' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}'");

$tempval = mysql_affected_rows($updatetstype);

} //(end of loop)

echo $tempval

 

I then get..

 

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /var/www/csv_importer.php on line 2254

 

[revraz]

Move the printf statement outside the loop instead.

[adam525]

OK,

 

Now it returns..

 

Records Updated: 1

[revraz]

Probably because just the last one is being recorded since it only updates 1 record at a time.

 

Store that into a variable during your loop, increment it each time, then when it's done echo that instead.

[adam525]

It looks like it's out of scope after the loop.  If I do this (just to see if there's still anything there)...

 

$updatetstype = mysql_query("UPDATE PROFIT SET PROFIT.type = 'TS' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}',$dbcon");

$tsupdate = mysql_affected_rows();

}

echo $tsupdate;

 

I get:

 

-1

[revraz]

During the loop, set a count.  Increment the count using the procedure above.

After the loop, echo the count, not the affected row sql function.

[adam525]

I tried it like so...

 

$updatetstype = mysql_query("UPDATE PROFIT SET PROFIT.type = 'TS' WHERE PROFIT.mid = '{$tsmid}' AND PROFIT.ReportDate = '{$tsdte}',$dbcon");

$countit = 0;

$countit++;

}

echo $countit;

 

and it's returning 1

[revraz]

Because you reset $countit to 0 each time the loop passes.  Set it once prior to the loop.

 

Also, you dont want to use $countit++ because that just increments that the loop went through, not if it was actually updated.  You want to capture the printf output, not increment it with ++.

[adam525]

Thank you!