[SOLVED] help with array syntax

[butteryak]

So I'm still pretty new at this,(disclaimer    ) So I'm working on some code to interact with mysql(to be run as a cron)  anyway.  I'm  not certain if my thinking is correct on this one, as it's a pretty complex syntax for me.  I'm wondering if ya'll can take a peek, and tell me if I'm moving in the right direction on this, or I'm completely off track.

 

so in a nut shell, I have the basics down, as far as connecting, selecting, etc.  (i know my mysql queries work)what I'm really uncertain about, regardless of my research, and perhaps some clarification from someone can help.  when a row is returned from the foreach loop,  and my $row query becomes $field  Is this the proper syntax to call upon a specific "field" within $field(such as $usage=$field['totaloctets'])  so that i can use a specific field returned by that row in my IF and mysql_query statements?

 

Basically what I'm doing is selecting from a table, which will return a number of rows.

thus, the while/foreach.    and then process each row, using information from that row, in the IF {Update}  statement. 

 

Is my thinking correct on how that is handled, or am I just waaay off......

...

 

mysql_connect(localhost,$username,$password);

 

@mysql_select_db($database) or die( "Unable to select database");

 

$totalacctDATA="SELECT Username, totaloctets FROM mtotacct";

 

while ($row=mysql_fetch_row($totalacctDATA)) {

  foreach ($row as $field)  {

      $usage=$field['totaloctets'];

      $user=$field['Username'];

      $updateuser=mysql_query(UPDATE `radius`.`usergroup` SET `GroupName`  = 'penalty'

            WHERE Username = $field['Username']);

          $selectthroughput=mysql_query("SELECT `Value` FROM `radgroupreply` WHERE Attribute = 'throughput' AND `groupname` =

            (SELECT `Groupname` from `usergroup` WHERE `username`=

            $user");

      If ($usage > $selectthroughput) THEN {$updateuser}

      }

    }

 

 

mysql_close();

 

 

 

 

 

thanks for any input folks!!!

 

B

 

[GingerRobot]

You're correct that the required syntax to access an element of an array is like this:

 

$array['key'];
//quote are unnecessary if the key is an integer:
$array[1];

 

However, you may run into syntactical problems when you try to use this in a query. For example, if you try this:

 

mysql_query("SELECT * FROM table WHERE somefield='$array['key']' ");

 

You'll have an error in your query. Strings inside the query must be contained within quotes. In this case, we're using double quotes to demarcate then entire query, so we might use single quotes to delimit the string that somefield must be equal to. However, we're also using single quotes around our the key of our array. MySQL then gets confused.

 

There's a variety of solutions to this (such as switching to doubles to contain the string somefield must be equal to and escaping them, or using concatenation) but, handily, PHP doesn't require single quotes around keys of arrays that are strings if that array is inside double quotes. Thus, this is valid:

 

mysql_query("SELECT * FROM table WHERE somefield='$array[key]' ");

 

Hope that clears up some of the problems with array syntax.

 

By the looks of your code, i'm assuming it was very much pseudocode - you don't actually execute the query and the THEN token isn't used in PHP amongst other errors. If you need further help, you'll need to post some actual code.

 

And finally - if you're faced with using a query inside a loop, it's quite possible a neater solution could be arrived at by using a join. Again though, we'd need more information about your database structure and what you're trying to achieve to help (and the post would be more appropriately placed in the MySQL board).

 

[wildteen88]

The way you're using the foreach is completely incorrect. However looking at the code you don't even need the foreach loop.

 

What are you trying to do?

[butteryak]

Yes, this was a "best attempt" at writing out what I wanted to do, so indeed some parts may be pseudocode, but in a way it's close to what I want to do, minus, the syntax errors, code errors, etc,etc. 

 

 

Basically, i want to Select rows  from table X

 

$totalacctDATA="SELECT Username, totaloctets FROM mtotacct";

 

 

 

$totalacctDATA  returns  a row like this......

 

Usrname          totaloctets

 

tom                    1000

bill                    5000

 

or

 

user(A)              throughput(B)

 

 

Process all rows that are fetched.

 

(This is where I used the while/foreach, but apparently, I was incorrect in this logic)

 

For each processed row

 

If throughput(B) for user(A) is  >  C (C=selects a predetermined amount from a mysqltable, based upon username from returned $row (user(A))

 

Then (update another mysql table, based upon user(A) 

 

So technically, how to process an update(with info from the array), IF conditions are met  (the IF statement is dependent on information retrieved from query)  inside the loop.

 

like I said, maybe I"m way off, in my thinking on how to do this, or if it can even be done.

 

So maybe this pseudocode is a little cleaner, and makes a bit more sense......sorta

 

mysql_connect(localhost,$username,$password);

 

@mysql_select_db($database) or die( "Unable to select database");

 

$totalacctDATA="SELECT Username, totaloctets FROM mtotacct";

 

while ($row=mysql_fetch_row($totalacctDATA))

     

           

 

      If ($row[throughput] > (mysql_query("SELECT `Value` FROM `radgroupreply` WHERE Attribute = 'throughput'

              AND  `groupname`  = (SELECT `Groupname` from `usergroup` WHERE `username`=

            '$row[username]' "))  {mysql_query(UPDATE `radius`.`usergroup` SET `GroupName`  = 'penalty'

            WHERE Username = '$row[username]');}

     

 

 

mysql_close();

 

I'm not so concerned too much about the mysql code(I know it works, minus the string from array),I did debate over wether this should be in mysql section, or here, but i'm more concerned as to how to go about coding the PHP, to execute the mysql queries the way I want to. so thats why i opted for here. 

 

thanks again for your thoughts on this......

 

 

 

[butteryak]

With more reading, I'm thinking I should actually be using

 

while($row=mysql_fetch_array($totalacctDATA, MYSQL_ASSOC))

 

so that I can access the results in the array, by their field names.

 

?

[butteryak]

Actually, 

mysql_fetch_assoc($totalacctDATA)

[butteryak]

This might look better?

 

<?php

 

$username="xxxxxx";

$password="xxxxxxx";

$database="radius";

 

mysql_connect(localhost,$username,$password);

 

@mysql_select_db($database) or die( "Unable to select database");

 

$totalacctDATA="SELECT Username, totaloctets FROM mtotacct";

 

while ($row=mysql_fetch_assoc($totalacctDATA))  {

        $usage=$row['totaloctets'];

        $user=$row['Username'];

 

        $selectthroughput=mysql_query("SELECT `Value` FROM `radgroupreply` WHERE Attribute = 'throughput' AND `groupname` = (SELECT `Groupname` from `usergroup` WHERE `username`=  '$user' ");

       

        $updateuser=mysql_query(UPDATE `radius`.`usergroup` SET `GroupName`  = 'penalty' WHERE Username = '$user');

         

      If ($usage > $selectthroughput) {$updateuser}

    }

 

 

mysql_close();

 

?>

 

Like I said, not to concerned about the mysql queries, more concerned that the PHP syntax to call the array, and then be able to utilize the associated array inside the loop, for the if statements and the queries. and inevitably the result of the If. 

 

so that it loops until all rows have been processed. and the mysql table is updated if conditions are met for each individual loop.

 

 

[GingerRobot]

The mysql_query() function returns a resource - never a specific value from the database. Therefore, comparing $usage to it doesn't make sense - you need to retrieve the actual result from the resource (either with one of the mysql_fetch_xxx functions or mysql_result). Second, this line:

 

   $updateuser=mysql_query(UPDATE `radius`.`usergroup` SET `GroupName`  = 'penalty' WHERE Username = '$user');

 

Executes the query. Therefore, it is executed regardless of the truth of the if statement. You could assign the query string to a a variable prior to your if statement and then execute it inside the if statement, or just do the whole thing inside the if statement.

[butteryak]

Problem solved....thanks guys for all the help, it's much appreciated....

 

Here is the "final" code, that worked for me.......

 

mysql_connect(localhost,$username,$password);

 

@mysql_select_db($database) or die( "Unable to select database");

 

$res=mysql_query("SELECT Username, totaloctets FROM mtotacct");

 

while ($result=mysql_fetch_assoc($res))  {

        $usage=$result['totaloctets'];

        $user=$result['Username'];

 

        $result2=mysql_query("SELECT `Value` FROM `radgroupreply` WHERE Attribute = 'throughput' AND    `groupname`  = (SELECT `defgroup` from `usergroup` WHERE `username`=  '$user' )");

        $selectthroughput=mysql_result($result2, `Value`);     

         

      If ($usage > $selectthroughput) {mysql_query("UPDATE `radius`.`usergroup` SET `GroupName`  = 'penalty'

            WHERE Username = '$user' "); }

    }

 

 

mysql_close();

 

 

 

 

?>