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.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Odd MySQL query question

    I don't know if this is possible, but I will get straight to the point.

    I have my query pulling data from TableA. I am using Comparison Operators to get the data that I need for this page. The only column pertaining to this problem is the record_id in this table. I need to pull data from TableA with the comparison of if the status of this in TableB is a certain status.

    TableA has the record_id
    TableB has status of either No or Yes

    A longer explanation of what I need. I need to select the record_id from TableA where the status, which is in TableB, is No. I can't move or add a column to TableA to house the status. The status for every record is in another Table, but I still need to be able to pull records based on the status of that same id from another table.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    so what does this have to do with PHP? it should be posted in the MySQL area.

    Do you also have a column for record_id in table B? If so then how do you think you should be performing the join to get the query you want?

  • #3
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad
    so what does this have to do with PHP? it should be posted in the MySQL area.
    I didn't specify, but I am using PHP to query the db...I do not have shell access.


    Quote Originally Posted by guelphdad
    Do you also have a column for record_id in table B? If so then how do you think you should be performing the join to get the query you want?
    I do have record_id in table B. I do not know how to get the query I need & that is why I am asking. I have just been listing all records on a page w/the status shown. I want to change the page & show only the records that the status is No w/out showing the status.

    I have like 3 Table Columns on the page. Column 1 showed record_id, Column 2 showed record_name, & Column 3 showed status. With this we would do what we needed to do with the data.

    I want to change this & have 2 Table Columns w/only the record_name & record_id, but only need to display records where the status in Table B is No.

    This is what I have so far w/out the needed change.
    PHP Code:
            $query_seminars = ("SELECT record_name, record_id 
                                FROM $tableA 
                                WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= record_date 
                                AND record_name != ''
                                ORDER BY `record_id` DESC"
    ); 

  • #4
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    can you provide your table schema? your explanation was confusing

  • #5
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    PHP Code:
    CREATE TABLE `tableA` (
      `
    record_idint(32NOT NULL auto_increment,
      `
    record_namevarchar(32NOT NULL default '0',
      
    PRIMARY KEY  (`record_id`)
    ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Order Base' AUTO_INCREMENT=13830 
    PHP Code:
    CREATE TABLE `tableB` (
      `
    commission_idint(32NOT NULL auto_increment,
      `
    regth_idint(32NOT NULL default '0',
      `
    reth_idint(32NOT NULL default '0',
      `
    record_idint(32NOT NULL default '0',
      `
    commissiondecimal(6,2NOT NULL default '0.00',
      `
    datedatetime NOT NULL default '0000-00-00 00:00:00',
      `
    statusenum('NO','YES'NOT NULL default 'NO',
      
    PRIMARY KEY  (`commission_id`)
    ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Commissions Archives' AUTO_INCREMENT=13045 
    The record_id in tableB is the same number that is the record_id in tableA. I need to pull that record_id & record_name from tableA ONLY when the status in tableB is NO.

  • #6
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    SELECT a.record_id, a.record_name FROM tablea a JOIN tableb b ON (a.record_id=b.record_id) WHERE b.status='NO';

  • #7
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have to make this work again, but it needs to be a little different.

    I need to pull that record_id from tableA ONLY when the record_id is found in tableB. Like if record_id number 1050 is not in tableB then don't need that record's data from tableA.

  • #8
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    SELECT a.record_id, a.record_name FROM tablea a JOIN tableb b ON (a.record_id=b.record_id)

  • #9
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks. It pulls the records like I need, but for some reason it pulls them twice. I have posted the php code below. I am copying & pasting it from my php file. I am using a code that I found to show records in a drop down menue & once you choose an option it populates the page with the record info.

    I did change the PHP Comparison Operator to != in the JOIN statement & it pulls the records like I need. I manually checked all the records displayed in the drop down menu & it is correct.

    The problem with showing each record twice is that in the drop down menu it is showing each record_id twice & not just once.
    PHP Code:
        <?PHP
            
    //error_reporting(E_ALL);
            
    require_once('cdb.php');
            
            
    mysql_select_db($db_name$conn);
            
    //SELECT a.record_id, a.record_name FROM tablea a JOIN tableb b ON (a.record_id=b.record_id)
            
    $query_seminars = ("SELECT a.customers_id, a.order_id, a.order_date, a.referring_id, a.order_status 
                                FROM $order_table a JOIN $commisions_table b ON (a.order_id != b.order_id)
                                WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= a.order_date 
                                AND a.referring_id != ''
                                AND a.order_status = 'Filled'
                                ORDER BY a.order_id DESC"
    );
            
    $seminars mysql_query($query_seminars$conn) or die(mysql_error());
            
    $row_seminars mysql_fetch_assoc($seminars);
            
    $totalRows_seminars mysql_num_rows($seminars);
            
            
    $colname_chosen_seminar "-1";
            if (isset(
    $_POST['order_id'])) {
              
    $colname_chosen_seminar = (get_magic_quotes_gpc()) ? $_POST['order_id'] : addslashes($_POST['order_id']);
            }
            
    mysql_select_db($db_name$conn);
            
    $query_chosen_seminar sprintf("    SELECT * 
                                                FROM $order_table 
                                                WHERE order_id = %s"
    $colname_chosen_seminar);
            
    $chosen_seminar mysql_query($query_chosen_seminar$conn) or die(mysql_error());
            
    $row_chosen_seminar mysql_fetch_assoc($chosen_seminar);
            
    $totalRows_chosen_seminar mysql_num_rows($chosen_seminar);
        
    ?>
    <form action="<?php $_SERVER['PHP_SELF'?>" method="post" name="news_entry" id="news_entry">
      <table width="40%" border="0" align="center" cellpadding="4" cellspacing="0" class="newsheader">
        <tr> 
          <td width="44%" align="left" valign="top"><div align="right"><strong>Order ID :</strong></div></td>
          <td width="56%" align="left" valign="top"><div align="left">
            <select name="order_id" onChange="chkFrm(this)">
                <option value="Choose" <?php if (!(strcmp("Choose"$_POST['customers_id']))) {echo "SELECTED";} ?>>Select Order ID</option>
                <?php
    do {
    ?>
                <option value="<?php echo $row_seminars['order_id']?>"<?php if (!(strcmp($row_seminars['order_id'], $_POST['order_id']))) {echo "SELECTED";} ?>><?php echo $row_seminars['order_id']?></option>
                <?php
    } while ($row_seminars mysql_fetch_assoc($seminars));
      
    $rows mysql_num_rows($seminars);
      if(
    $rows 0) {
          
    mysql_data_seek($seminars0);
          
    $row_seminars mysql_fetch_assoc($seminars);
      }
    ?>
            </select>

  • #10
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    your query isn't the one I gave...joining with a != condition isn't what you want- I can't think of a situation where you would ever want that.

    doing ...FROM table a JOIN table b...
    is the same as:
    ...FROM table a LEFT INNER JOIN table b...
    (LEFT and INNER are the defaults)
    this means that the query will only take records from a that meet the join condition, which is what you said you wanted.

  • #11
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    With me just using FROM $order_table a JOIN $commisions_table b ON (a.order_id=b.order_id) it is pulling the records that are in the $commisions_table. I emptied the tables except for 5 records. I placed the record_id for 2 records in the $commisions_table. In the drop down menu it is showing those record's id numbers. I need it to show every record's id number that is in the $order_table, but not yet in the $commisions_table

    When I used the != it showed me every record_id that was in $order_table that hasn't yet been added to $commisions_table which is what I need.

  • #12
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Don't mean to bump my own thread, but I would like a little more help since I got it working, but it is showing the Record ID's twice in the drop down menu.

    I have to have this completed within 48 hours so that I don't have to make monthly ratings by hand in Excel for last month.


  •  

    Posting Permissions

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