how to Query database fields with spaces

[nyler01]

Please help, Someone there who knows how to create an sql for ms access database and php. The Problem is the fields in the table are written with spaces and I am not gonna change it in any means because it is connected to a very large program and it will take a lot of time to revise all the codes in the entire software so i think i can query this database even if it has a space. Thanks for helping ..

[pritaeas]

In mysql you can quote columns using the backtick character, in mssql using brackets. Possibly access has a similar method, but I have not found it yet.

[nyler01]

i've tried using bactick character but it failed.. i hope someone out there whow knows how to deal with this situation.. knock knock!!! any body out there..

[hielo]

i've tried using bactick character but it failed.

Then you either are NOT using MySQL OR you probably used apostrophes instead of backticks. The backtick (`) is the one on the same key as the ~ character.

[hielo]

again if you are using MS Access:

SELECT [First Name] FROM [Users Table]

but in MySQL:

SELECT `First Name` FROM `Users Table`

[nyler01]

I tried adding [] in the fields and table with space but still didnt work.. i guess the problem is the line with a red font.. i dont know how to extract fields with space.. thanks

$sql="SELECT *
FROM Module INNER JOIN (Trainings INNER JOIN ((Employee INNER JOIN EmployeeClassName ON Employee.ClassID = EmployeeClassName.ClassID) INNER JOIN [Training Details] ON Employee.EmployeeID = [Training Details].[Employee ID]) ON Trainings.[Training ID] = [Training Details].[Training ID]) ON Module.ModuleID = Trainings.[Module ID] where [Full Name]='$username' order by date desc";

echo "<center><table border='1'>";
echo "<th>Module Name</th>";
echo"<th>Date Taken</th>";

$query=odbc_exec($conn,$sql);
$tnum=0;
while (odbc_fetch_row($query)){
$tnum++;
$trainings=odbc_result($query,"[Module Name]");
$date=odbc_result($query,"Date");
$class=odbc_result($query,"EmployeeClassName");
echo"<tr><td>$trainings</td>";

echo"<td>$date</td></tr>";
}

echo "</table></center>";

[hielo]

you need the brackets when formulating the SQL command only. Once the query executes successfully, the BRACKETS are NOT needed. Try: $trainings=odbc_result($query,"Module Name"); leave you SQL as is (WITH the brackets).

[nyler01]

i removed the brackets but still not successful.. what went wrong.. huhuhu. i am really depressed here..

[hielo]

OK, then other alternatives are to ACTUALLY list the fields that you are trying to select. Make sure you read ALL this post BEFORE you attempt the suggested changes:

Instead of SELECT * FROM... use:

/*
NOTE: Date is typically a built-in function OR a keyword in DBs, so you need to use the [] around it so that it is acknowledged as YOUR field name not as the built-in function/keyword.

On the query below, I don't know if 'Date' (uppercase "D" in SELECT) and 'date' (lowercase "d" in ORDER BY) are the same. If yes, try using the exact letter case (uppercase or uppercase) you have in your Table definition, AND Prefix them with the tableName on which they are defined. My guess was that they are in Module table, but if not, then provide the correct table name
*/
$sql="SELECT [Module].[Module Name] as [moduleName], [Module].[Date], [Employee].[EmployeeClassName]
FROM Module INNER JOIN (Trainings INNER JOIN ((Employee INNER JOIN EmployeeClassName ON Employee.ClassID = EmployeeClassName.ClassID) INNER JOIN [Training Details] ON Employee.EmployeeID = [Training Details].[Employee ID]) ON Trainings.[Training ID] = [Training Details].[Training ID]) ON Module.ModuleID = Trainings.[Module ID] where [Full Name]='$username' order by [Module].[Date] desc";

With the changes above, you should be able to use either:

$trainings=odbc_result($query,"moduleName");

OR

/*
http://php.net/manual/en/function.odbc-result.php
[I]field:[/I]
The field name being retrieved. It can either be an integer containing the column number of the field you want; or it can be a string containing the name of the field. 
[I]Notes:[/I]
Field indices start from 1...
*/
$trainings=odbc_result($query,1);

[nyler01]

is there any other method available? i've tried your code hielo but still unsuccessful in getting result. there is no either output.. what other method would you recommend?

[hielo]

Verify that the query is executing successfully. Instead of $query=odbc_exec($conn,$sql); try $query=odbc_exec($conn,$sql) or die( odbc_errormsg($conn) );

[nyler01]

The code returns only the table header "Module name" and Date "Taken"...
NOthing has changed..

[nyler01]

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '\\Hicapdata\operation_management\Six Sigma Database Server\6 sigma database working prototype_be.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

it is the return of the script..