How Do I Put SELECT Statement Into "for" Array?

[Volitics]

Does anyone know how I can put the SELECT statement below into a for array?

require_once "./conf/config.inc.php";
$Result = mysql_query("SELECT * FROM $TableName", $db_connection);
echo mysql_field_name($Result, 0) . "\n";
echo mysql_field_name($Result, 1) . "\n";
echo mysql_field_name($Result, 2) . "\n";
echo mysql_field_name($Result, 3) . "\n";
echo mysql_field_name($Result, 4);

//How do I put the above output(s) into a "for" array like the one below?

for ($n =1; $n<=500; $n++){
echo $n;
}

Thank you in advance.

Volitics

[Bijan]

Where did you get that 500 in your for loop?! How do you know that it's 500? You should use mysql_num_fields and write something like the following:

for($i=0; $i<mysql_num_fields($Result); $++)
    echo mysql_field_name($Result, $i);

[Volitics]

Bijan;

Thank you for your kind help.

Best Regards;

Volitics

[dalecosp]

How about:

$Result = mysql_query("SELECT * FROM $TableName", $db_connection);
$the_array=mysql_fetch_assoc($Result);
$the_fields=array_keys($the_array);

foreach ($the_fields as $r) {
	echo $r."<br>";
}

??
😉

[Bijan]

Originally posted by dalecosp
How about:

$Result = mysql_query("SELECT * FROM $TableName", $db_connection);
$the_array=mysql_fetch_assoc($Result);
$the_fields=array_keys($the_array);

foreach ($the_fields as $r) {
	echo $r."<br>";
}

??
😉 [/B]

Well, this one is gonna be very slow for big tables. If you have millkons of records and you only need to get the field names, you don't need to get all the data back too! Actually I read in MySql manuals that the best way for doing it is by using LIMIT 0, like:

$res = mysql_query("SELECT * FROM mytable LIMIT 0");

So, you don't get anything back, but you still can get the field names!

[dalecosp]

Originally posted by Bijan
Well, this one is gonna be very slow for big tables. If you have millkons of records and you only need to get the field names, you don't need to get all the data back too! Actually I read in MySql manuals that the best way for doing it is by using LIMIT 0, like:

$res = mysql_query("SELECT * FROM mytable LIMIT 0");

So, you don't get anything back, but you still can get the field names! [/B]

Right, I suppose; why on earth would anyone want just the field names and *no data, though? I was assuming that he was attempting to display some meaningful output ... if you have a table with a bazillion entries, you never want to query 'select from' anyway, right? 😉

Cool about the LIMIT thing, though ...

[Volitics]

Bijan, dalecosp;

Thank you for your help. I appreciate the information.

What I'm working on is an insurance quote web site called "FirstInsuranceQuote.com". The web site will accept requests for insurance quotes for the fifty states in the U.S. What I'm needing to do is select the column headings and put them into a drop-down option menu.

The web site will have selection menus for each of the fifty states.

For example, the state I'm working on new, New York, has sixty-two (62) counties. In the MySQL database I have a table, called "StateNewYork," where the name of each of the counties is a column heading.

Rather than code each of the sixty-two counties into the drop-down menu on HTML pages I decided to extract them from the database. There is a total of about 3,100 counties in all fifty states in the U.S. The MySQL database will have a table for each of the states. Each table for each of the states will have a column for each of the counties.

If I had to code all 3,100 counties into HTML drop-down menus it could get to be a laborous task. That's why I decided to extract them directly from the database column headings. I've been working on it all day (actually I've been working on it off and on for several days - whew!). Finally, I've got it working. It seems to be working pretty good.

Thank each of you again for your help.

Best Regards;

Volitics

[jlpierce]

You stated that you had a table for New York, and in it you have 62 columns. Based on this statement you must have 50 tables each listing the counties as columns in it respective table.

I think the better approach would be to have a states table that has (New York for example) 62 records with the state abbreviation as the key and the county in the second column.

Postgresql for example:

CREATE TABLE states (
state character (2) NOT NULL,
county character varying(60) NOT NULL)
primary key (state, county);

Now, with a setup like this you can find examples all over this site regarding how to populate a drop down with the county names as the selector and value.

I feel that with your current schema you will eventually have problems as the number of counties in each state will vary.

If you need to store additional info for rates per 1000.00 for example you can then store that data in another table and reference it back to the master table (in this case states).

I hope this might lead you in the proper direction, I am not sure of your database background, but a good starting place for learning about SQL and good schema design would be a trip to www.sql.org.

John Pierce
feel free to email me if I might be of assistance.

[Bijan]

62 columns in a table?! Wow, then maybe it's better that you rotate your table 90' degrees. What you need is a kind of design like this:

States Table:

Id | Name

1 | NY
.
.

And then Counties Table:

State Id | County Name

1 | "I dunno NY counties names! I'm not an American!"
1 | "I dunno NY counties names! I'm not from USA"
.
.
.
2 | "First country name for the 2nd state
2 | "Second county name for the 2nd state
.
.
.

sql.org is a real cool place as jlpierce mentioned, they really work with some big data! If you need any help for this design, you can start a new thread and maybe put a link to that in here!

Keep on with the good work,
bijan

[jlpierce]

Originally posted by Bijan
62 columns in a table?! Wow, then maybe it's better that you rotate your table 90' degrees. What you need is a kind of design like this:

States Table:

Id | Name

1 | NY

And then Counties Table:

State Id | County Name

1 | "I dunno NY counties names! I'm not an American!"
1 | "I dunno NY counties names! I'm not from USA"
.
2 | "First country name for the 2nd state
2 | "Second county name for the 2nd state

sql.org is a real cool place as jlpierce mentioned, they really work with some big data! If you need any help for this design, you can start a new thread and maybe put a link to that in here!

Keep on with the good work,
bijan

Bijan is correct, rotating the table 90 degrees is the way to go (as I indicated). But, why force an unnatural numeric key in the states table and create a second table to reference the counties from the sates.

You can achieve both in one table, if you use the two letter U.S. Postal abbreviation "NY" for examply then your table would look like this:

states

name | County

NY | County 1
NY | County 2
etc;

Composite key the table on both fields and no duplicates will exist.

You can extend the database further by creating
additional tables that reference the states table.

In the real estate world they say LOCATION, LOCATION, LOCATION.

In the database world they should say SCHEMA, SCHEMA, SCHEMA, because if you do not normalize your database effectively then you will always have PROBLEMS or as Dr. Codd called them ANOMALIES.

John

[Bijan]

But, why force an unnatural numeric key in the states table and create a second table to reference the counties from the sates.

Yeah, you're right. We really do not need that, but you can see my devotion to numeric ids! I never use character ids unless I really need them, most of the times I'm much easier with numbers because I'm always afraid of future and some new data that might come!

bijan

PS: I also liked that compare, LOCATION and SCHEMA 🙂

<edit>
This is my 100th post! Hooooooohooooooooooo
</edit>

[jlpierce]

I do use numeric keys now and then, but mostly I try to find the natural key that would exist in the data scheme. It is not always possible though.

I have seen contact management software that force a numeric key on the contacts, but I have decided that using the contact means itself as the key is usually appropriate.

I guess there are as many thoughts on the subject as there are db designers

John