Jump to content


Photo

Pulling Lists from MySQL


  • Please log in to reply
5 replies to this topic

#1 WendyLady

WendyLady
  • Members
  • PipPipPip
  • Advanced Member
  • 38 posts

Posted 21 August 2006 - 02:01 AM

I hope this is a simple question that I've just been sitting here too long writing code & my brain is fried.

What I am basically trying to do is pull lists from MySql.  I have three tables.  One table is a list of possible dates, and the other two tables have various events, each one with a date that corresponds to one in the first table.

What I want to be able to do is pull each date, then list below each one all the rows from the 2nd & 3rd tables that have that date.  There may be multiple dates, each one with multiple listings below it.  For some reason, I can't get my brain around how to echo this out logically.

The logic flows as:
--find all dates in table 1 that have boolean flag of "1"
--go to table 2 and table 3 and find all events that have one of these dates
--list each date with its corresponding events below

I'm not looking for explicit code, but rather just some help with how to do this in code.  It seems like it should be simple, so it is making me feel really dumb, LOL.  Am I thinking about it too hard?

Thanks!

Wendy

#2 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 21 August 2006 - 02:20 AM

I find that I also tend to over think things. Any ways I think that this is what you are asking for.

Here is an example for getting the data from the first table with the where clause of 1
$sql= mysql_query("SELECT * FROM `table1` WHERE `something` = '1'") or die(mysql_error());
while ($row = mysql_fetch_assoc($sql)) {
         $date = $row['date'];//or what ever row you want from table one
         $q = mysql_query("SELECT `event` FROM `table2`, `table3` WHERE `date` = '$date'") or die(mysql_error());
         while($rw = mysql_fetch_assoc($q)) {
                 $event = $rw['event'];
                  echo "$date<br />";
                  echo "$event<br />";
         }
}

Hope this helps.
Tom

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#3 switchdoc

switchdoc
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationColorado

Posted 21 August 2006 - 02:21 AM

I believe what you need here is a JOIN statement.

Here are a few pages on it. See if this helps:

http://mysql.com/doc....0/en/join.html
http://www.keithjbro...ql/mysql_p5.php

#4 switchdoc

switchdoc
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationColorado

Posted 21 August 2006 - 02:22 AM

Looks like Tom beat me too it, and better too!

#5 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 21 August 2006 - 09:17 AM

BE CAREFUL of the script presented by Tom...

Many hosts limit you to 50 queries per script.Switch doc was correct in that a join could be used... But the script above would fail if 50 records were returned from teh first query (on MOST hosting packages - unless you have extended rights and can remove any restrictiosn on your site.)

The alternative is loading ALL the contents of each table into an array (one array for each table) and then using hash tables to grab the correct results (no where near as pretty as the queries IMO but won't fail like that scritp could)
follow me on twitter @PHPsycho

#6 WendyLady

WendyLady
  • Members
  • PipPipPip
  • Advanced Member
  • 38 posts

Posted 28 August 2006 - 05:59 PM

Sorry for long delay before adding update:

I didn't get any of these to work, and finally decided to add a secondary step.  The user first sees a list of dates, then chooses one & a second query pulls everything for that date.  It is slightly more convoluted for the user, but it works well.

Thanks everyone!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users