fenway

No, it won't cause problems -- you just need to alias your columns.

Try again.

Oh, I can definitely make it more efficient -- without a doubt.

Method 2 -- no magic types.

Why do you have duplicates? GROUP BY isn't the same as DISTINCT.

OK, I'll play with it over the next few days.

You can't use GROUP BY like that.

I just tested your query in 5.1.41-community, and I get this error: "column ref_id cannot be null". Are you sure that query is the one you're running, or that those tables are correct?

And you think mysql is doing this?

(2) is the preferred way to go. yes, it's easier to have fewer tables, but it makes life more complicated if you never need multiple groups.

I have no idea what you're talking about -- but it doesn' sounds like mysql.

"learning" mysqli doesn't mean anything -- it's a wrapper class -- you shouldn't have to change anything.

This is a typical anti-pattern. What if you have the same UID in both the video and photo table? A somewhat-better way to to have two columns: video_attachment_uid and photo_attachment_uid. Of course, that's limiting if you ever need 2. You're supposed to have another table for each such attachment -- maybe with a base table if they share many attributes (e.g. title,description,date,etc.) -- so that many-to-one is easy.

So you want to group all the Mondays together?

Then you have an error --- check mysql_error().

Well, you'll have to fetch() something.

Read the rules, and try again.

Same day, or same date?

Everything can always be done more efficiently -- to a point -- but I didn't see anything wrong with your approach on first glance. It's true that a summary table that you generate "periodically" with leave you with a proper DB and give you the speed of no joins -- but you can make that choice later. I'm working towards a Friday deadline, but I should have time over the weekend to play with it.

So you've asked for my help, and I've investing time into getting to the root cause, and you give up? You wouldn't have anywhere near this many joins -- maybe 2 or 3, maximum. Instead, you're opting for a flat-flat DB design -- all because you don't want to wait. And by "wait", I mean give me enough information to proceed. Remember, you PM'ed me to take an interest -- are you saying that I've wasted all of my time?

Well, if I change the query, how I am supposed to verify that I haven't screwed up the query? You'll need to run the existing query for the sample data, and tell me what I should expect to get -- then I can play with it confidently.

That will work, but it's the wrong approach. Don't have mysql do more work to make your life easier.

$result is an object, not a column value.

So, to be clear, those are the correct values for the sample data you provided?

There is no "order" to records. That simply means you've deleted some along the way. This isn't a real problem -- don't pretend that it is. There's an ORDER BY clause to deal with ordering. But you should never be relying on the auto-increment for ordering anyway, since it's a magic number that you don't control.