Jump to content

Mysql Sorting Problem


Recommended Posts

Hey all I'm trying to output some info from a mysql table. I am having troubles figuring out how to do it in a specific manner. I'm thinking I might need nested calls or to use a for, or foreach loop. Anyways, I keep getting really confused, and can't come up with a solution. This is my mysql table structure, pardon my formatting of the table here, tried to make it all line up right as possible, but you should still see my basic structure!

|  Date		|  Venue	  |  Person			|  2011-12-29 |  Palace	 |  Greg				|  2011-12-29 |  Palace	 |  John				|  2011-12-29 |  Palace	   |  Scott			  |  2011-12-29 |  Riggers	|  Bob				|  2011-12-29 |  Riggers	|  Sam				|  2011-12-30 |  Mankind   |  Greg				|  2011-12-30 |  Mankind   |  Scott				|  2011-12-30 |  Palace	   |  Chris				

I'm trying to output this information into div's formatted in this specific way.

<div id="date">2011-12-29 <div id="venue">Palace<div id="person">Greg</div><div id="person">John</div><div id="person">Scott</div></div> <div id="venue">Riggers<div id="person">Bob</div><div id="person">Sam</div></div> </div> <div id="date">2011-12-30 <div id="venue">Mankind<div id="person">Greg</div><div id="person">Scott</div></div> <div id="venue">Palace<div id="person">Chris</div></div> </div>

Getting everything to echo out in this way has really been stumping me to no end. Any pointers would be appreciated! Thank you so much.

Edited by chasethemetal
Link to post
Share on other sites

I am not sure where are you facing problem? can you be specific please?

Link to post
Share on other sites

What does my Select statement need to look like? Which looping method should I use to get the information output'd like this? For instance the basic way to loop the data out looks like this.

 $con = mysql_query("SELECT * FROM events GROUP BY date,venue ORDER BY date ASC", $db) while($data = mysql_fetch_array($con)){echo $data['date'];echo $data['venue'];echo $data['people'];};

But this doesn't result in the data formated in which there is only ONE date for every related Venue, with each Venue then having the related people inside. I am trying to have my information sorted and grouped so that the echo'd output would result in how my div structure above is. I hope that explained my situation more clearly.

Edited by chasethemetal
Link to post
Share on other sites
What does my Select statement need to look like?
your query seems fine
Which looping method should I use to get the information output'd like this?
Every loop has different purpose and has its own features. but you can make any loop in such manner where it can overlap the feature of other loop. what i mean is most of the example/practises for database fetching use while loop but you can use any other loop too. something like this should work. it is checking the previous date and the current date. if its new it will start a new div else it will continue to fetch.
  $dateFlag='';while($data = mysql_fetch_array($con)){if($dateFlag!=$data['date']){echo "<div id='date'>{$data['date']}";}else{$dateFlag=$dbadta['date'];}//Here will be echo of your other fields if($dateFlag!=$data['date']){echo '</div>'; //close the div}}

Edited by birbal
Link to post
Share on other sites

Thanks birbal. I've tried your method, this doesn't produce the results I'm looking for. It still echo's a DIV for every date record, so we will have repeated dates... Also I'm not sure what "$dbadta" in your code does, is that just to make a null value? This is what your method would echo

<div id="date">2011-12-29</div><div id="date">2011-12-29</div><div id="date">2011-12-30</div><div id="date">2011-12-30</div>

I'm searching a way to have one date, with it's related venues, and then related people inside that.

Link to post
Share on other sites
$dateFlag=''; while($data = mysql_fetch_array($con)){if($dateFlag!=$data['date']){echo "<div id='date'>{$data['date']}";}else{$dateFlag=$data['date'];}//Here will be echo of your other fields if($dateFlag!=$data['date']){echo '</div>'; //close the div}

my bad. it was a typo. here is the corrected one

Link to post
Share on other sites

Thanks birbal. This still doesn't result in what I'm searching for. It will still list multiple same dates, take a close look at my desired div output structure above, and then take a closer look at how my table is structured. You'll see that the method you are suggesting doesn't echo the desired results. I am working on something right now that uses ajax to build the div structure on click. But I would love to find a way to have all the information outputed at once. Again if anyone know's what I'm trying to do please point me in the right direction.

Edited by chasethemetal
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...