Query Giving Me Results I Don't Want.
query:
SELECT sales_reps.sr_id,
sales_reps.order_id,
sales_reps.name,
sales_reps.job_number,
UNIX_TIMESTAMP( shop_orders.date ) AS date,
sales_reps.stage,
sales_reps.status,
pi.fname AS pfname,
pi.lname AS plname,
sales_reps.date_to_shop,
users.builder,
users.division,
shop_assignments.editor_id,
shopper.fname AS sfname,
shopper.lname AS slname,
ei.shop_result,
ei.good_tape
FROM sales_reps
LEFT JOIN shop_orders ON shop_orders.order_id = sales_reps.order_id
LEFT JOIN users ON users.user_id = shop_orders.builder_id
LEFT JOIN shop_assignments ON shop_assignments.sr_id = sales_reps.sr_id
LEFT JOIN users AS shopper ON shopper.user_id = shop_assignments.shopper_id
LEFT JOIN users AS pi ON pi.user_id = shop_assignments.pi_id
LEFT JOIN exit_interviews AS ei ON ei.shop_id = shop_assignments.shop_id
WHERE sales_reps.status = 'Rejected'
AND (
ei.shop_result != 'Trip x1'
OR ei.shop_result != 'Trip x2'
OR ei.shop_result != 'Non-Reg Trip x1'
OR ei.shop_result != 'Non-Reg Trip x2'
OR ei.shop_result != 'Office Closed'
)
ORDER BY users.builder, users.division, shop_orders.date DESC , sales_reps.stage ASC
It's not filtering out the records with the shop_result field matching what I've listed.
View Complete Forum Thread with Replies
Related Forum Messages:
Dont Start If The User Dont Have Administrator Privileges On The PC
I instaled MySQL in a computer (with widows XP) that not always is loged with a user withthe computer administrator privileges. when a person whitout administrator privileges is the first person that logs, the DataBase dont startup until an administrator logs on. What can i do to allways startup the DB without care the computer's administrator privileges?
View Replies !
Fulltext Search Not Giving Exact Results
How do i get fulltext boolean search to locate results based on partial words. For example i am searching for +protein +bar but i also want results for anything that contains the word bar such as bars PHP $keywords = $_GET['query']; // add + to the first keyword $keywords = '+' . $keywords; // replace ' ' with ' +' so you add a + to every keyword $keywords = str_replace(' ', ' +', $keywords); $query = "SELECT products_id as prodid, products_name as name, manufacturers_id as manufacturers_id, products_price as price, products_msrp as msrp, products_date_added as pda, products_status as status, products_sku as sku, products_weight as weight, MATCH(products_name, products_description, products_keywords, products_notes) AGAINST ('$keywords' IN BOOLEAN MODE) AS score FROM products WHERE MATCH(products_name, products_description, products_keywords, products_notes) AGAINST ('$keywords' IN BOOLEAN MODE) ORDER BY score DESC"; $result = mysql_query($query) or die("Error in $query!"); OUTPUT: SELECT products_id as prodid, products_name as name, manufacturers_id as manufacturers_id, products_price as price, products_msrp as msrp, products_date_added as pda, products_status as status, products_sku as sku, products_weight as weight, MATCH(products_name) AGAINST ('+protein +bar' IN BOOLEAN MODE) AS score FROM products WHERE MATCH(products_name) AGAINST ('+protein +bar' IN BOOLEAN MODE) ORDER BY score ASC
View Replies !
Query Giving Error
when I try to run the following query,I get the error: #1064 - You have an error in your SQL syntax near 'select min(dt_startdate) from tbl_bookings)>��-11-09' GROUP BY p.id_property ' at line 1 query: SELECT p. * , f. * , ph.nme_photo, ph.nme_caption, p.id_property FROM tbl_bookings b, tbl_propertyfeature f, tbl_propertyphoto ph, tbl_property p WHERE b.id_property = p.id_property AND f.id_property = p.id_property AND p.id_property = ph.id_property AND ph.flg_main =1 AND p.flg_active =1 AND p.blnSpecial = 'Y' AND b.dt_startdate IN ( SELECT min( dt_startdate ) FROM tbl_bookings ) > ��-11-09' GROUP BY p.id_property I am using MySql 3.23.58
View Replies !
Dont Have An Error File
I have problems setting up the server. When trying to start it it gives me Error 0 and when I look for my error logfile in the data directory I don't find one. In the manual it is written that it should be there unless you tell the server to put it somewhere else but I didn't do this.
View Replies !
All Entries Which 'dont' Match
I am trying to query a list of hosts and extract all entries which 'dont' match. SELECT LOGS.host, GOODLIST.host FROM LOGS,db.GOODLIST WHERE (LOGS.host <> GOODLIST.host) When I use this query, it is very very slow. Matching the host with the GOODLIST.host works great and fast but when I use <> to get the hosts which DONT match, it doesnt work as expected.
View Replies !
How Do I Make It Dynamic I Dont Want 20 000 Pages Of Data?
I havent really got a clue what im doing so i need some help I am tryig to create a site with news on it the problem im having is that on my index.php isbring up the headlines with a section of content $leader fine but how do i create a link from the $headline to the same page (dynamic) but only showing the headline and full content of the headline clicked on. I cant get my pages to be dynamic which is the whole idea of using mysql and php so please teach me or send me to some decent tutorials the manual on php.net is to technical it doesnt explain anything and the other tutorials out there are to simple they dont get into the complex stuff. here is the code <? include("inf.php"); mysql_connect($hostname,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="SELECT * FROM ($tablename)"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); $i=0; while ($i < $num) { $id=mysql_result($result,$i,"id"); $datetime=mysql_result($result,$i,"datetime"); $subject=mysql_result($result,$i,"subject"); $headline=mysql_result($result,$i,"headline"); $leader=mysql_result($result,$i,"leader"); $content=mysql_result($result,$i,"content"); ?> <!-- Begin #main --> <div id="main"> <div id="main2"> <div id="main3"> <h2 class="date-header"> <? include("site/includes/time.php"); ?> </h2> <div class="post"> <h3 class="post-title"> <!-- On the main index.php page I want 5 headlines and then I want the headline to link to this same (dynamic) page but with the variable $content also showing and the other four headlines no longer showing --> <a href="Article alone page" title="permanent link"><? echo "$headline"; ?> </a> - posted on <? echo "$datetime"; ?></h3> <div class="post-body"> <p> <b><? echo "$leader"; ?></b> </p> </div> </div> <div class="post"> <div> <br /> </div> </div> </div></div></div> <!-- End #main --> <? ++$i; } ?>
View Replies !
Dont Display Record After Date Has Passed
Lets say I am making a site that has upcoming birthdays of a whole lot of people, and I want to display only the birthdays that have not happened yet (ie where birthday > current date). What I do not know, is what format I must enter the date into; at the moment I am just using a char type in my MySQL database and entering it as : 26/03 (or what ever). Also, how would I get the current date and compare it to this date?
View Replies !
Mysql Client Dont Write Russian Characters
I installed MySQL 5.0.24 and have so problem.I can't type russian characters in mysql client. Database has data in koi8 encoding (from dump, from php form...) and this data correctly shown.But when I switch encoding to russian in mysql client cursor stops moving independent from my keys pressing,when switch it return to English and typing continued.For example,I enter: select fld from tbl where fld="russ",if "russ" in koi8 I cann't type this characters. It is possible type russian characters from OS command line: mysql -p -e "select fld from tbl where fld='russ'" dbname. In this case whole query text will be entered and correctly executed. If anybody know how possibly decide this problem write me please. My OS is ALTLINUX 3.0 : LANG=ru_RU.KOI8-R MySQL's variables: | character_set_client | koi8r | character_set_connection | koi8r | character_set_database | koi8r | character_set_filesystem | binary | character_set_results | koi8r | character_set_server | koi8r | character_set_system | utf8 | character_sets_dir | /usr/local/mysql-standard-5.0.24-linux-i686/share/mysql/charsets/ | | collation_connection | koi8r_general_ci | collation_database | koi8r_general_ci | collation_server | koi8r_general_ci
View Replies !
Foreign Key Constraints Dont Work (suse Linux, Innodb)
i changed standard storage engine to innoDB and created tables to test the foreign key constraints: create table prof ( id integer auto_increment, name varchar(30) not null, primary key(id) ); and create table lesson ( id integer auto_increment, profid integer, name varchar(30), primary key(id), foreign key(profid) references prof(id) on delete cascade); now, when i delete a dataset in "lesson", it should delete the corresponding dataset in "prof" right? also, i shouldnt be able to create a dataset in "lesson", when there is no prof with the provided id i think. but it doesn't get deleted, and i also can create lessons with any profid, even if the prof with that id doesnt exists.. somehow the foreign key constraints dont work
View Replies !
Why Dont Shared Hosting Accounts Provide Stored Procedures And Triggers?
i am searching for 3 days now for a reliable, fast hosting company, which supports stored procedures and triggers on mysql5. most of them dont support this, because mysql5 has a design-flaw in which it stores procedures and triggers on a root level-area (tables mysql information_schema). due this logic, most of the hosting companies wont allow you to create/run procedures and triggers. funny, that hosting companies forbid you to use new technologies and methods to build faster.
View Replies !
Save Query Results
In other words, how do I build a new table from the results of a query, so I can access pieces of this data later without performing the same query (a very heavy one) over and over again?
View Replies !
Filter Results Of One Query
I'm looking for the syntax that would allow me to filter one querys results using another query i.e. QUERYA = SELECT * FROM table WHERE id < 100 QUERYB = SELECT * FROM QUERYA WHERE id <> 49 I understand that I can do the above example in one query - I'm just using that to simplify matters.
View Replies !
Order Query Results
Regarding a dummy set of data below, ID Status 1 Open 2 Closed 3 Expired 4 Closed 5 Open 6 Cancelled 7 Expired 8 Cancelled I want to view the results but order them according to the status. i.e. i want to view in order of Open, Closed, Expired, Cancelled I can only think of a long drawn out way by which i would create the four seperate queries.
View Replies !
Query Results By Pages
I would like to display query results in pages of say 20 results each. e.g. a table with two columns, serial# and descrition, with a few thousand entries. What would be the SQL query to find a specific serial number, and display all 20 results within the "page" that row happens to be in? e.g say serial#=3211 is in row 64, display rows 60->79.
View Replies !
Formatting Query Results?
I have a shell script (sh) that queries a MySQL database and then formats the results to an html page. I realize shell is probably not the best choice for this but it's all I know. My problem is that I need to do a mysql query for every field in every record in order to put it into the html page. This is obviously very slow. This is an example of what I'm doing now: ProductLine=`mysql -u www -s support <<EOF SELECT ProductLine FROM Documents WHERE ID = '$ID'; EOF` DocType=`mysql -u www -s support <<EOF SELECT DocType FROM Documents WHERE ID = '$ID'; EOF` DocName=`mysql -u www -s support <<EOF SELECT DocName FROM Documents WHERE ID = '$ID'; EOF` FileName=`mysql -u www -s support <<EOF SELECT FileName FROM Documents WHERE ID = '$ID'; EOF` Like I said, I am doing this for every field in every record. It works but it is VERY slow. I thought if there was a way to tell the query to insert a special charater between each field then I could use awk to pick out the fields and only do one query per record... something like this: SELECT ProductLine, DocType, DocName, FileName FROM Documents WHERE ID = '$ID'; I don't know how to take that output and put it into my shell variables.
View Replies !
Saving Query Results
Is there any way to save the results of a query without the quote marks? When i try to import the text file that I saved the query to, I have to go thru and delete all of the quote marks.
View Replies !
How To Get Distinct Hour(s) In Query Results?
I need to write a query that will count the number of records that have been written for each hour of the day. This must be done entirely in SQL, and I am not an SQL expert. I know how to break the hour out of a timestamp using the hour() function, but I'm unclear on how to get the breakdown like I described above.
View Replies !
Eliminating Duplicate Results From Query
I'm trying to dynamically build pull down menus based on the contents of a column (in order to build a search query). But the column may have the same data entered multiple times. This results in a pull down that has the same option listed several times. I wonder if I can eliminate duplicates from that array? So that it only appears once in the array.
View Replies !
Update Query Results To Another Table
So I'm learning, bear with me, this is probably pretty easy(but I am a bit perplexed) but I'm writing a php page for a cron. I am selecting a user name from a table basically selecting all row entries in that table with a certain username, and adding them together. $query=mysql_query(SELECT UserName, SUM(AcctOutputOctets) FROM radacct GROUP BY UserName) The output is this(when I run a query in mysql) UserName..............SUM(AcctOutputOctets) test..................... 345566 Basically i'm adding up all the users entries and totaling their Usage. Now the question. I want to write that to another table named mtotacct. format like this. Username..............TotalOctets. I'm just a little uncertain how to properly set this up to pass the results from the query to the UPDATE statement.
View Replies !
Showing Query Results In HTML
In mysql I can find the minimum data from a particular column by doing as follows: SELECT MIN(Freq_Min) FROM datatable; and mysql will display the results. Now I want to do the same thing but I want to do it in html, so I wrote the following code. When I open up the webpage stats.php it says that it couldn't execute the query. Can someone help get me on track? .....
View Replies !
Use Query-results As Delayed Insert?
Can I use the results of one query like: -> SELECT user_id FROM tbl_customers And use the results to fill the VALUES-statement in another table like: -> INSERT INTO tbl_postal ('user-id') VALUES (<previous query results>) If so, could someone complete my second SQL-statement, since I'm staring at my CRT for over 2 evenings and I cannot get it to work.
View Replies !
Query Is Not Returning Desired Results.
My query is not returning desired results. I want to structure my query to return the Post Subject, Post Text, and Poster. Instead, it is returning the correct Post Subject, correct Post Text, but the Poster is just repeating the username from the '$_POST[username]' variable. How can I alter the query to return the correct name? the query PHP <?php$conn = mysql_connect( $domain, $user, $password ) or die("Err:Conn");$rs = mysql_select_db($db, $conn) or die("Err:Db");$sql = "SELECT `table_posts_text`.`post_subject`, `table_posts_text`.`post_text`, `table_users`.`username` FROM table_categories, table_forums, table_topics, table_posts, table_posts_text, table_users WHERE username='$_POST[student]' ANDcat_title='$_POST[category]' AND`table_categories`.`cat_id`=`table_forums`.`cat_id` AND `table_forums`.`forum_id`=`table_topics`.`forum_id` AND `table_topics`.`topic_id`=`table_posts`.`topic_id` AND `table_posts`.`post_id`=`table_posts_text`.`post_id`AND `table_topics`.`topic_poster`=`table_users`.`user_id`AND`table_posts`.`poster_id`=`table_users`.`user_id`ORDER BY `table_topics`.`topic_id`, `table_posts`.`post_id`"; $rs=mysql_query($sql,$conn);while($row=mysql_fetch_array($rs)){ echo($row['post_subject'] . $row['post_text'] . $row['username']);}?> There are six tables involved: 1. table_categories (cat_id medint, cat_title varchar) 2. table_topics (topic_id medint, forum_id smallint topic_title varchar topic_poster medint) 3. table_forums (forum_id smallint, cat_id medint, forum_name varchar) 4. table_posts (post_id medint, topic_id medint, forum_id smallint, poster_id medint) 5. table_posts_text (post_id medint, post_subject varchar, post_text text) 6. table_users (user_id medint, username varchar)
View Replies !
Reversing Query Results (not The Same As ORDER BY)
I am trying to reverse the order in which the results of my query are given. I am tweaking a gallery. I need to get the previous 3 pics based on the pic I am looking at. The following code is a simplified version of what I have. The problem is, when I echo the results, the thumbnails are displayed in DESC order (i need them to display in ASC order) but if I order the query using ASC, the query gets the wrong images. SELECT pic_id WHERE pic_id < $current_pic_id ORDER BY pic_id DESC LIMIT 3 I need the results to show like this: oldest pic | older pic | old pic | current pic | new pic | newer pic | newest pic I've got the new pics sorted out but the old pics are causing a little problem... So basically, is there a way that I can reverse the results of my query?
View Replies !
Mixed ORDER BY Query Results
Is there a way to order a query result a specific way? For instance, I'm querying using WHERE id IN(23, 25, 19) I would like the results to stay in that order instead of by ASC or DESC. Is this possible?
View Replies !
Case Insensitive Query Results?
I've been noticing that the MySQL 4.1.19 server on a client's Web server returns case-insensitive results for SELECT statements when using PHP's PDO extension. I didn't notice this happening on my XP dev server with MySQL 5.0, and have never seen it happen on any other server. Does anyone know why this happens and what can be done about it?
View Replies !
Limiting Results In Query Question
I've got a SQL query which i want to return one result from each development in the database. The result i want to be returned is the first image in the database related to a development. There are currently two developments in my database however all the images that are stored in the database are returned and not one per development. QUERY: select development_images.development_id, developments.title, developments.description, development_images.url, development_images.image_title from developments, development_images WHERE developments.id = development_images.development_id order by developments.id DESC LINK: http://demo2.pixel-room.net/developm...dex.php?page=1
View Replies !
Combine All Query Results In One Column
I got a query similar to that: Code: SELECT c_01, c_02, c_03, c_04, c_05 FROM table WHERE c_01 != '' OR c_02 != '' OR c_03 != '' OR c_04 != '' OR c_05 and I get results similar to: Code: +---------+ ---------+ ---------+ ---------+ ---------+ | c_01 | c_02 | c_03 | c_04 | c_05 | +---------+ ---------+ ---------+ ---------+ ---------+ | 0 | 0 | 0 | 0 | data_06 | | data_01 | data_02 | 0 | data_04 | 0 | | 0 | 0 | data_03 | data_05 | 0 | | data_07 | 0 | 0 | 0 | 0 | +---------+ ---------+ ---------+ ---------+ ---------+ But the result I need is something like: Code: +---------+ | c_ALL | +---------+ | data_01 | | data_02 | | data_03 | | data_04 | | data_05 | | data_06 | | data_07 | +---------+ I just can't figure out how to combine several column in 1 column. I could loop through everything in PHP but that data is also sometimes redundant so I'd like use GROUP BY first before it gets to PHP. I have no control over the architecture of the DB...
View Replies !
Query Exact Results And The Wildcard
I need to run a query that pulls 100% accurate data. For this I need to use the "equal" rather than "LIKE". To throw a monkey wrench in the equation, not all fields are mandatory. When the user does not choose to search by a field, I simply want the query to treat it as ALL. Normally I would use the wildcard for this, but the wildcard only works on LIKE. If I use EQUAL, MySQL wants to match the wildcard which of course returns no records. EXAMPLE DETAILS: Variables are retrieved and sanitized from POST. For the sake of this example, I will keep it simple. Each variable collects a the POST if it exists, and if it does not exist it will set the default to "%" (the mySql wildcard). The variables are as follows: $city, $state, $gender The Query would be: SELECT * FROM users WHERE city = '$city' AND state = '$state' AND gender = '$gender'
View Replies !
Order By Giving Two Groups
I have a new database table which contains name and url field. When I order by name I get two groups, the first being those without an entry in the url field, the second with an entry in the url field, each grou seperately ordered correctly. In a possibly related problem with the same table displaying in a web browser through php, there are again two groups diplaying when ordered by name. If a member of the first group is displayed and resubmitted without change, it then appears in the second group. I tried doing an export / import to refresh the data, but it didn't help. Any ideas for causes and or solutions?
View Replies !
Mysqldump Giving Error
im currently using mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386), for some reason i have problems running mysqldump program, i have tried the followings and they all gave me error, could someone point me out? thanks. mysqldump --opt --user="g8" --password="xxx" --databases="g8" mysqldump --opt --user="g8" --password="xxx" --database="g8" mysqldump --opt --user="g8" --password="xxx" --database g8 g8 is my username and also the database name im using, and xxx is my password.
View Replies !
Mysql Giving Error With '
I have built a php script that inserts data into a mysql table but everytime I try and insert data with an ' it returns an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's', 'step')' at line 2 in this particular case I was trying to insert the word "dave's"
View Replies !
FULLTEXT Search Giving Me Nothing?
I'm new to MySQL FULLTEXT searching, so I'm hopping this'll be an easy fix. $query = "SELECT * FROM `downloads` WHERE MATCH (`title`,`desc`) AGAINST ('$name') AND level = 1 ORDER BY $orderby"; $query = mysql_query($query); The above query will find nothing when $name = 'ipod', even though I know 'ipod' is listed MANY times throughout the table. I've tried adding "IN BOOLEAN MODE" after '$name', but that gives me an error when trying to run mysql_fetch_array... What gives? On a side note, $query outputs as it should: SELECT * FROM `downloads` WHERE MATCH (`title`,`desc`) AGAINST ('ipod') AND level = 1 ORDER BY os ASC Edit: I found that if I run the following, ALL database rows are returned no matter what the search term: $querytxt = "SELECT *, MATCH (`title`,`desc`) AGAINST ('$name') FROM `downloads` WHERE level = 1 ORDER BY $orderby";
View Replies !
Retreiving Large Query Results In Chunks
I'm running queries with MySql 4.0.17 that return thousands of records. Because I need to present them in GUI, I returieve the results in chunks using LIMIT, for example - get first 100, then the range 100-2000 and so on. The problem is as follows: in the first chunk, MySQL uses one strategy to fetch the results, and in the following chunks - a different strategy. This means that records from the subsequent queries might have records that already appeared in the first query or that some records will be left out. For performance issues it is a problem to add a unique secondary sorting criteria (like id) to the query. Is there a clean way to force MySQL to relate to the first (initial) query result set?
View Replies !
Conditional Select Based On Query Results
I want to print a different message on the database, if a query returns an empty set and a different if the query returns any records. How can i accomplish that? I looked at the case statement but i can't get it working with that.
View Replies !
Running A 'Distinct' Query Returns Some Results TWICE !?
I've spent hours trying to find out why a perfectly simple query that uses the 'distinct' keyword such as: Select distinct(field1) From tablename; works fine based on eg. field1, but when running it on eg. field2 in the very same table, it returns 2 results (yet, only on some words!). Pls see example of result below: Eg of a 'faulty' result: .....
View Replies !
Query To Ignore Results If Condition True
I am having trouble defining a query that ignores rows if a query if true IE. Column 1 = 1,2,3,4,5 Column 2 = a,b,a,a,c For example: I search for results from column 1 and if '3=a', ignore all 'a' rows thus returning the result b=>2, c=>5.
View Replies !
How To Concat Sub-query Results In A Single Cell
I have an user table, a product table and a product_affect_user table. +------+------------------+ | user | other column.... | +------+------------------+ | 6478 | firstname, lastn | +------+------------------+ +------+-------+ | prod | descr | +------+-------+ | 0932 | blaah | +------+-------+ +------+----------+ | user | product | +------+----------+ | 6478 | 923 +------+----------+ That I try to do is to fetch all product affected to an user in the same cell, something like that: +------+------------------+ | user | affected product | +------+------------------+ + 6478 | 1,45,324,673,923 | +------+------------------+
View Replies !
Create URL From To Database Fields In Query Results
I have a MySQL database with table that works as document library. Table keeps details about docs on our server. There are bunch of fields but the key ones here are FileName, URL and location. I'm using PHP to work with the MySQL Database hosted on a Mac OS X Server. Basically, I have a query set up to pull certain records based on a field called Keywords. On the result page I want a field (or item) that combines the URL field and Filename field that ultimately points to the phycial location of the file. For every record thr url field contents is the same since the physical location of the files are in the same directory on the server. Right now the URL field has http://www.mysite.com/docs/. I'd like the result page to have the filename shown, but that filename would be a link to URL+filename. Is there a way to do this? .....
View Replies !
|
TRACKER
