MYSQL :: Alternate Solution To "intersect" In A Single Table...

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?

Relations In "CASCADE", "SET NULL", "NO ACTION", "RESTRICT", "--" ?
what do they mean to set the relations of a Foreign KEY in "CASCADE", "SET NULL", "NO ACTION", "RESTRICT", "--" ?

Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop.

$sql = "SELECT * FROM mytable order by points desc limit 10";
$rec = mysql_query($sql) or die(mysql_error());
$datas = mysql_fetch_array($rec);

do{
$sq = "Select * from secondtable where linkid = '$datas[id]'";
$rst = mysql_query($sq) or die(mysql_error());
$rows = mysql_fetch_array($rst);
echo "$rows[somefield]";
}while($datas= mysql_fetch_array($rec));
This works perfectly but I want the second query to be out of the loop if there is a way and how.

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?

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.

How Can I "see" A Table In A Database On A Remote MySQL Server After Creating It
I used the following SQL to create a new table in a database on
a remote MySQL server by copying one already there. I know the
table exists SOMEWHERE in cyberspace. I can read its data, write
to it, delete from it. But I cannot see it. The only way I know it
exists is by running this SQL from Access 97 pass through query:

SELECT ALL new_tbl.name FROM new_tbl

The database resides on a MySQL server that was created with
a single table (named test) in it for testing purposes. I ran the
following SQL to create another copy of the table in the same
database named: new_tbl

CREATE TABLE new_tbl SELECT * FROM test;

I cannot see the new table in the Access 97 database window
under the Tables Tab. Anybody know how to overcome this?

Its a severe drawback to programming efforts not to KNOW what
tables are in your database.

Multi-table UPDATE Problem (was "mysql Question")
$sql = "UPDATE char_lair , char_main m SET l.zip_id='$zip',
l.lair_addr='$address', m.alias='$alias', m.real_name='$name',
m.align='$align' WHERE m.id = $cid AND m.lair_id = l.id";

I am using mysql v.3.23, I got error in this code but can't find the solution.
The message displayed in browser is:
---------------------------------------------------
UPDATE char_lair , char_main USING char_lair l, char_main m SET l.zip_id='z3', l.lair_addr='a3', m.alias='ch3', m.real_name='rn3', m.align='good' WHERE m.id = 2 AND m.lair_id = l.idYou have an error in your SQL syntax near ' char_main USING char_lair l, char_main m SET l.zip_id='z3', l.lair_addr='a3'' at line 1
=========================================
Is there any syntax error?

"table Is Read Only" After Copying Table Files To New Server
I'm trying to port my MySQL tables for a database called "tracerlock" from
one server to another. On the old server, in the /var/lib/mysql/tracerlock
directory, there was a ".MYD", ".MYI" and ".frm" file for every table in
the database. So after creating a database called "tracerlock" on the new
server, I copied these files over to the /var/lib/mysql/tracerlock
directory on the new server. On both servers, all the table files are
owned by user "mysql" in the "mysql" group.

Now, when I connect to MySQL on the new machine and load the tracerlock
database, "show tables" shows all the tables as expected, and I can perform
SELECT statements on them and get the correct results. But when I try
doing INSERT statements on one of the tables that was copied over, I get:

mysql> INSERT INTO test VALUES(3);
ERROR 1036: Table 'test' is read only

However, if I create a new table called test2, I can perform INSERT
statements on it with no error:

mysql> insert into test2 values(3);
Query OK, 1 row affected (0.00 sec)

Code:

Please Explain MySQL Distinct Table Types (was "Where To Use ?")
These are the MySQL distinct table types.

MyISAM
MERGE
ISAM
HEAP
InnoDB
BDB or BerkeleyDB Tables

so please anyone help me to find out the use of each n every type.
it will be very helpfull for me if you people can provide simple meanings rather than technical.
For Ex : why to use MERGE over HEAP or something like this
Your help is most welcome

Search Script That Searches "linked" Table
I'm designing an ecommerce store, and I am on to the "search the site" script. I had a simple script set up, but its missing an important part of the search.

The way the (2 relevant) tables are set up are like this:

product_information
Field Type
ID int(10)
product_code varchar(30)
product_summary text
categoryID tinyint(3)

category_lookup
Field Type
ID tinyint(3)
category varchar(25)
parent tinyint(3)
product_code is the product's unique number, category ID refers to the ID of the category that product belongs to (socks, shoes, etc. etc.), and `categoryID` is equal to the category_lookup field `ID`.

What I would like to be able to do is if someone searches socks, any product that has 'socks' in the product_summary or the category it belongs to would be returned. Is this going to be too taxing on the server? I'm new to doing queries at this level so I can't put the query in to the proper syntax. Is there a way to do this all in one efficient query? Or since I'd have to do a lookup for each product in the database, would it be better to first check to see if any of the keywords are in the category_lookup table, and return the ID's if they are. Then I can include just those ID's in the where clause?

Any TABLE TYPE Be Able To Save And Retrieve "Diagram"?
I'm seeking a solution that is able to:

