4 replies to this topic

[Spartan10590]

Hello everyone,

I have very little (read none) experience with MySQL and I am trying to finish a web database which was started by a coworker.

He has left me with examples to go on for everything, and I'm confident I can figure it out, however I have hit a snag.

We are trying to build a database of technical information for ships. The problem being that there are several different lines needed for each different ship class (some require more than others) and that we are trying to use one select statement per class, but for every ship in that class as well. Unfortunately, not all ships of one class have the same information.

What I need to do, is find a way to, essentially, use a variable in a LIMIT statement, which I've already read as being impossible. I need a way to use a single statement to select the relevant information for a single ship. The current setup we have is to use a LIMT #, # statement, but this will only allow for the statement to call up a single ship's information.

Again, I'm not too good at this, but I do have a basic enough understanding. Any help I can get would be greatly appreciated, and if you need to know anything more than what I've said, I can do that.

Thanks

Edited by Spartan10590, 11 August 2008 - 08:53 AM.

[JoeyMagz]

Doing that is all about how you set up the database. You need to make sure to use an id column for each class which will be defined throughout every table. However, to select all information about a specific vessel from the table you would use:

SELECT * FROM boat_class_table WHERE vessel_type = 'some vessel type'

or to select all vessels in a class regardless of type:

SELECT * FROM boat_class_table

The only time I really use LIMIT is when I'm trying to select a distinct value from a table, or when I'm selecting information from a table using the "primary key". (like the ID column)

With those 2 examples there, you would be able to create code that would select all the vessels and then display stats about each individual vessel. Hope that helps you out.

Edited by JoeyMagz, 11 August 2008 - 10:55 AM.

[Spartan10590]

Ok, the way he has the database setup won't allow me to do that, but I have an idea.

Would I be able to add two new columns, say "ship_type" and "ship_id", where "ship_type" is a letter specific to each ship type, say a carrier or a destroyer, and then "ship_id" is a specific ship in that type.

Using this, can I add a statement on one page to pull up a list of all ships of a type, and then based on what I select, use the second column to pull up the ship I want?

I'd assume it would look something like this:

SELECT * FROM navydb WHERE ship_type = 'x' (First Page, showing a list of all ships of a type)

and

SELECT * FROM navydb WHERE ship_id = 'x' (Second Page, showing the info for one ship)

Edited:
Ok, sorry for double post, but I've figured out that my method posted above won't work either (or not in a way that I can see).

Is there any way that I can get a value, such as a "ship_id" to be "stored" so that it can then be called upon on a new page to call the appropriate data? Say, if I clicked on CVN-68, which has a ship_id of "6", can I have a single statement that will then pull up every line with a ship_id of '6'?

The thing that keeps catching me is that I need to then be able to use this same statement (it is on one page per ship calss, so I can have a different one for every class, but the one statement has to pull up a specific ship in that class depending on which one I click on) to call up a different specific ship.

I wish I would've had my friend figure out more of this, but he has since stopped working here for the summer (and I am done here by the end of the week, which is why I may seem to be in a hurry to figure this out).

Thanks again.


Edited again:
Admins are going to hate me for a triple post (Does editing here do the same thing as bumping/adding a reply?)


I've made a little progress. I'm now able to pull information based on the 'ship_type' field that I've setup.

Now, all I need to do is find a way to limit the results of this query to those lines that have the same 'ship_id' as the ship I've selected.

My friend has a line on a previous page in this database where I found this:

  $data = mysql_query("SELECT name, hull, id, decomD, sot1, sot2, sot3, capacity, qty,
mfr, tmanual, mip, tmanual, apl, conv, nframe, ndeck, ndoor, wtestD,  plc, lcurtain, stray, 
lastupdate FROM navydb WHERE hull LIKE 'AOE%' AND id=" 
. mysql_real_escape_string($_GET['id'])) or die(mysql_error());   

(Moved the lines around to avoid breaking the page)

In my new portion of code, 'hull' has been replaced with "ship_type='a' ". Will the "id=" (in my case it would be ship_id=) statement allow me to limit results to those that have the same ship_id as my original search? I would need to use an AND statement here as there would be multiple ships with the same ship_id, but only one ship per ship_id in each ship_type. In other words I'm recycling ship_ids between types.

Sorry for all my posts, I'm learning as I go here so I'm constantly second guessing myself or finding something different to try.

Edited by rc69, 12 August 2008 - 11:27 AM.

[rc69]

Quote

Does editing here do the same thing as bumping/adding a reply?

Nope.

[JoeyMagz]

I'm really confused here...lol If you can draw a picture of this (I mean literally draw a picture of what you want in photoshop, because I don't understand what you're asking at all lol) or just tell me what you want done in like 2 sentences, because when I read too much my head goes crazy and my eyes get all blurry, that's why I'm in the coast guard now and not in college. Sorry I can't be more helpful right now.