MYSQL :: "Select Least" Condition?

Single Query For No Of Hits "Today", "This Month", "This Year"
Is it possible to construct a single query that fetches the no of pageviews "today", "this month", "this year" and "total since start"?

The table is very simple, one field for Date (YYYY-MM-DD) and one for the no of pageviews this day.

If not, would it be much slower/faster to divide the query into 4 different queries?

Incorporate An Additional WHERE Condition (was "Help With My Query Please?")
Iâ€™m trying to incorporate an additional statement in the below query, which works:

$sql = "SELECT b.bookID, b.roomID, b.arrive, b.depart, b.status, b.property_id
FROM room_booking_two b
WHERE ((b.arrive BETWEEN '$d1' AND '$d2') OR
(b.depart BETWEEN '$d1' AND '$d2'))
ORDER BY b.roomID, b.arrive";
Now I want to add the line:

WHERE b.property_id=".$_GET['property_id'])
So I thought it would work like this:

$sql = "SELECT b.bookID, b.roomID, b.arrive, b.depart, b.status, b.property_id
FROM room_booking_two b
WHERE b.property_id=".$_GET['property_id'])
AND ((b.arrive BETWEEN '$d1' AND '$d2') OR
(b.depart BETWEEN '$d1' AND '$d2'))
ORDER BY b.roomID, b.arrive";
But that does not seem to work. Can anyone please advise?

How To Use Equal To"==" Operator In If Condition
i am using a "if condtn" in a stored procedure where
i am chekn if "checkid" is equal to 1 or 2 so i m writing seprate if conditions for this.

Like:-
if(checkid==1) then
#######
end if;
if(checkid==2) then
#######
end if;

bt i am getting an error like:
Error Code : 1064
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 '=1
end if;
end if;
if(checkid=2) then

I am not getting this error...
is there anything wrong in if condition syntax or in condition.
Hw should i write the if condition then...???

"Select * FROM" Vs "Select Field1,field2 FROM"
No matter how many fields I want to retrieve from the table I always use "Select * FROM Table" instead of "Select field1,field2 FROM Table".

Are there any disadvantages of always using "Select * FROM Table"?

"between A And B" With Another Condition?
How can I extract values for "members whose level is below '3', and
their names are between the alphabets 'a' and 'b' "?
For example, one of the results should be 'Alex', whose level is '1'.
Or 'Bob', whose level is '2'. But not Charlie, or Tom...etc.

I tried a query like

SELECT uid FROM members WHERE level > '1' AND name between 'a' and 'b',

but it does not work....

Could anyone give me a good solution?

Searching For Numbers - "close To" Or "approximate"
I would like to know how to search a table for records where a particular field is "close to" a particular number.

For instance, let's say I have a table containing students and their most recent test scores. I'd like to see students who scored 7/10. That's easy enough (select * from table where score = 7). I'd also like to run another query to identify other students whose scores are not 7 but "close to" 7. Ie students who scored 6 or 8.

As an added bonus, I'd like to be able to do a related search that shows students who scored 7, then shows the other students ordered by how "close" they are to 7. Ie students who scored 6 or 8 are "closest" matches, and students who scored 1 are "furthest". I hope this makes sense!

Is there any pre-existing MySQL command that will do this? Or will I have to run separate queries for each value?

REQ: MySQL 4.0 Equivilent Of "DateDiff("m", Date, Now())=1"
I have a query that works in the rest of the SQL world

"SELECT invoice.*, invoice.Date
FROM invoice
WHERE (DateDiff("m", Date, Now())=1);"

which will give me all of last month's invoices.

However it doesn't work with MySQL 4.0.

While

"SELECT
Invoice.*
FROM
invoice
WHERE
(Month(Invoice.Date) = ((Month(Now()))-1))"

Is a close substituent, it will blowup in January.

Anyone have an equivalent expression for MySQL 4.0?

How To Modify The "select" Function In The Source-code-version?
I have a source-code-version of MySQL at hand,and I want to modify the "select" function to optimize it.

The question is:how can I start? I wonder whether "select.cc" is the right file I can edit.

Select "german, French" Only With "german"
in a table I have this entry in the column "language":

"GERMAN, FRENCH"

how do i have to make/modify my mysql-syntax so that i can select the entry abowe only with the word: "GERMAN"? Is this possible or do i have to make a data manipulation first?

Can You Select And Concat Multiple Items Separated By "," ?
The select I have finds multiple values for a field. I want to take that field and create ONE field separated by commas.

