WEB PHP & MySQL help

Discussion in 'OT Technology' started by dissonance, Jan 13, 2009.

  1. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    So I have a site that displays the movies I have. Right now I keep it all organized in HTML tables (HTML and very minimal CSS is all I know. I have been looking at w3schools' PHP and MySQL guides the past few days though). I would like to create a MySQL database to store all of the information and retrive it via PHP. I have created the database no problem but am having issues with how to retrive the data in a usable manner.

    My MySQL table, titled 'Movies', is organized as follows:
    P_Id ... Title ... IMG_SRC ... IMDB_URL ... Sort ... Origin ... AlphaCat
    1 ....... 300 ..... 300.jpg ... tt0416449 .. 300 .... Original . #
    2 ....... Blow .... blow.jpg .. tt0221027 .. blow ... Download . B
    etc, etc

    The P_Id column will be used to create the home page that displays my last 16 movies acquired. I'm not worried about that page right now.

    So.... I want to be able to search the MySQL table and create a separate HTML table for each letter of the alphabet, to be presented on their own pages. My table rows are like this:

    Code:
    <TD Width="205" Align="Center"><A Target="_IMDB" HREF="http://www.imdb.com/title/tt0221027/"><IMG SRC="blow.jpg" Border="0" Alt="Blow"></A></TD>


    I want the table to be as many rows as it needs but only 4 columns wide. I want it to be sorted based on the Sort column in the MySQL table. Then I want the following:
    - Title column info to be placed in the Alt="" tag
    - IMG_SRC info to be placed in the IMG SRC="" tag
    - IMDB_URL info to be placed in the HREF="" tag

    So I figured out how to select all the items where AlphaCat='#' and sort the data but am pretty much lost as to how to get the info into the table format I'm wanting. Anyone bored and want to help me with this?

    Example of how I am selecting the items:
    Code:
    SELECT Title,IMG_SRC,IMDB_URL,Sort FROM Movies WHERE AlphaCat='A' ORDER BY Sort ASC
    [BTW: This isn't for a business/method for me making money. I just have ~1k movies and use this site for friends to see what I have if they want to borrow something]
     
    Last edited: Jan 13, 2009
  2. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    loop through the dataset with something like this

    Code:
    $sql = "SELECT Title,IMG_SRC,IMDB_URL,Sort FROM Movies WHERE AlphaCat='A' ORDER BY Sort ASC";
    $result = mysql_query($sql);
    
    echo '<table>';
    while($row = mysql_fetch_assoc($result)){
       echo '<tr>';
       echo '<td>'.$row['Title'].'</td>';
       echo '<td><img src="'.$row['IMG_SRC'].'" /></td>';
       
       ...  etc
    
       echo '</tr>';
    }
    echo '</table>';
    
    you don't need to use the alphacat either. if you want to return all movies that start with A or whatever you can do WHERE Title LIKE 'a%'.
     
  3. whup

    whup I wish you had children and.. so that I could step

    Joined:
    Feb 12, 2007
    Messages:
    1,603
    Likes Received:
    0
    If you want to break it up by letter, you can do something like this:

    $lastCat = ''; // Remember the previous item's cat

    foreach( $rows as $row )
    {
    if( $lastCat != $row['AlphaCat'] )
    {
    // We've got to the next letter, so print out the header, something like <a name="A"></a><h2>A</h2>
    }
    $lastCat = $row['AlphaCat'];
    }

    You could have some quick-links to jump to a letter at the top of the page:

    $letters = range('A', 'Z');

    foreach($letters as $letter)
    {
    // Print out the link e.g. <a href="#A">A</a>
    }

    You using "#" for numbers could have problems as it's also the anchor tag, so you might want to choose something else for that.

    Do you really need to manually have an AlphaCat too? Can't you just grab the first letter/number of the title?
     
  4. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    Thanks for the help, I'll try it out this weekend hopefully..

    I can change the '#'. But I guess the AlphaCat isn't necessary. I suppose grabbing the first letter/number would work better
     

Share This Page