MYSQL :: Table/Query Guidance Requested

Advice Requested
I'm going to be creating a signup form with the standard fields (fname, lname, email, etc), plus user definable fields. Currently, I'm kind of stuck on how to go about doing this.

My users table has an id field (PK), email field, IP address field, etc. I want to give the site admins more control over what they can put into their signup forms. Some people may want to have a website field, others may want to have a field for the user's fav book. Whatever.

Now, if I put these into the users table, I'm now fixed at how many fields I can have, correct? I mean, I can set a determined number of fields and call them custom1, custom2, etc. and let the admin define their user-readable names, but it's still a fixed amount of fields.

I could create a new table with 2 fields - name and value - where name is the PK. That way the admin could enter as many as they want. But how would I then have the same fields in the users table to hold the data?

REQ: Help With M:N Cross Referenced Tables Requested
Table "person":
Fields: pid, name, email

Table "address":
Fields: aid, street, city, zip, state, validfrom

Table "xref_pers_addr":
Fields: refid, pid, aid, timestamp

for security reasons I want to configure the user's access to the
database to be write only, no delete or drop should be allowed. Hence
when an address of a person has to be updated (e.g. because he or she
moved) a ne entry has to be made in the address table with an updated
"validfrom" field. In the long run there will be more than one address
entry per person, and only the youngest entry is the valid one. Which
SQL query allows to get a list like

pid, name, street, city, zip, state, validfrom

that only shows the latest address per person?

I assume there must be an sql hack for mysql version < 4.0 which
accomplishes that without using subqueries that are only available in
mysql >=4.1. which i do not intend to deploy on my server.

My guess was this:

SELECT pid, name, CONCAT(MAX(validfrom)," ", street," ", city," ",
zip," ", state) FROM person,address,xref_pers_addr WHERE
person.pid=xref_pers_addr.pid AND address.aid=xref_pers_addr.aid GROUP
BY pid;

That grabs only the latest address, but I obtain the whole address in
one field an I have to disassemble it later into the respective fields
which is quite cumbersome.

Any other option to do that without using subqueries?

Assitance Requested On Syntax With Joins (outer)
Select d.* from
( select agent_addr, max(date_time) from eventlog where priority = 1
group by agent_addr) d,
(select agent_addr, max(date_time) from eventlog where priority = 6
group by agent_addr) u
where d.agent_addr = u.agent_addr
and d.access_time >= u.access_time

The above statement is to get certain data from an event table. I need
all of the lines where the event is 1 and there are no newer event 1's
or event 6's

I need to know what devices are currently down (Priority 1) based on
this history table. where there is no newer event 1 entries for the
agent_addr, or there is no newer priority 6's for that agent_addr
(priority 6 means device is back up)

I'm stumped on the syntax to do joins, or to get this to work.

the table works like the following
device goes down entry is made as priority 1 eventid 1
device comes back up new endtry is made priority 6 eventid 2
device goes back down another entry is made as priority 1. eventid 3

I need to make sure i only am getting a list of the latest status of
each device in the aformentioned scenario it would be the one with
event ID 3

ODBC Driver Does Not Support The Requested Properties
I'm trying to log data to a certain schema on MySQL 5 server via DSN. MySQL connector/ODBC driver version is 3.51., running in Win XP.

*Sometimes* I receive the following error:

---
An error occurred calling 'LogResults' in 'DBLog' of 'DBLog 1.0 Type Library'
An error occurred executing a statement.
Schema: My Recordset.
Statement: My_Table.
ODBC driver does not support the requested properties.
Description: ODBC driver does not support the requested properties.
Number: -2147217887
NativeError: 0
SQLState:
Reported by: Microsoft OLE DB Provider for ODBC Drivers
Source: DBLog
---

The worst thing is that sometimes everything seem to be ok but no data is logged to database! This case is very confusing, the logging has been successfull before and I've not been able to trace this. I've checked the basics (SQL syntax, schema priviledges, ...).

Client Does Not Support Authentication Protocol Requested By Server
i have this message when i try to use 'mysql_connect' command in php. error message :

Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in c:program filesapache groupapachehtdocs7mysql_test.php on line 2

#1251 - Client Does Not Support Authentication Protocol Requested By Server; ...
I am trying to get phpmyadmin working with mysql but I get the below errors...
Why is this??

"phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in config.inc.php and make sure that they correspond to the information given by the administrator of the MySQL server."

and...

" #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client "

changes in config.inc.php file:

$cfg['PmaAbsoluteUri'] = 'http://localhost/phpmyadmin/'

$cfg['Servers'][$i]['auth_type'] = 'config'
$cfg['Servers'][$i]['user'] = 'root'
$cfg['Servers'][$i]['password'] = 'pword'

Any ideas if I am missing something?

ERROR 1251: Client Does Not Support Authentication Protocol Requested
I have a problem with a MySQL environment.

- One server (Windows)
- Two instances of mysql (4.0.27 & 5.x.x)
- Each instance are running as a service with a different port number (tcp 3307/3306)
- Both services are set up with their own parameter files, so they point to the correct binary and data directory

When I try to log on through the console against the MySQL 5 database by starting c:program filesmysql5inmysql, then everything is working as it should.

If I try to do the same against the Mysql 4 database, it returns an error that the client does not support the authentication protocol requested.

Remote Access Not Working: Error 1251 Client Does Not Support Authentication Protocol Requested By Server
I'm trying to set up my MySQL database for remote access. I've never been a DB admin so this is all mostly new to me. I am binding to my public IP address, and I have created a new user with the following grant commands run from the mysql command line on the server:

mysql> grant all privileges on prs_data.* to 'username'@'%' identified by 'change';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on prs_data.* to 'username'@'localhost' identified by 'change';
Query OK, 0 rows affected (0.01 sec)

When I try to connect remotely from another host, here's what happens:

Client Does Not Support Authentication Protocol Requested By Server; Consider Upgrading MySQL Client In
I have recently upgraded to MySQL 5. Got this error message in my IE Browser. Have read some of the comments on other forums and my choices are: Upgrade all client programs to use a 4.1.1 or newer client library, or

SET PASSWORD FOR
-> 'some_user'@'some_host' = OLD_PASSWORD('newpwd')

Can someone please advise?

Mysql_connect(): Client Does Not Support Authentication Protocol Requested By Server; Consider Upgrading MySQL Client
Basically I am setting up php,mysql and apache to run an application. While trying to connect I get the following error:

Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in c:sudhirapachehtdocssvgmap est4.php on line 14
Cannot connect to the database.
Client does not support authentication protocol requested by server; consider upgrading MySQL client
I have tried the following message posted earlier by many of you guys i.e
"SET PASSWORD FOR peter@localhost = OLD_PASSWORD('something')
flush privileges

I am trying this from a long time and do not find a way to fix this problem
I request you guys to help me in soughting this out and I appreciate your patience in reading my post.

Mysql_connect(): Client Does Not Support Authentication Protocol Requested By Server; Consider Upgrading MySQL Client
I am having an awkward problem with my MySQL installation. I have a number of scripts that access the database from a few different web hosting sites. I have no problem accessing the database from the scripts that I host locally. The scripts that are on an outside server accessing the database gives me a mysql_connect() error.

Locally I use MySQL 5.0 and PHP 5.0. I am assuming that the 3rd party web host uses an older version of PHP and thinking that may be the problem. Is this the possible problem and if so is there a way to fix it without ruining the scripts that are currently working?

Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP

$gettray = mysql_query("SELECT trailers.title,
trailers.link,
trailers.movie,
movie.title AS mtitle
FROM trailers,movie
WHERE trailers.movie=movie.word
ORDER BY trailerid
DESC LIMIT 6",$connm);

It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table.

I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet.

If the row does not exist in 'movies', the program than uses the entire trailer title like so

PHP

if($ttray['mtitle']) {
$newttitle = explode("-",$ttray['title']);
$newttitle = array_reverse($newttitle);
$ttitle = $newttitle[0];
$ttitle = $ttray['mtitle'] ."- ". $ttitle;
} else {
$ttitle = $ttray['title'];
}

Thanks
Ryan

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

Create Virtual Table During Query Or Load Table To Memory From Text File
I have access to my application database via a secure link, so I cannot link a local database to it, only run queries on the remote server and download the result.

The database is missing some useful information, and I cannot add a table at this stage. I would like to achieve the result by creating a virtual table or the like. ...

Multi Table Query, Grab More Than Single Row From Secondary Table
I have a query like so:

PHP Code:

$selectt = mysql_query("SELECT trailers.trailer_id,
trailers.trailer_title,
trailers.description, trailers.file_url,
trailers.runtime, trailers.views,
trailers.rating, trailers.tt, trailers.keyw, trailers.date,
tags.snub,
films.film_title, films.studio, films.keyw, films.image
FROM films,tags,trailers
WHERE trailers.trailer_id=tags.trailer_id
AND trailers.film_id=films.film_id
ORDER BY trailers.trailer_id DESC LIMIT 8",$dbh);

I know this is pretty sloppy, I have multiple 'tags' per trailer ID, and I was hoping to grab all of them in a single query; maybe into an array? So that it shows the row, and then all the tags that belong to it.

Possible?

1 Simply Query Problem. Please Help With Insert Into 1 Table From Another Table
I have Table A that has some records already in and then I have Table B that does a few things and gets updated regularly.

With Table A, I want to get it updated every 8 hours or so with new data that has been inserted into Table B.

I will do this using the Cron.

But what would be the best insert query to use so that it does the process really quick.

The query that I have so far is:

Quote:

mysql_query("INSERT INTO table a (id, title, descrip) SELECT id, title, descrip FROM table b where app=1");

With the above query the id's match in both tables. But I only want the new records to be inserted into Table A where app=1 in Table B and the row is not already in Table A.

How can I add to the query so that it does this.

Query Table A And Return Records Not Found In Table B.
Given table A and table B.  Table A is a master table and table B is a list of authority or reference which is fed by selecting records from table A (via an UI I wrote in PHP).

The interface is working fine but I am left with one problem.    If I need to edit my table of authority, the source table (table A) should return ONLY entries not previously selected.

That said, if the A.key is in B.key, records from table A matching B.key should be omitted.

I could do this by looping through the query results from table A and querying table B, if no match, show A.key.  This I think is a bit antiquated and possibly too involved.

Is there a joint query command I can use?

Help With 3 Table Query & Counting Rows In Third Table
I have three tables, a members, a vendors and a products. I'm using a query to grab all of the rows from the first two tables, matching on a primary key. This is easy and works fine. However, I'm trying to pull the number of products from the third table and it's giving me trouble. Basically, I can count the products for each vendor if products exist, but if there are no products, instead of returning 0, no rows are returning.

current query looks like this:

SELECT t1.*, t2.*, count(*) AS count FROM vendors as t1, members AS t2, products AS t3 WHERE t2.mem_id = t1.mem_id AND t3.vendors_id = t1.vendors_id GROUP BY t1.vendors_id;

So, all of the vendors that have zero products are being left out of the results. Does anyone know if it's possible to achieve this without using a temp table?

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

A Table And A Query
=====================
person + frout |
=====================
me + apple |
---------------------------
you + orange |
---------------------------
me + banana|
---------------------------
you + patato |
---------------------------

and I need the following result:
========================
person + frout |
========================
me + apple-banana
--------------------------------
you + orange-patato
--------------------------------

3 Table Query
After bringing my server to its knees, I decided I should stop experimenting and get some help.
I have three tables:Honors (honorid, userid, honor, assnid, schoolid, year);
Schools (schoolid, name, website); and
Associations (assnid, name, website).
Each user may have multiple honors...and each honor is bestowed by EITHER a School or an Association...NEVER both.
I want to select all rows from the Honors table where userid = $UID, and then the name and website from EITHER the Schools OR the Associations table, depending upon whether there is a value for schoolid or assnid

Multi Table Sql Query
these are my 2 tables:
1. candidates_jobs - id time candidate_id job_id
2. jobs - id time name posted
I need to select all the jobs from the jobs table where:
1. candidate_id is not equal to 1
AND
2. jobs.id is not found in job_id
AND
jobs.psted = 1
Now, I have figued out the above using:
SELECT a.id, a.name, a.time, a.region, a.posted, a.employer_id, b.job_id, b.candidate_id
FROM jobs a, candidates_jobs b
WHERE b.job_id != a.id
AND b.candidate_id != Ƈ'
AND a.posted = Ƈ'
However, how should I display each job only once, instead of all the possible combinations?
group by does not work.

Well This One Would Be Really Tough, If At All Possible. One Table Query.
in this table I have a list of menus, of 4 different levels. 1, 2, 3, 4.

Level 1 is Root (one title only). Level 2 is menu (multiple title), each one's parent being Root, level 1. Level 3 is sub menu, each one is linked to one menu of level 2. Same for level 4, sub sub menu, each one is lined to one sub menu of level 3.

Table structure is -

id | title | level | parent_id | other_fields_goes_here

where parent_id tells to which a particular title is linked. For Root (level 1), parent is 0.

I need to get a list of all the menus, depending on conditions of other_fields (there are many), like this -

Root
Menu_id_1
Submenu 1 WHERE parent_id = menu_id_1 AND ...
Submenu 2 WHERE parent_id = menu_id_1 AND ...
Menu_id_2
Submenu 2 WHERE parent_id = menu_id_2 AND ...
Submenu 2 WHERE parent_id = menu_id_2 AND ...

and so on.

Well, I have done this using multiple select commands, for printing the menu and their sub and sub sub menus. Now I need to get them with one select command, as one list, aim being to use them for a drop down.

Query Help - Referencing A Table Twice
The title of this post may not be optimal, but I wasn't sure what else to call it

I have 3 tables
upload_transactions,
uploaded_files,
and users

I'm allowing users to upload files to our server. Since they can upload up to 5 files at once, I decided it was necessary for 2 "uploads" related tables:
"upload_transactions" stores the userid of the sender, userid of recipient, date/time of upload, sender message, sender subject line, and assigns an auto id for the transaction.

"uploaded_files" stores info related to the actual uploaded files including file name, file type, file size, an auto id, and the id number of the parent "upload_transaction"

the "users" table stores user accounts including auto id# and the username(which is their email)

With my query, I am retrieving data for all of the files sent to an individual user (for their "inbox"). I then loop through and bunch the files together based on their "upload transaction id"

My problem is that since the user id of the sending user and recipient user are both stored in the "upload_transactions" table, when the user id # is cross referenced to the "users" table I'm only able to get the recipient username(email), but not the sender username(email).

This query is getting relatively complicated (for my level of comfort) and I'm not sure how to re-write this query to get both the sending user_id and the recipient user_id

I'm thinking I might need to create an alias of the users table, but haven't yet succeeded.

I hope I've made sense! I'd appreciate any assistance...

below is the query and php code that does everything I want (except get the username/email of the sending user)...

$query="SELECT".TBL_UPLOADED_FILES.".file_id as file_id,
".TBL_UPLOADED_FILES.".file_name as file_name,
".TBL_UPLOADED_FILES.".file_type as file_type,
".TBL_UPLOADED_FILES.".file_size as file_size,

".TBL_UPLOAD_TRANSACTIONS.".upload_id as upload_id,
".TBL_UPLOAD_TRANSACTIONS.".sender_id as sender_id,
".TBL_UPLOAD_TRANSACTIONS.".recipient_id as recipient_id,
DATE_FORMAT(".TBL_UPLOAD_TRANSACTIONS.".date_sent, '%e %b, %Y %l:%i%p') as date_sent,
".TBL_UPLOAD_TRANSACTIONS.".subject_line as subject_line,
".TBL_UPLOAD_TRANSACTIONS.".message as message,

".TBL_USERS.".username as sender_email

FROM ".DB_NAME.".".TBL_UPLOADED_FILES.",
".DB_NAME.".".TBL_UPLOAD_TRANSACTIONS.",
".DB_NAME.".".TBL_USERS."

WHERE ".TBL_USERS.".username='".$session->username."'
AND ............................

Cross-table Query
I'm updating an old query that will return a random selected song from a radio stations playlist. After the orignal query was implemented the queue policy has changed and the query should no longer return songs from an artist that is already in the queue and waiting to be played.

The current query (simplified):

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

Joining The Same Table Twice In One Query
I have a table I want to join twice in one SELECT statement and I can do it just fine. However, the column I want that gets pulled in has the same name in both instances and I don't know how to name them uniquely. Here is a stripped down version of what I'm trying to do:

Query Result As New Table?
How can I store a Query Result as a new Table?

SQL Table Creation Query
how can one get the sql query with which a table was created?

Query To Get Records In One Table But Not In Another One.
I have this query that works but now I want to refine it so that it only selects records in the loaninfo table that do not have a record in the achtransfer table. I not sure how to do that. Here is the query I have now....

Query To Clean Up A Table
I have a table that I use the keep high scores for a variety of online games. Its structure looks like this:

CREATE TABLE `logicscores` (
`gamename` varchar(30) NOT NULL default '0',
`user` varchar(20) NOT NULL default '',
`score` int(10) NOT NULL default '0',
`date` date NOT NULL default '0000-00-00'
) TYPE=MyISAM;

The table has become too large and I'd like to clean out the data that I don't need. I need preserve the following and delete everything else:

1.) The highest 25 scores for each gamename.

2.) Each user's single highest score for each gamename. (If user Bob plays gamename game1 three times and game2 five times, I need to preserve his single highest game1 score and and single highest game2 score. The other six scores Bob recorded can be deleted.)

Is this possible with MySQL queries alone.

Table Join Query
I'm trying to join multiple database tables and having a little trouble with it, hope someone can help:
There are 3 tables with columns: id, user, date, time, action
I'm trying to get user, date, time and action from all 3 tables where the user equals "anyuser" and sort the result by date DESC, time DESC.

Table Backup Query
What's the most efficient way (by using MySQL queries) to backup a database table? I had been doing something like this, in which I had to back up to two different tables:

CREATE TABLE backup_1 LIKE main_table;
CREATE TABLE backup_2 LIKE main_table;
INSERT INTO backup_1 SELECT * FROM main_table;
INSERT INTO backup_2 SELECT * FROM main_table;

This process can be very slow. Is there a better way I can do this?

Create Table Query
I just dump this script from another database so I could add the same create table query for different db, when I run the query I get the error.

mysql> CREATE TABLE SENT_MESSAGES (
-> INDNUMBER int(11) NOT NULL auto_increment,
-> CLIENT varchar(100) NOT NULL default '',
-> CODE varchar(100) NOT NULL default '',
-> TYPE varchar(100) NOT NULL default '',
-> WHEN timestamp(14) NOT NULL,
-> PRIMARY KEY (INDNUMBER)
-> ) TYPE=MyISAM;
ERROR 1064: You have an error in your SQL syntax near 'WHEN timestamp(14) NOT NULL,
PRIMARY KEY (INDNUMBER)
) TYPE=MyISAM' at line 6

