Grow your CSS skills. Land your dream job.

SQL Table to HTML Table

  • # October 21, 2012 at 2:05 am

    Oh yay for being able to make a super mini database system from scratch.

    Here’s the lowdown: You are able to submit scripts(like play scripts) through a form to a MySQL database. After being approved by an administrator, they show up in a table on the viewing page, and you can click on the script you wish to read in a viewscript.php?id=32 form.

    It is modeled after [my friend's website](http://www.everythingdojo.com/themedb.php “Theme DB”), specifically the . This is a site in which he stores CSS codes for blogs [here](http://www.artofproblemsolving.com/Forum/apsjour.php “AoPS Blogs”). (Uh yeah you have probably figured out by now that I’m in middle school i.e. not an adult like yall but I love PHP :D)

    Anyway you cannot see the approving thing (not being a registered user and not being an styles moderator) and you don’t need to understand the purpose of the sites but if you can look around the first site linked to, specifically the View section, so you get a feel for where I’m stuck at.

    Now for my problem: If you can see how the links in the view mode have a get variable attached to it something like “page.php?id=12″.
    So I have a table holding the scripts with columns id, title, author, script, approved.

    < ?php
    $tablesql = “SELECT title, author FROM accscriptdb WHERE approved=0″;
    $result1 = mysql_query($tablesql);
    $idsql = “SELECT id FROM accscriptdb WHERE approved=0″;
    $result2 = mysql_query($idsql);
    if (!$result1 || !$result2) {
    exit(“QUERY ERROR: ” . mysql_error());
    } else {
    $tableData = ”;
    while($row = mysql_fetch_assoc($result1)) {
    $currRow = ”;
    foreach($row as $key => $value) {
    $currRow .= “ $value

    “;
    }
    $tableData .= “
    $currRow

    n”;
    }
    echo(“

    //oh gosh it’s filtering out the HTML, it’s just a valid table, this is not the problem

    $tableData

    Script Name Author

    “);
    } ?>

    Right now, my second query is not being used, but I am spitting out a table with the title and author now. That works fine…now I am trying to add a link (the get variable kind) to each one in the foreach loop. I am thinking of using an array but I am not very good at fetching selected data in the sql table and using it effectively. I’m quite fuzzy on how I might do this, help?

    Thanks for reading [my quite lengthy] question,
    Red

    # October 21, 2012 at 2:25 pm

    I’m afraid I don’t understand your question.

    However, some things I notice:

    a) you should gracefully recover when there’s an error instead of using `exit`. If you `exit` in the middle of a script, the user is left with a broken page: give an error message instead (e.g., “Sorry, we broke somethign, please try again!”).

    b) why two queries? Why not `SELECT id,title,author FROM accscriptdb WHERE approved=0`?

    c) are you intentionally selecting `approved=0` (logically, that would seem to imply that the item is *not* approved)?

    d) are you intentionally “dumping” the results from each row? Or is that what you’re asking – how to use/format the individual result values? example:

    < ?php
    /* ext/mysql is outdated. ext/mysqli or PDO is recommended instead. */
    $DB = new mysqli( ‘DBhost’,’DBuser’,’DBpassword’,’yourDatabaseName’ );
    $sql = “SELECT id,title,author FROM accscriptdb WHERE approved=0″;
    // query DB
    $result = $DB->query( $SQL );
    if( !$result ){ /* error handling here */ }
    else{
    // loop through rows in resultset
    while( $row = $result->fetch_assoc() ){
    // the array $data will hold the markup for each [HTML] table row
    $data[] = < << HTML

    Row ID {$row}

    Row Title {$row}

    Row Author {$row}

    HTML
    ;
    }
    // put the table rows together, inside

    tags
    $tableData = “
    “.implode( “n”,$data ).”

    “;
    // print the HTML table
    print $tableData;
    }

    # October 21, 2012 at 6:53 pm

    Yeah I’m still in the habit of using mysql…

    I am building the approve page so yes I am selecting scripts which have not been approved yet.

    I am using two queries because the id number does not need to show up in the table. I am trying to use it as a separate data source to build “approvescript.php?id=12″ format urls to go around the title column in the table. There are only two columns in the table, title and author. However, I need to build a loop generated link around each title td. It should apply a get variable to the end of the link so I can target the script in the DB to fetch the data I need…

    Do you get it now?

    # October 21, 2012 at 8:08 pm

    If you’re wanting to build a URL for each title column in the table, then it would seem that you *do* need it – so there’s no point in separating it out.

    However, if there *is no `id` column* in the table (in the db), then that is a problem.

    How do you expect to get an `id` if there is no `id` field?

    **EDIT**



    Once you *have* the id, you can use it in a hyperlink like so:

    $data[] = < << HTML

    Row Title


    {$row}

    Row Author

    {$row}

    HTML
    ;

    # October 22, 2012 at 10:02 am

    Yes, I do have an id column in my database. And now I see how I should have utilized fetch_assos. Thanks!

    # October 22, 2012 at 10:58 am

    no problem!

    # October 22, 2012 at 10:33 pm

    oh gosh now I’m having problems with php syntax. nothing I see wrong, but the page isn’t coming back due to a php error. This is always my syntax problem. -_-

    sorry guys I’m basically asking you to help me debug it

    < ?php
    $sql = “SELECT id, title, author FROM accscriptdb WHERE approved=0″;
    $result = mysql_query($sql);

    if (!$result) {
    exit(“QUERY ERROR: ” . mysql_error());
    } else {
    $tableData = ”;
    while($row = mysql_fetch_assoc($result)) {
    $currRow = ”;
    $currRow .= “ $row

    “;
    $currRow .= “ $row

    “;
    $tableData .= “
    $currRow

    n”;
    }
    echo(“table
    tr
    thScript Nameth
    thAuthorth
    tr
    $tableData
    “);

    } ?>

    # October 23, 2012 at 7:36 pm

    Anyone got help for me or am I still just being dumb?

    # October 23, 2012 at 8:23 pm

    What error are you receiving?

    Also, the code block in your post is formatted incorrectly – if you fix it, I can try to help debug it.

    (If you’re using markdown to make your code blocks, make sure that *every line* is indented 4 spaces. If all else fails, delete the < from the HTML tags in your code.)

    # October 25, 2012 at 1:40 pm

    It does not load the page, therefore I cannot read any error. I will fix the code now…

    # October 25, 2012 at 9:02 pm

    The problem you have right now is how you’re putting your variables into your strings:

    $str = “Doing this $myVar won’t work.”;
    $str = “Either concatenate “.$myVar[index'].” with the string, “;
    $str = “Or use curly braces around {$myVar} to make sure it parses right.”

    If you’re not getting an error from this code, that means you don’t have error reporting enabled. During development, **every last error** should be displayed.

    // somewhere near the top
    error_reporting( -1 );
    ini_set( ‘display_errors’,’1′ );

    The other issue you might run into is that, while you check if there was an error, you never check if there were actually any rows returned:

    if( !$result ){ print “There was an error.”; }
    elseif( mysql_num_rows( $result ) === 0 ){
    print “No matching rows were found.”;
    }

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".