Is that possible? I could do it in PHP but it will be easier programmatically if I can do it here.

For example:

HTML
Select field1 where x=2;
val1
val2
val3
val4
I want it to be like:

HTML
Select field1 where x=2;
val1, val2, val3, val4
where "val1, val2, val3, val4" is ONE field I can then use.

I am actually using this as a subquery so it's not as easy as php's explode/implode.

First And Last Time In Each Date By Empl (was "Help Me With This Select Statement")
I have the following table which has the following fields:

logid, empid, transdate,transtime

I want to select the first and last transtime in a specific date for every employee, how can i do that?

Dates In The Past 3 Weeks (was "SELECT Query Help")
I'm trying to select data from a table that has been submitted in the past 3 weeks. I am not having any success.

Best 4 Students From Each State (was "help With Select Statement In SQL")
I've got a database that contains names of registered students with the states they come from. I intend to select 4 each from every state that has the highest total scores. I use PHP and MYSQL database.

NAME STATE TOTAL Course
Simon Alabama 56 Computer Science
Mark Alabama 85 Electrical Engineering
John Washington 45 Computer Science
Linda Florida 99 Statistics
Fred Florida 23 Mehcanical Engineering

This is just an illustration of how the database looks like.

the sql statement should select the best 4 students from each state based on thier total scores.

"Unable To Select Requested Database."
i currenty run a game. Well just adding flies now and sorting it out etc..... but when i go to like online.php or anyother flie that the DB runs off.. i get "Unable to select requested database."

Value "000" Stored As "0" In Text Field?
I've managed to import my data from a CSV using SQLyog Enterprise, however, I have a problem with the table storing the text value "000", MySQL truncates it to "0", unfortunately, I need the value to be stored as "000", does anyone know of any way that I can force the field to store all the characters, rather than treating it as a number? Surely a text field should treat any numbers stored within it as text?

What Is The Meaning Of The Letters "AB" In "MySQL AB"?
What does "AB" stand for in "MySQL AB".

Return A "true" Or "false"
i wanna do a "select", where if the script finds any matches, i dont wanna know about them. Instead, i just want it to return a a value: true (1) ou false (0).

ie.:

$sth = $dbh->prepare("SELECT * FROM maps WHERE name='algo' LIMIT 1");
$sth->execute();

if (match is true) {
print "True Match. A match was found";
}
else {
print "False Match. No matches were found.";
}

"Group By" Forgets "Order By"
I'm trying to build a simple RSS reader for a client and am running into some serious problems with the GROUP BY in MySQL. Basically I have two tables: one that holds all the entries and one that holds all the feeds. Then what I can do is say:
[MYSQL]SELECT * FROM entries WHERE feed_id='13' ORDER BY date DESC[/MYSQL]
That will select all the entries that belong to feed #13. This is fine, but what I'm doing now is building a library that will display the feed_id with the latest entry with that id. So I would think it would be something like the following:
[MYSQL]SELECT * FROM entries GROUP BY feed_id ORDER BY date DESC[/MYSQL]
That should get me the single latest entry from each feed_id. However it's forgetting the ORDER BY clause all together and seemingly ordering it by the auto-incrememnt value in the entries field.

Select Where Four Of The Ten Fields Are Same (was "query")
I have a table with ten fields. Now I want to select * from table where four of the ten fields are same. How can I do the query?

MySQL "SELECT" Speed
I've read from MySQL's page about optimization, but still have two specific questions:

1) Let's say I do something like "SELECT * FROM table WHERE col1=val1". And, in this specific case, I know that any time col1=val1, that col2 will always equal val2. So, would it be any faster (or slower) to do: "SELECT * FROM table WHERE col1=val1 AND col2=val2"?

2) Does the order of the argument in the WHERE part affect speed? Ex: "WHERE col_index=val1 AND col=val2" versus "WHERE col=val2 AND col_index=val1"?

"select Into Outfile " Issue
I am running MYSQL 5.0 ON WINXP pro.

I have the following procedure:

Select From Table Where Fieldvalue != ""
Does anyone know the MySQL Query for selecting all fields from a table where the fieldvalues do NOT equal nothing?

SELECT Statement Using WHERE That Involves Value From "next" Row?
Is is possible to construct a SELECT statement that contains a WHERE
clause that uses the value from a column in the "next" row?

ie. given a table with a single field named "myField" with the
following values I want a SELECT statement that selects the rows
"WHERE myField='1' and [NEXT ROW]myField = '2' ":

5
6
1
2
1
3

Select Value Separated By Comma ","
how to select the value which separated by ',' or comma?

About "Select Count(*)" Always Returns 0.
my mysql version is:

Ver 14.7 Distrib 4.1.14, for pc-linux-gnu (i686) using EditLine wrapper

OS: uname -a
Linux backup_srv1 2.4.29 #4 SMP Thu Mar 24 10:31:26 CST 2005 i686 unknown unknown GNU/Linux

Desc:

My statement is
"SELECT COUNT(1) FROM t_card_info_035 WHERE Fcommodity_id = 0x1111 AND Fseller_uin =111111 AND Fdeal_id = 12345;"

my client is a daemon, when it started, it can return the correct result, (such as 4 ) but after two or three days later, it always returns 0. I had gdb into it and find that the result in the result row is 0. but when I restart my daemon program, it will be ok again. This made me crazy... it is a mysql c API bug or something else?

My daemon client is programmed by c++ , and use the c API to connect mysql. I had check that there is nothing wrong with my code, and it has no memory leak or somthing else.

Algorithm Of "select" Statement
As I have to access MySQL database in Pocket PC, I'm developing a
evc++ program to read and retrieve data from MySQL table.

Where can I get some detail information about how "select" and "where"
statements work in the format, index and data files of MySQL database?

Impossible "select Statement"
I have a table with 3 simple fields all 2 varchar 1 double.

distributor,manufactutrerssku,cost

I have several diffent distributors listed in my table and some of them carry the same manufacturers products so I am trying to find out which skus are available at more than one distributor and where the cost is best. This is simple enought to do in php but on a select line I am lost. Can this be done or is it impossible?

How To Select "Next" Record
I've written an application around a database with about 15K records. I need to analyze 1 record at a time, but then skip to the next record based on the index order. Currently, I do this with the following statement:

SELECT * FROM Database ORDER BY IndexVariable1, IndexVariable2 LIMIT 25 ,1

This gets the 25th row of the database. When I want to get the next row, I change the 25 to 26 and repeat the same SELECT satement above.

The problem is that this is very slow. It takes about a half-second per select (unless the read was recently cached and is repeated).

I suspect tht I'm using a brute force method to read the next record, and that a quicker, more elegant method is possible.

Is there a "SELECT NEXT" statement that will execute more quickly than my ugly cludge? Or can anyone offer a better approach that I can try?

"select Match X Against Y" To %
I know this is impossible to do correct since relevance is not calculated as this in the first place, but is there some resonable convertion between the number you get when you
SELECT MATCH (<fulltext index>) AGAINST (<string>) AS relevance...
and percentage value?

Select "MyValue" FROM Tbl? (php)
I would like to do is have a certain value/variable returned in my search results. Like

$myValue="hello";

sql="SELECT column1,'".$myValue."' FROM myTable LIMIT 1"

so it would return something like

column1Info - hello

If you are wondering why I would need to do something like this, I'm trying to use a select in an insert statement to update two columns of a table, however one of the values inserted is a variable. I can do this:

INSERT INTO EB_books (catagoryID,bookName) select catagoryID,CURDATE() from EB_catagories LIMIT 1

But instead of the CURDATE() I would like that to be a value/varialbe in there.

Speed Up "SELECT *"
I'm really at a loss here, so i hope someone can help me.

I'm writing an application in VB and i want to search through a database on the server.
I have a table called TEST_TABLE, it has three fields:

id, name, log

"id" is a BIGINT and the primary key
"name" is a VARCHAR(128)
"log" is a MEDIUMTEXT

The table has 5000 entries.

When i perform a "SELECT * FROM TEST_TABLE" it takes almost 10 seconds to complete.

Is there any way to speed this up?

I already tried creating an index, but it doesn't seem to be possible to create an index that holds all the fields.

REGEXP To Find "foo-bar" And "foo Bar"
Does somebody know how to solve this:

select * form geo,places where geo.name = places.name

Where geo.name could be New York
and geo.places could be New-York

Count(*) In A Select Returns "1". It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost:

SELECT *,count(*)
FROM cancons c, musics m, discos d, r_discos_cancons rdc
WHERE c.c_id_music = m.m_id
AND rdc.rdc_id_disc = d.d_id
AND d.d_id_music = m.m_id
AND m.m_id = 24
GROUP BY c_id

note:
cancons (ca) = songs (en)
discos (ca) = cd's (en)
music (ca) = musician (en)

don't worry for the WHERE part. i need it because of the foreign keys.
this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think.

I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.

Select User's Templates Or Default Templates (was "How To Do This?")
I don't know if this is possible.

I have an email_template table, with id, user_id, template_id, subject, message.

There are default templates, marked with user_id = NULL. If a user customizes a template, that is copied with user_id = whatever their ID is.

So say there are 3 templates. Say user 1 has customized template 2. Then when the script lists the templates, it'll list template 1, the default, their customization of template 2, and the default template 3.

How can I do this? Basically, select all templates with user_id = null, unless there's one with user_id = whatever the user's id is, then select that one.

I had it working with some group by clause but I'm not sure why it worked. It might have been unintentional. but the query was

select * from email_template where user_id = 1 or user_id is null group by template_id order by template_id;
It always returned the template with a user id if it existed, but I don't know why.

"AS" In A Select
Probably not understand this fully but for some reason when I do the following query below:

SELECT u.uid, u.username, u.password, m.uid AS moderator_uid, a.uid AS admin_uid FROM vid_users u, vid_moderators m, vid_admins a WHERE u.uid= ཆ' LIMIT 1

When I get the results, moderator_uid and admin_uid always is 1. Basically I want the query to look into 3 tables and all of them have a field called uid. I used the AS so it would look clearer on which uid I get. WHen the uid=4 moderator_uid and admin_uid is suppose to equal 4 also but why does it keep coming out as 1?

Select From Where Id="%"??
I am having the user select radio buttons for criteria, and I want the query to search the database based on the criteria.

suppose in a table there is a field called favorite_number, and I have a form where the user picks a number, and I want to return all table entries that have that number as a favorite i.e.

select from members where favorite_number='number'

where number is set with POST.

Now... If I want to have another option 'all' so the user can select number one through 9, or 'all' with which he can return all table entries regardless of the favorite number. How do I do this?

I know I can do this with an if/then - i.e. if number='all' then.. else.. but I am attempting a complex filter with four or five criteria and figure there must be an easier way.

What I thought I might be able to do was if the user selected all, set number to % and do a query with-where number="%", but it didn't work.

"LIKE" In SQL Select
I need to get all results based on part of a string, eg:

"answer_2_0" or "answer_2_1"

so this is the SQL I made

SELECT blah,blah FROM table WHERE somehting=1 AND answer LIKE 'answer_2_%'

the problem is, I have results such as

answer_21_0
answer_22_1 etc

and they are returned.... I thought using the % AFTER the last "_" would work.

"Calculated Fields" In "
o I want to create a table "totals" with three following fields:
Field 1 => quantity
Field 2 => unit_price
Field 3 => quantity*unit_price

o I thought I could do something like:

=CODE=============================
CREATE TABLE totals
(
quantity INT,
unit_price DOUBLE,
total DOUBLE AS quantity*unit_price
);
==================================

o It fails with the following error:

=REPORT===========================
ERROR 1064 (42000):
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 'AS quantity*unit_price)'
==================================

Question1: Is it possible to declare "calculated fields" at "table creation" ?
Question2: If yes, how to do so?

Maximum Execution Time Of 300 Seconds Exceeded (was "MYSQL Error")
I have a huge database 400mb+ in size which i have exported into a .sql file. I tried to run this export script into a new database and it runs fine until three quarters into the scripts execution and the following error appears:

ERROR 1064 (42000): 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 '<br / >
<b>Fatal error</b>: Maximum execution time of 300 seconds exceeded in <b' at li ne 1
I believe this error may relate to "delayed_insert_timeout = 300". How do I increase this limit in mysql - maybe it may solve the problem but then again it may not .

Any advice is most gratefully appreciated - As you can imagine if the script runs fine for 3/4 of it and then an error appears, stating the error is on line 1, it will take me days to go through the whole script (hundreds of thousands of rows) to find the error as I assume the error does not reside on line 1 as this line would have been executed.

