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 14 of 14
  1. #1
    New Coder
    Join Date
    Jun 2006
    Posts
    21
    Thanks
    2
    Thanked 0 Times in 0 Posts

    PHP and fetching from a relational database

    I'm still a beginner at PHP and having worked all day so far on other things my brain has kind of hit capacity, so apologies if this is easy.

    Anyway, my table is set up so I have

    library
    filename
    artist name
    track name
    catid

    then I have a category table
    id
    category

    My catid input is set to use the different fields that are in category input.


    But how do I display the category name when I specify the library catid?

    My code currently is as follows

    PHP Code:
    // Get all the data from the "library" table
    $result mysql_query("SELECT * FROM library"
    or die(
    mysql_error());  

    // see if any rows were returned
    if (mysql_num_rows($result) > 0) {
        
    // yes
        // print them one after another
        
    echo "<table>";
        while(
    $row mysql_fetch_row($result)) {
            echo 
    "<tr>";
            echo 
    "<td>".$row[1]."</td>";
            echo 
    "<td>".$row[2]."</td>";
            echo 
    "<td>".$row[3]."</td>";
            echo 
    "<td>".$row[4]."</td>";
            echo 
    "</tr>";
        }
        echo 
    "</table>";
    }
    else {
        
    // no
        // print status message
        
    echo "No rows found!";

    row4 is the catid in the library database. How do I get this bit to show the category name?

    Thanks in advance,
    Ed
    Last edited by blueuniverse; 07-21-2008 at 05:05 PM.

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    start counting from 0. $row[0] is library.

    regards

  • #3
    New Coder
    Join Date
    Jun 2006
    Posts
    21
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Apologies, explained my table structure a bit quickly there.

    library (table name)
    id
    filename
    artist name
    track name
    catid


    The catid specifies a non unique number that I then need to link to the category table, match the id of that and then return the category name.

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by blueuniverse View Post
    Apologies, explained my table structure a bit quickly there.

    library (table name)
    id
    filename
    artist name
    track name
    catid


    The catid specifies a non unique number that I then need to link to the category table, match the id of that and then return the category name.
    PHP Code:
    $query "select l.id, l.filename, l.artist_name, l.track_name, l.catid, c.category from library as l, category as c where l.catid = c.id";
    $result mysql_query($query);
    if(
    $result){
      while(
    $row mysql_fetch_assoc($result)){
         foreach(
    $row as $field => $value){
            print 
    '<td>'.$value.'</td>';
         }
      }

    PS: make the code to print table and tr and if you want you can use field name from $field.

    regards

  • Users who have thanked oesxyl for this post:

    blueuniverse (07-22-2008)

  • #5
    New Coder
    Join Date
    Jun 2006
    Posts
    21
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Apologies but I can't get it to format in a similar manner nor can I get it to selectively display fields.

    My bad if this is again easy but I'd only really just got my head around the existing code, I've played about for a while using similar methods but I can't get it to print out in a similar manner.

    Thanks in advance for your help.

  • #6
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by blueuniverse View Post
    Apologies but I can't get it to format in a similar manner nor can I get it to selectively display fields.

    My bad if this is again easy but I'd only really just got my head around the existing code, I've played about for a while using similar methods but I can't get it to print out in a similar manner.

    Thanks in advance for your help.
    not clear for me what is the problem but:
    - you must use the names of the fields from your database. names like 'artist name' or 'track name' must be quoted in mysql or as I do replace the spaces with '_'.
    - $row is a hash array, so you can see the keys and the values using print_r or echoing the $field and $value variable from the foreach. you can also address some field for example like:
    PHP Code:
    echo $row['catid']; 
    I hope I don't missunderstand the problem but in case I do, post what I have unclear and I try to help.

    Edit: put this in foreach loop:
    PHP Code:
    echo $field.": ".$value."<br>"
    and see ehat happend


    regards
    Last edited by oesxyl; 07-22-2008 at 01:36 PM.

  • #7
    New Coder
    Join Date
    Jun 2006
    Posts
    21
    Thanks
    2
    Thanked 0 Times in 0 Posts
    This is displaying as follows

    0: id
    1: trackname
    2: artist
    3: file_name
    4: catid
    5: category
    Whereas before it displayed as follows

    Track Name Artist Filename Catid
    As far as formatting goes I want it to display exactly as before in a row just with the catid replaced with the category name.

    i.e. I need a way to place the data in a row and to remove fields, I don't for example want the id or the catid to display.

    Thanks,
    Ed

  • #8
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by blueuniverse View Post
    This is displaying as follows
    Whereas before it displayed as follows

    As far as formatting goes I want it to display exactly as before in a row just with the catid replaced with the category name.

    i.e. I need a way to place the data in a row and to remove fields, I don't for example want the id or the catid to display.

    Thanks,
    Ed
    please post the code you have now.
    I don't know what you use to show the data this way, so we start from your code and modify until work how you want.

    regards

  • #9
    Regular Coder
    Join Date
    Apr 2006
    Posts
    186
    Thanks
    21
    Thanked 6 Times in 6 Posts
    I think what he wants is like this. I think this should work:
    PHP Code:
        while($row mysql_fetch_assoc($result)) {
            echo 
    "<tr>";
            echo 
    "<td>".$row['trackname']."</td>";
            echo 
    "<td>".$row['artist']."</td>";
            echo 
    "<td>".$row['file_name']."</td>";
            echo 
    "<td>".$row['category']."</td>";
            echo 
    "</tr>";
        } 
    Or instead of selecting everything in your SQL, you can just select those 4 columns and then print out those 4 results with foreach.

  • Users who have thanked PeaTearGriffin for this post:

    blueuniverse (07-23-2008)

  • #10
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by PeaTearGriffin View Post
    I think what he wants is like this. I think this should work:
    PHP Code:
        while($row mysql_fetch_assoc($result)) {
            echo 
    "<tr>";
            echo 
    "<td>".$row['trackname']."</td>";
            echo 
    "<td>".$row['artist']."</td>";
            echo 
    "<td>".$row['file_name']."</td>";
            echo 
    "<td>".$row['category']."</td>";
            echo 
    "</tr>";
        } 
    Or instead of selecting everything in your SQL, you can just select those 4 columns and then print out those 4 results with foreach.
    that's why I ask for actual code. 'category' is in other table then 'file_name' therefor must use a where to join the tables and probably op don't use mysql_fetch_assoc, I susspect it use mysql_fetch_row or mysql_fetch_array. Are too many assumption,

    regards

  • #11
    Regular Coder
    Join Date
    Apr 2006
    Posts
    186
    Thanks
    21
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by oesxyl View Post
    that's why I ask for actual code. 'category' is in other table then 'file_name' therefor must use a where to join the tables and probably op don't use mysql_fetch_assoc, I susspect it use mysql_fetch_row or mysql_fetch_array. Are too many assumption,

    regards
    Oh didn't see that as he stated 6 rows were displayed... Maybe change the select statement to this?
    Code:
    SELECT trackname, artist, file_name, category
    FROM library l INNER JOIN category c
    ON l.catid = c.id

  • Users who have thanked PeaTearGriffin for this post:

    oesxyl (07-22-2008)

  • #12
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by PeaTearGriffin View Post
    Oh didn't see that as he stated 6 rows were displayed... Maybe change the select statement to this?
    Code:
    SELECT trackname, artist, file_name, category
    FROM library l INNER JOIN category c
    ON l.catid = c.id
    the result from #7 use the echo posted in #6 and the query from
    #4,

    Edit: your query could get rid of l. and c. from first part of the select, I notice only now,
    is a good idea but I never like join( without reason),

    regards
    Last edited by oesxyl; 07-22-2008 at 06:19 PM.

  • #13
    Regular Coder
    Join Date
    Jul 2008
    Location
    Blackpool, UK
    Posts
    176
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi


    I too am in a similar situation to blueuniverse, I have a database with two tables one with a list of categories that link to a select dropdown in a form, this table called categories and the other table has information in like name, etc etc called portfolio.

    What blue universe is trying to achieve from what I can gather is listing everything in library table by a category in the category table. and then displaying the results.


    Now at work I have a piece of code that if you have the same issue as me, then it will work how you want it to.


    The thing i have found with PHP forums is not many people read the initial problem and or the poster does go into enough depth.

    Therefore it takes you twice as long to sort the problem out.


    I have been learning PHP for two weeks and have not really had any code from forums that has actually helped me.


    Once become fluent in php I will become Mother Teresa of the PHP world, and will actually help somebody solve a problem.


    Ifind that by having the code in front of you complete, then you learn quicker than tying to solve it yourself.



    Let me know if you dont sort this problem out and i will post the code tomorrow.


    Danny

  • #14
    New Coder
    Join Date
    Jun 2006
    Posts
    21
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by PeaTearGriffin View Post
    I think what he wants is like this. I think this should work:
    PHP Code:
        while($row mysql_fetch_assoc($result)) {
            echo 
    "<tr>";
            echo 
    "<td>".$row['trackname']."</td>";
            echo 
    "<td>".$row['artist']."</td>";
            echo 
    "<td>".$row['file_name']."</td>";
            echo 
    "<td>".$row['category']."</td>";
            echo 
    "</tr>";
        } 
    Or instead of selecting everything in your SQL, you can just select those 4 columns and then print out those 4 results with foreach.
    This did the trick, thanks for all your help - made huge progress because of it.


  •  

    Posting Permissions

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