MYSQL :: User_id And Password Error? (was "mysql Query")

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

ERROR 1045: Access Denied For User: '"root"@localhost' (Using Password: YES)
I have been working on this for three days (about 16 hours) to no avail. I have root access on a dedicated server running whm/cpanel on Redhat 9. I have followed every instruction from mysql.com as well as every suggestion I've been able to find.

These are the two errors I bounce between:

ERROR 1045: Access denied for user: '"root"@localhost' (Using password: YES)

and

ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

I'm out of ideas.

Invalid Use Of Group Function (was "Baffled By Query Error")
trying to figure out why I keep getting this error with the following query:

SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins
FROM account a LEFT JOIN calls c ON c.account_id = a.id
WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2
AND SUM(c.agent_product_time) >= '500' GROUP BY c.account_id ORDER BY mins

ERROR: #1111 - Invalid use of group function

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?

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.

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?

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

Error: MySQL Client Ran Out Of Memory (was "Urgently Need Help")
I'm having a big trouble with this query
select *, UNIX_TIMESTAMP(date_submitted) as postedon,
UNIX_TIMESTAMP(DATE_ADD(date_submitted,
INTERVAL auction_period DAY)) as expiry
from
bidsnbuys_products
where
approved='yes'
It works before but lately we've got a huge records and if
our customers search a product it will give them an error,
I even tested by myself and the error is

MySQL client ran out of memory
Is there a better mysql query with my query above?

Error Importing The .sql File (was "MySQL Problem")
I ahve just exported about 8-10 of my tables from one database into a .sql file and then tried importing the .sql file into phpmyadmin on my local localhost serer and am getting this error?

You have an error in your SQL syntax near 'ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1

INSERT INTO `atm_areas` ' at line 22

Why?

Getting Dreamweaver 8 To Work With MySQL: The "Unidentified Error"
I set up MySQL on my Macintosh at my job (G4 / OSX 10.3.9) 3 days ago, along with PHP --

all went fine, & I got MySQL started, a DB set up -- until I tried to connect Dreamweaver to it I go to connect to the DB in Dreamweaver 8, and when I try to set up the DB in the "databases" tab and click on the "database" field I get :

"An unidentified error has occurred" Code:

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

Adjacent Theater Seats (was "mySQL Query Issue")
Having not been working with more than a simple SELECT * from table query, I am having a few issues when it comes to using more complex queries.

I am currently using a query to select seats from a database using the following query:

$query1 = mysql_query("SELECT struct.seatid, struct.row, struct.seatnum FROM seats as seat LEFT JOIN structure as struct USING (seatid) WHERE seat.showtimeid=".(int) $_GET['id']." && seat.available = Ƈ' && seat.timelimit < $minutes2 ORDER BY seat.seatid ASC");
For each record that is selected I want to check if either of the seats either side of it are also selected, if not then it is not to be shown.

I have added a while loop below the above query and run another query following it (not sure if that makes sense, so I will paste it below!)

while($rows = mysql_fetch_assoc($query1)){

$minusseat = $rows[seatid] - 1;
$plusseat = $rows[seatid] + 1;

$queryminusseat = mysql_query("SELECT * from seats LEFT JOIN structure WHERE seat.showtimeid=".(int) $_GET['id']." && seat.available = Ƈ' && seat.seatid == $minusseat && seat.timelimit < $minutes2");

$num_rows = mysql_num_rows($queryminusseat)or die(mysql_error());

echo "$num_rows";

//if($minusnum > 0){
//echo "$minusseat " . $rows[seatid] ." $plusseat ";
//} else {
//echo "$minusseat " . $rows[seatid] ." $plusseat ";
//}

}
The code above is used below the initial query and the following error is being displayed:

Quote:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:Program Filesxampphtdocsprojectprototype1 ickets.php on line 154
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 'WHERE seat.showtimeid=1 && seat.available = Ƈ' && seat.seatid == 1 && seat.time' at line 1

Bugzilla: MySQL Returns "Got Error 127..."
Our bugzilla database started giving this error during last week sometime. "Got error 127 from table handle". I Googled it, and came to believe that "repairing" and "optimizing" the table should sort out my problem, only to be shocked to see that over 10 000 records have been deleted by the repair/optimise. You see, this is not an option. We have over 18 000 bugs logged on our system, and as an organising our size, we need to comply to certain SAS70 requirements. Losing 10 000 records on our bugs database will fail the entire organisation the SAS70 reward, and guess what... it's on my plate, and if I can't do anything about it... people will talk bad about me for long after I've left the company.

I understand that the table records has gone corrupt. How it went corrupt I don't know and why it was working fine with over 10 000 corrputed records for the last 2 years, I also don't know. But right now it's not my job to understand it... I need to fix it, and keep every record in tact.

