MYSQL :: Syntax For "One Or Zero" Characters In LIKE Statement?

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?

Find A Field With "x Amount Of Characters"
Once again I believe I am overlooking the obvious

I am trying to create a query, in which the "WHERE" clause looks for a fixed amount of characters in a field.

A simple example:
SELECT user.id, phone.id FROM 'users' WHERE 'phone.id' (IS ONLY 7 CHARACTERS LONG)

I've tried a fixed amount of wildcards (ie: WHERE 'phone.id' LIKE "%%%%%%%") but it doesn't limit the number of characters to 7.

Am I missing the obvious - or is this not possible?
For the record, I'm using MySQL 5

Exporting & Importing "special" Characters
I'm trying to get some data from one MySQL installation to another (just upgraded from 4.0.15 to 4.1.9 - but have both installs available - and the data is still in the old install). The data was painstakingly typed and added to the old database, and has a lot of "special" characters - quotations, apostrophes, â„¢, Â©, and so on. When I do a dump from the old database, all of those characters are replaced by other odd characters that I don't want.

1. Dump data from a database such that characters such as â„¢ and Â© quotation marks, etc. are preserved?
2. Then import that data into a new database such that those characters are still preserved?

I'd really like to find a way to get the data from the one old MySQL to the new one without having to go into every field to "find & replace" all the odd characters that end up in place of what I need.

PS. I did think about just trying to "move" or "copy" the data files from one mysql-data-dir to the other, but that didnt' seem to work, at least not the way I did it.
PPS. In case it makes a difference - I'm working with OS X Server.

Sort By Non "" Characters First, Alphabetically?
If my field has something like this:

1. dez
2. eft
3.
4. hgt
5.

Is there any way to put the non-blank fields on top, sorted
alphabetically A-Z, and the blank fields on bottom in whatever order?

Subquery Gives Syntax Error (was "A Sql Problem")
i am in deep trouble while executing this query on the server having mysql version " MySQL - 4.0.25-standard"

The query is :- Select * from table1 where column1 IN ( "Select DISTINCT column2 from table2") ORDER BY column1 DESC limit 0,20
it gives no results , i have seen that both tables have records corresponding to them.

i have tried to eliminate quotes from the subqurey then it gives an syntax error

while it is working at my localhost. having mysql 5.0.27-community-nt

The query here runs as Select * from table1 where column1 IN ( Select DISTINCT column2 from table2) ORDER BY column1 DESC limit 0,20

if i put quotes here then it gives no results.

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?

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.

Help With "load Data Infile" Statement
I'm having trouble with the load data statement I've written. It's not returning an error, but no data is being imported.

This is what I'm running:

load data infile 'filename.csv' into table table.table1 fields terminated by ',' lines terminated by '/r/n' IGNORE 1 LINES;

My csv file looks like this:

numberhitstypecodesitecountrydate
958375482033ABCDFGBR 20060410
384574905042ABCDFGBR 20060409
029485736353ABCDFGBR 20060409
049585785744ABCDFGBR 20060409
345039458392ABCDFGBR 20060409
049586489542ABCDFGBR 20060409

Using "HANDLER" Syntax Via C API ( NEWBIE)
I am looking at converting our xbase application (using open source
from www.xharbour.org )from Foxpro format DBF files to mysql. I have
purchased the O'Reilly book "MySQL Reference Manual" and have studied
the C interface to mysql. One thing missing from the book is how to
handle the version 4.0 "HANDLER" high speed access.

Do I just use

mysql_query(&mysql, "HANDLER myTable OPEN");
mysql_query(&mysql, "HANDLER myTable READ .....") ;
.... fetch row
.... process
mysql_free_result(...)
mysql_query(&mysql, "HANDLER myTable CLOSE");

or is there an undocumented ( in the book ) C API call to "HANDLER".

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

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?

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?

Using "HANDLER" Syntax Via C API
I am looking at converting our xbase application (using open source
from www.xharbour.org )from Foxpro format DBF files to mysql. I have
purchased the O'Reilly book "MySQL Reference Manual" and have studied
the C interface to mysql. One thing missing from the book is how to
handle the version 4.0 "HANDLER" high speed access.

Do I just use

mysql_query(&mysql, "HANDLER myTable OPEN");
mysql_query(&mysql, "HANDLER myTable READ .....") ;
.... fetch row
.... process
mysql_free_result(...)
mysql_query(&mysql, "HANDLER myTable CLOSE");

or is there an undocumented ( in the book ) C API call to "HANDLER".

SQL Syntax For "source"?
Is there an SQL statement that does the same as the "source" command in the mysql command line?. For example I have a file named "sql.sql" and want to execute all SQL statements in it.

"ERROR 1064 (42000): You Have An Error In Your SQL Syntax " When Trying To Import Backup
Just upgraded to the latest version of mySQL. Before I did that, I did a full backup of all tables using the Administrator tool. Now when I try to do a restore, I get the message in the subject. Even if I copy-paste the SQL code into the command line client, I get the error. For example, I use this code:

CREATE TABLE "news" (
"news_id" int(4) NOT NULL auto_increment,
"news_subject" varchar(96) NOT NULL default '',
"news_author" varchar(96) NOT NULL default '',
"news_email" varchar(96) NOT NULL default '',
"news_body" longtext NOT NULL,
"news_time" int(10) NOT NULL default '0',
PRIMARY KEY ("news_id")
) TYPE=MyISAM DEFAULT CHARSET=latin1;

And I get this error:

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 '"news
" (
"news_id" int(4) NOT NULL auto_increment,
"news_subject" varchar(96' at line 1

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.

Where A=1 And Either B=2 Or C=2 (was "sql Statement Help")
SELECT name FROM table WHERE A=1 AND B=2 OR C=2

I would like to get the name when A=1 and either B=2 or C=2.

Is There A "contains" Sql Statement?
I'm trying to run a query where i'm testing if one of the fields contains a phrase, s/thing like this:

select *
from myTable
where companyID='5'
and company_role contains 'admin'

how could i go about translating that "contains" statement to some real sql?

"in" Statement
i have written a programm in java using an oracle database. Now i want to use it at home with a mysql database. The problem is that mysql doesn't support the keyword in . would be nice if somebody could help me.

SELECT * FROM PARTNER p WHERE p.PID in
(select PID FROM LIEFERT where AID ='"+getAid()+"') ";

It's possible that there are more rows than one in the Liefert-table.

"SQL Syntax Error" (Error: 1064)
I've been trying to use mx-systems to make a phpbb portal lately. I've already posted on their site, but the error is an "SQL Syntax Error", so I figure you guys would be just and helpful (if not more helpful).

The error message looks like this:

General Error

Insert boring error message here.

DEBUG MODE

SQL Error : 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 'column col WHERE page_id = 1 ORDER BY column_order' a ....

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

Sql Syntax"
This drives me nuts. Whenever I try importing my database from my website onto my home computer, I get these errors. I know the syntax is ok, because if I breakl the import into many small steps, then I get it to work. But that is so tedius. So the errors have something to do with it being a large query. Drives me nuts!! For example, it has a problem with

#
# Table structure for table `nuke_headlines`
#

DROP TABLE IF EXISTS nuke_headlines;

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

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?

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

Syntax Error Near â€˜Show= 'Y"
I am trying to create a query within a coldfusion page which filter records flagged not to show.

I have a table with a filed called SHOW set enum with values either Y or N

When running the same SQL I used with the page that runs from Access, as below, the error I get a syntax error near â€˜Show= 'Y" ?

SELECT * FROM tblEstate where show = â€˜Nâ€™ ORDER BY DisplayOrder ASC

What is the equivalent to Accessâ€™s Ture/False?

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 ' 
Fatal error: 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?

Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this:

WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00"))

Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like:
Day Min Max
2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59
2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68
where at this point i`m getting min=40 and max=68

"supplied Argument Is Not A Valid MySQL Result Resource" Error
i am getting this error with the following code:

$query = "SELECT title, firstname, surname, address1, address2, address3, postcode,"
."tel, mobile, email"
."FROM user"
."WHERE username='$valid_user'";

us.skill_id=s.skill_id"

$result=mysql_query($query);
$num_rows=mysql_num_rows($result);
$row=mysql_fetch_array($result);

The error is referring ot the last 2 lines of this script.

Unable To Connect To Server (was "Novice User Problem")
I've been reading Kevin Yank's Build Your Own Database Driven Website and am unable to connect to the MySQL server as he describes in Chapter 4 (p. 74-75).

After modifying his code to produce a mysql_error, I get:

Unable to connect to server at this time.Client does not support authentication protocol requested by server; consider upgrading MySQL client

Even though I am using the latest MySQL server (5.0).
My
?php

$dbcnx = @mysql_connect('localhost', 'root', 'my_actual_password');
if (!$dbcnx) {
exit('Unable to connect to server at this time.' . mysql_error() . '');
}

if (!@mysql_select_db('ijdb')) {
exit('Unable to locate the joke database at this time.');
}

?>
Here are all the jokes in our MySQL database:</P.
<blockquote>
<?php
$result = @mysql_query('SELECT joketext FROM joke');
if (!$result) {
exit('Error performing query: ' . mysql_error() . '');
}
while ($row = mysql_fetch_array($result)) {
echo '' . $row['joketext'] . ''
}
?>

Linking Stores With Products (was "MySQL Dificult Code")
I have a MySQL database dificult (for me anyway..).
I have a ecommerce site with a table of products.
Now I want to create a new table with a list of stores where clients can find near home those products. In a limit situation I can have a list of all products indexed in one store (and the products list is increasing everyday, the same with the list of store...). I want this feature to be capable of introdution of new products in a store that already as several products.
My idea is to develop 3 tables:

Products
+----+-----------+--------------+
| id | name | description |
+----+-----------+--------------+
| 1 | xxxxx | aasa... |
| 2 | vvvvvv | asasasasas... |
+----+-----------+--------------+

Stores
+----+-------------------+-----------+
| id | store | address |
+----+-------------------+----------+
| 1 | aaaaa | street1... |
| 2 | bbbbbb | street2... |
+----+-------------------+-----------+

Linking stores with products
+----+-------------------+-------------+
| id | store id | products id |
+----+-------------------+-------------+
| 1 | 1 | 1;2 |
| 2 | 2 | 2 |
+----+-------------------+-------------+

Can I implement this in MySQL? I mean how can (if I can) place several ids in a field? and how can I do the query in order to list the products inside a store?
Hope that I made my self clear. If not please do not hesitate to request further information.

What Is Execution Time Of A Query Based On? (was "a Mysql Question")
when selecting data from the database, does the time taken to retrieve it vary from 56k connections to T3 connections? or does it all depends ont he general server speed/amount of connections on the db?

im not sure if ive explained that in the best way for people to understand
but im sure someone will get what i mean

List All Clients Not Assigned To Current User (was "Query Help Please")
I have two tables like this:

*clients
-- client_id
-- client_name

*manage
-- user_id (multiple entries possible)
-- client_id

To assign clients to users there is a entry in the manage table of the
user_id and client id.

I want to list all the client_ids and client_names that are not assigned to the current user_id say 1.