We help IT Professionals succeed at work.

Echo Multiple values from multiple records

peter Ojeda
peter Ojeda asked
on
130 Views
Last Modified: 2017-03-13
Hi experts. I have a table in my sql server db with columns "DATE",  "SHIFT", "AREA","PERSON1", "PERSON2", and "PERSON3". On my form page I have a table that performs multiple inserts. DATE and SHIFT are entered in all records and are the same value for each submission. So 3-10-17, MORNING, FRONT, PETER, DAN, JULIE will be the first record submitted and 3-10-17, MORNING, BACK, LUKE, JIM, KOBE  would be the second submission. My main issue here is now I am doing a select statement to display these values in a php table but since the columns are the same if I DATE and SHIFT I can only show echo one record. I know this sounds confusing so refer to the image below and you will understand.
Comment
Watch Question

Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
No image.  In addition, you may need to check if '3-10-17' is a valid DATE format.  If it is not, then you can't sort or ORDER BY that column properly.  You also may want to have an index 'auto-increment' column so that you will have a unique ID for each row that you can use for UPDATE statements.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
3-10-17 is ambiguous.  Do you want March 10 or October 3?  The disambiguation rules may not be your friend here, so I would recommend not using this as a date string.  You need to use the ISO-8601 format strings for all internal representations of date/time values.

Give yourself a little time with these articles -- they will show you exactly how to deal with a multitude of date/time questions!

Procedural
https://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL-Procedural-Version.html

Object-oriented
https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Here https://msdn.microsoft.com/en-us/library/ms186724.aspx is the T-SQL Date format info for MS SQL Server.  It shows similar requirements.  If you don't use one of the standard Date formats in a DATE column, then it probably won't INSERT and you can't use it in DATE arithmetic.
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
In addition, using DATE as a column name can potentially cause problems because it is used by the server for other things.  Here are the reserved words for T-SQL and ODBC.  If I am going to use something like DATE in a column, I always modify it to something like DATEW so it does not match any reserved words.

Author

Commented:
Sorry I was trying to put two images in and was clicking around. Must of accidently removed it.
select-results.PNG
selectsql.PNG

Author

Commented:
Ok thankyou all for your comments. I have been using the y-m-d format for a while now with no problem as Ray pointed out I understand how it can become one.
CERTIFIED EXPERT

Commented:
Can you please explain what is the output you are trying to get?

You have two records in your sample here with' 3-10-17'(database date format is yyyy-mm-dd) and MORNING.

Do you have problem with your INSERTS or SELECT that shows only one record?

Author

Commented:
The select statement is where I am having an issue. I was just trying to display how it is in sql vs how it displays in my php page. I want it to look as it is in SQL. So based on date and Shift it will display all the results in a table format
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
SELECT 
  `DATE`, 
  `CREW`, 
  `SHIFT`,
  `LINE`,
  `LINE_NUM`,
  `PERSON_1`,
  `PERSON_2`,
  `PERSON_3`,
  `PERSON_4`
FROM
   `YourTableNameHere`
ORDER BY
  `DATE`, 
  `SHIFT`

Open in new window

