MYSQL :: Quering Works Except Need To Sort Results By Other Table...

Mysql.exe Sort Of Works
I have just installed MySQL (not an easy thing to do). I also have installed PHP. I use Dreamweaver8 for 2 static sites, and eventually will figure out how to use that with PHP/MySQL.
Everything seems to work fine, and I'm working my way through Janet Valade's book, "PHP and MySQL for Dummies." Everything I have tried in the book works perfectly except for the mysql.exe command.
I am in the correct folder and type mysql.exe -u root -p and am prompted for my password. When I enter my password the cursor moves to the next line and just sits there. Nothing happens. Fortunately, the window is not frozen and I can just close it.

Sort With Limit But Sort ALL Results.
I have a query that limits by 50 results at a time and I also have an order on a int field. The issue I'm having is it's only ordering by the 50 it returns, how can I have it order by the entire results set and then give me back only 50 results?

SQL Query Works Fine With Phpmyadmin But Gives Me No Results And EOF With ASP
I have a strange problem: when I perform a sql query on my phpmyadmin it works fine and it gives me the right results. If I perform the _same_ query through my asp application I reach immediately the EOF condition without any results!

Delete Using Multiple Table: Works On 4.1.20, But Not On 4.0.16
I've a simple delete query delete from joingrp as J using joingrp as J,msggrp as M
where J.msgid=M.msgid and M.parent!=0

When I tried it on phpMyadmin at ixweb, having mysql-4.1.20, it works as desired.
But when I tried it on phpMyadmin at iPowerweb, having 4.0.16, it says
#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 'as J using joingrp as J,msggrp as M where J.msgid=M.msgid Can I solve this without using any server-side loops?

Quering A Heirachical Tree
I have a database where each user has a unique ID and a referrer ID.

If Mr. A refers Mr. B, Mr B. has Mr. A's ID in his referrer field.

It is easy enough to build a query to work out who has been referred by who, but what I want to do is find out who referred who down to five levels.

So, the second level would be, everyone who was referred by those who were referred by Mr A.

Quering 3 Tables In One Statment (GROUP BY)
below is my rather complecated sql query. It does two things:

a) Gets all the articles in catagory '4'
b) Gets all the articles in catagories with parent catagory '4'

(a) and (b) run fine on their own, but when i combine them with an OR statement, i get multiple (seemingly) redundant results. Only one of which has the desired catagory information. If i use "GROUP BY a.`id`" I get the right results, but not the right catagory information for (b). Code:

Table Sort Order
i have a table, table1 (field1, field2)

field1 is the primary key, auto increment

i want to select table like this:

select * from table1 order by field1

since field1 is auto increment, it is safe to use

select * from table1

Php To Sort A Mysql Table
i know how to sort a table depending on a certain field name. e.g
select * where 'Points' DESC;

but is there a way it can sort the 'points' column first and then it will sort all the other columns in DESCENDING order as well. it only seems to sort the one column in descending and the rest in Ascending.

Sort Table After The Filednames
i would like to sort my table after the fieldnames, best after the third column. The fieldnames are numbers...

The table looks like this:

ID | Sequence | 534 | 345 | 867 | 122 | 453
-------------------------------------------
1 | DNA1 | 1 | 0 | 1 | 0 | 0
2 | DNA2 | 0 | 1 | 1 | 0 | 1
3 | DNA3 | 1 | 0 | 0 | 1 | 0

Holy Smokes I'm An SQL Idiot - Two Table One Sort
So, yea... I'm and SQL idiot apparently. Conceptually I think this sounds pretty easy but I cannot figure out how to pull it off.

Table 1:
aid, atitle, adate

Table 2:
bid, btitle, bdate, bsummary

How do I link those two together so that I can sort by both adate and bdate in the same column. Oh, and I also MUST have bsummary....

MyISAM Table And Sort Order On The Disk
I ran the myisamchk tool with the options --sort-index
--sort-records=1 against an MYI file. Is there something I can use to
confirm what this command did? I would like to see what the sort order
on the disk for each table in my database.

Sort MySQL Data By Field In Second Table
I've got a table, 'Events', with a series of fields, one of which is 'CompanyID'. This is an integer that refers to another table, 'Company', which is a list of IDs and companies.

Now, is it possible to select from the first table of 'Events' and sort the data by the company name in 'Company' referred to by the 'CompanyID'?

Concat 2nd Table Results Into First?
Given three tables used to track multiple email addresses and phone numbers for a person:

