Php Code Not Pulling Info From Sql Server

[ncurran217]

<h1>View Log</h1>
<?php
 $timezone = "America/Chicago";
 date_default_timezone_set($timezone);
 $today = date("Y-m-d");
?>
<?php
$serverName = 'Server\SQLEXPRESS';
$connectionInfo = array('Database'=>'database', 'UID'=>'username', 'PWD'=>'password','ReturnDatesAsStrings'=>true,);
$connection = sqlsrv_connect($serverName, $connectionInfo);


$query = ' SELECT ForteID
  FROM database.dbo.Reps 
  ORDER By ForteID';
$result = sqlsrv_query($connection,$query);


// Move the data to a simple array to simplify presentation code.
$resultAsArray = array(); 
while ( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC )) {
   $resultAsArray = $row;
}
?>


<form method="get" action="getlog.php">
<select>
<?php foreach ($resultAsArray as $row): ?>
   <option value="<?php= $row['ForteID'] ?>"><?php= $row['ForteID'] ?></option>
<?php endforeach; ?>
</select>
<BR>
<table>
<tr>
 <td>Start Date:</td>
 <td><input name="start_date" type="date" value="<?php echo $today;?>" autocomplete="off" required="required"></td>
</tr>
<tr>
 <td>End Date:</td>
 <td><input name="end_date" type="date" value="<?php echo $today;?>" autocomplete="off" required="required"></td>
</tr>
</table>
<br>
<input type="submit" name="getLog" value="Get Log"><br><br>
<input type="button" value="Back to Form" onclick="window.location.href='index.php';"> <br>
</form>
</html>

 

My result for the drop down box is nothing. I believe something is wrong with the

 

while ( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC ))

 

Thanks in advance for the help.

[Jessica]

You need to check for errors. See the link in my signature - it contains mysqli information but the same concept applies with any DB. Check the PHP manual for the functions you'll need. 

[ncurran217]

Well, there seem to be no errors. The Values seem to be pulling from the Query, but just not displaying. The drop down box has options just all blank. If I take out the second

<?php= $row[ForteID] ?>

and put in Select or anything else, it just has a list of Select as all the options.

[TOA]

I see two things:

 

1. I've never seen the short tags <?php=

2. Name your select

[ncurran217]

  <select id="test" name="test">
  <?php foreach ($resultAsArray as $row): ?>
  <option name="ForteID" id="ForteID" value="<?php $row['ForteID'] ?>"><?php $row['ForteID'] ?></option>
  <?php endforeach; ?>
  </select>

 

I have this now and now it has nothing in the select box, doesn't even drop down.

[TOA]

You need to echo $row['ForteID']

 

<?php echo $row['ForteID']; ?>

Edited December 19, 2012 by TOA

[ncurran217]

Did that and still nothing shows up in the drop down box. Do you want me to repost my code as changes have been made?

[jazzman1]

What kind of DB are you using, MySQL or MSSQL ?

 

EDIT: Check this out -> http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/1389b199-4cdd-4e98-8363-ea5b819954c4

Edited December 19, 2012 by jazzman1

[ncurran217]

MSSQL

[jazzman1]

I cannot help you, sorry about that.

[TOA]

Did that and still nothing shows up in the drop down box. Do you want me to repost my code as changes have been made?

 

Yes

 

What kind of DB are you using, MySQL or MSSQL ?

 

I'm not sure that matters, unless his query is failing and someone has to tweak his query.

 

OP..we can find that out by changing the following lines

$result = sqlsrv_query($connection,$query);
if(!$result) {
die('We have no result so everything after will fail');
}

 

Also, you should have error reporting turned on.

 

Let us know how you get on.

[ncurran217]

I do not get anything with putting that if statement in. I am pretty sure the query is fine. I think it is something with this part of the code:

 

$resultAsArray = array(); 
while ( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC )) {
       $resultAsArray = $row;
}
?>
<select>
<?php foreach ($resultAsArray as $row): ?>
       <option value="<?php= $row['ForteID'] ?>"><?php= $row['ForteID'] ?></option>
<?php endforeach; ?>
</select>

 

I think there is supposed to be more, doesn't seem like it is taking the query results into effect.

[TOA]

That if was to check if you get a result or not. If it doesn't die, you get a result and you're correct, it's in there.

 

I'm going to say again, echo $row and name your select.

[ncurran217]

Sorry I copied the information up top, here it is with all the changes everyone suggested.:

 

