Loops and Arrays Help

[wellscam]

Hello! I'm super stoked I found you guys, this is the quickest, least amount of crap php site I have been able to find and I hope you can help me out. I am running PHP 5.2.4 and connecting to an Access database generated by our phone switch (I would prefer MySql, but the switch software creates and maintains the database, I'm just tapping into it).

 

I am fairly new to PHP and looking to get some help with a large query I set up for our phone system. Basically, what I want to do, is make the huge hand-coded query more dynamic and smaller so that it returns results faster and I don't have to edit a bunch of code every time something changes (which is often!).

 

Here is the query:

 

          $sqlCJB = "SELECT Count(*) AS TotalNumberOfCallsCJB FROM AllInfo WHERE Extension = '3911' AND Date=#".$id."#";
		$timeCJB = "SELECT Date, Extension, sum(DurationS) AS CountOfDurationCJB
			FROM AllInfo 
			WHERE Extension = '3911' 
				AND IO = 'Out' 
				AND Date=#$id#
			GROUP BY Date, Extension, IO 
		   ";	

            $queryCJB = odbc_exec($odbc,$sqlCJB) or die(odbc_errormsg());
					$timequeryCJB = odbc_exec($odbc,$timeCJB) or die(odbc_errormsg());

/*		***	Query Carrie J Boreham*** */
		            while($row = odbc_fetch_array($queryCJB)) {
               
		      echo "<tr>";
                  echo "<td class=\"borderTable\"><center>3911</center></td class=\"borderTable\">";
                  echo "<td class=\"borderTable\"><center>Carrie Boreham</center></td class=\"borderTable\">"; 
                  echo "<td class=\"borderTable\"><center>".$row['TotalNumberOfCallsCJB']."</center></td class=\"borderTable\">";

			  		while($row = odbc_fetch_array($timequeryCJB)){

			  echo "<td class=\"borderTable\"><center>".minutestoHours($row["CountOfDurationCJB"])."</center></td class=\"borderTable\">";	}
			  
            } // end while

 

Then I have this in the file 50 times under different query and sql names for all the employees. What I would like to do is figure out how to make this loop on itself based on another file that I can edit on the fly to simple add or remove someone from the list, then have the output sortable by column ( I don't know how to make this sortable. )

 

            echo "<center><table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" class=\"borderTable\">
<tr><th class=\"borderTable\">   Extension   </th><th class=\"borderTable\">     Username     </th><th class=\"borderTable\">Total Dials</th><th class=\"borderTable\">Total Talk Time</th></tr>";

 

Think maybe someone can help me out on this? I know it's probably pretty simple for someone who does this all the time, I just can't figure it out.

[ober]

For the users list, you could simply create a text file that you edit.  Search the board on handling files or look at the manual under http://us.php.net/manual/en/function.file.php.  That should get you started.

 

Then you just feed the extension into the query as a variable in a loop.  file() will split the contents of the file into an array already so you can just use foreach() or a while() loop to run through the array.

 

As far as sorting by column, simply pass a variable to the query and use an "ORDER BY" clause (this works in any DB).

[wellscam]

Wow, you make it sound so simple!! I've never used any of these functions before!

 

I know it's asking a lot, but think you might be able to hepl me with coding this part:

 

Then you just feed the extension into the query as a variable in a loop.  file() will split the contents of the file into an array already so you can just use foreach() or a while() loop to run through the array.

 

I'm so new at this I feel like I'm trying to speak French and I only understand how to say yes, and no, and ask where the bathroom is at! I can usually look at examples and logically figure it out then try to adapt it to my own purposes, but I've been trying to do that for this now for a couple months (as I find the time) and just can't seem to figure it out!

[ober]

From your query you posted it looks like all you need to query the DB for a user is their phone extension, right?  If so, I'd create a text file with the following (just a list of extension numbers):

 

1234

1343

2345

 

Then:

$exts = file('/home/user/phones.txt');
foreach ($exts as $num => $ext) {
    $query = "SELECT * FROM table WHERE ext = $ext";
    // do whatever else needs to be done here
}

 

Or if you want to just do one query where you grab all of the records at once:

 

$exts = file('/home/user/phones.txt');
$str .= "SELECT * FROM table WHERE ext IN (".implode(",",$exts.)")";
    // do whatever else needs to be done here
}

 

Does that help?

[wellscam]

I think so, but If I'm reading that right, it's actually going to combine all of my extensions into one result right?

 

What I'm trying to do is get a total count of dials and talk time for each extension individually.

 

Am I misunderstanding the query?

[ober]

Oh... well I wasn't clear on the intent.  You can still get totals within one query.  You should be able to use a combination of DISTINCT, COUNT and GROUP BY to get what you need.  I'd suggest posting in the database boards to get that information.

[wellscam]

Thank you so much for getting me started in the right direction!! 

[ober]

Be sure to post again if you get stuck.