Table 1: id, name, address
Table 2: table1_id, email
Table 3: table1_id, phone

How can I do a select to end up with all emails and phones concated together into a single row per individual:

id, name, address, "email,email,email", "phone,phone"

The solution needs to work with MySQL 3.23 (or possible MySQL 4.0). 4.1 is NOT an option, so group_concat is out.

Select From Table And Count Results In Another
I have to tables table one containing airports and table two containg bookings. What I need to be able to do is count how many rows are returned form the booking table that matches each airport in the airport table.

So I might have in my airport table
Newcastle
Heathrow
Standstead
etc etc

and in my bookings table I have

Newcastle
Newcastle
Standstead
Heathrow
Heathrow

And what I want returned is
Newcastle(2)
Standstead(1)
Heathrow(3)

How can I go about doing this.

Selecting Differnt Results From The Same Table.
I have am using mysql.

I wish to select from a table x number of result that match the same criteria then continue select that same x number that match the same criteria but differnt from the first criteria.

example table.

x = 2
medicine type
asprin    I
tylenol   I
advil     II
creatin   III
salvation  R
Redemption R
painkiller I

results would look like

asprin      I
tylenol     I
advil       II
creatin     III
Salvation   R
Remdemption R

It needs to go through the table selecting up to X and then continue on to the next possible value. Does anyone have any suggestions?

Geting Random Results From A Table
i'm trying to get random results from a table.

The goal is to get a new headline everytime the page is loaded (or reloaded).

The table named articles has the following fields:
headline - self explained
sumary - a brief description of the headline
area - which is a number from 1 to 9
date - the date (YYYY-MM-DD) when the article was posted

I can do that with simple arrays using the shuffle () command but it's not working with an associative array from a query result.

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

Join 3 Tables, Limit Results From 3rd Table
Yet again I have a query about joins and even though I keep reading I cannot find a solution for my problem that is relevant or that I understand.

I have 3 tables (I have shortened the number of fields to those that are relevant):

category:

Quote:

catid

product:

Quote:

prodid
catid
prodname

prodimgs:

Quote:

imgid
prodid
imgname

I can select all products per category and I can select all images per product, but where I am having the problem is selecting all products for one category and at the same time selecting all the images per product and limiting the number of images displayed to one without limiting the products to one.

I have tried various joins and tested a lot, getting different results but none of them are returning what I need.

Select all products for a category:

PHP

SELECT prodid, prodname FROM product WHERE catid='$this->catid'

Works fine.

Select all images per product per category and limit results of image to 1:

PHP

SELECT product.prodid, product.catid, product.prodname, prodimgs.imgname FROM product, prodimgs WHERE product.catid ='$this->catid' AND prodimgs.prodid = product.prodid LIMIT 1

This returns only one product and one image per category despite there being more.

Using the same code as above but removing the limit I get a display of all images per product with the product name repeated over and over.

I know I need to use joins somehow but I have tried various ways and tested the sql but am always getting an error on the joins and I don't know how or where to put the limit so that it is only applied to the prodimgs table.

So what I want is:

1. User click on a category

2. On the next page all product names linked to the category are displayed, but once only.

3. Next to each product name, one image related to that product is to be displayed if an image is stored in the prodimgs table

Use A Mapping Table To Get Query Results From Several Tables
Ok, so I have 4 tables...

Projects -> id, project_name, project_type
Variables -> id, variable_name
Symbols -> id, symbol_name
Mapped -> project_id, variable_id, symbol_id

if I had to find

(project_id=5, variable_id=2, symbol_id=3) AND
(project_id=2, variable_id=14, symbol_id=1) AND
(project_id=34, variable_id=78, symbol_id=44)

How could I write a query that would return the results for these all at once?

I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3

Returning 1 Tables Results From Two Table Query
I am executing a query which finds a criteria from certain records in table A and returns all values from table B if the criteria in table A is true. I want to return only the data from table B but can't find an easy way to do that? E.g the MySQL 'From' expression contains both tables.

Use A Mapping Table To Get Query Results From Several Tables
Hi all,

Ok, so I have 4 tables...

Projects -> id, project_name, project_type
Variables -> id, variable_name
Symbols -> id, symbol_name
Mapped -> project_id, variable_id, symbol_id

if I had to find

(project_id=5, variable_id=2, symbol_id=3) AND
(project_id=2, variable_id=14, symbol_id=1) AND
(project_id=34, variable_id=78, symbol_id=44)

How could I write a query that would return the results for these all at once?

I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3 ....