Note use of ` (Backticks) to get around conflict with DB reserved words. This is not endorsing the use of reserved words but demonstrate you can use them without confusing the DB.

The above query will give you the results of the SQL table in the second image in the right order. You can then simply loop through the result set and output the values.

I am not sure if I have understood your requirements properly but this is my best guess (based on information provided).
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
I don't believe that MS SQL Server supports the use of backticks.

This page https://msdn.microsoft.com/en-us/library/ms189822.aspx says...
"Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, you can do this only by using delimited identifiers."

Here is a confusing page about 'delimiters':
https://technet.microsoft.com/en-us/library/ms176027(v=sql.105).aspx

Author

Commented:
Thank you yes I was able to perform this with a while loop and without the back ticks. Any recommendations on how to pass two results into my select statement? I am able to pass the date into there (which I am going to change from just date) but I also want to pass the shift into there.
On my PHP form I have this
 <a href="CE_CREWA_SELECT2.php?ID=<?php echo $result["DATE"] ->format("Y-m-d");?>">View/Edit</a>

Open in new window

which passes the date to the query, and the query it is $stmt = "SELECT * FROM CE_MANNING WHERE DATE = ?  ";. This works but I want to pass date as well as SHIFT.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
I don't believe that MS SQL Server supports the use of backticks.
I was going on this (as it was not clear on TA's selected)
I have a table in my sql server db
MS SQL uses [ ] so
SELECT 
  [DATE], 
  [CREW], 
  [SHIFT],
  [LINE],
  [LINE_NUM],
  [PERSON_1],
  [PERSON_2],
  [PERSON_3],
  [PERSON_4]
FROM
   [CE_MANNING]
ORDER BY
  [DATE], 
  [SHIFT]

Open in new window


To your question - you want to use the AND operator in the WHERE clause
SELECT * FROM [CE_MANNING] WHERE [DATE]=? AND [SHIFT]=?

Open in new window


Given your link you are passing this to another script
<a href="CE_CREWA_SELECT2.php?ID=<?php echo $result["DATE"] ->format("Y-m-d");?>">View/Edit</a>

Open in new window

To say how the script changes we need to see it but to pass the additional parameter
<a href="CE_CREWA_SELECT2.php?ID=<?php echo $result["DATE"]->format("Y-m-d");?>&amp;shift=<?php echo $result["SHIFT"];?>">View/Edit</a>

Open in new window

Author

Commented:
The code below is the php script I am using to do the select statement that I am attempting to pass the date and Shift into. I am able to pass just the date with the code previously shown and if I remove Shift from the select query. I am not sure if I am messing up the select query or the passing of the variable
  $strID = null;

   if(isset($_GET["ID"]))
   {
	   $strID = $_GET["ID"];
   }
  
   $connectionInfo = array("Database"=>$dbName, "UID"=>$userName, "PWD"=>$userPassword, "MultipleActiveResultSets"=>true);

   $conn = sqlsrv_connect( $serverName, $connectionInfo);

   if( $conn === false ) {
      die( print_r( sqlsrv_errors(), true));
   }

	$stmt = "SELECT * FROM TABLLE WHERE DATE = ?, AND SHIFT= ? ";
	$params = array($strID);

	$query = sqlsrv_query( $conn, $stmt, $params);

	$result = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)

?>

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Maybe I'm lost but 'sqlsrv_fetch_array' only returns one row at a time.  I only see you calling it once.  I though you wanted multiple rows.

http://php.net/manual/en/function.sqlsrv-fetch-array.php

Author

Commented:
Hi Julien I attempted to do it the way you just said but I'm still getting an error message and then some. View below what is happening now. I have no clue what I am doing wrong.
<?
$strID = null;
// DEFAULT RETURN
$result = array();

// GET PARAMS SAFELY
$strID = isset($_GET["ID"]) ? $_GET["ID"] : false;
$strSHIFT = isset($_GET["SHIFT"]) ? $_GET["SHIFT"] : false;
// ONLY PROCEED IF WE GOT VALID PARAMETERS
if ($strID && $strSHIFT) {
  $connectionInfo = array(""=>$dbName, ""=>$userName, ""=>$userPassword, "MultipleActiveResultSets"=>true);
  $conn = sqlsrv_connect( $serverName, $connectionInfo);
  
  if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
  }

  // SET UP QUERY WITH PARAMETERS
  $stmt = "SELECT * FROM [CE_MANNING] WHERE [DATE] = ?, AND [SHIFT]= ? ";
  
  // ADD PARAMETER VALUES
  $params = array($strID, $strSHIFT);
  
  $query = sqlsrv_query( $conn, $stmt, $params);
  
  $result = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC);
}
// RETURN DATA
?>
<table width="500" border="1" id="myTable">
  <tr>
    <th width="91"> <div align="center">ID</div></th>
    <th width="91"> <div align="center">Date</div></th>
  </tr>
<?php
while($result = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC))
{
?>
  <tr>
	<td align="center" width="120px" nowrap> <div align="center"><?php echo $result["DATE"] ;?></div></td>
	<td align="center" width="120px" nowrap> <?php echo $result["SHIFT"];?></td>
  </tr>
<?php
}
?>
</table>
<br>
<br>
<?php
sqlsrv_close($conn);
?>
</div>
</article>
</body>
</html>

Open in new window

example-3_12_17.PNG
Most Valuable Expert 2011
Author of the Year 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Ray, thankyou for all your input. Always thought the error messages did not help too much but I will definitely try that. And I removed most of the css just to save space. Added the php trigger error(which I am very thankful for showing me) and the error message below is now displayed.
3_12_17ex.PNG
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions