Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to set array from ms sql -> php

Posted on 2014-01-03
17
Medium Priority
?
413 Views
Last Modified: 2014-01-06
Hi!

Have this code:

<?php
error_reporting(E_ALL);
ini_set("display_errors", 1);

require_once('Connect_databse.php');

$table1 = 'SHOW_UserOrganisation';

$orgnavn = $_POST["organisasjon"];

// change the query so that the column names match your subcategory table
$query="select distinct(Name) from {$table1} WHERE OrganisationName='{$orgnavn}'";
$result = sqlsrv_query( $conn,$query); 
?>
<?php

while($row3 = sqlsrv_fetch_array($result)):
   $dropdown1 .= '<option value="'.$row3['Name'].'">'.$row3['Name'].'</option>';
endwhile;


$dropdown2 = <<<EOF
	<option>DROPP 5</option>
	<option>DROPP 6</option>
	<option>DROPP 7</option>
	<option>DROPP 8</option>
EOF;

$dropdown3 = <<<EOF
	TEST
EOF;

$response = array(
	'text1' => $dropdown1,
	'text2' => $dropdown2,
	'text3' => $dropdown3,
);

echo json_encode($response);

?>

Open in new window



But the return array from this code:
while($row3 = sqlsrv_fetch_array($result)):
   $dropdown1 .= '<option value="'.$row3['Name'].'">'.$row3['Name'].'</option>';
endwhile;

Is emty ????

What do i do wrong here, how to fix this ?
0
Comment
Question by:team2005
  • 8
  • 4
  • 2
  • +2
17 Comments
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 39753875
Try this:

// change the query so that the column names match your subcategory table
$query="select distinct(Name) from {$table1} WHERE OrganisationName='{$orgnavn}'";
$result = sqlsrv_query( $conn,$query); 
if ($result === false) {
    die( print_r( sqlsrv_errors(), true) );
}
while($row3 = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)):
   $dropdown1 .= '<option value="'.$row3['Name'].'">'.$row3['Name'].'</option>';
endwhile;

Open in new window

0
 
LVL 2

Author Comment

by:team2005
ID: 39753884
Hi!

Same result, emty array ?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39753897
Let's apply some debugging principles.  Please use var_dump($result) after line 13.  Also remove line 14 and 15, since they are just noise.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:Marco Gasi
ID: 39753900
Well, try to check if the query is the expected one:

$query="select distinct(Name) from {$table1} WHERE OrganisationName='{$orgnavn}'";
echo $query."<br>";

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39753947
Here's where I would start debugging.  You can see each of the important "moving parts" with var_dump().  This will probably tell you what went wrong.

Questions to ask:

1. Did the query string look right?
2. Did the query run correctly?
3. Did the query find any data?
4. Did $response correctly encode into a JSON string?

<?php // RAY_temp_team2005.php
error_reporting(E_ALL);
ini_set("display_errors", 1);

require_once('Connect_databse.php');

$table1 = 'SHOW_UserOrganisation';

// SHOW WHAT THE POST REQUEST CONTAINS
var_dump($_POST);

// THIS VARIABLE SHOULD BE SANITY-CHECKED!
$orgnavn = $_POST["organisasjon"];

// CONSTRUCT A QUERY USING TWO VARIABLES
$query="select distinct(Name) from {$table1} WHERE OrganisationName='{$orgnavn}'";

// SHOW THE QUERY
var_dump($query);

// RUN THE QUERY
$result = sqlsrv_query($conn,$query); 

// SHOW THE RESULT
var_dump($result);

while($row3 = sqlsrv_fetch_array($result))
{
    // SHOW EACH ROW OF THE RESULT SET
    var_dump($row3);
    $dropdown1 .= '<option value="'.$row3['Name'].'">'.$row3['Name'].'</option>';
}


$dropdown2 = <<<EOF
	<option>DROPP 5</option>
	<option>DROPP 6</option>
	<option>DROPP 7</option>
	<option>DROPP 8</option>
EOF;

$dropdown3 = <<<EOF
	TEST
EOF;

$response = array(
	'text1' => $dropdown1,
	'text2' => $dropdown2,
	'text3' => $dropdown3,
);

// CREATE THE JSON STRING
$out = json_encode($response);

// SHOW THE JSON STRING
var_dump($out);
echo $out;

Open in new window

0
 
LVL 34

Expert Comment

by:Slick812
ID: 39754523
greetings  team2005, ,  You have this line o code -
$dropdown1 .= '<option value="'.$row3['Name'].'">'.$row3['Name'].'</option>';

Which is suppose to ADD to the end of the String $dropdown1 , , HOWEVER, there is NO STRING $dropdown1, if you use the add to string operator   .=     it is NOT the same as declaring a variable with  =   , it DOES NOT CREATE A VARIABLE.

so you might try this code -

$dropdown1 = '';
while($row3 = sqlsrv_fetch_array($result)):
   $dropdown1 .= '<option value="'.$row3['Name'].'">'.$row3['Name'].'</option>';
endwhile;
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39755055
@Slick812: Try this script.  You will see that the .= concatenation operator will define the variable if it does not exists.  If Notice level messages are suppressed (the default configuration of PHP), this behavior will occur silently.

<?php // RAY_temp_slick812.php
error_reporting(E_ALL ^ E_NOTICE);

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/HTML/Q_28329877.html#a39754523

var_dump($x);
$x .= "There was nothing before, but now data has appeared";
echo $x;

Open in new window