I am running the script on mysql 5, and via source /path/****.sql on linux.

Error "Can't Find FULLTEXT Index Matching The Column List"
I'm trying to use fulltext search with mysql match() against() but i keep getting this error
Can't find FULLTEXT index matching the column list

even though i already altered the table to make the column im searching FULLTEXT

any ideas?

Design For Stock Items And Checkin/checkout Process (was "Query Help")
I'm currently in the process of trying to bodge together a simple(ish) stock system to be used on a company intranet but I've ground to a halt with a particular problem.

Using a MySQL database I've got tables with 11 fields, including 'Quantity' (self explanatory) and BookOut. Now this BookOut field contains information on the user who has booked out an item including the users name, the date/time etc

The problem I have is this. If an item has a quantity of 7 and a user books out 1, then his/her info is stored in BookOut but when another user books out another 1 of said item at a later stage then the previous BookOut info is overwritten.

Is there a way in that everytime another item is booked out that a new line is created within a specific field?

}

if(!sizeof($error)){

$update_SQL = "Update joystick Set BookOut = '" . mysql_real_escape_string($BookOut) . "', PCserial = '" . mysql_real_escape_string($PCserial) . "', Quantity = Quantity - " . $Quantity . ", MachineNo = '" . mysql_real_escape_string($MachineNo) . "', Date = now() WHERE ID = " . $ID;
if(! @mysql_query($update_SQL)){

$error[] = "Update SQL failed";

}
The above is the query I am currently using. I'm not totally proficient with .php and have already had a lot of help getting this far.

Query To Populate A Calendar (was "Wanted The Experts Opinions (that Means YOU!)")
I am building my first calendar for a client to list All their events on a month to month basis.

There are several tables of data that I need to query to populat the calendar from: Events, Committee Meetings, Lectures, and Group Actions.

I was wondering:

#1) Is it more effecient to query all the tables once and to store the results into an array that I can check as I loop through and output the days for the calendar?

#2) OR, is it better to query all the tables for each day as I loop through and construct the calendar ouput?

After formulating my question it seems like a no-brainer: That option #1 would be the most effecient, but I still would like to know your opinions. Especially if you have made a calendar before.

Searching A Comma Separated List Of Numbers (was "Brain Toot")
I'm at burnout point and simply cannot remember how to do this in a MySQL query.

I have as an input from a form:

Ƈ,4,6,8,55,74,11123'

Basically a comma separated list of numbers

The table I'm accessing has multiple records with a column called 'areas' this column for each may contain some of multiple of these input form numbers. e.g.

row 1 Ɖ,1,76,77,89,400'
row 2 Ƈ,2,7,8,55'
row 3 Ƈ,4,6,8,55,74,11123'

I need to return any row that has at least 1 matching value from the form input list.

I literally staring at the screen after a marathon coding stretch and cannot get the mind in gear.

Store Web Site Club Registration Data (was "MySQL Question")
I am working on a website for a club. One thing that they want included in the site is a way for people to register online for the club and their services and for this information to be put into a database for the owners to view. Is this something that I can do with MySQL?

Batch Mode Feedback Of "mysql" Command Line Tool
while writing a bash shell script to backup some databases and I am having slight problems with the mysql command line tool's feedback.

Here goes command line:

#!/usr/bin/env bash
mysql -ubackup -e "UPDATE `mydb`.`sysvar` SET `val`='backup' WHERE `var`='updateState' AND `val`='0';"

The backspaces are nescessary for the shell, otherwise it'd interpret whatever is inclosed in --> ` <--- as command.

The problem is, I need to know wether a line has changed or not. The command always returns 0 (that is a success in Unix/Linux) if it was successfully executed, no matter wether or not a line had been changed, and produces no output at all.

Could anyone tell me how I get that info?

Full Text Search - Un-stopping Default Stop Word "first"
using a full text query with the word "first" does not return any rows. I would like to remove the word "first" from the default stop word list ?

Encountering 2013 Error ("Lost Connection To MySQL Server...")
I have a nightly job that performs backups on my MySQL servers. There are three production MySQL instances on different machines. My backups have been running successfully for months (until now).

However, on last night's backups for one instance, a couple of database/schemas (appear to have) backed up successfully, but the larger schemas failed, as follows:

=======================================
Backup of schema dhunt was successful in 0 seconds
Backup of schema mysql was successful in 0 seconds
Backup of schema support_appl was successful in 0 seconds
Error: Backup of schema support_dict was not successful (1115 seconds):
mysqldump: Got error: 2013: Lost connection to MySQL server during query when retrieving data from server
Error: Backup of schema test was not successful (0 seconds):
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect
=======================================

I re-ran the backup cron job with the same results. Does anyone have any troubleshooting suggestions?