Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Calling and Linking up Data from table entries (SAM Broadcaster)

    I do apologise if my terminology is flawed, i am still desperately trying to learn how to do this stuff.
    I have spent weeks reading others posts about how to do this though everything i have done has failed to work.
    I figured its time i swallowed my pride and asked for some help
    If anyone can be so good as to help me out on this i would greatly apreciate it.

    I have a table, with a selection of categories,
    which all have sub categorys and in those there is lots of "results" or data..
    I am not sure how to phrase this.

    I don't know if any of you have had any experience with SAM Broadcaster and its PHP pages..
    these are what I am trying to modify

    I have been looking for a way to display these results on a page..
    preferably in a drop down menu,
    though at this point anything will do.

    Table - Category - SubCategory - Results

    All the connections to the db are already done in a config file... i basically need to:
    SELECT `genre` FROM songlist
    and display everything from in there..

    Thats for the start anyway.

    The idea is then to select a genre, for example metal.. or trance.. or whatever..
    and have every song come back that is in that genre

  • #2
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    I don't have any experience of SAM broadcaster. Other than what I just read about it on wikepedia. So obviously you have PHP installed on your server. And you have a DB. You say: "I have a table, with a selection of categories". How do you view that table are you using phpMyAdmin or something what type of sql is it? Is it mysql or another. Either way you would need an HTML SELECT (dropdown list) element which would post the data to another/same page then use the post data sent accross to run your query. Something like this. N.B The SQL will only work on a mysql database.

    PHP Code:
    <?PHP
    //checks if dropdown form data was sent if it wasn't then draw dropdown list 
    if(!isset($_POST))
    {
        echo

        <form method="post" action="">
              <select name="genre" onchange="this.form.submit();">
                   <option value="trance"> Trance
                   <option value="house"> House
                   <option value="rock"> Rock
              <select>
        </form>
    '
    ;
    }
    elseif(
    in_array($_POST["genre"], array("trance","house","rock"))) //in array is for validation. Would need to list all genres in array
    {
         
    //If the form was completed then select data from database
         
    $conn mysql_connect("localhost""mysql_user""mysql_password");

         
    //connect to DB
         
    if (!$conn) {
             echo 
    "Unable to connect to DB: " mysql_error();
             exit;
         }
      
         
    //select DB
         
    if (!mysql_select_db("mydbname")) {
             echo 
    "Unable to select mydbname: " mysql_error();
             exit;
         }

         
    // Select data. The * Star wildcard selects all fields in database. Using genre would be pointless as we already know what genre we are looking for that is in the where clause
         
    $sql "SELECT * FROM songlist WHERE genre='".$_POST["genre"]."' ";

         
    // Run the query
         
    $result mysql_query($sql);

         if (!
    $result) {
             echo 
    "Could not successfully run query ($sql) from DB: " mysql_error();
             exit;
         }

         
    // If no data with that genre was found tell them and exit
         
    if (mysql_num_rows($result) == 0) {
             echo 
    "No rows found, nothing to print so am exiting";
             exit;
         }

         
    // See next comment. For more info look at html tables
         
    echo '<table border="1">';
         $=
    0;


         while (
    $row mysql_fetch_assoc($result)) {
         
    // By this time it has fetched the data and has put them in an associative array see associative arrays. I will use this data to make a table of songs.
              
    echo "<tr>";
              echo 
    "<td>row ".$i.":</td>";
              
    $i++;
              foreach(
    $row as $key => $value)
              {
                    echo 
    "<td>".$key." is ".$value."</td>";
              }
              echo 
    "</tr>";
         }
         echo 
    "</table>";
    mysql_close($con);
    }
    else
    {
    //If validation failed
     
    echo "Hacker";

    }

    ?>
    Last edited by timgolding; 03-22-2009 at 03:56 PM.
    You can not say you know how to do something, until you can teach it to someone else.

  • Users who have thanked timgolding for this post:

    Slave (03-22-2009)

  • #3
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    thank you i will mess with that..

    as for the db.. its mysql and i look at in in msql admin or myswl query browser

    I did get this lil snippet of code that sorta helps with the populating of the dropdown menu

    Code:
    <?
    echo "<select name='genre'><option value=''>Select Genre</option>";
    $db->open("SELECT DISTINCT genre FROM songlist WHERE songtype='S' order by genre");
    while($results=$db->row()) {
      echo "<option value='$results[genre]'>$results[genre]</option>";
    }
    ?>
    the issue is.. now the playlist doesn't show...

    here is: http://www.nexusradio.co.uk/requests/playlist2.php
    which is the working origional code

    and here is: http://www.nexusradio.co.uk/requests/playlist3.php

    as you can see the genres are populated.. however the playlist now won't show.. and I don't know how to get the genre thing to search properly either...

    the origoinal search code is this:

    Code:
    <form method="POST" action="playlist2.php">
      <p>Search 
    <? InputText("search",$search,'',20); ?> <input type="submit" value="Go" name="B1">
    &nbsp;&nbsp;Display <? InputCombo("limit",$limit,25,'5,10,25,50,100'); ?> results
    
    </form>
    </p>
    
    
    Search by Artist:<br><a href='?letter=0'>0 - 9</a><?
     for($c=ord('A');$c<=ord('Z');$c++)
     {
      $v = chr($c);
      echo ", <a href='?letter=$v'>$v</a>";
     }
    ?>
    <br>
    Anyway I will see if I can apply any of the code you have kindly written for me to any of what i have done here
    Last edited by Slave; 03-22-2009 at 05:44 PM.

  • #4
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    The code you have posted in the above example with

    $db->open

    suggest that your code uses a class for handling the SQL requests. So you should look to use that class throughout. maybe replace all
    PHP Code:
    //If the form was completed then select data from database
         
    $conn mysql_connect("localhost""mysql_user""mysql_password");

         
    //connect to DB
         
    if (!$conn) {
             echo 
    "Unable to connect to DB: " mysql_error();
             exit;
         }
      
         
    //select DB
         
    if (!mysql_select_db("mydbname")) {
             echo 
    "Unable to select mydbname: " mysql_error();
             exit;
         }

         
    // Select data. The * Star wildcard selects all fields in database. Using genre would be pointless as we already know what genre we are looking for that is in the where clause
         
    $sql "SELECT * FROM songlist WHERE genre='".$_POST["genre"]."' ";

         
    // Run the query
         
    $result mysql_query($sql); 
    with

    PHP Code:
    $db->open("SELECT * FROM songlist WHERE genre='".$_POST["genre"]."' "); 
    I cant help you further without a more informed view of you code.
    You can not say you know how to do something, until you can teach it to someone else.

  • #5
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I tried using that origional bit of code (obviously editing in my sb name and pw and stuff) you suplied so i could get my head round what each line was doing...

    i got a tstring error over this bit: $=0;
    I figured it was supposed to be $i=0 but i got the hacker error... so i tried to blank it out and got the hacker error on that.

    What I have done is made something with check boxes in the search and then they can type and select what they want...

    the new search2.php is like this:

    Code:
    <form method="POST" name="database" action="playlist2.php">
    <? InputText("search",$search,'',55); ?>
    <input name="B1" value="Start search" type="submit" />  Show <? InputCombo("limit",$limit,25,'5,10,25,50,100'); ?> results
    
    <br>
    
    Look for:
    
    <input id="title"  name="dcat" value="title"  type="radio" /><label for="title" >Title</label>
    <input id="artist" name="dcat" value="artist" type="radio" /><label for="artist">Artist</label>
    <input id="album"  name="dcat" value="album"  type="radio" /><label for="album" >Album</label>
    <input id="albumyear"   name="dcat" value="albumyear"   type="radio" /><label for="albumyear"  >Albumyear</label>
    <input id="genre"  name="dcat" value="genre"  type="radio" /><label for="genre" >Genre</label>
    
    <br>
    
    <input type="checkbox" name="match" value="1" checked>&nbsp;exact search
         
    
    </form>
    
    
    
    
    Search by Artist:<br><a href='?letter=0'>0 - 9</a><?
     for($c=ord('A');$c<=ord('Z');$c++)
     {
      $v = chr($c);
      echo ", <a href='?letter=$v'>$v</a>";
     }
    ?>
    <br>
    which relates to the playlist2.php which is this

    Code:
    <head>
    <STYLE TYPE="text/css">
    <!--
    .dimlow, .dimlow TD, .dimlow TH
    
    {
    background-image:url('./../forum/styles/X-Treme Green/theme/images/cellpic3.gif');
    background-color:141414;
    color:white;
    font-family:sans-serif;
    }
    -->
    </STYLE>
    </head>
    <?
     require("config.php"); 
     
     $where = " WHERE (songtype='S') AND (status=0) ";
     
     Def($start,0);
     Def($limit,100);
     Def($search,"");
     Def($match, ""); // add match variable for checkbox
    Def($dcat, ""); // add cat variable for selectbox
    
    
     //########## BUILD SEARCH STRING ################
    if ("" == $dcat)
    {
        $dcat = "all";
    }
    
    if(!empty($search) && !$match) // search for all words in search string
     {
       $words = Array();
       $temp = explode(' ',$search);
       reset($temp);
       while(list($key,$val) = each($temp))
       {
        $val = trim($val);
        if(!empty($val))
    	 $words[] = $val;
       }
    	 
    
       $where2 = "";	  
       reset($words);
       while(list($key,$val) = each($words))	 
       {
         if(!empty($where2)) $where2 .= " OR ";
    	if ("all" == $dcat) // search all fields
         {
             $where2 .= " (title like '%$val%') OR (artist like '%$val%') OR (album like '%$val%') ";
         }
         else // search selected field only
         {
             $where2 .= " ($dcat like '%$val%') ";
         }
       }
       $where .= "AND ($where2) ";
    }
    else if (!empty($search) && $match == 1) // do exact match search
    {
       if ("all" == $dcat) // search exact match in all fields
       {
           $where2 .= " (title like '%$search%') OR (artist like '%$search%') OR (album like '%$search%') ";
       }
       else // search exact match in selected field only
       {
           $where2 .= " ($dcat like '%$search%') ";
       }
       $where .= "AND ($where2) ";
    }
     
     if((isset($letter)) && (!$letter==""))
     {
      $nextletter = chr(ord($letter)+1);
      if($letter=='0')
       $where .= " AND NOT((artist>='A') AND (artist<'ZZZZZZZZZZZ')) ";
      else
       {
        $db->AddStr($letter); $db->AddStr($nextletter);
        $where .= " AND ((artist>=:letter) AND (artist<:nextletter)) ";
       }
     }
     else
     {
    		$letter="";
     }
     
     //########## =================== ################ 
     
     //Calculate total
     $tmp = $db->params; //Save params for second query
     $db->open("SELECT count(*) as cnt FROM songlist $where ");
     $row = $db->row();
     $cnt = $row["cnt"];
     
     //Now grab a section of that
     $db->params = $tmp; //Restore params
     $db->open("SELECT * FROM songlist $where ORDER BY artist ASC, title ASC, genre ASC", $limit, $start);
     
     $first = $start+1;
     $last  = min($cnt,$start+$limit);
     $rc    = $start;
     
     $prevlnk = "";
     $nextlnk = "";
     if($cnt>0)
     {
     
     if(!isset($search))
    	 { $search=""; }
      $searchstr = urlencode($search);
      $prev = max(0,$start-$limit);
      if($start>0)
        $prevlnk = "<a href='?start=$prev&limit=$limit&letter=$letter&search=$searchstr'>&lt;&lt; Previous</a>";
       
      $tmp = ($start+$limit);
      if($tmp<$cnt) 
        $nextlnk = "<a href='?start=$tmp&limit=$limit&letter=$letter&search=$searchstr'>Next &gt;&gt;</a>";
     }
        
    function PutSongRow($song) 
    {
     global $rc, $start, $darkrow, $lightrow;
     
     $rc++;
     $bgcolor = $darkrow;
     if(($rc % 2)==0) $bgcolor = $lightrow;
     
     
     PrepareSong($song);
    ?> 
      <tr bgcolor="<? echo $bgcolor; ?>"> 
        <td nowrap align="right" width="1%"><font size="2" color="#00ff00"><small><? echo "$rc"; ?></small></font></td>
        <td nowrap><font size="2" color="#00ff00">&nbsp;<small><? echo $song["combine"]; ?></small></font></td>
    	
    	   
        <td nowrap width="1%"><font size="2" color="#ff0000">&nbsp;<small><? echo $song["genre"]; ?></small></font></td>
    	
    	<td nowrap width="1%"> 
          <p align="center"><font size="2" color="#00ff00"><a href="<? echo $song["request"]; ?>"><img
        src="images/request.gif" alt="Request this song now!" border="0"></a></font> 
        </td>
    	
        <td nowrap width="1%"> 
          <p align="center"><font size="2" color="#00ff00"><a href="<? echo $song["buycd"]; ?>" target="_blank"><img
        src="images/buy.gif" alt="Buy this CD now!" border="0"></a></font> 
        </td>
        <td nowrap width="1%"> 
          <p align="center"><font size="2" color="#00ff00"><a href="<? echo $song["website"]; ?>" target="_blank"><img
        src="images/home.gif" alt="Artist homepage" border="0"></a></font> 
        </td>
    	
    	<td nowrap align="center" width="1%"> 
          <font size="2" color="#00ff00"><a href="javascript:songinfo(<? echo $song["songid"]; ?>)"><img
        src="images/info.gif" alt="Song information" border="0"></a></font> 
        </td>
    	
        <td nowrap><font color="#00ff00" size="2"><small><? echo $song["album"]; ?></small></font></td>
        <td nowrap> 
          <p align="right"><font color="#00ff00" size="2"><small><strong><? echo $song["mmss"]; ?></strong></small></font>
        </td>
      </tr>
    <?
    }//PutSongRow
    
    /* ## ===================================================================== ## */
    ?>
    
    <? require("header2.php"); ?>
    
    <? require("search2.php"); ?>
    <br>
    
    
    <table border="0" width="98%" cellspacing="0" cellpadding="4">
      <tr class="dimlow"> 
        <td colspan="9" nowrap align="left"> 
          <b><font face="Verdana, Arial, Helvetica, sans-serif" size="1" color="#FFFFFF">Playlist results</font></b>
        </td>
      </tr>	
    <? 
      while($song = $db->row())
       PutSongRow($song); 
    ?>
      
      <tr class="dimlow"> 
        <td colspan="9" nowrap align="center">
    	<? echo "$prevlnk"; ?>
     &nbsp; ( Showing <? echo "$first to $last of $cnt"; ?> ) &nbsp; 	 
    	<? echo "$nextlnk"; ?></td>
      </tr>	
      
    </table>
    
    <br>
    <? require("search2.php"); ?>  
    <? require("footer.php"); ?>
    the trouble is.. no matter how i do it.. i can't put this code in

    Code:
    <?
    echo "<select name='genre'><option>Select Genre</option>";
    $db->open("SELECT DISTINCT genre FROM songlist WHERE songtype='S' order by genre");
    while($results=$db->row()) {
      echo "<option value='$results[genre]'>$results[genre]</option>";
    }
    ?>
    because it makes the playlist results not display...

    so for example

    Working: http://www.nexusradio.co.uk/requests/playlist2.php
    Not Working: http://www.nexusradio.co.uk/requests/playlist3.php

  • #6
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    not sure $db is a member of class and i don't see where is being declared is that in config.php. can we see the class? Look in config.php for class Classname it might be linked from within config.php with an include('path/to/class') or require_once('path/to/file')

    Where are you putting in the code:

    PHP Code:
    echo "<select name='genre'><option>Select Genre</option>";
    $db->open("SELECT DISTINCT genre FROM songlist WHERE songtype='S' order by genre");
    while(
    $results=$db->row()) {
      echo 
    "<option value='$results[genre]'>$results[genre]</option>";

    It might be that your selecting genres before the playlist has had a chance to render. E.g assingning new values to $db->row to early?
    You can not say you know how to do something, until you can teach it to someone else.

  • #7
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I am putting the table code in the search2.php... if i put ANYWHERE in the playlist2.php it ignroes it.. as if it were never there...

    config.php?

    here you go...

    Code:
    <?php
     
    /* ## ======================================== ## */  
      $commonpath = "./common";
      
      //Station general details
      $station  = "changes as per dj";
      $email    = "changes as per dj";
      $logo     = "images/logo.gif";
      
      $stationid   = 93192;           //The ID of your registered station on AudioRealm.com
      $sam["host"] = "changes as per dj and blocked out due to security issues
    "; //The IP address of the machine SAM is running on (DO NOT use a local IP address like 127.0.0.1 or 192.x.x.x)
      $sam["port"] = "1221";      //The port SAM handles HTTP requests on. Usually 1221.
      
      
      //General options
      $privaterequests = true;  //If False, AudioRealm.com will handle the requests
      $showtoprequests = true;  //Must we show the top 10 requests on the now playing page?
      $requestdays     = 365;    //Show the top10 requests for the last xx days
    
      $showpic     = true; //Must we show pictures in now playing section?   
      $picture_dir = "pictures/"; //Directory where all your album pictures are stored
      $picture_na  = $picture_dir."na.gif"; //Use this picture if the song has no picture
      
      //Row colors used
      $darkrow  = "#171717";
      $lightrow = "#1a1a1a";   
     
    /* ## ======================================== ## */
      
     $metabasepath = "$commonpath/metabase";
     require("$metabasepath/metabase_interface.php");
     require("$metabasepath/metabase_database.php");
    
     require_once("common/form.php");
     require_once("common/db.php");
     require_once("common/functions.php");
     
     // Load EGPCS vars into globals (emulates register_globals = On in php.ini)
    if (!empty($HTTP_ENV_VARS)) while(list($name, $value) = each($HTTP_ENV_VARS)) $$name = $value;
    if (!empty($HTTP_GET_VARS)) while(list($name, $value) = each($HTTP_GET_VARS)) $$name = $value;
    if (!empty($HTTP_POST_VARS)) while(list($name, $value) = each($HTTP_POST_VARS)) $$name = $value;
    if (!empty($HTTP_COOKIE_VARS)) while(list($name, $value) = each($HTTP_COOKIE_VARS)) $$name = $value;
    if (!empty($HTTP_SERVER_VARS)) while(list($name, $value) = each($HTTP_SERVER_VARS)) $$name = $value;
       
    
     $db = new DBTable();
     
     //Your REMOTE MySQL database login details
     //IMPORTANT: This is the database login details for the database located on the WEBSERVER.
     $db->ReadXMLConfig("dbconfig.xml.php");
     
     //Your LOCAL MySQL database login details
     //This is the login details the webserver will use to contact the local database on the SAM Broadcaster server.
     //This is only used for making dedications from the request window.
     //$db->ReadXMLConfig("samdb.xml.php");
     $samlogin = $db->login; //In most cases the remote database will be the same as the local database.
      
     //Finally connect to the database
     $db->connect();
    ?>

  • #8
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by timgolding View Post
    It might be that your selecting genres before the playlist has had a chance to render. E.g assingning new values to $db->row to early?
    Your right...

    If i put the drop down menu box BELOW the playlist only then the playlist still displays.

    However people are not going to go to the bottom of a playlist to find a search option...

    is there any work around for this?

  • #9
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    I think its because you are calling
    PHP Code:
     <? require("header2.php"); ?>
    <? 
    require("search2.php"); ?>
    After the songlist query has been initialized but before the results are displayed. This is the code where the songlist query is set up.
    PHP Code:
    //Calculate total
     
    $tmp $db->params//Save params for second query
     
    $db->open("SELECT count(*) as cnt FROM songlist $where ");
     
    $row $db->row();
     
    $cnt $row["cnt"];
     
     
    //Now grab a section of that
     
    $db->params $tmp//Restore params
     
    $db->open("SELECT * FROM songlist $where ORDER BY artist ASC, title ASC, genre 
    Then before it is executed and the results processed display you run search2.php and change the query for your search.

    PHP Code:
    require("search2.php"
    So maybe just move
    PHP Code:
     <? require("header2.php"); ?>
    <? 
    require("search2.php"); ?>
    to just below

    PHP Code:
    require("config.php"); 
    Or move the other code down.

    Another alternative is just making another instance of DBTable();

    PHP Code:
    <?PHP
    $db2 
    = new DBTable(); 
    $db2->ReadXMLConfig("dbconfig.xml.php");
    $db2->login
    $db2->connect();

    echo 
    "<select name='genre'><option>Select Genre</option>";
    $db2->open("SELECT DISTINCT genre FROM songlist WHERE songtype='S' order by genre");
    while(
    $results=$db2->row()) {
      echo 
    "<option value='$results[genre]'>$results[genre]</option>";

    echo 
    "</select>";
    ?>
    That should also work i think?
    You can not say you know how to do something, until you can teach it to someone else.

  • Users who have thanked timgolding for this post:

    Slave (03-26-2009)

  • #10
    New Coder
    Join Date
    Mar 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    excellent, it does and i edited the rest of the script to get along with it to...

    thank you so much!!!


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •