Irritating select statement

Fernashes

If somebody could give me a hand with this... because I'm going nuts!

Basically it's a configuration database to keep track of who has what. It's set up as you've got items (servers, firewalls, software, routers, etc.) and details for those items (name, serial number, os, etc). I was asked to make this as dynamic as possible so have chosen to make it possible to have dynamic drop down lists which can be generated from a table... such as an operating system.

To get around using MySQL variables by accident I tend to name my things in Dutch. 🙂

Here are the pertinent tables:

detail

detailid (primary key)
itemid (foreign key to the item)
typeid (foreign key to "detail_type" which is next)
waarde (the actual value of the detail, eg OS = Windows 2003).

detail_type (list of possible details)

typeid (primary key)
oms (description of the detail, eg Windows 2003)
bron (bron=source, the link to the table if it's got more than one value, like the table list_os)
templateid (next table...)

detail_template (the template for which type of object it is - is it a standard text field or does it need to be treated as a drop down menu?)

templateid (primary key)
template (this is the name of the file that has the correct data, have been using includes)
oms (description of the template)

What I want to do is pass the variable detail_type.typeid when making a new detail, basically saying "I want a new object of this type which automatically grabs the correct name and template type." That all works. But I need the list of things to choose from when creating a new object, eg an operating system. In theory, I should be able to grab the name of the table out of detail_type.bron but I keep getting MySQL errors.

The error I'm getting is 1064, which the MySQL manual tells me is when you have a variable using the name of one of the standard MySQL variables. I don't see this happening... does anybody else?

This is the query I'm using:

SELECT * 
FROM {
SELECT bron
FROM detail_type
WHERE detail_type.typeid =6};

detail_type.typeid=6 refers to the type "operating system".

Can anybody give me a heads up for why this is going wrong? I'd be much obliged!

Azala

I'm a little confused as to what data you actually want to retrieve from the db, but:

I was asked to make this as dynamic as possible so have chosen to make it possible to have dynamic drop down lists which can be generated from a table... such as an operating system.

$result = mysql_query("SELECT * FROM detail_type");
echo '<select>';
while ($row = mysql_fetch_row($result)){
    echo '<option id="'.$row[0].'">'.$row[1].'</option>';  //Output a select box with OS choices and a link to it's ID
}
echo '</select>';

$row[0] will be typeid and $row[1] will be oms, change $row[1] to $row[2] if you want to output bron instead...

Not sure if I answered whatever you were asking though?

Fernashes

Hi Azala,

Thanks for your reply. Not quite what I meant, though. Shall we try it again?

Basically, this is the flow that I've envisioned
1. You've got the variable detail_type.typeid which has been passed via the URL to the current page, because you want to make a new detail.

Get all the detailtype info from the database.
Using detailtype.templateid -> detail_template.template, get the address of the page which needs to be included to correctly handle this data type (text field, drop down menu, etc.)
If the type is a drop down menu, you need to get the list from the corresponding table in the database. For example, the list of operating systems resides in "list_os". And that is where the source (detail_type.bron) comes in: that's the name of the table you need to grab.
Use the table to generate the correct drop down box.
Choose the correct value.
Submit it to the database.

The problem that I'm having is with the MySQL query... whenever I try to do the one I've shown above, I get MySQL syntax errors. And the whole idea is worthless if I can't get that table name. 🙂

Make more sense?

Looking forward to your answer!

Azala

Could you just try to explain what you want the SQL query to do?

This gives error (I have no idea what you are trying to achieve with this query):

SELECT *  

FROM { 
SELECT bron 
FROM detail_type 
WHERE detail_type.typeid =6};

This will not give error:

SELECT bron 
FROM detail_type 
WHERE detail_type.typeid =6