ncurran217

That worked. I always forget about the case sensitive part.

I get Unidentified Indexes for Listcode year and count at this line: $data[$row['listcode']][$row['year']] = $row['count']

Ok, I am just confusing myself, on trying to put this together. I figured there could be some type of verifying of the next row that the List Code is the same, if the list code is the same it will echo out the counts of the years in the columns. Once it sees the List Code does not match it will move to the next row and start the process again. But i have not linked while statements with actual conditions or with foreach statement with conditions. All I know is that I am extremely confused on how to even do this. Can anyone right up an example of how this would be done. Or point me to where there is a tutorial with something similar to this? Thank you in advance.

Also, the 003EL and 004EL are not the only ones, there are hundreds of those, so hard cording that in wouldnt be an option. But will try and play with it and see what I can get to work. If you have any options on how I would do this that will be very helpful. Thanks again for the help!

hmm I will try a few things with that and see what I can put together.

Right now I have a code that outputs results into a table: <?php include 'includes/db_connect.php'; $List = $_GET['List']; if( $connection === false ) { echo "Unable to connect.</br>"; die( print_r( sqlsrv_errors(), true)); } $query = " SELECT LISTCODE, YEAR, COUNT(YEAR) AS Count FROM Names GROUP BY LISTCODE, RIGHT(LISTCODE, 2), YEAR HAVING (RIGHT(LISTCODE, 2) = '$List') ORDER BY LISTCODE, YEAR "; $result = sqlsrv_query($connection,$query); // each array key is the database column name, the corresponding value is the legend/heading to display in the HTML table // the order of the items in this array are the order they will be output in the HTML table $fields = array('LISTCODE'=>'ListCode','YEAR'=>'Year','Count'=>'Count'); // start table and produce table heading echo "<table>\n<tr>"; foreach($fields as $legend){ echo "<th>$legend</th>"; } echo "</tr>\n"; // output table data while($row = sqlsrv_fetch_array( $result,SQLSRV_FETCH_ASSOC)) { echo "<tr>"; foreach($fields as $key=>$not_used) { echo "<td>$row[$key]</td>"; } echo "</tr>\n"; } echo "</table>\n"; sqlsrv_free_stmt ($result); sqlsrv_close( $connection); ?> That all works great and the attached picture originalqueryoutput is what it looks like. Just a little hard to read. What I would like to is have the output look something like the attached picture wantedqueryoutput, which I just did in excel to show an example of what I am going for. Is this possible? And how would I even begin to manipulate the data rows from the query to the table? Thanks in advance for the help!

Awesome. Thank you very much for the help. Spreads new light on it for me!