Monitor A Table Query
Environment: mysql 5.0.22 on Fedora Core 5
I have a mysql table that contains user IDs and passwords.
These IDs and passwords are used to allow access various web pages.
But the initial web page that does the authorization lookup may not be working correctly.
Is there a way to monitor the mysql (table) in realtime to see if the lookup is being performed?

Make-table Query
In Access, you can create a make-table query. Is the same thing possible in MySQL?

Multiple Table Query
I'm building an entertainment/food/festival website for my community using PHP and MySQL and have hit my first "speedbump" in the process. I have a simple text form that I need to query 3 seperate tables in my database (venue_profiles, artist_profiles and events) so if someone searches for a term, it will return a numbered list of everything regardless if it is a venue/artist profile or event.
Everything was working fine when I only had to query 1 table but now I've been thrown a curveball and need it to search 3.
Here are the tables:fields in question.
1) venue_profiles: id, name, city, category, description
2) artist_profiles: id, name, description
3) events: id, title, city, description
What will the query look like for what I need? and how do I go about creating one simple list that includes all the results from all 3 tables?

Need To Use Same Table In Subquery As In Insert Query
I have this table called "testtab":

ID | value
----------
1 | 8
2 | 15
3 | 5

Field ID is INT autoincrement as usual for ID field, field value is common INT. I need to insert new record to it. The inserted value should be equal to the highest value +1. I tried to do something like this:

INSERT INTO testtab SET value=(select max(value) from testtab)+1
Mysql threw this error:

You can't specify target table for update in FROM clause
I searched internet and found that I cannot use the same table in a subqery of insert/delete/update query. So my question is: Is here some workaround for this? I know I could use two queries - one to find the maximum value and second to insert the value+1 into DB, but I would like to do it in a single query.

