Jump to content

Query Multiple Tables using 1 common field


dodgeitorelse3
Go to solution Solved by Barand,

Recommended Posts

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.

 

web server.png

db server.png

locale.png

mapnames.png

modifications.png

objectives.png

terrain.png

tooltips.png

vehicles.png

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.