This course teaches how to install and configure Windows Server 2012 R2. It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).
Echo Multiple values from multiple records
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.
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
LVL 85
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.
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
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
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.
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.
Sorry I was trying to put two images in and was clicking around. Must of accidently removed it.
select-results.PNG
selectsql.PNG
select-results.PNG
selectsql.PNG
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.
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?
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?
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
SELECT
`DATE`,
`CREW`,
`SHIFT`,
`LINE`,
`LINE_NUM`,
`PERSON_1`,
`PERSON_2`,
`PERSON_3`,
`PERSON_4`
FROM
`YourTableNameHere`
ORDER BY
`DATE`,
`SHIFT`
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).
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
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
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
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>
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 dbMS 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]
To your question - you want to use the AND operator in the WHERE clause
SELECT * FROM [CE_MANNING] WHERE [DATE]=? AND [SHIFT]=?
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>
<a href="CE_CREWA_SELECT2.php?ID=<?php echo $result["DATE"]->format("Y-m-d");?>&shift=<?php echo $result["SHIFT"];?>">View/Edit</a>
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)
?>
$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("Database"=>$dbName, "UID"=>$userName, "PWD"=>$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 [TABLLE] 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
?>
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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
http://php.net/manual/en/function.sqlsrv-fetch-array.php
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>
Couple of things that might help.
First, do not use the short open tag. Instead of <? always start your PHP scripts with <?php. It's a long story about why, but just do it and you will save yourself a headache later.
Second, add error_reporting(E_ALL) to the top of your PHP scripts, and correct anything that causes a message. PHP suppresses some messages, including the one that would tell you if your script relied on an undefined variable, so getting better error reporting than the PHP default is very important. This setting may already be in play for some of your scripts, but for my own safety, I explicitly list it in the top of all my PHP scripts.
Third, please, please, please show us the script and the error message together! In the picture of the error message it shows a Notice turning up on line 127, but the code snippet only has 55 lines. So we do not really know what is going on in your script. Life's too short to guess about this stuff. Instead of guessing (or making us guess) please learn about the SSCCE and use its guidance to help clarify your questions. It's amazing how fast we can answer questions when we have all the parts of the puzzle :-)
Fourth, when you have a statement like this sqlsrv_query(), it's important to test the return value and if it's FALSE, it's important to visualize the error information. One less thing to guess about! And please get out of the habit of using die() to print error messages. PHP has trigger_error() to print error messages, and it's the right thing to use no matter how many (bad) PHP examples you've seen on the internet with die()!
First, do not use the short open tag. Instead of <? always start your PHP scripts with <?php. It's a long story about why, but just do it and you will save yourself a headache later.
Second, add error_reporting(E_ALL) to the top of your PHP scripts, and correct anything that causes a message. PHP suppresses some messages, including the one that would tell you if your script relied on an undefined variable, so getting better error reporting than the PHP default is very important. This setting may already be in play for some of your scripts, but for my own safety, I explicitly list it in the top of all my PHP scripts.
Third, please, please, please show us the script and the error message together! In the picture of the error message it shows a Notice turning up on line 127, but the code snippet only has 55 lines. So we do not really know what is going on in your script. Life's too short to guess about this stuff. Instead of guessing (or making us guess) please learn about the SSCCE and use its guidance to help clarify your questions. It's amazing how fast we can answer questions when we have all the parts of the puzzle :-)
Fourth, when you have a statement like this sqlsrv_query(), it's important to test the return value and if it's FALSE, it's important to visualize the error information. One less thing to guess about! And please get out of the habit of using die() to print error messages. PHP has trigger_error() to print error messages, and it's the right thing to use no matter how many (bad) PHP examples you've seen on the internet with die()!
$query = sqlsrv_query( $conn, $stmt, $params);
if (!$query) trigger_error( sqlsrv_errors(), E_USER_WARNING);
// I THINK A COMMA IS OUT OF PLACE HERE
$stmt = "SELECT * FROM [CE_MANNING] WHERE [DATE] = ?, AND [SHIFT]= ? ";
// I THINK THIS MAY BE CLOSER TO WHAT YOU WANT
$stmt = "SELECT * FROM [CE_MANNING] WHERE [DATE] = ? AND [SHIFT]= ? ";
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
3_12_17ex.PNG
The error is saying there is something wrong with your connection code - this was the same code you supplied in your post so I would suggest you get that fixed first. I am assuming you had a working version of this code - so best to start with that and make the changes.
The code I posted only added the following
1. Get the SHIFT parameter from the URL
2. Make provision for the SHIFT parameter in the SELECT by adding an AND SHIFT = ?
3. Adding the SHIFT parameter to the params array.
The rest of the code remains pretty much as you posted it. Without having your specific setup I cannot test the code but in theory it is correct.
I second Ray's comments above - it is no use telling us something does not work - you need to give us all the relevant code and the error messages that go with it.
The code I posted only added the following
1. Get the SHIFT parameter from the URL
2. Make provision for the SHIFT parameter in the SELECT by adding an AND SHIFT = ?
3. Adding the SHIFT parameter to the params array.
The rest of the code remains pretty much as you posted it. Without having your specific setup I cannot test the code but in theory it is correct.
I second Ray's comments above - it is no use telling us something does not work - you need to give us all the relevant code and the error messages that go with it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.