Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: MYSQL Query

  1. #1
    jamison is offline Nearly a Glow Sage
    Join Date
    Jun 2005
    Posts
    20

    Default MYSQL Query

    I am trying to query one of my MYSQL data bases.
    The query below doesn't work:
    SELECT *
    FROM master_stock
    WHERE symbol='BA' AND 'BAC' AND 'BMY'
    AND dtime
    BETWEEN '2009-09-08 9:30'
    AND '2009-09-08 16:30'
    The problem is in the Where command. This is the first time I have tried to retrieve multiple symbols fro the db at one time.
    If I query for one symbol like - WHERE symbol='BA' the query returns the needed data.
    Can anyone please help me with the correct syntax. Thank you very much.

  2. #2
    Matt's Avatar
    Matt is offline GlowHost Administrator
    Join Date
    Jan 2005
    Location
    Behind your monitor
    Posts
    5,350

    Default

    I have moved this thread into the "Programming talk" section of these forums, I think you will have better results in this section than "General Support."
    Send your friends and site visitors to GlowHost and get $125 plus bonus!
    GlowHost Affiliate Program | Read our Blog | GlowHost's Facebook | Follow us on Twitter | GlowHost on Google+

  3. #3
    Dmitriy is offline Nearly a Master Glow Jedi
    Join Date
    Feb 2007
    Location
    Ukraine
    Posts
    124

    Default

    I guess you really want to do this:

    (symbol='BA' OR symbol='BAC' OR symbol='BMY')

  4. #4
    jamison is offline Nearly a Glow Sage
    Join Date
    Jun 2005
    Posts
    20

    Default

    Yes, I really do want to do this and thank you for your reply. However, I am getting some unusual results. My query is
    SELECT *
    FROM master_stock
    WHERE symbol = 'BA'
    OR symbol = 'BAC'
    OR symbol = 'BMY'
    OR symbol = 'CAT'
    AND dtime
    BETWEEN '2009-09-08 9:00'
    AND '2009-09-08 17:00';

    The last symbol is dropped from the report and the report begins at the first day of the data base, not on Sept, 8th. Any ideas. I really appreciate the help.

  5. #5
    Dmitriy is offline Nearly a Master Glow Jedi
    Join Date
    Feb 2007
    Location
    Ukraine
    Posts
    124

    Default

    This one is complete correct example

    PHP Code:
    SELECT 
    FROM master_stock
    WHERE 
    (symbol 'BA' OR 
    symbol 'BAC' OR 
    symbol 'BMY' OR 
    symbol 'CAT') AND 
    dtime BETWEEN '2009-09-08 9:00' AND '2009-09-08 17:00' 
    For simplycity I'd suggest you omit BETWEEN and use only logic operands

    PHP Code:
    SELECT 
    FROM master_stock
    WHERE 
    (symbol 'BA' OR 
    symbol 'BAC' OR 
    symbol 'BMY' OR 
    symbol 'CAT') AND 
    dtime >= '2009-09-08 9:00' AND 
    dtime <= '2009-09-08 17:00' 
    The trick is in brackets where you put your OR's.

  6. #6
    jamison is offline Nearly a Glow Sage
    Join Date
    Jun 2005
    Posts
    20

    Default

    Hi Dimitriy,
    Thank you very much for your excellent held. I got exactly what I needed and learned something as well. Your help is most appreciated.

  7. #7
    jmarcv's Avatar
    jmarcv is offline Cranky Coder
    Join Date
    Jan 2005
    Posts
    354

    Default

    If you get to a point where you start adding more symbols, consider this.

    WHERE find_in_set(symbol,'BA,BAC,BMY,CAT,DOG') ...

  8. #8
    jamison is offline Nearly a Glow Sage
    Join Date
    Jun 2005
    Posts
    20

    Default

    Great point. Thank you for the tip. Saves a lot of cutting and pasting

  9. #9
    rlhanson's Avatar
    rlhanson is offline Master Glow Jedi
    Join Date
    Aug 2007
    Location
    Chapman, Kansas
    Posts
    531

    Default

    I have a pretty cool php script that I got from phpsense.com and have changed the connection information to query an existing database on my server.
    What I would like to do (I know JM is now groaning) is:

    When a user logs in with their email and password, check db1/table1 & db2/table2 to see if the user exists in either db. If they do, allow login, if not in either, don't allow login.
    I read something about JOIN, but I don't get it. I want to eventually be able to display content based off of which (or both) db the user exists in and I need to pass the login/password to login scripts (I got that part figured out).

    Config:
    PHP Code:
    <?php
     define
    ('DB_HOST''localhost');
        
    define('DB_USER''user');
        
    define('DB_PASSWORD''pw');
        
    define('DB_DATABASE''database');
    ?>
    Login script:
    PHP Code:
    <?php
     
    //Start session
     
    session_start();
     
     
    //Include database connection details
     
    require_once('config.php');
     
     
    //Array to store validation errors
     
    $errmsg_arr = array();
     
     
    //Validation error flag
     
    $errflag false;
     
     
    //Connect to mysql server
     
    $link mysql_connect(DB_HOSTDB_USERDB_PASSWORD);
     if(!
    $link) {
      die(
    'Failed to connect to server: ' mysql_error());
     }
     
     
    //Select database
     
    $db mysql_select_db(DB_DATABASE);
     if(!
    $db) {
      die(
    "Unable to select database");
     }
     
     
    //Function to sanitize values received from the form. Prevents SQL injection
     
    function clean($str) {
      
    $str = @trim($str);
      if(
    get_magic_quotes_gpc()) {
       
    $str stripslashes($str);
      }
      return 
    mysql_real_escape_string($str);
     }
     
     
    //Sanitize the POST values
     
    $login clean($_POST['login']);
     
    $password clean($_POST['password']);
     
     
    //Input Validations
     
    if($login == '') {
      
    $errmsg_arr[] = 'Login ID missing';
      
    $errflag true;
     }
     if(
    $password == '') {
      
    $errmsg_arr[] = 'Password missing';
      
    $errflag true;
     }
     
     
    //If there are input validations, redirect back to the login form
     
    if($errflag) {
      
    $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
      
    session_write_close();
      
    header("location: login-form.php");
      exit();
     }
     
     
    //Create query
     
    $qry="SELECT * FROM members WHERE login='$login' AND passwd='".md5($_POST['password'])."'";
     
    $result=mysql_query($qry);
     
     
    //Check whether the query was successful or not
     
    if($result) {
      if(
    mysql_num_rows($result) == 1) {
       
    //Login Successful
       
    session_regenerate_id();
       
    $member mysql_fetch_assoc($result);
       
    $_SESSION['SESS_MEMBER_ID'] = $member['member_id'];
       
    $_SESSION['SESS_FIRST_NAME'] = $member['firstname'];
       
    $_SESSION['SESS_LAST_NAME'] = $member['lastname'];
       
    session_write_close();
       
    header("location: member-index.php");
       exit();
      }else {
       
    //Login failed
       
    header("location: login-failed.php");
       exit();
      }
     }else {
      die(
    "Query failed");
     }
    ?>
    Can someone point me in the right direction?
    Thank you,
    Lynne Hanson
    RL Hanson-Online

  10. #10
    jmarcv's Avatar
    jmarcv is offline Cranky Coder
    Join Date
    Jan 2005
    Posts
    354

    Default

    Yep - groaning because you malke me read and dont get down to the detaills LOL!


    select id from check_db1.table
    UNION
    select id from db2.table2

    Thats how you do what you want

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 26
    Last Post: 02-01-2008, 08:17 PM
  2. Replies: 0
    Last Post: 04-21-2005, 09:13 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16