Link to home
Create AccountLog in
Avatar of Nico2011
Nico2011Flag for United Kingdom of Great Britain and Northern Ireland

asked on

MSSQL DB field data using PHP

Hello (Ray?),

The code below works great, but I need to create an array variable using the field ID, which then needs to be inserted into a JSON-RPC feed, so I can re-query the server.  At the moment, I receive the data correctly, but it's pre-empted by the field name etc.

The result I need in a variable is '1, 2, 3, 4, 5' etc.

Thanks in advance.

<?php // RAY_temp_nico2011.php
error_reporting(E_ALL);
echo '<pre>';


// YOU MUST PROVIDE A VALUE FOR THIS VARIABLE $tablename !
$tablename = "villas";
$sql = "SELECT ID FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$tablename'";


// MAN PAGE: http://php.net/manual/en/function.mssql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mssql-get-last-message.php
$query = mssql_connect( 'Server_IP', 'Username', 'PW' ) or die ( 'Can not connect to server: ' . mssql_get_last_message() );

// MAN PAGE: http://php.net/manual/en/function.mssql-select-db.php
mssql_select_db( 'nicoco_villas_test', $query ) or die ( 'Can not select database: ' . mssql_get_last_message() );

// MAN PAGE: http://php.net/manual/en/function.mssql-query.php
$res = mssql_query("Select * from villas", $query);
if ($res === TRUE)
{
    echo 'NO ROWS';
}
elseif ($res === FALSE)
{
    echo "FAIL: $sql <br/>";
    die( mssql_get_last_message() );
}

// MAN PAGE: http://php.net/manual/en/function.mssql-fetch-assoc.php
while ($row = mssql_fetch_assoc($res))
{
    var_dump($row);
} 

Open in new window

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Go to line 13 and lose the variable assignment.  $query is a confusing name.  Then go to line 19 and remove $query from the query.

Then post the CREATE TABLE script for the villas table so we can see what the data looks like.  Once we can see the data, the code usually writes itself.
Avatar of Nico2011

ASKER

Thanks - not sure how to post the CREATE TABLE script, but I've managed to do this: http://villasdirect.com/LG/db_connect1.php which shows the table structure.

I've replaced $query with $rs - line 16 referred to $query too, so I replaced that also.

Thanks again.
Great - that's perfect.  You do not actually need the $rs variable.  It's not necessary to assign it in the connect statement and it's not required in the query call, either.  As you do more programming you will find that variable proliferation is not your friend :-)

Usually the CREATE TABLE statement can be gotten with a query like SHOW CREATE TABLE.
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you both - thanks for testing it for me too Dave.