MYSQL :: "Unable To Select Requested Database."

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?

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'] . ''
}
?>

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

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

"Unable To Jump To Row"
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 4 in /home/chilipie/public_html/icook/index.php on line 29

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 4 in /home/chilipie/public_html/icook/index.php on line 30

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 4 in /home/chilipie/public_html/icook/index.php on line 31<?php

require("includes/header.inc.php"); // Include Header

$current_page=$_GET['page']; // Define "current_page"

$query_pages="SELECT * FROM pages WHERE url_title='$current_page'"; // Define "query_pages" (MySQL Query)

$result_pages=mysql_query($query_pages); // Define "result_pages"

switch($_GET['page']) { // Get Page ID

Code:

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.

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.

Cleaning Database Field Script "O'Connell"
I have fields like these in my database:

O'Connell

I would like to write a sql statement that would eliminate backslashes from the first and lastname fields.

How can I do this?

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?

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 ?

Need Help On Structuring Database For Multiple "categories"
Can someone give me any clues as to how to structure my database /
tables? I am a bit clueless as to the best method, as this is the
first time I've dealt with nested categories.

I have a "Graphics" section on my website. This will be broken down
into categories (such as "Holidays" and "Automobiles") and each
category will have multiple themes (such as "Christmas", "Easter" and
"Honda", "Porsche"). So I take it I need three tables.

But I'm not sure what's the best way to do it. Normally if I have a
single category -> item relationship, I'd just do:

How To Import "mysqldump" File Into Different Database?
We have successfully used "mysqldump" to create backup files for our databases on our mysql installation. We wish to test the quality/integrity of our dump files by importing a dump file into an empty database having a name different from the original source database from which we created the mysqldump file.

Can someone offer an example of a command we would use to restore a dumpfile into a database different from the original source database?

"dumping" Database From My Comp To Server?
i have been developing a site and building the DB on my comp at home. i do not have inet access and choose not to for security reasons. am i able to utilize the dump feature (new to this) still?

would i be able to save (burn) the info to a disk and then use that to perform the DB transfer to the new server? or what would be the workaround, if any...

Database Does Not Accept The " ' " Character
I use a simple PHP script to post entries to a database. If anyone uses the character " ' " in any word, such as entering his name as Brian O'Neil - this entry doen not get posted to the database. The database field for NAME is a TEXT field. I tried to change it to a VARCHAR field but it still will not accept. What to do?

"use Database" And Slow Log File
I have an application, which normally looks at two InnoDB databases:

1) db1 with approx 100 tables for usual queries
and
2) db2 with 2 tables for registration of visitors (cake_session).

naturally, queries appear independently and

there are a lot of queries in a slow log file after commands

use db1

and

use db2.

Need the commmand "USE" much time?

How can I reduce this time?

Is it usefull, if I make "USE" only for db1
and access the table cake_session like INSERT INTO db2.cake_session ...?

"mysql" Database Disappeared
All was fine until I installed fabForce then I noticed that the "mysql" was gone and I was getting errors when the program was trying to check the users table in the mysql database. I have reinstalled, but it still isn't there. I see the mysql database files are still around (C:/Program Files/MySQL/MySQL Server 5.0/Data/), does anyone
know how to restore from these files?

"Select Least" Condition?
I have a MySQL table that has a float field...(apr)
What I am trying to do, is select the three rows that contain the least amount in the apr field, but am not too sure on what query/condition to use...

Could somebody please point me in the right direction?

Could I use something like:

SELECT * FROM table_name ORDER BY apr ASC LIMIT 3
D

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.

Dropping Database Called "database"
I have finally managed to install and configure mysql on my pc for testing but need to import data from an Access Database.

I first found out that I had to create a new user and grant priviledges to it - which means I could connect.

My next stage was using the migration tool to import the data.

This did not go so well...

Firstly, many of the table names had spaces in them which means that they are effectively useless in mysql.

So I thought I would start again, ensuring that the tables are first renamed in Access.

I then went to show databases and, during the migration process, it has given the imported data the default name of "database".

I now have a major problem deleting it as it thinks the name is an instruction!

Anyone know how to get over this issue?

Exporting A Database While It's "being Used"?
I have a relatively active forum that has recently become even busier than before. I used to export the database once or twice a week without closing the forum first, but recently it takes a lot longer to export the database, so I was just wondering if there would be any type of error/problem if I'm exporting the database while someone is making a post or something like that?

Database Name Including "-" Possible?
My website server has given me a database with a name which includes a "-" chr.

like "name-12345"

I would like to create a database on my computer's server with the same name so I don't have to change names between servers, but it doesn't accept the "-" chr.

Is there a way round this?

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?

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