Jump to content

[SOLVED] Query Query :S


brissy_matty

Recommended Posts

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 =====================

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

$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.

 

 

Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

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.   Restore formatting

  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.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.