Cannot get data from SQL when column name includes a blank

**Chewbacca** · 07-07-2010, 01:31 PM

Hi, I am a total newbie so please have patience with me.

I have managed to get a connection to my SQL 2008 database and even to retrieve some data. The problem shows when the column name in the table contains a blank ( e.g 'Contact person' or 'Phone No_' ). When this is the case, PHP does not output any data, only the column name.

I don't get any error messages or anything.

Now, since the database is our business system I do not want to rename any columns, so I need a way to display data from columns that DO have a blank in teir name.

Any suggestions?

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
		"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
	<title>PHP test</title>
	<meta http-equiv="content-type"
		content="text/html; charset= utf-8/>
</head>
<?php
  $db = mssql_connect('SERVERNAME', 'USER','PASSWORD');
  if (!$db)
 
{
    $output= 'Uppkopplingen gick inte bra.';
   echo $output;
   exit();
}
else
{
    mssql_select_db('[DATABASE]',$db);
    $output= 'Ansluten till Databasen.<br>';
       echo $output;
   //exit();
} 
echo 'Nu är jag ansluten till databasen... Skickar en fråga till SQL-databasen...<br>';
// Send a select query to MSSQL
$query = mssql_query('select top 10 No_, Name, Address, City, "Contact person" as Contact, "Phone No_" as phone from [TABLE]where Market=1');

// Check if there were any records
if (!mssql_num_rows($query)) {
    echo 'Hittade inga poster';
} else 
{
	echo 'Antal funna rader: ' . mssql_num_rows($query) . '<br>';
    // The following is equal to the code below:
    //
    // while ($row = mssql_fetch_row($query)) 


echo '<ul>';

    while ($row = mssql_fetch_assoc($query)) {
        echo '<li>' . $row['No_'] . ' '. $row['Name'] .' '. $row['Address'].' '. $row['City'] .' '. $row['phone'] .' '. $row['Contact'] .'</li>';
		
    }

echo '</ul>';
    
}
// Free the query result
mssql_free_result($query);
?>

The above code results in the following output:

Ansluten till Databasen.
Nu är jag ansluten till databasen... Skickar en fråga till SQL-databasen...
Antal funna rader: 10

* 1234567 TEST TESTSSON Test S�GV�GEN 1 STOCKHOLM Phone No_ Contact person
* 1900000 ANDREAS STIHL NORDEN AB GLIMMERV�GEN 6 STENKULLEN Phone No_ Contact person
* 1900001 MALMBY MOTOR AB MALMBYV�GEN 2 STR�NGN�S Phone No_ Contact person
* 1900002 HEDMANS MOTOR AB NORRV�GEN 39 ARVIDSJAUR Phone No_ Contact person
* 1900003 G�TEBORGS SKOG & TR�DG�RD AB DATAV�GEN 21 A ASKIM Phone No_ Contact person
* 1900004 TINGSRYDS MOTORTJ�NST L�V�NGEN PL 1068 TINGSRYD Phone No_ Contact person
* 1900005 HILDINGS MOTOR INDUSTRIV�GEN 4 YTTERHOGDAL Phone No_ Contact person
* 1900006 KUNGSBACKA J�RN & FRITID AB TEKNIKGATAN 8 KUNGSBACKA Phone No_ Contact person
* 1900007 PSB MASKIN AB NORRBACKAGATAN 23 STOCKHOLM Phone No_ Contact person
* 1900008 S�G & TR�DG�RDSCENTER BOX 111 S�FFLE Phone No_ Contact person

**bluewalrus** · 07-07-2010, 02:19 PM

I think you need to use a single quote ' and you'll need to escape it cause your already using that to open the query so

'select top 10 No_, Name, Address, City, \'Contact person\' as Contact, \'Phone No_\' as phone from [TABLE]where Market=1');

**djr33** · 07-07-2010, 07:19 PM

An even simpler way is to just use:
SELECT * FROM ....

The star means "everything". It's not as efficient, but it saves some work in coding it. That may work, depending on your needs.
That won't work if you are using it in another part of the query, like "WHERE...", so you'll need the method above.

Also, this is a good reason to not use spaces or other special characters in table/column names. It'll save you work later.

**Chewbacca** · 07-09-2010, 12:43 PM

Turned out to be pretty simple. I could actually use the same syntax as I do in TSQL and embrace the column names in square brackets.

Thread: Cannot get data from SQL when column name includes a blank

Thread Tools

Search Thread

Cannot get data from SQL when column name includes a blank

Bookmarks

Bookmarks

Posting Permissions