I have this code here and it works, just wondering if there is an easier and cleaner way to do this or not. Here is the code I have: <?php include 'includes/db_connectdate.php'; $Start_Date = $_GET['start_date']; $End_Date = $_GET['end_date']; $Forte_ID = $_GET['ForteID']; $query = ' SELECT Reps.Rep, Logs.RefNumber, Logs.Disposition, Logs.Cancel_Disposition, Logs.Date, Logs.appNumber, Logs.Phone_Num, Logs.Con_Number, Logs.Finance_Num, Logs.Num_Payments, Logs.ACH_CC, Logs.Post_Date, Logs.Callback, Logs.Disc_Amount, Logs.Total_Cost, Logs.Total_MP, Logs.New_MP_Amt, Logs.New_DP_Amt, Logs.Notes FROM Logs INNER JOIN Reps ON Logs.ForteID = Reps.ForteID WHERE Logs.Date BETWEEN \''.$Start_Date.'\' AND \''.$End_Date.'\' AND Logs.ForteID = \''.$Forte_ID.'\' ORDER BY Logs.RefNumber'; $result = sqlsrv_query($connection,$query); if (!$result) { $message = 'ERROR: ' . sqlsrv_errors(); return $message; } else { echo '<table border="1" cellpadding="5" style="border:3px solid black; text-align: center;"> <tr> <th style="border:3px solid black;">Rep</th> <th style="border:3px solid black;">Reference Number</th> <th style="border:3px solid black;">Call Disposition</th> <th style="border:3px solid black;">Cancel Disposition</th> <th style="border:3px solid black;">Date</th> <th style="border:3px solid black;">AppNumber</th> <th style="border:3px solid black;">Phone Number</th> <th style="border:3px solid black;">Contract Number</th> <th style="border:3px solid black;">Finance Number</th> <th style="border:3px solid black;"># of Payments</th> <th style="border:3px solid black;">ACH/CC</th> <th style="border:3px solid black;">Post Date</th> <th style="border:3px solid black;">Callback</th> <th style="border:3px solid black;">Discount Amount</th> <th style="border:3px solid black;">New Total Cost</th> <th style="border:3px solid black;">Total Monthly Payments</th> <th style="border:3px solid black;">New MP Amount</th> <th style="border:3px solid black;">New DP Amount</th> <th style="border:3px solid black;">Notes</th> </tr>'; while ( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC )) { echo '<tr style="border:3px solid black; text-align: center;">'; echo '<td nowrap>'.$row['Rep'].'</td>'; echo '<td nowrap>'.$row['RefNumber'].'</td>'; echo '<td nowrap>'.$row['Disposition'].'</td>'; echo '<td nowrap>'.$row['Cancel_Disposition'].'</td>'; echo '<td nowrap>'.$row['Date'].'</td>'; echo '<td nowrap>'.$row['appNumber'].'</td>'; echo '<td nowrap>'.$row['Phone_Num'].'</td>'; echo '<td nowrap>'.$row['Con_Number'].'</td>'; echo '<td nowrap>'.$row['Finance_Num'].'</td>'; echo '<td nowrap>'.$row['Num_Payments'].'</td>'; echo '<td nowrap>'.$row['ACH_CC'].'</td>'; echo '<td nowrap>'.$row['Post_Date'].'</td>'; echo '<td nowrap>'.$row['Callback'].'</td>'; echo '<td nowrap>'.$row['Disc_Amount'].'</td>'; echo '<td nowrap>'.$row['Total_Cost'].'</td>'; echo '<td nowrap>'.$row['Total_MP'].'</td>'; echo '<td nowrap>'.$row['New_MP_Amt'].'</td>'; echo '<td nowrap>'.$row['New_DP_Amt'].'</td>'; echo '<td nowrap>'.$row['Notes'].'</td>'; echo '</tr>'; } echo '</table>'; } sqlsrv_free_stmt ($result); sqlsrv_close( $connection); ?> Thanks for the help in advance!

Thanks again!

This is what I have for my logout.php page: <?php session_start(); session_destroy(); header("location: login.php"); exit() ?> Is that good? Or do you recommend something else?

Thanks!!!

I have worked up a login page and everything and works great, as well as on all the other pages set to if not logged in it will redirect to the login page. Code is here: <?php session_start(); if (!(isset($_SESSION['forteid']) && $_SESSION['forteid'] != '')) { header ("Location: login.php"); } ?> Now trying to add in certain pages where only certain access can access the page if not it will redirect back to the home page. This is what I have, but when not logged in and go to a page with this at the top, it just seems to skip over the first if (!isset) check: <?php session_start(); if (!(isset($_SESSION['forteid']) && $_SESSION['forteid'] != '')) { header ("Location: login.php"); } if (!(isset($_SESSION['accesslevel']) && $_SESSION['accesslevel'] != '1')) { header ("Location: noaccess.php"); } ?> I thought if else statement but not sure how to write that. Thanks for the help in advance!

Well I found out what I was doing wrong. Thanks for the help anyways. <?php session_start(); if (!(isset($_SESSION['forteid']) && $_SESSION['forteid'] != '')) { header ("Location: login.php"); } ?> And it works!

Ok, makes sense, would you be able to point me in the direction on how I would do that? Or a bit of the code that I can research?!

So I am new on using the SESSION variables in PHP. I have created a simple login screen. Not trying to be to secure with it, just wanted it to be a simple login so a certain field was filled in and the user cannot change the Rep field. Anyways, I have put this at the top of the page where it goes to when they login in the username and password are correct: <?php session_start(); if(isset($_SESSION['forteid'])) { header("location: index.php"); } else { header("location: login.php"); } ?> When I log in the page comes up with: This webpage has a redirect loop. The webpage at http://cslogs:8081/test/index.php has resulted in too many redirects. I am new with this so not sure how to fix this one or haven't found a way to attempt to fix it. Basically I want it to where they can't just type in http://cslogs:8081/test/index.php in the address bar and get to the screen without logging in, which the login page is just login.php. Thanks in advance.

Thanks for all the help guys and gals. The day I had kept putting in the where clause was the only day that didn't have data. So it really wasn't anything wrong with the code. But you were right in a sense as it was not matching, because there was nothing for that date. I am sorry for the headaches I may have caused!

Well if I just do a query in SSMS this is the format it outputs of a record of the date field: 2012-12-18 I could put in either way in a query in SSMS 2012-12-28 or 12/28/2012 and it still pulls up. The column type is set to (date,null). Also, I have in the $connectionInfo 'ReturnDatesAsStrings'=>true, which I took out but didn't seem to make a difference.