1. Draw a diagram (such as an organization chart or cause-and-effect diagram) and fill nodes with related data.

2. Then, save the data and diagram into MySQL.

3. Finally, retrieve both data and diagram from MySQL and display them in corresponding places.

Limiting Fields In "show Table Status"
show table status like "products";

Sometimes this is too much information and I was looking for a way in MySQL to select only the columns you wanted to see like you could do with a select.

I know I can use PHP or Perl to parse this and output only what you want but wanted to see if there was a fairly easy
way to do so in MySQL. For example if I wanted to see the
Name, Rows, and Auto_increment fields only.

ERROR 1206 (HY000) At Line 41: "The Total Number Of Locks Exceeds The Lock Table Size"
I'm loading a large file into a table, and I get this error:

"ERROR 1206 (HY000) at line 41: The total number of locks exceeds the lock table size"

I already searched, and the only advice given was to do an explict full-table lock on that table before loading to avoid the row-by-row locks filling up the lock table. Well, I tried that, and it's not helping either.

Here's my log:

Table "hard Links" Under Windows
I am attempting to create "Symbolic" or as windows calls it "hard links" for some tables in a database.
I have 2 databases, db1 has a users table that I created a 'hard link' to in db2.
I created this using this program which ads shell intiration for hard linking: http://www.elsdoerfer.info/=ntfslink

The hard-link appears to work, I tested with a simple .txt file and i can make changes to one file and open the other and see them. The 2 databases were working fine for awile after I created these hard-links, then things started to go wrong.

It seems whenever I try to add a new row to the users table in db2 (with the hard-link) the change doens't show up in the same table in db1. It also corrupts the table. And same the other way, I added a bunch of users into the table in db1, then did a CHECK on the users table in db2 and it gives me this "Size of datafile is: 6872396 Should be: 6874300". So it seems part of the data is being written, since it knows what it should be.

I read that one can do this type of setup in linux with symbolic links, maybe it doens't work with windows hard-links? It seems it should... maybe i'm missing something?

Table Does Not Exist (was "Database Error")
Database error: Invalid SQL: update sponsors set status=-1 where end_date<=&#55614;&#57158;-07-22'
MySQL Error: 1146 (Table 'chinese.sponsors' doesn't exist)
Session halted.

What does this mean ?

Error In "create Table Keyword_relevance"
PHP

environment

mySQL4.0
win XP
codlFusion Server

The following code is basically fom http://r937.com/keyword_relevance.html.

<cfquery datasource='ds'>

create table keyword_relevance
( id tinyint not null primary key auto_increment
, Description varchar(99)
);
insert into keyword_relevance (Description) values
('I like to play games')
,('One game, two games, eh')
,('There''s no keyword')
,('The games keyword is present only once')
,('The games keyword is present only once in a longer sentence')
,('The games keyword is present only once in a really really really really really really long sentence')
,('Games games')
,('Games beautiful games')
,('')
,(null)
,('Games')
,('Games ')
,(' Games');

</cfquery>
The code above produces an error at http://dot.kr/test/01.cfm
The error occures maybe in "create table keyword_relevance"

What Does CREATE TABLE Phpbb_anti_robotic_reg Mean? (was "Mysql Help")
Hey im trying to install a hack onto my forum, which isnt really the important part. Ive done the steps until i come to the MYSQL part which reads the following:

Quote:

CREATE TABLE phpbb_anti_robotic_reg (
session_id char(32) DEFAULT '' NOT NULL,
reg_key char(5) NOT NULL,
timestamp int(11) unsigned NOT NULL,
PRIMARY KEY (session_id));

"hard" Sort Table Order
Is it possible to resort the rows in a table like in a query where using
ORDER BY, but have to new sort order stored in the table structure?

How Do I Restore A Table After Its Been "dropped" Using Phpmysql ?
what a tragic mistake i made by clicking on the "drop" button instead of the "empty" button. Now how can i restore that table?

"hard" Sort Table Order
Is it possible to resort the rows in a table like in a query where using
ORDER BY, but have to new sort order stored in the table structure?

Suspected DB Table "crash"
A table in one of DBs I believe has crashed (for lack of a better word).

No matter whatever I use to access it, PHP scrpt, PHP My Admin, SQLyog freezes when the table (PHPMyAdmin "freezes" when selecting the DB with the suspect table in it) is selected for repair, to analyze, or any other operation.

Is there anything I can do to "fix" this? I do not have SSH access to the server so that is a constraint.

1000 Tables, Or One Table? (was "Best Way To Do This?")
I have 1000 items that I want to keep daily stats on like number of downloads etc. Is it best to make 1000 tables, one for each item and make a row for each day or is it best to make one table with a row for for each item and use something like serialize to store the daily numbers?

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?

"create Table" Speed
I'm wondering if there's any way to speed up create table queries?
Besides upgrading hardware, that is. The very simplest table creation
query, "create table table1 ( field1 INT(10))" is taking about .03
seconds, which compared to other queries (large inserts at .01 seconds)
and previous experience appears inordinately long. Further, it appears
that most of that .03 seconds is some kind of overhead, since the
complexity of the create table query appears to be irrelevant.

Running: Debian Woody with dotdeb packages.

Rename Table "bedrijf-2"
i export a access table to mysql 5 and named it 'bedrijf-2'

it is not related to any other table. I cannot delete it , rename it.
No delete or rename syntax worked. it gave the error about the -2.

can someone show me a way how to delete it?

"The Table Is Full" Error.
I have been looking through the logs for our content management system, and have found this error: "The table 'CMS_BACKUP_FILES' is full"

This is a table in the mysql database that is running on the same machine as our intranet. Can anyone help me resolve this issue as I have zero to limited MySQL experience and training.

"left Join" In One Table And Not In Another
I have three tables joined on key fields: delivery is joined with invoice_detail on delivery_id and with location on loc_id. I want to return records of deliveries that have corresponding records in the location table, but have no corresponding records in the invoice table.

Here's the query I'm attempting to use:

NOW() Or Date("Ymd") To A Table Column
I recently started using MySql for my clients Web Pages. (Newbie) Iâ€™ve tried every trick possible to insert the DATE automatically without the user having to enter the date they fill the form. I have used Java, PHP and SQL instructions to fill the table column and nothing happens. Maybe is something with the DATE format column? All other table columns get the information is entered by the person filing the form. Is there something Iâ€™m doing wrong?

I have verifies that the DATE variable info is collected through PHP and Java, but the instruction to INSERT INTO does not takes the DATE info into the column.The field stills empty.

"marking" A Line In The Table
Is there any way to "mark" a line that has been recently replaced.

What I want this to do is everytime a line is replaced, I would like to somehow mark it so that my query can somehow tell which line has been recently replaced and which one hasn't... (Not my idea, boss's....) My idea was to somehow put a 1 in a designated column when the line was replaced, and then once it was read that would turn to 0 or NULL. Then I would set up my read to only scan for 1's in the designated column... Is this possible or something close to this?

"DESCRIBE Table" Not Working
Hi, I've got DMX2004 with Impakt 2.6.2 and MySQL 4.0.16 with ODBC
3.51.06. I am trying to get tables descriptions in order to alter
their columns. As following MySQL manual, I use for instance "DESCRIBE
table" or any other method like "SHOW COLUMNS FROM" but it does not
work, i get the following 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 'LIMIT -1' at line 1"
while it works perfectly with MySQL control center or phpMyAdmin.
So logically it should be due to PhAkt.

"DESCRIBE Table" Not Working
I've got DMX2004 with Impakt 2.6.2 and MySQL 4.0.16 with ODBC
3.51.06. I am trying to get tables descriptions in order to alter
their columns.

As following MySQL manual, I use for instance "DESCRIBE
table" or any other method like "SHOW COLUMNS FROM" but it does not
work, i get the following 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 'LIMIT -1' at line 1"
while it works perfectly with MySQL control center or phpMyAdmin.
So logically it should be due to PhAkt.

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

Grab 'title' From The Table 'forum' Within The Same Query (was "Help With Query")
I have the following query for my vBulletin database:
PHP

$get_stats_newthreads = $db->query_read(" SELECT thread.forumid, thread.postuserid, thread.postusername, thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.iconid, thread.views, IF(views<=replycount, replycount+1, views) AS views, thread.visible FROM " . TABLE_PREFIX . "thread AS thread WHERE NOT ISNULL(thread.threadid) AND $weekold<lastpost AND thread.visible!=0 AND (forumid=34 OR forumid=7 OR forumid=8 OR forumid=11 OR forumid=10) ORDER BY rand() DESC LIMIT 5");

and would like to grab 'title' from the table 'forum' where forum.forumid=thread.forumid

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

Table "in Use"?
Not sure if this is a MySQL question or a php question, but I'll start here since I think it has to do more with the database...

When I view a particular database in phpMyAdmin it says that the tables in it are "in use" and when I try and browse them an error is given saying that they don't exist.

Is there a way to fix this or are they corrupt?

There's a screen shot attached also... just in case...

Table "IN USE" At ISP.
I have a table that phpmyadmin gives a status as "in use". Nothing can be done with this table and I can not even drop it. I have limited access since this is a hosted solution at an ISP. The ISP has no idea what to do about this. Therefore I am turning to the experts to see if you all can help. I have done a search and have seen a similar problem but there was no fix for this.

Anybody have any clues or answers for this?

I also have another site with the same issue. (I think the host had an issue with MySQL at one point that caused this.) They have restored this from an older backup but this new database has all of it's tables in use. The one I talked about at the begining has only one table out of two in this state. The other table works fine.

A "" In A Table
We have a third party company who sends us DB data for us to display. They have multiple tables for cross referencing. One of the problems that I am running into is the fact that they use a "D" as one of their records in one of the tables. When I go to cross reference other tables based on this value, MySQL throws back an error because of the "". Any ideas on a way to get around this?

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.