MYSQL Issue: Spaces in field names

Background: Our company purchases data from an outside source as part of our support agreement we have to retain the structure of the database. This has caused some issues but nothing I couldnt resolve until now.

Problem: Some of our mysql fields have spaces in them.

Example SQL: 'SELECT
`Divisions`.`Division ID`,
`Divisions`.`Manufacturer ID`,
`Divisions`.`Division Name`
FROM
`Divisions`'

As you can see each field name has a space, TBS doesnt seem to pick up the name of the field and errors out.

ERROR:
TinyButStrong Error in field [blk1.Dealer...] : item 'Dealer' is not an existing key in the array. This message can be cancelled using parameter 'noerr'.

So my question; is there a way for TBS to see the entire field name? I tried `, ' and " TBS assumes that each symbol is part of the field name.

Thanks in advance
-J-

PS i did search the forums several times before posting, apperently I'm the first to discover this issue =(

Hi,

The space char is a separator for TBS tag parameters. So you have to use Alias in your queries in order to have columns with no spaced names.
Example :
  SELECT `Division ID` AS Division_ID FROM Divisions

However, TBS 3.2.0 is already available in RC version, and I can make the final version to support columns with spaced names.

Alias worked just fine! It would be awesome to have the spaces in the new release, however as mentioned alias worked perfectly, thanks for an awesome product and perfect support.

-J-

I seem to have another related issue when using this sql:

SELECT
`Divisions`.`Division Name` AS Division_Name
FROM
`Divisions`
WHERE
`Divisions`.`Division ID` =  '7'

I get a blank page, the seven will be a $var but for testing I used an actual value. with out the where clause it works perfectly.  I tried adding the division id as a reference and using it in my where clause apparently thats not supported my MYSQL(v5).

The HTTPD error log returns the error:
PHP Parse error:  parse error, unexpected T_LNUMBER in /var/www/html/research/research.php on line 25

Line 25 is: `Divisions`.`Division ID` =  '7'

Thank you in advance

-J-

Hi,

It seems that is a pure PHP problem.
It is related to the line that build your SQL statement.

I fiigured it out, its not really a php issue at least as far as I can tell.

I guess its a habit issue I've always used single quotes around my variables in the sql statement, turns out that TBS (I'm assuming) doesn't like it as PHP hasn't errored out on it before.

Doesnt Work:
SELECT
`Divisions`.`Division Name` AS Division_Name
FROM
`Divisions`
WHERE
`Divisions`.`Division ID` =  '7'

Does Work:
SELECT
`Divisions`.`Division Name` AS Division_Name
FROM
`Divisions`
WHERE
`Divisions`.`Division ID` =  "7"

Hi,

The error message you had comes from PHP and is about the syntax of a line in your script, not the TBS class.
So it cannot be TBS.

It's also technically interesting to know that TBS does not manage your SQL. It takes the SQL instruction as is and transfers it to the Database System.
(nevertheless it can inject some value in the string where jokers are placed in case of subblock mode)

I wasnt disputing you, I'm a PHP novice and most of my php expirence is trial and error and what I read in books and websites, learning this way causes one to think literally and within the confines of whats known.

What i did to make it work the way I tend to write code is change the single quotes around the entire sql to double quotes and then put my variables in single quotes which is how I do it outside of TBS.

So far TBS has been awesome I use smiley on another site and its just fine but a lot to get lost in for a novice like myself.

This so far has been easier to grasp and to actually use.

Thanks again

-J-

Hi Jyoung2k,

My remark wasn't for dispute :)
It was just to give you some clues for further debuging.

I also had some issues with php and mysql with spaces in field names  I stumbled across this thread before I found my answer on my own through trial and error the answer is:  if you use the single quote on the tilde in your $query=  instead of the single quote on the double quote you can leave your spaces intact without aliases.  [code ]for example:   $query = "UPDATE `table_name` SET `Field Name with spaces intact` = '$var1', `Another field` = '$var2' WHERE `This Field` = '$record_var';";