Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare two fields and report

    I have a simple table that I need to compare the TL fileds and produce a report only if the TL fields matches and the Status feild equals Buy and Sell.

    I want to match any inventory that is the same TL and I am buying, selling, and report that from my database. I am copying a simplified version of the table as an example.

    I am using PHP and MySQL as tools and cannot figure out how to comapire all the fields, as I am a rookie at best.

    ID RefNo TL Status Status_Date
    56 25 DryVan Sell 2006-06-21
    57 25 DryVan Buy 2006-06-30

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Here's one way, probably not the best way but it works:
    PHP Code:
    $query "
    select a.ID
    from simple_table as a, simple_table as b
    where a.TL = b.TL
    and a.ID != b.ID
    and status in ('Buy', 'Sell')"


  • #3
    New Coder
    Join Date
    Jul 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think I am following you but getting an erroe when trying.

    (Parse error: parse error, unexpected T_STRING in /home/trailerr/public_html/AS.php on line 52)
    Line 52 is and status in ('Buy', 'Sell')";

    Here is what I did from your example;

    PHP Code:
    mysql_select_db($dbname);

        
    $result mysql_query ('"
            SELECT tractortype.TractorMake
            FROM tractortype as a,tractortype as b
            WHERE tractortype.TractorMake=tractortype.TracortMake
            and status in ('
    Buy', 'Sell)");
            or die("
    Query failed " . mysql_error());
        
        while ($row = mysql_fetch_assoc($result)) 

        echo "
    <class=titler>Tractors Matched</a> as of ".date("F dY");
        
        echo "
    $row[0] - $row[1] - $row[2] - $row[3] - $row[4] - $row[10] - $row[11]<br>";
    ?> 
    Last edited by guelphdad; 07-21-2006 at 04:01 PM.

  • #4
    Regular Coder
    Join Date
    Mar 2005
    Location
    Brighton, UK
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    mysql_query ('"
    You've got a single quotation mark after the bracket that shouldnt be there.

  • #5
    New Coder
    Join Date
    Jul 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That worked but not producing any information for the table. The table over 100 entires and shuld match 5 times as the TT field matches Sell with the TTfield macthes Buy.

    Does the "FROM tractortype as a,tractortype as b" statment need to be modified?

    PHP Code:
    <?php

    // section for tractors
        
    require_once('settings.inc');

        
    mysql_select_db($dbname);

        
    $result mysql_query ("
            SELECT tractortype.TT
            FROM tractortype as a,tractortype as b
            WHERE tractortype.TT=tractortype.TT
            and status in ('Buy', 'Sell')"
    )
            or die(
    "Query failed : " mysql_error());
        
        while (
    $row mysql_fetch_assoc($result)) 

        echo 
    "<a class=titler>Tractors Matched</a> as of ".date("F d, Y");
        
        echo 
    "> $row[0] - $row[1] - $row[2] - $row[3] - $row[4] - $row[10] - $row[11]<br>";
    ?>
    Last edited by guelphdad; 07-21-2006 at 04:01 PM.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    smarlar, welcome to Coding Forums. Please use [ php] and [/php] tags around PHP and use [ code] and [/code] around regular mysql code. It will help your code display much nicer and easier to read. Remove the space of course.

    I have added the tags in both your posts above. If you look at them you will see that even the code displays differently.

    May I also offer a piece of advice. When getting help with a query always test the query directly in mysql to see if the query is working correctly. Once you know it is working correctly, then any error after that must be as a result of PHP or ColdFusion or whatever other application you run your mysql query in.
    Last edited by guelphdad; 07-21-2006 at 04:03 PM.

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    I think the problem is that you don't understand what the aliases are doing and how to use them.

    When you use an alias to refer to a table then you must refer to the table with that alias afterwards.

    If TRACTORTYPE is the name of your table and you alias it as "a" and "b" then you no longer refer to the table as TRACTORTYPE but as "a" or "b".

    Try your query like this:

    PHP Code:
    $result mysql_query ("
            SELECT tractortype.TT
            FROM tractortype as a,tractortype as b
            WHERE a.TT=b.TT
            and status in ('Buy', 'Sell')"
    )
            or die(
    "Query failed : " mysql_error()); 

  • #8
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You're going to need this line
    PHP Code:
    and a.ID != b.ID 
    otherwise your results set will include 1 row for every row in the table, because every row will find a match with itself.

  • #9
    New Coder
    Join Date
    Jul 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the input as I am getting closer after testing the SQL over and over this what I am coming up with wich returns all the records, but when I use the A.TL !=b.TL I get no results. I should have one match after looking at the database.

    I am using the SELECT * because I am reporting on 6 fields not just one.

    PHP Code:
    mysql_select_db($dbname);

        
    $result mysql_query 
            
    ("SELECT *
            FROM trailertype as a, trailertype as b 
            WHERE a.TL !=b.TL
            AND Status in ('Buy', 'Sell')"
    )
            or die(
    "Query failed : " mysql_error());
        
        while (
    $row mysql_fetch_assoc($result)) 

  • #10
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    You want to be using = there, not !=.
    What fumigator meant was in addition, not instead of, and should probably be < (or >) rather than !=, otherwise you'll end up with twice as many results, having both permutations (combinations? maths was so long ago).
    Code:
    SELECT * FROM trailertype a, trailertype b WHERE a.TL=b.TL AND status IN ('Buy','Sell') AND a.id < b.id;

  • #11
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I think a join defaults to a left join only? So != would only bring back one result. I could be wrong; joins confuse me

    I'm not sure how the < or > would work... it seems it wouldn't catch all matches. If a.ID is 3 and b.ID is 1 and their TLs match it wouldn't list it.

  • #12
    New Coder
    Join Date
    Jul 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The table I am compairing has multiple fields but I only need to compair two fields the TL and Status

    TL|Status
    Dump Bed|Sell
    Dump Bed|Buy
    Dry Van|Sell
    Wet Van|Sell
    Overnight|Buy


    I need to return the results "Dump Bed" as they are the only two that match both TL's and the Status is Sell and Buy. I am using MySQl version 4.0.16 if that makes a difference. Here are the results thus far.

    PHP Code:
    ("SELECT *
            FROM trailertype
            WHERE trailertype.TL=trailertype.TL
            AND Status='Buy'"

    Returns all records with the Status types of Buy

    PHP Code:
    ("SELECT *
            FROM trailertype
            WHERE trailertype.Status<>trailertype.Status
            AND TL=TL"

    Returns all records that TL that equals TL

    PHP Code:
    ("SELECT *
            FROM trailertype a, trailertype b
            WHERE a.TL =b.TL 
            AND Status='Buy'"

    Returns nothing

    PHP Code:
    ("SELECT * 
                FROM trailertype as a, trailertype as b  
                WHERE a.TL !=b.TL 
                AND Status in ('Buy', 'Sell')"

    Returns nothing

    I have tired all possible combinations in My Sql admin panel and the same results are the same. Confused and dazed at this point, or do I need to copy all records to two separate databases to achieve the desired results, or do I need another variable to make this happen?

  • #13
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    Can you maybe provide a dump of your schema and some data?

  • #14
    New Coder
    Join Date
    Jul 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is a dump of the table.

    PHP Code:
    -- phpMyAdmin SQL Dump
    -- version 2.6.4-pl2
    -- http://www.phpmyadmin.net
    -- 
    -- 
    Hostlocalhost
    -- Generation TimeJul 242006 at 02:22 PM
    -- Server version4.0.16
    -- PHP Version4.4.1
    -- 
    -- 
    Database: `trailerr_ats`
    -- 

    -- --------------------------------------------------------

    -- 
    -- 
    Table structure for table `trailertype`
    -- 

    CREATE TABLE `trailertype` (
      `
    IDint(11NOT NULL auto_increment,
      `
    RefNoint(11NOT NULL default '0',
      `
    CustomerNamevarchar(50) default NULL,
      `
    TLvarchar(50NOT NULL default '',
      `
    TLOvarchar(50NOT NULL default '',
      `
    TrailerLengthvarchar(50) default NULL,
      `
    TrailerLengthOthervarchar(50) default NULL,
      `
    TrailerWidthvarchar(50) default NULL,
      `
    TrailerRoofTypevarchar(50) default NULL,
      `
    TrailerFloorvarchar(50) default NULL,
      `
    TrailerRearDoorsvarchar(50NOT NULL default '',
      `
    TrailerUnitTypevarchar(50NOT NULL default '',
      `
    TrailerLiningvarchar(50) default NULL,
      `
    TrailerInteriorOptionsvarchar(50) default NULL,
      `
    TrailerInteriorOptions1varchar(50NOT NULL default '',
      `
    TrailerKingPinSettingvarchar(50) default NULL,
      `
    TrailerSuspensionvarchar(50) default NULL,
      `
    TrailerSuspensionTypevarchar(50) default NULL,
      `
    TrailerTiresvarchar(50) default NULL,
      `
    TrailerTiresOthervarchar(50) default NULL,
      `
    TrailerColorvarchar(50) default NULL,
      `
    TrailerColorOthervarchar(50NOT NULL default '',
      `
    Datedate NOT NULL default '0000-00-00',
      `
    Yearvarchar(4NOT NULL default '',
      `
    Quantityint(4NOT NULL default '0',
      `
    Locationvarchar(5NOT NULL default '',
      `
    SellingPricefloat NOT NULL default '0',
      `
    Statusvarchar(20NOT NULL default '0',
      `
    other_text,
      `
    SELL_STATUSvarchar(20) default '0',
      `
    SPricefloat default '0',
      `
    ACostfloat default '0',
      `
    sidfloat default '0',
      `
    SCommfloat default '0',
      `
    titletext NOT NULL,
      `
    imgdatalongblob NOT NULL,
      `
    Status_Datedate NOT NULL default '0000-00-00',
      `
    aYearvarchar(4NOT NULL default '',
      `
    aMonthchar(2NOT NULL default '',
      
    PRIMARY KEY  (`ID`),
      
    KEY `CustomerName` (`CustomerName`),
      
    KEY `CustomerName_2` (`CustomerName`),
      
    KEY `CustomerName_3` (`CustomerName`),
      
    KEY `CustomerName_4` (`CustomerName`),
      
    KEY `CustomerName_5` (`CustomerName`),
      
    KEY `CustomerName_6` (`CustomerName`),
      
    KEY `CustomerName_7` (`CustomerName`),
      
    KEY `CustomerName_8` (`CustomerName`)
    TYPE=MyISAM AUTO_INCREMENT=97 ;

    -- 
    -- 
    Dumping data for table `trailertype`
    -- 

    INSERT INTO `trailertype` (`ID`, `RefNo`, `CustomerName`, `TL`, `TLO`, `TrailerLength`, `TrailerLengthOther`, `TrailerWidth`, `TrailerRoofType`, `TrailerFloor`, `TrailerRearDoors`, `TrailerUnitType`, `TrailerLining`, `TrailerInteriorOptions`, `TrailerInteriorOptions1`, `TrailerKingPinSetting`, `TrailerSuspension`, `TrailerSuspensionType`, `TrailerTires`, `TrailerTiresOther`, `TrailerColor`, `TrailerColorOther`, `Date`, `Year`, `Quantity`, `Location`, `SellingPrice`, `Status`, `other_`, `SELL_STATUS`, `SPrice`, `ACost`, `sid`, `SComm`, `title`, `imgdata`, `Status_Date`, `aYear`, `aMonth`) VALUES (9036NULL'FlatBed''''NA''''''''''''''''''''''''''NA''''NA''''0000-00-00''2005'11'AL'22500'Sell''''0'0000'''''0000-00-00''2007''6');
    INSERT INTO `trailertype` (`ID`, `RefNo`, `CustomerName`, `TL`, `TLO`, `TrailerLength`, `TrailerLengthOther`, `TrailerWidth`, `TrailerRoofType`, `TrailerFloor`, `TrailerRearDoors`, `TrailerUnitType`, `TrailerLining`, `TrailerInteriorOptions`, `TrailerInteriorOptions1`, `TrailerKingPinSetting`, `TrailerSuspension`, `TrailerSuspensionType`, `TrailerTires`, `TrailerTiresOther`, `TrailerColor`, `TrailerColorOther`, `Date`, `Year`, `Quantity`, `Location`, `SellingPrice`, `Status`, `other_`, `SELL_STATUS`, `SPrice`, `ACost`, `sid`, `SComm`, `title`, `imgdata`, `Status_Date`, `aYear`, `aMonth`) VALUES (9136NULL'FlatBed''''48''''96''Aluminum''Laminated hardwood''Swing''''''''''''''Spread''NA''''NA''''2006-06-30''2005'11'AL'22500'Sell''''0'0000'''''0000-00-00''2007''6');
    INSERT INTO `trailertype` (`ID`, `RefNo`, `CustomerName`, `TL`, `TLO`, `TrailerLength`, `TrailerLengthOther`, `TrailerWidth`, `TrailerRoofType`, `TrailerFloor`, `TrailerRearDoors`, `TrailerUnitType`, `TrailerLining`, `TrailerInteriorOptions`, `TrailerInteriorOptions1`, `TrailerKingPinSetting`, `TrailerSuspension`, `TrailerSuspensionType`, `TrailerTires`, `TrailerTiresOther`, `TrailerColor`, `TrailerColorOther`, `Date`, `Year`, `Quantity`, `Location`, `SellingPrice`, `Status`, `other_`, `SELL_STATUS`, `SPrice`, `ACost`, `sid`, `SComm`, `title`, `imgdata`, `Status_Date`, `aYear`, `aMonth`) VALUES (9236NULL'FlatBed''''48''''96''Aluminum''Laminated hardwood''''Carrier''Kemlite''''e-Track''42''Air''Slide''LP 24.5''''Black''''2006-06-30''2005'11'AL'22500'Sell''''0'0000'''''2006-06-30''2007''6');
    INSERT INTO `trailertype` (`ID`, `RefNo`, `CustomerName`, `TL`, `TLO`, `TrailerLength`, `TrailerLengthOther`, `TrailerWidth`, `TrailerRoofType`, `TrailerFloor`, `TrailerRearDoors`, `TrailerUnitType`, `TrailerLining`, `TrailerInteriorOptions`, `TrailerInteriorOptions1`, `TrailerKingPinSetting`, `TrailerSuspension`, `TrailerSuspensionType`, `TrailerTires`, `TrailerTiresOther`, `TrailerColor`, `TrailerColorOther`, `Date`, `Year`, `Quantity`, `Location`, `SellingPrice`, `Status`, `other_`, `SELL_STATUS`, `SPrice`, `ACost`, `sid`, `SComm`, `title`, `imgdata`, `Status_Date`, `aYear`, `aMonth`) VALUES (9336NULL'BellyDump''''NA''''''''''''''''''''''''''NA''''NA''''2006-07-24''2000'10'Multi'0'Buy''''0'0000'''''2006-07-24''2006''4');
    INSERT INTO `trailertype` (`ID`, `RefNo`, `CustomerName`, `TL`, `TLO`, `TrailerLength`, `TrailerLengthOther`, `TrailerWidth`, `TrailerRoofType`, `TrailerFloor`, `TrailerRearDoors`, `TrailerUnitType`, `TrailerLining`, `TrailerInteriorOptions`, `TrailerInteriorOptions1`, `TrailerKingPinSetting`, `TrailerSuspension`, `TrailerSuspensionType`, `TrailerTires`, `TrailerTiresOther`, `TrailerColor`, `TrailerColorOther`, `Date`, `Year`, `Quantity`, `Location`, `SellingPrice`, `Status`, `other_`, `SELL_STATUS`, `SPrice`, `ACost`, `sid`, `SComm`, `title`, `imgdata`, `Status_Date`, `aYear`, `aMonth`) VALUES (9436NULL'DryVan''''NA''''''''''''''''''''''''''NA''''NA''''2006-07-24''2000'50'AL'0'Buy''''0'0000'''''2006-07-24''2006''8');
    INSERT INTO `trailertype` (`ID`, `RefNo`, `CustomerName`, `TL`, `TLO`, `TrailerLength`, `TrailerLengthOther`, `TrailerWidth`, `TrailerRoofType`, `TrailerFloor`, `TrailerRearDoors`, `TrailerUnitType`, `TrailerLining`, `TrailerInteriorOptions`, `TrailerInteriorOptions1`, `TrailerKingPinSetting`, `TrailerSuspension`, `TrailerSuspensionType`, `TrailerTires`, `TrailerTiresOther`, `TrailerColor`, `TrailerColorOther`, `Date`, `Year`, `Quantity`, `Location`, `SellingPrice`, `Status`, `other_`, `SELL_STATUS`, `SPrice`, `ACost`, `sid`, `SComm`, `title`, `imgdata`, `Status_Date`, `aYear`, `aMonth`) VALUES (9636NULL'Grain''''NA''''''''''''''''''''''''Slide''NA''''NA''''2006-07-24''2000'12'Multi'0'Buy''''0'0000'''''2006-07-24''2006''4'); 
    Last edited by smarlar; 07-24-2006 at 10:23 PM.

  • #15
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    The 'CREATE TABLE' and a handful of INSERT queries would be better, I'm far too lazy to try and do anything with that.


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

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