Recent Topics
  1. b2evolution CMS Support Forums
  2. Miscellaneous
  3. Chat away
  4. more silly 1103 questions...

more silly 1103 questions...

1 Feb 25, 2008 03:24    

So far so good. I've totally butchered the message form to make an online flight contest that'll last the year. People register and add flights via forms, and stuff gets displayed by pulling crap from shiny new tables.

First thing that's got me upset is that when someone enters a flight the "flights" table stores the pilotID number along with all the groovy data about the flight. Eventually I have a page that lists the furthest flight from each site. On that page I want to say "XXX miles on NNN date by ZZZZZZ" where ZZZZZZ is the pilot's name. So is there a groovy way to JOIN the pilots table to the flights table? Here is, effectively, the query I'm doing to get the record-setting flight: 

$query = ("SELECT pilots_number, site, start_date, end_place, MAX(miles), story FROM `flights` WHERE wing_type = 'hg' AND flight_type = 'opendistance' GROUP BY `site` ORDER BY `site` ASC");

Oddly enough, it actually works ;) So I figure I could just do another query to get the pilot's name from the pilots table using the pilots_number value, but it seems to me this would be a good place for a "join", or can I not JOIN if I'm GROUPING?

Next up, and this one is purely cosmetic, is converting "story" back to something that looks like a blog post. I would *love* to be able to push it through the smilies plugin and the auto-P plugin, but have no idea how to do that given that they fire on an actual item. Any hints on how I can do that? Like when the person types in a "return return" I should be able to make it display like that. Instead all I get is one huge paragraph of exactly what was entered.

I guess I should also be afraid of malicious crap hidden in html in the "story" but it's all old people who don't know html from a hole in the ground so I'm not too worried. BUT if it's easy to scrub an input the way comments on a post would be scrubbed that'd be way cool.

Thanks much for any help anyone can offer!

Back to top

2 Feb 25, 2008 03:45

$query = ("SELECT flights.pilots_number, flights.site, flights.start_date, filghts.end_place, flights.MAX(miles), flights.story, pilots.pilot_name pilots.pilots_number FROM `flights`, `pilots` WHERE flights.pilots_number = pilots.pilots_number AND wing_type = 'hg' AND flight_type = 'opendistance' GROUP BY `site` ORDER BY `site` ASC");

I give you some Yabba type warranty only, because you see, I couldn' t test and actually I am blonde also.

--F

Back to top

3 Feb 25, 2008 03:55

Groovy! Looks really smart too. I'll give it a shot here very soon.

Thanks!

EDIT: by the way http://ahga.org/azxc08.php?disp=azxc_hg is the page this particular query is feeding (and is currently full of fake data for testing/developing purposes). It's kinda neat to make it look like posts in a blog yah? But getting paragraphs and smilies working is not that important given that I can come back to it in a few months and try again.

Back to top

4 Feb 25, 2008 04:02

$query = ("SELECT pilots.pilot_name, flights.site, flights.start_date, filghts.end_place, flights.MAX(miles), flights.story FROM `flights`, `pilots` WHERE flights.pilots_number = pilots.pilots_number AND wing_type = 'hg' AND flight_type = 'opendistance' GROUP BY `site` ORDER BY `site` ASC");


No need for the pilots_number in the output query

Back to top

5 Feb 25, 2008 04:40

I ended up with "..... FROM this LEFT JOIN that ON this.foo = that.foo ....." which seems to do the trick. Oddly enough just listing the tables should have but didn't, and plain old JOIN didn't work either. I seem to recall "what should work didn't" last time I tried to do this type of thing, which happened to be for the same domain on the same server. Reckon it's something there, but not enough to worry over.

I'll play with crafting up some simplified home-made renderers to apply to the flight stories before committing to the database later. I think it's finally time to call that project "good enough" and get back to 240 skins. :)

Back to top

6 Feb 25, 2008 05:06

oops. Somehow it is getting the MAX(miles) but mixing it with fields from different entries in the table. Even did it via phpmyadmin and got the same screwup. hmmm.... gonna just make it be multiple queries I guess.

Back to top

7 Feb 25, 2008 06:13

hmmm.... Seems "GROUP BY" did weird things to the results. Specifically it mixed stuff up such that when I did a print_r on the results it would tell me that line item 17 had the distance results from line item 18 and the story text from line item 18. An abbreviated version of what seems to work is: 

$query = ("SELECT flights.site, flights.various, flights.miles, pilots.name FROM `azxc08_flights` LEFT JOIN `azxc08_pilots` ON flights.pilots_number = pilots.pilots_number WHERE foo = 'bar' AND bing = 'bang' ORDER BY `site` ASC, `miles` DESC");

$sites = mysql_query($query);

$previous_site = '';

while( $a_site = mysql_fetch_assoc($sites) ) {
	$site = $a_site['site'];
	if( $previous_site == $site ) {
		continue;
		}
	$previous_site = $site;
	...
	}

Not bad I guess.

Back to top


Form is loading...