Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Question Unanswered: Newbie question about concatenating 2 tables

    Hi everybody!

    I'm pretty new to databases, but i'm trying to learn

    I need to concatenate 2 databases but i don't know how.

    the command i have is:

    // combobox: switchm_from
    $combo_switchm_from = new DataCombo;
    $combo_switchm_from->Query = "select indice_puerto, concat(puerto_equipo, ' ', puerto_tipo, ' ', puerto_nombre, ' ',puerto_ip) from puertos order by puerto_equipo ";
    $combo_switchm_from->SelectName = "switchm_from";

    what i get is:

    puerto_equipo is linked to a table named equipos to a field named host_name and i get a number when i need to get a name, example:


    1 FastEthernet f0/0 192.168.0.1

    where:
    1 is the value of the field puertos.puerto_equipo which is linked to equipos.host_name and the contents of the equipos.host_name is 'ccslab1'. FastEthernet, f0/0, 192.168.0.1 are puerto_tipo, puerto_nombre, puerto_ip from puertos table

    and i need:

    ccslab1 FastEthernet f0/0 192.168.0.1

    English is not my primary language and i now very very very little about databases, this code was generated by appgini application so trying to explain maybe a little hard for me so forgive me if the explanation is confuse.

    Thanks a lot in advance for any help you can give me

  2. #2
    Join Date
    Feb 2004
    Location
    Siggenthal, Switzerland
    Posts
    19
    First Point:
    You like to link to tables
    equipos and puertos

    select equipos.host_name , puertos.puerto_tipo, puertos.puerto_nombre, puertos.puerto_ip
    from puertos, equipos
    where puertos.puertos.puerto_equipo = equipos.incide_host_name

    Second Point: Concatenate the output:
    like you have done it with concat.

    If its not clear, please post your two tables structure + data

    Walter

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    Thank you Walter,

    here are the 4 tables structure, by the way, you can see it working on http://www.hostudio.com/fieldlab in case you want to play with it, this is a test run

    CREATE TABLE `equipos` (
    `indice_equipo` int(11) NOT NULL auto_increment,
    `host_name` varchar(18) NOT NULL default 'ccslab-',
    `mod_equipo` varchar(20) NOT NULL default '',
    `tipo_equipo` varchar(10) default NULL,
    `equipo_status` varchar(10) default NULL,
    `equipo_num_as` varchar(5) default NULL,
    `equipo_puerto_as` char(3) default NULL,
    `equipo_ubicacion` varchar(15) default NULL,
    `equipo_comentario` varchar(50) default NULL,
    PRIMARY KEY (`indice_equipo`),
    UNIQUE KEY `host_name` (`host_name`)
    ) TYPE=MyISAM AUTO_INCREMENT=2 ;

    CREATE TABLE `puertos` (
    `indice_puerto` int(11) NOT NULL auto_increment,
    `puerto_equipo` int(11) default NULL,
    `puerto_tipo` varchar(18) default NULL,
    `puerto_nombre` varchar(10) default 'fa/0/0/0',
    `puerto_ip` varchar(15) default '10.96.253.',
    `puerto_comentario` varchar(50) default NULL,
    PRIMARY KEY (`indice_puerto`)
    ) TYPE=MyISAM AUTO_INCREMENT=2 ;

    CREATE TABLE `switchmatrix_equipo` (
    `indice_switch` int(11) NOT NULL auto_increment,
    `switch_equipo` varchar(18) default 'ccslab-sm',
    `switch_ip` varchar(15) default NULL,
    `switch_modelo` varchar(15) default NULL,
    PRIMARY KEY (`indice_switch`),
    UNIQUE KEY `switch_equipo` (`switch_equipo`),
    UNIQUE KEY `switch_ip` (`switch_ip`)
    ) TYPE=MyISAM AUTO_INCREMENT=2 ;

    CREATE TABLE `swtichm_puertos` (
    `indice_switchm` int(11) NOT NULL auto_increment,
    `switchm_equipo` int(11) default NULL,
    `switchm_modulo` varchar(4) default NULL,
    `switchm_puerto` varchar(8) default NULL,
    `switchm_patch` varchar(6) default NULL,
    `switchm_from` int(11) default NULL,
    `switchm_status` varchar(4) default NULL,
    PRIMARY KEY (`indice_switchm`),
    UNIQUE KEY `switchm_patch` (`switchm_patch`)
    ) TYPE=MyISAM AUTO_INCREMENT=2 ;

    Just in case it helps you understand what i'm trying to do, here are the translations for the caption and/or field names:

    indice = index
    equipo = equipment
    modulo = module
    puerto = port
    modelo = model
    comentario = comments
    nombre = name
    ubicación = location
    tipo = type.

    Thank you very much for your help, i've tried to create the sentence with the info you gave me, replacing the field names for the actual fieldnames but i get a syntax error, obviously i'm doing something wrong , i understand the logic but í'm lost about sql syntax, as told before, i generated it using a code generator called appgini, just modified the box adding the concatenat
    Attached Files Attached Files
    Last edited by ppespepe; 02-25-04 at 00:42.

  4. #4
    Join Date
    Feb 2004
    Posts
    4

    Thumbs up

    and here is the the another php file]
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2004
    Location
    Siggenthal, Switzerland
    Posts
    19
    Hi

    If you like to see the host_name instead of the indice
    and the incide of puerto and equipo are linked, then
    the SQL-Command:

    SELECT host_name, concat( puerto_equipo, ' ', puerto_tipo, ' ', puerto_nombre, ' ', puerto_ip )
    FROM puertos, equipos
    WHERE indice_puerto = indice_equipo
    ORDER BY puerto_equipo

    But when I click on the link you provided, the I see, that the command
    you posted I don't see a mistake, if I choose puertos, it shows
    a name for the equipos and not an id,

    So where's the problem?

    Walter

  6. #6
    Join Date
    Feb 2004
    Posts
    4

    Thumbs up

    Thanks again Walter....

    If you take a look at the last table which is "Puertos SwitchMatrix" which translates SwitchMatrix Ports, then you´ll see at the equipo (equipment) col, a number and not a name, same in the "Vista en detalle" (Detailed View) window at the "Equipo Conectado" (connected equipment) you´ll see something like this:

    1 FastEthernet fa0/0 10.96.253.36

    where "1" should be ccslab-2600a so what i need is a view like this:

    ccslab-2600a FastEthernet fa0/0 10.96.253.36

    Where does this number comes from?, from the index number for this record in the equipos table, and what´s recorded in the puertos´s equipo field.

    I'll try with the command line you gave me, once again thank you for your invaluable help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •