SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 26
  1. #1
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best Way to Store Various Data (many pages) to mySQL?

    I am not a programmer or a web programmer. This task is #4 or 5 on my priority list but must be completed regardless. I say this because I'm sure my noob status will be quite a apparant. I don't have the knowledge to write code off the cuff. I've gotten pretty far with basic google searches but I think I'm treading into the more advanced area so I feel I need some tailored help.

    Priority #1: Get this working regardless of security (it will be on a LAN so not worried about SQL injection)
    Priority #2: Will see if mysqli works in this environment ...RedHat with mysql v5.0.6 (I think) and Firefox for web browser
    Priority #3: Learn how to stop SQL injection (just so I know how to be a better web programmer)

    Priorities #2 and 3 can and will be pushed till I have more time to optimize and learn. If this was the only thing I'm working on it might be different, BUT 2 and 3 are most definitely things I really want to implement.


    Project
    I'm creating a meeting organizer on the (LAN) web that not only has checklists for the various meetings but stores status for various items of interest. So for example, someone running the meeting would use a checklist and then navigate to various pages and update the status of these various items. Not all items will need to be updated, so only on as needed basis.

    In addition, I would like to save this session to the DB so if someone wanted to see the history of the status of these items they can do a date query and retrieve what the meeting organizer updated.


    For lack of better ingenuity I am using div boxes that the user clicks on to change the status of an item (so 1st click => box goes Green, 2nd click => box goes Yellow, 3rd click => box goes red => 4th goes back to default) using JavaScript. For now I am having the script create a cookie so the page remembers the last status. Obviously this is very ugly because 1) it only works for the user logged in, and 2) it creates an insane load of cookies due to the various items across multiple pages.

    It also presents the problem of how am I going to save this status to the database in elegant manner without hardcoding every box (way ugly). I would like to keep the div box design if nothing else it is what everyone is used to now ...and they like the visual presentation over straight text. I can get the database to save a cookie (haven't tried multiple cookies yet) so I got that part at least. (small victories, right? )

    I'm also using a div box as a notepad so notes can be entered on the various items. It uses domstorage (which I know nothing about! - seems to be a cross between sessions and cookies) - Example I used. I customized it to make it easier for free flowing text (i.e. stripped the list tags), but I have zero clue how it works, how I will make the webpage clear it after Submit, and if I can save the data to mysql like I been doing with php and cookies. I know using HTML forms would be easier but this is what I implemented before I got the database up. It does look pretty slick so I would like to keep it if I can make it work.


    Solution?
    I know this is a long winded 1st post but I wanted to explain what I'm working on so if anyone is kind enough to reply they can visualize what I'm trying to do. I'm sure this an easy project for many here but this is beyond my expertise or knowledge level. Prior to this I only knew HTML, simple JS and CSS. I have no formal training and I only have limited internet.

    I doubt it's worth going forward on the cookie design but if I stuck with it, I need some coding help on how to parse select cookies from a vast amount of cookies and only update the database with the ones I want to.

    What is the better way to store and save information to the (mysql) database where the user will be going back and forward across multiple pages? Function code would really help the uninformed. I know I'm not providing any code myself but that is because it is on a different network and I would have to hand write it on paper and the re-type it here, but if there are snippets that would help I will provide it if asked.

    Please help an idiot - so so tired of browsing Stack Overflow. I have read many (not all) stickies here and articles but not finding what I need. Even if this seems like really simple stuff to you, it's not for me. Pretend I'm a 2nd grader who needs to be schooled, I can take it.

  2. #2
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm thinking of maybe making each div box the user clicks on a hidden form field ...if that works. Maybe having the onClick JavaScript function insert PHP code that echos the hidden form so I can keep it dynamic and insert the proper value to choose from. Though I'm not sure if the PHP code will execute on page load or lay dormant in the JS function until it is inserted via onClick. If it would work then maybe having a function something like this...

    Code:
    foreach($_POST as $name => $value)
     {
       $sql = "UPDATE table_name SET $name=mysql_escape_string($value) WHERE condition";
       mysql_query($sql,$con);
       unset($sql);
     }

    I really don't think that will work but maybe this will help spark some conversation. My brain is having a hard time figuring out how I can associate the Table Column name that needs to be updated with the proper value. So if there are 10 div boxes on the page and the user clicks box #7 two times so it goes to Yellow than I want to update the appropriate field. Or maybe the user will update all 10 boxes and write some notes.

    Any ideas?

  3. #3
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bump!!

    Not sure why I haven't gotten any replies yet, but I could really use some help on this. If it helps here is the current code of the page I am working on and what is holding me back.

    This page is one of the page's where the user updates the status of many items (represented as div boxes). Below is the onClick JavaScript function that I use to change the color of div box.


    JAVASCRIPT ONCLICK FUNCTION

    Code:
    function statusChange(objid)
     {
        var x=document.getElementById ('shoe'+objid);
        if (x.className.match ("default"))
          {
             x.className = "nom";
             x.innerHTML = 'Nominal <input type=hidden" name="shoe_name'+objid+'" value="shoe'+objid+'" /><input type="hidden" name="shoe_val'+objid+'" value="1" />';
             createCookie('shoe'+objid,'nom',1);
          } // end_if
        else if  (x.className.match ("nom"))
          {
             x.className = "marg";
             x.innerHTML = 'Marginal <input type=hidden" name="shoe_name'+objid+'" value="shoe'+objid+'" /><input type="hidden" name="shoe_val'+objid+'" value="2" />';
             createCookie('shoe'+objid,'marg',2);
          } // end_else_if
    
    // ETC...
    // Function works, just showing it for reference ...There are 4 classes (nom, marg, prob and default).
    DATABASE QUERY

    PHP Code:
    <?php 

        
    if (isset($_POST['update']))
          {
             
    $shoe $_POST['shoe_status'];     // shoe_status retrieved from radio buttons (below)
             
    $shoen $_POST['shoe_name01'];    // shoe_name retrieved from JS function hidden form, hard coding until I can figure all this out (NEED LOOP BAD)
             
    $shoev $_POST['shoe_value01'];   // shoe_value retrieved from JS as well.   Really need some HELP writing a good loop function for all the boxes
       // Need help on loop so shoe_name and value can go from shoe_name01 to shoe_nameXX

             
    $sql1"UPDATE status SET shoe=$shoe WHERE sid=1";
             
    $sql2"UPDATE shoe_status SET $shoen=$shoev WHERE side=1";

    // ************************************
    // Trouble shooting Code
    // ************************************

             
    echo 'shoebox is ' $_POST['shoe_name01'];     // making sure I'm receiving the variable from $_POST
             
    echo '<br>shoebox value is ' $mocv '<br>';  // same thing since I've had problems with it not getting the variable from form submit

    // END TROUBLE SHOOTING

    // $db = is referenced from my database connect file, which is at the top of my page along with session start

             
    $retval1 mysql_query($sql$db);   // ignore its not mysqli for now.   For some reason I can't stack queries, any idea why?
             
    $retval2 mysql_query($sql2$db);  // seems inefficient to need separate queries for each update.   Really need a good loop if there is no way around this

             
    if (! $retval1 && ! $retval2)
               {
                   die(
    'Not able to update data: ' mysql_error());
                   
    // If I get this it nukes my entire page, anyway around that?   I even tried wrapping this PHP code block in a div
               
    }
               echo 
    "Updated status successfully </br></br>";
               
    mysql_close($db);
        } 
    //END_IF
    ?>
    If the above php is not above my form than nothing works ...which is driving me crazy because if I get that stupid error message it nukes the rest of my page. Not sure why I can't stack multiple queries in one such as ($sql1, sql2, $db) in retval but nothing updates if I do it this way. At least with the code above I am updating the database.

    The problem is I need a way to streamline this for ALL the boxes. I think I need a loop, such as my pathetic attempt in post #2, but I'm not very good with loops and could really use some help there. I think if I can get the loop going it will solve most of my issues even if I don't get a response on how to make this more elegant.

    Then once I get it working I can start working on making this mysqli compliant (do I just add an 'i' to everything or is it more complicated than that?) and work on preventing sql injections.


    Function Call and example div Box that stores the status of the various items

    The onClick function dynamically inserts hidden form fields into these boxes. Very crude but I couldn't figure out a better way to do this. Asked for help and so far no replies.

    HTML Code:
    <form method="POST" name="shoe_box" action="<?php $_PHP_SELF ?>">
    <div class="container"> 
    <div class="statusBorder"> Shoe 1
    <div <?php if ($_COOKIE['shoe01'])) { echo 'class='.$_COOKIE['shoe01']; } else { echo 'class=default'; } ?> id="shoe01" onClick="statusChange('01')"> <font size="1">Click to Change</font></div>
    </div>
    </div>
    
    // TONS of these boxes (5 to 20 per page)
    // This code shows me pulling from cookies but I'm starting to pull from the database.
    // The DB query seems to be working even if I'm hard coding each query instead of looping through.

    RADIO BUTTONS (other part of update)

    HTML Code:
    <div class="radio_buttons">
    <b><font size="3" color="#0069ff"> OVERALL STATUS:</font></br>
    <input type="radio" name="shoe_status" id="shoe_nom" value="1"><label>Nominal</label>
    <input type="radio" name="shoe_status" id="shoe_marg" value="2"><label>Marginal</label>
    <input type="radio" name="shoe_status" id="shoe_prob" value="3"><label>Problem</label>
    </br></br><align="center"><input name="update" type="submit" id="update" value="Save Status"></align> <!-- Not that this stupid align tag works =/ -->
    </b></form>
    </div> <!-- end radio button div -->
    I'm updating the database. I'm able to pull information from the database.

    But this is from me hard coding each box which is dumb. I probably went overboard saying it but I could really use some help with a loop function so it dynamically creates an Update sql query for each box that is updated as well as updating the overall status (radio buttons).

    All of this is over my head tbh and I probably won't understand half of what anyone says which is why I'm begging so hard for the loop function. Also if the original question still stands, if you know of a better way to do this and make this more efficient I would love to know. Its kinda hard for me to think like a programmer right now but maybe I can get there if I can solve this project, which is why I took this project on. I volunteered for this so I could learn but learning on my own I can't help but think I'm learning some bad habits (beyond the mysqli and injection prone sql statements).

  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    774
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Just a quick look.

    Is it sid=1
    OR
    side=1

  5. #5
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sid=1

    That was a typo - shame I can't edit that. I had to hand type all of this code so it's not copy & paste as all my code is on the LAN. It's typed correctly in the actual code otherwise the DB wouldn't get updated but thanks for checking so far! I might be missing something simple like that keeping me from updating both records using only one retval. I have not even attempted the foreach loop (post 2) yet because I know my logic isn't fleshed all the way out. Hurts my brain trying to figure it out.

  6. #6
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sooo...

    if $_POST is receiving the following:
    - shoe_status (from radio buttons)
    - shoe_nameXX (dynamic insert from JS onClick)
    - shoe_valueXX (dynamic insert from JS on Click)

    How can I form my loop so I ignore shoe_status (was thinking if (! $_POST(shoe_status))) but grab each box that was updated and create an SQL update for each one? Since the name is dynamic I think I need the syntax to parse the first 9 characters in the IF statement and Loop through with a counter checking what is not null and then create the SQL statement. But perhaps there is a better way?

    What is the best way to bridge the gap between JS variables and PHP variables?

    Does no one have any ideas on this? So many smart people on this forum and no one willing to help me out on this loop function. Perhaps my post was offensive in some way?

  7. #7
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    774
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    I played around with your code a bit and just couldn't get it to work like I wanted. Decided to make my own version, going off the theme of creating Meetings and a list of items to toggle the status on. This page covers pretty much all of it even commented out mysqli connection. You will notice an array where you can adjust button names and colors near the top of the page. Also there are two button styles. I will also upload the sql files for the little testing I did.

    PHP Code:
    if(isset($status) && array_key_exists($status,$status_types['sname'])){ 
    NOTE: Small change to line 238 that is NOT done in attached copy. Used Posted Copy below.
    PHP Code:
    <?php
    session_start
    ();
    include (
    "config.php");

    //////// config.php ///////
    //$host = "localhost";  
    /*Database user name.*/    
    //$login = "";
    /*Database Password.*/
    //$dbpass = "";
    /*Database name.*/
    //$dbname = "";
    //$db = new mysqli("$host", "$login", "$dbpass", "$dbname"); 
    //////////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////
    /*Assumimg "Meeting Mgr" will have an ID set to session as Mgr_id when they log in.
    For testing I will force this variable below.  Remove this section in live login mode.*/    
    $_SESSION['Mgr_id'] = "124";                                                                              
    ////////////////////////////////////////////////////////////////////////////// 
    //////////////////////////////////////////////////////////////////////////////
    /* Make sure manager is logged in.  
    Change ['Mgr_id'] session key if needed, e.g. ['user_id'] or whatever you are using. */ 
    if(!isset($_SESSION['Mgr_id'])){
        
    header("location: login.php");
        exit;
    }
    //////////////////////////////////////////////////////////////////////////////
        
    $Mgr_id $_SESSION['Mgr_id'];
    //////////////////////////////////////////////////////////////////////////////

    ////////////////////////////////////////////////////////////////////////////// 
    /////////////// Set Name, Background Color and Text Color //////////////////// 
    ////////////////////////////////////////////////////////////////////////////// 
    $status_types = array(
        
    "sname" => array("Not Defined","Normal","Marginal","Problem"),
        
    "bgcolor" => array("#FFFFFF","#006600","#FFFF00","#FF0000"),
        
    "txtcolor" => array("#000000","#FFFFFF","#000000","#FFFFFF")
        );
    //////////////////////////////////////////////////////////////////////////////
    ////////////////////////////////////////////////////////////////////////////// 
    // Two button styles.  Round = blank, full rectangle = 2
    $button_type ""
    //$button_type = "2";
    //////////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////


    //////////////////////////////////////////////////////////////////////////////
    ////////////////////////////// Add New Meeting //////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////

    if(isset($_POST['AddMeeting']) && !empty($_POST['newmeeting'])){
                                                                     
        
    $newmeeting mysqli_real_escape_string($db$_POST['newmeeting']);
        
    $mgr_id mysqli_real_escape_string($db$Mgr_id);
        
    $mod_date date('Y-m-d H:i:s');
        
        
    $sql "INSERT INTO `meetings` (`meeting_name`, `mgr_id`, `date_created`, `date_modified`) 
        VALUES('
    $newmeeting', '$mgr_id', '$mod_date', '$mod_date')";
        
    $db->query($sql) or die(mysqli_error($db));
    }

    //////////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////


    //////////////////////////////////////////////////////////////////////////////
    ////////////////////////////// Add New Item //////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////

    if(isset($_POST['newitem']) && !empty($_POST['newitem'])){
                                                                     
        
    $newitem mysqli_real_escape_string($db$_POST['newitem']);
        
    $Meeting_id mysqli_real_escape_string($db$_POST['Meeting_id']);
        
    $mgr_id mysqli_real_escape_string($db$Mgr_id);
        
    $mod_date date('Y-m-d H:i:s');
        
        
    $sql "INSERT INTO `items` (`item_name`, `meeting_id`, `mgr_id`, `date_modified`) 
        VALUES('
    $newitem', '$Meeting_id', '$mgr_id', '$mod_date')";
        
    $db->query($sql) or die(mysqli_error($db));
        
        
    $sqlu "UPDATE `meetings` SET `date_modified` = '$mod_date'";
        
    $db->query($sqlu) or die(mysqli_error($db));
    }
    //////////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////



    //////////////////////////////////////////////////////////////////////////////
    ////////////////////// Update Item Status and Name ///////////////////////////
    //////////////////////////////////////////////////////////////////////////////

    if(isset($_POST['Save'])){ 

        
    $Meeting_id mysqli_real_escape_string($db$_POST['Meeting_id']);
        
    $mgr_id mysqli_real_escape_string($db$_SESSION['Mgr_id']);
        
    $mod_date date('Y-m-d H:i:s');
        
        foreach(
    $_POST['item_name'] as $item_id => $item_name){
                 
            
    $item_name mysqli_real_escape_string($db$item_name);         
            
    $status_name trim($_POST['item_status'][$item_id]);     
            
    $status array_search($status_name,$status_types['sname']);
            
            
    $sql "UPDATE `items` SET `item_name` = '$item_name', `status` = '$status', `date_modified` = '$mod_date'  
            WHERE `item_id` = 
    $item_id AND `meeting_id` = '$Meeting_id'"
            
    $db->query($sql) or die(mysqli_error($db));
        }    
        
        
    $sqlu "UPDATE `meetings` SET `date_modified` = '$mod_date' WHERE `meeting_id` = '$Meeting_id'";
        
    $db->query($sqlu) or die(mysqli_error($db));
    }
    ////////////////////////////////////////////////////////////////////////////// 
    ////////////////////////////////////////////////////////////////////////////// 

    //////////////////////////////////////////////////////////////////////////////
    ////////////////////////// Meeting Table Cells ///////////////////////////////
    //////////////////////////////////////////////////////////////////////////////
    $meeting_rows "";
    if(isset(
    $Mgr_id) && !empty($Mgr_id)){
        
    $meeting_rows .= "<tr>
                <th colspan=\"4\">Meetings</th>
            </tr>    
            <tr>
                <td colspan=\"4\">
                    <form method=\"post\" action=\"\">
                        <div style=\"width:75%; float:left; text-align:center;\">
                            <input type=\"text\" name=\"newmeeting\" value=\"\" class=\"name\" placeholder=\"Enter NEW Meeting Name Here\" />
                        </div>
                        <div style=\"width:25%; float:left; text-align:center;\">
                            <input type=\"submit\" name=\"AddMeeting\" value=\"Add Meeting\" />
                        </div>
                    </form>
                </td>
            </tr>
            <tr class=\"subheading\">
                <td style=\"text-align:center;\">Meeting Name</td>
                <td style=\"text-align:center;\">Date Created</td>
                <td style=\"text-align:center;\">Date Modified</td>
                <td style=\"text-align:center;\">View</td>
            </tr>\r"
    ;
        
    $sql "SELECT
        m.meeting_id,
        m.meeting_name,
        m.date_created,
        m.date_modified
        FROM meetings as m
        WHERE m.mgr_id = '
    $Mgr_id' ORDER BY m.meeting_id  DESC";
        
    $result $db->query($sql) or die(mysqli_error($db));    
        while(
    $row $result->fetch_array()) {
            
    $meeting_id $row['meeting_id'];
            
    $meeting_name $row['meeting_name'];
            
    $date_created $row['date_created'];
            
    $date_modified $row['date_modified'];
          
    $meeting_rows .= "<tr>
                <td style=\"text-align:center; font-weight:bold;\">
    $meeting_name</td>
                <td style=\"text-align:center; font-weight:bold;\">
    $date_created</td>
                <td style=\"text-align:center; font-weight:bold;\">
    $date_modified</td>
                <td style=\"text-align:center;\">
                    <form method=\"post\" action=\"\">
                        <input type=\"hidden\" name=\"Meeting_id\" value=\"" 
    $meeting_id "\" />
                        <input type=\"submit\" name=\"view_meeting\" value=\"View\" />
                    </form>
                </td>
            </tr>\r"
    ;
        }


    //////////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////
    if(isset($_POST['Close'])){
        unset(
    $_POST['Meeting_id']);
        unset(
    $meeting_id);
    }
    //////////////////////////////////////////////////////////////////////////////
    ////////////////////// Build Status Table Rows ///////////////////////////////
    ////////////////////////////////////////////////////////////////////////////// 
    $display_rows "";

    // Check for POST meeting ID and query for data.
    if(isset($_POST['Meeting_id']) && !empty($_POST['Meeting_id'])){
        
        
    $Mgr_id mysqli_real_escape_string($db$Mgr_id);
        
    $Meeting_id mysqli_real_escape_string($db$_POST['Meeting_id']);
        
        
    $sql "SELECT
        m.meeting_name,
        m.date_modified,  
        i.item_id, 
        i.item_name, 
        i.status
        FROM meetings as m 
        LEFT JOIN items as i 
        ON i.meeting_id = m.meeting_id AND i.mgr_id = m.mgr_id 
        WHERE m.meeting_id = '
    $Meeting_id' AND m.mgr_id = '$Mgr_id' ORDER BY i.item_id  ASC";
        
    $result $db->query($sql) or die(mysqli_error($db));
        
        
    $h=0
        while(
    $row $result->fetch_array()) {
            
    $meeting_name $row['meeting_name'];
            
    $date_modified $row['date_modified'];
            
    $item_id $row['item_id'];
            
    $item_name $row['item_name'];
            
    $status $row['status'];
            
            if(
    $h==0){
            
    $display_rows .= "<tr>
                <th colspan=\"2\">
                    <div style=\"width:25%; float:left;\">&nbsp;</div>
                    <div style=\"width:50%; float:left;\">" 
    $meeting_name ."</div>
                    <div style=\"width:25%; float:left;\">" 
    $date_modified "</div>
                </th>
            </tr>
            <tr>
                <td colspan=\"2\">
                    <div style=\"width:75%; float:left; text-align:center;\">
                        <input type=\"text\" name=\"newitem\" value=\"\" class=\"name\" placeholder=\"Enter NEW Item Name Here\" />
                    </div>
                    <div style=\"width:25%; float:left; text-align:center;\">
                        <input type=\"submit\" name=\"AddItem\" value=\"Add Item\" />
                    </div>
                </td>
            </tr>
            <tr class=\"subheading\">
                <td>
                Item Name
                <input type=\"hidden\" name=\"Meeting_id\" value=\"" 
    $Meeting_id ."\" />
                <input type=\"hidden\" name=\"Mgr_id\" value=\"" 
    $Mgr_id ."\" />
                </td>
                <td>Status</td>
            </tr>\r"
    ;
                
    $h++;
            }  
            
            
    // check for status
            
    if(isset($status) && array_key_exists($status,$status_types['sname'])){ 
                
    $status_name $status_types['sname'][$status];
                
    $status_color $status_types['bgcolor'][$status];
                
    $status_color_text $status_types['txtcolor'][$status];
                
                
    $display_rows .= "<tr>
                    <td style=\"text-align:center; width:50%;\">
                        <input type=\"text\" name=\"item_name[
    $item_id]\" value=\"" $item_name ."\" class=\"name\" />
                    </td>
                    <td style=\"text-align:center; width:50%;\">
                        <input type=\"text\" name=\"item_status[
    $item_id]\" id=\"item$item_id\" value=\"" $status_name ."\" class=\"clr" $button_type ."\" style=\"background-color:" $status_color "; color: " $status_color_text "\" onclick=\"return change('item$item_id');\" />
                    </td>
                </tr>\r"
    ;
            }
        }    
            
    $display_rows .= "<tr>
                <td colspan=\"2\" style=\"text-align:center;\">
                    <input type=\"submit\" name=\"Save\" value=\"Save\" />
                    <input type=\"submit\" name=\"Close\" value=\"Close\" />
                </td>
            </tr>
            <tr>
                <td colspan=\"2\" style=\"text-align:center;\">
                    Item names can be editing above.  Click the Status button to toggle throught options.
                </td>
            </tr>\r"
    ;
    }

    ?>

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
    <head>
    <style type="text/css">
    .clr {
      border:1px solid #515151;
      box-shadow:0 15px 10px -15px rgba(0,0,0,0.7); 
      overflow:hidden; 
      text-align:center;   
      font-family: Arial;
      font-size: 13px;
      font-weight:bold;
      box-shadow:inset 2px 2px 5px rgba(255, 255, 255, 0.5), inset -2px -2px 5px rgba(0, 0, 0, 0.5);
      border-radius:20px 20px 20px 20px;
    }
    .clr2 {
      width: 100%;
      border:1px solid #515151;
      box-shadow:0 15px 10px -15px rgba(0,0,0,0.7); 
      overflow:hidden; 
      text-align:center;   
      font-family: Arial;
      font-size: 13px;
      font-weight:bold;
      box-shadow:inset 2px 2px 5px rgba(255, 255, 255, 0.5), inset -2px -2px 5px rgba(0, 0, 0, 0.5);
    }
    .display {
      width:700px;
      margin: 5px auto 15px;
      background-color:#DFDFDF;
      color:#00000;  
      font-family: Arial;
      font-size: 13px;
    }
    .display th{
      background-color:#848484;
      color:#FFF;
      font-size: 12px;
      font-weight:bold;
      text-align:center;
      white-space:nowrap;
    }
    .display .subheading td{
      background-color:#E9E9E9;
      color:#3E3E3E;
      font-size: 11px;
      font-weight:bold;
      text-align:center;
      white-space:nowrap;
    }
    .display td{
      background-color:#FBFBFB;
      color:#00000;
      font-weight:normal;
      white-space:nowrap;
    }
    .display .name {
      width:98%;
      background-color:#FDFDFD;
      color:#00000;
      font-weight:bold;
      white-space:nowrap;
      border: none;
      border:1px solid #DFDFDF;
    </style>
    </head>
    <body>

        <table border="0" cellspacing="1" cellpadding="2" class="display">
            <?php echo $meeting_rows?>
        </table>
        
    <form action="" method="post">
        <table border="0" cellspacing="1" cellpadding="2" class="display">
            <?php echo $display_rows?>        
        </table>
    </form>

    <!-- Using $status_types array we echo the value for each element in JS -->
    <script type="text/javascript">    
    function change(objid) // no ';' here
    {
        var elem = document.getElementById(objid);
        if (elem.value=="<?php echo "{$status_types['sname'][0]}"?>") elem.value = "<?php echo "{$status_types['sname'][1]}"?>", elem.style.backgroundColor = "<?php echo "{$status_types['bgcolor'][1]}"?>", elem.style.color = "<?php echo "{$status_types['txtcolor'][1]}"?>";
        else if (elem.value=="<?php echo "{$status_types['sname'][1]}"?>") elem.value = "<?php echo "{$status_types['sname'][2]}"?>", elem.style.backgroundColor = "<?php echo "{$status_types['bgcolor'][2]}"?>", elem.style.color = "<?php echo "{$status_types['txtcolor'][2]}"?>";
        else if (elem.value=="<?php echo "{$status_types['sname'][2]}"?>") elem.value = "<?php echo "{$status_types['sname'][3]}"?>", elem.style.backgroundColor = "<?php echo "{$status_types['bgcolor'][3]}"?>", elem.style.color = "<?php echo "{$status_types['txtcolor'][3]}"?>";
        else if (elem.value=="<?php echo "{$status_types['sname'][3]}"?>") elem.value = "<?php echo "{$status_types['sname'][0]}"?>", elem.style.backgroundColor = "<?php echo "{$status_types['bgcolor'][0]}"?>", elem.style.color = "<?php echo "{$status_types['txtcolor'][0]}"?>";    
    }
    </script>
    </body>
    </html>
    Attached Files Attached Files

  8. #8
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, seriously awesome man, thanks!

    I am getting a PHP error (blank page) trying out the code but it may be because I had to hand-type everything (LAN isn't connected to the internet). I tried copying and pasting the code into an online engine and I get an error as well. Is there one (online code parsing engine) you recommend? The environment I'm working in is using MySQL 5.1, is mysqli supported in 5.1? Looks like it according to this manual.

    Even though I cannot get the code to work, yet, I've learned a lot of great techniques. You don't learn half of these techniques in online tutorials, or at least the ones I am reading. I didn't know you could escape " by putting a \ in front of it. The use of arrays on status_types is gold. I should have thought to echo the HTML like you did as that gets past my hurdle where I needed the php code before the html but I couldn't tie my PHP variables to it. Not sure I understand how this next part works, I first tried connecting this to the MySQL DB I'm using but it was saying I had a syntax error with this "m. " code.

    PHP Code:
    if(isset($_POST['Meeting_id']) && !empty($_POST['Meeting_id'])){
        
        
    $Mgr_id mysqli_real_escape_string($db$Mgr_id);
        
    $Meeting_id mysqli_real_escape_string($db$_POST['Meeting_id']);
        
        
    $sql "SELECT
        m.meeting_name,   // <<<<<<<<<<<<<< this code it is complaining about, think it was actually the WHERE statement
        m.date_modified,   // but I don't understand how this m. and i. stuff works 
        i.item_id, 
        i.item_name, 
        i.status
        FROM meetings as m 
        LEFT JOIN items as i 
        ON i.meeting_id = m.meeting_id AND i.mgr_id = m.mgr_id 
        WHERE m.meeting_id = '
    $Meeting_id' AND m.mgr_id = '$Mgr_id' ORDER BY i.item_id  ASC";
        
    $result $db->query($sql) or die(mysqli_error($db)); 
    Not sure if that was the exact part it had issues with but it contains the text the error message had (line # was off). I've never seen forward ticks/quotes used before (`). What does that provide?

    Speaking of the Database. How should I set up those tables? (meeting and items)


    So glad you responded. I've learned more from your response than I have by reading tons of stackoverflow questions and tutorials. Definitely more advanced than my current level.

  9. #9
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    You might want to consider upgrading your MySQL to a newer version as the current (community (GPL)) version is 5.6
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  10. #10
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    You might want to consider upgrading your MySQL to a newer version as the current (community (GPL)) version is 5.6
    I have no control over the environment. I cannot update anything. The environment is Red Hat (forgot version), Firefox (forgot version) and MySQL 5.1.

  11. #11
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    774
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    You are connecting with mysqli right like shown at the top of the example? Not mysql.
    I'm wondering if the error is on those mysqli_real_escape_string() lines.
    PHP Code:
        $Mgr_id mysqli_real_escape_string($db$Mgr_id);
        
    $Meeting_id mysqli_real_escape_string($db$_POST['Meeting_id']); 
    If it is a connection issue, you might try this instead.
    PHP Code:
    $db mysqli_connect("$host""$login""$dbpass""$dbname"); 
    Oher than that I'm not sure what is causing the error. Try making the "as" uppercase. I usually write it as uppercase, not sure why I didn't in this example.
    PHP Code:
        $sql "SELECT
        m.meeting_name,   
        m.date_modified,   
        i.item_id, 
        i.item_name, 
        i.status
        FROM meetings AS m 
        LEFT JOIN items AS i 
        ON i.meeting_id = m.meeting_id AND i.mgr_id = m.mgr_id 
        WHERE m.meeting_id = '
    $Meeting_id' AND m.mgr_id = '$Mgr_id' ORDER BY i.item_id  ASC"
    So the m. and i. are table identifiers defined after that "AS" when you call the table. When using JOIN you define ON what fields must match from second table (i) to first table (m), which is why we say ON i.meeting_id = m.meeting_id AND i.mgr_id = m.mgr_id.

  12. #12
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    When you echo the content $sql what do you get?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  13. #13
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    774
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    You said you wanted to see it, well here's a basic sample.
    Example136.jpg

  14. #14
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    When you echo the content $sql what do you get?
    Nothing. I'm getting a blank page currently.


    Quote Originally Posted by Drummin View Post
    You said you wanted to see it, well here's a basic sample.
    Example136.jpg
    It won't let me see the pic (forum says invalid attachment). Will try on another PC later.

    So using your code I'm past the SQL error ...and that is even with using the MySQL database rather than local host, but I'm still getting a blank page when I try to load it. Gotta love how PHP gives you a blank page when rather than telling the browser's Error Console what the problem is.

    Will try playing around with it some more. I'm going to try changing all the "mysqli"s into the old "mysql" to see if that is the problem because outside of the closing bracket missing in the CSS code I can't spot any errors so I'm wondering if this code is just too new for the environment I'm working in. If you see or think of something let me know.

  15. #15
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    774
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Are you sure you are in a php environment? If you add this to the top of the page does it show?
    PHP Code:
    <?php
    echo "Hello";
    ?>
    IF it shows at the top, try moving it to the bottom.

  16. #16
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,904
    Mentioned
    74 Post(s)
    Tagged
    7 Thread(s)
    Gotta love how PHP gives you a blank page when rather than telling the browser's Error Console what the problem is.


    Try this at the top of your page:
    PHP Code:
    <?php
    error_reporting
    (-1);
    ini_set('display_errors'true);
    echo 
    $test /0;
    die;
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  17. #17
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Drummin View Post
    Are you sure you are in a php environment? If you add this to the top of the page does it show?
    PHP Code:
    <?php
    echo "Hello";
    ?>
    IF it shows at the top, try moving it to the bottom.
    lol Actually I did try that just to see. In fact I been slicing and dicing the code with a hello to see what loads and what doesn't. I haven't tried everything yet, but so far I do it does not like this query for some reason...

    $result = $db->query($sql) or die(mysqli_error($db));

    Something about that is making PHP unhappy about loading. Still have more lines to parse.

    Quote Originally Posted by John_Betong View Post


    Try this at the top of your page:
    PHP Code:
    <?php
    error_reporting
    (-1);
    ini_set('display_errors'true);
    echo 
    $test /0;
    die;
    Awesome, will have to try that one for sure. If it works, it may turn out to be a favorite of mine since debugging PHP is not intuitive.

  18. #18
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    774
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Also for test add...
    PHP Code:
    if(!$db){
        echo 
    "No connection to database";

    $result = $db->query($sql) or die(mysqli_error($db));
    If you really think it's this line you might try changing the queries to...
    PHP Code:
        //$result = $db->query($sql) or die(mysqli_error($db));    
        
    $result mysqli_query($db,$sql) or die(mysqli_error($db)); 
    I have a feeling you're not connecting with mysqli (notice the "i" at the end) or something to do with table of field names or the Casing, i.e. Uppercase, lowercase. If you get that display errors going it should shed some light on it.

  19. #19
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Drummin View Post
    Also for test add...
    PHP Code:
    if(!$db){
        echo 
    "No connection to database";


    If you really think it's this line you might try changing the queries to...
    PHP Code:
        //$result = $db->query($sql) or die(mysqli_error($db));    
        
    $result mysqli_query($db,$sql) or die(mysqli_error($db)); 
    I have a feeling you're not connecting with mysqli (notice the "i" at the end) or something to do with table of field names or the Casing, i.e. Uppercase, lowercase. If you get that display errors going it should shed some light on it.
    Actually I already have code that checks for the database connection. I have code checking for the table space pull as well.

    I replaced the above line (before I saw your reply) with the following...

    PHP Code:
    $result mysql_query($db,$sql);
    if(! 
    $result) { die {'Not working ' . die(mysql_error($db)); } 
    I went that approach simply because I know that works in this environment. Then I can start trimming the excess code off and adding the 'i' back in. Using this approach got "Not working" so at least the PHP syntax is happy so far. But since I trimmed the code to only the 'Meeting Table Cells' section I don't expect the query to work at this point anyway. Once I get it so its happy with the syntax I can start troubleshooting the actual database queries. I think the environment I'm working in is probably 3 generations behind so it probably doesn't know some of this syntax.


    Won't be able to try anything else out till next week.

  20. #20
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    774
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Ahhh see, your connection? You are using mysql, not mysqli as the page is coded in. If you can, switch to the mysqli login sample I provided.

    IF you are going to use mysql, then you need to change those mysqli_real_escape_string(); lines so they don't have the $db in them like so. Note they don't have the "i" in mysqli.

    PHP Code:
    $newmeeting mysql_real_escape_string($_POST['newmeeting']); 
    And then your queries would just be

    PHP Code:
    $result mysql_query($sql) or die(mysql_error()); 
    Again without the "i".

    I believe I asked you this in this post.
    Post #11

  21. #21
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry I guess I didn't do a good job explaining.

    I have 1 instance of your file, completely untouched using your config.php connection file where it uses the local host. I am getting a blank page using this file.

    I created a second copy of your meetings file. In this second copy I am using a modified version of your connection file (it's still mysqli (not mysql)) where I included error checking but it connects to my database instead of localhost. In this second copy I am copying and pasting code from the first file section by section to see what code works and what code is making the page go blank. I am getting to the section you titled "Meeting Table Cells" which makes sense since all the previous sections rely on $_POST data which is obviously not happening since no post data. When I played around with the code in this section I found the SQL query below to be the problem child.

    PHP Code:
    $result $db->query($sql) or die(mysqli_error($db)); 
    Since I found that line to be the problem I tried the following code in its place, keeping everything else the same (including the mysqli connection file ...and no code below the Meeting Table Cells section).

    PHP Code:
    $result mysql_query($db,$sql);
    if(! 
    $result) { die {'Not working ' . die(mysql_error($db)); } 
    Making that 1 line change the page will load with it simply saying "Not working" ...which is to be expected. Now since I am only using a snippet of the code (from Top to Meeting Table Cells) obviously it's not working as you intended. I only did this so I can see what is not allowing the page to load for me. When I get back at it tomorrow I will try the above query with mysqli instead since the rest of the code is yours which uses mysqli. Next attempt will be to move the error checking to one line like you have it (with the OR statement). If that works than it means the environment I am working in somehow doesn't like the $db->query($sql) syntax. Since the version of mysql I'm working with is 3gen behind, v5.1, its not hard to believe.

  22. #22
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    774
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    I always have host = localhost whether I dealing with my local development server or a live server hosted somewhere else.

    Maybe you can post the full page including your connection so we might spot the problem.

  23. #23
    SitePoint Member
    Join Date
    Jan 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok for some reason it is not liking the "or" part. Once I figured that part out I was still getting hung up as it kept giving me the error check message on this section of code...

    PHP Code:
    ////////////////////////// Meeting Table Cells ///////////////////////////////
    //////////////////////////////////////////////////////////////////////////////
    $meeting_rows "";
    if(isset(
    $Mgr_id) && !empty($Mgr_id)){
        
    $meeting_rows .= "<tr>
                <th colspan=\"4\">Meetings</th>
            </tr>    
            <tr>
                <td colspan=\"4\">
                    <form method=\"post\" action=\"\">
                        <div style=\"width:75%; float:left; text-align:center;\">
                            <input type=\"text\" name=\"newmeeting\" value=\"\" class=\"name\" placeholder=\"Enter NEW Meeting Name Here\" />
                        </div>
                        <div style=\"width:25%; float:left; text-align:center;\">
                            <input type=\"submit\" name=\"AddMeeting\" value=\"Add Meeting\" />
                        </div>
                    </form>
                </td>
            </tr>
            <tr class=\"subheading\">
                <td style=\"text-align:center;\">Meeting Name</td>
                <td style=\"text-align:center;\">Date Created</td>
                <td style=\"text-align:center;\">Date Modified</td>
                <td style=\"text-align:center;\">View</td>
            </tr>\r"
    ;
        
    $sql "SELECT
        m.meeting_id,
        m.meeting_name,
        m.date_created,
        m.date_modified
        FROM meetings as m
        WHERE m.mgr_id = '
    $Mgr_id' ORDER BY m.meeting_id  DESC";
        
    $result $db->query($sql) or die(mysqli_error($db));    
        while(
    $row $result->fetch_array()) {
            
    $meeting_id $row['meeting_id'];
            
    $meeting_name $row['meeting_name'];
            
    $date_created $row['date_created'];
            
    $date_modified $row['date_modified'];
          
    $meeting_rows .= "<tr>
                <td style=\"text-align:center; font-weight:bold;\">
    $meeting_name</td>
                <td style=\"text-align:center; font-weight:bold;\">
    $date_created</td>
                <td style=\"text-align:center; font-weight:bold;\">
    $date_modified</td>
                <td style=\"text-align:center;\">
                    <form method=\"post\" action=\"\">
                        <input type=\"hidden\" name=\"Meeting_id\" value=\"" 
    $meeting_id "\" />
                        <input type=\"submit\" name=\"view_meeting\" value=\"View\" />
                    </form>
                </td>
            </tr>\r"
    ;
        }

    I remembered to try John_Betong's error check code (thanks again) and it gave me these two error messages...

    Warning: mysqli_query(): Couldn't fetch mysqli in (path of my php file) on line 164
    Warning: mysqli_error(): Couldn't fetch mysqli in (path of my php file) on line 165

    My line numbers are different since I have the error checking code in it. Line 164 is the $result = $db->query($sql) or die(mysqli_error($db)); part (as seen above) except I changed it to $result = mysqli_query($db,$sql); With Line 165 = if(! $result) { die('Meeting Table Query not working ' . mysqli_error($db)); } So I see nothing displayed but "Meeting Table Query not working" + any PHP error messages (no sql msgs).

    So I went back to my old connection file ($db = @mysql_connect ("host", "login", "pw"); ) and changed line 164 and 165 to mysql.

    Now the error message I get is:

    Warning: mysql_query() expects parameter 1 to be a string

    So it's complaining about my $db param if I'm not mistaken but I've used mysql_queries successfully with that db connect file on other pages. The parameter in my file is as listed above and then I have some if(!db) error checking in the file.


    If I made this reply too long and/or confusing let me know. Trying to include enough information (along with the previous post) so you know what I changed, but I'm curious as to why it's complaining it is expecting a string in that sql query.


    EDIT:
    @Drummin

    I won't be able to post the full file but I can hand type any sections you want to see. I will type the full connection file here in a bit. Essentially though the meetings.php file is just like yours except I changed that SQL query text to the format I listed above and I put in John's PHP error checking code. I'm using the file you attached and not the one you posted if that makes any difference. I did change line 238.

  24. #24
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,904
    Mentioned
    74 Post(s)
    Tagged
    7 Thread(s)
    More Debugging stuff:

    Add this function:

    PHP Code:

    /*
      usage:
        vd( $valueToBeTested );

      Output:
          shows type and value;
         
    */
    function vd$value)
    {
      echo 
    '<pre>';
        
    var_dump$value );
      echo 
    '</pre>';

    Before the line wherever you get an error or a warning type vd($valueToBeTested); and check to ensure that the type and value is what your expect.
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  25. #25
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    774
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    I still think it's a connection issue. Would like to see the connection code. And use localhost for the HOST. If using mysqli then all coding in page should be mysqli. If using mysql then all coding on page should be mysql. Can we assume that $_SESSION['Mgr_id'] is set?


Bookmarks

Posting Permissions

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