www.webdeveloper.com
Results 1 to 15 of 15

Thread: creating table using mysql

  1. #1
    Join Date
    Feb 2017
    Posts
    43

    creating table using mysql

    Hi,
    I'm stuck with the logic. I have two tables. One is artist second songs. Artist table has 8 artist and song table has 14 songs.
    For each artist, you must display their name and the number of songs accredited to them. This part i have done.
    but i'm struggle get the logic for summary.
    A summary of the songs and artists on the system should also be displayed on each view. The summary should inform the user of the total number of songs and the total number of active artists in the system.

    I don't know how to display the sumary in the table. Basically I don't know how to construct the query for all the information to get that in one table.
    this what I have so far but it only display artist with the song accredited to them plus is multiple table not in one table because of the while loop it return only one table at time.
    PHP Code:
    // Construct the query
    $sql "SELECT  a.name, COUNT(s.artist_id) FROM artist a JOIN song s
            ON (s.artist_id = a.id)
            GROUP BY a.name ASC"
    ;

    // Execute the query, assigning the result to $result
    $result mysqli_query($link,$sql);

    // If the query failed, $result will be "false", so we test for this, and exit if it is
    if ($result === false) {
        exit(
    $lang['mysqli_error']);
    }

    // Gather the author HTML for later
    $artists '';

    // Check if the query returned anything
    if (mysqli_num_rows($result) == 0) {
        
    // Pass an error message to the error template which will appear in place of authors
        
    $msg $lang['empty_result_error'];
        
    $artists .= $msg;


    } else {
        
    // Loop through $result, converting each record from the result set to an array which we assign to $row
        
    while ($row mysqli_fetch_assoc($result)) {
            
            
    // We can now access the values in $row using the database column names as array keys
            
            // Echo the escaped string to the page
            
    $pass1 str_replace('[+name+]'htmlentities($row['name']), $tpl_c);
            
    $pass2 str_replace('[+songs+]'htmlentities($row['COUNT(s.artist_id)']), $pass1);
            
    $artists .= $pass2;
        }
    }
    // We are finished with the result set, so no point keeping it in memory
    mysqli_free_result($result);

    // As we are doing no more database querying, we can also close the connection now.
    mysqli_close($link); 

    Hopefully it does make sence.

    thanks.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,335
    Seems like the simplest approach would be a couple counter variables you could increment in the while loop.
    PHP Code:
    $num_artists $num_songs 0;
    while(
    /* ... */) {
      
    $num_artists++;
      
    $num_songs += $row['COUNT(s.artist_id)'];
      
    // rest of the stuff you do in the loop...
    }
    // output or save the numbers in $num_artists and $num_songs as desired 
    _
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  3. #3
    Join Date
    Feb 2017
    Posts
    43
    Ok that could work but for this to construct the query I'm gonna need full join to get all two tables otherwise I don't get all artist or all songs in the database right?

    /////
    /////

  4. #4
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    1,904
    I don't know what you are trying to do but your first post shows some code that makes absolutely no sense. What are you doing with those "str_replace" calls? They aren't exactly the correct syntax and you are looping thru the results without accomplishing anything permanent that I can see. I don't have a clue what you are doing with $artists - sometimes you put an error message(?) in it, other times you save the $pass2 value in it. Those str_replaces are just bizarre. You seem to be missing the replacement value and from my guess are taking the result of the first call and overwriting it with the results of the second call. AND neither one of those str_replace calls seems to do anything.

    From the manual:
    mixed str_replace ( mixed $search , mixed $replace , mixed $subject [, int &$count ] )

    Your syntax looks nothing like this.

    As for your question in the last post - why don't you make the first query a better one?

    Code:
    "SELECT a.id, a.name, a.otherfield, a.anotherfield,...., 
                 s.otherfield, s.anotherfield, COUNT(s.artist_id) as songs FROM artist a 
    		left outer JOIN song s ON (s.artist_id = a.id) 
    		GROUP BY a.id ASC";
    This should (?) give you more of the data that is in the tables so that you can construct a proper html table to display all that you want to see.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  5. #5
    Join Date
    Feb 2017
    Posts
    43
    first post shows some code that makes absolutely no sense. What are you doing with those "str_replace" calls?
    Those str_replace() line of codes are exchanging placeholders for the real content for HTML. I have template that is pure HTML codes with placeholders. Those line of codes replace those placeholders and when I call my HTML template it will show me nice page and that way I have clear php page full of codes and function without any html codes. Anyway I'm not allow to use HTML in my php file.
    It is advance technique for php and mysql module that I'm taking now.



    /////

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,335
    Not knowing what you actually do with $artists later, it's difficult to say whether whatever problem you are having is how $artists is being populated by your DB query results, or whether it's an issue with your template code/format/whatever.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  7. #7
    Join Date
    Feb 2017
    Posts
    43
    ok guys this is the link to the project website http://titan.dcs.bbk.ac.uk/~mzacha02/w1tma/index.php

    if you click on artist you will see the outcome for now. If you remember once I have post the problem with multiple table and then you told me to fetch the function with array and get your table.
    This time I have my template so I can't use the previous function but I can rebuild the function for my template. I would see a problem there. Im strugle to get query right. But I think I will create to tables and that will solve the problem and get into database twice. I wanted to access to database only once and put everything in one table put I don't know how to do it so I think will have to create function where will be one table and then on php file eneter to database again and this time create second table only for summary of songs and artist.

    This is the rest of the php code so now you can see what I'm doing with those $artist.

    Template is just normal table with <tr><th> and <td> tags as you useally will create table.



    /*
    -----------Build our HTML page------------------
    */
    // Set our variable for the placeholders in our header.html
    $page_title = 'Artists page';
    $heading = 'Welcome at the Artists page.';
    $content = 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. In pulvinar ligula vel nisl ultricies venenatis. Integer eu suscipit nibh. Duis interdum nibh auctor ullamcorper vehicula. Fusce tincidunt dapibus fermentum. Nulla aliquam lacinia quam eget posuere. Suspendisse tempor neque et vulputate rutrum. Quisque consequat elementum dui, sit amet pellentesque velit dignissim eget. Pellentesque accumsan lobortis elit sed dapibus. Etiam sed tellus turpis. Pellentesque leo lectus, hendrerit id nibh non, vulputate luctus ex. Nunc dignissim pharetra dui, ac lobortis odio imperdiet sed. Ut id odio a tortor porta consectetur. Morbi scelerisque vehicula felis, nec scelerisque est tincidunt vitae. Aenean dapibus orci erat, a elementum tellus congue quis. In porttitor maximus vestibulum.';

    $pass1 = str_replace('[+page_title+]', $page_title, $tpl_a);
    $pass2 = str_replace('[+header+]', $heading, $pass1);
    $final = str_replace('[+content+]', $content, $pass2);
    //diplays our template file with all placeholders
    $content = $final . $artists . $tpl_b;
    echo $content;




    ///////

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,335
    Well, if I were going to do it, I'd just use something like this:
    PHP Code:
    <?php
    // bunch of code up to executing the query, then...
    ?>
    <table>
    <tr><th>Name of Artist</th><th>Number of Songs</th></tr>
    <?php
    while ($row mysqli_fetch_assoc($result)) {
      
    printf(
        
    "<tr><td>%s</td><td>%d</td></tr>\n",
        
    htmlentities($row['name']),
        
    $row['num_songs']  // or whatever alias you want to use for that count field
      
    );
    }
    ?>
    </table>
    <?php
    // rest of script...
    (I suggest using an alias for that count field, e.g. something like:
    SELECT a.name, COUNT(s.artist_id) as num_songs FROM . . .
    )
    _
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  9. #9
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    1,904
    Yeah - good luck with this. Your latest post only confuses me MORE! $tpl_b??? Where did THAT come from?

    Never mind - I don't want to know.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  10. #10
    Join Date
    Feb 2017
    Posts
    43
    nogDog i would have done probably the same thing but I'm not allow to use HTML in php file. All HTML tags have to be put in the template with placeholder and call the template later and fill up with the content.


    Ginerjm haha don't worry it is too much I know and is complex task. btw. $tpl_b is the template of footer. On top I 'm calling my template of header which is $tpl_a and $tpl_b is footer and $tpl_c is body but it doesn't matter those as those are only templates to calling out at the end to create dynamic web page. I will have to do it with two tables and maybe after that with css align as one table hahaha we will see. I already have the second table where is the summary but I was just trying to get all in one table.

    Btw. I have create view in sql of those two tables and that way I get the summary of all songs and artist but the problem is all is under one column. Do you guys have any idea why is that? and why is not in two column.

    This is the sql summary table
    Code:
    create view summary  as
    SELECT count(a.id) as 'number of artist'
    from artist a 
    LEFT JOIN song s ON a.id = s.id
    union
    select  count(s.id) as 'number of song'
    from song s
    LEFT JOIN artist a ON  s.id = a.id

    but like I said above it give me the data all under one column and not two as I'm declaring above in the code. One column should be 'number of artist' and the second column should be 'number of song'




    /////

  11. #11
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    1,904
    Why don't you use the sample query I gave you?
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  12. #12
    Join Date
    Feb 2017
    Posts
    43
    i will try later this week .. right now i don't have time to play with it as I have to get ready for information system management test on Wednesday.

    that query above is just another code what i have said that maybe i will have to do two access into the database to get all information. what i need is if you scroll on top and read the post you will understand what the college want from me to do.

    basically, create artist page where the only artist with song accredited to them can be displayed and then also all summary of the artist in the database and summary of songs in the database to display.
    if you look at the link i have the artist and the songs just need to add the summary there. it is so complex or is it just for me i don't know. I though someone here can help me with the logic to build that as i have run out of the logic with this.




    ////

  13. #13
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    1,904
    If you order the query results correctly you can just do counts of things as you loop thru and display the results, placing the summary where it needs to be.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  14. #14
    Join Date
    Feb 2017
    Posts
    43

    resolved solved

    Just to let you all know that I figure out how to merge everything into one table as I wanted to.

    Here is the link to the final outcome of the website.
    http://titan.dcs.bbk.ac.uk/~mzacha02...p?page=artists

    ginerjm that code for SQL couldn't work because when I tried to count the summary of artists it couldn't give me the sum of artists but it was giving me sum of the songs that artists were accredited to them. The reason is FK in songs table is artists_id. Therefore I had to access to database again and create view only for all artists in the table union with all songs in the table.
    At the bottom of the website, you can see the number of how many artists are in the artists table and how many songs in the songs table.

    Hopefully that makes sense. Thanks anyway for the help.
    Btw. to the table I had to just create template with start of the table and template for end of the table. I fetch all the data only with body of the table template. The outcome is as you can see on the website.


    This can be thread can be closed.


    ///

  15. #15
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    5,872
    Quote Originally Posted by ginerjm View Post
    I don't know what you are trying to do but your first post shows some code that makes absolutely no sense.
    if you read the OP's post For each artist, you must display their name and the number of songs accredited to them. This part i have done.
    but i'm struggle get the logic for summary.
    then it becomes apparent this is course work of some sort.
    --> JavaScript Frameworks like JQuery, Angular, Node <--
    ... and please remember to wrap code with forum BBCode tags:-

    [CODE]...[/CODE] [HTML]...[/HTML] [PHP]...[/PHP]

    If you can't think outside the box, you will be trapped forever with no escape...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

"

"

X vBulletin 4.2.2 Debug Information

  • Page Generation 0.15661 seconds
  • Memory Usage 3,046KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_code
  • (3)bbcode_php
  • (3)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (15)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (15)postbit
  • (15)postbit_onlinestatus
  • (15)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (72):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates