MYSQL :: Querying Only Shows 3 Columns

7e2 Shows Up As 7.00E+02
I have value that I'm adding to a table via an insert statement. The value is "7e2", but for some reason is being converted to scientific notation on the insert "7.00E+02". I have quotes around the field as well. If you look at the insert statement below, it's "question" field. Anyone know of a way to force it to be a string?

$sql = "INSERT INTO ICI_ANSWERS (session_id, scale_id, question, answer, date_stamp) VALUES ('" . $piece . $data[0] . "', " . $data[1] . ", '" . $data[2] . "', '" . $data[3] . "', '" . $data[4] . "')";

Opened_tables Always Shows Zero
When I log into any of our db servers, go into mysql on the command line, and do a 'show status', the value of Opened_tables is always showing 0.

However, when I log into mysql via the MySQL Administrator and look under Health-->Status Variables it shows a value.

Any idea why it's always showing zero when I do it from within mysql on the command line? It's happening on multiple servers.

MySQL Only Shows Homepage
I have a problem concerning MySQL and Apache. I host a Fedora server with Apache, MySQL and PHP installed. A customer has a webpage that uses MySQL but for some reason I can't get any other pages than the homepage up when I click on other links, I just come back to the homepage. The URL displays the other link but it still stays the same. Code:

'datetime' Shows Only '0000-00-00 00:00:00'
I currently have a form set up on a site to send data entered into a database.
Now, the form submits correctly, the data is stored in the database correctly,
but the 'datetime' shows up as '0000-00-00 00:00:00' in each record.

I set the datetime variable to 'not null' and when I update the table, it sets the
default value to '0000-00-00 00:00:00'. Am I forgetting to set something or
where and how does the form get that date and time info?

Error Shows The E-mail @ Sign..?
I am gettin an error msg when trying to enter my newsletter registration details to the database.

function process_nl($id) {
$name=$_POST['txtname'];
$email=$_POST['txtemail'];
$SQL = "INSERT INTO nletter (name, email) VALUES ($name, $email)";
if(mysql_query($SQL,$id)) {
return true;
exit;
}
else
return false;
}

error:
You have an error in your SQL syntax near '@yahoo.com)' at line 1

Phpmyadmin Shows ???? Of Korean Data
My machine is windows xp sp2 with Apache HTTPD 2.2.0, MySQL 5.0.18, PHP 4.4.1 pl1 phpMyAdmin 2.7.0-pl1.

And the settings of apm is;

1) httpd.conf
DefaultLanguage ko

2) php.ini
default_mimetype = "text/html"
;default_charset = "iso-8859-1"
;default_charset ="utf-8"
;efault_charset = "euc-kr"

3) my.inf
[mysqld]
character-set-server = utf8
collation-server = utf8_general_ci

On my test site, I can see right Korean data.
But on phpmyadmin I can see only ???? for Korean data.

Musql Dump Shows Error
I am trying to backing up a databse .

I open the command promt form start > run;
i wrote the command "mysqldump --opt kimberly > backup.sql"
kimberly = database name;

the error comes out :

mysqldump: Got error: 1045: Access denied for user 'ODBC'@'localhost' (using password: NO) when trying to connect

Arabic Character Encoding Problem (shows Just ????????)
i have a problem and i did not found a solution untill now and i hope i found it here.
when i try to enter the arabic characters throw the browser to the mysql DB using phpmyadmin it does not enter the arabic characters but strange characters
but when i fetch the data to the browsers it shows the characters in arabic.
(the is the first problem)

the other problem is ...
when i write directly to the phpmyadmin in arabic it shows me the arabic characters inside phpmyadmin and when i fetch dhe data to the browsers it shows to me only question marks ??????????????????.
#########################
inserting throw browsers = displaying ok in browsers but not ok in phpmyadmin
inserting throw phpmyadmin = displaying ok in phpmyadmin but ???????? in browsers
##########################

Query Log Shows 12+ Attempts To Connect Every Minute
I have mysql-4.0.20a-nt running on windows 2000. I had trouble connecting from a different computer in my home network so I set the following in my.ini:

set-variable=log=mysql.log

At startup I get the mysql.log file in the data directory. Upon inspection it shows a lot of connect commands, at least 12 every minute, with some 10 seconds in between, they come in pairs, first as ODBC@localhost with no password, and second as user@localhost with a password set. Access seems to be denied every time. Here's a snippet from the file:

MySql, Version: 4.0.20a-nt-log, started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
050320 20:38:08 1 Connect Access denied for user: 'user@localhost' (Using password: YES) ....

Date Field In Database Shows 0000-00-00 00:00:00
i have the following in my list of variables (partial list):

$id = $_POST['id'];
$name = $_POST['name'];
$date = date("D, d M yyyy, h:ia T");

and my connection includes:
$sqlquery = "INSERT INTO $table VALUES('$id','$name','$date')";

all other values are posting except the date.

in the mysql database, the date field is set up as follows:

Field Type Null Default
date timestamp Yes CURRENT_TIMESTAMP

my objective is simply to add a date field with the current date and time. i want mysql to insert the date for me in the format resulting in this type of output:

Fri, 19 Aug 2005, 10:23am CST. i just wasn't sure whether i was supposed to
include the date variable or not.

MYSQL Date Entry Shows 0000-00-00
i want the user to type in the date. Everything works fine and the form submits. When i open the database and query the results, the date field shows only 0000-00-00. It doesn't show the date entered (ex 05/23/07). What am i doing wrong?

I'm thinking it has to do something with converting the string to a date? If so, how do i do that in PHP.

Command SHOW SLAVE STATUS G, Shows That SLAVE_IO_RUNNING
I am trying to set up a master/slave replication.. I followed the instructions in the mysql manual.. I am using mysql 4.0.12 installed in windows xp... I have found out that the replication is not working ...

when i make the command SHOW SLAVE STATUS G, it shows that SLAVE_IO_RUNNING : No.
How can I make this run? What could have I done wrong?

Mysqld Shows High Cpu Usage Over Extended Time, Restart = Normal
Occasionally in checking one of the servers, I noticed that mysql shows 85% + of
cpu usage essentially leaving the server at 0% idle. After monitoring it for a
few hours, the status did not change. After a stop and start of mysql, things
progessed normally. Checking back a few days later I noticed it was once again
sitting up there at 95% (or thereabouts) and doing nothing of value from what i
could tell.

Have restarted MySQL during peak usage times for that server and its database,
and it has showed normal loads and CPU usage (approx 20% CPU with .1 to .3
load). Is there a known issue (running on FreeBSD 4.8, MySQL 3.23.55 MyISAM)?
Is there something I should check when next I notice the high CPU usage?

Many To Many Querying
I'm working on a site that will showcase graphic design projects. Every project can have tags. Right now, I've got the following:

tag_type:
tag_type_id int,
name varchar(256)

tag:
tag_id int,
project_id int,
tag_type_id int

project:
project_id int,
name varchar(256),
text text

So now, I'd like to know what the best way would be to extract the projects and their respective tags. I'm programming this in PHP. So basically what I'd want in the end is to be able to show all projects, and all tags that belong to each project. I'm not very experienced in using MySQL. All I can think of right now is to loop through the projects and do a new query every time, but that seems very inefficient to me.

Logical OR On Columns? (Merging Two Or More Columns Into One Column)
Considering the following example table:

Column1 | Column2 | Column3
===========================
NULL |"Value1" | NULL
---------------------------
"Value2"| NULL | NULL
---------------------------
NULL | NULL |"Value3"

In each row only one column contains an actual value (a string in our
example), the other columns are set to NULL.

I would like to merge those columns into one single column, discarding
the NULL entries.

The desired result would look like this:

Merged_Column
=============
"Value1"
-------------
"Value2"
-------------
"Value3"

A '"SELECT column1 OR column2 OR column3 as "Merged_Column"' doesn't
work unfortunately. Is there an easy way to accomplish this on the SQL
Server?

The next problem I am going to face is that every column is in fact
made of a different data type (ie. Date, Int). Is there a way to
modify the merge select (if that's possible) to have it cast
everything into a single specific type? Ie. make everything in the
merged column a string.

Querying From Two Databases
I need to query from two servers at once [in the same script], both databases are on the same server [localhost] and use the same username & password. How can this be done [php]?

Querying Two Tables
I have one table filled with a list of events. fields include date, title, and location of the event.

My other table has a list of locations and their map url.

i want to structure a query to include date, title, and location of each event. if the event location is in the map location table, i want the map url, too.

SELECT events.date, events.title, events.location,
maps.url
FROM events, maps
WHERE events.date >= CURDATE() AND maps.location=events.location
ORDER BY events.date ASC

this query returns only events that have a map. how do i get all events, and the url, if they have one?

Querying From Different Machines
I have databases on two different machines which I want to select from in one query, any good way of doing this without moving tables from one machine to another?

Querying Two Tables
A customer places an order.

A PHP script opens dbMyDatabase, queries table tbl_customers and if the customer is new, his details are inserted. Then order details are inserted into table tbl_orders, but this insert (tbl_orders) always fails.

Querying First 3 Letters
Can anybody tell me how to query the 3 order letters..

example is my code..

SELECT firstname, lastname, town, availability, expected_salary, keyword,dob,vacancy_type_id FROM rec_candidates WHERE keyword LIKE '%asp.net%';

Querying Database
i am making a php script where first it inserts a new row into the table, then a couple lines down it needs to query the table to find the same row that was just made. one of the columns is the datetime of when the entry was made, if i query the database a couple lines down for where the datetime was created ( NOW() ) would it return the same value or would the fact theres a few miliseconds difference change anything?

Querying The Database
I was wondering if there is a way to query mysql and get the next number that a calume is going to be if the colume is auto_increased by mysql.

Querying Timestamp
i got a timestamp timestamp(14) column.
format looks like 20050426043734
how do you query records created lets say within a week ago?

i tried

select * from table where timestamp < "one week ago";

but that doesnt seem to have worked.
i tried looking through mysql website but couldnt find an example @_@
i didnt want to do it via mathematical deductions...ie.. timestamp - 7
since im not sure how it'll handle month changes..i thought they would have like a built in statements like one week, one month..etc.

Properly Querying A Relational Db.
I've recently been working with more relational dbs and I was just wondering...am I working with them properly?

The reason I ask is because I'm wondering if there are easier/more efficient/more "proper" ways to be working with a relational db.

So here is an example: two tables, a project table and an employee table. The project table stores employee id in emp_id and is linked to the employee table by a foreign key (a one to one relation).

SELECT P.project_id, E.first_name, E.last_name, P.name, P.code, P.start_date, P.end_date, P.imdb
FROM project AS P, employee AS E
WHERE E.emp_id = P.supervisor
The thing is eventually we'll start to have tables with 4-5 foreign keys and I'm wondering if this is still "proper". Honestly it may seem a stupid question to some of you experts :-p

GUI For Populatin, Upating, And Querying
can anyone recommend a good GUI interface for mySQL?

Do you mostly use a command line client?

Querying Multiple Tables
I need to query multiple (15) tables to build a data entry screen. Is better to create 15 separate SELECT statements and query each table independently or create 1 large SELECT and query one time?

Querying A Category And Subcategory
I have a table in a database (store_category) that lists categories and subcategories. The subcategories have a field with the parent category's id in it. So something like:

id | name | parent
1 Dog 0
2 Puppy 1
3 Cat 0
4 Kitty 3

I then have a products table and in that table, each row has a field for the id of the category it's in.

The problem I'm having is selecting something like all of the products that are in both the Dog category and it's subcategories (in this case, Puppy). I can select a single category (parent or sub) just fine, but it's if I select only the parent and want to also include the products in it's subcategory that I run in to issues.

Querying A Column With Brackets In Its Name
I'm working with a poorly writtend database that has some
fields named as such: MTIC_PROD_VEND[ 1]

I do not have the option to rename this field but I do need to retreive data
from it with SQL. I've tried a suggestion of [MTIC_PROD_VEND[ 1]]] and that
doesn't seem to work, it may be because the 1 has a space leading it. Does
anyone happen to have any suggestion to try to work around this?

Querying Dates In Mysql
If I want to extract data with todays date then it's 'where blab blah =
current_date()'

For yesterday it's 'where blah blah = current_date()-1'

But can anyone tell me how to extract all data from the start of the
current week? Is there any way in Mysql to work out the start of the
current week?

The current month?

Year?

Querying Other Database From MySQL
We're using a mySQL database as a replica of another (Sybase) DB for
reporting purposes. The Sybase is part of a real-time mission critical
system - hence the separate database where people can run their queries
on.

Currently a separate application is copying the information we're
interested in from Sybase to mySQL in an overnight batch-process. We
want to extend this to loading every 15 minutes and are now
investigating the possible solutions.

I am considering to create a scheduled task in mySQL, where mySQL
itself reaches out to the Sybase ASE server and fetches the data. That
seems to make the setup much easier and eliminates the need for an
intermediate application.

Does anyone know if it is possible to create a "pass-through" query in
a mySQL stored procedure (or scheduled task)? I searched for this but
could not find anything, unfortunately.

Querying The Same Table Twice In The Same Query?
I have a table that consists of: -

ModuleID Int
PortID VARChar
Quantity Int

It is a linking table which links an individual module which may have ports of different types (or not) to a description of the ports. So for example a module might be: -

Module 1 has 4x(10/100 RJ45 Ports) and also 4x(MTRJ ports.)
Module 2 has 4x(10/100 RJ45 ports) and also 2x(SC ports).
Module 3 has 8x(10/100 RJ45 ports)

so the entries in the table would look something like: -

Querying Databases On Different Server
I'm not sure if this is possible, but i have one site that is hosting the database and i am creating another site where i would like to use the database from 1st site. How would i go about doing this? Can it even be done in MySQL?

Querying If A Table Exists
I am familiar wth the "IF EXISTS" clause to drop tables before recreating them. However, I am wondering I there is a way to do that in a select statement.

I'm trying to do something like this:

ex: select if(exists('mytable'), 'Found Table', 'Unknown Table);

Gathering Data Without 'Re-Querying'
I have a table called 'Products' and I want to display one product on a page along with other information about that product gathered from related tables. The information gathered from other tables will not always be possible because a piece of information for a product may not be set so I cannot do one query to gather all the information because matches won't necessarily turn up for each table.

For instance, the products have manufacturers and the manufacturers are stored on another table. The product may not have a manufacturer alocated to it i.e:

SELECT p.product_id, p.product_name, p.manufacturer_id, m.manufacturer_id, m.manufacturer_name
FROM products AS p, manufacturers AS m
WHERE p.product_id = 20 AND m.manufacturer_id = p.manufacturer_id

Querying Multiple Tables For One Record
This has been killing my coworker and I for most of the day now... perhaps someone else can help.

We have five tables in a database... one field has the same name in each table, the rest of the fields are different. We need to query each table against the common field and when a match is found, retrieve all fields for that record in that particular table.

I know you can do this with an IF-ELSEIF statement and query each table independently, stopping when the record is found. We would prefer to query each table at the same time and just grab the data--much shorter and efficient code, IMHO.

Any ideas? We have tried several variations of JOINs, UNIONs, etc. to no avail.

Querying Two Tables With Similar Data
I have two tables in my database "bannerclicks" and "bannerviews". They list the times a banner is clicked or viewed, respectively.
They contain similar data, namely:

bannerclicks
--------------
id INT
clickdate DATE
bannerid INT

bannerviews
--------------
id INT
viewdate DATE
bannerid INT

What would my query be if I wanted to display the months in which ANY activity took place (either a banner was clicked, or a banner viewed)?

I've been playing around with queries like this:

SELECT DISTINCT
MONTH(bannerclicks.clickdate) AS clickmonth,
MONTH(bannerviews.viewdate) AS viewmonth
FROM
bannerclicks, bannerviews
WHERE
bannerclicks.bannerid=2
AND
bannerviews.bannerid=2
... but it was all no good. In the end I decided to create one table "Banneractivity" with an additional "action" field to contain either "view" or "click". (in retrospect this is a much cleaner approach anyway)

But I'm still curious, how would I solve this problem in the future?

Querying The Same Field Of Joined Tables
I'd be endlessly thankful for help on the following problem:

The situation
I'm building an artist (musician) database that is to be searchable by various criteria. Users can search its entries by selecting an event type they are looking to book a band for, and/or by selecting up to 3 music genre preferences. The results are displayed as a list of short band descriptions to which all of the selected criteria should apply.

The table setup
Data is stored in separate tables:Artists - with artistID, Name and other band-specific info needed to display the result entriesGigtypes - with gigID and gigName (=event type denomination)Genres - with genreID and genreNameLookup table for Gigtypes (artistID and gigID)Lookup table for Gernes (artistID and genreID)Multiple gigtypes and genres can be attributed to each band, and are stored in the lookup tables.

An example of the query in common language
"Find all artists who play at parties AND play Jazz AND BluesRock AND Oldies."

Beginning of query
Querying gigtype and 1 genre is no problem since two different fields are involved. I simply hook up the lookups to the artist table by means of the artistID and query.

In the course of my searches I've seen there may more elegant ways, but this is the way I'm doing it at the moment (slightly simplified to prevent eye-sore):

SELECT * FROM artists, artist-gigtypes, artist-genres
WHERE artist-gigtypes.gigID = 1
AND artist-gigtypes.artistID = artists.artistID
AND artist-genres.artistID = artists.artistID
AND artist-genres.genreID = 6
ORDER BY artistName ASC
The problem
begins when I search for artists by more than one genre!

So my question specifically is how to write the genre query so that my results show only those artists to which ALL genres selected in the search form apply?.

What I've triedWHERE...AND - first I tried to simply translate the common language query version by adding the second (and 3rd) genre query with "and", which I saw is silly, since no row of the field will ever contain more than one genreIDWHERE...AND genreID IN (genrearray) - looked promising, but returns all bands to which ANY of the array entries applies
Thus, my problem.
Sorry if the solution has been posted elsewhere, but as a humble beginner I've had to realize I don't know the terms needed to find it!

Issue Querying A Category And Subcategory
I have a table in a database (store_category) that lists categories and subcategories. The subcategories have a field with the parent category's id in it. So something like:

id | name | parent
1 Dog 0
2 Puppy 1
3 Cat 0
4 Kitty 3
I then have a products table and in that table, each row has a field for the id of the category it's in.

The problem I'm having is selecting something like all of the products that are in both the Dog category and it's subcategories (in this case, Puppy). I can select a single category (parent or sub) just fine, but it's if I select only the parent and want to also include the products in it's subcategory that I run in to issues.

Querying 2 Tables With Dates Thrown In :)
just thought id see if i could pick the collective brain

I have a jobscript im writing and in the job table i store the date the job was added and a value in the field "period", now this period is an integer that refers to another table called job_period that contains the number of days a job is live for.

What i am trying to write at the moment is the script to expire a job once the period is done. so for example i have a job with "2" as the period - this refers to
2 - "14" in the periods table which means the job is live for 14 days.

Now im running a daily task that will do this, so how would i go about doing this query, which i have written in spoken english.

select jobs where date job added + period = today

bearing in mind this will be 2 tables being called.

i hope that makes sense as reading it im not sure it does

Querying Multiple Mysql Tables
I have inherited a site which used to use oscommerce but which the owner now wants to be completely redesigned. There is already product data in the database tables left behind from oscommerce and I was hoping to use this in the new design.

The problem is that the info is spread over numerous tables and I am not sure how to query the tables to display what I want. Ideally I want a list of products, broken down by category. See the tables in question below:

CREATE TABLE products_to_categories (
products_id int(11) NOT NULL default Ɔ',
categories_id int(11) NOT NULL default Ɔ',
PRIMARY KEY (products_id,categories_id)
) TYPE=MyISAM;

CREATE TABLE products (
products_id int(11) NOT NULL auto_increment,
products_quantity int(4) NOT NULL default Ɔ',
products_model varchar(12) default NULL,
products_image varchar(64) default NULL,
products_price decimal(15,4) NOT NULL default Ɔ.0000',
products_date_added datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00',
products_last_modified datetime default NULL,
products_date_available datetime default NULL,
products_weight decimal(5,2) NOT NULL default Ɔ.00',
products_status tinyint(1) NOT NULL default Ɔ',
products_tax_class_id int(11) NOT NULL default Ɔ',
manufacturers_id int(11) default NULL,
products_ordered int(11) NOT NULL default Ɔ',
PRIMARY KEY (products_id),
KEY idx_products_date_added (products_date_added)
) TYPE=MyISAM;

Querying Db For Current Week Items
I have a db where I store videos file names and the date they are posted in UNIX timestamp format.

I need to extract just the videos which have been posted during the current week. so I'm using this as a test but I'm not getting anything:

SELECT * FROM video WHERE WEEK(Video_PostedOn) = 37

do I have to first use a function to convert that?

Querying Values Separated By Commas
I would like to ask for your help for a scenario I am facing.

I have the two following tables for a simplified example:

Querying Text That Has Single Quote
I have cities table (MySQL).

City names are sometimes like:

's-Hertogenbosch (this is a name of actual city in Netherlands) or
Saint David's (this is a name of actual city in Grenada).

A problem is when querying such city name in mysql table like:

SELECT * FROM cities WHERE left(name,$length)= '$prefix' ORDER BY accent_name"

Efficiently Querying Across Partitioned Table
If I have a table horizontally partitioned based on primary key (may or may not use MySQL's inbuilt partitioning features), what are the options for efficiently querying on a column that isn't the primary key? For example, let's say you have a partitioned Country table partitioned on an autoincrement key (not the best example, but assume there are millions/billions of countries) across several tables (assume more than 20 tables).

Indexes don't span across partitions, so creating a partition on Country's "continent" attribute wouldn't make sense. If you then wanted to efficiently query for all countries in a given continent, how would you do this knowing that the Country table is really partitioned into several tables? What are the options at the MySQL or the application level?

Querying A Table By Range Of Names
I currently have a database with over 4000 records in it which breaks down into about 160+ pages on my website. I would like to break this down into smaller chucks. My question is how to get mysql to query a range of names such as all names between A and D?

Querying From Two Tables - Multiple Results?
I have two tables, A and B. A has 10 rows and B has 5 rows. I make a query like:

SELECT a.name, a.address
FROM A a, B b
WHERE a.zipcode!=b.zipcode

The problem is that since there are 5 rows in B, I get back 5x the results. I can use LIMIT except the number of rows in A is not known, it may not be known. How can I fix this?

Querying Form Multiple Tables
I'm trying to get info from multiple tables, and not having much success. I want it to be sorted by date, as if they were 1 table. Therein lies (one of) the problem, one is just date(YYYY-MM-DD) and the other is date and time(YYYY-MM-DD HH:MM:SS). Also, I'm not sure that everything else is right, or anywhere near it....

Querying From 2 Tables With Strange Results
What I want is to run a query that select the complete.a2 field and echos the value, where dn_records.number='5553331111'

Whats happening is all the values for complete.a2 are echoing back, even if dn_records.number isn't equal to '5553331111'

I am getting same results using the below php script as I am usnig the MySQL Query Browser.

<?php
mysql_connect( 'localhost', 'user', 'pass' )
or die( "Error! Could not connect to database: " . mysql_error() );

mysql_select_db( 'customers' )
or die( "Error! Could not select the database: " . mysql_error() );

$result = mysql_query("select complete.a2, dn_records.number from complete, dn_records where dn_records.number='5553331111'")
or die(mysql_error());

while($row = mysql_fetch_array( $result )) {
    echo "<tr><td>";
    echo $row["a2"];
    echo "</td></tr>";
}

?>