Using Results From One Table To Populate Column Names In Another
I have a table of gene names. I would like to use this table to populate the column names of a second table (all these columns will be of the same type ie INT(4) to represent expression level). The list of genes is large so I don't want to type in the columns by hand.

Caching Results For Large Multi-table Select
I'm developing a site that may eventually have a very large database of
users (hopefully, but who knows). Often there will be queries with multiple
joins and sometimes may include a few fulltext searches. Each page of
results will have a limited number of results displayed (obviously). As I
understand it, MySQL caches only exact queries, so doing a LIMIT (x,y) on
each query to get results for the specific page will cause MySQL to run the
entire query again, instead of pulling from the cache. I will limit the
results to 1000, but what's the best way to limit results per page?

I was thinking that I could run the SELECT and LIMIT to 1000, then to a
mysql_data_seek to jump to the starting row for the page and fetch a certain
number of rows for the page. For each page, the exact same SELECT would be
used and therefore the results would be cached.

'order By Alternating Table' Results And 'begins With' Select
I am just starting to learn SQL and have been working on a couple of questions and yes this is homework related. I am having trouble with 3 questions. I have the basis for the answers but am stuck on a couple of points.

I don't expect a direct answer but a push in the right direction would be helpful. Also, if anyone can recommend additional resources for assisting with learning SQL, that would be great. The first question is as follows:

A company is preparing invitations to its annual holiday celebration and wants to invite its sales staff (everyone in the SALESREPS table) and its customers (everyone in the CUSTOMERS table). Write a SQL statement that returns output looking like the following (including the sort order): Code:

Works
I have this and it works just fine but I cant help but think there is a much better way of accomplishing the same thing in one sweep verses 6
PHP Code:
mysql_query("update residential set bed='1' where bed like 'ONE%'");
mysql_query("update residential set bed='2' where bed like 'TWO%'");
mysql_query("update residential set bed='3' where bed like 'THREE%'");
mysql_query("update residential set bed='4' where bed like 'FOUR%'");
mysql_query("update residential set bed='5' where bed like 'FIVE%'");
mysql_query("update residential set bed='6' where bed like 'SIX%'");

Works In MYSQL 4.1 But Not In 5.0
MySQL
1054 - Unknown COLUMN 'p.manufacturers_id' IN 'on clause'
SELECT count(*) as total FROM products p, categories c, products_to_categories p2c LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE c.categories_status=1 AND p.products_id = p2c.products_id AND c.categories_id = p2c.categories_id AND p.products_status = Ƈ' AND p.products_id = pd.products_id AND pd.language_id = Ƈ'

Here is the query in PHP

PHP
"select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and p.products_status = Ƈ' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

How Exactly The LIKE Keyword Works?
I have a table named Inventory and have 3 items in it containing the work w069.

I want to run a query to return the three items.

My query: Select * from Inventory where Description Like 'w069'

It doesn't seem to work, returning zero row.

UNION - Works In 4.0.16, Not In 4.1.8
I have a query that works in 4.0.16 on my web host, but not 4.1.8 on my dev. box. Web host is upgrading in 10 days.

Notes:
Server language is ColdFusion
gamedate is type DATE

SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate >= now()
UNION (
(SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate < now()
ORDER BY gamedate desc LIMIT 1)
ORDER BY gamedate asc LIMIT 3

The goal is to select the most recent gamedate prior to "today" and the next two game dates including "today".

Query Only Works Once
I'm using jsp with mysql and am querying the database for data. Everything works fine on the first try. If I click back on my browser and hit submit again on my form, I get no data.

I've tried removing the close() commands but that doesn't seem to work.

SSN Changes In Database But Still Works As Key?
I have a ssn field in a table but somthing happens to the data. I use an update to enter the data and set the value to 123-12-1234 for a test but when I look in the table or I get the record from a record set it come out like -1123.

Why is this? But when I run a query and ask it to return all records which have a ssn of 123-12-1234 it returns the correct record. If this is correct then how can I display the ssn number in a java text field.

How Decimal Works
Say that we have an variable that is

var1 = 3
var2 = 3.65

var3 = var1 * var2 '--- Sum = 10.95

Now my Question!
How do I input this variable into MySQL in a COL whit DECIMAL (10,2)?
I only get the result 10.00 in the COL?
It seams that MySQL round every resout I get to .00 at the end.

If I make an varible hard ex.
var3 = "10.95"
that works but if I convert an numeric variable to string it doesnt?!?!?!
ex.
var4 = cstr(var3)

UPDATE Works In MS SQL
I use the following stament work with MSSql, but not MySql, why ?

Update mytable set mycol = 'my_Text' + mycol

Failed, mycol is varchar

MySQL Delete: Works In Php 5 But Not Php 4?
I've developed a small login system using Xampp on my local machine. Everything is running great except when the admin tries to delete something. Apparently I have the wrong syntax for my deletion in mysql.

Here's my original delete statement (from mysql5):
DELETE at.* FROM access_table AS at WHERE at.client_id=2

And here's the error I get from mysql4 (I ran the command in phpmyadmin console):
#1066 - Not unique table/alias: 'at'

So I removed the aliases and re wrote my command as such:
DELETE * FROM access_table WHERE at.client_id=2

And got the following error from php4:
#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 '* FROM access_table WHERE at.client_id=2' at line 1

SQL Query Works Only In Win32!
I use MySQL from my php page and from phpMyAdmin.

In Windows Server, following query works fine. In Linux Server instead it returns error.

The SQL Query is:

SELECT DISTINCT a.id, a.container, b.container FROM folder AS a JOIN folder AS b ON a.container=b.id

The PHP error is:

You have an error in your SQL syntax near 'ON a.container=b.id' at line 1

So, I ask: If in Windows this query work fine, where is the error in the Linux version?

SHOW DATABASES Works In 4.0.14
I've been trying to solve this problem for a long time. Only thing
I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I
did not convert the privilege tables with mysql_fix_privilege_tables.

Suddenly, the mysql started to show all databases to all users (even
though the user had access denied and could not do show tables on
them). So I started to find help and tried the
mysql_fix_privilege_tables. It didn't work properly (more than just
access denied and errors, that are ok) so I did the changes manually
conforming to source code of mysql_fix_privilege tables.

PhpMyAdmin stopped its warning about privilege tables not conforming
to current mysql version. Everything seemed ok except that SHOW
DATABASES still worked.

The users naturally all have Show_db_priv="N" in the tables. Also
trying "safe_show_database", "skip_show_database" in my.cnf and
"--skip-show-database" witch mysqld didn't help.

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.

select sc.name,
Sum(if(student.SNO is not null, 1, 0)) nmbr,
from school sc
left outer join student
on sc.school_id = student.school_id
group by sc.school_id

this runs just 0.30 sec with Mysql3.23 but 30Sec with Mysql 4.0.13.
i could not find a solution.But i suspect from NULL threatment.

Parentheses: Query Works With 4.1.7, But Not With 4.1.11
Since we updated to the newest MySQL version (Windows) some of our queries do not work any more.

It's reproducable by this (dummy) query:

SELECT B.ID from ((SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID);

This works with 4.1.7, but not with 4.1.11, unless the parentheses are removed:

SELECT B.ID from (SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID;

Does anybody know why MySQL changed its behaviour that way?

I know the parentheses in the first query are not necessary, but it would be quite difficult to search through all our queries for unnecessary parentheses.

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.....

Exporting MS Works For MySQL
I have a MS Works database and I can't find anything useful on the PHP site for working with this type of database. I did a quick search and it seems that it can be exported for OoenOffice.org and other spreadsheet database programs but none of them give a method. Mostly selling books and MS Access.

If there is a way to access the .MWD or .DBF Microsoft works files with PHP that would be great. If that's not possible, preferably not, I'd rther switch to MySQl easily if possible. I'm still looking but it's not looking good.

Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing.
Code:

SELECT Artists.Artist, Albums.Album_Name
FROM Artists, Albums
WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id

GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version.

The query is this:

Query Help. This Works, But I Don't Think It Is Good Practice
comics:
id
date
location

comments:
id
comicid
datetime
header
comment
author

Every comic can have multiple comments. Each comic will have a comment, but there is a period of time where the comic is posted but the first comment is not yet submitted. So for argument's sake we can't assume there is any comment at all for a given comic.

I want the all the comic ids and dates before a certain date along with the most first comment entered relating to that comic.

select comics.date
, comics.id
, comments.header
from comics
left outer
join comments
on comments.comicid = comics.id
where (comics.date>=񟭇-03-01')
group by comics.id
I never defined WHICH header should be returned when many are grouped together. I just know that mysql will return the first one in this circumstance. I seem to recall reading somewhere that different databases handle this sort of thing differently, with some returning a null in that field?

I keep trying to forumate a useful subquery but keep drawing blanks just as I think I have it figured out in my head. Like I forget how the string starts by the time I mentally formulate the end....