MYSQL Query

[jamison]

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.

[Matt]

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

[Dmitriy]

I guess you really want to do this:

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

[jamison]

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.

[Dmitriy]

This one is complete correct example

 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

 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.

[jamison]

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.

[jmarcv]

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

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

[jamison]

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

[rlhanson]

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
 define('DB_HOST', 'localhost');
    define('DB_USER', 'user');
    define('DB_PASSWORD', 'pw');
    define('DB_DATABASE', 'database');
?>  

Login script:

 <?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_HOST, DB_USER, DB_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?

[jmarcv]

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