Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

PHP MySQL Query Help

Options
  • 24-01-2011 4:00pm
    #1
    bob2oo7
    Registered Users Posts: 378 ✭✭


    Hi guys,

    I need some advice please

    I want my php website to run a query that will take all fields in a specific mysql table, and display them in a HTML table on my website

    I have the query that will take ALL fields and display them, however some of these fields are "optional" and may be blank, if they are, I dont want to display them

    any advise on how to approach this?

    Thanks in advance


Comments

  • Options
    #2
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    You'll need to post some code first. Also have a read of this from the charter.


  • Options
    #3
    bob2oo7
    Registered Users Posts: 378 ✭✭bob2oo7


    Evil Phil wrote: »
    You'll need to post some code first. Also have a read of this from the charter.

    Thanks Phil

    This code uses a loop and displays all the fields in my table

    I want to gather only fields that have data in them, and display them in a HTML table that I will then format to display the releveant data. I hope this makes sense. I have optional fields that a user can fill out (employmenthistory1,2,3,4 etc), and if they chose not to fill it out, I want my query/loop to ignore it


    <?php
    include("db_connect.inc.php");
    $global_dbh = mysql_connect("****", "****", "****")
    or die("Could not connect to database");
    mysql_select_db("colum-test")
    or die("Could not select database");
    function display_db_query($query_string, $connection, $header_bool, $table_params) {
    // perform the database query
    $result_id = mysql_query($query_string, $connection)
    or die("display_db_query:" . mysql_error());
    // find out the number of columns in result
    $column_count = mysql_num_fields($result_id)
    or die("display_db_query:" . mysql_error());
    // Here the table attributes from the $table_params variable are added
    print("<TABLE $table_params >\n");
    // optionally print a bold header at top of table
    if($header_bool) {
    print("<TR bgcolor='red'>");
    for($column_num = 0; $column_num < $column_count; $column_num++) {
    $field_name = mysql_field_name($result_id, $column_num);

    }
    print("</TR>\n");
    }
    // print the body of the table
    while($row = mysql_fetch_row($result_id)) {
    print("<TR ALIGN=LEFT VALIGN=TOP>");
    for($column_num = 0; $column_num < $column_count; $column_num++) {
    print("<TD class ='profile-table-class2'>$row[$column_num]</TD>\n");
    }
    print("</TR>\n");
    }
    print("</TABLE>\n");
    }

    function display_db_table($tablename, $connection, $header_bool, $table_params) {
    $query_string = "SELECT username,firstname,lastname,currentstatus,employmenthistory1,employmenthistory2 FROM $tablename where username ='testuser'";
    display_db_query($query_string, $connection,
    $header_bool, $table_params);
    }
    ?>
    <HTML><HEAD><TITLE>Displaying a MySQL table</TITLE>
    <LINK href="css/banner.css" rel="stylesheet" type="text/css">

    </HEAD>
    <BODY>
    <TABLE><TR><TD>
    <?php
    //In this example the table name to be displayed is static, but it could be taken from a form
    $table = "login";

    display_db_table($table, $global_dbh,
    TRUE, "border='2'");
    ?>
    </TD></TR></TABLE></BODY></HTML>


  • Options
    #4
    bob2oo7
    Registered Users Posts: 378 ✭✭bob2oo7


    This is actually code that I got off the internet, I dont understand it fully but if there is an easier way then that would be great :)


  • Options
    #5
    Dale Parish
    Registered Users Posts: 894 ✭✭✭Dale Parish


    Without reading any of that I personally think it would be better if you stored their previous employment details in one column and explode() the column data and echo that; since I only know the basics of SQL I don't be able to help you filter out empty columns


  • Options
    #6
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    Dale Parish wrote: »
    Without reading any of that I personally think it would be better if you stored their previous employment details in one column and explode() the column data and echo that;
    :eek:
    There's a whole database there, why not use it? What you're effectively proposing is storing an entire row's data within a single column. Why not just store it in a row? Then you don't have to explode it or implode it and it can be properly validated and data typed for the database to store and organise it most efficiently.

    Bob, your problem here is that although the fields are optional, some people will have them filled out, but others won't. So you will have to have the column in the table anyway to accomodate those people for whom information exists in that column. Where the value in that column for a particular row is NULL, this will output as a blank in PHP, so you don't need to specifically check if it's blank. Just output it anyway.

    If you're only displaying information from a single row, then you can simply check if the row is empty when you're generating the table's headers.


  • Advertisement
  • Options
    #7
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    To illustrate, look at the below data from this fictional database.

    Name|Emp.ID|Optional Data|Required Data|Optional Data|Required Data
    Joe Bloggs|12345|(NULL)|DATA|Something|DATA
    Jane Doe|95621|Some Data|DATA|(NULL)|DATA

    If you were to output this to HTML and "filter out" the empty cells from individual rows, your HTML table will look like this:
    Name|Emp.ID|Optional Data|Required Data|Optional Data|Required Data
    Joe Bloggs|12345|DATA|Something|DATA|
    Jane Doe|95621|Some Data|DATA|DATA|


  • Options
    #8
    bob2oo7
    Registered Users Posts: 378 ✭✭bob2oo7


    seamus wrote: »
    To illustrate, look at the below data from this fictional database.

    Name|Emp.ID|Optional Data|Required Data|Optional Data|Required Data
    Joe Bloggs|12345|(NULL)|DATA|Something|DATA
    Jane Doe|95621|Some Data|DATA|(NULL)|DATA
    If you were to output this to HTML and "filter out" the empty cells from individual rows, your HTML table will look like this:
    Name|Emp.ID|Optional Data|Required Data|Optional Data|Required Data
    Joe Bloggs|12345|DATA|Something|DATA|
    Jane Doe|95621|Some Data|DATA|DATA|

    A big problem i can forsee is that I want the information to display and look just a good if someone filles out employmentstatus1 only or if they fill out emplploymentstatus1,2,3,4,.... etc

    It needs to be a dynamic HTML table


  • Options
    #9
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    bob2oo7 wrote: »
    A big problem i can forsee is that I want the information to display and look just a good if someone filles out employmentstatus1 only or if they fill out emplploymentstatus1,2,3,4,.... etc

    It needs to be a dynamic HTML table
    That's definitely possible...but only if you're displaying information for one person. If you have multiple people it's not a possibility.


Advertisement