I've done a restore of the data all the way back to the day just before it crashed. When I ran a repair and optimize then, it did the same thing... over 10 000 records dissapear. PLEASE HELP. I am despirate!

"Illegal Mix Of Collations" MySQL Error
What I am attempting to do with an SQL query is grab data from multiple tables for outputting to an Excel spreadsheet file using PHP. Each row should show details for each member, as queried from multiple tables (using the field 'PersonalID' as the primary/foreign key). The query I have is as follows:

SELECT tblcoreuser.PersonalID, tblcoreuser.usrForename AS Forename, tblcoreuser.usrSurname AS Surname, tblcoreuser.voucher AS Free_Status, tblcoreuser.Joined AS Date_Joined, tblcoreuser.Package AS Membership_Package, tbladdress.Address1, tbladdress.Address2, tbladdress.County, tbladdress.Postcode, tblcredits.numCredits AS Remaining_Credits

FROM tblcoreuser, tbladdress, tblcredits

WHERE tblcoreuser.PersonalID = tbladdress.PersonalID AND tblcoreuser.PersonalID = tblcredits.PersonalID

ORDER BY tblcoreuser.PersonalID DESC;
The error being returned is:

Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

I've tried removing certain parts of the query, but cannot seem to pinpoint the exact cause of the problem. I'm sure I'm missing something simple -

Why Did INSERT DELAYED Get An Error? (was "Mysql Help!")
I have a mysql databae that gets pounded with INSERTs to basically an address book.

1) Is this high:
Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.
Traffic Ã¸ per hour
Received 37,793 KB 2,581 KB
Sent 1,128 MB 78,912 KB
Total 1,165 MB 81,493 KB
Connections Ã¸ per hour %
Failed attempts 6 0.41 0.01 %
Aborted 0 0.00 0.00 %
Total 71,281 4,867.81 100.00 %

Next And Prev DisplayOrder Values (was "Help With MySQL Query")
I have a table 'Album' containing columns 'PageID' and 'DisplayOrder' (both INT).
On each page that is displayed I want to have 'previous' and 'next' links to other pages, which are arranged via the DisplayOrder column.

At the moment I'm doing this in 2 separate queries:

SELECT DisplayOrder FROM Album WHERE PageID = $currentPage
$currentNo = [result of last query]
SELECT PageID FROM Album WHERE DisplayOrder IN ($currentNo-1, $currentNo+1)
Highly simplified but you get the idea. Would it be possible to combine this into one query?

Script To Change All Columns (was "mysql Query")
I have a table. I need to make some changes like change column name, change int() to var() etc. So I want to write a query that alter all the columns of the table then the data which were in the database before can be reinserted.

"Lost Connection To MySQL Server During Query"
Bog standard SuSE 8.2 Linux install.

I have installed mySQL (off the SuSE 8.2 distro CDs). I can connect locally,
e.g:

bealzebub> mysql -u root -p mysql -h localhost

....logs me in just fine. However, this:

bealzebub> mysql -u root -p mysql -h bealzebub

....causes the connection to fail with: "ERROR 2013: Lost connection to MySQL
server during query". Its the same if I try to log in from another machine.

"Lost Connection To MySQL Server During Query"
Hi, I wonder if anyone can help me with this.

Bog standard SuSE 8.2 Linux install.

I have installed mySQL (off the SuSE 8.2 distro CDs). I can connect locally,
e.g:

bealzebub> mysql -u root -p mysql -h localhost

....logs me in just fine. However, this:

bealzebub> mysql -u root -p mysql -h bealzebub

....causes the connection to fail with: "ERROR 2013: Lost connection to MySQL
server during query". Its the same if I try to log in from another machine.

Anyone?

DEFAULT CHARSET Error (was "Odd MYSQL Error!! :(")
I keep getting this error

PHP

Error
SQL query:

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 31, 2006 at 09:06 PM
-- Server version: 4.1.19
-- PHP Version: 4.3.11
--
-- Database: `jesse_game`
--
-- Table structure for table `games`
--
CREATE TABLE `games` (

`gameid` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`gametitle` text NOT NULL ,
`gamedesc` text NOT NULL ,
`gamekeywords` text NOT NULL ,
`gamefile` text NOT NULL ,
`gameheight` int( 11 ) NOT NULL default Ɔ',
`gamewidth` int( 11 ) NOT NULL default Ɔ',
`category` int( 11 ) NOT NULL default Ɔ',
`timesplayed` int( 11 ) NOT NULL default Ɔ',
`totalvotes` int( 11 ) NOT NULL default Ɔ',
`totalvotepoints` int( 11 ) NOT NULL default Ɔ',
`rating` int( 11 ) NOT NULL default Ɔ',
`gameicon` text NOT NULL ,
`gamestatus` int( 11 ) NOT NULL default Ɔ',
`playedtoday` int( 11 ) NOT NULL default Ɔ',
`gamelocation` int( 11 ) NOT NULL default Ɔ',
`iconlocation` int( 11 ) NOT NULL default Ɔ',
`instructions` text NOT NULL ,
`filetype` int( 11 ) NOT NULL default Ƈ',
`customcode` text NOT NULL ,
PRIMARY KEY ( `gameid` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =1515;

MySQL said:

#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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=1515' at line 23

PHP

#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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=1515' at line 23

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

"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

Not Sure How To Use Inner Join (was "MySQL Query Help Please...")
Basically i have 2 tables users and listings. In users table there is user's info like username / password etc. and in listings table there are listings of a user..which can be more than 1.

Currently I was just getting results by just searching the listings table by a simple query like: "SELECT * FROM listings WHERE title = 'abc'"; but now what I want do is that I can search for some fields in user table also.

For eg. currently listings are displayed..but now i should be able to see all listings by searching username of post code. So please tell me how do i go about it ? I know that it can be done using INNER JOIN but i am not sure how to use it.

Can You Use "UNIX_TIMESTAMP" Outside The Mysql Query?
Hey guys. I was using this code:

mysql_connect($dbhost, $dbuser, $dbpassword) or die("Couldn't connect to server.");
@mysql_select_db($dbname) or die("Couldn't connect to database.");

$q="SELECT poker_room,game_type,limit_type,UNIX_TIMESTAMP(date_time),date_time,buyin_desc,description,url FROM poker_tourneys ORDER BY date_time ASC";
$sql=mysql_query($q);
$num=mysql_num_rows($sql);

mysql_close();

Then using this code to get the results:

while (list($poker_room,$game_type,$limit_type,$dt,$dt2,$buyin_desc,$description,$url)=mysql_fetch_array($ sql)) {
// Blah
}

But I need to only display certain results so depending on the page they are on.. so I was wanting to get each results values with the "mysql_result()" funtion; but I can't figure out how to get the UNIX_TIMESTAMP to work this way.....

Help Me About Error "MySQL AB :: MySQL 3.23, 4.0, 4.1 Reference Manual :: A Errors"
As I was uploading my file to the data base, thi meaasge shows "MySQL AB :: MySQL 3.23, 4.0, 4.1 Reference Manual :: A Errors",

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?

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?

How To Change "#1226 - User 'root' Has Exceeded The 'max_questions' Resource" Error
I seem to have set a max_questions variable on my system and my admin console and command prompt don't work with this response:

"#1226 - User 'root' has exceeded the 'max_questions' resource"

How to fix?

And how do I not make thisw mistake again?

Error "cannot Connect Server On Local Host".
i cannot connect to MySql database at my server.F.Y.I i install MySQL 5.0 in my server 2003.when i try to connect from other pc its show error "cannot connect server on local host".how can i solve it.

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.

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

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:

Too Many Tables Error (was "I Feel Like I Am Missing Something")
I am new to administrating servers, and I just got myself a dedicated server and I am trying to do some things...

Right now I wanted to make a backup of a mysql db but I get an err 24 because I the db has too many tables (over 16000).

I want to edit the open_files_limit so I can get past this error, but I dont know where to edit!

All the places I read say I should edit my.cnf, so I use vi /etc/my.cnf and I get this:

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
After searching around this doesnt look right because all other files I've seen have way way more info in them.

Did I miss a configuration step somewhere, or am I editing the wrong file?

I am running Fedora Core6 with plesk.

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.

Error : "cannot Be Part Of FULLTEXT Index"
in order to make it possible to do a fulltext search on multiple columns I created a temporary table which holds all the fields I want to search in from different tables. I'm creating a temporary table, create an fullindex on it, search in it, drop it. On our local server this works fine. But when I perform the code on the server of our client I get the following error message:

"Column 'manufacturertitle' cannot be part of FULLTEXT index"

This is caused by:

"CREATE FULLTEXT INDEX full_index ON SearchTemp(producttitle, description, description2, manufacturertitle );"

In the original table the field 'manufacturertitle' has the following properties:
Type: varchar(255)
Collation : utf8_general_ci
Null : Yes

I found the following solution somewhere but I have no to clue what to do now:
"As of MySQL 4.1.1, full-text searches can be used with most multi-byte character sets. The exception is that for Unicode, the utf8 character set can be used, but not the ucs2 character set.".

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"

"Unable To Save Result Set" Error
I keep getting the following error.

Warning: mysql_query() [function.mysql-query]: Unable to save result set in /home/mysite/public_html/demo.php on line 303
Invalid query

That error is this line, which you can see at the bottom of the code below:

$result = mysql_query($sql) or die("Invalid query") . mysql_error();

Here is the PHP Code:

"system Error 5, Access Denied"
I have just installed mysql, and get this error when I type "net start mysql" in command prompt. I am using ms Vista. Is this due to not having access rights to the directory? Have tried unchecking read only for the relevant directories but although it seems to accept this, when I check again, the read only is still checked!

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.

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.

Unique Combinations Of Model And Make (was "Query Question")
I'm trying to write a query and was wondering if someone could help me formulate it. Here is an example table I'm working with:

Table: vehicles
Model Make
Ford Taurus
Ford Taurus
Ford F150
Chevy Tahoe
Chevy Blazer
Chevy Tahoe

I want a query that returns all unique combinations of model and make. So the result set should be:
Model Make
Ford Taurus
Ford F150
Chevy Blazer
Chevy Tahoe

I know how to return distinct values for one variable, but not for two like that. Any help is greatly appreciated.

Count Query Causing "excessive Processor Usage"
For the second time in the past 6 months my hosts have shut down my site because it was apparently causing a shared mySQL 5 server to max out at 100% usage. I'm not much a programmer, know even less about mySQL and obviously don't have access to the server from the back end but I'd appreciate any advice as to whether it is my query that is causing the problem:

Set rsMail = objConnC.Execute("SELECT * ,(SELECT COUNT(*) FROM comments WHERE comments.submissionID = submissions.submissionID AND comments.commentInclude = 1) as CommentCount FROM submissions WHERE PigeonHole = 'mailbag' AND Status <> 'hold' ORDER BY submissionID DESC LIMIT 20")

Apart from two instances, this query has been working fine on a page that receives between 10,000 and 30,000 visits a day.

The site is running on Windows and coded in ASP but is calling a separate mySQL server.

Most Products Flagged In The Past 24 Hours (was "Query Help")
I need a little help setting up the logic behind a query. Here is the situation:

I have 2 tables. Product table. Flag table (flagID, productID, date_flagged). Users can flag products. *date_flagged is a INTEGER unix timestamp.

I would like to be able to setup a query where I can pull up products that have been flagged the most in the past 24 hours.

At first I figured I would COUNT() the flags and GROUP BY productID, but this of course will only give me the most flagged all time, and because of the grouping by productID will not allow me to filter it by date.

Delete Except Latest 2500 Entries (was "help With Query")
I have a table that holds information on activities.
For each activity I store the following:
ID, When, Description
The ID is the key for the table
The When column holds the datetime when the activity was added

The table gets filled up pretty quickly.
I would like to run a query that will delete ALL the entries except for the 2500 latest ones. The When column should be the one to use when deciding which are the 2500 latest activities

Can someone show me the most efficient way of doing this?

Count User Comments In Each Category (was "help On A Query")
I would really appreciate if someone could help me out on this one. this are my tables:

comments

Comment_ID
Video_ID
Comment_Text
Comment_UserID
....
video
Video_ID
Category_ID
.....
category
Category_ID
Category_Name

I'm trying to get a user's count of comments for each category, ordered by the number of comments DESC. basically, I want to show the top categories where the user has posted the highest number of comments.

so far this query gets me the category name of where the user has posted:

PHP

$SQLTemp = "SELECT Category_Name
            FROM category
            WHERE Category_ID
            IN (

            SELECT DISTINCT (    Category_ID        )
            FROM video
            WHERE Video_ID
            IN (

            SELECT DISTINCT (    Video_ID        )
            FROM comments
            WHERE Comment_UserID = $id
            )
            )";

THANKS!!

Count Of *unique* Visits By Page For Each Day (was "Need Help With Query")
Using MySQL 4.1

I have a table 'orders' with the following fields:
ipaddress
pagenum
xtime (which is just the yearmonthday)

this is the query:
SELECT distinct pagenum,ipaddress,xtime,count(pagenum) AS pagenumtotal FROM `orders` group by pagenum,xtime ORDER by xtime, pagenum asc

What I am trying to do is return a count of *unique* visits by page for each day. This query returns a count of *all* visits by page for each day, including repeats. So if a visitors hits a certain page on the site 10 times and adds a record 10 times, the count result will include all 10 records in the count. I want this to only count as 1.

User_id And Password Error? (was "mysql Query")

Following Query right or not $sql = "SELECT * FROM login WHERE user_id = ".$username." AND password = ".$passwd."";if error then point out error

Following Query right or not

$sql = "SELECT * FROM login WHERE user_id = ".$username." AND password = ".$passwd."";

if error then point out error