So the font and color tags are not in my code, but keep getting automatically put in there when I save changes to the post. <?php ini_set('error_reporting', E_ALL); /*print_r($_POST);*/ $serverName = 'localhost\\SQLEXPRESS'; $connectionInfo = array('Database'=>'test', 'UID'=>'cslogslogin', 'PWD'=>'123456','ReturnDatesAsStrings'=>true,); $connection = sqlsrv_connect($serverName, $connectionInfo); if( $connection === false ) { echo "Could not connect.\n"; die( print_r( sqlsrv_errors(), true)); } $Start_Date = $_GET['start_date']; $End_Date = $_GET['end_date']; $query = " SELECT Reps.Rep, SUM(Logs.Num_Payments) AS Pmts_Collected FROM Logs INNER JOIN Reps ON Logs.ForteID = Reps.ForteID WHERE Logs.Date BETWEEN '2012-12-28' AND '2012-12-28' GROUP BY Reps.Rep HAVING SUM(Logs.Num_Payments) > 0\n"; $result = sqlsrv_query($connection,$query); /*echo $query;*/ if( $result === false ) { echo "Error in statement execution.\n"; die( print_r( sqlsrv_errors(), true)); } echo "<table border='1px' cellpadding='5' style='border:3px solid black; text-align: center;'>\n"; echo "<tr>\n"; echo "<th style='border:3px solid black;'>Rep</th>\n"; echo "<th style='border:3px solid black;'>Pmts Collected</th>\n"; echo "</tr>\n"; while ( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC )) { echo "<tr>"; echo "<td>{$row['Redp']}</td>"; echo "<td>{$row['Pmts_Collected']}</td>"; echo "</tr>\n"; } echo "</table>\n"; sqlsrv_free_stmt ($result); sqlsrv_close( $connection); ?> Alright I got it to not add in the font and color tags!

@PFMaBiSmAd - I also took out in the query the $Start_Date and $End_Date and just manually put in 2012-12-28 for each and still no results are showing. Here is the code I have as of now. There are a few changes with the SQL error reporting, which I have found on Microsofts website, I just put in as another attempt to see if those would through me any errors, but they have not. Here is the code: Code in another post: Let me know if there are any other ideas or if I should scrap something to start fresh from somewhere. Thanks again and I am sorry if I seemed reluctant.

I went back through all the posts and all the error reporting you and Jessica suggested I have in place and nothing has popped up with an error. If there is something else I am missing in it to show errors, please let me know, but as of this point I have done everything everyone has asked and still no results. @PFMaBiSmAd - for the characters being passed on, which characters would not pass on to the echo $query; but possibly pass on to the actual query that would result in a non-match. Here is my form code from the previous page: <form method="get" action="getcollect.php"> <table> <tr> <td>Start Date:</td> <td><input name="start_date" id="datepicker" type="text" value="<?php echo $today;?>" autocomplete="off" required="required"></td> </tr> <tr> <td>End Date:</td> <td><input name="end_date" id="datepicker2" type="text" value="<?php echo $today;?>" autocomplete="off" required="required"></td> </tr> </table> <br> <input type="submit" name="getcollect" value="Get Report"><br><br> <input type="button" value="Back to Reports" onclick="window.location.href='stats.php';"> <br> <input type="button" value="Individual Reports" onclick="window.location.href='individualstats.php';"> <br><br> </form>

I have put in all the error reporting that everyone has said to and still not receiving any errors. So I am not sure how you say I am reluctant.

My form is setup the same way on all my other pages with the datepicker and all those queries work fine. That is the only thing that is inputted from the form.

That was in SSMS, but I moved the where and group by clauses around and I got it to work. But when I put that in place in my php code, I do not get any results displayed like I do when I run the query in SSMS. I do not think it has anything to do with the SQL query.

I get this error when putting it into a WHERE clause: Msg 147, Level 15, State 1, Line 4 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. EDIT: I changed it around to have the WHERE clause to work, still no results pull up. But when I put in the echo print out of the query from the code into SSMS it pulls results.

SQL throws an error if I do not have it in the GROUP BY since I have it in the HAVING statement. This is the error in SSMS I get when I take the GROUP BY date part out: Msg 8121, Level 16, State 1, Line 5 Column 'Logs.Date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8121, Level 16, State 1, Line 5 Column 'Logs.Date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. I just do not want the date column to show with the results, that is why it is not in the SELECT statement.