Returning Headings From Tables...

Grofit

ive got a DB full of data about a characters equipment...

i have the following code to retrieve the data needed from the database..


$MySQLQuery = 	"SELECT 'Helmet', 'Upper_Armour', 'Lower_Armour', 'Boots', 'Weapon', 'Shield' FROM characters WHERE Name LIKE '$User'";
$MySQLResult = 	mysql_query($MySQLQuery, $MySQLObject) or die(mysql_error());

$EquipData = mysql_fetch_object($MySQLResult);

echo $EquipData->Helmet, $EquipData->Upper_Armour, $EquipData->Lower_Armour;

but instead of outputting "Leather Helmet" or anything it outputs "Helmet" and "Upper Armour".... basically the headings instead of the values... is there a reason why?

Frederick

Try this to see if it is your coding:

$sql = mysql_query("SELECT * FROM characters WHERE Name LIKE '$User'") or die (mysql_error());
$EquipData = mysql_fetch_array($sql);
echo $EquipData["Helmet"].", ".$EquipData["Upper_Armour"].", ".$EquipData["Lower_Armour"];

Roger_Ramjet

It is doing this because you have quoted the column names: turning them into char strings of literals which mysql then outputs.

$MySQLQuery =     "SELECT Helmet, Upper_Armour, Lower_Armour, Boots, Weapon, Shield FROM characters WHERE Name LIKE '$User'";
$MySQLResult =     mysql_query($MySQLQuery, $MySQLObject) or die(mysql_error());

$EquipData = mysql_fetch_object($MySQLResult);

echo $EquipData->Helmet, $EquipData->Upper_Armour, $EquipData->Lower_Armour;

Might be easier to just use SELECT * FROM .

Sxooter

Yeah, this is the rather non-intuitive usage of quotes in SQL.

" quotes go around identifiers,
' quotes go around constant values.

So, select "MyField" would select the field named MyField, usually respecting the case since you bothered to quote it. Select MyField would select the field myfield or MYFIELD depending on how your database folds case (PostgreSQL folds down, Oracle folds up, I believe MySQL also folds up too, but I'm not sure.)

csn

Why does Postgres fold down (if not using double quotes) instead of being case-sensitive? i.e. the following would all select different results:

select col1 from table1;
select Col1 from Table1;
select COL1 from TABLE1;

Sxooter

I'm not sure what you're asking here. Do you want each of those statements to be different or the same? To postgresql, they are all the same, i.e. they're all folded to lower case.

The SQL spec says to fold everything to upper case, but for easier reading, the postgresql folks chose lower case instead. The functionality is the same, in that as long as you either quote all the time, or never quote, postgresql will work just like oracle. I.e.:

create table "Bubba" ...

would create a table named Bubba in both. Selecting bubba or BUBBA or Bubba without quoting would then result in an error since you hadn't quoted the identifier and used the original mixed case.

My recommendation is to never quote identifiers when issuing DDL in either one, and let the database handle the upper / lower case folding for you.

csn

I guess I'm asking why databases (or the SQL spec) think they need to fold at all.

select col1, Col1, COL1 from table1;

could return three different columns. Not that I think having columns named like that is a good idea. Some people prefer to name their columns, tables, etc. with LeadingCaps or camelCaps. I don't think mysql does any folding - when I migrated to Postgres I had to rename all my columns, tables, etc. (either that or enclose them all in double quotes).

Sxooter

I'm not sure about the why either, except that the people on the SQL committee in the ancient past decided it was a good idea.

Grofit

ah ok thanks for replies everyone.. what about this problem...

"SELECT Inv1 FROM characters WHERE Name = 'Bob'";

is there any way to chance the Inv1 to a variable such as..

$InvSlot = "Inv";
$InvSlot .= "1";

"SELECT $InvSlot FROM characters WHERE Name = 'Bob'";

would that work? (i cant try it until i get back)

if not, is there a way to put variables in as the selections?

Sxooter

Sure. One caveat:

one: Make sure your input is properly escaped so you don't have to worry about SQL injection attacks (or an accidental use of an apostrophe)

$inv = pg_escape_string($_POST['input']);

Then, I usually build the string like so:

$query = "select ".$inv." from yada where...";

That's because 90% of the time I'm using an array to do it, like so:

$choices = array("table1","table2",table3");

And I make the select box return numbers, so that what I use looks like this:

$query = "select ".$choices[$inv]." from yada where...";

And arrays don't work well inside strings.

Grofit

ah! genius like how you can do with echo....

thx for that.. its just a bit confusing with " ` ' lol

Grofit

oh and sorry to keep spamming... is there an ItoA()/AtoI() in PHP like in C?

(ItoA = Int to Ascii and vice versa)

or can you just cheat it in PHP by type casting?

like:

$int = 5;

echo (string)$int;

Sxooter

Actually, PHP has one of the stranger, but more capable soft type systems around. In most systems you either have hard types, like C, where you declare ahead of time what it is and it never changes, or soft type systems where everything is stored as a string and converted n the fly. Generally, this makes the hard type systems faster, and the soft typed systems more versatile.

What PHP does is assign a type when the var is created / assigned to. So,

$var = 23.5

means the PHP interpreter internally stores 23.5 as a float.

$var = 55

gets assigned an int and

$var = "123 sesame street"

becomes a string.

Then, while operating on these vars, PHP automagically coerces them to the right types to be compatible based on what you're doing to them and what type they are. So, when you add two vars that are internally assigned as floats, PHP does NOT coercian, it just leaves them alone and does int math on them.

To make a long answer shorter, yes, you can coerce the type just like in C:

$a = $b(int)

and that's the normal way to do it. But you normally don't have to.