[SOLVED] Query Query :S

[brissy_matty]

Hi All,

 

I have a question im a little stuck on:- What i have is a database which contains a whole lot of movie session information. This includes the following fields - Date, Time, Movie Name, Location

 

What i am hoping to achieve is an output similar to below (formatting isnt important) but i am stuck on the query i should be using in PHP to make this happen.  I think i can achive it by having mutiple loops and loops within loops - however i am aldo thinking there is a much better solution.

 

Remembering that a movie may be in the database like 60 times with exactly the same name just different dates and times - i only need it to extract the specific movie based upon its name and i only want to display the results from a specific location. I have no problems getting just the session times from one location based on one movie on one day.  But when i want to show the results like below im majorly confused - if someone could point me in the right direction - or to a good tutorial which may be helpful it would be greatly appreaciated.

 

================== EXAMPLE OUTPUT BELOW ======================

 

PIRATES OF THE CARIBBEAN: AT WORLDS END

 

Thursday 6th June

10:00AM  11:00AM  12:00PM  1:00PM  2:00PM

 

Friday 7th June

11:00AM  12:00PM  3:00PM

 

Saturday 8th June

4:00PM  5:00PM  6:00PM

 

======================= END OF EXAMPLE =====================

[bubblegum.anarchy]

Consider a query that will collate all the information in the following format:

 

Movie | Date | Session Times

 

... and display the results in an order fashion using PHP.

 

Use GROUP_CONCAT for the session times, depending on how the session times are stored.

[brissy_matty]

hmmm I would still be stuck with the different dates.  The format of the database is far from practical all the date and time columns are in varchar but i have worked around that problem - unfortunately i cannot change them due to the data being exported systematically from an msaccess database.

 

 

the existing database is structured like this:

 

 

Title                  Time                                Date                Location

Spiderman 3        1/01/1900 10:30:00 AM      31/05/2007        Location 1

Spiderman 3        1/01/1900 10:30:00 AM      31/05/2007        Location 2

Spiderman 3        1/01/1900 11:30:00 AM      31/05/2007        Location 1

Spiderman 3        1/01/1900 11:30:00 AM      31/05/2007        Location 2

 

The formating of the dates and times from the varchar field i am fine with in fact i have AJAX working fine for selecting a movie by location and by date which will then show the session times.

 

But what i am after shows all the dates and session times for a movie as formated in my previous post.  Its the query/queries to achieve a result like this i am stuck on - given only the location and the movie name i want it to display the dates with times for any available dates in the database i.e MON, TUE, WED etc etc depending on dates that the movie will be screening.

[TreeNode]

pseudo:

 

$db = mysql_connection(h, u, p) or new_mysqli(h, u, p);

$SQL = "SELECT title, time, date, location FROM movieTable WHERE title = 'Spiderman 3' ORDER BY date, time ASC";

$result = mysql_query($SQL) or $db->query($SQL)

if (mysql_num_rows($result)) or ($result->num_rows) {

  $result_row = mysql_fetch_array($result) or $result->fetch_array(MYSQLI_ASSOC);

  $title = $result_row['title'];

  $location = $result_row['location'];

  do {

      $time = $result_row['time'];

      $date = $result_row['date'];

      echo $title . ", " . $time . ", " . $date . ", " . $location . "<b_r>";     

  } while ($result_row = mysql_fetch_array($result) or $result->fetch_array(MYSQLI_ASSOC));

}

else

NO RESULTS

 

[Illusion]

$result = msql_query('SELECT Time,Date FROM Table where title=$title and location=$location order by date,time ASC', $con);
if (!$result) {
    die('Query execution problem: ' . msql_error());
}

while ($row = msql_fetch_row($result)) {
  $time=explode($row[0]);
echo date("l dS F", strtotime("$row[1]")."  ".$time[1]." ".$time[2];
       
}

msql_free_result($result);

 

It will give the output as

 

 

Thursday 6th June  10:30:00 AM

Thursday 6th June  10:40:00 AM

 

like that..........do some manipulation to get the required format.

 

 

[Illusion]

echo date("l dS F", strtotime("$row[1]")."  ".$time[1]." ".$time[2];
       

 

 

Small modification

echo date("l dS F", strtotime($row[1])."  ".$time[1]." ".$time[2];
       

[brissy_matty]

okay so i am guessing that the only way to get it display all the times where there is an unknown quantity of them -  some will have 6 times others will have one or 2 - would be to setp up a loop ?

[TreeNode]

if you do:

while (my_row = mysql_object->fetch_array(...)) {

}

 

it will continue till there is no more data left to output from your query, so yes, it is better to setup a loop because you're data might change eventually