0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39755070
Since I always use error_reporting(E_ALL); I always hear about it.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39755105
+1 for the right error_reporting() level!
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39755262
Why would I want to leave unreported errors in my code?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39755304
... leave unreported errors in my code?
What a great place to start a sidebar discussion!  PHP was inaugurated with the design that it would be a language "so easy that even my grandmother could use it."  And in the context of that objective we made a lot of design flaws into "features" of the language.  The standard settings for Error_Reporting() are, in retrospect, one of the flaws.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39755321
Also, consider JavaScript where the plus sign is an overloaded operator meaning either addition or concatenation, depending on the context.  This violates the commutative law.  How the eff did that get into the JavaScript language?  My only explanation is that we are human and fall short of the vision of God.
0
 
LVL 2

Author Comment

by:team2005
ID: 39756185
Hi!

This is what it output to screen:

array(0) {
}

Notice: Undefined index: organisasjon in C:\DWASFiles\Sites\AgressionTestMysql\VirtualDirectory0\site\wwwroot\mssql\Reports\selektbrukere.php on line 13
string(74) "select distinct(Name) from SHOW_UserOrganisation WHERE OrganisationName=''"
resource(2) of type (SQL Server Statement)

Notice: Undefined variable: dropdown1 in C:\DWASFiles\Sites\AgressionTestMysql\VirtualDirectory0\site\wwwroot\mssql\Reports\selektbrukere.php on line 47
string(162) "{"text1":null,"text2":"\t<option>DROPP 5<\/option>\r\n\t<option>DROPP 6<\/option>\r\n\t<option>DROPP 7<\/option>\r\n\t<option>DROPP 8<\/option>","text3":"\tTEST"}"
{"text1":null,"text2":"\t<option>DROPP 5<\/option>\r\n\t<option>DROPP 6<\/option>\r\n\t<option>DROPP 7<\/option>\r\n\t<option>DROPP 8<\/option>","text3":"\tTEST"}

First error is to use GET insted of POST
Then i get this output:

array(1) { ["organisasjon"]=> string(7) "Softkey" } string(81) "select distinct(Name) from SHOW_UserOrganisation WHERE OrganisationName='Softkey'" resource(2) of type (SQL Server Statement) array(2) { [0]=> string(18) "Christian Svendsen" ["Name"]=> string(18) "Christian Svendsen" } Notice: Undefined variable: dropdown1 in C:\DWASFiles\Sites\AgressionTestMysql\VirtualDirectory0\site\wwwroot\mssql\Reports\selektbrukere.php on line 31 array(2) { [0]=> string(13) "Tor Erik Berg" ["Name"]=> string(13) "Tor Erik Berg" } string(280) "{"text1":"


But how to fix :
Notice: Undefined variable: dropdown1 ?
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39756557
Try this (note line 27)
<?php // RAY_temp_team2005.php
error_reporting(E_ALL);
ini_set("display_errors", 1);

require_once('Connect_databse.php');

$table1 = 'SHOW_UserOrganisation';

// ACTIVATE THIS TO SHOW WHAT THE POST REQUEST CONTAINS
// var_dump($_POST);

// THIS VARIABLE SHOULD BE SANITY-CHECKED!
$orgnavn = $_POST["organisasjon"];

// CONSTRUCT A QUERY USING TWO VARIABLES
$query="select distinct(Name) from {$table1} WHERE OrganisationName='{$orgnavn}'";

// ACTIVATE THIS TO SHOW THE QUERY
// var_dump($query);

// RUN THE QUERY
$result = sqlsrv_query($conn,$query); 

// ACTIVATE THIS TO SHOW THE RESULT
// var_dump($result);

$dropdown1 = NULL;
while($row3 = sqlsrv_fetch_array($result))
{
    // ACTIVATE THIS TO SHOW EACH ROW OF THE RESULT SET
    // var_dump($row3);
    $dropdown1 .= '<option value="'.$row3['Name'].'">'.$row3['Name'].'</option>';
}


$dropdown2 = <<<EOF
	<option>DROPP 5</option>
	<option>DROPP 6</option>
	<option>DROPP 7</option>
	<option>DROPP 8</option>
EOF;

$dropdown3 = <<<EOF
	TEST
EOF;

$response = array(
	'text1' => $dropdown1,
	'text2' => $dropdown2,
	'text3' => $dropdown3,
);

// CREATE THE JSON STRING
$out = json_encode($response);

// RETURN THE JSON STRING
echo $out;

Open in new window

In order to be able to write German poetry, you would need to have some command of German language -- vocabulary, grammar, etc.  The same is true for man-made languages.  You need to get a bit of a foundation in PHP if you're going to be successful writing PHP scripts.  To that end, you might want to look at this article.  It will lead you in the right direction of good learning resources and more importantly, it will help you avoid the many bad examples of PHP code that litter the internet!  This will not make you a pro, but it will help you get started in that direction.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

Best regards, ~Ray
0
 
LVL 2

Author Comment

by:team2005
ID: 39757875
Thanks Ray...

That works fine :)
Thanks for big help...

But one thing...

Have this code, that are now working fine.

<script type="text/javascript">

$(document).ready(function() {
$('#organisasjon').change(function() {
  $.ajax({
    url : 'selektbrukere.php',
    type : 'POST',
    dataType: 'json',
    data : { organisasjon : $(this).val() },
    success : function (data) {
      $("#brukere").html(data.text1);
      $("#kontroller").html(data.text2);
    }

  });
});
});

</script>

Open in new window


I want it to call selektbrukere.php when the page is called first time (default)
0
 
LVL 2

Author Closing Comment

by:team2005
ID: 39757879
Thanks
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39759297
Thanks for the points.  I missed the follow-on question here.  If it shows up in one of my EE Zones I'll see if I can help. ~Ray
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month20 days, 18 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question