MySQL "X AS Column"

a2jfreak

I don't have a column named `curage` or `thenage` . . . I find them from the DOB and the missingDate. MySQL gripes and says I have no column named "curage."

[php]
SELECT `ID`,COUNT(*) AS `count`,
((YEAR(CURDATE()) - YEAR(dob)) - (RIGHT(CURDATE(),5) < RIGHT(dob,5))) AS `curage`
FROM `missing`
WHERE `firstName` LIKE "%%"
HAVING `curage`=5
GROUP BY ID
[/php]

a2jfreak

Bump.

Changed query, but still doesn't work. This is annoying. Any ideas?

Shorty

a2jfreak wrote:

Bump.

Changed query, but still doesn't work. This is annoying. Any ideas?

Im attempting to read that SQL so I can help, but Il be damned if I can figure out what you are attempting to do

Shorty

Quick edit:

Lose all the backticks in your query for a start!

GHoosdum

I'm not entirely certain, but I think you need to make the first part of the query, where you establish 'curage' into a view. Then query on curage = 5 in that view, because I don't think you can put a HAVING clause on a derived column in the same query you establish it.

a2jfreak

Query simplified . . . if this one can be made to work I can add in the other stuff later . . .

[php]
SELECT `ID`,COUNT(*) AS `count`,
((YEAR(CURDATE()) - YEAR(dob)) - (RIGHT(CURDATE(),5) < RIGHT(dob,5))) AS `curage`
FROM `missing`
WHERE `firstName` LIKE "%%"
HAVING `curage`=5
GROUP BY ID


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 'GROUP BY ID' at line 1
[/php]

a2jfreak

How would I do that?

GHoosdum wrote:

I'm not entirely certain, but I think you need to make the first part of the query, where you establish 'curage' into a view.

GHoosdum

Do this first:

CREATE VIEW Age_view AS
SELECT ID,COUNT(*) AS Count_All, ((YEAR(CURDATE()) - YEAR(dob)) - (RIGHT
(CURDATE(),5) < RIGHT(dob,5))) AS Curage
FROM missing
WHERE firstName LIKE "%%";

You can't use "count" as the column name for the count because it's a reserved word, so I changed it to Count_All.

Then do this:

SELECT ID, Count_Age, Curage
FROM Age_View
HAVING Curage=5
GROUP BY ID;

a2jfreak

[php]
CREATE VIEW `age_view`
AS
SELECT
*,
COUNT(*) AS total,
((YEAR(CURDATE()) - YEAR(dob)) - (RIGHT(CURDATE(),5) < RIGHT(dob,5))) AS curage
FROM `missing`
WHERE `firstName` LIKE "%%"

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 'VIEW `age_view` AS SELECT *,COUNT(*) AS total, ((YEAR(CURDATE()
[/php]

GHoosdum

I don't think you need any of the ` marks in the syntax at all. Try it without any ` or quotes anywhere except for the "%%"

a2jfreak

This doesn't even use aliases and it fails.

Possibly my MySQL version (3.23.49) is too old. Are VIEWs only supported in v4?

[php]
CREATE VIEW view_age
AS
SELECT dob,firstName,lastName
FROM missing
WHERE firstName LIKE "%%"


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 'VIEW view_age AS SELECT dob,firstName,lastName FROM missing WHE
[/php]

GHoosdum

I'm not sure what SQL code is supported by which versions.

a2jfreak

Thanks for your help Rob and Dan, I finally got it working.

GHoosdum

Great! What did it take?

a2jfreak

Query 1: (used to attain the total number of records that match the criteria)
[php]
SELECT *,
DATE_FORMAT(dob, '%b %e, %Y') AS dob,
DATE_FORMAT(missingDate, '%b %e, %Y') AS missingDate,
CAST(((YEAR(CURDATE()) - YEAR(dob)) - (RIGHT(CURDATE(),5) < RIGHT(dob,5))) AS UNSIGNED) AS curage,
CAST(((YEAR(missingDate) - YEAR(dob)) - (RIGHT(missingDate,5) < RIGHT(dob,5))) AS UNSIGNED) AS thenage
FROM missing
WHERE firstName LIKE "%%"
HAVING curage=15
AND thenage=3
[/php]


Query 2: (Same as query 1, except the results are ORDERED by the lastname and only twenty records are sent back at the time)
[php]
SELECT *,
DATE_FORMAT(dob, '%b %e, %Y') AS dob,
DATE_FORMAT(missingDate, '%b %e, %Y') AS missingDate,
CAST(((YEAR(CURDATE()) - YEAR(dob)) - (RIGHT(CURDATE(),5) < RIGHT(dob,5))) AS UNSIGNED) AS curage,
CAST(((YEAR(missingDate) - YEAR(dob)) - (RIGHT(missingDate,5) < RIGHT(dob,5))) AS UNSIGNED) AS thenage
FROM missing
WHERE firstName LIKE "%%"
HAVING curage=15
AND thenage=3
ORDER BY lastName
LIMIT 0,20
[/php]

Is there any way I can have only *1* query rather than 2? Or should I not be concerned about that hit to the DB?

I use mysql_num_rows($result) in PHP to see how many rows Query 1 gives me.

GHoosdum

How many records in the DB? If you're under 10,000 or so, don't worry about transaction latency too much; 2 queries would be just fine.

a2jfreak

Right now that table has ~725 records