Query Multiple Tables using 1 common field

[dodgeitorelse3]

Is it possible to run a query to get data from multiple tables where they have only 1 common field name?

Below are images of the field names for each table as well as the web and database server info.

If possible which join would I use?

I can get 2 tables to return data using

SELECT id, size, Original_Name, resource, disabled, tickmark, image, type, creator, modder, beta, final, modded, classification, sf, tod, weather, es2
			FROM
				mapnames
					INNER JOIN mapnames_tooltips USING (id)
					ORDER BY id Asc"

however this gives me double entries in array.

 

Array
(
    [0] => Array
        (
            [0] => 1
            [id] => 1
            [1] => 11441673
            [size] => 11441673
            [2] => Border.aao
            [Original_Name] => Border.aao
            [3] => 2_8_5_default, abr, blannick, chevy, crypt, ffat, kool, rapidfire, vanoke, spectrum
            [resource] => 2_8_5_default, abr, blannick, chevy, crypt, ffat, kool, rapidfire, vanoke, spectrum
            [4] => 0
            [disabled] => 0
            [5] => 1
            [tickmark] => 1
            [6] => ../../gsreaders/lgsl/lgsl_files/maps/aarmy/armygame/border.jpg
            [image] => ../../gsreaders/lgsl/lgsl_files/maps/aarmy/armygame/border.jpg
            [7] => PVP
            [type] => PVP
            [8] => Richard Starr
            [creator] => Richard Starr
            [9] => 
            [modder] => 
            [10] => N
            [beta] => N
            [11] => Y
            [final] => Y
            [12] => N
            [modded] => N
            [13] => LAND
            [classification] => LAND
            [14] => N
            [sf] => N
            [15] => DAY
            [tod] => DAY
            [16] => HAZY
            [weather] => HAZY
            [17] => N
            [es2] => N
            [18] => 0
            [slots] => 0
        )

    [1] => Array
        (
            [0] => 2
            [id] => 2

This gives me double entries though.

Any advice would be greatly appreciated.

 

[Barand]

The names do not have to be the same, although it does make relationships clearer if they are. What matters is the value in the columns as that is what is used to match a record in a table to one or more related records records in another table.

Before using a database, the tables in it should be normalized. Looking at your tables, that term is something new to you and your whole design is bad need of repair. The correct design of your data makes life a lot easier further down the road.

There is a link to an SQL tutorial in my signature that may help you.

[dodgeitorelse3]

Yes, normalized is a new term to me. I see I have my work cut out for me. There are 1926 entries in each table I showed lol. Thank you for replying and I will follow up after I "NORMALIZE".. Wish me luck.

[dodgeitorelse3]

Still trying to figure out correct design. I understand the resource column is a comma separated string and is a bad design but not sure about any of the rest of the table table/design. I am still trying to figure this out.

[Barand]

Putting normalization aside for now, you are getting those duplicate field values because you are using fetch_array(). This returns an array for each row in the results with both column names and column numbers as keys.

Better to use fetch assoc() to get arrays with just the column names as keys.

Your query selects a column called "original_name". That does not appear in any of your column name images.

[dodgeitorelse3]

The query has Original_Name. I changed to fetch_assoc and now do not have the duplicate field values. Now I have to work on the normalization. Using design for database I currently have fails when I try to join all the tables. Thank you for that fix Barand. I tried to send sql files in message but I get the error too big. There are 1929 entries and file size of mapnames table is biggest weighing in at 600 kb. second biggest is 223 kb and the last five sql files are 100 kb or less.

 

[dodgeitorelse3]

I just cut the screen shot short for the Original_name field.

[dodgeitorelse3]

@Barand I marked your  reply as a solution. There were actually 2 solutions, the first was the SQL tutorials in your signature and the second was your post about 

19 hours ago, Barand said:

Better to use fetch assoc() to get arrays with just the column names as keys

Thank you very much.

This post also turned into a database normalization issue and I am still working on that.

[dodgeitorelse3]

Just an update for the normalization, Barand has taught me how to normalize tables. He also did the work and normalized my tables for me which allowed me to see and understand what proper normailization is and how it works. Thanks millions Barand.