Returning data from DB and showing it in specific way

Hello all,

i need help to show data from mySQL db i certain way. I have 2 tables in DB, one that has program for the day (channelID, name, time) and one that has references to time blocks with links to load appropriate video (id, channelID, url). the program can have multiple entries per hour (f.e. 10:00 news, 11:30 weather) but the links are fixed in 1 hr intervals. i need to show the program in 2 columns:

10:00 news

11:30 weather

and load appropriate link so if the interval is between 10:00:00-10:59:59 load url for 10hr block (so the news would be clickable and would link to 10hr.m3u8 f.e.), if 11:00:00-11:59:59 load the 11hr block (so the weather would be clickable and would link to 11hr.m3u8 f.e.)

any help is appreciated

I don’t see DATE field anywhere and it’s a little unclear of table names. Actual display of programs by time could be done after data for the day has been grabbed… Here’s a wild stab at a query.

$sql = "SELECT
      p.channelID
    , p.name
    , p.time
    , t.url    
    FROM programs as p
        LEFT JOIN time_blocks as t
            ON t.channelID = p.channelID
    ORDER BY p.time ASC";

thank you so much Drummin for responding.

the DATE field is column “start” in “epg table” the times do not align with the time blocks… in the programs, i can have f.e. 10:00 am - Program 1, 10:15 am - Program 2, 10:45am - program 3 , but in the other table time blocks that span 1 hour at a time. so each channel would have 24 url. here is the structure exactly:

table 1:
name: epg
columns: id_epg, start, title, description, id_channel

table 2:
name: tblMobile
columns: mobileID, id_channel, mobileURL, timeBlockIndicator

i was thinking i could have 2 separate queries; 1 to retrieve the data from epg table and one from tblMobile, store the tblMobile in an array and then search the array at every time the loop happens for the epg data writing to the page and pull appropriate link. but i was unsuccessful with that.

SO in epg the start field is a date time field?

correct

Is it possible to POST sql you are working with and what you’ve tried?
It is still unclear the relationship… as you use different terms when talking about what you’s like than the actual table names. I am thinking now that your tblMobile table is your primary table with time blocks stored in the field timeBlockIndicator but what kind of data is it holding? Just the hour? 10:00:00 or some other value.

epg then has specific programs (shows, videos whatever) 10:00 am - Program 1, 10:15 am - Program 2, 10:45am - program 3

Is that right?

the timeBlockIndicator field has just values 0,1,2,3… 24 so it indicates what time block that url is for f.e. 01:00am - 01:59am, the timeBlockIndicator value is1

the epg table holds values for program data (here is a sample of a row):

id_epg         id_channel     start                             title              description
124227009  40                  2015-08-23 23:55:00   Ma reggel    Felirato… oldalán!

this is query #1:

$date1 = date('Y-m-d 00:00:01');
$varTimeToday1 = strtotime ( '+ 6 hours' , strtotime ( $date1 ) ) ;
$varTimeToday1 = date ( 'Y-m-d h:m:s' , $varTimeToday1 );

$date2 = date('Y-m-d 23:59:59');
$varTimeToday2 = strtotime ( '+ 6 hours' , strtotime ( $date2 ) ) ;
$varTimeToday2 = date ( 'Y-m-d h:m:s' , $varTimeToday2 );

$thisCHid = $_GET['id'];


$result4 = mysql_query("SELECT epg.id_channel, epg.start, epg.title, epg.description, tblTVMobileChannels.tvChannelID
  FROM tblTVMobileChannels INNER JOIN epg ON tblTVMobileChannels.epg_ID = epg.id_channel WHERE epg.start > '$date1' AND  epg.start < '$date2' AND tblTVMobileChannels.tvChannelActive = '1' AND tblTVMobileChannels.tvChannelID = $thisCHid;")or die(mysql_error());

this is query #2

$thisCHid = $_GET['id'];
    $resultLinks = mysql_query("SELECT tblMobile.mobileID, tblMobile.mobileURL, tblMobile.mobileFile, tblMobile.timeBlockIndicator FROM tblMobile WHERE tblMobile.id_channel= '$thisCHid' AND tblMobile.mobileActive = '1';")or die(mysql_error());

Well based on what I understand, here is a PDO sample. I am sure it could be improved upon.

<?php 
$login = "";
$dbpass = "";
$db = "";
try {
    $pdo = new PDO('mysql:host=localhost;dbname='.$db, $login, $dbpass);
    $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );   
} catch (PDOException $e) {
    //echo "Error!: " . $e->getMessage() . "<br/>";
    echo "Database not available at this time";
    die();
}
?>
<html>
<body>
<?php 
$day   = date("Y-m-d");
$start = date("Y-m-d H:i:s", strtotime($day));
$end   = date("Y-m-d H:i:s", strtotime($day.' + 86400 seconds'));
$data = array();

$sql = "SELECT 
      e.start
    , e.title
    , e.description
    , t.mobileURL    
    , t.timeBlockIndicator
    , t.id_channel
    FROM epg as e 
        LEFT JOIN tblMobile as t
            ON t.epg_ID    = e.id_epg
    WHERE e.start BETWEEN :start AND :end AND t.mobileActive = '1'";
    
    if(isset($_GET['id']) && is_numeric($_GET['id'])):
        $sql .= " AND t.id_channel = :ChannelID ";
    endif; 
    
    $sql .= " ORDER BY e.start ASC";
    
    $query = $pdo->prepare($sql);
    $query->bindParam(':start', $start);    
    $query->bindParam(':end', $end);
    
    if(isset($_GET['id']) && is_numeric($_GET['id'])):    
        $query->bindParam(':ChannelID', $_GET['id']);
    endif;
         
    $query->execute();        
    while($row = $query->fetch(PDO::FETCH_ASSOC)){
        $data[$row['timeBlockIndicator']][strtotime($row['start'])] =    $row;
    }
    
    //echo "<pre>";
    //print_r($data);
    //echo "</pre>";    
    
    echo "<table>
    <tr>
        <th>Hour</th>
        <th>Time</th>
        <th>Name</th>
    </tr>\r";
    
    for($h=0;$h<=24;$h++):
        $hourstart = date("Y-m-d H:i:s", strtotime($start.' +'.$h.' hours'));
        $hourend = date("Y-m-d H:i:s", strtotime($hourstart.' +3599 seconds'));
        $hourstarttime = strtotime($hourstart);
        $hourendtime = strtotime($hourend);
        $hourshown = date("g:i a", strtotime($start.' +'.$h.' hours'));    
        
        echo '<tr>
        <td>'.$hourshown.'</td>'."\r";
                            
        
        if (array_key_exists($h,$data)){
            foreach($data[$h] as $showtime => $showdata):
                //double check time
                if($showtime >=    $hourstarttime && $showtime <= $hourendtime){
                    $showtimeshown = date("g:i a", $showtime);
                    // format link as needed 
                    $link = $showdata['mobileURL'];                    
                    echo '<td>'.$showtimeshown.'</td><td><a href="'.$link.'">'.$showdata['title'].'</a></td>'."\r";
                }else{                
                    echo '<td>&nbsp;</td><td>&nbsp;</td>'."\r";        
                }        
            endforeach;
        }else{                
            echo '<td>&nbsp;</td><td>&nbsp;</td>'."\r";        
        }
        
        echo '</tr>'."\r";
    
    endfor;
    echo '</table>'."\r"; 
?>
</body>
</html>

EDIT: Note it is hard to follow your query samples as they use different table/field names. Adjust sample as needed.

i will work on it tonight and let you know where i get with it. thank you so much with taking time to help me. i will be in touch!

unfortunately i don’t think it worked. here is the data and the column names to maybe demonstrate better what i need. I can change anything in the tblMobile if needed but can’t do any modifications on the epg table as that is a 3rd party data that is dumped into our DB daily.

id_epg       id_channel     start                      title
124719306 2 2015-08-27 00:05:00 BET LECHEM - vnitřní domov
124719308 2 2015-08-27 00:20:00 Putování modrou planetou
124719310 2 2015-08-27 01:00:00 Zambijský Bambomisioná…
124719312 2 2015-08-27 01:25:00 Cesta do turecké země
124719314 2 2015-08-27 01:45:00 Volání Etiopie. Situace katolické církve v Etiopii
124719316 2 2015-08-27 02:00:00 Se salesiány na jihu Madagaskaru
124719318 2 2015-08-27 02:20:00 Na jedné lodi
124719320 2 2015-08-27 03:25:00 Poutní chrám Panny Marie na Chlumku v Luži
124719322 2 2015-08-27 03:35:00 Přejeme si
124719324 2 2015-08-27 03:50:00 Dát smysl života

mobileID id_channel mobileURL                                                          mobileIndicator mobileActive
1                         2           http://iphone.online…:5000/archive/all.m3u8   A                              1
2                         2            http://iphone.online…:5000/archive/00.m3u8 0s                             1
3                         2            http://iphone.online…:5000/archive/01.m3u8 1s                             1
4                         2            http://iphone.online…:5000/archive/02.m3u8 2s                             1
5                         2            http://iphone.online…:5000/archive/03.m3u8 3s                             1
6                         2            http://iphone.online…:5000/archive/04.m3u8 4s                              1

Ok, so from this example all titles from epg table f.e. between 01:00:00through 01:59:59 would be linked using link ending in 01.m3u8 with mobileIndicator 1s. then each record between 02:00:00 through 02:59:59 would be linked with link ending in 02.m3u8 with mobileIndicator 2s. hope this makes more sense.