What Is Wrong With This Multi-table Query?
Here is my query:

SELECT articles.article_id, articles.feed_id, articles.title, articles.abstract, articles.link, articles.date
FROM articles,feeds,site,sub
WHERE (feeds.sub_id=12 AND articles.feed_id=feeds.feed_id)
LIMIT 30

This gives me 30 of the exact same article when there are tons of articles.

I want articles from all feeds that have the sub_id of 12.

How To Join Three Table Into A Little Complicated Query
I am using version 4.1.22. I simplifying this for example's sake. I have three tables:

order:
order_id
tip_amount

item:
item_id
order_id
price
vendor_id

vendor:
vendor_id
vendor Name

Each order can have many items. Each item has one vendor.

I need a query that displays all orders grouped by order_id, EXCEPT where there are multiple vendors where I need it to show grouped by order_id in the first column then grouped by vendor_id in the second column. Plus I need the tip_amount to show ONLY on the first line of the order.

It would like this (notice order# 299 has two entries and the tip amount was added to the first record vendor record that order):

PHP

order_id vendor_name total tip298 ABC $30 $2299 DEF $25 $4299 XYZ $12 300 LKW $17 $7...

Redesigned Table...can't Rewrite Query.
I've just altered the structure of a table in my database to make it clearer, and to remove duplicate records that would have been stored in it. The problem is I now want to query it, and can't work out how to do it in one query. I could do it in 3, but I'd rather be efficient if possible.
The table structure is

class
classid - int(3) - auto increment - primary key
classname - varchar(25)
classparent - int(3)

The idea being that a record from another table is linked to a classid, from which we can extract the parents of that particular class. There could be varying number of levels, but the top level has a classparent of 0.
Is there a way of writing this into one query, ie. using a classid, select the values for that row, and its parent, grandparent etc.?

Empty Value In Multi Table Query
MySQL
SELECT p.`desc` ,
IF(SUBSTRING(p.prodCode,1,3)='p1-',(SELECT p2.nPrice FROM tProducts p2 WHERE p2.prodCode=SUBSTRING(p.prodCode,4) LIMIT 1),0) as normalPrice,
COUNT(DISTINCT pic.ordering) as pic_count
FROM
tProducts p
JOIN prod_subCats psc ON p.productID=psc.relProdId
LEFT JOIN taxCodes tax ON p.taxCode=tax.taxCode
LEFT JOIN suppliers s ON p.supplierID=s.supplierID
LEFT JOIN pictures pic ON p.productID=pic.relProdId
LEFT JOIN freeProducts fp ON p.productID = fp.productID
LEFT JOIN tProducts freeProd ON fp.freeProductID=freeProd.productID
LEFT JOIN pictures freePic ON freeProd.productID=freePic.relProdId
LEFT JOIN prodReviews review ON p.productID=review.productID AND review.status=1

WHERE p.stocklevel>-3 AND psc.relSubCatId=7235
GROUP BY p.productID
LIMIT 1

The value p.`desc` comes up blank (not null, just blank) in this query, though I know this data is there.
If I remove either of the other 2 fields for the query, the correct data for field p.`desc` is returned.
This kind of thing would normally happen when you specify fields with the same name from different tables, either explicitly, or using *. But as far as I can tell, all my fields & tables are correctly aliased etc
To further confuse matters, this problem occurs on my (windows) development server running v4.1.1, but not the (linux) production machine running v 4.1.2
I'm stumped.

Passing Variable Into A Table Query
Newbie here again. I'm using MySQL, version 4.1.21-standard and php. This is probably one of those "boy this guy is an idiot" type questions, but I have to ask it anyway

My db has several tables of yearly statistics. The user has a drop-down menu with choices for which year to view data. This choice produces a variable labeled $year.

The tables are labeled like so:

2006_results
2005_results
2004_results
etc.

and fields such as

2006_results.winnings
2006_results.status
2006_results.sponsor

Can a query take the previous variable and use it to request the specific table, such as:

If $year = 2006, can the query structure uses $year_results.winnings to get 2006_results.winnings?

Oh, I almost forgot. If I force the query I'm using for a hardcoded year, such as 2005_results.winnings instead of using $year, the query works. I just can't get it to use the variable.