$result = sqlsrv_query($connection,$query);
if(!$result) {
die('We have no result so everything after will fail');
}
// Move the data to a simple array to simplify presentation code.
$resultAsArray = array();
while ( $row = sqlsrv_fetch( $result, SQLSRV_FETCH_ASSOC )) {
$resultAsArray []= $row;
}
/*echo $resultAsArray;*/
?>
<form method="get" action="getlog.php">
<table width="250" border="0">
<tr>
<td>Forte ID:</td>
<td>
<select name="test" id="test">
<?php foreach ($resultAsArray as $row): ?>
<option name="Forte_ID" id="Forte_ID" value="<?php echo $row[ForteID];?>"><?php echo $row[ForteID]; ?></option>
<?php endforeach; ?>
</select>

 

It doesn't die, but not results either in the drop down box. I also took the [ForteID] out of both spots and it still doesnt show any results.

Edited December 19, 2012 by ncurran217

[TOA]

There's not much of a difference here, but try this and let me know what happens. I'm not sure if this is the problem or not, but we have to start somewhere

 

$result = sqlsrv_query($connection,$query);
if(!$result) {
die('We have no result so everything after will fail');
}
// Move the data to a simple array to simplify presentation code.
$resultAsArray = array();
while ( $row = sqlsrv_fetch( $result, SQLSRV_FETCH_ASSOC )) {
$resultAsArray []= $row;
}
/*echo $resultAsArray;*/
?>
<form method="get" action="getlog.php">
<table width="250" border="0">
<tr>
<td>Forte ID:</td>
<td>
<select name="test" id="test">
<?php foreach ($resultAsArray as $row): ?>
<option value="<?php echo {$row['ForteID']};?>"><?php echo $row['ForteID']; ?></option>
<?php endforeach; ?>
</select>

 

PS options don't need a name, and an ID needs to be unique, so I took out the id too.

Edited December 19, 2012 by TOA

[ncurran217]

Parse error: syntax error, unexpected '{' in C:\wamp\www\cslogs\test.php on line 41

 

Is what I get, which is this part of my file:

 

<option name="Forte_ID" id="Forte_ID" value="<?php echo {$row['ForteID']};?>"><?php echo $row['ForteID']; ?></option>

 

Doesn't like the {}

Edited December 19, 2012 by ncurran217

[TOA]

OK, revert it back then; I was trying to see if maybe php wasn't parsing that array value.

 

The code if (!$result) just makes sure your connection failed or not, not that you have valid results. Add this right before your form and verify you have results, not just a non-false value

echo "<pre>";
print_r($resultAsArray);
echo "</pre>";

Edited December 19, 2012 by TOA

[ncurran217]

I put it in and I get this:

 

 

Array

(

)

[TOA]

So $resultAsArray is empty. That means you got no results from your query. Make sure your SQL or whatever is correct.

[ncurran217]

Yes $resultAsArray is empty, because it starts out empty. I put my query in to SQL and it pulled my list. My connection to the Database I know works, because I have the same thing for another page and it works fine. But getting this to put into a drop down box does not work. I had something else, but it only worked in Chrome but not IE and that was because I had a table nested into the select, which I have found out is wrong. So someone from another forum suggested this route for the select, but was psuedocode and I had to match up what he had in psuedocode to MSSQL code.

[ncurran217]

I am up to suggestions on another route to get this to work as well.

[TOA]

Yes $resultAsArray is empty, because it starts out empty. I put my query in to SQL and it pulled my list. My connection to the Database I know works, because I have the same thing for another page and it works fine. But getting this to put into a drop down box does not work. I had something else, but it only worked in Chrome but not IE and that was because I had a table nested into the select, which I have found out is wrong. So someone from another forum suggested this route for the select, but was psuedocode and I had to match up what he had in psuedocode to MSSQL code.

 

Right, so you're while statement is doing nothing. It has no relevant data is what I mean. It never puts anything into $resultAsArray so the problem is there.

[ncurran217]

So do I have this part wrong?

 

while ( $row = sqlsrv_fetch( $result, SQLSRV_FETCH_ASSOC )) {
$resultAsArray = $row;
}

[TOA]

From what I know of mssql, it looks fine, but I have to admit that knowledge is limited. But, we already determined it wasn't the connection or the db, it's the results using this (and btw this tests the connection, not the results)

 

if (!$result) {
die('We have no result so everything after will fail');
}

 

When you loop through your result set here

 

$resultAsArray = array();
while ( $row = sqlsrv_fetch( $result, SQLSRV_FETCH_ASSOC )) {
$resultAsArray []= $row;
}
print_r($resultAsArray);

 

we proved the results are empty with the print_r() statement.

 

So that tells us that the problem is before that, and since it's not with the connection, there's only the query left as far as I know. So that must be failing.

 

Sorry, that's about all I can help with.

[ncurran217]

Thank you for your help so much! I am learning as well so you have been more than helpful!