I thought you said mobileIndicator would have hour numbers (0-24) but looking at your post they have an ‘s’ after the number. If you add the ‘s’ after the number on these lines does it show records?

        if (array_key_exists($h.'s',$data)){
            foreach($data[$h.'s'] as $showtime => $showdata):

I get nothing, blank page. it is like it doesn’t move pass the connection to the DB. i don’t get an error msg that it didn’t connect to the DB. i just get blank page. I tried to put simple echo(“test”); after the connection and did not even get that. I included the user name, password and name of the DB into the variables. this may be possibly due to PHP 5.0.4 version we are using?

Un comment that line and does it show an error message?

//echo "Error!: " . $e->getMessage() . "<br/>";

grr… not working. this is what i have and i get no echo on either. i also tried to replace the $pdo with $conn

$servername = "localhost";
$login = ".....";
$dbpass = ".......";
$db = "......";

try {
    $pdo = new PDO("mysql:host=" . $servername . ";dbname=" . $db, $login, $dbpass);
    $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); 
    echo "Connected successfully";  
    }
    catch (PDOException $e) {
    echo "Error!: " . $e->getMessage() . "<br/>";
    echo "Database not available at this time";
    die();
}

Are you saying you don’t see the echo "Connected successfully"; line?

If you are not getting an error on connection, what does the rest of your code specifically the query look like? Have you compared table and field names to make sure they are correct? If you don’t spot a problem, wrap the query in the same kind of TRY/CATCH block to show errors

i don’t get echo for either Connected successfully nor Error. i will double check the query one more time. at the moment i get white page, nothing on it

Are you running a modified version of my code from post#8?

Let me see about converting it to mysqli… what a pain.

all i did is fill in the values for the connection variables and corrected the values in the query to make sure all names of columns are correct

OK this is modified mysqli version that also has the ‘s’ in the timeblock. As always, compare to your tables.

<?php 
$login = "";
$dbpass = "";
$dbname = "";

$conn = mysqli_connect('localhost', $login,$dbpass,$dbname);
?>
<html>
<body>
<?php 
$day   = date("Y-m-d");
$start = date("Y-m-d H:i:s", strtotime($day));
$end   = date("Y-m-d H:i:s", strtotime($day.' + 86400 seconds'));
$data = array();

$sql = "SELECT 
      e.start
    , e.title
    , e.description
    , t.mobileURL    
    , t.timeBlockIndicator
    , t.id_channel
    FROM epg as e 
        LEFT JOIN tblMobile as t
            ON t.epg_ID    = e.id_epg
    WHERE e.start BETWEEN ? AND ? AND t.mobileActive = '1'";
    
    if(isset($_GET['id']) && is_numeric($_GET['id'])):
        $sql .= " AND t.id_channel = ? ";
    endif; 
    
    $sql .= " ORDER BY e.start ASC";
    
    $query = $conn->prepare($sql);
    
    if(isset($_GET['id']) && is_numeric($_GET['id'])){    
        $query->bind_param('ssi', $start,$end,$_GET['id']);
    }else{ 
        $query->bind_param('ss', $start,$end);    
    }
         
    $query->execute();
    $result = $query->get_result();
       while ($row = $result->fetch_assoc()) {
        $data[$row['timeBlockIndicator']][strtotime($row['start'])] =    $row; 
    }
    
    //echo "<pre>";
    //print_r($data);
    //echo "</pre>";    
    
    echo "<table>
    <tr>
        <th>Hour</th>
        <th>Time</th>
        <th>Name</th>
    </tr>\r";
    
    for($h=0;$h<=24;$h++):
        $hourstart = date("Y-m-d H:i:s", strtotime($start.' +'.$h.' hours'));
        $hourend = date("Y-m-d H:i:s", strtotime($hourstart.' +3599 seconds'));
        $hourstarttime = strtotime($hourstart);
        $hourendtime = strtotime($hourend);
        $hourshown = date("g:i a", strtotime($start.' +'.$h.' hours'));    
        
        echo '<tr>
        <td>'.$hourshown.'</td>'."\r";
                            
        
        if (array_key_exists($h.'s',$data)){
            foreach($data[$h.'s'] as $showtime => $showdata):
                //double check time
                if($showtime >=    $hourstarttime && $showtime <= $hourendtime){
                    $showtimeshown = date("g:i a", $showtime);
                    // format link as needed 
                    $link = $showdata['mobileURL'];                    
                    echo '<td>'.$showtimeshown.'</td><td><a href="'.$link.'">'.$showdata['title'].'</a></td>'."\r";
                }else{                
                    echo '<td>&nbsp;</td><td>&nbsp;</td>'."\r";        
                }        
            endforeach;
        }else{                
            echo '<td>&nbsp;</td><td>&nbsp;</td>'."\r";        
        }
        
        echo '</tr>'."\r";
    
    endfor;
    echo '</table>'."\r"; 
?>
</body>
</html>

do i leave the ? in the WHERE statement? WHERE e.start BETWEEN ? AND ? AND