Returning data from DB and showing it in specific way

udwo · September 17, 2015, 7:13pm

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

Drummin · September 18, 2015, 7:32am

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";

udwo · September 18, 2015, 3:35pm

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.

Drummin · September 18, 2015, 4:09pm

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

udwo · September 18, 2015, 4:13pm

correct

Drummin · September 18, 2015, 4:23pm

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?

udwo · September 18, 2015, 4:43pm

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());

Drummin · September 18, 2015, 8:19pm

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.

udwo · September 18, 2015, 9:21pm

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!

udwo · September 21, 2015, 7:29pm

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.

Drummin · September 21, 2015, 9:06pm

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):

udwo · September 22, 2015, 3:41pm

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?

Drummin · September 22, 2015, 4:03pm

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

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

udwo · September 22, 2015, 6:05pm

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();
}

Drummin · September 22, 2015, 6:29pm

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

udwo · September 22, 2015, 6:34pm

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

Drummin · September 22, 2015, 6:43pm

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

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

udwo · September 22, 2015, 6:45pm

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

Drummin · September 22, 2015, 7:42pm

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>

udwo · September 22, 2015, 8:42pm

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

Topic		Replies	Views
How to get results from database and display like this? PHP	31	2058	March 15, 2020
Join three tables and display info form all three PHP	16	14995	October 8, 2014
PHP formating MySql data PHP	6	1208	October 18, 2014
How to manage PHP page PHP	8	2579	February 11, 2018
Syntax help needed PHP	25	1286	March 7, 2011

Returning data from DB and showing it in specific way

Related topics