I'm trying to get the latest post from any entry in one of two tables in a database. The query I'm using is:

$result = mysql_query("SELECT * FROM `strategyguides`,`editorials` ORDER BY `date` DESC LIMIT 0 , 1");

It comes back with an error that says the 'date' section is "ambiguous"...

Any help?

I have had the same problem. I searched google and found it is because you are limiting to 1 record, 1 record cannot be sorted. I know what you want to do (arrange, then get first record) but this doesn't seem to be the way to do it.

Any suggestions?

it also could mean that a field called "date" appears in both tables

Also note that date is a reserved word and should not be used for column names which may also be the cause of the ambiguity error.

As chrishirst already writes, the date column is probbaly in both tables. You need to tell MySQL which of the two date you want (`strategyguides` or `editorials`). This is simply done by prefixing the tablename before the column name.. e.g: `editorials`.`date` or
`strategyguides`.`date` depending on which one you want.

So try

$result = mysql_query("SELECT * FROM `strategyguides`,`editorials` ORDER BY `editorials`.`date` DESC LIMIT 0 , 1");

or

$result = mysql_query("SELECT * FROM `strategyguides`,`editorials` ORDER BY `strategyguides`.`date` DESC LIMIT 0 , 1");

This is my first post - so go easy

But I want it from both... I want the query to pick up the newest entry from two tables.

Make one for each...

$result = mysql_query("SELECT * FROM `strategyguides`,`editorials` ORDER BY `editorials`.`date` DESC, `strategyguides`.`date` DESC LIMIT 0 , 1");

Originally Posted by chrishirst

Also note that date is a reserved word and should not be used for column names which may also be the cause of the ambiguity error.

As chrishirst says, date is reserved in mysql.. try changing the column names to something like

"$result = mysql_query("SELECT * FROM `strategyguides`,`editorials` ORDER BY `editorials`.`date1` DESC, `strategyguides`.`date1` DESC LIMIT 0 , 1");"

What other words are reserved? for example is string reserved?

http://dev.mysql.com/doc/refman/5.0/...ved-words.html lists reserved-words.. But if you use back-ticks (`) you should be safe.

Alright, my next problem in what I figure will be a LONG line of mySQL query problems... lol

My example database is like this
ENTRY 1 : Bob, The Zoo, 3
ENTRY 2 : Frank, The Zoo, 4
ENTRY 3 : Tom, The Park, 5

What I want is a query that will find the one in the middle (called category), and list them out, but not puting any duplicates. For example, if it was to use the above database I gave then the output would be:
The Zoo
The Park

Is there any way to do this or no?

use ... GROUP BY fieldname ... in the query

http://www.w3schools.com/sql/sql_groupby.asp

Alright, and so to finish off this script, I need to add to that SQL query something that will let me know how many entries there are with the condition CATEGORY = THIS_CATEGORY

Here's the query right now: SELECT a.*, b.realname FROM `sounds` AS a LEFT JOIN `staff` AS b ON b.id = a.staffid GROUP BY `category` ORDER BY `id` ASC

Thank you for the continuous support and help!

select count(*) from table as alias where column = criteria

So I put these many many many different querys together to get the one final query, and it gave me an error. I don't know what's wrong with it, but maybe someone does.

SELECT a.*, b.realname,a.count(*) FROM `sounds` AS a LEFT JOIN `staff` AS b ON b.id = a.staffid GROUP BY `category` WHERE `category` = CONVERT(_utf8 'Front Gate' USING latin1) COLLATE latin1_general_ci ORDER BY `id` ASC

Thanks!

and the error was ??

#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 `sounds` AS a LEFT JOIN `staff` AS b ON b . id = a . staffid GROUP BY' at line 1

a.count(*) AS alias

SELECT a.*, b.realname,a.count(*) AS `music` FROM `sounds` AS a LEFT JOIN `staff` AS b ON b.id = a.staffid GROUP BY `category` WHERE `category` = CONVERT(_utf8 'Front Gate' USING latin1) COLLATE latin1_general_ci ORDER BY `id` ASC

is what you're telling me, this is the error it gives me now:

#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 `music` FROM `sounds` AS a LEFT JOIN `staff` AS b ON b . id = a . staff' at line 1

Any last ideas before I give up on this completely?

works ok for me (with different tables of course)

what version of MySQL is it? and is that a direct copy of the query?